-
Notifications
You must be signed in to change notification settings - Fork 89
/
Excel.php
executable file
·951 lines (899 loc) · 33.1 KB
/
Excel.php
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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
<?php
namespace moonland\phpexcel;
use yii\helpers\ArrayHelper;
use yii\base\InvalidConfigException;
use yii\base\InvalidParamException;
use yii\i18n\Formatter;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
/**
* Excel Widget for generate Excel File or for load Excel File.
*
* Usage
* -----
*
* Exporting data into an excel file.
*
* ~~~
*
* // export data only one worksheet.
*
* \moonland\phpexcel\Excel::widget([
* 'models' => $allModels,
* 'mode' => 'export', //default value as 'export'
* 'columns' => ['column1','column2','column3'],
* //without header working, because the header will be get label from attribute label.
* 'headers' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* ]);
*
* \moonland\phpexcel\Excel::export([
* 'models' => $allModels,
* 'columns' => ['column1','column2','column3'],
* //without header working, because the header will be get label from attribute label.
* 'headers' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* ]);
*
* // export data with multiple worksheet.
*
* \moonland\phpexcel\Excel::widget([
* 'isMultipleSheet' => true,
* 'models' => [
* 'sheet1' => $allModels1,
* 'sheet2' => $allModels2,
* 'sheet3' => $allModels3
* ],
* 'mode' => 'export', //default value as 'export'
* 'columns' => [
* 'sheet1' => ['column1','column2','column3'],
* 'sheet2' => ['column1','column2','column3'],
* 'sheet3' => ['column1','column2','column3']
* ],
* //without header working, because the header will be get label from attribute label.
* 'headers' => [
* 'sheet1' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* 'sheet2' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* 'sheet3' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3']
* ],
* ]);
*
* \moonland\phpexcel\Excel::export([
* 'isMultipleSheet' => true,
* 'models' => [
* 'sheet1' => $allModels1,
* 'sheet2' => $allModels2,
* 'sheet3' => $allModels3
* ],
* 'columns' => [
* 'sheet1' => ['column1','column2','column3'],
* 'sheet2' => ['column1','column2','column3'],
* 'sheet3' => ['column1','column2','column3']
* ],
* //without header working, because the header will be get label from attribute label.
* 'headers' => [
* 'sheet1' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* 'sheet2' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* 'sheet3' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3']
* ],
* ]);
*
* ~~~
*
* New Feature for exporting data, you can use this if you familiar yii gridview.
* That is same with gridview data column.
* Columns in array mode valid params are 'attribute', 'header', 'format', 'value', and footer (TODO).
* Columns in string mode valid layout are 'attribute:format:header:footer(TODO)'.
*
* ~~~
*
* \moonland\phpexcel\Excel::export([
* 'models' => Post::find()->all(),
* 'columns' => [
* 'author.name:text:Author Name',
* [
* 'attribute' => 'content',
* 'header' => 'Content Post',
* 'format' => 'text',
* 'value' => function($model) {
* return ExampleClass::removeText('example', $model->content);
* },
* ],
* 'like_it:text:Reader like this content',
* 'created_at:datetime',
* [
* 'attribute' => 'updated_at',
* 'format' => 'date',
* ],
* ],
* 'headers' => [
* 'created_at' => 'Date Created Content',
* ],
* ]);
*
* ~~~
*
*
* Import file excel and return into an array.
*
* ~~~
*
* $data = \moonland\phpexcel\Excel::import($fileName, $config); // $config is an optional
*
* $data = \moonland\phpexcel\Excel::widget([
* 'mode' => 'import',
* 'fileName' => $fileName,
* 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
* 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
* 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
* ]);
*
* $data = \moonland\phpexcel\Excel::import($fileName, [
* 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
* 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
* 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
* ]);
*
* // import data with multiple file.
*
* $data = \moonland\phpexcel\Excel::widget([
* 'mode' => 'import',
* 'fileName' => [
* 'file1' => $fileName1,
* 'file2' => $fileName2,
* 'file3' => $fileName3,
* ],
* 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
* 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
* 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
* ]);
*
* $data = \moonland\phpexcel\Excel::import([
* 'file1' => $fileName1,
* 'file2' => $fileName2,
* 'file3' => $fileName3,
* ], [
* 'setFirstRecordAsKeys' => true, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
* 'setIndexSheetByName' => true, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
* 'getOnlySheet' => 'sheet1', // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
* ]);
*
* ~~~
*
* Result example from the code on the top :
*
* ~~~
*
* // only one sheet or specified sheet.
*
* Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2));
*
* // data with multiple worksheet
*
* Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)),
* [Sheet2] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)));
*
* // data with multiple file and specified sheet or only one worksheet
*
* Array([file1] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)),
* [file2] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)));
*
* // data with multiple file and multiple worksheet
*
* Array([file1] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)),
* [Sheet2] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2))),
* [file2] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2)),
* [Sheet2] => Array([0] => Array([name] => Anam, [email] => [email protected], [framework interest] => Yii2),
* [1] => Array([name] => Example, [email] => [email protected], [framework interest] => Yii2))));
*
* ~~~
*
* @property string $mode is an export mode or import mode. valid value are 'export' and 'import'
* @property boolean $isMultipleSheet for set the export excel with multiple sheet.
* @property array $properties for set property on the excel object.
* @property array $models Model object or DataProvider object with much data.
* @property array $columns to get the attributes from the model, this valid value only the exist attribute on the model.
* If this is not set, then all attribute of the model will be set as columns.
* @property array $headers to set the header column on first line. Set this if want to custom header.
* If not set, the header will get attributes label of model attributes.
* @property string|array $fileName is a name for file name to export or import. Multiple file name only use for import mode, not work if you use the export mode.
* @property string $savePath is a directory to save the file or you can blank this to set the file as attachment.
* @property string $format for excel to export. Valid value are 'Xls','Xlsx','Xml','Ods','Slk','Gnumeric','Csv', and 'Html'.
* @property boolean $setFirstTitle to set the title column on the first line. The columns will have a header on the first line.
* @property boolean $asAttachment to set the file excel to download mode.
* @property boolean $setFirstRecordAsKeys to set the first record on excel file to a keys of array per line.
* If you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
* @property boolean $setIndexSheetByName to set the sheet index by sheet name or array result if the sheet not only one
* @property string $getOnlySheet is a sheet name to getting the data. This is only get the sheet with same name.
* @property array|Formatter $formatter the formatter used to format model attribute values into displayable texts.
* This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
* instance. If this property is not set, the "formatter" application component will be used.
*
* @author Moh Khoirul Anam <[email protected]>
* @copyright 2014
* @since 1
*/
class Excel extends \yii\base\Widget
{
// Border style
const BORDER_NONE = 'none';
const BORDER_DASHDOT = 'dashDot';
const BORDER_DASHDOTDOT = 'dashDotDot';
const BORDER_DASHED = 'dashed';
const BORDER_DOTTED = 'dotted';
const BORDER_DOUBLE = 'double';
const BORDER_HAIR = 'hair';
const BORDER_MEDIUM = 'medium';
const BORDER_MEDIUMDASHDOT = 'mediumDashDot';
const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
const BORDER_MEDIUMDASHED = 'mediumDashed';
const BORDER_SLANTDASHDOT = 'slantDashDot';
const BORDER_THICK = 'thick';
const BORDER_THIN = 'thin';
// Colors
const COLOR_BLACK = 'FF000000';
const COLOR_WHITE = 'FFFFFFFF';
const COLOR_RED = 'FFFF0000';
const COLOR_DARKRED = 'FF800000';
const COLOR_BLUE = 'FF0000FF';
const COLOR_DARKBLUE = 'FF000080';
const COLOR_GREEN = 'FF00FF00';
const COLOR_DARKGREEN = 'FF008000';
const COLOR_YELLOW = 'FFFFFF00';
const COLOR_DARKYELLOW = 'FF808000';
// Horizontal alignment styles
const HORIZONTAL_GENERAL = 'general';
const HORIZONTAL_LEFT = 'left';
const HORIZONTAL_RIGHT = 'right';
const HORIZONTAL_CENTER = 'center';
const HORIZONTAL_CENTER_CONTINUOUS = 'centerContinuous';
const HORIZONTAL_JUSTIFY = 'justify';
const HORIZONTAL_FILL = 'fill';
const HORIZONTAL_DISTRIBUTED = 'distributed'; // Excel2007 only
// Vertical alignment styles
const VERTICAL_BOTTOM = 'bottom';
const VERTICAL_TOP = 'top';
const VERTICAL_CENTER = 'center';
const VERTICAL_JUSTIFY = 'justify';
const VERTICAL_DISTRIBUTED = 'distributed'; // Excel2007 only
// Read order
const READORDER_CONTEXT = 0;
const READORDER_LTR = 1;
const READORDER_RTL = 2;
// Fill types
const FILL_NONE = 'none';
const FILL_SOLID = 'solid';
const FILL_GRADIENT_LINEAR = 'linear';
const FILL_GRADIENT_PATH = 'path';
const FILL_PATTERN_DARKDOWN = 'darkDown';
const FILL_PATTERN_DARKGRAY = 'darkGray';
const FILL_PATTERN_DARKGRID = 'darkGrid';
const FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal';
const FILL_PATTERN_DARKTRELLIS = 'darkTrellis';
const FILL_PATTERN_DARKUP = 'darkUp';
const FILL_PATTERN_DARKVERTICAL = 'darkVertical';
const FILL_PATTERN_GRAY0625 = 'gray0625';
const FILL_PATTERN_GRAY125 = 'gray125';
const FILL_PATTERN_LIGHTDOWN = 'lightDown';
const FILL_PATTERN_LIGHTGRAY = 'lightGray';
const FILL_PATTERN_LIGHTGRID = 'lightGrid';
const FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal';
const FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis';
const FILL_PATTERN_LIGHTUP = 'lightUp';
const FILL_PATTERN_LIGHTVERTICAL = 'lightVertical';
const FILL_PATTERN_MEDIUMGRAY = 'mediumGray';
// Pre-defined formats
const FORMAT_GENERAL = 'General';
const FORMAT_TEXT = '@';
const FORMAT_NUMBER = '0';
const FORMAT_NUMBER_00 = '0.00';
const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
const FORMAT_PERCENTAGE = '0%';
const FORMAT_PERCENTAGE_00 = '0.00%';
const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
const FORMAT_DATE_DMYSLASH = 'd/m/yy';
const FORMAT_DATE_DMYMINUS = 'd-m-yy';
const FORMAT_DATE_DMMINUS = 'd-m';
const FORMAT_DATE_MYMINUS = 'm-yy';
const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
const FORMAT_DATE_XLSX16 = 'd-mmm';
const FORMAT_DATE_XLSX17 = 'mmm-yy';
const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
const FORMAT_DATE_DATETIME = 'd/m/yy h:mm';
const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
const FORMAT_DATE_TIME3 = 'h:mm';
const FORMAT_DATE_TIME4 = 'h:mm:ss';
const FORMAT_DATE_TIME5 = 'mm:ss';
const FORMAT_DATE_TIME6 = 'h:mm:ss';
const FORMAT_DATE_TIME7 = 'i:s.S';
const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@';
const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
const FORMAT_CURRENCY_USD = '$#,##0_-';
const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"';
const FORMAT_CURRENCY_EUR = '#,##0_-"€"';
/**
* @var string mode is an export mode or import mode. valid value are 'export' and 'import'.
*/
public $mode = 'export';
/**
* @var boolean for set the export excel with multiple sheet.
*/
public $isMultipleSheet = false;
/**
* @var array properties for set property on the excel object.
*/
public $properties;
/**
* @var Model object or DataProvider object with much data.
*/
public $models;
/**
* @var array columns to get the attributes from the model, this valid value only the exist attribute on the model.
* If this is not set, then all attribute of the model will be set as columns.
*/
public $columns = [];
/**
* @var array header to set the header column on first line. Set this if want to custom header.
* If not set, the header will get attributes label of model attributes.
*/
public $headers = [];
/**
* @var string|array name for file name to export or save.
*/
public $fileName;
/**
* @var string save path is a directory to save the file or you can blank this to set the file as attachment.
*/
public $savePath;
/**
* @var string format for excel to export. Valid value are 'Xls','Xlsx','Xml','Ods','Slk','Gnumeric','Csv', and 'Html'.
*/
public $format;
/**
* @var boolean to set the title column on the first line.
*/
public $setFirstTitle = true;
/**
* @var boolean to set the file excel to download mode.
*/
public $asAttachment = false;
/**
* @var boolean to set the first record on excel file to a keys of array per line.
* If you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
*/
public $setFirstRecordAsKeys = true;
/**
* @var boolean to set the sheet index by sheet name or array result if the sheet not only one.
*/
public $setIndexSheetByName = false;
/**
* @var string sheetname to getting. This is only get the sheet with same name.
*/
public $getOnlySheet;
/**
* @var boolean to set the import data will return as array.
*/
public $asArray;
/**
* @var array to unread record by index number.
*/
public $leaveRecordByIndex = [];
/**
* @var array to read record by index, other will leave.
*/
public $getOnlyRecordByIndex = [];
/**
* @var array|Formatter the formatter used to format model attribute values into displayable texts.
* This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
* instance. If this property is not set, the "formatter" application component will be used.
*/
public $formatter;
/**
* @var boolean define the column autosize
*/
public $autoSize = false;
/**
* @var boolean if true, this writer pre-calculates all formulas in the spreadsheet. This can be slow on large spreadsheets, and maybe even unwanted.
*/
public $preCalculationFormula = false;
/**
* @var boolean Because of a bug in the Office2003 compatibility pack, there can be some small issues when opening Xlsx spreadsheets (mostly related to formula calculation)
*/
public $compatibilityOffice2003 = false;
/**
* @var custom CSV delimiter for import. Works only with CSV files
*/
public $CSVDelimiter = ";";
/**
* @var custom CSV encoding for import. Works only with CSV files
*/
public $CSVEncoding = "UTF-8";
/**
* (non-PHPdoc)
* @see \yii\base\Object::init()
*/
public function init()
{
parent::init();
if ($this->formatter == null) {
$this->formatter = \Yii::$app->getFormatter();
} elseif (is_array($this->formatter)) {
$this->formatter = \Yii::createObject($this->formatter);
}
if (!$this->formatter instanceof Formatter) {
throw new InvalidConfigException('The "formatter" property must be either a Format object or a configuration array.');
}
}
/**
* Setting data from models
*/
public function executeColumns(&$activeSheet = null, $models, $columns = [], $headers = [])
{
if ($activeSheet == null) {
$activeSheet = $this->activeSheet;
}
$hasHeader = false;
$row = 1;
$char = 26;
foreach ($models as $model) {
if (empty($columns)) {
$columns = $model->attributes();
}
if ($this->setFirstTitle && !$hasHeader) {
$isPlus = false;
$colplus = 0;
$colnum = 1;
foreach ($columns as $key=>$column) {
$col = '';
if ($colnum > $char) {
$colplus += 1;
$colnum = 1;
$isPlus = true;
}
if ($isPlus) {
$col .= chr(64+$colplus);
}
$col .= chr(64+$colnum);
$header = '';
if (is_array($column)) {
if (isset($column['header'])) {
$header = $column['header'];
} elseif (isset($column['attribute']) && isset($headers[$column['attribute']])) {
$header = $headers[$column['attribute']];
} elseif (isset($column['attribute'])) {
$header = $model->getAttributeLabel($column['attribute']);
} elseif (isset($column['cellFormat']) && is_array($column['cellFormat'])) {
$activeSheet->getStyle($col.$row)->applyFromArray($column['cellFormat']);
}
} else {
if(isset($headers[$column])) {
$header = $headers[$column];
} else {
$header = $model->getAttributeLabel($column);
}
}
if (isset($column['width'])) {
$activeSheet->getColumnDimension(strtoupper($col))->setWidth($column['width']);
}
$activeSheet->setCellValue($col.$row,$header);
$colnum++;
}
$hasHeader=true;
$row++;
}
$isPlus = false;
$colplus = 0;
$colnum = 1;
foreach ($columns as $key=>$column) {
$col = '';
if ($colnum > $char) {
$colplus++;
$colnum = 1;
$isPlus = true;
}
if ($isPlus) {
$col .= chr(64+$colplus);
}
$col .= chr(64+$colnum);
if (is_array($column)) {
$column_value = $this->executeGetColumnData($model, $column);
if (isset($column['cellFormat']) && is_array($column['cellFormat'])) {
$activeSheet->getStyle($col.$row)->applyFromArray($column['cellFormat']);
}
} else {
$column_value = $this->executeGetColumnData($model, ['attribute' => $column]);
}
$activeSheet->setCellValue($col.$row,$column_value);
$colnum++;
}
$row++;
if($this->autoSize){
foreach (range(0, $colnum) as $col) {
$activeSheet->getColumnDimensionByColumn($col)->setAutoSize(true);
}
}
}
}
/**
* Setting label or keys on every record if setFirstRecordAsKeys is true.
* @param array $sheetData
* @return multitype:multitype:array
*/
public function executeArrayLabel($sheetData)
{
$keys = ArrayHelper::remove($sheetData, '1');
$new_data = [];
foreach ($sheetData as $values)
{
$new_data[] = array_combine($keys, $values);
}
return $new_data;
}
/**
* Leave record with same index number.
* @param array $sheetData
* @param array $index
* @return array
*/
public function executeLeaveRecords($sheetData = [], $index = [])
{
foreach ($sheetData as $key => $data)
{
if (in_array($key, $index))
{
unset($sheetData[$key]);
}
}
return $sheetData;
}
/**
* Read record with same index number.
* @param array $sheetData
* @param array $index
* @return array
*/
public function executeGetOnlyRecords($sheetData = [], $index = [])
{
foreach ($sheetData as $key => $data)
{
if (!in_array($key, $index))
{
unset($sheetData[$key]);
}
}
return $sheetData;
}
/**
* Getting column value.
* @param Model $model
* @param array $params
* @return Ambigous <NULL, string, mixed>
*/
public function executeGetColumnData($model, $params = [])
{
$value = null;
if (isset($params['value']) && $params['value'] !== null) {
if (is_string($params['value'])) {
$value = ArrayHelper::getValue($model, $params['value']);
} else {
$value = call_user_func($params['value'], $model, $this);
}
} elseif (isset($params['attribute']) && $params['attribute'] !== null) {
$value = ArrayHelper::getValue($model, $params['attribute']);
}
if (isset($params['format']) && $params['format'] != null)
$value = $this->formatter()->format($value, $params['format']);
return $value;
}
/**
* Populating columns for checking the column is string or array. if is string this will be checking have a formatter or header.
* @param array $columns
* @throws InvalidParamException
* @return multitype:multitype:array
*/
public function populateColumns($columns = [])
{
$_columns = [];
foreach ($columns as $key => $value)
{
if (is_string($value))
{
$value_log = explode(':', $value);
$_columns[$key] = ['attribute' => $value_log[0]];
if (isset($value_log[1]) && $value_log[1] !== null) {
$_columns[$key]['format'] = $value_log[1];
}
if (isset($value_log[2]) && $value_log[2] !== null) {
$_columns[$key]['header'] = $value_log[2];
}
} elseif (is_array($value)) {
if (!isset($value['attribute']) && !isset($value['value'])) {
throw new \InvalidArgumentException('Attribute or Value must be defined.');
}
$_columns[$key] = $value;
}
}
return $_columns;
}
/**
* Formatter for i18n.
* @return Formatter
*/
public function formatter()
{
if (!isset($this->formatter))
$this->formatter = \Yii::$app->getFormatter();
return $this->formatter;
}
/**
* Setting header to download generated file xls
*/
public function setHeaders()
{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $this->getFileName() .'"');
header('Cache-Control: max-age=0');
}
/**
* Getting the file name of exporting xls file
* @return string
*/
public function getFileName()
{
$fileName = 'exports.xlsx';
if (isset($this->fileName)) {
$fileName = $this->fileName;
if (strpos($fileName, '.xlsx') === false)
$fileName .= '.xlsx';
}
return $fileName;
}
/**
* Setting properties for excel file
* @param PHPExcel $objectExcel
* @param array $properties
*/
public function properties(&$objectExcel, $properties = [])
{
foreach ($properties as $key => $value)
{
$keyname = "set" . ucfirst($key);
$objectExcel->getProperties()->{$keyname}($value);
}
}
/**
* saving the xls file to download or to path
*/
public function writeFile($sheet)
{
if (!isset($this->format))
$this->format = 'Xlsx';
$objectwriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($sheet, $this->format);
$path = 'php://output';
if (isset($this->savePath) && $this->savePath != null) {
$path = $this->savePath . '/' . $this->getFileName();
}
$objectwriter->setOffice2003Compatibility($this->compatibilityOffice2003);
$objectwriter->setPreCalculateFormulas($this->preCalculationFormula);
$objectwriter->save($path);
if ($path == 'php://output')
exit();
return true;
}
/**
* reading the xls file
*/
public function readFile($fileName)
{
if (!isset($this->format))
$this->format = \PhpOffice\PhpSpreadsheet\IOFactory::identify($fileName);
$objectreader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($this->format);
if ($this->format == "Csv") {
$objectreader->setDelimiter($this->CSVDelimiter);
$objectreader->setInputEncoding($this->CSVEncoding);
}
$objectPhpExcel = $objectreader->load($fileName);
$sheetCount = $objectPhpExcel->getSheetCount();
$sheetDatas = [];
if ($sheetCount > 1) {
foreach ($objectPhpExcel->getSheetNames() as $sheetIndex => $sheetName) {
if (isset($this->getOnlySheet) && $this->getOnlySheet != null) {
if(!$objectPhpExcel->getSheetByName($this->getOnlySheet)) {
return $sheetDatas;
}
$objectPhpExcel->setActiveSheetIndexByName($this->getOnlySheet);
$indexed = $this->getOnlySheet;
$sheetDatas[$indexed] = $objectPhpExcel->getActiveSheet()->toArray(null, true, true, true);
if ($this->setFirstRecordAsKeys) {
$sheetDatas[$indexed] = $this->executeArrayLabel($sheetDatas[$indexed]);
}
if (!empty($this->getOnlyRecordByIndex)) {
$sheetDatas[$indexed] = $this->executeGetOnlyRecords($sheetDatas[$indexed], $this->getOnlyRecordByIndex);
}
if (!empty($this->leaveRecordByIndex)) {
$sheetDatas[$indexed] = $this->executeLeaveRecords($sheetDatas[$indexed], $this->leaveRecordByIndex);
}
return $sheetDatas[$indexed];
} else {
$objectPhpExcel->setActiveSheetIndexByName($sheetName);
$indexed = $this->setIndexSheetByName==true ? $sheetName : $sheetIndex;
$sheetDatas[$indexed] = $objectPhpExcel->getActiveSheet()->toArray(null, true, true, true);
if ($this->setFirstRecordAsKeys) {
$sheetDatas[$indexed] = $this->executeArrayLabel($sheetDatas[$indexed]);
}
if (!empty($this->getOnlyRecordByIndex) && isset($this->getOnlyRecordByIndex[$indexed]) && is_array($this->getOnlyRecordByIndex[$indexed])) {
$sheetDatas = $this->executeGetOnlyRecords($sheetDatas, $this->getOnlyRecordByIndex[$indexed]);
}
if (!empty($this->leaveRecordByIndex) && isset($this->leaveRecordByIndex[$indexed]) && is_array($this->leaveRecordByIndex[$indexed])) {
$sheetDatas[$indexed] = $this->executeLeaveRecords($sheetDatas[$indexed], $this->leaveRecordByIndex[$indexed]);
}
}
}
} else {
$sheetDatas = $objectPhpExcel->getActiveSheet()->toArray(null, true, true, true);
if ($this->setFirstRecordAsKeys) {
$sheetDatas = $this->executeArrayLabel($sheetDatas);
}
if (!empty($this->getOnlyRecordByIndex)) {
$sheetDatas = $this->executeGetOnlyRecords($sheetDatas, $this->getOnlyRecordByIndex);
}
if (!empty($this->leaveRecordByIndex)) {
$sheetDatas = $this->executeLeaveRecords($sheetDatas, $this->leaveRecordByIndex);
}
}
return $sheetDatas;
}
/**
* (non-PHPdoc)
* @see \yii\base\Widget::run()
*/
public function run()
{
if ($this->mode == 'export')
{
$sheet = new Spreadsheet();
if (!isset($this->models))
throw new InvalidConfigException('Config models must be set');
if (isset($this->properties))
{
$this->properties($sheet, $this->properties);
}
if ($this->isMultipleSheet) {
$index = 0;
$worksheet = [];
foreach ($this->models as $title => $models) {
$sheet->createSheet($index);
$sheet->getSheet($index)->setTitle($title);
$worksheet[$index] = $sheet->getSheet($index);
$columns = isset($this->columns[$title]) ? $this->columns[$title] : [];
$headers = isset($this->headers[$title]) ? $this->headers[$title] : [];
$this->executeColumns($worksheet[$index], $models, $this->populateColumns($columns), $headers);
$index++;
}
} else {
$worksheet = $sheet->getActiveSheet();
$this->executeColumns($worksheet, $this->models, isset($this->columns) ? $this->populateColumns($this->columns) : [], isset($this->headers) ? $this->headers : []);
}
if ($this->asAttachment) {
$this->setHeaders();
}
$this->writeFile($sheet);
$sheet->disconnectWorksheets();
unset($sheet);
}
elseif ($this->mode == 'import')
{
if (is_array($this->fileName)) {
$datas = [];
foreach ($this->fileName as $key => $filename) {
$datas[$key] = $this->readFile($filename);
}
return $datas;
} else {
return $this->readFile($this->fileName);
}
}
}
/**
* Exporting data into an excel file.
*
* ~~~
*
* \moonland\phpexcel\Excel::export([
* 'models' => $allModels,
* 'columns' => ['column1','column2','column3'],
* //without header working, because the header will be get label from attribute label.
* 'header' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
* ]);
*
* ~~~
*
* New Feature for exporting data, you can use this if you familiar yii gridview.
* That is same with gridview data column.
* Columns in array mode valid params are 'attribute', 'header', 'format', 'value', and footer (TODO).
* Columns in string mode valid layout are 'attribute:format:header:footer(TODO)'.
*
* ~~~
*
* \moonland\phpexcel\Excel::export([
* 'models' => Post::find()->all(),
* 'columns' => [
* 'author.name:text:Author Name',
* [
* 'attribute' => 'content',
* 'header' => 'Content Post',
* 'format' => 'text',
* 'value' => function($model) {
* return ExampleClass::removeText('example', $model->content);
* },
* ],
* 'like_it:text:Reader like this content',
* 'created_at:datetime',
* [
* 'attribute' => 'updated_at',
* 'format' => 'date',
* ],
* ],
* 'headers' => [
* 'created_at' => 'Date Created Content',
* ],
* ]);
*
* ~~~
*
* @param array $config
* @return string
*/
public static function export($config=[])
{
$config = ArrayHelper::merge(['mode' => 'export'], $config);
return self::widget($config);
}
/**
* Import file excel and return into an array.
*
* ~~~
*
* $data = \moonland\phpexcel\Excel::import($fileName, ['setFirstRecordAsKeys' => true]);
*
* ~~~
*
* @param string!array $fileName to load.
* @param array $config is a more configuration.
* @return string
*/
public static function import($fileName, $config=[])
{
$config = ArrayHelper::merge(['mode' => 'import', 'fileName' => $fileName, 'asArray' => true], $config);
return self::widget($config);
}
/**
* @param array $config
* @return string
*/
public static function widget($config = [])
{
if ((isset($config['mode']) and $config['mode'] == 'import') && !isset($config['asArray'])) {
$config['asArray'] = true;
}
if (isset($config['asArray']) && $config['asArray']==true)
{
$config['class'] = get_called_class();
$widget = \Yii::createObject($config);
return $widget->run();
} else {
return parent::widget($config);
}
}
}