Spreadsheet::WriteExcel

Discussion in 'Perl Misc' started by Sean Berry, May 8, 2004.

  1. Sean Berry

    Sean Berry Guest

    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, May 8, 2004
    #1
    1. Advertising

  2. Sean Berry

    Sean Berry Guest

    Spreadsheet::parseExcel::SaveParser;

    Found it.


    "Sean Berry" <> wrote in message
    news:ODWmc.85741$Jy3.46975@fed1read03...
    > 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, May 8, 2004
    #2
    1. Advertising

  3. Sean Berry

    Bob Walton Guest

    Sean Berry wrote:

    > 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::parseExcel 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::parseExcel::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.


    ....


    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, May 8, 2004
    #3
  4. Sean Berry

    Sean Berry Guest

    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" <> wrote in message
    news:...
    > Sean Berry wrote:
    >
    > > 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::parseExcel 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::parseExcel::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.
    >
    >
    > ...
    >
    >
    > --
    > Bob Walton
    > Email: http://bwalton.com/cgi-bin/emailbob.pl
    >
     
    Sean Berry, May 8, 2004
    #4
  5. Sean Berry

    Bob Walton Guest

    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?


    ....

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
     
    Bob Walton, May 8, 2004
    #5
  6. Sean Berry

    Sean Berry Guest

    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.


    "Bob Walton" <> wrote in message
    news:...
    > 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?
    >
    >
    > ...
    >
    > --
    > Bob Walton
    > Email: http://bwalton.com/cgi-bin/emailbob.pl
    >
     
    Sean Berry, May 8, 2004
    #6
  7. Sean Berry

    Sean Berry Guest

    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

    "Sean Berry" <> wrote in message
    news:ODWmc.85741$Jy3.46975@fed1read03...
    > 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, May 8, 2004
    #7
  8. Sean Berry wrote:

    > 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.
    --
     
    John McNamara, May 9, 2004
    #8
  9. Sean Berry

    Sean Berry Guest

    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::parseExcel::SaveParser;

    my $parser = new Spreadsheet::parseExcel::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.



    "John McNamara" <> wrote in message
    news:...
    > Sean Berry wrote:
    >
    > > 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, May 9, 2004
    #9
  10. Sean Berry

    Sean Berry Guest

    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::parseExcel?


    "John McNamara" <> wrote in message
    news:...
    > Sean Berry wrote:
    >
    > > 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, May 9, 2004
    #10
  11. Sean Berry

    Sean Berry Guest

    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

    "John McNamara" <> wrote in message
    news:...
    > Sean Berry wrote:
    >
    > > 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, May 9, 2004
    #11
  12. Sean Berry

    Sean Berry Guest

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

    Sorry


    "Sean Berry" <> wrote in message
    news:ODWmc.85741$Jy3.46975@fed1read03...
    > 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, May 9, 2004
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fabio

    Spreadsheet::WriteExcel

    Fabio, Mar 2, 2004, in forum: Perl
    Replies:
    2
    Views:
    889
    Tom Niesytto
    Mar 4, 2004
  2. Erica
    Replies:
    2
    Views:
    110
    John M. Gamble
    Sep 5, 2003
  3. Sven Jungnickel
    Replies:
    3
    Views:
    207
    John McNamara
    Jul 29, 2004
  4. Zhidian Du
    Replies:
    1
    Views:
    139
    A. Sinan Unur
    Dec 11, 2003
  5. A Ymous

    Query about Spreadsheet::WriteExcel

    A Ymous, Sep 10, 2004, in forum: Perl Misc
    Replies:
    1
    Views:
    119
    Carl Inglis
    Dec 2, 2004
Loading...

Share This Page