About:
Spreadsheet::WriteExcel is a Perl module which can be used to create native Excel binary files. Formatted text and numbers can be written to multiple worksheets in a workbook. Formulas and functions are also supported. It is 100% Perl and doesn't require any Windows libraries or a copy of Excel. It will also work on the majority of Unix and Macintosh platforms. Generated files are compatible with Excel 97, 2000, 2002, and 2003, and with OpenOffice and Gnumeric. An older version also supports Excel 5/95.

Release focus: Major bugfixes

Changes:
This version provides greater compatibility with third party applications that read Excel files, such as Apache POI.

Author:
John McNamara [contact developer]

Rating:	reset 1 2 3 4 5 6 7 8 9 10
8.67/10.00 (77 votes)

Homepage:
http://search.cpan.org/search?dist=Spreadsheet-WriteExcel
Tar/GZ:
http://www.cpan.org/[..]AMARA/Spreadsheet-WriteExcel-2.21.tar.gz
Changelog:
http://search.cpan.org/[..]MARA/Spreadsheet-WriteExcel-2.21/Changes
Mailing list archive:
http://groups.google.com/group/spreadsheet-writeexcel/

Trove categories: [change]

[Development Status]		5 - Production/Stable
[Intended Audience]		Developers
[License]		OSI Approved :: Artistic License
[Operating System]		OS Independent
[Programming Language]		Perl
[Topic]		Database, Office/Business :: Financial :: Spreadsheet, Office/Business :: Office Suites, Software Development :: Libraries

Dependencies: [change]
No dependencies filed

 Branches

Branch	Version	Last release	License	URLs
Default	2.21	10-Mar-2008	Artistic License

 Releases

Version	Focus	Date
2.21	Major bugfixes	10-Mar-2008 14:28
2.20	Major feature enhancements	06-Oct-2007 23:28
2.18	Minor bugfixes	21-Jan-2007 21:59
2.17	Minor feature enhancements	22-May-2006 08:37
2.16	Major feature enhancements	09-Jan-2006 12:51
2.15	Minor bugfixes	19-Sep-2005 12:09
2.14	Minor bugfixes	11-May-2005 12:36
2.13	Minor bugfixes	21-Apr-2005 10:43
2.12	Minor feature enhancements	24-Mar-2005 14:01
2.11	Minor feature enhancements	01-Oct-2004 09:03

 Comments

[»] Spreadsheet::WriteExcel update
by John McNamara - Jan 9th 2006 03:51:47

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

http://groups.google.com/group/spreadsheet-writeexcel/

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:

http://tinyurl.com/7ayes

John.
--

[reply] [top]

[»] External link not working in sreadshee point to workseet genrated using WriteExcel
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

[reply] [top]

[»] Re: External link not working in sreadshee point to workseet genrated using WriteExcel
by John McNamara - Nov 18th 2005 04:17:05


> when we try to use our genrated

> spreadsheet as extrenal link to other

> workbook using Excel 2002


See the response on the Google-Groups Spreadsheet::WriteExcel forum.

http://groups.google.com/group/spreadsheet-writeexcel/browse_thread/thread/d2c86b846bf66d7e/


John.
--



[reply] [top]

---

[»] Spreadsheet::WriteExcel Pre-release with Cell Comments
by John McNamara - Oct 6th 2005 02:31:00

A pre-release of the next version of Spreadsheet::WriteExcel with support for cell comments is now available. See here for details.

John.
--

[reply] [top]

[»] Spreadsheet::WriteExcel update
by John McNamara - Sep 19th 2005 04:16:14

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

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.

John.
--

[reply] [top]

[»] Announce: Spreadsheet::WriteExcelXML version 0.10
by John McNamara - Apr 29th 2005 04:03:53

Spreadsheet::WriteExcelXML version 0.10 has been released to CPAN.

Spreadsheet::WriteExcelXML is intended as a drop-in replacement for
Spreadsheet::WriteExcel which targets the Excel XML format.


This version adds the frequently requested features of autofilters.

And from the previous version.

* Added write_comment() method to allow cell comments.

* Added write_html_string() method to allow multiple
formats to be added to a cell via Html formatting.


Homepage:
http://search.cpan.org/search?dist=Spreadsheet-WriteExcelXML

Download:
http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcelXML-0.10.tar.gz

Changelog:
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcelXML-0.10/Changes

John.
--

[reply] [top]

[»] Comments in Excel 2xxx
by Daniel Meier - Apr 4th 2005 10:51:03

Hello John

As you have described in your todo list in the cpan archive, you are going to implement the write_comment method soon. Do you have an idea when it is finished?
thx
Daniel

[reply] [top]

[»] Re: Comments in Excel 2xxx
by John McNamara - Apr 5th 2005 16:01:19


> As you have described in your todo list in the cpan archive,

> you are going to implement the write_comment method soon.

> Do you have an idea when it is finished?



I am currently working on cell comments and autofilters. However, they are proving difficult to implement.

I cannot, at this stage, say when they will be finished.


John.
--


[reply] [top]

---

[»] Thanks
by Sarq6669 - Feb 26th 2005 13:02:13

Hey,

First, let me say thanks for the great module. I know people are constantly asking for graphs, so I won't bug you about it, rather... can you give me a link to where microsoft describes the "code" behind the graphs? You mention it in the "To Do List" section of the documentation and was wondering where/what it was.

Thanks.

[reply] [top]

[»] Re: Thanks
by John McNamara - Mar 15th 2005 17:24:08


> Hey,

>

> can you give me a link to where microsoft describes the

> "code" behind the graphs?




Sorry, I somehow managed to miss this post. The main Spreadsheet::WriteExcel discussion has moved to the following Google Group.



Spreadsheet::WriteExcel supports charts via external templates since version 2.10.



If you are looking for general information on the internal format of charts have a look at the links here:



John.

--




[reply] [top]

---

[»] Spreadsheet::WriteExcel Google Group
by John McNamara - Dec 2nd 2004 16:52:03

Hi,

I've set a Google group to discuss and ask questions about Spreadsheet::WriteExcel:

http://groups-beta.google.com/group/spreadsheet-writeexcel/

I hope that it will become a repository for frequently asked questions and for code shared on a peer level.

John.
--

[reply] [top]

[»] dependency files
by keep - Oct 22nd 2004 09:31:35

when I click the link(s) for the dependencies noted I get 'page not found' .. are they no longer depencencies?

[reply] [top]

[»] Re: dependency files
by jeff covey - Oct 22nd 2004 10:14:32


> when I click the link(s) for the
> dependencies noted I get 'page not
> found' .. are they no longer
> depencencies?

Where do you see dependencies? It says "No dependencies filed" for me.

Sincerely,
Jeff

--
vs lbh pna ernq guvf, lbh'er n trrx.

[reply] [top]

---

[»] Spreadsheet::WriteExcel version 2.10
by John McNamara - Sep 27th 2004 15:50:14

Spreadsheet::WriteExcel version 2.10 contains some interesting new features.

The first of these is support for charts via external templates. Here are some example programs from the distro and screenshots of the corresponding output:

Program: demo1.pl and output: demo1.jpg.
Program: demo2.pl and output: demo2.jpg.
Program: demo3.pl and output: demo3.jpg.


In perl 5.8 utf8 is now supported automatically in the write() and write_string() methods:

Program: unicode_2022_jp.pl and output: unicode_2022_jp.jpg.
Program: unicode_8859_11.pl and output: unicode_8859_11.jpg.
Program: unicode_8859_7.pl and output: unicode_8859_7.jpg.
Program: unicode_big5.pl and output: unicode_big5.jpg.
Program: unicode_cp1251.pl and output: unicode_cp1251.jpg.
Program: unicode_cp1256.pl and output: unicode_cp1256.jpg.
Program: unicode_koi8r.pl and output: unicode_koi8r.jpg.
Program: unicode_polish_utf8.pl and output: unicode_polish_utf8.jpg.
Program: unicode_shift_jis.pl and output: unicode_shift_jis.jpg.

With older perls you can still use UTF-16 and the write_unicode() method as before.


See the ChangeLog for full details.

The 2.xx development will now concentrate on AutoFilters and Cell Comments in that order.

I hope that there is some good news here for someone. :-)

John.
--

[reply] [top]

[»] VLOOKUP with Cell Ref is #VALUE! Error
by Foam Head - Sep 22nd 2004 13:45:30

Using 2.04 and Excel 2002, there is a problem with VLOOKUP using a cell reference for the lookup_value (first) argument. Oddly, if you go to the #VALUE! cell, hit F2 (edit the cell), hit Enter (done with editting -- made no changes), the formula works correctly. Using a string for the lookup_value does not have this problem.

$ cat ./formula-needs-enter.pl
#!/usr/bin/perl -w

use Spreadsheet::WriteExcel;

print "Using perl $] and Spreadsheet::WriteExcel $Spreadsheet::WriteExcel::VERSION\n";

my $outFile = 'test-formula.xls';
my $workbook = new Spreadsheet::WriteExcel $outFile;
die "Failed to open $outFile: $!\n" unless defined $workbook;

my $worksheet = $workbook->add_worksheet( 'Test' );

$worksheet->write_string( 0, 0, 'Data' );
$worksheet->write_row( 1, 0, [ qw( Banana 3 ) ] );
$worksheet->write_row( 2, 0, [ qw( Apple 12 ) ] );

$worksheet->write_string( 4, 0, 'Lookup' );
$worksheet->write_string( 5, 0, 'Apple' );
# This works
$worksheet->write_formula( 5, 1, q{=VLOOKUP("Apple",A2:B3,2,FALSE)} );
$worksheet->write_string( 6, 0, 'Banana' );
# This requires F2/Enter workaround
$worksheet->write_formula( 6, 1, q{=VLOOKUP(A7,A2:B3,2,FALSE)} );

$ ./formula-needs-enter.pl
Using perl 5.006001 and Spreadsheet::WriteExcel 2.04
$

PS. Love the module. And I vote for AutoFilter support any way you can do it :-).

[reply] [top]

[»] Re: VLOOKUP with Cell Ref is #VALUE! Error
by Foam Head - Sep 25th 2004 11:59:14

Same error with the new 2.10 version :-(.

$ ./formula-needs-enter.pl
Using perl 5.006001 and Spreadsheet::WriteExcel 2.10
$

>

> $ ./formula-needs-enter.pl

> Using perl 5.006001 and Spreadsheet::WriteExcel 2.04

> $

>


[reply] [top]

---

[»] Re: VLOOKUP with Cell Ref is #VALUE! Error
by John McNamara - Sep 26th 2004 17:12:00


> Using 2.04 and Excel 2002, there is a problem with VLOOKUP using a cell

> reference for the lookup_value (first) argument.


This happens due to a bug in the Formula.pm parser which misinterprets the class of some nested tokens. This is on the shorter TODO list.

In the meantime here is a workaround using store_formula() and repeat_formula():





#!/usr/bin/perl -w

use Spreadsheet::WriteExcel;


my $outFile = 'test-formula2.xls';
my $workbook = new Spreadsheet::WriteExcel $outFile;
die "Failed to open $outFile: $!\n" unless defined $workbook;

my $worksheet = $workbook->add_worksheet( 'Test' );

$worksheet->write_string( 0, 0, 'Data' );
$worksheet->write_row( 1, 0, [ qw( Banana 3 ) ] );
$worksheet->write_row( 2, 0, [ qw( Apple 12 ) ] );

$worksheet->write_string( 4, 0, 'Lookup' );
$worksheet->write_string( 5, 0, 'Apple' );

$worksheet->write_string( 6, 0, 'Banana' );

# Workaround for bad parsing of VLOOKUP.
my $formula = q{=VLOOKUP(A7,A2:B3,2,FALSE)};
my $vlookup = $worksheet->store_formula($formula);
@$vlookup = map {s/_ref2d/_ref2dV/;$_} @$vlookup;

$worksheet->repeat_formula( 6, 1, $vlookup );


__END__



You can also use the same repeat_formula() call to change the cell reference if required.

John.

--








[reply] [top]

---

[»] Re: VLOOKUP with Cell Ref is #VALUE! Error
by Foam Head - Sep 30th 2004 11:33:55

Yup, that works. Thanks, John.

[reply] [top]

---

[»] Strange behaviour
by Joost - Sep 5th 2004 08:32:22

I'm getting some strange results when generating xls files. My setup is Windows XP with Office 2003. My generated files sometimes crash Excel. Yes, that's right, *sometimes*! Excel then comes up with the 'Do you want to send a report to Microsoft' screen and closes. And I'm constantly generating the same xls file! It seems to occur randomly, but I have been able to reproduce this error:

1. Double-click on the xls file
2. Excel starts and displays the contents
3. Close the xls within Excel, but keep Excel running
4. Double-click on the xls file again
5. Crash!

This happens everytime with one of the xls files I generate. Another one I'm generating doesn't crash when I perform these steps. I've also tried this on a friend's computer (WinXP & Offices 2003), exact same behaviour. Any ideas? I can provide a sample xls if needed.

[reply] [top]

[»] Re: Strange behaviour
by Joost - Sep 5th 2004 08:54:58

Well, never mind the 'how to crash' plan. It worked for the 20 times I tried it, but now it opens without problems the second time as well. I'm going crazy here. Something is not right though, it's either the generated file, or Excel 2003....

[reply] [top]

---

[»] Re: Strange behaviour
by John McNamara - Sep 5th 2004 14:14:24


> I'm getting some strange results when
generating xls files. My setup is

> Windows XP with Office 2003. My
generated files sometimes crash Excel.

> Yes, that's right, *sometimes*!



Hi,

The most common problem with the 2.xx releases that causes Excel to crash is the reuse of a format that has been applied to a merged range in an non-merged cell.

See the warning here.

If you are using merged ranges in your program check there first.

If this isn't the problem and you manage to get a reproducible bug send me a small test program that demonstrates the problem. You can use the bug_report.pl program in the distro as a basis.

Ciao,

John.
--



[reply] [top]

---

[»] Size of the produced excel files
by Daniel Meier - Aug 13th 2004 07:21:11

Hello

I was wondering about the file size of my produced excel files.

An excel file created with WritePerl.pm : 10KB
Same data but manually saved in excel (format excel 2002 sp2): 20KB

Thats half of the original file, which is working for me. But what is in the other half, is there more meta information or else? Do I loose some information?

thx
Daniel

[reply] [top]

[»] Re: Size of the produced excel files
by John McNamara - Aug 16th 2004 16:17:45


> I was wondering about the file size of
my produced excel files.


Hi,

I usually get asked the opposite question, "Why is my WriteExcel file bigger than the same file saved in Excel?".

The reason that WriteExcel files are smaller for small files is that (as you guessed) it doesn't include any of the standard metadata that Excel does.

The reason that WriteExcel files are generally bigger for big files is that it doesn't employ some space saving mechanisms that Excel does.

John.
--



[reply] [top]

---

[»] Two formats in one cell
by Clinton - Aug 3rd 2004 03:59:06

I'd like to put two strings into a cell, one in black and the other in red. How can I format part of the text in a cell without formating the whole cell?

thanks

Clint

[reply] [top]

[»] Re: Two formats in one cell
by John McNamara - Aug 8th 2004 10:13:58


> I'd like to put two strings into a cell, one in black and the other in red.

> How can I format part of the text in a cell without formating the whole cell?

Hi,

That currently isn't possible.

I had been working on this feature in an earlier release but it caused a conflict with Spreadsheet::ParseExcel::Saveparser so I dropped it out until I had more time to work on it.

A working version is still available if you are interested but it is based on the Excel5 version of the module and it isn't compatible with the new Excel97+ versions.

It will probably get merged back into the core in a later version.

John.
--



[reply] [top]

---

[»] Example code for mod_perl 2
by Matisse Enzer - Jun 3rd 2004 23:03:41

I sent John a modified version of the mod_perl.pl example, for mod_perl 2.

For those who want to know right now how to use Spreadsheet::WriteExcel with mod_perl here's a stripped-down version of the example - most comments removed:

###############################################################################
#
# PerlModule Spreadsheet::WriteExcel::MP2Test
# <Location /spreadsheet-test>
# SetHandler perl-script
# PerlResponseHandler Spreadsheet::WriteExcel::MP2Test
# </Location>
#
package Spreadsheet::WriteExcel::MP2Test;
use strict;
use Apache::Const -compile => qw( :common );
use Spreadsheet::WriteExcel;

sub handler {
my($r) = @_; # Apache request object is passed to handler in mod_perl 2
my $filename ="mod_perl2_test.xls";
$r->headers_out->{'Content-Disposition'} = "attachment;filename=$filename";
$r->content_type('application/vnd.ms-excel');
my $xls_str;
tie *XLS => $r; # The mod_perl 2 way. Tie to the Apache::RequestRec object
binmode(*XLS);

my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
my $worksheet = $workbook->add_worksheet();

$worksheet->set_column(0, 0, 20);
my $format = $workbook->add_format();
$format->set_bold();
$format->set_size(15);
$format->set_color('blue');

$worksheet->write(0, 0, 'Hi Excel! from ' . $r->hostname , $format);
$workbook->close();
return Apache::OK;
}

1;
########################################################

[reply] [top]

[»] OLE::Storage_Lite: Recommended upgrade.
by John McNamara - May 28th 2004 14:53:23

OLE::Storage_Lite 0.12 has been uploaded to CPAN. This is a recommended upgrade for anyone using Spreadsheet::WriteExcel::Big or Spreadsheet::WriteExcel::FromXML.

It fixes several problems when working with large files. Thanks to Dave Rouzier for the main patch.

In other news there have been some recent updates to Spreadsheet::WriteExcel. Version 1.01 is the culmination of the 0.4x releases and is the last of the Excel 5 versions of the module (barring minor bugfixes).

The main development will now move to the 2.xx versions of the module that support the Excel 97 file format.

As noted in the changelog, Version 2.01 isn't fully compatible with 1.01 since the write_comment() method isn't supported yet. The module is also 1.5 to 2 times slower than 1.01. These issues will be resolved in upcoming releases.

One final note, there will be a 2.02 release that doesn't contain any code changes. The only change is to the internal version numbers to keep CPAN.pm happy. It is not a required upgrade.

John.
--

[reply] [top]

[»] Spreadsheet::WriteExcelXML
by John McNamara - Apr 25th 2004 08:30:07

The Spreadsheet::WriteExcelXML module has been uploaded to CPAN.

It uses the same interface as Spreadsheet::WriteExcel but emits Excel XML instead of the Excel binary format.

It is compatible with Excel 2002 and 2003.

It doesn't support all of the features of Spreadsheet::WriteExcel but they will be added in time.

John.
--

[reply] [top]

[»] Linking external sheets...
by Sidrew - Apr 2nd 2004 15:43:33

Ok,

For starters.. this is a linux (debian) host.. running 0.42 of WriteExcel.

I'm having a problem linking external data on a windoze server. I've tried doing this as a formula ala:

$formula = "='\\\\someserver\\path\\[file.xls]$sheet'$cell";
$worksheet{$key}->write_formula ('C1', $formula, $format);

And also using external url link "notation" ala:

$worksheet{$key}->write ('C1', 'external://someserver/path/file.xls#$sheet!$cell', $format);

The problem with the former method is that the module complains that it can't find the sheet. The problem with the latter is that the variables $sheet and $cell aren't evaluated...

Does anyone know how to do this properly?... I'm at a loss here.

Thanks

Sid

[reply] [top]

[»] Re: Linking external sheets...
by John McNamara - Apr 3rd 2004 13:42:50


> The problem with the former method is that the module complains that it can't

> find the sheet. The problem with the latter is that the variables $sheet and

> $cell aren't evaluated...



The first example doesn't work because references to external worksheets aren't supported in Spreadsheet::WriteExcel formulas.

The second example should work if you change the single quotes to double quotes, or use the qq() operator, so that the variables are interpolated in the string. See the relevant section of perlop for more details.

--
John.



[reply] [top]

---

[»] Re: Linking external sheets...
by Sidrew - Apr 6th 2004 06:37:13


>

> % The problem with the former method is

> that the module complains that it can't

> % find the sheet. The problem with the

> latter is that the variables $sheet and

> % $cell aren't evaluated...

>

>

>

>

> The first example doesn't work because

> references to external worksheets aren't

> supported in Spreadsheet::WriteExcel

> formulas.

>

> The second example should work if you

> change the single quotes to double

> quotes, or use the qq() operator, so

> that the variables are interpolated in

> the string. See the relevant section of

> perlop for more details.

>

> --

> John.

>

>

Thanks, John....

That solved my variable evaluation... perhaps the syntax of my external link isn't proper... when I open the resultant excel file, I get errors that the links aren't valid.

This statement:

$worksheet{"$key"}->write ($row, $col, qq{external://server/path/file.xls#'$sheet'!$cell}, $format);

Gives me this in the cell:

\\server\path\file.xls - 'Sheet Name'!C6

something like that... perhaps the URL syntax is invalid.

My main question is... is there any way to link cells from an external sheet into a WriteExcel workbook?

Thanks.

Sid

[reply] [top]

---

[»] Re: Linking external sheets...
by John McNamara - Apr 6th 2004 14:32:40


> Gives me this in the cell:

>

> \\server\path\file.xls - 'Sheet Name'!C6


The syntax is correct but the resulting link should look like this

\\server\path\file.xls#'Sheet Name'!C6

If that isn't a typo you should check that part of your code.


> My main question is... is there any way to link cells from an external sheet into a WriteExcel workbook?


No that isn't currently possible due to some tricky problems with including external references in formulas. Sorry. :-(

--
John.


[reply] [top]

---

[»] Displaying Strings with Leading Zeros
by Jason Bauer - Apr 2nd 2004 14:11:47

I'm using this module for an ad-hoc reporting feature of the application I'm working on. One common field in our data is a zero-padded request number (0019504). This number shows up on a lot of the custom reports people are making, but Excel is chopping off the leading zeros.

The software doesn't know where this field will show up, so I can't simply use the '0000000' format. I've tried using the '@' format as suggested in the manual, but it doesn't seem to have any effect.

Do you know of any way to force Excel to display the field as a Text column to keep my leading zeros?

[reply] [top]

[»] Re: Displaying Strings with Leading Zeros
by John McNamara - Apr 2nd 2004 14:54:33


> Do you know of any way to force Excel to display the field as a Text column to keep my leading zeros



The keep_leading_zeros() Worksheet method may change the Spreadsheet::WriteExcel default behaviour to give you something more like what you are looking for.

The keep_leading_zeros() docs give an explanation of some of the issues involved.

It would also be best to add a '@' format as well. This may require you to test the incoming data for
the request numbers before writing them.

--
John.


[reply] [top]

---

[»] Support for printing Thai characters
by Aditya Sanghi - Apr 2nd 2004 04:33:13

Hi! I'm using Version 0.42 from CPAN on Windows.

Trying to create Excel spreadsheet containing Thai and English characters. I cant seem to get the thai characters printed using the write method.

Is there support for different encodings?

Any help would be much appreciated.

Thanks for a *REALLY* lifesaving (facesaving) module!

Cheers!

[reply] [top]

[»] Re: Support for printing Thai characters
by John McNamara - Apr 2nd 2004 14:40:44


> Trying to create Excel spreadsheet containing Thai and English characters.



There is a pre-release version of Spreadsheet::WriteExcel that supports the Excel 97 file format and thus Unicode strings. See this link below for more information.

--
John.


[reply] [top]

---

[»] Problems with large number of worksheets per workbook
by Bernd Lebender - Mar 24th 2004 10:36:56

Hi,
when I $wb->close(); a workbook with some hundred worksheets, I keep getting the message :

Can't locate Carp/Heavy.pm in @INC (@INC contains: .... .) at /tools/perl-5.8.0/lib/5.8.0/Carp.pm line 178.

But Carp/Heavy.pm is definitely accessible thru @INC ?!
When I reduce the number of sheets (to about 100 or less) everything (as far as I can perceive it!) works fine.
This experienced limit of number of sheets somehow also depends on the amount of information I have put into the sheets.

Did I overlook some builtin limitations? Excel97 itself didn't have problems to store and open a Workbook with 2000 sheets as Excel5.0 !

Any idea what to do ?

Thank you Bernd

[reply] [top]

[»] Re: Problems with large number of worksheets per workbook
by John McNamara - Mar 24th 2004 14:55:15


> I keep getting the message :
>
> Can't locate Carp/Heavy.pm in @INC (@INC
>

I think that this error message is due to a system limitation on the number of open temp (or non temp) files.

Spreadsheet::WriteExcel creates a new temporary file for each worksheet that you add to a workbook. The reason for this is to minimise the amount of data that is stored in memory. Thus, the maximum number of worksheets that S::WE can create in a workbook is limited to the number of files that can be opened simultaneously.

Try the following program to see what your system limit is (more of less):


#!/usr/bin/perl -w
use strict;
use IO::File;
my @tempfiles;
for my $i (1 .. 2000) {
my $fh = IO::File->new_tmpfile();
die "Failed to created file number $i.\n" unless defined $fh;
push @tempfiles, $fh;
}
__END__


You could try to invoke File::Temp as the temp file handler by calling the set_tempdir() Workbook method. It will probably run up against the same limit however.

In which case you can fix the problem by increasing the system limit for the number of simultaneously open files.


John.
--


[reply] [top]

---

[»] Re: Problems with large number of worksheets per workbook
by Bernd Lebender - Mar 25th 2004 02:52:23


>
> % ...the number of open temp ( or non temp) files ...
> %
>
You have been right! However changing "max number of open files" doesn't really
help on UNIX systems, because it is a kernel parameter.

I tried $wb->set_tempdir() for an existing directory where I (the owner) had all rights.
I got the message:
Unable to create temp files in /home/lebender/Obelix.
Data will be stored in memory. Refer to set_tempdir()
in the Spreadsheet::WriteExcel documentation.

But the program completed successfully for 930 sheets and with no real performance impact.
Size went up to 16Mb which required to use Big-file feature.
Storing als Excel97 reduced size to 2MB.
That's fine for the time being.

Once set_tempdir() gets fixed to accept new tempdir, I would be happy if there is
a new feature of set_tempdir() to force to store temp data in memory; perhaps
if set_tempdir() is called without parameter ?

Thanks again
Bernd

[reply] [top]

---

[»] Resizing Comment-Boxes
by Bernd Lebender - Mar 18th 2004 06:05:31

You 'll not be surprised to hear -- this module is REALLY a BIG help!
Have used majority of the features --- never any serious problems. Some indications on
limitations exceeded would be fine (e.g. #of columns).

In order to mimic kind of "3-D-spreadsheet" I'm using "comment" feature. Often line length or number of lines in a comment exceeds default box size. It would be great, if the comment box would be auto-sized to it's actual content, or a method would allow to give explicit size to a comment box. By today I have to leave it up to the reader of the Execl-file to use Excel's "Edit Comment" feature to make the content readable. This sometimes is bejond their ....

Additionally I'ld like to join the choir of other users and ask if there is any chance to get Macro's (and Graph's) implemented in near future.

Thank you,
Bernd

[reply] [top]

[»] Re: Resizing Comment-Boxes
by John McNamara - Mar 18th 2004 15:00:57


> It would be great, if the comment box would be
> auto-sized to it's actual content, or a
> method would allow to give explicit size
> to a comment box.

Sizing the comment box is an Excel 97+ feature so it isn't available in Spreadsheet::WriteExcel 0.42.

Also, I am having problems implementing cell comments in the Excel 97 version of the module so it isn't available in the 0.49.x versions either.

All I can say is that this feature will, hopefully, be available in a later release.

John.
--



[reply] [top]

---

[»] A brief update
by John McNamara - Feb 15th 2004 15:19:24

Apologies to anyone who has sent questions to my CPAN email address in the last two months. I have been moving country and haven't been able to reply to most of them.

I am currently trying to work through the backlog.

In the next few months I will release the final Excel 5 / 0.4x version of Spreadsheet::WriteExcel after which I will add all of the latest bug fixes and improvements to the Excel 97 version and make that the official CPAN release.

I am also porting Spreadsheet::WriteExcel to support the Excel XML format (SpreadsheetML). A preliminary release of this will be on CPAN in a few weeks.

This will make it easier to support filters, pivot tables and some other features which are difficult to implement in the binary format.

Still no macros or charts however. They aren't supported in the published XML schemas.

John.
--

[reply] [top]

[»] Incompatible xls files?
by ezelena - Jan 29th 2004 03:59:52

Hi,

At first, thanks for this good stuff, but... I have a problem... I try to use this component:
http://www.tmssoftware.com/flexcel.htm

and I got a %1 not found message, when I try to open XLS-files created with WriteExcel module :(

Any Idea?

Endre

[reply] [top]

[»] Re: Incompatible xls files?
by John McNamara - Feb 15th 2004 15:25:59


> I have a problem... I try to use this component:
> http://www.tmssoftware.com/flexcel.htm
> and I got a %1 not found message, when I try to open XLS-files created with
> WriteExcel module :(

The Excel file format is available to some extent but a lot of information is missing. As such most third party providers of Excel writing and reading software make assumptions about the file structure.

As such, while I can guarantee Spreadsheet::WriteExcel can create a file that Excel can read I cannot guarantee the file will work with other applications.

Sorry. :-(

John.
--


[reply] [top]

---

[»] Excel 2000 auto-prompt to save file
by Brig - Jan 27th 2004 12:10:22

It seems that after Spreadsheet::WriteExcel creates an .xls file for me, if I open that file from Windows Explorer and make any changes to the data, Excel does not automatically prompt me to Save Changes to the file as I click through doing a File Close. Is there some setting that I'm missing?

Thanks

[reply] [top]

[»] Re: Excel 2000 auto-prompt to save file
by John McNamara - Feb 15th 2004 15:27:57


> It seems that after Spreadsheet::WriteExcel creates an .xls
> file for me, if I open that file from Windows Explorer and make any changes to
> the data, Excel does not automatically prompt me to Save Changes to the file as
> I click through doing a File Close.

I cannot reproduce this.

Could you send me an email with a sample program, details of your Windows, Excel, perl and Spreadsheet::WriteExcel versions and a short point by point description of the manual steps involved. Send it to jmcnamara at cpan dot org.

John.
--


[reply] [top]

---

[»] Next release?
by Jeff - Dec 28th 2003 06:37:11

I'm stuggling with long strings here. I've tried using long_string (), and occasionally in Excel the string terminates early or creates an #N/A error. I am a newbie at PERL, and your time and help is appreciated. So...

Do you have any suggestions on tweaking long_string()?
Is the #N/A error caused by too many arguments in the cell?
Will double-quotes cut off the string in the cell?
When will the next release be... uh... released?

All suggestions welcome.
Thanks,

--
-Jeff

[reply] [top]

[»] Re: Next release?
by John McNamara - Jan 4th 2004 17:42:59


> I'm stuggling with long strings here. I've tried using long_string (), and
> occasionally in Excel the string terminates early or creates an #N/A
> error.

The long_string() function was a kludge while I worked on the Excel 97 version of the module. I can guess at what might be causing your problem but it probably isn't worth fixing.

Instead, if you need long strings then it is probably best to use the Excel 97 pre-release version. See the announcement below:

--
John.


[reply] [top]

---

[»] Excel2000 - text wrap limitation?
by rik - Nov 6th 2003 01:30:04

Hi,
this is a great module and it helps a lot. I am currently concentrating on v0.49.7 since I need to send longer than 255 strings. Now, when I merge cells and wrap the text Excel cannot wrap text longer than 1000 chars? This turned out to be 'feature' of Excel2000 and it is valid for single cells as well (ACCORDING TO MICROSOFT: if a cell contains more than 1,024 characters, the following rules apply: Characters after approximately the 1,024th character do not appear in the cell; however, they appear in the formula bar when you edit or select the cell.) Do you know a workaround or anthing to handle this?
Best regards

[reply] [top]

[»] Re: Excel2000 - text wrap limitation?
by John McNamara - Nov 13th 2003 14:48:28


> when I merge cells and wrap the text Excel cannot wrap text
> longer than 1000 chars?


Unfortunately this is a feature of Excel 97+. Although you can have up to 32767 characters in a cell Excel will only display 1024 (but as you note the full string is displayed in the formula bar).


I don't know of any workaround for this apart from splitting the string into more than one cell.


John.
--


[reply] [top]

---

[»] OK, now what do I do?
by John Gunkle - Oct 14th 2003 06:48:53

My program reads a DB2 database and creates a multisheet workbook as out put from the query. My trace prints show me that it is doing exactly what I set out to do and I get an .xls file for output.

The problem is I can't open it with Excel 97, which is this company's standard. I get: 'file.xls' cannot be accessed. The file maybe read-only, or you maybe trying to access a read-only location. Or, the server the document is stored on may not be responding. None of this applies.

I have tried the test script to test the process. I was unable to open my output xls file nor the test xls file.

Is there something I am missing?

John Gunkle

--
John

[reply] [top]

[»] Re: OK, now what do I do?
by John McNamara - Oct 14th 2003 07:33:16


> The problem is I can't open it with Excel 97, which is this company's
> standard. I get: 'file.xls' cannot be accessed. The file maybe read-only, or
> you maybe trying to access a read-only location. Or, the server the document is
> stored on may not be responding.

This rather cryptic error message is Excel's way of saying that the file has been corrupted.

This can happen for a number of reasons:

1. The file was corrupted during file transfer. This can happen, for example, if you ftp to Windows in ascii mode. Check the file sizes on the host and target to make sure this hasn't happened.

2. UTF8 data was included in the file. This often happens when writing strings returned by an XML parser. See the "Working with XML data" section of the documentation for details.

3. A merge range was set-up incorrectly. This is generally hard to do with the latest versions of the module.

If none of these cases apply drop me a line: jmcnamara at cpan dot org.

John.
--



[reply] [top]

---

[»] Re: OK, now what do I do?
by Dieter Runggas - Jan 28th 2004 07:35:18


>
>
> If none of these cases apply drop me a
> line: jmcnamara at cpan dot org.
>
>
I think, there is a 4th case: if the file is simply too long.

We have a sheet containing more than 3000 rows of 16 columns. But each row contains one column with a chemical structure represented as a bmp file with a size of 60 Kb.

This makes the xls file 180 MB big. This does'n work!

If we omit the graphics (2 MB) it works, if we reduce the rows to 2000 (120 MB) it works too.

So - ok, now what can I do?


[reply] [top]

---

[»] Re: OK, now what do I do?
by John McNamara - Feb 15th 2004 15:37:33


> I think, there is a 4th case: if the file is simply too long.

This is due to a bug in OLE::Storage_Lite. If you send me an email I'll send you a patched version that someone else kindly send to me: jmcnamara at cpan dot org.

For anyone else who is interested I'll upload it to an ftp server and post a link here.

John.
--


[reply] [top]

---

[»] AutoFilter
by Daniel Kölle - Sep 26th 2003 07:21:29

Thank you for the great module!

I have one small question: Is it possible to add an AutoFilter (Data -> Filter -> AutoFilter)? Since I always create large workbooks it would be a nice feature.

[reply] [top]

[»] Re: AutoFilter
by John McNamara - Oct 2nd 2003 04:19:19


> Is it possible to add an AutoFilter


It isn't currently possible to add an AutoFilter using Spreadsheet::WriteExcel.

It is a frequently requested feature but there are some implementation problems associated with it and to date I've put off working on it.

Specifically, while it is relatively easy to add the drop down filter boxes via the file format the actual filter has to be calculated dynamically. Once a filter is applied in Excel the application goes through each row in the filtered region and sets them to hidden if they don't meet the filter criteria.

Excel can do this because it has the entire file loaded in memory. Spreadsheet::WriteExcel on the other hand stores as little as possible of the file in memory to increase efficiency.

As such, the only way around this would be for the user to manually hide rows via the set_row() worksheet method.

So "vox pop" time. Would the people who are subscribed to this project like to have an AutoFilter feature with the proviso that the filtering would have to be done manually? Answer below or send me a mail. jmcnamara at cpan dot org.

--
John.



[reply] [top]

---

[»] Spreadsheet::WriteExcel::FromXML
by Kyle Burton - Jul 30th 2003 13:21:35

We just released the first beta version (1.00) of an add-on module to CPAN that allows you to transform a specific XML DTD to an XLS file using the module.

[reply] [top]

[»] Re: Spreadsheet::WriteExcel::FromXML
by John McNamara - Aug 27th 2003 03:36:48


> We just released the first beta version (1.00) of an add-on module to CPAN that
> allows you to transform a specific XML DTD to an XLS file using the module.


See: Spreadsheet::WriteExcel::FromXML

This is a very clever idea.

The module uses a DTD to define a simple XML format that can be converted to an Excel file using Spreadsheet::WriteExcel as a backend.

This is a potentially powerful approach since it effectively decouples your data from Perl, apart from a single filter program, and allows you to create Excel files using your preferred XML tools.

Requires Spreadsheet::WriteExcel version 0.42.

John.
--



[reply] [top]

---

[»] Outlines
by hpguy - Jul 16th 2003 10:16:28

Is there a way to create outlines like so?

- level 1
|- level 2
||- level 3
||| . level 4
||| . level 4

I have a spreadsheet where the totals and sub-totals are at the top, but when I use the outline feature of the module, I can't get it working to collapse "upwards".

[reply] [top]

[»] Re: Outlines
by hpguy - Jul 16th 2003 10:57:33

Nevermind. Duh, it's always when I ask for help do I find the answers on my own. For those of you interested, you just have to make the following call:

$worksheet->outline_settings(1,0,1,1);



> Is there a way to create outlines like
> so?
>
> - level 1
> |- level 2
> ||- level 3
> ||| . level 4
> ||| . level 4
>
> I have a spreadsheet where the totals
> and sub-totals are at the top, but when
> I use the outline feature of the module,
> I can't get it working to collapse
> "upwards".


[reply] [top]

---

[»] Re: Outlines
by christopher - Aug 1st 2003 08:39:56


> Nevermind. Duh, it's always when I ask
> for help do I find the answers on my
> own. For those of you interested, you
> just have to make the following call:
>
> $worksheet->outline_settings(1,0,1,1);
>
>
>
> % Is there a way to create outlines
> like
> % so?
> %
> % - level 1
> % |- level 2
> % ||- level 3
> % ||| . level 4
> % ||| . level 4
> %
> % I have a spreadsheet where the totals
> % and sub-totals are at the top, but
> when
> % I use the outline feature of the
> module,
> % I can't get it working to collapse
> % "upwards".
>
>
>

doesn't the docs say this is only for a mac?


[reply] [top]

---

[»] Re: Outlines
by John McNamara - Aug 1st 2003 17:57:14


> doesn't the docs say this is only for a mac?

The Worksheet::outline_settings() feature should work for all versions of Excel.

The feature that is specific to the Mac is Format::set_outline() which is a font property.

John.
--


[reply] [top]

---

[»] set_h_pagebreaks broken in pre-release
by jmmygoggle - Jul 12th 2003 23:48:30

The set_h_pagebreaks function doesn't seem to be working correctly on Excel 2000 (or OpenOffice) using pre-releases 0.49.7 and 0.49.5. Downgrading back to version 0.39 brings back the pagebreaks but I require the larger 32767 character limits enabled by the pre-releases. (Among other problems, the long string concatenation formula workaround for previous versions causes some undesirable error messages.) I'm not sure if certain features can be counted on in pre-release versions but I would appreciate any thoughts on how I can get manual page breaks working again. Print formatting is important to the files I'm creating.

Separately, I've noticed that set_text_wrap() on very large files causes extreme lag time when opening files and inserting columns. Applying the text wrap formatting to only those cells that require it (obvious?) squashed the minutes of loading time and any crashes associated with inserting columns. This might be evident to others who know the XLS file format better but it was a helpful discovery to me.

[reply] [top]

[»] Re: set_h_pagebreaks broken in pre-release
by John McNamara - Jul 20th 2003 16:33:13


> The set_h_pagebreaks function doesn't seem to be working
> correctly on Excel 2000 (or OpenOffice) using pre-
> releases 0.49.7 and 0.49.5.

You are right. There was a bug in the vertical and horizontal page-break handling. I've fixed it and updated 0.49.7 (without up-reving it).

The updated tar file is available here.


> Separately, I've noticed that set_text_wrap() on very large files
> causes extreme lag time when opening files and inserting columns.

I'll look into this.

Thanks.

--
John.



[reply] [top]

---

[»] Re: set_h_pagebreaks broken in pre-release
by jmmygoggle - Jul 22nd 2003 13:11:38

Can anyone verify this problem? I would appreciate any info on this as I can't seem to get page breaks working in my code and I don't want to keep running in circles if this is broken on the module side. I realize Mr. McNamara is very busy with life and a new baby and has given a 4 month estimate for some new features. (Thanks again for this outstanding module.) If anyone else has successfully or unsuccessfully used horizontal pagebreaks in their files for versions 0.49.7 or 0.49.5 please let me know. Also let me know if something has changed in the implementation of page breaks since 0.39 that may be of use. Thanks much.


> The set_h_pagebreaks function doesn't
> seem to be working correctly on Excel
> 2000 (or OpenOffice) using pre-releases
> 0.49.7 and 0.49.5. Downgrading back to
> version 0.39 brings back the pagebreaks
> but I require the larger 32767 character
> limits enabled by the pre-releases.


[reply] [top]

---

[»] Re: set_h_pagebreaks broken in pre-release
by jmmygoggle - Jul 22nd 2003 13:17:56

My apologies for not refreshing this page I had open in my browser. My desperation had just eclipsed my sorry excuse for patience. How do you make the time to keep everyone so happy?


> (Thanks again for this outstanding module.)


[reply] [top]

---

[»] Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Apr 15th 2003 15:35:43

Another pre-release version of Spreadsheet::WriteExcel with Excel97 support is now available. This release fixes a bug which, in some cases, could cause Excel to crash when using Unicode strings. Thanks to Dmitry Kochurov for the bug report.

This release also changes the default format of the Unicode string required by the write_unicode() method. The string must now be big-endian UTF-16 instead of little-endian UTF-16. This change is mainly to make it easier to use other CPAN modules which generally default to handling UTF-16 data as big-endian regardless of the platform. UTF-16LE data can be written via the new write_unicode_le() method.

See the release notes for further details. Here are some screenshots of Cyrillic and Japanese text created using the examples in the release notes.

The tar file can be downloaded from here.

The pre-release has been tested with Excel97 and Excel2000 and Gnumeric. Excel5 files and OpenOffice are not supported in this release. Porters should ignore this version of the module and wait for the official CPAN release.

John.
--

[reply] [top]

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Y.Watanabe - Apr 20th 2003 18:23:54

Ok. I tested japanese unicode charcter and feel good !

#!/usr/bin/perl
# this script charset = EUC Japanese, line feed code = LF
# RedHat Linux6.2(en), perl5.0005
# test for excel PC = Win2000(ja)+ Excel2000(ja)
use Spreadsheet::WriteExcel;
use Unicode::Japanese;
$workbook = Spreadsheet::WriteExcel->new('test.xls');
$worksheet = $workbook->addworksheet();
$format = $workbook->addformat(font => 'MS Gothic');
$s = Unicode::Japanese->new();
$str = "JAPANESE CHARACTER"; # replace with japanese character
$worksheet->write_unicode("A1", $s->set($str,'euc')->utf16, $format);
$workbook->close();



[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Isabelle Brette - Apr 25th 2003 00:34:49

Got a problem : the generated file works well with Office XP, but makes Office 2000 crash...

[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Y.Watanabe - Apr 25th 2003 09:46:46

? I tested with excel2000(ja) and excel2002(ja) and no problem...
(from Tokyo.)

[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Apr 25th 2003 17:01:50


> Got a problem : the generated file works well with Office XP, but makes Office 2000 crash...


I tested pre-release 5 with Excel 97 and Excel 2000 and I didn't encounter any crashes.

Can you try to establish the smallest Spreadsheet::WriteExcel program that causes your Excel 2000 to crash and then send me the program.

Also, double check that the file wasn't corrupted in some way during transfer from the Excel XP system to the Excel 2000 system.

John.
--



[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Isabelle Brette - Apr 28th 2003 00:26:23


>
> Can you try to establish the smallest
> Spreadsheet::WriteExcel program that
> causes your Excel 2000 to crash and then
> send me the program.
>
> Also, double check that the file wasn't
> corrupted in some way during transfer
> from the Excel XP system to the Excel
> 2000 system.
Unfortunately, I don't have the time for further tests...
And I don't have Excel 2000 at hand (my users have it but they're a few miles away, and they're the ones who warned me that the file made their system crash)...

As the file is generated directly by the web server, there was no transfer from one system to the other.

[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Simone - Apr 29th 2003 05:20:31

Simple test:

use Spreadsheet::WriteExcel;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");

# Add a worksheet
$worksheet = $workbook->addworksheet();

# Add and define a format
$format = $workbook->addformat(); # Add a format
$format->set_bold();
$format->set_color('red');
$format->set_align('center');

$format1 = $workbook->addformat(); # Add a format
$format1->set_bold();
$format1->set_color('red');
$format1->set_align('center');


# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write($row, $col, "Hi Excel!", $format);
$worksheet->write(1, $col, "Hi Excel!");
$worksheet->merge_range("C2:F9", "Hi Excel!", $format);

# Write a number and a formula using A1 notation
$worksheet->write('A3', 1.2345);
$worksheet->write('A4', '=SIN(PI()/4)');



With this script when I open the file perl.xls Excel 97, 2000 and 2002 crash. Now if you change the row:

$worksheet->merge_range("C2:F9", "Hi Excel!", $format);

with:

$worksheet->merge_range("C2:F9", "Hi Excel!", $format1);

The perl.xls works fine!

The problem, I think, appear when I user the same cell format for the $worksheet->write and $worksheet->merge_range.

Any idea?

[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Apr 29th 2003 06:46:01



> With this script when I open the file perl.xls Excel 97, 2000 and 2002 crash.
> Now if you change the row:
>
> $worksheet->merge_range("C2:F9", "Hi Excel!", $format);
>
> with:
>
> $worksheet->merge_range("C2:F9", "Hi Excel!", $format1);
>
> The perl.xls works fine!
> The problem, I think, appear when I user the same cell format for the $worksheet->write
> and $worksheet->merge_range.


This doesn't crash my versions of Excel 97 or Excel 2000. Nevertheless, you shouldn't use a format that was used for merge_range() outside of that function.

The documentation for merge_range() states:

The format object that is used with a merge_range() method call is marked internally as being associated with a merged range. As such, it shouldn't be used for other formatting.

However, I'll try to fix this in future releases so that it doesn't cause any more crashes.

John.
--






[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Simone - Apr 29th 2003 07:50:16


>
> This doesn't crash my versions of Excel
> 97 or Excel 2000.

The script doesn't crash whith stable version 0.41 ...

> However, I'll try to fix this in future
> releases so that it doesn't cause any
> more crashes.

Thanks a lot; great module!


[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Apr 29th 2003 15:57:28


>
> % This doesn't crash my versions of Excel 97 or Excel 2000.
>
> The script doesn't crash whith stable version 0.41 ...

Sorry, I was wrong. It does crash my versions of Excel 97 and 2000. :-(

So for now, users of the pre-release should stick with the suggestion in the docs and use separate formats for merged and non-merged cells. It will be fixed in an upcoming release.

This bug doesn't affect the Excel 5 versions of the module.

John.
--




[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by Cliff Steele - Jun 2nd 2003 01:19:35


> Another pre-release version of
> Spreadsheet::WriteExcel with Excel97
> support is now available. This release
> fixes a bug which, in some cases, could
> cause Excel to crash when using Unicode
> strings. Thanks to Dmitry Kochurov for
> the bug report.
>
> This release also changes the default
> format of the Unicode string required by
> the write_unicode() method. The string
> must now be big-endian UTF-16 instead of
> little-endian UTF-16. This change is
> mainly to make it easier to use other
> CPAN modules which generally default to
> handling UTF-16 data as big-endian
> regardless of the platform. UTF-16LE
> data can be written via the new
> write_unicode_le() method.
>
> See the release notes for further
> details. Here are some screenshots of
> Cyrillic and Japanese text created using
> the examples in the release notes.
> The tar file can be downloaded from
> here.
>
>
>
> The pre-release has been tested with
> Excel97 and Excel2000 and
> Gnumeric. Excel5 files and OpenOffice
> are not supported in this release.
> Porters should ignore this version of
> the module and wait for the
> official
> CPAN release.
>
> John.
> --
This is a really good module that I have used extensively for some time. I do however observe a significant increase in CPU usage in this version compared to the current stable release. In my tests, a sheet that took 300 CPU seconds to produce previously not takes almost 2100! Any thoughts? Anyone else seen this behaviour?

Cliff


--
Cliff

[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Jun 3rd 2003 16:24:23


> I do
> however observe a significant increase in CPU usage in this version compared to
> the current stable release. In my tests, a sheet that took 300 CPU seconds to
> produce previously not takes almost 2100!
> Any thoughts? Anyone else seen this behaviour?

This is a little worrying. I was a aware of a small slowdown in average size files but it is clearly significant for much larger files.

I'll look into it.

John.
--





[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by EmKay - Jun 8th 2003 13:47:59

John,

The pre-release doesn't seem to support write_comment anymore. Are you planning to support that? If so, when?


> Another pre-release version of
> Spreadsheet::WriteExcel with Excel97
> support is now available. This release
> fixes a bug which, in some cases, could
> cause Excel to crash when using Unicode
> strings. Thanks to Dmitry Kochurov for
> the bug report.
>
> This release also changes the default
> format of the Unicode string required by
> the write_unicode() method. The string
> must now be big-endian UTF-16 instead of
> little-endian UTF-16. This change is
> mainly to make it easier to use other
> CPAN modules which generally default to
> handling UTF-16 data as big-endian
> regardless of the platform. UTF-16LE
> data can be written via the new
> write_unicode_le() method.
>
> See the release notes for further
> details. Here are some screenshots of
> Cyrillic and Japanese text created using
> the examples in the release notes.
> The tar file can be downloaded from
> here.
>
>
>
> The pre-release has been tested with
> Excel97 and Excel2000 and
> Gnumeric. Excel5 files and OpenOffice
> are not supported in this release.
> Porters should ignore this version of
> the module and wait for the
> official
> CPAN release.
>
> John.
> --


[reply] [top]

---

[»] Re: Spreadsheet::WriteExcel - Excel97 Pre-release 5
by John McNamara - Jun 8th 2003 15:02:34


> The pre-release doesn't seem to support
> write_comment anymore. Are you planning
> to support that? If so, when?


The write_comment() method and outlining are missing from the current Excel 97 pre-release.

Excel 97 comments are significantly different from Excel 5 comments so it needs a bit of work.

They will be added in the next pre-release which will follow the next (and probably final) Excel 5 CPAN release.

I am currently trying to fit in various open source development requirements with the more pressing requirements of a new born baby. So it will probably be about 4 months before this feature is added.

John.
--



[reply] [top]

---

[»] Import to MS Access
by Michael Wulff - Mar 24th 2003 08:33:59

We want to import Excel files created by Spreadsheet::WriteExcel from MS Access. I realize that the BUGS section of the documentation says "The Excel files that are produced by this module are not compatible with MS Access." Is there a chance that this will work in one of the next releases? Workaround so far: Open the Excel file and save it.

[reply] [top]

[»] Re: Import to MS Access
by John McNamara - Mar 24th 2003 16:05:06


> We want to import Excel files created by Spreadsheet::WriteExcel from MS Access.

Along with the standard cell data that Excel stores it also saves an exo-skeletal DB structure that that can be used for quick lookup of individual cells.

Excel doesn't strictly require this additional structure (although it does speed-up reading of a file). However, as far as I can tell, MS Access does require it.

Programmatically this structure is difficult to generate without incurring a large penalty in either speed or memory. As such Spreadsheet::WriteExcel avoids creating it. This is unlikely to change in future releases.

In addition to your workaround it is possible to write data from Perl directly to an Excel file using DBI with DBD::ADO or DBD::ODBC.

John.
--



[reply] [top]

---

[»] Re: Import to MS Access
by John McNamara - Mar 27th 2003 00:33:05


> In addition to your workaround it is possible to write data from Perl
> directly to an Excel file using DBI with DBD::ADO or DBD::ODBC.


Sorry, that should say "directly to an Access file using DBI with DBD::ADO or DBD::ODBC."

John.
--


[reply] [top]

---

[»] Grief with mod_perl 2.0
by Tom - Mar 17th 2003 10:34:26

I have been testing Spreadsheet::WriteExcel 0.40 on three platforms (RH 8 Linux, NT 4 and XP), all running under Apache with mod_perl. My CGI program generates a spreadsheet and returns it via STDOUT. I was consistently getting blank spreadsheets on the NT system (Apache 1.3/mod_perl) until I saw John's documentation recommending to duplicate STDOUT:

tie *XLS 'Apache' ;
binmode(*XLS);
my $workbook=Spreadsheet::WriteExcel->new(\*XLS);

.. instead of the original ..

binmode(STDOUT);
my $workbook=Spreadsheet::WriteExcel->new(\*STDOUT);

I don't know why this problem only showed up on one of my three mod_perl platforms, but it did fix the problem for it. However, this method does not seem to be supported on Mod_Perl 2; there is no TIEHANDLE method in Apache in MP2 from what I can find.

Is this code sti