3
#######################################################################
5
# chartex - A utility to extract charts from an Excel file for
6
# insertion into a Spreadsheet::WriteExcel file.
8
# reverse('�'), September 2004, John McNamara, jmcnamara@cpan.org
10
# Documentation after __END__
15
use OLE::Storage_Lite;
23
my $chart_name = 'chart';
31
# Do the Getopt and Pod::Usage routines.
36
'chart=s' => \$chart_name,
39
pod2usage(1) if $help;
40
pod2usage(-verbose => 2) if $man;
43
# From the Pod::Usage pod:
44
# If no arguments were given, then allow STDIN to be used only
45
# if it's not connected to a terminal (otherwise print usage)
46
pod2usage() if @ARGV == 0 && -t STDIN;
51
# Check that the file can be opened because OLE::Storage_Lite won't tell us.
52
# Possible race condition here. Could fix with latest OLE::Storage_Lite. TODO.
56
open TMP, $file or die "Couldn't open $file. $!\n";
59
my $ole = OLE::Storage_Lite->new($file);
60
my $book97 = pack 'v*', unpack 'C*', 'Workbook';
61
my $workbook = ($ole->getPpsSearch([$book97], 1, 1))[0];
63
die "Couldn't find Excel97 data in file $file.\n" unless $workbook;
66
# Write the data to a file so that we can access it with read().
67
my $tmpfile = IO::File->new_tmpfile();
70
my $biff = $workbook->{Data};
71
print {$tmpfile} $biff;
79
# Read the file record by record and look for a chart BOF record.
81
while (read $tmpfile, $header, 4) {
83
my ($record, $length) = unpack "vv", $header;
86
read $tmpfile, $data, $length;
89
if ($record == 0x0085) {
90
push @sheetnames, substr $data, 8;
94
if ($record == 0x0017) {
95
my $count = unpack 'v', $data;
97
for my $i (1 .. $count) {
98
my @tmp = unpack 'vvv', substr($data, 2 +6*($i-1));
105
if ($record == 0x0809) {
106
my $type = unpack 'xx v', $data;
108
if ($type == 0x0020) {
109
my $filename = sprintf "%s%02d.bin", $chart_name, $chart_index;
110
open CHART, ">$filename" or die "Couldn't open $filename: $!";
112
printf "\nExtracting \"%s\" to %s", $sheetnames[$sheet_index],
121
print CHART $header, $data;
125
if ($record == 0x000A) {
132
print "\n\n", ('=' x 60), "\n";
133
print "Add the following near the start of your program.\n";
134
print "Change variable name \$worksheet if required.\n\n";
136
for my $aref (@exrefs) {
137
my $sheet1 = $sheetnames[$aref->[1]];
138
my $sheet2 = $sheetnames[$aref->[2]];
142
if ($sheet1 ne $sheet2) {
143
$range = $sheet1 . ":" . $sheet2;
149
$range = "'$range'" if $range =~ /[^\w:]/;
151
print " \$worksheet->store_formula(\"=$range!A1\");\n";
162
chartex - A utility to extract charts from an Excel file for insertion into a Spreadsheet::WriteExcel file.
166
This program is used for extracting one or more charts from an Excel file in binary format. The charts can then be included in a C<Spreadsheet::WriteExcel> file.
168
See the C<add_chart_ext()> section of the Spreadsheet::WriteExcel documentation for more details.
173
chartex [--chartname --help --man] file.xls
176
--chartname -c The root name for the extracted charts,
184
=item B<--chartname or -c>
186
This sets the root name for the extracted charts, defaults to "chart". For example:
190
Extracting "Chart1" to chart01.bin
193
$ chartex -c mychart file.xls
195
Extracting "Chart1" to mychart01.bin
197
=item B<--help or -h>
199
Print a brief help message and exits.
204
Prints the manual page and exits.
211
John McNamara jmcnamara@cpan.org
221
� MMV, John McNamara.
223
All Rights Reserved. This program is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.