Hiding cell values via WriteExcel module

Discussion in 'Perl Misc' started by dn.perl@gmail.com, Mar 9, 2009.

  1. Guest

    A quick search of the forum's archives shows that there might be a way
    to hide a cell in an excel sheet via win32::eek:le module.
    http://groups.google.com/group/comp...d?lnk=gst&q=excel hidden ole#7943a11cad6139dd

    Excel's help for the topic 'Hide or display cell values' suggests that
    if a cell's format is set to Number > Custom, and the type is set
    to ;;; (three semi-colons), the field is shown blank on the sheet but
    the correct value remains associated with the cell. Can this hiding of
    the data for a cell or an entire row or an entire column be achieved
    through WriteExcel module?

    Even if I could *actually* set a column's width to 0 via WriteExcel
    module, it would do.
    But if I use: set_column('B:B', 0) ; the column B's width is set to
    some non-zero default value which Excel seems to calculate internally.

    TIA.
     
    , Mar 9, 2009
    #1
    1. Advertising

  2. smallpond Guest

    On Mar 9, 5:25 pm, wrote:

    >
    > Excel's help for the topic 'Hide or display cell values' suggests that
    > if a cell's format is set to Number > Custom, and the type is set
    > to ;;; (three semi-colons), the field is shown blank on the sheet but
    > the correct value remains associated with the cell. Can this hiding of
    > the data for a cell or an entire row or an entire column be achieved
    > through WriteExcel module?
    >


    Have you tried it?
    $hide_cell = set_num_format(';;;');
    $worksheet->write(0, 0, 3.1415926, $hide_cell);
     
    smallpond, Mar 9, 2009
    #2
    1. Advertising

  3. Jim Gibson Guest

    In article
    <>,
    <> wrote:

    > A quick search of the forum's archives shows that there might be a way
    > to hide a cell in an excel sheet via win32::eek:le module.

    [...]
    >
    > Even if I could *actually* set a column's width to 0 via WriteExcel
    > module, it would do.
    > But if I use: set_column('B:B', 0) ; the column B's width is set to
    > some non-zero default value which Excel seems to calculate internally.


    The version of Spreadsheet::WriteExcel on my system (2.25) has a hidden
    attribute in the set_column method:

    set_column($first_col, $last_col, $width, $format, $hidden, $level,
    $collapsed)

    with examples:

    $worksheet->set_column('D:D', 20, $format, 1);
    $worksheet->set_column('E:E', undef, undef, 1);

    Have you tried that?

    --
    Jim Gibson
     
    Jim Gibson, Mar 10, 2009
    #3
  4. smallpond Guest

    On Mar 9, 8:32 pm, Jim Gibson <> wrote:
    > In article
    > <>,
    >
    >
    >
    > <> wrote:
    > > A quick search of the forum's archives shows that there might be a way
    > > to hide a cell in an excel sheet via win32::eek:le module.

    > [...]
    >
    > > Even if I could *actually* set a column's width to 0 via WriteExcel
    > > module, it would do.
    > > But if I use: set_column('B:B', 0) ; the column B's width is set to
    > > some non-zero default value which Excel seems to calculate internally.

    >
    > The version of Spreadsheet::WriteExcel on my system (2.25) has a hidden
    > attribute in the set_column method:
    >
    > set_column($first_col, $last_col, $width, $format, $hidden, $level,
    > $collapsed)
    >
    > with examples:
    >
    > $worksheet->set_column('D:D', 20, $format, 1);
    > $worksheet->set_column('E:E', undef, undef, 1);
    >
    > Have you tried that?
    >
    > --
    > Jim Gibson


    I think that is for hiding formulas rather than values.
     
    smallpond, Mar 10, 2009
    #4
  5. Jim Gibson Guest

    In article
    <>,
    smallpond <> wrote:

    > On Mar 9, 8:32 pm, Jim Gibson <> wrote:
    > > In article
    > > <>,
    > >
    > > The version of Spreadsheet::WriteExcel on my system (2.25) has a hidden
    > > attribute in the set_column method:
    > >
    > > set_column($first_col, $last_col, $width, $format, $hidden, $level,
    > > $collapsed)
    > >
    > > with examples:
    > >
    > > $worksheet->set_column('D:D', 20, $format, 1);
    > > $worksheet->set_column('E:E', undef, undef, 1);
    > >
    > > Have you tried that?
    > >

    >
    > I think that is for hiding formulas rather than values.


    It is for setting default values for columns. From the documentation:

    "The $hidden parameter should be set to 1 if you wish to hide a column.
    This can be used, for example, to hide intermediary steps in a
    complicated calculation:"

    Followed by the above examples.

    --
    Jim Gibson
     
    Jim Gibson, Mar 10, 2009
    #5
  6. smallpond Guest

    On Mar 10, 12:37 pm, Jim Gibson <> wrote:
    > In article
    > <>,
    >
    >
    >
    > smallpond <> wrote:
    > > On Mar 9, 8:32 pm, Jim Gibson <> wrote:
    > > > In article
    > > > <>,

    >
    > > > The version of Spreadsheet::WriteExcel on my system (2.25) has a hidden
    > > > attribute in the set_column method:

    >
    > > > set_column($first_col, $last_col, $width, $format, $hidden, $level,
    > > > $collapsed)

    >
    > > > with examples:

    >
    > > > $worksheet->set_column('D:D', 20, $format, 1);
    > > > $worksheet->set_column('E:E', undef, undef, 1);

    >
    > > > Have you tried that?

    >
    > > I think that is for hiding formulas rather than values.

    >
    > It is for setting default values for columns. From the documentation:
    >
    > "The $hidden parameter should be set to 1 if you wish to hide a column.
    > This can be used, for example, to hide intermediary steps in a
    > complicated calculation:"
    >
    > Followed by the above examples.
    >


    Sorry. I thought set_column was applying the hidden property to
    cells in the column, but it is actually hiding the whole column.
    Too many uses of "hidden" in that program.
     
    smallpond, Mar 10, 2009
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fabio

    Spreadsheet::WriteExcel

    Fabio, Mar 2, 2004, in forum: Perl
    Replies:
    2
    Views:
    902
    Tom Niesytto
    Mar 4, 2004
  2. Erica
    Replies:
    2
    Views:
    118
    John M. Gamble
    Sep 5, 2003
  3. Pam
    Replies:
    7
    Views:
    360
  4. Replies:
    2
    Views:
    150
  5. Replies:
    2
    Views:
    120
    Jim Gibson
    Jul 8, 2009
Loading...

Share This Page