line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package RapidApp::Module::Grid::Role::ExcelExport; |
2
|
|
|
|
|
|
|
|
3
|
4
|
|
|
4
|
|
3123
|
use strict; |
|
4
|
|
|
|
|
11
|
|
|
4
|
|
|
|
|
124
|
|
4
|
4
|
|
|
4
|
|
22
|
use warnings; |
|
4
|
|
|
|
|
8
|
|
|
4
|
|
|
|
|
182
|
|
5
|
4
|
|
|
4
|
|
23
|
use Moose::Role; |
|
4
|
|
|
|
|
16
|
|
|
4
|
|
|
|
|
59
|
|
6
|
|
|
|
|
|
|
|
7
|
4
|
|
|
4
|
|
24964
|
use Excel::Writer::XLSX; |
|
4
|
|
|
|
|
617486
|
|
|
4
|
|
|
|
|
201
|
|
8
|
4
|
|
|
4
|
|
1912
|
use RapidApp::Spreadsheet::ExcelTableWriter; |
|
4
|
|
|
|
|
18
|
|
|
4
|
|
|
|
|
167
|
|
9
|
4
|
|
|
4
|
|
37
|
use RapidApp::Util qw(:all); |
|
4
|
|
|
|
|
9
|
|
|
4
|
|
|
|
|
2133
|
|
10
|
|
|
|
|
|
|
require JSON; |
11
|
|
|
|
|
|
|
require Text::CSV; |
12
|
4
|
|
|
4
|
|
29
|
use DateTime; |
|
4
|
|
|
|
|
9
|
|
|
4
|
|
|
|
|
2109
|
|
13
|
|
|
|
|
|
|
|
14
|
|
|
|
0
|
0
|
|
sub BUILD {} |
15
|
|
|
|
|
|
|
before 'BUILD' => sub { |
16
|
|
|
|
|
|
|
my $self = shift; |
17
|
|
|
|
|
|
|
|
18
|
|
|
|
|
|
|
$self->apply_actions( export_to_file => 'export_to_file' ); |
19
|
|
|
|
|
|
|
}; |
20
|
|
|
|
|
|
|
|
21
|
|
|
|
|
|
|
|
22
|
|
|
|
|
|
|
around 'options_menu_items' => sub { |
23
|
|
|
|
|
|
|
my $orig = shift; |
24
|
|
|
|
|
|
|
my $self = shift; |
25
|
|
|
|
|
|
|
|
26
|
|
|
|
|
|
|
my $items = $self->$orig(@_); |
27
|
|
|
|
|
|
|
$items = [] unless (defined $items); |
28
|
|
|
|
|
|
|
|
29
|
|
|
|
|
|
|
push @$items, { |
30
|
|
|
|
|
|
|
text => 'Download As...', |
31
|
|
|
|
|
|
|
hideOnClick => \0, |
32
|
|
|
|
|
|
|
iconCls => 'ra-icon-document-save', |
33
|
|
|
|
|
|
|
menu => RapidApp::JSONFunc->new( func => 'new Ext.ux.RapidApp.AppTab.AppGrid2.ExcelExportMenu', |
34
|
|
|
|
|
|
|
parm => { |
35
|
|
|
|
|
|
|
url => $self->local_url('/export_to_file'), |
36
|
|
|
|
|
|
|
# This shouldn't be required, but the sub menu's loose track of their parents!! |
37
|
|
|
|
|
|
|
buttonId => $self->options_menu_button_Id |
38
|
|
|
|
|
|
|
} |
39
|
|
|
|
|
|
|
) |
40
|
|
|
|
|
|
|
}; |
41
|
|
|
|
|
|
|
|
42
|
|
|
|
|
|
|
return $items; |
43
|
|
|
|
|
|
|
}; |
44
|
|
|
|
|
|
|
|
45
|
|
|
|
|
|
|
my $xlsx_mime= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; |
46
|
|
|
|
|
|
|
my %formats= map { $_->{mime} => $_ } ( |
47
|
|
|
|
|
|
|
{ mime => 'text/csv', file_ext => '.csv', renderer => 'export_render_csv' }, |
48
|
|
|
|
|
|
|
{ mime => 'text/tab-separated-values', file_ext => '.tsv', renderer => 'export_render_tsv' }, |
49
|
|
|
|
|
|
|
{ mime => 'application/json', file_ext => '.json', renderer => 'export_render_json' }, |
50
|
|
|
|
|
|
|
{ mime => $xlsx_mime, file_ext => '.xlsx', renderer => 'export_render_excel' }, |
51
|
|
|
|
|
|
|
); |
52
|
|
|
|
|
|
|
sub export_to_file { |
53
|
0
|
|
|
0
|
0
|
|
my $self = shift; |
54
|
0
|
|
|
|
|
|
my $params = $self->c->req->params; |
55
|
|
|
|
|
|
|
|
56
|
|
|
|
|
|
|
# Determine output format, defaulting to CSV |
57
|
0
|
|
0
|
|
|
|
my $export_format= $formats{$params->{export_format}} || $formats{'text/csv'}; |
58
|
|
|
|
|
|
|
|
59
|
|
|
|
|
|
|
# Determine file name, defaulting to 'export', and apply the default file extension. |
60
|
0
|
|
0
|
|
|
|
my $export_filename = $params->{export_filename} || 'export'; |
61
|
|
|
|
|
|
|
|
62
|
|
|
|
|
|
|
# New: append the current date/time to the export filename: |
63
|
0
|
|
|
|
|
|
my $dt = DateTime->now( time_zone => 'local' ); |
64
|
0
|
|
|
|
|
|
$export_filename .= join('','-',$dt->ymd('-'),'_',$dt->hms('')); |
65
|
|
|
|
|
|
|
|
66
|
|
|
|
|
|
|
$export_filename .= $export_format->{file_ext} |
67
|
0
|
0
|
|
|
|
|
unless substr($export_filename,-length($export_format->{file_ext})) eq $export_format->{file_ext}; |
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
# Clean up params so that AppGrid doesn't get confused |
70
|
0
|
|
|
|
|
|
delete $params->{export_filename}; |
71
|
0
|
|
|
|
|
|
delete $params->{export_format}; |
72
|
|
|
|
|
|
|
|
73
|
|
|
|
|
|
|
# Get the list of desired columns from the query parameters. |
74
|
|
|
|
|
|
|
# If not specified, we use all defined columns. |
75
|
|
|
|
|
|
|
my $columns= ($params->{columns}) |
76
|
|
|
|
|
|
|
? $self->json->decode($params->{columns}) |
77
|
0
|
0
|
|
|
|
|
: $self->column_order; |
78
|
|
|
|
|
|
|
|
79
|
|
|
|
|
|
|
# filter out columns that we can't use, and also build the column definitions for ExcelTableWriter |
80
|
0
|
|
|
|
|
|
my @colDefs = (); |
81
|
0
|
|
|
|
|
|
foreach my $col (@$columns) { |
82
|
0
|
0
|
|
|
|
|
my $field = $self->get_column($col) or die "column $col does not exist in columns hash"; |
83
|
|
|
|
|
|
|
|
84
|
|
|
|
|
|
|
# New: If render_column is defined, use it instead of name |
85
|
0
|
0
|
|
|
|
|
my $colname = $field->render_column ? $field->render_column : $field->name; |
86
|
|
|
|
|
|
|
|
87
|
0
|
0
|
|
|
|
|
next if ($field->name eq 'icon'); |
88
|
0
|
0
|
|
|
|
|
next if $field->no_column; |
89
|
0
|
0
|
0
|
|
|
|
next unless (defined $field->header and defined $field->name); |
90
|
0
|
|
|
|
|
|
push @colDefs, { |
91
|
|
|
|
|
|
|
name => $colname, |
92
|
|
|
|
|
|
|
label => $field->header |
93
|
|
|
|
|
|
|
}; |
94
|
|
|
|
|
|
|
} |
95
|
|
|
|
|
|
|
|
96
|
|
|
|
|
|
|
# Restrict columns to the set we chose to keep. |
97
|
|
|
|
|
|
|
# Note that the previous ref is a constant, and would be bad if we modified it. |
98
|
0
|
|
|
|
|
|
$columns= [ map { $_->{name} } @colDefs ]; |
|
0
|
|
|
|
|
|
|
99
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
# override the columns that DataStore is fetching |
101
|
|
|
|
|
|
|
#$self->c->req->params->{columns}= $self->json->encode($columns); |
102
|
0
|
|
|
|
|
|
my $data = $self->DataStore->read({%$params, columns => $columns, ignore_page_size => 1}); |
103
|
|
|
|
|
|
|
|
104
|
|
|
|
|
|
|
# TODO: We just read all rows into memory, and now we're building the file in memory as well. |
105
|
|
|
|
|
|
|
# We would do well to replace this with a db-cursor-to-tempfile streaming design |
106
|
|
|
|
|
|
|
|
107
|
0
|
|
|
|
|
|
my $dlData = ''; |
108
|
0
|
|
|
|
|
|
open my $fd, '>', \$dlData; |
109
|
|
|
|
|
|
|
|
110
|
0
|
|
|
|
|
|
my $method= $export_format->{renderer}; |
111
|
0
|
|
|
|
|
|
$self->$method({ %$params, col_defs => \@colDefs }, $data, $fd); |
112
|
|
|
|
|
|
|
|
113
|
0
|
|
|
|
|
|
close $fd; |
114
|
|
|
|
|
|
|
|
115
|
0
|
|
|
|
|
|
$self->render_as_json(0); |
116
|
|
|
|
|
|
|
|
117
|
0
|
|
|
|
|
|
my $h= $self->c->res->headers; |
118
|
|
|
|
|
|
|
|
119
|
|
|
|
|
|
|
# Excel 97-2003 format (XLS) |
120
|
|
|
|
|
|
|
#$h->content_type('application/vnd.ms-excel'); |
121
|
|
|
|
|
|
|
|
122
|
|
|
|
|
|
|
# Generic Spreadsheet format |
123
|
|
|
|
|
|
|
#$h->content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); |
124
|
|
|
|
|
|
|
|
125
|
|
|
|
|
|
|
# Excel XLSX format |
126
|
|
|
|
|
|
|
#$h->content_type('application/vnd.ms-excel.12'); |
127
|
0
|
|
|
|
|
|
$h->content_type($export_format->{mime}); |
128
|
|
|
|
|
|
|
|
129
|
|
|
|
|
|
|
# Make it a file download: |
130
|
0
|
|
|
|
|
|
$h->header('Content-disposition' => "attachment; filename=\"$export_filename\""); |
131
|
|
|
|
|
|
|
|
132
|
4
|
|
|
4
|
|
29
|
$h->content_length(do { use bytes; length($dlData) }); |
|
4
|
|
|
|
|
8
|
|
|
4
|
|
|
|
|
30
|
|
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
133
|
0
|
|
|
|
|
|
$h->last_modified(time); |
134
|
0
|
|
|
|
|
|
$h->expires(time()); |
135
|
0
|
|
|
|
|
|
$h->header('Pragma' => 'no-cache'); |
136
|
0
|
|
|
|
|
|
$h->header('Cache-Control' => 'no-cache'); |
137
|
|
|
|
|
|
|
|
138
|
0
|
|
|
|
|
|
return $dlData; |
139
|
|
|
|
|
|
|
} |
140
|
|
|
|
|
|
|
|
141
|
|
|
|
|
|
|
sub export_render_excel { |
142
|
0
|
|
|
0
|
0
|
|
my ($self, $params, $data, $fd)= @_; |
143
|
|
|
|
|
|
|
|
144
|
0
|
|
|
|
|
|
my $xls = Excel::Writer::XLSX->new($fd); |
145
|
|
|
|
|
|
|
|
146
|
|
|
|
|
|
|
# -- Excel/Writer/XLSX-specific: (slashes used instead of :: to protect from find/replace) |
147
|
0
|
|
|
|
|
|
$xls->set_optimization(); |
148
|
|
|
|
|
|
|
# -- |
149
|
|
|
|
|
|
|
|
150
|
0
|
|
|
|
|
|
$xls->set_properties( |
151
|
|
|
|
|
|
|
title => 'Exported RapidApp AppGrid Module: ' . ref($self), |
152
|
|
|
|
|
|
|
); |
153
|
0
|
|
|
|
|
|
my $ws = $xls->add_worksheet; |
154
|
|
|
|
|
|
|
my $tw = RapidApp::Spreadsheet::ExcelTableWriter->new( |
155
|
|
|
|
|
|
|
wbook => $xls, |
156
|
|
|
|
|
|
|
wsheet => $ws, |
157
|
|
|
|
|
|
|
columns => $params->{col_defs}, |
158
|
0
|
|
|
|
|
|
ignoreUnknownRowKeys => 1, |
159
|
|
|
|
|
|
|
); |
160
|
|
|
|
|
|
|
|
161
|
|
|
|
|
|
|
######################################### |
162
|
0
|
|
|
|
|
|
$tw->writeRow($_) for (@{$data->{rows}}); |
|
0
|
|
|
|
|
|
|
163
|
|
|
|
|
|
|
######################################### |
164
|
|
|
|
|
|
|
|
165
|
|
|
|
|
|
|
#### Column Summaries #### |
166
|
0
|
0
|
|
|
|
|
if(exists $data->{column_summaries}) { |
167
|
0
|
|
|
|
|
|
my $sums = $data->{column_summaries}; |
168
|
0
|
|
|
|
|
|
$self->convert_render_cols_hash($sums); |
169
|
|
|
|
|
|
|
|
170
|
0
|
|
|
|
|
|
my $funcs; |
171
|
0
|
0
|
|
|
|
|
if ($params->{column_summaries}) { |
172
|
0
|
|
|
|
|
|
$funcs = $self->json->decode($params->{column_summaries}); |
173
|
0
|
|
|
|
|
|
$self->convert_render_cols_hash($funcs); |
174
|
|
|
|
|
|
|
} |
175
|
|
|
|
|
|
|
|
176
|
0
|
|
|
|
|
|
$tw->writeRow({}); |
177
|
0
|
|
|
|
|
|
$tw->writeRow({}); |
178
|
0
|
|
|
|
|
|
my $fmt = $xls->add_format; |
179
|
0
|
|
|
|
|
|
$fmt->set_bold(); |
180
|
0
|
|
|
|
|
|
local $RapidApp::Spreadsheet::ExcelTableWriter::writeRowFormat = $fmt; |
181
|
0
|
|
|
|
|
|
$tw->writeRow('Col Summaries'); |
182
|
0
|
|
|
|
|
|
$fmt->set_italic(); |
183
|
|
|
|
|
|
|
|
184
|
0
|
0
|
0
|
|
|
|
if($data->{results} && $data->{results} > @{$data->{rows}}) { |
|
0
|
|
|
|
|
|
|
185
|
0
|
|
|
|
|
|
my $fmt = $xls->add_format; |
186
|
0
|
|
|
|
|
|
$fmt->set_italic(); |
187
|
0
|
|
|
|
|
|
local $RapidApp::Spreadsheet::ExcelTableWriter::writeRowFormat = $fmt; |
188
|
0
|
|
|
|
|
|
$tw->writeRow('(Note: all rows are not shown above)'); |
189
|
|
|
|
|
|
|
} |
190
|
0
|
|
|
|
|
|
$tw->writeRow({}); |
191
|
0
|
0
|
|
|
|
|
$tw->writeRow($funcs) if ($funcs); |
192
|
|
|
|
|
|
|
|
193
|
0
|
|
|
|
|
|
$RapidApp::Spreadsheet::ExcelTableWriter::writeRowFormat = undef; |
194
|
0
|
|
|
|
|
|
$tw->writeRow($sums); |
195
|
|
|
|
|
|
|
} |
196
|
|
|
|
|
|
|
#### |
197
|
|
|
|
|
|
|
|
198
|
0
|
|
|
|
|
|
$tw->autosizeColumns(); |
199
|
0
|
|
|
|
|
|
$xls->close(); |
200
|
|
|
|
|
|
|
} |
201
|
|
|
|
|
|
|
|
202
|
|
|
|
|
|
|
sub export_render_csv { |
203
|
0
|
|
|
0
|
0
|
|
my ($self, $params, $data, $fd)= @_; |
204
|
0
|
0
|
|
|
|
|
my $csv= Text::CSV->new({ binary => 1 }) or die "Can't create CSV instance"; |
205
|
|
|
|
|
|
|
|
206
|
0
|
|
|
|
|
|
my @cols= map { $_->{name} } @{ $params->{col_defs} }; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
207
|
0
|
|
|
|
|
|
my @titles= map { $_->{label} } @{ $params->{col_defs} }; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
208
|
|
|
|
|
|
|
|
209
|
|
|
|
|
|
|
# Write header row |
210
|
0
|
|
|
|
|
|
$csv->print($fd, \@titles); |
211
|
0
|
|
|
|
|
|
print $fd "\r\n"; |
212
|
|
|
|
|
|
|
|
213
|
|
|
|
|
|
|
# Write data rows |
214
|
0
|
|
|
|
|
|
for (@{ $data->{rows} }) { |
|
0
|
|
|
|
|
|
|
215
|
0
|
|
|
|
|
|
$csv->print($fd, [ @{$_}{@cols} ]); |
|
0
|
|
|
|
|
|
|
216
|
0
|
|
|
|
|
|
print $fd "\r\n"; |
217
|
|
|
|
|
|
|
} |
218
|
|
|
|
|
|
|
} |
219
|
|
|
|
|
|
|
|
220
|
|
|
|
|
|
|
sub export_render_tsv { |
221
|
0
|
|
|
0
|
0
|
|
my ($self, $params, $data, $fd)= @_; |
222
|
0
|
0
|
|
|
|
|
my $csv= Text::CSV->new({ binary => 1, sep_char => "\t", quote_space => 0 }) or die "Can't create CSV instance"; |
223
|
|
|
|
|
|
|
|
224
|
0
|
|
|
|
|
|
my @cols= map { $_->{name} } @{ $params->{col_defs} }; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
225
|
0
|
|
|
|
|
|
my @titles= map { $_->{label} } @{ $params->{col_defs} }; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
226
|
|
|
|
|
|
|
|
227
|
|
|
|
|
|
|
# Write header row |
228
|
0
|
|
|
|
|
|
$csv->print($fd, \@titles); |
229
|
0
|
|
|
|
|
|
print $fd "\r\n"; |
230
|
|
|
|
|
|
|
|
231
|
|
|
|
|
|
|
# Write data rows |
232
|
0
|
|
|
|
|
|
for (@{ $data->{rows} }) { |
|
0
|
|
|
|
|
|
|
233
|
0
|
|
|
|
|
|
$csv->print($fd, [ @{$_}{@cols} ]); |
|
0
|
|
|
|
|
|
|
234
|
0
|
|
|
|
|
|
print $fd "\r\n"; |
235
|
|
|
|
|
|
|
} |
236
|
|
|
|
|
|
|
} |
237
|
|
|
|
|
|
|
|
238
|
|
|
|
|
|
|
sub export_render_json { |
239
|
0
|
|
|
0
|
0
|
|
my ($self, $params, $data, $fd)= @_; |
240
|
|
|
|
|
|
|
|
241
|
0
|
|
|
|
|
|
my $json= JSON->new->ascii(1); |
242
|
0
|
|
|
|
|
|
my @cols= map { $_->{name} } @{ $params->{col_defs} }; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
243
|
|
|
|
|
|
|
|
244
|
|
|
|
|
|
|
# Export row-by-row for two reasons: |
245
|
|
|
|
|
|
|
# First, it prevents us from making 3 full copies of the whole |
246
|
|
|
|
|
|
|
# dataset in memory (could run the server out of ram) |
247
|
|
|
|
|
|
|
# and Second, if we insert a newline after each row then it won't |
248
|
|
|
|
|
|
|
# break text viewers (like less) as badly as if they try to view |
249
|
|
|
|
|
|
|
# several MB of data on a single line. |
250
|
0
|
|
|
|
|
|
$fd->print("{\"columns\":" . $json->encode($params->{col_defs}) . ",\r\n" |
251
|
|
|
|
|
|
|
." \"rows\":["); |
252
|
0
|
|
|
|
|
|
for (my $i= 0; $i < @{ $data->{rows} }; $i++) { |
|
0
|
|
|
|
|
|
|
253
|
0
|
|
|
|
|
|
my %row; |
254
|
|
|
|
|
|
|
# Unfortunately the data might contain extra columns like the primary keys, |
255
|
|
|
|
|
|
|
# and we might not want to show these to users. (Think SSNs) |
256
|
|
|
|
|
|
|
# So we have to perform a translation on each row to extract only the columns we should export. |
257
|
0
|
|
|
|
|
|
@row{@cols}= @{$data->{rows}[$i]}{@cols}; |
|
0
|
|
|
|
|
|
|
258
|
0
|
0
|
|
|
|
|
$fd->print($i? ",\r\n " : "\r\n "); |
259
|
0
|
|
|
|
|
|
$fd->print($json->encode(\%row)); |
260
|
|
|
|
|
|
|
} |
261
|
0
|
|
|
|
|
|
$fd->print("\r\n ]"); |
262
|
0
|
0
|
|
|
|
|
if (exists $data->{column_summaries}) { |
263
|
0
|
|
|
|
|
|
$fd->print(",\r\n \"column_summaries\":"); |
264
|
0
|
|
|
|
|
|
$fd->print($json->encode($data->{column_summaries})); |
265
|
|
|
|
|
|
|
} |
266
|
0
|
|
|
|
|
|
$fd->print("\r\n}"); |
267
|
|
|
|
|
|
|
} |
268
|
|
|
|
|
|
|
|
269
|
|
|
|
|
|
|
sub convert_render_cols_hash { |
270
|
0
|
|
|
0
|
0
|
|
my $self = shift; |
271
|
0
|
|
|
|
|
|
my $hash = shift; |
272
|
|
|
|
|
|
|
|
273
|
0
|
|
|
|
|
|
foreach my $col (keys %$hash) { |
274
|
0
|
0
|
0
|
|
|
|
my $field = $self->get_column($col) or |
275
|
|
|
|
|
|
|
warn "ExcelExport: column $col does not exist in columns hash" |
276
|
|
|
|
|
|
|
and next; |
277
|
0
|
0
|
|
|
|
|
my $colname = $field->render_column ? $field->render_column : $field->name; |
278
|
0
|
|
|
|
|
|
$hash->{$colname} = delete $hash->{$col}; |
279
|
|
|
|
|
|
|
} |
280
|
|
|
|
|
|
|
} |
281
|
|
|
|
|
|
|
|
282
|
|
|
|
|
|
|
|
283
|
|
|
|
|
|
|
1; |