[»]
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
