xls2csv.bat
3.32 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
#!/bin/env perl
BEGIN {
use File::Basename;
unshift(@INC, dirname $0);
}
use strict;
use Spreadsheet::ParseExcel;
# declare some varibles local
my($row, $col, $sheet, $cell, $usage, $basename, $sheetnumber, $filename);
##
## Usage information
##
$usage = <<EOF;
xls2csv.pl <excel file> [<output file>] [<worksheet number>]
Translate the Microsoft Excel spreadsheet file contained in
<excel file> into comma separated value format (CSV) and store
in <output file>.
If <output file> is not specified, the output file will have the
same name as the input file with '.xls' or '.XLS' (if any)
removed and '.csv' appended.
If no worksheet number is given, each worksheet will be written to
a separate file with the name '<output file>_<worksheet name>.csv'.
EOF
##
## parse arguments
##
if(!defined($ARGV[0]))
{
print $usage;
exit 1;
}
$basename = $ARGV[1];
$basename =~ s/.csv//;
if ($basename eq "")
{
my @path;
@path = split(/[\/\\]/, $ARGV[0]); # split on file separator
$basename = $path[$#path];
$basename =~ s/.xls//i;
}
if(defined($ARGV[2]) )
{
$sheetnumber = $ARGV[2];
die "Sheetnumber must be an integer larger than 0." if $sheetnumber < 1;
}
##
## open spreadsheet
##
my $oExcel = new Spreadsheet::ParseExcel;
print "Loading $ARGV[0] ...\n";
open(FH, "<$ARGV[0]") or die "Unable to open file '$ARGV[0]'.\n";
close(FH);
my $oBook = $oExcel->Parse($ARGV[0]);
print "\n";
print "Orignal Filename :", $oBook->{File} , "\n";
print "Number of Sheets :", $oBook->{SheetCount} , "\n";
print "Author :", $oBook->{Author} , "\n";
print "\n";
my @sheetlist = (@{$oBook->{Worksheet}});
if (defined($sheetnumber))
{
@sheetlist=($sheetlist[$sheetnumber-1]);
}
##
## iterate across each worksheet, writing out a separat csv file
##
my $i=0;
foreach my $sheet (@sheetlist)
{
$i++;
my $sheetname = $sheet->{Name};
if(defined($sheetnumber))
{
$filename = "${basename}.csv";
}
else
{
$filename = "${basename}_${sheetname}.csv";
}
print "Writing Sheet number $i ('$sheetname') to file '$filename'\n";
open(OutFile,">$filename");
my $cumulativeBlankLines=0;
my $minrow = $sheet->{MinRow};
my $maxrow = $sheet->{MaxRow};
my $mincol = $sheet->{MinCol};
my $maxcol = $sheet->{MaxCol};
print "Minrow=$minrow Maxrow=$maxrow Mincol=$mincol Maxcol=$maxcol\n";
for(my $row = $minrow; $row <= $maxrow; $row++)
{
my $outputLine = "";
for(my $col = $mincol; $col <= $maxcol; $col++)
{
my $cell = $sheet->{Cells}[$row][$col];
if( defined($cell) )
{
$_=$cell->Value; #{Val};
# convert '#NUM!' strings to missing (empty) values
s/#NUM!//;
# escape double-quote characters in the data since
# they are used as field delimiters
s/\"/\\\"/g;
}
else
{
$_ = '';
}
$outputLine .= "\"" . $_ . "\"" if(length($_)>0);
# separate cells with commas
$outputLine .= "," if( $col != $maxcol) ;
}
#$outputLine =~ s/[, ]+$//g; ## strip off trailing blanks and commas
# skip blank/empty lines
if( $outputLine =~ /^[, ]*$/ )
{
$cumulativeBlankLines++
}
else
{
print OutFile "$outputLine \n"
}
}
close OutFile;
print " (Ignored $cumulativeBlankLines blank lines.)\n"
if ($cumulativeBlankLines);
print "\n";
}