Spreadsheet::WriteExcel

[Sean Berry]

Is there a way of opening a preformatted excel file, then write to empty
cells in the table.

I have a table with various formulas, cell and text colors, borders, etc.
But, the data is in a 28 x 170 sized chunk of the sheet. This will be a big
pain to code so I was looking for a shortcut.

Is is possible to parse the .xml file and use the values within instead of
just the data?

TIA

 

[Sean Berry]

Spreadsheet:arseExcel::SaveParser;

Found it.

 

[Bob Walton]

Is there a way of opening a preformatted excel file, then write to empty
cells in the table.

I have a table with various formulas, cell and text colors, borders, etc.
But, the data is in a 28 x 170 sized chunk of the sheet. This will be a big
pain to code so I was looking for a shortcut.

Well, one way would be to use Excel itself via the Win32::OLE module.
Another way might be to use the Spreadsheet:arseExcel module to read
the "preformatted Excel file", and then use Spreadsheet::WriteExcel to
write a new Excel file. Another way would be with the
Spreadsheet:arseExcel::SaveParser module, which will do a
read-modify-write. What will give the greatest mileage in the long run?
Probably Win32::OLE.

Is is possible to parse the .xml file and use the values within instead of
just the data?

What .xml file? Do you mean .xls ? Or are you referring to
Spreadsheet::WriteExcel::FromXML (if so, how do you get the XML file?)?
And what do you mean by "values within" versus "just the data"? What
you're trying to say isn't at all clear to me. If you want to parse an
XML file, check CPAN for modules with XML and Parse in their names.


....

 

[Sean Berry]

This is for a report that will run once a month so mileage isn't really an
issue.

However, I tried a simple insert of one cell on a preformatted spreadsheet
and it gave me the following error:

Can't locate object method "set_print_scale" via package
"Spreadsheet::WriteExcel::Worksheet" at
/usr/local/lib/perl5/site_perl/5.005/Spreadsheet/ParseExcel/SaveParser.pm
line 125.

I am using perl 5.005. The module was already installed by the sytsem
admin, so what is the problem. Do I need a more recent version of the
SaveParser module???

Thanks in advance.

 

[Bob Walton]

Sean Berry wrote:

....

However, I tried a simple insert of one cell on a preformatted spreadsheet
and it gave me the following error:

Can't locate object method "set_print_scale" via package
"Spreadsheet::WriteExcel::Worksheet" at
/usr/local/lib/perl5/site_perl/5.005/Spreadsheet/ParseExcel/SaveParser.pm
line 125.

I am using perl 5.005. The module was already installed by the sytsem

admin, so what is the problem. Do I need a more recent version of the
SaveParser module???

Do yourself and everyone else a huge favor and upgrade to a Perl that
isn't years old. With that version of Perl, you probably need an
*older* version of SaveParser, if there even was such a thing back then.

Question: Are you using the docs that go with the versions of Perl and
the various modules you are using?


....

 

[Sean Berry]

I would upgrade, bit there would be too many issues to deal with if I did.
For instance: the IO::Socket module is not backwards compatable, so any
program using it (lots of them) would be broken. The system admin already
had the SaveParser installed so I am almost certain that it should work.
The file is datad July of 2002. I am able to use the Parser and WriteExcel
mods with no problems.

 

[Sean Berry]

I got by my previous error. But now I have a new problem.

If I ever try to write past the 8th column, the xls file becomes corrupted
and will not open. I can fill cells in columns up to 8, but as soon as I
try to add a cell to column 8, whamo.

Any ideas here?

TIA

 

[John McNamara]

If I ever try to write past the 8th column, the xls file becomes corrupted
and will not open. I can fill cells in columns up to 8, but as soon as I
try to add a cell to column 8, whamo.

This type of problem usually indicates that utf8 strings are being
written to the file. This causes some other internal strings to be
coerced to utf8 and thus the binary data get corrupted.

See the "Working with XML" section of the Spreadsheet::WriteExcel
docs:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/WriteExcel.pm#WORKING_WITH_XML

Let me know if this isn't the cause of the problem.

John.
--

 

[Sean Berry]

I don't think this is my problem. I am not parsing an XML file. Here is
the code in question.

----------------start code---------------------

#!/usr/bin/perl -w
use strict;
use Spreadsheet:arseExcel::SaveParser;

my $parser = new Spreadsheet:arseExcel::SaveParser;
my $template = $parser->Parse('summary.xls');

my $sheet = 0;
my $row = 3;
my $col = 1;
my $format =
$template->{Worksheet}[$sheet]->{Cells}[$row][$col]->{FormatNo};
$template->AddCell(0, $row, $col, "=SUM(C4:N4)", $format);
$col += 1;

## Add values to columns 1-6 and rows 3-16
while ($col < 7) {
while ($row < 16) {
$template->AddCell(0, $row, $col, "505", $format);
$row += 1;
}
$row = 3;
$col += 1;
}

## Add single cell value for testing. < Works...
$template->AddCell(0, 9, 7, "606", $format);
$workbook = $template->SaveAs('book1.xls');

-----------------end code----------------------------

If I change to line "while ($col < 7)" to any number higher than 7 I get the
error.

Later, when I get this code to work, I will be parsing a tab delimited txt
file and
adding the data from that file to this formatted excel spreadsheet.

Any help is appreciated a great deal. I am trying to get this project done
by this
Tuesday so that I can start automating this task.

Thanks.

 

[Sean Berry]

Apparently this has to do with the formatting of my existing excel file.

I removed all of the colors and other formatting from the spreadsheet and it
works no problem. Any known issues about some excel formatting options?

Would it be more reasonable to parse a file and write to a new file instead
using
Spreadsheet::WriteExcel and SpreadSheet:arseExcel?

 

[Sean Berry]

I reformatted the xls file starting with a brand new spreadsheet and making
it the exact same as what I had before, and it works with no errors. The
only problem now, not really a problem, is that it messes up the text
allignment within the cells. Some things are left justified, others are
right...

Any ideas here?

TIA

 

[Sean Berry]

Error in my original post... not xml file... XLS file.

Sorry