Spreadsheet::WriteExcel

Spreadsheet::WriteExcel update
Just a reminder that questions and discussions in relation to Spreadsheet::WriteExcel have moved to Google Groups.

groups.google.com/grou... (groups.google.com/grou...)

Release and other announcements will still continue here.

Also, if you'd care to donate to the Spreadsheet::WriteExcel project, you can now do so via PayPal:

tinyurl.com/7ayes (tinyurl.com/7ayes)

John.

--

Re: External link not working in sreadshee point to workseet genrated using WriteExcel

> when we try to use our genrated

> spreadsheet as extrenal link to other

> workbook using Excel 2002

See the response (groups.google.com/grou...) on the Google-Groups Spreadsheet::WriteExcel forum.

groups.google.com/grou...

John.

--

External link not working in sreadshee point to workseet genrated using WriteExcel
We are using Spreadsheet::WriteExcel(3) version 2.12 and perl version 5.8.6 to genrate spread sheet

when we try to use our genrated spreadsheet as extrenal link to other workbook using Excel 2002

=VLOOKUP("abc",'[XYZ.xls]Margin summary detail'!$F:$M,8,0)/1000000

It will show as #N/A

but it will start working fine after we will open the genrated file and it will work fine once we open the genrated file

part of the code we use to genrate excell file is

sub create_excel {

my $out_file_path = shift;

my $csv_data = shift;

# Create a new Excel workbook

if (not defined($out_file_path) or $out_file_path =~ /(\{|\}|\$)/) {

error "File path is not valid:";

error $out_file_path if (defined $out_file_path);

return FALSE;

}

my $dirName = dirname($out_file_path);

Deshaw::GBO::Util::Mkdir($dirName);

my $workbook = Spreadsheet::WriteExcel->new($out_file_path);

if (not defined $workbook) {

error "Failed to create workbook";

return FALSE;

}

# total margin summary detail.

my $worksheet = $workbook->add_worksheet('Margin summary detail');

# Add and define formats

my $heading_format = $workbook->add_format(); # Add a format for heading

# Format the header

$heading_format->set_align('center');

$heading_format->set_text_wrap();

#$heading_format->set_bold();

# Create default formats

my $string_format = $workbook->addformat(num_format => '@', align => 'left');

my $int_format = $workbook->addformat(num_format => '#,##0;(#,##0);-', align => 'right');

my $float_format = $workbook->addformat(num_format => '#,##0.00;(#,##0.00);-', align => 'right');

our %column_format = (

0 => $string_format,

6 => $int_format,

7 => $int_format,

8 => $int_format,

9 => $float_format,

10 => $int_format,

11 => $int_format,

12 => $int_format,

13 => $string_format,

);

my $csv = Text::CSV->new();

my $row_count = 0;

my $line;

my $fh;

# Set the first row height to 33

$worksheet->set_row(0, 33);

# Set the column width to 15

$worksheet->set_column('F:Z', 15);

my @csv_arr = split("\n",$csv_data);

foreach my $line (@csv_arr) {

$line =~ s/\n|\r//g;

if ($csv->parse($line)) {

my @field_arr = $csv->fields;

my $cols = scalar(@field_arr);

my $column_count = 0;

while($column_count < $cols ) {

my $col_format = $column_format{$column_count}

unless($cols < 2);

$col_format = $heading_format if($row_count == 0);

debug($line);

$worksheet->write($row_count, $column_count,

$field_arr[$column_count],

$col_format);

$column_count++;

$col_format = undef;

}

}

$row_count += 1;

}

$workbook->close();

return TRUE;

}

Spreadsheet::WriteExcel Pre-release with Cell Comments
A pre-release of the next version of Spreadsheet::WriteExcel with support for cell comments is now available. See here (groups.google.com/grou...) for details.

John.

--

Spreadsheet::WriteExcel update
Just a reminder that questions and discussions in relation to Spreadsheet::WriteExcel have moved to Google Groups (groups.google.com/grou...).

Release and other announcements will still continue here.

Also, if you'd care to donate to the Spreadsheet::WriteExcel project, you can now do so via PayPal: here (tinyurl.com/7ayes).

John.

--