Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Changing cell property in Excel through a module

by prasadbabu (Prior)
on Sep 21, 2005 at 05:58 UTC ( [id://493665]=perlquestion: print w/replies, xml ) Need Help??

prasadbabu has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I am using Spreadsheet::WriteExcel for some data generation.

In the first column of the Excel sheet i need the numbers with 5 digits. When i generated that using sprintf function, it is not generating as 5 digit.

Anyhow i accomplished my job by just converting it into a string by concatenating a space, rather a number. I know this is not the proper way. Is there anyother way to do it.

We can do manually by just changing the cell property, number to text. But by using Spreadsheet::WriteExcel i cannot able to change. Is there a propery in this module to do that, or is it not possible to change by this module?

This is the code i tried.

use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("PIIgenerator2.xls"); my $isbn = $ARGV[0]; my $worksheet = $workbook->add_worksheet("Revises"); my %font = ( -font => 'Arial', -size => 12, -color => 'black', -bold => 1, ); my $format = $workbook->add_format(%font); $worksheet->write('A1', "ISBN"); $worksheet->write('B1', "$isbn"); $worksheet->write('A2', "Article ID", $format); $worksheet->write('B2', "ISBN", $format); $worksheet->write('C2', "PII", $format); $worksheet->write('D2', "Comments", $format); #my $format1 = $workbook->set_num_format(); my $s; for $a (3..10) { $s = sprintf("%05d",($a-2)); my $s1 = "$s".' '; $worksheet->write("A$a", "$s1"); }

Thanks in advance


Replies are listed 'Best First'.
Re: Changing cell property in Excel through a module
by gopalr (Priest) on Sep 21, 2005 at 06:41 UTC
Re: Changing cell property in Excel through a module
by jmcnamara (Monsignor) on Sep 21, 2005 at 08:12 UTC

    The best way to do this is to use an Excel number format instead of sprintf. That way if the number is changed by the user the format will stay the same.

    In this case the number format that you need is '00000'.

    ... my $isbn_format = $workbook->add_format(num_format => '00000'); for my $row (3..10) { $worksheet->write($row -1, 0, $row, $isbn_format); }

    See the set_num_format() section of the docs as well as the keep_leading_zeros() section (as suggested by runrig above) for some background information and this for an introduction to Excel's number formats.


Re: Changing cell property in Excel through a module
by runrig (Abbot) on Sep 21, 2005 at 06:05 UTC
    See the keep_leading_zeros section of the docs for several ways to keep the leading zeros.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://493665]
Approved by gopalr
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (4)
As of 2024-04-16 17:46 GMT
Find Nodes?
    Voting Booth?

    No recent polls found