Writing a number to a cell using Spreadsheet::WriteExcel

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

One of the columns in my database contains an id which is 8 digits long
It can take the form

What I want is for the leading zeros to be maintained. So I created a format
using the set_num_format method and passing '@' as a parameter. The code is as follows

my $workbook = Spreadsheet::WriteExcel->new($file);
    my $wb = $workbook->addworksheet('Current Students');
    my $format = $workbook->addformat();
    $format->set_num_format('@');
    my $i = 0;
    while (my @row = $sth->fetchrow_array())
    {
        my $j = 0;
        foreach my $item (@row)
        {
            if ($j == 0)            {
                $wb->write($i, $j, $item, $format);
            }
            else
            {
                $item = lc($item);
                $wb->write($i, $j, &capitalize($item));
            }
            $j++;
        }
        $i++;
    }
[download]

When I look at the spreadsheet in M$ Excel the column contains the following

99000123
98001234
50
1000123
2003445
[download]

All the leading zeros have gone. Please can someone tell me what I am missing.
I been right through the documentation, that's where I found the '@' parameter.

Thanks

Comment on Writing a number to a cell using Spreadsheet::WriteExcel Select or Download Code

Replies are listed 'Best First'.
Re: Writing a number to a cell using Spreadsheet::WriteExcel by rdfield (Priest) on Oct 25, 2002 at 10:09 UTC
Try the format '00000000'. rdfield	[reply]
Re: Re: Writing a number to a cell using Spreadsheet::WriteExcel by ColtsFoot (Chaplain) on Oct 25, 2002 at 10:16 UTC
rdfield, Your a star, $format->set_num_format('00000000'); worked just fine Did I miss it in the doc ?	[reply]
Re: Re: Re: Writing a number to a cell using Spreadsheet::WriteExcel by rdfield (Priest) on Oct 25, 2002 at 10:46 UTC
You're welcome. The format was derived directly from the examples in Excel's "Format Cells..." dialog. rdfield	[reply]
Re: Writing a number to a cell using Spreadsheet::WriteExcel by jmcnamara (Monsignor) on Oct 25, 2002 at 14:20 UTC
The best way to deal with this is to use a "00000000" number format, as shown by rdfield. ++ There are also other methods and other issues however. These are discussed in more recent versions of the Spreadsheet::WriteExcel documentation in the section keep_leading_zeros(). -- John.	[reply]
Re: Writing a number to a cell using Spreadsheet::WriteExcel by sch (Pilgrim) on Oct 25, 2002 at 10:06 UTC
Can't say that I know the module but it looks as if it's just taking the number without applying the formatting. You could try sprintf - something like: `$wb->write($i, $j, sprintf("%08d", $item), $format);` [download] UPDATE: I was going to say try a format of "00000000", but rdfield beat me to it Hey, if there's code up ^^ there ^^, don't blame me if it doesn't work. But today you took me walking, Through a land that we have lost, While our children sit at websites, With no access to the cost	[reply] [d/l]