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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
use utf8;
use strict;
use warnings;
use Getopt::Long;
use Excel::ValueReader::XLSX;
# use Excel::Reader::XLSX;
use Spreadsheet::ParseXLSX;
use Data::XLSX::Parser;
# options de la ligne de commande
GetOptions \my %opt,
'xl_file=s', # fichier Excel des comparaisons
'valuereader!',
'ivaluereader!',
'vrlibxml!',
'ivrlibxml!',
'reader!',
'parsexlsx!',
'xparser!',
;
$opt{xl_file} //= "d:/temp/Audit/Stats_acces_2024/Stats_DM_par_semaine_S1_2024.xlsx";
my ($start, $cpu, $system) = (time, times);
valuereader($opt{xl_file}) if $opt{valuereader};
ivaluereader($opt{xl_file})if $opt{ivaluereader};
vrlibxml($opt{xl_file}) if $opt{vrlibxml};
ivrlibxml($opt{xl_file}) if $opt{ivrlibxml};
reader($opt{xl_file}) if $opt{reader};
parsexlsx($opt{xl_file}) if $opt{parsexlsx};
xparser($opt{xl_file}) if $opt{xparser};
my ($end, $ecpu, $esystem) = (time, times);
printf "%d elapsed, %d cpu, %d system\n", $end-$start, $ecpu-$cpu, $esystem-$system;
sub valuereader {
my $xl_file = shift;
warn "using ValueReader\n";
my $rv = Excel::ValueReader::XLSX->new(xlsx => $xl_file);
foreach my $sheet_name ($rv->sheet_names) {
my $vals = $rv->values($sheet_name);
my $n_rows = @$vals;
warn "sheet $sheet_name has $n_rows rows\n";
}
}
sub ivaluereader {
my $xl_file = shift;
warn "using ValueReader iterator\n";
my $rv = Excel::ValueReader::XLSX->new(xlsx => $xl_file);
foreach my $sheet_name ($rv->sheet_names) {
my $it = $rv->ivalues($sheet_name);
my $n_rows = 0;
$n_rows++ while $it->();
warn "sheet $sheet_name has $n_rows rows\n";
}
}
sub vrlibxml {
my $xl_file = shift;
warn "using ValueReader with LibXML\n";
my $rv = Excel::ValueReader::XLSX->new(xlsx => $xl_file, using => 'LibXML');
foreach my $sheet_name ($rv->sheet_names) {
my $vals = $rv->values($sheet_name);
my $n_rows = @$vals;
warn "sheet $sheet_name has $n_rows rows\n";
}
}
sub ivrlibxml {
my $xl_file = shift;
warn "using ValueReader iterator with LibXML\n";
my $rv = Excel::ValueReader::XLSX->new(xlsx => $xl_file, using => 'LibXML');
foreach my $sheet_name ($rv->sheet_names) {
my $it = $rv->ivalues($sheet_name);
my $n_rows = 0;
$n_rows++ while $it->();
warn "sheet $sheet_name has $n_rows rows\n";
}
}
sub reader {
my $xl_file = shift;
warn "using Excel::Reader::XLSX\n";
my $reader = Excel::Reader::XLSX->new();
my $workbook = $reader->read_file($xl_file);
for my $worksheet ( $workbook->worksheets() ) {
my $sheet_name = $worksheet->name();
my @rows;
while ( my $row = $worksheet->next_row() ) {
my @row;
while ( my $cell = $row->next_cell() ) {
push @row, $cell->value();
}
push @rows, \@row;
}
my $n_rows = @rows;
warn "sheet $sheet_name has $n_rows rows\n";
}
}
sub parsexlsx {
my $xl_file = shift;
warn "using Spreadsheet::ParseXLSX\n";
my $parser = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse($xl_file) or die $parser->error;
for my $worksheet ( $workbook->worksheets() ) {
my $sheet_name = $worksheet->get_name();
my ( $row_min, $row_max ) = $worksheet->row_range();
warn "sheet $sheet_name has $row_max rows\n";
}
}
sub xparser {
my $xl_file = shift;
warn "using Data::XLSX::Parser\n";
my $parser = Data::XLSX::Parser->new;
my @rows;
$parser->add_row_event_handler(sub {
my ($row) = @_;
push @rows, $row;
});
$parser->open($xl_file);
foreach my $sheet_name ($parser->workbook->names) {
@rows = ();
$parser->sheet_by_rid( "rId" . $parser->workbook->sheet_id( $sheet_name ) );
my $n_rows = @rows;
warn "sheet $sheet_name has $n_rows rows\n";
@rows = ();
}
}
__END__
using ValueReader
sheet Stats_DM_par_semaine_S1_2024 has 800131 rows
40 elapsed, 32 cpu, 0 system
using ValueReader iterator
sheet Stats_DM_par_semaine_S1_2024 has 800131 rows
34 elapsed, 30 cpu, 0 system
using ValueReader with LibXML
sheet Stats_DM_par_semaine_S1_2024 has 800131 rows
101 elapsed, 83 cpu, 0 system
using ValueReader iterator with LibXML
sheet Stats_DM_par_semaine_S1_2024 has 800131 rows
91 elapsed, 80 cpu, 0 system
using Spreadsheet::ParseXLSX
sheet Stats_DM_par_semaine_S1_2024 has 800130 rows
1272 elapsed, 870 cpu, 4 system
using Data::XLSX::Parser
sheet Stats_DM_par_semaine_S1_2024 has 800131 rows
125 elapsed, 107 cpu, 1 system
|
