/
excel.php
executable file
·133 lines (111 loc) · 4.57 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
<?php
require_once 'phpexcel-1.7.6/Classes/PHPExcel.php';
/**
* @param int $year year of the required data
* @param int $month month of the required data
* @param string $what name of the statistic, used for titles
* @param array $col_header titles of the columns
* @param array $report multi dimensional array containing data
* @param int $total_visitor number of total visitors
* @param array $col_format settings for the format of the excel columns
* @param int $col_total_visitor excel column in which write total visitors number
* @return null send the file to be saved
*/
function create_report($year, $month, $what, $col_header, $col_format, $report, $total_visitor, $col_total_visitor=0) {
// Preparations
$_ALPHA = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$str_stat = 'statistics';
$str_tot = 'total';
$first_line = 3;
$format = array(
'text' => PHPExcel_Style_NumberFormat::FORMAT_TEXT,
'float' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1,
'int1000' => '#,##0',
'int' => '###0',
'time' => 'hh:mm:ss'
);
$date_name = date('F Y', mktime(0, 0, 0, $month, 1, $year));
$title = ucwords($what.' '.$str_stat);
$col_len = count($col_header) - 1;
// Thin black border
$styleThinBlackBorderAll = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,
'color' => array('argb' => 'FF000000'),
),
),
);
// Thick black border
$styleThickBlackBorderOutline = array(
'borders' => array(
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array('argb' => 'FF000000'),
),
),
);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()
->setCreator('Giulia Alfonsi - Pure360')
->setLastModifiedBy('Giulia Alfonsi - Pure360')
->setTitle($title.' - '.$date_name)
->setKeywords($title.' '.$date_name);
// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$sh1 = $objPHPExcel->getActiveSheet();
$sh1->setCellValue('A1', $title.' - '.$date_name);
foreach ($col_header as $k => $h) {
$sh1->setCellValue($_ALPHA[$k].$first_line, $h);
}
$i = $first_line + 1;
foreach ($report as $row) {
foreach ($row as $k => $v) {
$sh1->setCellValue($_ALPHA[$k].$i, $v);
}
$i++;
}
// Total visitors
if ($col_total_visitor > 0) {
$sh1->setCellValue($_ALPHA[$col_total_visitor - 2] . $i, ucfirst($str_tot))
->setCellValue($_ALPHA[$col_total_visitor - 1] . $i, $total_visitor);
// style bold
$sh1->getStyle('A'.$i.':'.$_ALPHA[$col_len].$i)->getFont()->setBold(true);
// style borders
$sh1->getStyle('A'.$i.':'.$_ALPHA[$col_len].$i)->applyFromArray($styleThinBlackBorderAll);
$sh1->getStyle('A'.$i.':'.$_ALPHA[$col_len].$i)->applyFromArray($styleThickBlackBorderOutline);
}
// Set autofilter
$objPHPExcel->getActiveSheet()->setAutoFilter('A'.$first_line.':'.$_ALPHA[$col_len].$i);
// Set repeated row
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(3, 3);
// Style
// align
$sh1->mergeCells('A1:'.$_ALPHA[$col_len].'1')->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sh1->getStyle('A'.$first_line.':'.$_ALPHA[$col_len].$first_line)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// bold
$sh1->getStyle('A1')->getFont()->setBold(true);
$sh1->getStyle('A'.$first_line.':'.$_ALPHA[$col_len].$first_line)->getFont()->setBold(true);
// borders
$sh1->getStyle('A'.$first_line.':'.$_ALPHA[$col_len].($i - 1))->applyFromArray($styleThinBlackBorderAll);
for ($c = 0; $c <= $col_len; $c++) {
// column widths
$sh1->getColumnDimension($_ALPHA[$c])->setAutoSize(true);
// cell number formats
$sh1->getStyle($_ALPHA[$c].($first_line + 1).':'.$_ALPHA[$c].$i)->getNumberFormat()->setFormatCode($format[$col_format[$c]]);
}
// Rename sheet
$sh1->setTitle(ucfirst($what).' '.$date_name);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$what.'_'.$year.'-'.str_pad($month, 2, '0', STR_PAD_LEFT).'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
} // end create_report
?>