| 1 | package Mojolicious::Plugin::ExportExcel; | ||
| 2 | |||
| 3 | 0 | ▶ | use Mojo::Base 'Mojolicious::Plugin'; |
|
Subroutine
BEGIN: called CC=1 SCAR=2.6
| |||
| 4 | use Spreadsheet::WriteExcel; | ||
| 5 | use Clone qw/clone/; | ||
| 6 | use Mojo::JSON qw/to_json/; | ||
| 7 | |||
| 8 | sub export_excel_renderer{ | ||
| 9 | my ($r, $c, $output, $options) = @_; | ||
| 10 | |||
| 11 | # 不需要编码 | ||
| 12 | delete $options->{encoding}; | ||
| 13 | |||
| 14 | # 设置渲染格式 | ||
| 15 | $options->{format} = 'xls'; | ||
| 16 | |||
| 17 | if($c->stash->{excel} && !(ref $c->stash->{excel})){ | ||
| 18 | ${$output} = $c->stash->{excel}; | ||
| 19 | return 1; | ||
| 20 | } | ||
| 21 | |||
| 22 | my $excel = $c->stash->{excel}; | ||
| 23 | unless(ref $excel eq "HASH"){ | ||
| 24 | $c->reply->exception("the param [excel] require hashref or string"); | ||
| 25 | return undef; | ||
| 26 | } | ||
| 27 | |||
| 28 | ## $settings 是 excel 表的配置信息 | ||
| 29 | my $settings = $excel->{settings} || {}; | ||
| 30 | |||
| 31 | ## $settings 是 excel 表的数据信息 | ||
| 32 | my $data = $excel->{data}; | ||
| 33 | |||
| 34 | ## $settings 是 excel 表的渲染规则 | ||
| 35 | my $option = $excel->{option}; | ||
| 36 | |||
| 37 | ## 数据不满足要求 则不渲染 | ||
| 38 | unless(ref $settings eq "HASH" && ref $data eq "ARRAY" && ref $option eq "ARRAY"){ | ||
| 39 | $c->reply->exception("the param is invalid: settings require HASH; data require ARRAY; option require ARRAY"); | ||
| 40 | return undef; | ||
| 41 | } | ||
| 42 | |||
| 43 | |||
| 44 | ## 创建内存文件 | ||
| 45 | open my $excel_file, '>', \my $excel_content; | ||
| 46 | ## 创建 excel 对象 | ||
| 47 | my $excel_obj = Spreadsheet::WriteExcel->new($excel_file); | ||
| 48 | ## 在 excel 中创建一个电子表 sheet | ||
| 49 | my $ss = $excel_obj->add_worksheet($settings->{sheet_name}); | ||
| 50 | |||
| 51 | |||
| 52 | ##----------------------共用的格式写在下面-------------------------- | ||
| 53 | ## 表头的样式 | ||
| 54 | my $header_format = delete $settings->{header_format} || {bold => 1}; | ||
| 55 | |||
| 56 | ## 表头高度 | ||
| 57 | my $header_height = delete $settings->{header_height} || 20; | ||
| 58 | |||
| 59 | ## 数据行的样式 | ||
| 60 | my $data_format = delete $settings->{data_format} || {}; | ||
| 61 | |||
| 62 | ## 样式缓存,一个表中能添加的样式是有限的,多了后面的就不起效果了 | ||
| 63 | my $df_cache = {}; | ||
| 64 | |||
| 65 | ## 数据行高度 | ||
| 66 | my $data_height = delete $settings->{data_height} || 20; | ||
| 67 | |||
| 68 | ## 条件样式 | ||
| 69 | my $condition_format = delete $settings->{condition_format} || {}; | ||
| 70 | |||
| 71 | ##----------------------共用的格式写在上面-------------------------- | ||
| 72 | |||
| 73 | |||
| 74 | |||
| 75 | ## 开始渲染 | ||
| 76 | my $heading = []; | ||
| 77 | |||
| 78 | ## 计算表头 | ||
| 79 | my $col = 0; | ||
| 80 | foreach my $o(@{$option}){ | ||
| 81 | if($o->{header}){ | ||
| 82 | push(@{$heading}, $o->{header}); | ||
| 83 | }else{ | ||
| 84 | push(@{$heading}, $o->{key}); | ||
| 85 | } | ||
| 86 | $ss->set_column($col, $col, $o->{width}) if($o->{width}); | ||
| 87 | $col++; | ||
| 88 | } | ||
| 89 | my $row = 0; | ||
| 90 | $ss->set_row($row, $header_height); | ||
| 91 | $ss->write_row($row, 0, $heading, $excel_obj->add_format(%{$header_format})); | ||
| 92 | $row++; | ||
| 93 | |||
| 94 | ## 对数据依照渲染规则进行渲染 | ||
| 95 | foreach my $srd (@{$data}){ | ||
| 96 | $col = 0; | ||
| 97 | |||
| 98 | ## 计算得到当前行的格式 | ||
| 99 | my $rf = clone($data_format); | ||
| 100 | if($condition_format->{row} && @{$condition_format->{row}}){ | ||
| 101 | foreach(@{$condition_format->{row}}){ | ||
| 102 | if($_->{condition}->($srd->{$_->{key}})){ | ||
| 103 | $rf = {%{$rf}, $_->{format} ? %{$_->{format}} : ()}; | ||
| 104 | } | ||
| 105 | } | ||
| 106 | } | ||
| 107 | |||
| 108 | $ss->set_row($row, $data_height); | ||
| 109 | |||
| 110 | foreach my $o(@{$option}){ | ||
| 111 | my $d = $srd->{$o->{key}}; | ||
| 112 | |||
| 113 | ## 单元格样式 | ||
| 114 | my $cf = {%{$rf}, $o->{format} ? %{$o->{format}} : ()}; | ||
| 115 | |||
| 116 | if($condition_format->{cell} && $condition_format->{cell}->{$o->{key}}){ | ||
| 117 | if($condition_format->{cell}->{$o->{key}}->{condition}->($d)){ | ||
| 118 | my $tf = $condition_format->{cell}->{$o->{key}}->{format}; | ||
| 119 | $cf = {%{$cf}, $tf ? %{$tf} : ()}; | ||
| 120 | } | ||
| 121 | } | ||
| 122 | |||
| 123 | if($o->{map}){ | ||
| 124 | $d = $o->{map}->{$d} || $o->{map}->{default}; | ||
| 125 | }elsif($o->{action}){ | ||
| 126 | $d = $o->{action}->($d, $srd); | ||
| 127 | } | ||
| 128 | |||
| 129 | my $df; | ||
| 130 | my $cf_json = to_json($cf); | ||
| 131 | if($df_cache->{$cf_json}){ | ||
| 132 | $df = $df_cache->{$cf_json}; | ||
| 133 | }else{ | ||
| 134 | $df_cache->{$cf_json} = $df = $excel_obj->add_format(%{$cf}); | ||
| 135 | } | ||
| 136 | if($o->{type} eq "number"){ | ||
| 137 | $ss->write_number($row, $col, $d, $df); | ||
| 138 | }elsif($o->{type} eq "string"){ | ||
| 139 | $ss->write_string($row, $col, $d, $df); | ||
| 140 | }elsif($o->{type} eq "url"){ | ||
| 141 | $ss->write_url($row, $col, $d, $df); | ||
| 142 | }else{ | ||
| 143 | $ss->write($row, $col, $d, $df); | ||
| 144 | } | ||
| 145 | $col++; | ||
| 146 | } | ||
| 147 | $row++; | ||
| 148 | } | ||
| 149 | |||
| 150 | $excel_obj->close; | ||
| 151 | |||
| 152 | ## 设置输出 | ||
| 153 | ${$output} = $excel_content; | ||
| 154 | |||
| 155 | return 1; | ||
| 156 | } | ||
| 157 | |||
| 158 | sub register{ | ||
| 159 | my ($self, $app) = @_; | ||
| 160 | |||
| 161 | $app->types->type(xls => 'application/vnd.ms-excel'); | ||
| 162 | $app->renderer->add_handler(xls => \&export_excel_renderer); | ||
| 163 | $app->helper( | ||
| 164 | render_excel => sub{ | ||
| 165 | shift->render(handler => 'xls', @_); | ||
| 166 | } | ||
| 167 | ); | ||
| 168 | } | ||
| 169 | |||
| 170 | =head1 NAME | ||
| 171 | |||
| 172 | Mojolicious::Plugin::ExportExcel - The great new Mojolicious::Plugin::ExportExcel! | ||
| 173 | |||
| 174 | =head1 VERSION | ||
| 175 | |||
| 176 | Version 0.01 | ||
| 177 | |||
| 178 | =cut | ||
| 179 | |||
| 180 | our $VERSION = '1.1.1'; | ||
| 181 | |||
| 182 | |||
| 183 | =head1 SYNOPSIS | ||
| 184 | |||
| 185 | |||
| 186 | # Mojolicious | ||
| 187 | $self->plugin('ExportExcel'); | ||
| 188 | |||
| 189 | # Mojolicious::Lite | ||
| 190 | plugin 'ExportExcel'; | ||
| 191 | |||
| 192 | |||
| 193 | =head1 EXPORT | ||
| 194 | |||
| 195 | A list of functions that can be exported. You can delete this section | ||
| 196 | if you don't export anything, such as for a purely object-oriented module. | ||
| 197 | |||
| 198 | =head1 METHODS | ||
| 199 | |||
| 200 | Mojolicious::Plugin::ExportExcel 覆盖了Mojolicious::Plugin中的register方法。 | ||
| 201 | |||
| 202 | =head2 register | ||
| 203 | |||
| 204 | 向Mojolicious的renderer中添加了一个handler:xls,用于渲染Excel文件。 | ||
| 205 | 向Mojolicious中添加一个helper:render_excel,用于渲染Excel文件。 | ||
| 206 | |||
| 207 | |||
| 208 | |||
| 209 | =head2 export_excel_renderer | ||
| 210 | |||
| 211 | 执行xls handler时的渲染方法。 | ||
| 212 | |||
| 213 | |||
| 214 | |||
| 215 | =head1 PARAMETER | ||
| 216 | |||
| 217 | |||
| 218 | =head2 option | ||
| 219 | |||
| 220 | my $option = [ | ||
| 221 | { | ||
| 222 | key => "recharge_status", ## 从 data的当前hash中取哪个键的值放入对应单元格 | ||
| 223 | header => "状态", ## 对应列的表头 | ||
| 224 | map => { ## 需要对用key从data取出的当前值做映射转换的规则 | ||
| 225 | 0 => "待支付", | ||
| 226 | 2 => "待付款", | ||
| 227 | 3 => "完成", | ||
| 228 | 4 => "取消" | ||
| 229 | }, | ||
| 230 | action=>sub{ ## action 与 map 只有一个工作,且map优先级高 | ||
| 231 | ## 接收两个参数, | ||
| 232 | ## 第一个为 当前单元格的值 | ||
| 233 | ## 第二个为 当前行的hash对象 | ||
| 234 | ## 返回的值会被作为当前单元格的最值写入excel表中 | ||
| 235 | ## 其实功能与 map 类似 | ||
| 236 | } | ||
| 237 | type=>"string", ## 数据类型,string,number,url | ||
| 238 | format=>{}, ## 对应列的样式 | ||
| 239 | }, | ||
| 240 | …… | ||
| 241 | ]; | ||
| 242 | |||
| 243 | |||
| 244 | =head2 settings | ||
| 245 | |||
| 246 | 一个存储了excel表格配置信息的 hashref。 | ||
| 247 | |||
| 248 | my $settings=>{ | ||
| 249 | sheet_name => "", ## 表格名称 默认为空 | ||
| 250 | header_format => {}, ## 表头格式 默认为加粗 | ||
| 251 | data_format => {}, ## 数据格式 默认为无格式 | ||
| 252 | header_height => {}, ## 表头高度 默认为20 | ||
| 253 | data_height => {}, ## 数据高度 默认为20 | ||
| 254 | condition_format=>{ ## 根据条件设置样式 | ||
| 255 | row=>[ ## 根据条件设置一行的格式 | ||
| 256 | { | ||
| 257 | key=>"", ## 要判断的数据行 | ||
| 258 | condition=>sub{}, ## 判断条件 | ||
| 259 | format=>{} ## 满足条件时的样式 | ||
| 260 | }, | ||
| 261 | …… | ||
| 262 | ], | ||
| 263 | cell=>{ ## 根据条件设置一个单元格的样式 | ||
| 264 | key=>{ ## key 要判断数据的键名 | ||
| 265 | condition=>sub{}, ## 判断条件 | ||
| 266 | format=>{} ## 满足条件时的样式 | ||
| 267 | }, | ||
| 268 | …… | ||
| 269 | } | ||
| 270 | }, | ||
| 271 | ‘A:A'=>100 ## 对应列的宽度 | ||
| 272 | |||
| 273 | } | ||
| 274 | |||
| 275 | |||
| 276 | =head2 data | ||
| 277 | |||
| 278 | 一个hashref 的 arrayref ,其中存储的是要渲染成excel表格的数据。 | ||
| 279 | |||
| 280 | |||
| 281 | =head1 SUPPORT | ||
| 282 | |||
| 283 | You can find documentation for this module with the perldoc command. | ||
| 284 | |||
| 285 | perldoc Mojolicious::Plugin::ExportExcel | ||
| 286 | |||
| 287 | =cut | ||
| 288 | |||
| 289 | 1; # End of Mojolicious::Plugin::ExportExcel | ||