Re: Adding Excel sheet

[ickyb0d]

Whenever i've used the Spreadsheet module, i also found it hard to create an Excel file from scratch, since the module doesn't handle formatting too well. What i ended up doing was just creating my template and formatting in windows, then loading the template using Spreadsheet::ParseExcel. After that it's just a matter of writing and saving it using WriteExcel and SaveParser. Here are some snippets from some code i had laying around. Hope this helps.

#excel modules
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

#loading template
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('timesheets/old_template.xls')
        or die "Unable to open template";
my $temp_workbook;

#adding info...
#user name
setCellFormat(4,2);
$template->AddCell(0, 4, 2, "$real_name", $format);

#project name
setCellFormat(5,2);
$template->AddCell(0, 5, 2, "$project_name", $format);

#save data
my $workbook;

{       #ignore SaveAs errors and warnings
        local $^W = 0;
        $workbook = $template->SaveAs('/home/~myname/httpdocs/spreadsh
+eet.xls');
}

[Fuism]

I would like to thank everyone here for their help.... Alot of good information. I post if I have more infor, I think this is a great start for me. Thanks again all...

[Fuism]

This works great except for one thing.... AddCell just adds a cell, I need it to replace the cell rather than AddCell, I would need something like 'EditCell'. WriteExcel wont be able to write to this. How would you edit the cell? Is it possible to delete the cell and replace it with 'AddCell'?

[Trix606]

Looking at your original post, I'm a little confused as to why you need to edit a cell. If you use Spreadsheet::ParseExcel::SaveParser you load in your existing spreadsheet then create your new worksheet:

$oBook->AddWorksheet('February');

To this new blank worksheet first add the cells from the prior month's worksheet that are the same in the new month's worksheet for example:

my $oOld_Sheet = $oBook->{Worksheet}[0];
for (my $row = 0; $row <= 4; $row++) {
    my $oCell = $oOld_Sheet->{Cells}[$row][0];
    if (defined $oCell->{Val}) {
        $oBook->AddCell(1, $row, 0, $oCell->{Val}, $oCell);
     }
 }

Adding the sales for the new month to the new sheet using the AddCell method as well. Finally save the workbook under a new name using the SaveAs method.

If you really need to edit a cell then Win32::OLE as InfiniteSilence and davidrw mentioned is probably what you will have to use, also if the worksheet you are adding contains formulas.

[Fuism]

Yes the worksheets contain formulas. For the new worksheet, I need everything to be the same as the previous(format, etc). I just need to replace most of the numbers copied over from the previous worksheet with the numbers from the current month. I will try Win32::OLE.. Thanks again...