Writing to a Parsed Excel Spreadsheet

Discussion in 'Perl Misc' started by Pam, Sep 29, 2006.

  1. Pam

    Pam Guest

    Hello:

    Here is a good one I wonder if anyone can answer.


    I am getting information from a data base, the response is put into a
    comma seperated file. I then use Text::CSV_XS to put that comma
    seperated info into a spreadsheet by using Spreadsheet::WriteExcel.
    This allows me to create a workbook and a worksheet which I can
    format and write to.

    I then parse that spreadsheet using Spreadsheet::parseExcel to
    check for empty cells which work fine, but one thing I must do
    before I can parse the file is close the file. This is because I am
    writing to the same file in which I am trying to parse.


    Since I have closed the spreadsheet I wish to write to I seemed to have
    lost my scope. Meaning the information I got from the parse now needs
    to
    be put into the spreadsheet.

    If the cell is empty I want to write to it. Is it possible to write
    using
    Spreadsheet::parseExcell info I have gathered?



    Thank You,
    Pamela
    Pam, Sep 29, 2006
    #1
    1. Advertising

  2. Pam

    J. Gleixner Guest

    Pam wrote:
    > Hello:
    >
    > Here is a good one I wonder if anyone can answer.
    >
    >
    > I am getting information from a data base, the response is put into a
    > comma seperated file. I then use Text::CSV_XS to put that comma
    > seperated info into a spreadsheet by using Spreadsheet::WriteExcel.
    > This allows me to create a workbook and a worksheet which I can
    > format and write to.
    >
    > I then parse that spreadsheet using Spreadsheet::parseExcel to
    > check for empty cells which work fine, but one thing I must do
    > before I can parse the file is close the file. This is because I am
    > writing to the same file in which I am trying to parse.


    Why not do all of your "empty cell" checks as you write it, correcting
    them as you go? It doesn't really make sense to write it, then open it,
    parse it, and write it again.


    > Since I have closed the spreadsheet I wish to write to I seemed to have
    > lost my scope. Meaning the information I got from the parse now needs
    > to
    > be put into the spreadsheet.
    >
    > If the cell is empty I want to write to it. Is it possible to write
    > using
    > Spreadsheet::parseExcell info I have gathered?


    If you must, then parse it again, write it to a new XLS file, and rename
    it when you're finished.
    J. Gleixner, Sep 29, 2006
    #2
    1. Advertising

  3. Pam

    Pam Guest

    J. Gleixner wrote:
    > Pam wrote:
    > > Hello:
    > >
    > > Here is a good one I wonder if anyone can answer.
    > >
    > >
    > > I am getting information from a data base, the response is put into a
    > > comma seperated file. I then use Text::CSV_XS to put that comma
    > > seperated info into a spreadsheet by using Spreadsheet::WriteExcel.
    > > This allows me to create a workbook and a worksheet which I can
    > > format and write to.
    > >
    > > I then parse that spreadsheet using Spreadsheet::parseExcel to
    > > check for empty cells which work fine, but one thing I must do
    > > before I can parse the file is close the file. This is because I am
    > > writing to the same file in which I am trying to parse.

    >
    > Why not do all of your "empty cell" checks as you write it, correcting
    > them as you go? It doesn't really make sense to write it, then open it,
    > parse it, and write it again.
    >
    >
    > > Since I have closed the spreadsheet I wish to write to I seemed to have
    > > lost my scope. Meaning the information I got from the parse now needs
    > > to
    > > be put into the spreadsheet.
    > >
    > > If the cell is empty I want to write to it. Is it possible to write
    > > using
    > > Spreadsheet::parseExcell info I have gathered?

    >
    > If you must, then parse it again, write it to a new XLS file, and rename
    > it when you're finished.



    Hi:

    Hmmm that is an idea, but this requirement was given to me after I did
    the writing
    and formating. I have a version of the script already running on a
    cron job
    I was adding more requirements and wanted to make every attempt not
    have to
    do a lot of modification to the code. The modules I am using are:

    use strict;
    use warnings;
    use Spreadsheet::parseExcel;
    use Spreadsheet::WriteExcel;
    use Text::CSV_XS;


    I'm not aware of a way to check for a undef vlaue with out parsing it.
    The way
    it goes is I have to add the columns and formating first before I am
    able to check it if is
    empty. Logically it would make more sense to do it that way but my
    requirments
    don't allow me to.


    I have tow questions:

    Can you tell me how you would create a spreadsheet write to it and
    parse it
    (check for empty cell) at same tiem.


    Can you tell me if it is possible to write to a parse file. Would I
    write to a sheet
    or to the work book/


    #tThis is what I am doing now
    # Create a new Excel workbook
    my $workbook = Spreadsheet::WriteExcel->new("filename.xls");
    my $worksheet = $workbook->add_worksheet();


    # Create a new CSV parsing object
    my $csv = Text::CSV_XS->new;

    # Row and column are zero indexed
    my $row = 0;
    my $total;
    my $count;

    while (<CSVFILE>) {
    if ($csv->parse($_)) {
    my @Fld = $csv->fields;

    my $col = 0;
    foreach my $token (@Fld) {
    $worksheet->write($row, $col, $token, $format3);
    $col++;
    }
    $row++;
    if ($row > 1){
    $count = $count + 1;

    $total = $count;

    }

    }
    else {
    my $err = $csv->error_input;
    print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
    }

    }
    print "Adding sheet1\n";




    This is how I am writing to the existing spreadsheet.

    # Add a Format
    my $format = $workbook->add_format();
    #must set wrap for CCB comments and Description
    $format->set_text_wrap();
    $format->set_bold();
    $format->set_bg_color('51');
    $format->set_border();
    $format->set_bottom();
    $format->set_top();
    $format->set_left();
    $format->set_right();



    $worksheet->set_column(0, 0, 11);
    $worksheet->write(0, $col, "Identifier", $format,);
    $worksheet->write(0, 1, "Team Comments", $format,);
    $worksheet->write(0, 2, "Description", $format);
    $worksheet->write(0, 3, "Status", $format);
    $worksheet->write(0, 4, "Severity", $format);
    $worksheet->write(0, 5, "Priority", $format);
    $worksheet->write(0, 6, "CCBComments_encl", $format);
    $worksheet->write(0, 7, "Primary-feature-team", $format);
    $worksheet->write(0, 8, "Sub-feature-team", $format);
    $worksheet->write(0, 9, "Project", $format);
    $worksheet->write(0, 10,"Product", $format);
    $worksheet->write(0, 11,"Products-targeted", $format);
    $worksheet->write(0, 12,"Products-targed_del", $format);
    $worksheet->write(0, 13,"Products-targetd_add", $format);


    #This is the parser
    my $oBook = Spreadsheet::parseExcel::Workbook->Parse("filename.xls");

    my($iR, $iC, $oWkS, $oWkC);
    foreach my $oWkS (@{$oBook->{Worksheet}}) {
    print "--------- SHEET:", $oWkS->{Name}, "\n";
    for(my $iR = $oWkS->{MinRow} ;
    defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
    $iR++) {
    for(my $iC = 11 ; $iC <=11; $iC++) {

    if ( $oWkS->{Cells}[$iR][$iC]->Value != defined || warn "No
    Data $iR"){


    #Need to add something like this
    # $worksheet->write($iR, $iC, "Platform",
    $format2);

    } else{

    $oWkC = $oWkS->{Cells}[$iR][$iC];


    print "( $iR , $iC ) =>", $oWkC->Value, "\n" if(
    $oWkC);

    }


    Then close the book and rename it is the simplest way I know
    I am only looking to write to one column which is column 11.

    Thanks,
    Pamela
    Pam, Sep 29, 2006
    #3
    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. FSD
    Replies:
    0
    Views:
    1,188
  2.  James
    Replies:
    0
    Views:
    167
    James
    May 19, 2011
  3. Will James
    Replies:
    22
    Views:
    626
    Will James
    May 26, 2011
  4. FSD

    Writing to an Excel Spreadsheet

    FSD, Feb 19, 2005, in forum: Perl Misc
    Replies:
    1
    Views:
    110
    Steven Kuo
    Feb 19, 2005
  5. linearfusion
    Replies:
    2
    Views:
    121
    linearfusion
    Jun 27, 2006
Loading...

Share This Page