Change the cell format to "text" instead of "general"
Fúlvio
I am starting to use perl and get a code that uses module
Spreadsheet::WriteExcel.
I am facing a problem when I try to create a ".csv" file that contains
"========" characters. The problem is that the cells in ".csv" file
reads these
"========" characters as a fomula because the cell format in ".csv"
file by default
is "general".
My code uses "$workbook->addformat()" to format the text in cells.
Is it possible to change the cell format to "text" using this code?
If not, is there another solution using perl?
Thanks a lot
Fúlvio
Wardman...@emc.com
I just did a search for "text" in the doco. There's plenty of information in there and in the examples. Here's just one way you can write strings but check out the formats.pl example for lots of tricks.
write_string($row, $column, $string, $format)
Write a string to the cell specified by $row and $column:
$worksheet->write_string(0, 0, "Your text here" );
$worksheet->write_string('A2', "or here" );
The maximum string size is 32767 characters. However the maximum string
segment that Excel can display in a cell is 1000. All 32767 characters
can be displayed in the formula bar.
The $format parameter is optional.
Fúlvio
The problem in this case is that my program is structured to use
write_row.
This function(write_row) is called a lot of time in my program so, I
can't change these calls.
In my case I need to format the cells in addformat to indicate that
the cells format is
"text" instead of "general".
Fúlvio
jmcnamara
On Dec 5, 11:08 am, "Fúlvio" <fulvi...@gmail.com> wrote:
> Hi Wardman,
>
> The problem in this case is that my program is structured to use
> write_row.
> This function(write_row) is called a lot of time in my program so, I
> can't change these calls.
> In my case I need to format the cells in addformat to indicate that
> the cells format is
> "text" instead of "general".
In that case you should probably modify the behaviour of write() using
add_write_handler() to write the "====+" strings as text. See the
add_write_handler() section of the docs and the associated examples:
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#add_write_handler($re,_$code_ref)
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/examples/write_handler1.pl
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/examples/write_handler2.pl
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/examples/write_handler3.pl
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/examples/write_handler4.pl
John.
--
Fúlvio
But I don't understand how to use the add_write_handler function - I
am starting to work with perl :( .
The code that I am using is bellow.
foreach my $record (@$fields_listref)
{
$worksheet->write_row($row++, $col, $record);
}
The $record is the data that will be write and I don't know who data
are these. Can be number, text etc.
My main question is:
Is it possible to change the cells format using perl? addformat()
function
change the bold, underlined etc. Can this function change the cells
format?
Thanks again
Fúlvio
On Dec 5, 9:02 am, jmcnamara <jmcnam...@cpan.org> wrote:
> On Dec 5, 11:08 am, "Fúlvio" <fulvi...@gmail.com> wrote:
>
> > Hi Wardman,
>
> > The problem in this case is that my program is structured to use
> > write_row.
> > This function(write_row) is called a lot of time in my program so, I
> > can't change these calls.
> > In my case I need to format the cells in addformat to indicate that
> > the cells format is
> > "text" instead of "general".
>
> Hi Fúlvio,
>
> In that case you should probably modify the behaviour of write() using
> add_write_handler() to write the "====+" strings as text. See the
> add_write_handler() section of the docs and the associated examples:
>
>
> John.
> --
mo...@hol.gr
I'm not sure this is your case, but it seems to be:
You can have format control, not just over rows, but also over
individual cells.
In my case, what I did is first specify the formats I needed:
my $formatd = $workbookd->addformat();
$formatd->set_bold();
$formatd->set_size(12);
$formatd->set_color('black');
$formatd->set_align('center');
my $format_alarm0d= $workbookd->addformat();
$format_alarm0d->set_bold();
$format_alarm0d->set_size(12);
$format_alarm0d->set_color('green');
$format_alarm0d->set_align('center');
my $format_alarm2d= $workbookd->addformat();
$format_alarm2d->set_bold();
$format_alarm2d->set_size(12);
$format_alarm2d->set_color('red');
$format_alarm2d->set_align('center');
my $format_alarm1d= $workbookd->addformat();
$format_alarm1d->set_bold();
$format_alarm1d->set_size(12);
$format_alarm1d->set_color('orange');
$format_alarm1d->set_align('center');
Next,
if(condition1){$formatday=$formatd;}
elsif(condition2){$formatday=$format_alarm2d;}
elsif(condition3){$formatday=$format_alarm0d;}
....
my $column=0; while($column<=$max_no_columns){
$rworksheettimdaily->{$date1}->write($row,$column,$date,$formatday);$column++;}
Does this sound like what you want?
>
> Sorry John,
>
> But I don't understand how to use the add_write_handler function - I
> am starting to work with perl :( .
> The code that I am using is bellow.
>
> foreach my $record (@$fields_listref)
> {
> $worksheet->write_row($row++, $col, $record);
> }
>
> The $record is the data that will be write and I don't know who data
> are these. Can be number, text etc.
>
> My main question is:
> Is it possible to change the cells format using perl? addformat()
> function
> change the bold, underlined etc. Can this function change the cells
> format?
>
> Thanks again
>
> F?lvio
>
>
>
> On Dec 5, 9:02 am, jmcnamara <jmcnam...@cpan.org> wrote:
>> On Dec 5, 11:08 am, "F?lvio" <fulvi...@gmail.com> wrote:
>>
>> > Hi Wardman,
>>
>> > The problem in this case is that my program is structured to use
>> > write_row.
>> > This function(write_row) is called a lot of time in my program so, I
>> > can't change these calls.
>> > In my case I need to format the cells in addformat to indicate that
>> > the cells format is
>> > "text" instead of "general".
>>
>> Hi F?lvio,
Fúlvio
I wold like to format the cells properties changing the format from
"general" to "text".
When I do this change the "====" is treated as a string instead of
a formula.
Using the excel what I want is right click on the cell
click on format cell, change the number format category
from "general" to "text".
Thanks
Fúlvio
On Dec 5, 10:25 am, m...@hol.gr wrote:
>
> >> John.
> >> --
jmcnamara
> The problem is that you format only the size, color and align.
> I wold like to format the cells properties changing the format from
> "general" to "text".
> When I do this change the "====" is treated as a string instead of
> a formula.
If you want to write data to Excel that is formatted as 'Text' then
you must do 2 things. The first is to write the data as a string using
write_string() and the second is to apply a text format to the cell
using the Excel text format indicator '@':
my $text_format = $workbook->add_format(num_format => '@');
$worksheet->write_string('A10', '=====', $text_format);
If you are a perl beginner the the add_write_handler() methodology may
be a little advanced for you. Perhaps a better approach would be to
just ignore/omit rows that contain '=====' strings.
Anyway, here is a working example that uses a write handler to process
'=====' strings as text. You can decide if you want to use this
approach.
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('test.xls');
my $worksheet = $workbook->add_worksheet();
my $text_format = $workbook->add_format(num_format => '@');
#
# Add a handler to match strings composed of equal signs.
#
$worksheet->add_write_handler(qr/^=+$/, \&write_underlines);
#
# The following function processes the data when a match is found.
#
sub write_underlines {
my $worksheet = shift;
my @args = @_;
return $worksheet->write_string(@args, $text_format);
}
#
# Simulate CSV conversion
#
my $rows;
while (<DATA>){
next unless /\S/;
chomp;
# You should use a module to parse the csv data
my @row = split ',';
push @$rows, \@row
}
#
# Write the data.
#
my $row = 0;
for my $record (@$rows) {
$worksheet->write_row($row++, 0, $record);
}
__DATA__
North,South,East,West
=====,=====,====,====
100,200,300,400
200,300,400,100
300,400,500,600
1,2000,4,5
John.
--
Wardy
an apostrophe in front of the text?
my $APOS = q{'};
if ( $string =~ m/ \A = /xms ) {
$string = $APOS . $string;
jmcnamara
On Dec 5, 7:54 pm, Wardy <war...@gmail.com> wrote:
> can't you just stick an apostrophe in front of the text?
Strangely, no.
You can (and in many cases have to) do that within Excel to indicate
that certain tokens are strings. However, the apostrophe isn't saved
as part of the string. It is only used to indicate a string within the
Excel environment. As such "'====" and "====" are different strings.
> > If you want to write data to Excel that is formatted as 'Text' then
> > you must do 2 things. The first is to write the data as a string using
> > write_string() and the second is to apply a text format to the cell
> > using the Excel text format indicator '@':
required. The 'Text' format isn't strictly required. It just tells
Excel that you want to maintain this string as text even if it is
edited.
John.
--
Wardman...@emc.com
I had a feeling I'd tried that before.
But I was only thinking of a way to prevent the ==== giving errors.
Similar to your earlier suggestion of getting rid of them altogether.
I have a similar problem with write_row and I ended up just using a loop
to write_* depending on a setting I have in each column's format.
So, rather than write a while row, I just loop through and write each
cell the way I want it.
for my $rowid ( @{$row_heading_ref} ) {
my $multi_line_cell = 0; # Is there a newline in this row?
$col = 0;
for my $column_ref ( @{$column_list_ref} ) {
my $column = $column_ref->[0];
my $format = $column_ref->[2];
if ( defined $row_detail_ref->{DETAIL}->{$rowid}{$column} ) {
# Write any cell value
my $cell_value =
$row_detail_ref->{DETAIL}->{$rowid}{$column};
if ( defined $format and $format eq 'text' ) {
$sheet->write_string( $row, $col, $cell_value );
}
else {
$sheet->write( $row, $col, $cell_value );
}
# Does this row have any mulit-line cells. If so we let
Excel
# pick the row height
if ( !$multi_line_cell and index( $cell_value, "\n" ) >= 0 )
{
$multi_line_cell = 1;
}
# Write any comment
my $cell_comment
= $row_detail_ref->{COMMENT}->{$rowid}{$column} ||
$EMPTY;
if ( $cell_comment ne $EMPTY ) {
$sheet->write_comment( $row, $col, $cell_comment );
}
} ## end if ( defined $row_detail_ref...
$col++;
} ## end for my $column_ref ( @{...
# Fix row height. Don't know why but sometimes it looks
double-spaced
$sheet->set_row( $row, 17 ) unless $multi_line_cell;
$row++;
} ## end for my $rowid ( @{$row_heading_ref...
The column ref thing gets set up elsewhere like:
push @columns, (
['metahead', 10, 'text' ],
['metapos' ],
['metaconf', 13 ],
['metastripe' ],
[qw(mapped), undef, undef, 'hide' ],
[qw(ports), 11 ],
[qw(luns), 11, 'text' ],
);
-----Original Message-----
From: spreadsheet...@googlegroups.com
[mailto:spreadsheet...@googlegroups.com] On Behalf Of jmcnamara
Sent: Thursday, 6 December 2007 9:49 AM
To: Spreadsheet::WriteExcel
Subject: [Spreadsheet::WriteExcel] Re: Change the cell format to "text"
instead of "general"
Fúlvio
About the question bellow:
1 - Unfortunately I'm not able to test right now but can't you just
the apostrophe was showed in the excel file.
What I understood is that I need to change my code to use write_string
instead of write_row, because
isn't possible to use addformat(...) with write_row to indicate that
write_row is writing a string or to
indicate that the cells must be in "text format" instead of "general
format" is it right?
Fúlvio
jmcnamara
On Dec 6, 11:44 am, "Fúlvio" <fulvi...@gmail.com> wrote:
> What I understood is that I need to change my code to use write_string
> instead of write_row, because
> isn't possible to use addformat(...) with write_row to indicate that
> write_row is writing a string or to
> indicate that the cells must be in "text format" instead of "general
> format" is it right?
Yes, that is right.
The write_row() method is just a convenient wrapper around the write()
method which is in itself a wrapper around write_number(),
write_string() and some other methods.
Therefore, if you need more control over the data that you are writing
then you should unroll the write_row() loop and use your own loop to
write the data in the way that you want.
This is what Michael Wardman was demonstrating above.
John.
--
Fúlvio
Thanks for all help.
Fúlvio