/** * Inserts an array of data into an Excel Worksheet * * @return PHPExcel */ function insertIntoExcel($arrRows, $arrCols, $arrMeta = array(), $arrCollapse = array()) { include_once 'lib/phpexcel/Classes/PHPExcel.php'; // Insert the data function addRows($arrData, $intRow, $exlSheet) { foreach ($arrData as $arrRow) { if ($arrRow === false) { continue; } if (!is_array($arrRow)) { $arrRow = array($arrRow); } foreach ($arrRow as $intCol => $mxtValue) { $exlCell = $exlSheet->getCellByColumnAndRow($intCol, $intRow); if ($mxtValue) { $exlCell->setDataType(PHPExcel_Cell_DataType::TYPE_STRING); if (is_array($mxtValue)) { if (isset($mxtValue['type']) && $mxtValue['type'] == 'number') { $exlCell->setDataType(PHPExcel_Cell_DataType::TYPE_NUMERIC); $exlSheet->getStyleByColumnAndRow($intCol, $intRow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); } if (isset($mxtValue['styles'])) { $style = $exlSheet->getStyleByColumnAndRow($intCol, $intRow); if (in_array('bold', $mxtValue['styles'])) { $style->getFont()->setBold(true); } if (in_array('italic', $mxtValue['styles'])) { $style->getFont()->setItalic(); } if (in_array('underine', $mxtValue['styles'])) { $style->getFont()->setUnderline(); } if (in_array('strikethrough', $mxtValue['styles'])) { $style->getFont()->setStrikethrough(); } if (isset($mxtValue['styles']['size'])) { $style->getFont()->setSize($mxtValue['styles']['size']); } if (isset($mxtValue['styles']['color'])) { $style->getFont()->setColor($mxtValue['styles']['color']); } if (isset($mxtValue['styles']['align'])) { switch ($mxtValue['styles']['align']) { case 'left': $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); break; case 'right': $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); break; case 'center': $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); break; } } } } $exlCell->setValue(is_array($mxtValue) ? isset($mxtValue['value']) ? $mxtValue['value'] : '' : $mxtValue); } } $intRow++; } return $intRow; } // Create the workbook and set the meta data $exlWorkbook = new PHPExcel(); if ($arrMeta) { $exlProp = $exlWorkbook->getProperties(); foreach ($arrMeta as $key => $value) { switch ($key) { case 'creator': $exlProp->setCreator($value); $exlProp->setLastModifiedBy($value); break; case 'title': $exlProp->setTitle($value); $exlProp->setSubject($value); break; case 'description': $exlProp->setDescription($value); break; case 'keywords': $exlProp->setKeywords($value); break; case 'category': $exlProp->setCategory($value); break; } } } // Initialize the main worksheet $intRow = 1; $exlSheet = $exlWorkbook->getActiveSheet(); $exlSheet->getDefaultStyle()->getFont()->setName('Calibri')->setSize(11); if (isset($arrMeta['title'])) { $exlSheet->setTitle(substr($arrMeta['title'], 0, 12)); $exlSheet->setCellValueByColumnAndRow(0, $intRow, $arrMeta['title']); $style = $exlSheet->getStyleByColumnAndRow(0, $intRow)->getFont()->setBold(true)->setSize(16); $intRow++; } if (isset($arrMeta['intro']) && is_array($arrMeta['intro'])) { $intRow++; $intRow = addRows($arrMeta['intro'], $intRow, $exlSheet); } // Initialize the columns $intRow++; foreach ($arrCols as $intCol => $mxtValue) { $exlSheet->setCellValueByColumnAndRow($intCol, $intRow, isset($mxtValue['title']) ? $mxtValue['title'] : $mxtValue); $style = $exlSheet->getStyleByColumnAndRow($intCol, $intRow); $style->getFont()->setBold(true); $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM); if (isset($mxtValue['width'])) { $exlSheet->getColumnDimensionByColumn($intCol)->setWidth((double) $mxtValue['width']); } } $intOffset = $intRow; $intRow++; // Add the data rows $intRow = addRows($arrRows, $intRow, $exlSheet); // Add the filter $exlSheet->setAutoFilterByColumnAndRow(0, $intOffset, sizeof($arrCols) - 1, $intOffset + sizeof($arrRows) - 1); foreach ($arrCollapse as $from => $to) { for ($i = $from; $i < $to; $i++) { $exlSheet->getColumnDimensionByColumn($i)->setOutlineLevel(1); $exlSheet->getColumnDimensionByColumn($i)->setVisible(false); } $exlSheet->getColumnDimensionByColumn($to)->setCollapsed(true); } $exlWorkbook->setActiveSheetIndex(0); $exlSheet->getCellByColumnAndRow(0, $intOffset); $exlSheet->freezePaneByColumnAndRow(0, $intOffset + 1); // Add the outro if (isset($arrMeta['intro']) && is_array($arrMeta['intro'])) { foreach ($arrMeta['intro'] as $introRow) { $intRow++; } } return $exlWorkbook; }
function addRows($table, $rows) { if (addRows($table, $rows)) { $this->updated = true; } }