I am using the Perl Spreadsheet::WriteExcel module to create an .xls spreadsheet (I know about Excel::Writer::XLSX, but I need to retain the .xls format for backwards compatibility), and some cells are not being written correctly. There are a few cells which have values of the form 1E##, which Excel interprets as scientific notation by default. To fix this, I'm using set_num_format('@') to force the format of the cell to plain text, but it isn't working. If I look at the cell format in Excel once the workbook is generated, it has been set to Text as I expected, but the values have still been changed to numbers of the form 1E+##.
Declaration of the formats being used:
my $formatRS = $workbook->add_format();
$formatRS->set_align('right');
$formatRS->set_align('top');
$formatRS->set_text_wrap();
$formatRS->set_size(8);
$formatRS->set_num_format('@');
my $formatLS = $workbook->add_format();
$formatLS->set_align('left');
$formatLS->set_align('top');
$formatLS->set_text_wrap();
$formatLS->set_size(8);
$formatLS->set_num_format('@');
Writing the data:
$worksheet2->write($row, 0, "$Eq->[0]", $formatRS);
$worksheet2->write($row, 1, "$Eq->[1]", $formatLS);
$worksheet2->write($row, 2, "$Eq->[2]", $formatLS);
Breaking at the point where the data is written shows that the @Eq array still contains the right values, and the worksheet is not modified afterwards. Is there any way to stop the data from being mangled like this?
Edit: Wrote up a quick script to demonstrate the problem. This creates a workbook with three cells. The first value is preserved, but the second and third are converted to numbers despite the cells being formatted as Text.
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("Workbook_Test.xls");
my $format = $workbook->add_format();
$format->set_num_format('@');
my $worksheet1 = $workbook->add_worksheet('sheet');
$worksheet1->write(0, 0, "1E10B", $format);
$worksheet1->write(0, 1, "1E37", $format);
$worksheet1->write(0, 2, "1E10", $format);