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

[hclo...@gmail.com]

[»]
by Harish Lodwal - Nov 18th 2005 02:01:39

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;
}

--
Regards Harish

[jmcnamara]

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

Hi,

You aren't doing anything wrong in your code.

The problem is that references to external worksheets like this aren't
supported by Spreadsheet::WriteExcel.

They are supported in Spreadsheet::WriteExcelXML if that is any use.

John.
--