blib/lib/Data/Table/Excel.pm

filter: Statementstmt 87.5% 14 / 16 Branchbran 50.0% 1 / 2 Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 6 / 6 Podpod n/a totaltotal 87.5% 21 / 24 CC 2 SCAR 3.2
CC
2
Cov
83%
BEGIN
2.1
BEGIN
1.3
BEGIN
1.0
CRAP 1.4
↑ Summary
1 package Data::Table::Excel;
2 1 ▶BEGIN { die "Your perl version is old, see README for instructions" if $] < 5.005; }
Subroutine BEGIN: called CC=2 SCAR=7.6
Branch: 1 branch
BranchTrueFalseTotal
if $] < "5.005" 0 1 1
3  
4 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use Data::Table;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 1 ▶use Spreadsheet::WriteExcel;
Subroutine BEGIN: called CC=1 SCAR=0.0
7 1 ▶use Spreadsheet::ParseExcel;
Subroutine BEGIN: called CC=1 SCAR=0.0
8 #use Spreadsheet::XLSX;
9 0 ▶use Spreadsheet::ParseXLSX;
Subroutine BEGIN: called CC=1 SCAR=2.6
10 use Excel::Writer::XLSX;
11 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
12 use Carp;
13 use Exporter 'import';
14  
15 @ISA = qw(Exporter AutoLoader);
16 # Items to export into callers namespace by default. Note: do not export
17 # names by default without a very good reason. Use EXPORT_OK instead.
18 # Do not simply export all your public functions/methods/constants.
19 @EXPORT = ();
20 @EXPORT_OK = qw(
21 tables2xls xls2tables tables2xlsx xlsx2tables xls2xlsx xlsx2xls is_xlsx excelFileToTable
22 );
23 $VERSION = '0.5';
24  
25 sub xls2tables {
26 my ($fileName, $sheetNames, $sheetIndices) = @_;
27 return excelFileToTable($fileName, $sheetNames, $sheetIndices, '2003');
28 }
29  
30 sub xlsx2tables {
31 my ($fileName, $sheetNames, $sheetIndices) = @_;
32 return excelFileToTable($fileName, $sheetNames, $sheetIndices, '2007');
33 }
34  
35 sub is_xlsx {
36 my $filename=shift;
37 open(IN, $filename) or die "Cannot open $filename!";
38 binmode IN;
39 my $buffer;
40 read(IN, $buffer, 2, 0);
41 close(IN);
42 return uc($buffer) eq 'PK';
43 }
44  
45 sub excelFileToTable {
46 my ($fileName, $sheetNames, $sheetIndices, $excelFormat) = @_;
47 my %sheetsName = ();
48 my %sheetsIndex = ();
49 if (defined($sheetNames)) {
50 $sheetNames=[$sheetNames] if (ref($sheetNames) eq 'SCALAR');
51 if (ref($sheetNames) eq 'ARRAY') {
52 foreach my $name (@$sheetNames) {
53 $sheetsName{$name} = 1;
54 }
55 }
56 } elsif (defined($sheetIndices)) {
57 $sheetIndices=[$sheetIndices] if (ref($sheetIndices) eq 'SCALAR');
58 if (ref($sheetIndices) eq 'ARRAY') {
59 foreach my $idx (@$sheetIndices) {
60 $sheetsIndex{$idx} = 1;
61 }
62 }
63 }
64 my $excel = undef;
65 if (!defined($excelFormat)) {
66 $excelFormat=is_xlsx($fileName)?'2007':'2003';
67 }
68 if ($excelFormat eq '2003') {
69 $excel = Spreadsheet::ParseExcel::Workbook->Parse($fileName);
70 } elsif ($excelFormat eq '2007') {
71 #$excel = Spreadsheet::XLSX->new($fileName);
72 my $parser=Spreadsheet::ParseXLSX->new;
73 $excel = $parser->parse($fileName);
74 } else {
75 croak "Unrecognized Excel format, must be either 2003 or 2007!";
76 }
77 my @tables = ();
78 my @sheets = ();
79 my @column_headers = ();
80 my $num = 0;
81 foreach my $sheet (@{$excel->{Worksheet}}) {
82 $num++;
83 next if ((scalar keys %sheetsName) && !defined($sheetsName{$sheet->{Name}}));
84 next if ((scalar keys %sheetsIndex) && !defined($sheetsIndex{$num}));
85 next unless defined($sheet->{MinRow}) && defined($sheet->{MaxRow}) && defined($sheet->{MinCol}) && defined($sheet->{MaxRow});
86 push @sheets, $sheet->{Name};
87 #printf("Sheet: %s\n", $sheet->{Name});
88 $sheet->{MaxRow} ||= $sheet->{MinRow};
89 $sheet->{MaxCol} ||= $sheet->{MinCol};
90 my @header = ();
91 foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
92 my $cel=$sheet->{Cells}[$sheet->{MinRow}][$col];
93 push @header, defined($cel)?$cel->{Val}:undef;
94 }
95 my $s = join($Data::Table::DEFAULTS{CSV_DELIMITER}, map {Data::Table::csvEscape($_)} @header);
96 my $t = undef;
97 my $hasColumnHeader=Data::Table::fromFileIsHeader($s, $Data::Table::DEFAULTS{CSV_DELIMITER});
98 push @column_headers, $hasColumnHeader;
99 if ($hasColumnHeader) {
100 $t = new Data::Table([], \@header, 0);
101 } else {
102 my @newHeader = map {"col$_"} (1..($sheet->{MaxCol}-$sheet->{MinCol}+1));
103 # name each column as col1, col2, .. etc
104 $t = new Data::Table([\@header], \@newHeader, 0);
105 }
106 foreach my $row (($sheet->{MinRow}+1) .. $sheet->{MaxRow}) {
107 my @one = ();
108 foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
109 my $cel=$sheet->{Cells}[$row][$col];
110 push @one, defined($cel)?$cel->{Val}:undef;
111 }
112 $t->addRow(\@one);
113 }
114 push @tables, $t;
115 }
116 return (\@tables, \@sheets, \@column_headers);
117 }
118  
119 # color palette is defined in
120 # http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/doc/palette.html
121 sub oneTable2Worksheet {
122 my ($workbook, $t, $name, $colors, $portrait, $column_header) = @_;
123 $column_header = 0 unless defined($column_header);
124 # Add a worksheet
125 my $worksheet = $workbook->add_worksheet($name);
126 $portrait=1 unless defined($portrait);
127 #my @BG_COLOR=(26,47,44);
128 my @BG_COLOR=(44, 9, 30);
129 @BG_COLOR=@$colors if ((ref($colors) eq "ARRAY") && (scalar @$colors==3));
130 my $fmt_header= $workbook->add_format();
131 $fmt_header->set_bg_color($BG_COLOR[2]);
132 $fmt_header->set_bold();
133 $fmt_header->set_color('white');
134 my $fmt_odd= $workbook->add_format();
135 $fmt_odd->set_bg_color($BG_COLOR[0]);
136 my $fmt_even= $workbook->add_format();
137 $fmt_even->set_bg_color($BG_COLOR[1]);
138 my @FORMAT = ($fmt_odd, $fmt_even);
139  
140 my @header=$t->header;
141 my $offset=($column_header)? 1:0;
142 if ($portrait) {
143 if ($column_header) {
144 for (my $i=0; $i<@header; $i++) {
145 $worksheet->write(0, $i, $header[$i], $fmt_header);
146 }
147 }
148 for (my $i=0; $i<$t->nofRow; $i++) {
149 for (my $j=0; $j<$t->nofCol; $j++) {
150 $worksheet->write($i+$offset, $j, $t->elm($i,$j), $FORMAT[$i%2]);
151 }
152 }
153 } else {
154 if ($column_header) {
155 for (my $i=0; $i<@header; $i++) {
156 $worksheet->write($i, 0, $header[$i], $fmt_header);
157 }
158 }
159 for (my $i=0; $i<$t->nofRow; $i++) {
160 for (my $j=0; $j<$t->nofCol; $j++) {
161 $worksheet->write($j, $i+$offset, $t->elm($i,$j), $FORMAT[$i%2]);
162 }
163 }
164 }
165 }
166  
167 sub tables2excelFile {
168 my ($fileName, $tables, $names, $colors, $portrait, $excelFormat, $column_headers) = @_;
169 confess("No table is specified!\n") unless (defined($tables)&&(scalar @$tables));
170 $names =[] unless defined($names);
171 $colors=[] unless defined($colors);
172 $portrait=[] unless defined($portrait);
173 $column_headers=[1] unless defined($column_headers);
174 my $workbook = undef;
175 $excelFormat='2007' unless defined($excelFormat);
176 if ($excelFormat eq '2003') {
177 $workbook = Spreadsheet::WriteExcel->new($fileName);
178 } elsif ($excelFormat eq '2007') {
179 $workbook = Excel::Writer::XLSX->new($fileName);
180 } else {
181 croak "Unrecognized Excel format, must be either 2003 or 2007!";
182 }
183 $portrait=[] unless defined($portrait);
184 my ($prevColors, $prevPortrait, $prevColumnHeader) = (undef, undef, undef);
185 for (my $i=0; $i<@$tables; $i++) {
186 my $myColor=$colors->[$i];
187 $myColor=$prevColors if (!defined($myColor) && defined($prevColors));
188 $prevColors=$myColor;
189 my $myPortrait=$portrait->[$i];
190 $myPortrait=$prevPortrait if (!defined($myPortrait) && defined($prevPortrait));
191 $prevPortrait=$myPortrait;
192 my $mySheet = $names->[$i] ? $names->[$i]:"Sheet".($i+1);
193 my $myColumnHeader = $column_headers->[$i];
194 $myColumnHeader = $prevColumnHeader if (!defined($myColumnHeader) && defined($prevColumnHeader));
195 $prevColumnHeader=$myColumnHeader;
196 oneTable2Worksheet($workbook, $tables->[$i], $mySheet, $myColor, $myPortrait, $myColumnHeader);
197 }
198 }
199  
200 sub tables2xls {
201 my ($fileName, $tables, $names, $colors, $portrait, $column_headers) = @_;
202 tables2excelFile($fileName, $tables, $names, $colors, $portrait, '2003', $column_headers);
203 }
204  
205 sub tables2xlsx {
206 my ($fileName, $tables, $names, $colors, $portrait, $column_headers) = @_;
207 tables2excelFile($fileName, $tables, $names, $colors, $portrait, '2007', $column_headers);
208 }
209  
210 sub xls2xlsx {
211 my ($xlsFile, $xlsxFile) = @_;
212 unless (defined($xlsxFile)) {
213 $xlsxFile = $xlsFile;
214 $xlsxFile =~ s/\.xls$/\.xlsx/i;
215 }
216 my ($tables, $table_names, $column_headers) = xls2tables($xlsFile);
217 tables2xlsx($xlsxFile, $tables, $table_names, undef, undef, $column_headers);
218 }
219  
220 sub xlsx2xls {
221 my ($xlsxFile, $xlsFile) = @_;
222 unless (defined($xlsFile)) {
223 $xlsFile = $xlsxFile;
224 $xlsFile =~ s/\.xlsx$/\.xls/i;
225 }
226 my ($tables, $table_names, $column_headers) = xlsx2tables($xlsxFile);
227 tables2xls($xlsFile, $tables, $table_names, undef, undef, $column_headers);
228 }
229  
230 1;
231  
232 __END__
↑ Summary