Hiding cell values via WriteExcel module

[dn.perl]

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: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.

 

[smallpond]

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);

 

[Jim Gibson]

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: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', 20, $format, 1);
$worksheet->set_column('E:E', undef, undef, 1);

Have you tried that?

 

[smallpond]

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: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', 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.

 

[Jim Gibson]

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.

 

[smallpond]

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.