Writing to a Parsed Excel Spreadsheet

[Pam]

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:arseExcel 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:arseExcell info I have gathered?



Thank You,
Pamela

 

[J. Gleixner]

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:arseExcel 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:arseExcell 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]

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:arseExcel;
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:arseExcel::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