Re: Saveparser: Add Formula

[sonal gupta]

Can anyone please reply to my post. I really need the answer as I am stuck here.

Regards,

Sonal

On Wednesday, November 7, 2012 11:07:34 AM UTC+5:30, sonal gupta wrote:

Hello,

What is the format to write formula to a spreadsheet being edited by Saveparser package.

--

Sonal

[jmcnamara]

On Nov 7, 5:37 am, sonal gupta <guptasonal1...@gmail.com> wrote:
> Hello,
>
> What is the format to write formula to a spreadsheet being edited by
> Saveparser package.

Hi,

As stated in the documentation, it isn't possible to rewrite a formula
in an Excel file using SaveParser due to the format of formulas in an
xls file and due to limitations of the module.

You can write a formula again by getting a reference to the underlying
Spreadsheet::WriteExcel module and using that to write a new formula.
Something like this.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

# Open the template with SaveParser
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse( 'template.xls' );

my $sheet = $template->worksheet( 0 );
my $row = 0;
my $col = 0;


# Get the format form cell A1.
my $cell = $sheet->get_cell( 0, 0 );
my $format_number = $cell->{FormatNo};


# Write data to some cells
$template->AddCell( 0, $row, $col, 1, $format_number );
$template->AddCell( 0, $row + 1, $col, "Hello", $format_number );

# Add a new worksheet
$template->AddWorksheet( 'New Data' );

# The SaveParser SaveAs() method returns a reference to a
# Spreadsheet::WriteExcel object. If you wish you can then
# use this to access any of the methods that aren't
# available from the SaveParser object. If you don't need
# to do this just use SaveAs().
#
my $workbook = $template->SaveAs( 'new.xls' );

# Use Spreadsheet::WriteExcel methods
my $worksheet = $workbook->sheets( 0 );

$worksheet->write( $row + 2, $col, "World2" );
$worksheet->write( 'D3', '=1+2' );

$workbook->close();

[sonal gupta]

Hello,

I am using the following script to write a formula using the saveparser package

    $wsw1->AddCell($j,9,'=VLOOKUP(B'.$y.',\'Module Paths\'!$A$3:B$225,2,FALSE)',$formatb);

    $wsw1->AddCell($j,10,'=VLOOKUP(C'.$y.',\'Module Pin Name\'!$B$2:$M$182,2,FALSE)',$formatb);

In the first one, it writes the formula while in second one it deletes all the sheets after wsw1 sheet and gives an error that the sheet "Module Pin Name" was not found. This error did not happen when I moved the sheet   "Module Pin Name" before wsw1 sheet.

Is this a bug or expected behaviour? What is the work around if I dont want to move the sheet  "Module Pin Name" anywhere? Also, as per my understanding, it should not parse VLOOKUP and just write it as a text, why is it operating on it?

Regards,

Sonal

--
--
You received this message because you are subscribed to the Spreadsheet::ParseExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-parseexcel?hl=en

--
Regards
Sonal Gupta

[jmcnamara]

On Nov 28, 9:29 am, sonal gupta <guptasonal1...@gmail.com> wrote:
> Hello,
>

> I am using the following script to write a formula using the saveparser
> package
>
>     $wsw1->AddCell($j,9,'=VLOOKUP(B'.$y.',\'Module
> Paths\'!$A$3:B$225,2,FALSE)',$formatb);
>     $wsw1->AddCell($j,10,'=VLOOKUP(C'.$y.',\'Module Pin
> Name\'!$B$2:$M$182,2,FALSE)',$formatb);
>
> In the first one, it writes the formula while in second one it deletes all
> the sheets after wsw1 sheet and gives an error that the sheet "Module Pin
> Name" was not found. This error did not happen when I moved the sheet
>   "Module Pin Name" before wsw1 sheet.

Hi,

That is unfortunately due to the fact that the underlying
Spreadsheet::WriteExcel cannot parse a formula that refers to a
worksheet that hasn't been created yet since it has to map the
worksheet name to an internal index as required by Excel.

In Spreadsheet::WriteExcel programs this isn't generally an issue
since you usually create the worksheets at the start of the program
but due to the logic of SaveParser it doesn't behave like that.

I'm not sure you can workaround that with SaveParser. You may have to
read the data from the existing file with ParseExcel and then rewrite
it with WriteExcel (which is essentially what SaveParser is doing).

Regards,

John.
--