Sometime as a PHP Developer, I usually get task to create report in
excel. Of course I use PHPExcel to generate excel document. To use
PHPExcel library, I must do this step below:
1. Include PHPExcel library
2. Create Object of PHPExcel
3. Set Active Sheet
4. Set style of column and row
5. Set value by column and row
6. Set filename and save
If
we just need 1 column and 1 row with a less data we can do it fast and
simple. For example we need to create 1 excel file with 5 rows data,
result from mysql query, there are:
No Name City
1 Dono Yogyakarta
2 Rina Wonosobo
3 Danu Purworejo
4 Riki Jakarta
5 Agnes Bogor
First, we need to set each column style (A, B and C column because we just need No, Name, and City for header):
$this->sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
(to set center alignment);
$this->sheet->getColumnDimension('A1')->setWidth(5); (to set column size);
$this->sheet->setCellValueByColumnAndRow('A', 1, 'No');
Do the same thing to B and C column. Of course we can set others style that has described in PHPExcel Documentation.
If we done with the header section, then we need to set style and value of each data, we can use for loop:
$lastRow = 2;
foreach ($resultData AS $key => $value) {
$no = $key+1;
$this->sheet->setCellValueByColumnAndRow('A', $lastRow, $no);
$this->sheet->setCellValueByColumnAndRow('B', $lastRow, $value['name']);
$this->sheet->setCellValueByColumnAndRow('C', $lastRow, $value['city']);
$lastRow++;
}
We also set its style, if needed. After set the style and value, we need to set filename and then download it:
$this->obj_PHPExcel->SetFileName('Data'.$datetime . '.xls');
$this->obj_PHPExcel->Save();
exit;
All
clear, but if we have too many data to export, we need more time to set
style and value of each column and row with above way. And I have
create functions to make it more easy, these functions describes below:
/**
* Use to set title of excel sheet (title location is above header)
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param int $row : row index
* @param array $arrTitle : title (title1, title2, ...)
* @param int $column : column name -> using getColumnString($number)
*/
function title($row, $arrTitle, $column) {
if (!empty($arrTitle)) {
for ($i = 0; $i < count($arrTitle); $i++) {
$this->sheet->mergeCells('A' . $row . ':' . $column . $row)->setCellValue('A' . $row, $arrTitle[$i]);
//echo "<pre>"; echo print_r($arrTitle[$i]);exit();
$this->sheet->getStyle('A' .
$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle('A' . $row)->applyFromArray($this->fontBold);
$row++;
}
$this->index = $row;
}
}
/**
* This function used to create footer of excel sheet
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param int $row : row index
* @param array $arrTitle : title (title1, title2, ...)
* @param int $column : column name -> using getColumnString($number)
*/
function footer($last, $arrTitle, $column) {
if (!empty($arrTitle)) {
for ($i = 0; $i < count($arrTitle); $i++) {
$this->sheet->mergeCells('D' . $last . ':' . $column .
$last)->setCellValue('D' . $last, $arrTitle[$i]);
$this->sheet->getStyle('D' .
$last)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->sheet->getStyle('D' . $last)->applyFromArray($this->fontBold);
$row++;
}
$this->index = $row;
}
}
/**
* This function used to create header table
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param array $cols
* @param int $last_col : column index
* @param int $last_row : row index
*/
private function setHeader($cols, $last_col, $last_row) {
foreach ($cols as $col) {
$this->sheet->setCellValueByColumnAndRow($last_col, $last_row, $col['header']);
if ($col['size'])
$this->sheet->getColumnDimension(getColumnString($last_col))->setWidth($col['size']);
else
$this->sheet->getColumnDimension(getColumnString($last_col))->setWidth()->setAutoSize(true);
$this->sheet->getStyleByColumnAndRow($last_col,
$last_row)->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle('A' . $last_row . ':' .
(getColumnString(count($cols) - 1)) .
$last_row)->applyFromArray($this->border);
$this->sheet->getStyle('A' . $last_row . ':' .
(getColumnString(count($cols) - 1)) .
$last_row)->applyFromArray($this->fontBold);
$last_col++;
}
$this->index = $last_row;
}
/**
* This function used to initialize $cols variable ($cols used to write data into column)
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param string $arrData : field
* @param array $arrAlign : column align
* @param array $arrSize : column width
* @param array $arrHeader : header table
* @return array
*/
private function setCols($arrData, $arrAlign, $arrSize, $arrHeader) {
$cols = array();
if (!empty($arrData)) {
for ($i = 0; $i < count($arrData); $i++) {
$cols[$i] = array(
'header' => $arrHeader[$i],
'data' => $arrData[$i],
'size' => $arrSize[$i],
'align' => $arrAlign[$i]
);
}
} else if (!empty($arrHeader)) {
for ($i = 0; $i < count($arrHeader); $i++) {
$cols[$i] = array(
'header' => $arrHeader[$i],
'align' => 'center',
'size' => $arrSize[$i]
);
}
}
return $cols;
}
# make excel function
/**
* This function used to write data into excel table
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param array $data : The data from query that use to write to excel table
* @param array $cols : array that define header, data, align and size
* @param boolean $total : if report need total row at the bottom of table
*/
private function makeExcel($data, $cols, $total , $firstRow) {
$last_row = 1 ;
$first_row = $last_row;
$last_col = 0;
$columnName = getColumnString(count($cols) - 1);
$title = array($this->title1, $this->label_jalur_global);
$this->title($last_row , $title, $columnName);
$last_row = $this->index + 1 + $firstRow;
$this->setHeader($cols, $last_col, $last_row);
if (!empty($data)) {
// foreach($mergeHorizontal as $key => $merge){
//
$this->sheet->mergeCells(getColumnString($merge[$key]['first_column'])
. $merge[$key]['first_row'] . ':' . $merge[$key]['last_column'] .
$merge[$key]['last_row']);
// }
$no = 1;
foreach ($data as $rows) {
$last_row++;
$first_row = $last_row;
$rows['no'] = $no;
$last_col = 0;
$this->writeDataCol($last_col, $last_row, $rows, $cols);
$no++;
$this->sheet->getStyle('A' . $first_row . ':' .
(getColumnString(count($cols) - 1)) .
($last_row))->applyFromArray($this->border);
}
$last_row++;
if ($total) {
$this->total((count($cols) - 1), $last_row, $last_row, $cols, $data);
}
} else {
$last_row = $this->index + 1;
$this->sheet->mergeCells('A' . $last_row . ':' . $columnName .
$last_row)->setCellValue('A' . $last_row, '-- TIDAK ADA DATA --');
$this->sheet->getStyle('A' .
$last_row)->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle('A' . $last_row)->applyFromArray($this->fontBold);
$this->sheet->getStyle('A' . $last_row . ':' . $columnName . $last_row)->applyFromArray($this->border);
}
}
/**
* This function used to write data to table column
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param int $last_col : column index
* @param int $row : row index
* @param array $rows : data
* @param array $cols
*/
function writeDataCol($last_col, $row, $rows, $cols, $idLap = '') {
foreach ($cols as $col) {
$string = $rows[$col['data']];
if ($string[0] == '0') {
# if first carachter of string is 0, than set format cell to String
$this->sheet->setCellValueExplicit(getColumnString($last_col) .
$row, $string, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$this->sheet->setCellValueByColumnAndRow($last_col, $row, $string);
}
if (isset($col['align'])) {
# set column alignment of cells
switch ($col['align']) {
case 'center':
$align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
break;
case 'left':
$align = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
break;
case 'right':
$align = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
break;
case 'justify':
$align = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
break;
}
$this->sheet->getStyle(getColumnString($last_col) . $row)->getAlignment()->setHorizontal($align);
}
if (isset($col['size'])) {
# set column size
$this->sheet->getColumnDimension(getColumnString($last_col))->setWidth($col['size']);
} else {
# set to auto size
$this->sheet->getColumnDimension(getColumnString($last_col))->setWidth()->setAutoSize(true);
}
if (isset($col['wrap']) AND $col['wrap'] === true) {
# set to wrap text
$this->sheet->getStyleByColumnAndRow($last_col, $row)->getAlignment()->setWrapText(true);
}
$last_col++;
$this->last_col = $last_col;
}
}
/**
* This function set TOTAL row, if needed. This row will be add at the end of table data row
* @param int $start : The first index column that will be merged
* @param int $first_row : The first index row after header table
* @param int $last_row : The last index row
* @param array $cols
* @param array $data
*/
private function total($start, $first_row, $last_row, $cols, $data) {
# The first data that will be sum
$data_pertama = $last_row - (count($data));
# The last column
$last_col_name = (getColumnString(count($cols) - 1));
if ($start > 0) {
$this->sheet->mergeCells('A' . $last_row . ':' . getColumnString($start - 1) . $last_row);
}
$this->sheet->setCellValue('A' . $last_row, 'TOTAL');
for ($i = $start; $i < count($cols); $i++) {
$this->sheet->setCellValue(getColumnString($i) . $last_row,
'=SUM(' . getColumnString($i) . $data_pertama . ':' .
getColumnString($i) . ($last_row - 1) . ')');
}
$this->sheet->getStyle('A' . $first_row . ':' . $last_col_name .
($last_row))->applyFromArray($this->border);
$this->sheet->getStyle('A' . $last_row . ':' . $last_col_name .
($last_row))->applyFromArray($this->fontBold);
$this->sheet->getStyle('A' . $last_row . ':' . $last_col_name .
($last_row))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
/**
* This function used to export to excel file (2003 -> .xls)
* @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
* @param String $name : nama untuk file excel nya
*/
private function printExcel($name) {
$this->obj_PHPExcel->SetFileName($name . '.xls');
$this->obj_PHPExcel->Save();
exit;
}
And here is example how to do with the functions:
The data result should be like this (multidimensional array) :
Array {
[0] =>
[name] => Dino
[address] => Papringan
[city] => Yogyakarta
[gender] => Male
[phone] => 085227052001
[1] =>
[name] => Rina
[address] => Grogol
[city] => Jakarta
[gender] => Female
[phone] => 088774567123
}
private function report() {
$arrHeader = array('No', 'Name', 'Address', 'City', 'Gender', 'Phone Number');
$arrData = array('no', 'name', 'address', 'city', 'gender', 'phone');
$arrSize = array(5, 35, 35, 30, 10, 15);
$arrAlign = array('center', 'left', 'left', 'left', 'left', 'center');
$cols = $this->setCols($arrData, $arrAlign, $arrSize, $arrHeader);
$data = $this->obj_recap->getRecapData();
$this->makeExcel($data, $cols, false);
$this->printExcel('Recap Data' . '_' . $this->date);
}
If
we must create many reports in a single php file, this function is
usefull for me, just copy functions above in a single php file, then
include it in php file that use to create excel file. But this code
still can improved, but I don't have much time to improve it anymore :D,
if you have your own idea to share please don't hesitate to write
comment below.
Sharing is carring :D
