01.
<?php
02.
03.
namespace App\Libraries;
04.
05.
use
Excel;
06.
07.
class
MsExcel {
08.
public
static
function
export(
$filename
,
$dataset
) {
09.
Excel::create(
$filename
,
function
(
$excel
)
use
(
$dataset
) {
10.
foreach
(
$dataset
as
$datatable
) {
11.
$excel
->sheet(
$datatable
[
'SheetName'
],
function
(
$sheet
)
use
(
$datatable
) {
12.
self::pageSetup(
$sheet
,
$datatable
[
'Format'
]);
13.
self::title(
$sheet
,
$datatable
[
'Title'
],
count
(
$datatable
[
'Header'
]));
14.
self::header(
$sheet
,
$datatable
[
'Header'
]);
15.
self::fillData(
$sheet
,
$datatable
[
'Data'
]);
16.
self::home(
$sheet
);
17.
});
18.
}
19.
20.
$excel
->setActiveSheetIndex(0);
21.
})->export(
'xlsx'
);
22.
}
23.
24.
protected
static
function
pageSetup(
$sheet
,
$format
) {
25.
$sheet
->setOrientation(
'landscape'
);
26.
$sheet
->setPageMargin(0.5);
27.
$sheet
->setFontFamily(
'Microsoft Sans Serif'
);
28.
$sheet
->setFontSize(10);
29.
30.
$sheet
->setColumnFormat(
$format
);
31.
}
32.
33.
protected
static
function
title(
$sheet
,
$title
,
$merge
) {
34.
$column
= self::stringFromColumnIndex(
$merge
) .
'1'
;
35.
36.
$sheet
->mergeCells(
'A1:'
.
$column
);
37.
$sheet
->cell(
'A1'
,
$title
);
38.
$sheet
->cell(
'A1'
,
function
(
$cell
) {
39.
$cell
->setAlignment(
'center'
);
40.
$cell
->setFontWeight(
'bold'
);
41.
});
42.
}
43.
44.
protected
static
function
header(
$sheet
,
$header
) {
45.
$col
= 1;
46.
47.
foreach
(
$header
as
$name
) {
48.
$column
= self::stringFromColumnIndex(
$col
) .
'3'
;
49.
50.
$sheet
->cell(
$column
,
$name
);
51.
$sheet
->cell(
$column
,
function
(
$cell
) {
52.
$cell
->setBorder(
'thin'
,
'thin'
,
'thin'
,
'thin'
);
53.
$cell
->setBackground(
'#f2f5a9'
);
54.
$cell
->setAlignment(
'center'
);
55.
});
56.
57.
$col
++;
58.
}
59.
}
60.
61.
protected
static
function
fillData(
$sheet
,
$data
) {
62.
$row
= 4;
63.
64.
foreach
(
$data
as
$datarow
) {
65.
$col
= 1;
66.
67.
foreach
(
$datarow
as
$value
) {
68.
$column
= self::stringFromColumnIndex(
$col
) .
$row
;
69.
70.
$sheet
->setBorder(
$column
,
'thin'
);
71.
$sheet
->cell(
$column
,
$value
);
72.
73.
$col
++;
74.
}
75.
76.
$row
++;
77.
}
78.
}
79.
80.
protected
static
function
home(
$sheet
) {
81.
$sheet
->cell(
'A1'
,
function
(
$cell
) {
82.
$cell
->setBorder(
'none'
,
'none'
,
'none'
,
'none'
);
83.
});
84.
}
85.
86.
protected
static
function
stringFromColumnIndex(
$num
) {
87.
$numeric
= (
$num
- 1) % 26;
88.
$letter
=
chr
(65 +
$numeric
);
89.
$num2
=
intval
((
$num
- 1) / 26);
90.
91.
if
(
$num2
> 0) {
92.
return
getNameFromNumber(
$num2
) .
$letter
;
93.
}
94.
else
{
95.
return
$letter
;
96.
}
97.
}
98.
}