PERL Miscellaneous - Writing to a Parsed Excel Spreadsheet

Author

Writing to a Parsed Excel Spreadsheet

Pam

2006-09-29, 6:59 pm

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

J. Gleixner

2006-09-29, 6:59 pm

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.

Pam

2006-09-29, 6:59 pm

J. Gleixner wrote:
> Pam wrote:
>
> 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.
>
>
>
> 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

Jim Gibson

2006-09-29, 6:59 pm

In article <1159554463.928320.113600@i42g2000cwa.googlegroups.com>, Pam
<pamelapdh@aol.com> wrote:

> J. Gleixner wrote:
>
>
> 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.

Spreadsheet::ParseExcel reads spreadsheets and Spreadsheet::WriteExcel
writes them. Neither can be used to update an existing spreadsheet.
However, check out the Spreadsheet::ParseExcel::SaveParser module. It
purports to allow you to write data to an existing spreadsheet. I have
not used it and cannot comment on it.

You could also save the spreadsheet data in a two-dimensional array as
you write it, or just add a true value to a 2D array for each
spreadsheet cell you create. That way, after you have completed writing
the new spreadsheet, you can check the content of any spreadsheet cell
by checking the content of the array. In particular, you can ascertain
which cells have been written to and which have not.

--
Jim Gibson

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

Sponsored Links