Spreadsheet::WriteExcel pre-release with Autofilters

[jmcnamara]

Hi,

A pre-release of the next version of Spreadsheet::WriteExcel with
support for autofilters is now available:

http://homepage.eircom.net/~jmcnamara/perl/prerel/Spreadsheet-WriteExcel-2.19.3.tar.gz

This work was also sponsored by Cassens Transport.

Here is a small example:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my $workbook = Spreadsheet::WriteExcel->new('autotest.xls');
my $worksheet = $workbook->add_worksheet();

$worksheet->write_col('A1',
[
['a', 'b', 'c'],
[ 1, 1, 1 ],
[ 2, 2, 2 ],
[ 3, 3, 3 ],
[ 4, 4, 4 ],
]
);

$worksheet->autofilter('A1:C5');

__END__

There is also a autofilter.pl example program in the charts dir of the
distro. Here is a screenshot of the output:

http://homepage.eircom.net/~jmcnamara/perl/autofilter.jpg

NOTE, this inserts the autofilter drop down boxes and sets the
required range.

It doesn't, as yet, apply any conditions to the filter. This will be
added in a later release. In addition hidden and unhidden rows due to
the filter condition will have to be set (programmatically) by the
user since it isn't part of the file format. It is something that
Excel calculates at runtime.

There are some failing tests during "make test" but you can ignore
them for now.

This has been one of the most requested features for quite some time
so give it a try and let me know if you encounter any problems. :-)

John.
--

[LuckyJ]

Hi John,

First of all, thanks for this awesome module and all the work you have
done. You're saving lives.

But, I just tried to use this feature and encountered an issue. I'll
try and describe it as best I can:

If I have a worksheet with a hash mark, aka the pound sign: #, the
spreadsheet will fail creation at the END (not on the actual
autofilter function call).

The error given is:

Unknown sheet name ####-#### in formula at /usr/lib/perl5/site_perl/
5.8/Spreadseet/WriteExcel/Workbook.pm line 836

I do not have a worksheet named that name. I have one close to it: "8-
####-####". If I change the worksheet name to something other than a
name with # signs in it, the spreadsheet completes just fine and the
function works.

Any thoughts?

Thanks,
Jason

[LuckyJ]

Actually, I take that back. So far, I have to replace the # in a sheet
(tab) name with a NUMBER. Any other character fails. As well as other
valid sheet name special characters, like %.

Weird...

[Tim Allwine]

jmcnamara wrote:
> Hi,
>
> A pre-release of the next version of Spreadsheet::WriteExcel with
> support for autofilters is now available:
>
> http://homepage.eircom.net/~jmcnamara/perl/prerel/Spreadsheet-WriteExcel-2.19.3.tar.gz
>
> This work was also sponsored by Cassens Transport.
>

$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00_IEEE_double..........ok

t/01_add_worksheet........ok

t/02_merge_formats........ok

t/03_cleanup..............ok

t/11_date_time............ok

t/12_date_only............ok

t/13_date_seconds.........ok

t/21_escher...............ok 1/41

# Failed test ' _store_mso_client_anchor(A1)'
# in t/21_escher.t at line 376.
# got: '00 00 10 F0 12 00 00 00 00 00 00 00 01 00 F0 00 00 00
1E 00 03 00 F0 00 04 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 00 00 1E 00
03 00 F0 00 04 00 78 00'
t/21_escher...............NOK 24

# Failed test ' _store_mso_client_anchor(A2)'
t/21_escher...............NOK 25# in t/21_escher.t at line 395.

# got: '00 00 10 F0 12 00 00 00 01 00 01 00 01 00 F0 00 00 00
69 00 03 00 F0 00 04 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 00 00 69 00
03 00 F0 00 04 00 C4 00'

# Failed test ' _store_mso_client_anchor(A3)'
# in t/21_escher.t at line 414.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 01 00 F0 00 01 00
69 00 03 00 F0 00 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 01 00 69 00
03 00 F0 00 05 00 C4 00'
t/21_escher...............NOK 26

# Failed test ' _store_mso_client_anchor(A65534)'
# in t/21_escher.t at line 433.
# got: '00 00 10 F0 12 00 00 00 FD FF FD FF 01 00 F0 00 F9 FF
3C 00 03 00 F0 00 FD FF'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 F9 FF 3C 00
03 00 F0 00 FD FF 97 00'
t/21_escher...............NOK 27

# Failed test ' _store_mso_client_anchor(A65535)'
t/21_escher...............NOK 28# in t/21_escher.t at line 452.

# got: '00 00 10 F0 12 00 00 00 FE FF FE FF 01 00 F0 00 FA FF
3C 00 03 00 F0 00 FE FF'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 FA FF 3C 00
03 00 F0 00 FE FF 97 00'

# Failed test ' _store_mso_client_anchor(A65536)'
# in t/21_escher.t at line 471.
# got: '00 00 10 F0 12 00 00 00 FF FF FF FF 01 00 F0 00 FB FF
1E 00 03 00 F0 00 FF FF'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 FB FF 1E 00
03 00 F0 00 FF FF 78 00'
t/21_escher...............NOK 29

# Failed test ' _store_mso_client_anchor(IT3)'
t/21_escher...............NOK 30# in t/21_escher.t at line 490.

# got: '00 00 10 F0 12 00 00 00 02 00 02 00 FA 00 10 03 01 00
69 00 FC 00 10 03 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 FA 00 10 03 01 00 69 00
FC 00 10 03 05 00 C4 00'

# Failed test ' _store_mso_client_anchor(IU3)'
# in t/21_escher.t at line 509.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 FB 00 10 03 01 00
69 00 FD 00 10 03 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 FB 00 10 03 01 00 69 00
FD 00 10 03 05 00 C4 00'
t/21_escher...............NOK 31

# Failed test ' _store_mso_client_anchor(IV3)'
# in t/21_escher.t at line 528.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 FC 00 10 03 01 00
69 00 FE 00 10 03 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 FC 00 10 03 01 00 69 00
FE 00 10 03 05 00 C4 00'
t/21_escher...............NOK 32

# Failed test ' _store_mso_client_anchor(A3). Cell offsets changed.'
t/21_escher...............NOK 33# in t/21_escher.t at line 549.

# got: '00 00 10 F0 12 00 00 00 02 00 02 00 01 00 20 01 01 00
88 00 03 00 20 01 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 20 01 01 00 88 00
03 00 20 01 05 00 E2 00'

# Failed test ' _store_mso_client_anchor(A3). Dimensions changed.'
# in t/21_escher.t at line 570.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 01 00 F0 00 01 00
69 00 07 00 F0 00 0A 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 01 00 69 00
07 00 F0 00 0A 00 1E 00'
t/21_escher...............NOK 34

# Failed test ' _store_mso_client_anchor(A3). Dimensions changed.'
t/21_escher...............NOK 35# in t/21_escher.t at line 591.

# got: '00 00 10 F0 12 00 00 00 02 00 02 00 01 00 F0 00 01 00
69 00 07 00 F0 00 0A 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 01 00 69 00
07 00 F0 00 0A 00 1E 00'

# Failed test ' _store_mso_client_anchor(F3). Col width changed.'
# in t/21_escher.t at line 613.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 06 00 6A 00 01 00
69 00 06 00 F2 03 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 06 00 6A 00 01 00 69 00
06 00 F2 03 05 00 C4 00'
t/21_escher...............NOK 36

# Failed test ' _store_mso_client_anchor(K3). Col width changed.'
# in t/21_escher.t at line 635.
# got: '00 00 10 F0 12 00 00 00 02 00 02 00 0B 00 D1 01 01 00
69 00 0F 00 B0 00 05 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 0B 00 D1 01 01 00 69 00
0F 00 B0 00 05 00 C4 00'
t/21_escher...............NOK 37

# Failed test ' _store_mso_client_anchor(A6). Row height changed.'
# in t/21_escher.t at line 660.
# got: '00 00 10 F0 12 00 00 00 05 00 05 00 01 00 F0 00 04 00
69 00 03 00 F0 00 0A 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 04 00 69 00
03 00 F0 00 0A 00 E2 00'
t/21_escher...............NOK 38

# Failed test ' _store_mso_client_anchor(A15). Row height changed.'
t/21_escher...............NOK 39# in t/21_escher.t at line 682.

# got: '00 00 10 F0 12 00 00 00 0E 00 0E 00 01 00 F0 00 0D 00
69 00 03 00 F0 00 0E 00'
# expected: '00 00 10 F0 12 00 00 00 03 00 01 00 F0 00 0D 00 69 00
03 00 F0 00 0E 00 CD 00'
# Looks like you planned 41 tests but ran 1 extra.
# Looks like you failed 16 tests of 42 run.
t/21_escher...............dubious

Test returned status 16 (wstat 4096, 0x1000)
DIED. FAILED tests 24-39, 42
Failed 17/41 tests, 58.54% okay
t/22_mso_drawing_group....Use of uninitialized value in unpack at
t/22_mso_drawing_group.t line 876.

# Failed test ' Sheet1: 1 comments.'
# in t/22_mso_drawing_group.t at line 66.
# got: ''
# expected: 'EB 00 5A 00 0F 00 00 F0 52 00 00 00 00 00 06 F0 18 00
00 00 02 04 00 00 02 00 00 00 02 00 00 00 01 00 00 00 01 00 00 00 02 00
00 00 33 00 0B F0 12 00 00 00 BF 00 08 00 08 00 81 01 09 00 00 08 C0 01
40 00 00 08 40 00 1E F1 10 00 00 00 0D 00 00 08 0C 00 00 08 17 00 00 08
F7 00 00 10'
Can't use an undefined value as an ARRAY reference at
t/22_mso_drawing_group.t line 77.
t/22_mso_drawing_group....NOK 1# Looks like you planned 34 tests but
only ran 1.
# Looks like you failed 1 test of 1 run.
# Looks like your test died just after 1.
t/22_mso_drawing_group....dubious

Test returned status 255 (wstat 65280, 0xff00)
DIED. FAILED tests 1-34
Failed 34/34 tests, 0.00% okay
t/23_note.................ok

t/24_txo..................ok

Failed Test Stat Wstat Total Fail List of Failed
-------------------------------------------------------------------------------
t/21_escher.t 16 4096 41 17 24-39 42
t/22_mso_drawing_group.t 255 65280 34 67 1-34
Failed 2/11 test scripts. 49/788 subtests failed.
Files=11, Tests=788, 2 wallclock secs ( 1.35 cusr + 0.23 csys = 1.58 CPU)
Failed 2/11 test programs. 49/788 subtests failed.
make: *** [test_dynamic] Error 255

I'm trying to build this on an Intel Mac OS X 10.4.10

-Tim

[LuckyJ]

If I use say, a comma instead of a #, I get this error (slightly
different):

Couldn't parse formula: =e00,,,,,!A1 at /usr/lib/perl5/site_perl/5.8/
Spreadsheet/WriteExcel/Workbook.pm line 836

[jmcnamara]

On Jul 20, 6:21 pm, LuckyJ <jason.luttg...@gmail.com> wrote:
> If I have a worksheet with a hash mark, aka the pound sign: #, the
> spreadsheet will fail creation at the END (not on the actual
> autofilter function call).

Hi,

I wasn't aware of that but I know what causes it. I'll fix it in the
next pre-release.

John.
--

[jmcnamara]

On Jul 20, 6:31 pm, Tim Allwine <tallw...@oreilly.com> wrote:
>
> $ make test
>
> ...

>
> # Failed test ' _store_mso_client_anchor(A1)'
> # in t/21_escher.t at line 376.

Hi Tim,

There are a lot of failing tests but they are "mainly harmless".

They should all pass in the next pre-release.

John.
--

[LuckyJ]

Hey John, there also seems to be an issue with insert_bitmap and using
the autofilter. After putting an autofilter in, the bitmap disappears.

If you run inset_bitmap after the autofilter, the script seems to hang
and never generate a spreadsheet...

Any thoughts?

[LuckyJ]

By the way, here is the info from your bug report perl script:

Perl version : 5.008007
OS name : cygwin
Module versions: (not all are required)
Spreadsheet::WriteExcel 2.19.3
Parse::RecDescent 1.94
File::Temp 0.16
OLE::Storage_Lite 0.14
IO::Stringy 2.110

[jmcnamara]

On Jul 20, 11:15 pm, LuckyJ <jason.luttg...@gmail.com> wrote:
> Hey John, there also seems to be an issue with insert_bitmap and using
> the autofilter. After putting an autofilter in, the bitmap disappears.

Hi,

The insert_bitmap() method generates Excel 5 style images that work
with Excel 97 style objects such as comments or autofilters.

Use the new insert_image() method instead with a PNG image.

Insert_bitmap() will be deprecated and replaced internally with a call
to insert_image() when I get some time to improve the image support.

So for now use insert_image() and PNGs.

> If you run inset_bitmap after the autofilter, the script seems to hang
> and never generate a spreadsheet...

I'll have a look at that.

John.
--

[LuckyJ]

On Jul 21, 5:38 am, jmcnamara <jmcnam...@cpan.org> wrote:
> Hi,
>
> The insert_bitmap() method generates Excel 5 style images that work
> with Excel 97 style objects such as comments or autofilters.
>
> Use the new insert_image() method instead with a PNG image.

Worked like a charm! Thanks.

> > If you run inset_bitmap after the autofilter, the script seems to hang
> > and never generate a spreadsheet...
>
> I'll have a look at that.

Not really a big deal anymore, as the insert_image works great. Thanks
though.