PHP PHPExcel类代码示例

 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     if (!is_null($this->_spreadSheet)) {
         // Garbage collect...
         foreach ($this->_spreadSheet->getAllSheets() as $sheet) {
             $sheet->garbageCollect();
         }
         // Create new ZIP file and open it for writing
         $objZip = new ZipArchive();
         // Try opening the ZIP file
         if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
             if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) {
                 throw new Exception("Could not open " . $pFilename . " for writing.");
             }
         }
         // Add media
         $sheetCount = $this->_spreadSheet->getSheetCount();
         for ($i = 0; $i < $sheetCount; ++$i) {
             for ($j = 0; $j < $this->_spreadSheet->getSheet($i)->getDrawingCollection()->count(); ++$j) {
                 if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j) instanceof PHPExcel_Worksheet_BaseDrawing) {
                     $imgTemp = $this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j);
                     $objZip->addFromString('media/' . $imgTemp->getFilename(), file_get_contents($imgTemp->getPath()));
                 }
             }
         }
         // Add phpexcel.xml to the document, which represents a PHP serialized PHPExcel object
         $objZip->addFromString('phpexcel.xml', $this->_writeSerialized($this->_spreadSheet, $pFilename));
         // Close file
         if ($objZip->close() === false) {
             throw new Exception("Could not close zip file {$pFilename}.");
         }
     } else {
         throw new Exception("PHPExcel object unassigned.");
     }
 }

 function exportExcel($expTitle, $expCellName, $expTableData)
 {
     $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
     //文件名称
     $fileName = $_SESSION['account'] . date('_YmdHis');
     //or $xlsTitle 文件名称可根据自己情况设定
     $cellNum = count($expCellName);
     $dataNum = count($expTableData);
     vendor("PHPExcel.PHPExcel");
     $objPHPExcel = new \PHPExcel();
     $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
     $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
     //合并单元格
     for ($i = 0; $i < $cellNum; $i++) {
         $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
     }
     for ($i = 0; $i < $dataNum; $i++) {
         for ($j = 0; $j < $cellNum; $j++) {
             $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
         }
     }
     header('pragma:public');
     header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
     header("Content-Disposition:attachment;filename={$fileName}.xls");
     //attachment新窗口打印inline本窗口打印
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
     exit;
 }

 public function output()
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     $objSheet = $objPHPExcel->setActiveSheetIndex(0);
     $col = 0;
     $row = 1;
     if (isset($this->header)) {
         foreach ($this->header as $v) {
             $cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
             $objSheet->setCellValue($cell, $v);
             $col++;
         }
         $row++;
         $col = 0;
     }
     foreach ($this->content as $rowValue) {
         foreach ($rowValue as $_v) {
             $cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
             $objSheet->setCellValue($cell, $_v);
             $col++;
         }
         $row++;
         $col = 0;
     }
     // Rename worksheet
     $objPHPExcel->getActiveSheet()->setTitle($this->title);
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objPHPExcel->setActiveSheetIndex(0);
     $this->browserExport($this->type, $this->filename);
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $this->type);
     $objWriter->save('php://output');
 }

 public static function usersReport()
 {
     $conn = new Connect();
     $query = 'SELECT * FROM ' . self::DB_TBL_USUARIOS;
     $consult = $conn->prepare($query);
     $consult->execute();
     if ($consult->rowCount() > 0) {
         header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
         header('Content-Disposition: attachment;filename="Reporte de usuarios.xlsx"');
         header('Cache-Control: max-age=0');
         $reportName = "Reporte de usuarios";
         $reportNameTitles = array("Id", "Cédula", "Nombres", "Apellidos", "Email", "Teléfono", "Extensión", "Usuario", "Contrasena", "Rol");
         $styleColumnsTitle = array('font' => array('name' => 'Arial', 'bold' => true));
         $generarReporteXLSX = new PHPExcel();
         $generarReporteXLSX->getProperties()->setCreator("VideoConferencias UTPL")->setLastModifiedBy("VideoConferencias UTPL")->setTitle("Reporte de usuarios")->setSubject("Reporte de usuarios")->setDescription("Reporte de usuarios")->setKeywords("Reporte de usuarios")->setCategory("Reportes");
         $generarReporteXLSX->setActiveSheetIndex(0)->mergeCells('A1:J1');
         $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A1', $reportName)->setCellValue('A3', $reportNameTitles[0])->setCellValue('B3', $reportNameTitles[1])->setCellValue('C3', $reportNameTitles[2])->setCellValue('D3', $reportNameTitles[3])->setCellValue('E3', $reportNameTitles[4])->setCellValue('F3', $reportNameTitles[5])->setCellValue('G3', $reportNameTitles[6])->setCellValue('H3', $reportNameTitles[7])->setCellValue('I3', $reportNameTitles[8])->setCellValue('J3', $reportNameTitles[9]);
         $i = 4;
         while ($row = $consult->fetch()) {
             $generarReporteXLSX->setActiveSheetIndex(0)->setCellValue('A' . $i, $row['id'])->setCellValue('B' . $i, $row['cedula'])->setCellValue('C' . $i, $row['nombres'])->setCellValue('D' . $i, $row['apellidos'])->setCellValue('E' . $i, $row['email'])->setCellValue('F' . $i, $row['telefono'])->setCellValue('G' . $i, $row['telefono_ext'])->setCellValue('H' . $i, $row['usuario'])->setCellValue('I' . $i, $row['contrasena'])->setCellValue('J' . $i, $row['id_rol']);
             $i++;
         }
         $generarReporteXLSX->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleColumnsTitle);
         $generarReporteXLSX->getActiveSheet()->setTitle('Usuarios');
         $generarReporteXLSX->setActiveSheetIndex(0);
         $generarReporteXLSX->getActiveSheet(0)->freezePaneByColumnAndRow(0, 4);
         $objWriter = PHPExcel_IOFactory::createWriter($generarReporteXLSX, 'Excel2007');
         $objWriter->save('php://output');
         exit;
     }
 }

 /**
  * Добавляет таблицу со списком
  *
  * @param PHPExcel $objPHPExcel
  * @param array $reportData
  */
 protected function addDataTable(PHPExcel $objPHPExcel, array $reportData)
 {
     $activeSheet = $objPHPExcel->getActiveSheet();
     $rowc = 11;
     foreach ($reportData['rows'] as $row) {
         $activeSheet->setCellValue('A' . $rowc, $row['number'])->setCellValue('B' . $rowc, date('d.m.Y', strtotime($row['issuing_date'])))->setCellValue('C' . $rowc, $row['paid_date'] ? date('d.m.Y', strtotime($row['paid_date'])) : '')->setCellValue('D' . $rowc, $row['platform_name'])->setCellValue('E' . $rowc, $row['debit'])->setCellValue('F' . $rowc, $row['sum'])->setCellValue('G' . $rowc, $row['is_vat'] ? Yii::app()->params->VAT / 100 : 0)->setCellValue('H' . $rowc, $row['sum_with_vat'])->setCellValue('I' . $rowc, $row['is_paid'] ? 'оплачен' : 'не оплачен')->setCellValue('J' . $rowc, $row['is_active'] ? 'активна' : 'неактивна');
         $activeSheet->getStyle('I' . $rowc)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $row['is_paid'] ? 'eaf1dd' : 'f2dddc')));
         $activeSheet->getStyle('J' . $rowc)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $row['is_active'] ? 'eaf1dd' : 'f2dddc')));
         if ($row['billing_details_type'] == null) {
             $activeSheet->setCellValue('K' . $rowc, 'нет данных');
             $activeSheet->getStyle('K' . $rowc)->getFont()->getColor()->setRGB('bfbfbf');
         } else {
             $activeSheet->setCellValue('K' . $rowc, $row['billing_details_type'] . ': ' . $row['billing_details_text']);
         }
         ++$rowc;
     }
     $this->addTableTotal($activeSheet, $reportData, $rowc);
     $this->formatTable($activeSheet, 'A', 10, 'K', $rowc, array('headerRowHeight' => 27));
     $activeSheet->getStyle('E11:G' . $rowc)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
     $activeSheet->getStyle('G11:F' . $rowc)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
     $activeSheet->getColumnDimension('A')->setWidth(10.71 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(10.71 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(10.71 * 1.05);
     $activeSheet->getColumnDimension('D')->setWidth(13.29 * 1.05);
     $activeSheet->getColumnDimension('E')->setWidth(14.71 * 1.05);
     $activeSheet->getColumnDimension('F')->setWidth(14.43 * 1.05);
     $activeSheet->getColumnDimension('G')->setWidth(13.71 * 1.05);
     $activeSheet->getColumnDimension('H')->setWidth(13.71 * 1.05);
     $activeSheet->getColumnDimension('I')->setWidth(16.14 * 1.05);
     $activeSheet->getColumnDimension('J')->setWidth(19.57 * 1.05);
     $activeSheet->getColumnDimension('K')->setWidth(35.71 * 1.05);
 }

 public function actionCsv($pars = 0)
 {
     if ($pars) {
         $model = Operation::find()->where("pars = :pars", [':pars' => (int) $pars])->orderBy("url asc")->all();
     } else {
         $model = Operation::find()->orderBy("url asc")->all();
     }
     include Yii::getAlias('@vendor/phpoffice/phpexcel/Classes/PHPExcel.php');
     include Yii::getAlias('@vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php');
     $objPHPExcel = new \PHPExcel();
     $objPHPExcel->getProperties()->setCreator("Php Shaman")->setLastModifiedBy("Php Shaman")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Data for yelp parsing")->setKeywords("office 2007 openxml php")->setCategory("Data for yelp parsing");
     // Add some data
     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'name')->setCellValue('B1', 'categories')->setCellValue('C1', 'phone')->setCellValue('D1', 'state')->setCellValue('E1', 'city')->setCellValue('F1', 'address')->setCellValue('G1', 'postal')->setCellValue('H1', 'site')->setCellValue('I1', 'description');
     foreach ($model as $k => $o) {
         $cat = [];
         foreach ($o->categories as $c) {
             $cat[] = $c->name;
         }
         $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . ($k + 2), $o->name)->setCellValue('B' . ($k + 2), join(' | ', $cat))->setCellValue('C' . ($k + 2), $o->phone)->setCellValue('D' . ($k + 2), ParsSettings::getState($o->state))->setCellValue('E' . ($k + 2), $o->city)->setCellValue('F' . ($k + 2), $o->address)->setCellValue('G' . ($k + 2), $o->postal)->setCellValue('H' . ($k + 2), $o->site)->setCellValue('I' . ($k + 2), $o->description);
     }
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="pars.xls"');
     header('Cache-Control: max-age=0');
     header('Cache-Control: max-age=1');
     header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
     header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
     header('Cache-Control: cache, must-revalidate');
     header('Pragma: public');
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
 }

function databaseToExcel($tableName, $firmId, $examId, $dir)
{
    $filename = "sinav_" . $firmId . "_" . $examId . ".xls";
    $db =& JFactory::getDBO();
    $query = "SELECT * FROM " . $tableName . " WHERE firmaId = " . $firmId . " AND sinavId = " . $examId;
    $db->setQuery($query);
    $data = $db->loadRowList();
    $tableFields = getTableFields($tableName);
    $columnNumber = getNumColumns($tableName);
    $phpExcel = new PHPExcel();
    //Configure the sheet
    $sheet = $phpExcel->getActiveSheet();
    $sheet->setTitle("jos_deneme");
    //Header
    for ($i = 3; $i < $columnNumber; $i++) {
        $value = $tableFields[$i];
        $sheet->setCellValueByColumnAndRow($i - 3, 1, $value);
    }
    //Data
    $row = 2;
    foreach ($data as $rows) {
        $column = 0;
        for ($i = 3; $i < $columnNumber; $i++) {
            $sheet->setCellValueByColumnAndRow($column, $row, $rows[$i]);
            $column++;
        }
        $row++;
    }
    $writer = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
    $writer->save($dir . $filename);
    $redirect = JURI::base() . $dir . $filename;
    header('Location: ' . $redirect);
}

 public function registrations($registrations = [])
 {
     ini_set('memory_limit', '1G');
     $headings = ['ID', 'Name', 'Email', 'Country', 'Items', 'Created'];
     $headingRow = 1;
     $highestHeadingCol = count($headings) - 1;
     $excel = new \PHPExcel();
     $sheet = $excel->getActiveSheet();
     $sheet->setTitle('Registrations')->getStyle($this->cellRange('A', $headingRow, $highestHeadingCol, $headingRow))->applyFromArray(self::$greyHeaderStyles);
     foreach ($headings as $col => $heading) {
         $sheet->setCellValueByColumnAndRow($col, $headingRow, $heading);
     }
     foreach (range('A', $sheet->getHighestColumn()) as $col) {
         $sheet->getColumnDimension($col)->setAutoSize(true);
     }
     foreach ([4] as $col) {
         $sheet->getColumnDimensionByColumn($col)->setAutoSize(false);
     }
     foreach ($registrations as $registration) {
         /** @var $registration Registration  */
         $row = $sheet->getHighestRow() + 1;
         $col = 0;
         $sheet->setCellValueByColumnAndRow($col++, $row, $registration->getId())->setCellValueByColumnAndRow($col++, $row, $registration->getName())->setCellValueByColumnAndRow($col++, $row, $registration->getEmail())->setCellValueByColumnAndRow($col++, $row, $registration->getCountry()->translate('en')->getTitle());
         $items = [];
         foreach ($registration->getItems() as $item) {
             $items[] = $item->getTitle();
         }
         $sheet->setCellValueByColumnAndRow($col++, $row, implode(', ', $items))->setCellValueByColumnAndRow($col++, $row, $registration->getCreatedAt()->format('d.m.Y H:i:s'));
     }
     return $this->retrieveContent($excel);
 }

 public static function downloadExcelFileByArray($data, $fileName = '')
 {
     self::prepare();
     if (!$fileName) {
         $fileName = 'xls-download-' . date('Y-m-d-H-i-s') . '.xls';
     }
     $objPHPExcel = new \PHPExcel();
     $objPHPExcel->getActiveSheet()->fromArray($data);
     $objPHPExcel->getActiveSheet()->freezePane('A2');
     // Redirect output to a client’s web browser (Excel5)
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="' . $fileName . '"');
     header('Cache-Control: max-age=0');
     // If you're serving to IE 9, then the following may be needed
     header('Cache-Control: max-age=1');
     // If you're serving to IE over SSL, then the following may be needed
     header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
     // Date in the past
     header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
     // always modified
     header('Cache-Control: cache, must-revalidate');
     // HTTP/1.1
     header('Pragma: public');
     // HTTP/1.0
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
     $objWriter->save('php://output');
     exit;
 }

 public function initialize($details)
 {
     $this->log("Loading PHPExcel");
     /** PHPExcel_Writer_Excel2007 */
     include 'PHPExcel/PHPExcel/Writer/Excel2007.php';
     /** PHPExcel_Writer_Excel5 - for 2003 and below */
     include 'PHPExcel/PHPExcel/Writer/Excel5.php';
     /** PHPExcel_HTML Writer*/
     include 'PHPExcel/PHPExcel/Writer/HTML.php';
     /** PHPExcel_PDF Writer */
     include 'PHPExcel/PHPExcel/IOFactory.php';
     /*Initialize format array*/
     $this->formats['xls'] = "Excel 2003";
     //$this->formats['xlsx'] = "Excel 2007";
     $this->formats['pdf'] = "Adobe PDF";
     $this->details = $details;
     $this->log("Loading details:" . "<ul>" . "<li>Creator: " . $details['creator'] . "</li>" . "<li>Title: " . $details['title'] . "</li>" . "<li>Format: " . $details['format'] . "</li>" . "<li>Filename: " . $details['fileName'] . "</li>" . "</ul>");
     // Create new PHPExcel object
     $objPHPExcel = new PHPExcel();
     $this->log("Creating report - " . $details['title']);
     // Set properties
     $this->log("Setting properties of report");
     $objPHPExcel->getProperties()->setCreator($details['creator'])->setLastModifiedBy($details['creator'])->setTitle($details['title'])->setSubject($details['title'] . " report")->setDescription($details['title'] . " report generated using PHPExcel and ADODB");
     $this->fileName = $details['fileName'];
     $this->format = $details['format'];
     $this->setPHPExcelObj($objPHPExcel);
 }

 /**
  * Save PHPExcel to file
  *
  * @param  string  $pFilename
  * @throws PHPExcel_Writer_Exception
  */
 public function save($pFilename = null)
 {
     if (!$this->spreadSheet) {
         throw new PHPExcel_Writer_Exception('PHPExcel object unassigned.');
     }
     // garbage collect
     $this->spreadSheet->garbageCollect();
     // If $pFilename is php://output or php://stdout, make it a temporary file...
     $originalFilename = $pFilename;
     if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') {
         $pFilename = @tempnam(PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp');
         if ($pFilename == '') {
             $pFilename = $originalFilename;
         }
     }
     $objZip = $this->createZip($pFilename);
     $objZip->addFromString('META-INF/manifest.xml', $this->getWriterPart('meta_inf')->writeManifest());
     $objZip->addFromString('Thumbnails/thumbnail.png', $this->getWriterPart('thumbnails')->writeThumbnail());
     $objZip->addFromString('content.xml', $this->getWriterPart('content')->write());
     $objZip->addFromString('meta.xml', $this->getWriterPart('meta')->write());
     $objZip->addFromString('mimetype', $this->getWriterPart('mimetype')->write());
     $objZip->addFromString('settings.xml', $this->getWriterPart('settings')->write());
     $objZip->addFromString('styles.xml', $this->getWriterPart('styles')->write());
     // Close file
     if ($objZip->close() === false) {
         throw new PHPExcel_Writer_Exception("Could not close zip file {$pFilename}.");
     }
     // If a temporary file was used, copy it to the correct file stream
     if ($originalFilename != $pFilename) {
         if (copy($pFilename, $originalFilename) === false) {
             throw new PHPExcel_Writer_Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}.");
         }
         @unlink($pFilename);
     }
 }

 public function actionTemplate()
 {
     Yii::import('ext.heart.excel.EHeartExcel', true);
     EHeartExcel::init();
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getActiveSheet()->setCellValue('A1', 'No')->setCellValue('B1', 'ModelType')->setCellValue('C1', 'Model')->setCellValue('D1', 'Controller')->setCellValue('E1', 'ModelParent')->setCellValue('F1', 'ControllerParent');
     $modelPath = Yii::getPathOfAlias('application.models');
     $files = scandir($modelPath);
     $row = 2;
     foreach ($files as $file) {
         if (is_file($modelPath . '/' . $file) && CFileHelper::getExtension($file) === 'php' && !in_array($file, array('ContactForm.php', 'LoginForm.php', 'Admin.php', 'User.php'))) {
             $file_arr = explode(".", $file);
             $filename = $file_arr[0];
             $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row - 1)->setCellValue('B' . $row, "1")->setCellValue('C' . $row, $filename)->setCellValue('D' . $row, 'test/' . $filename)->setCellValue('E' . $row, '-')->setCellValue('F' . $row, '-');
             $row++;
         }
     }
     // Redirect output to a client’s web browser (Excel2007)
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="models.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objWriter->save('php://output');
     exit;
 }

 /**
  * Добавляет таблицу со списком
  *
  * @param PHPExcel $objPHPExcel
  * @param array $reportData
  */
 protected function addDataTable(PHPExcel $objPHPExcel, array $reportData)
 {
     $activeSheet = $objPHPExcel->getActiveSheet();
     $rowc = 11;
     foreach ($reportData['rows'] as $row) {
         $activeSheet->setCellValue('A' . $rowc, $row['platform_name'])->setCellValue('B' . $rowc, $row['clicks'])->setCellValue('C' . $rowc, $row['cost'])->setCellValue('D' . $rowc, $row['price'])->setCellValue('E' . $rowc, $row['is_vat'] ? Yii::app()->params->VAT / 100 : 0)->setCellValue('F' . $rowc, $row['price_with_vat'])->setCellValue('G' . $rowc, $row['debit'])->setCellValue('H' . $rowc, $row['debit_with_vat'])->setCellValue('I' . $rowc, $row['debit_vat'])->setCellValue('J' . $rowc, $row['is_active'] ? 'активна' : 'неактивна')->getStyle('J' . $rowc)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => $row['is_active'] ? 'eaf1dd' : 'f2dddc'))));
         if (empty($row['billing_details_type'])) {
             $activeSheet->setCellValue('K' . $rowc, 'нет данных');
             $activeSheet->getStyle('K' . $rowc)->getFont()->getColor()->setRGB('bfbfbf');
         } else {
             $activeSheet->setCellValue('K' . $rowc, $row['billing_details_type'] . ': ' . $row['billing_details_text']);
         }
         ++$rowc;
     }
     $this->addTableTotal($activeSheet, $reportData, $rowc);
     $this->formatTable($activeSheet, 'A', 10, 'K', $rowc);
     $activeSheet->getStyle('C11:I' . $rowc)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
     $activeSheet->getStyle('E11:E' . $rowc)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
     $activeSheet->getColumnDimension('A')->setWidth(17.43 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(13.29 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(14.71 * 1.05);
     $activeSheet->getColumnDimension('D')->setWidth(14.71 * 1.05);
     $activeSheet->getColumnDimension('E')->setWidth(5.71 * 1.05);
     $activeSheet->getColumnDimension('F')->setWidth(13.71 * 1.05);
     $activeSheet->getColumnDimension('G')->setWidth(16.43 * 1.05);
     $activeSheet->getColumnDimension('H')->setWidth(14.29 * 1.05);
     $activeSheet->getColumnDimension('I')->setWidth(10.0 * 1.05);
     $activeSheet->getColumnDimension('J')->setWidth(16.14 * 1.05);
     $activeSheet->getColumnDimension('K')->setWidth(35.71 * 1.05);
 }

 /**
  * Excelにデータを出力し、ダウンロードする
  */
 public function generate()
 {
     $excel = new PHPExcel();
     $excel->setActiveSheetIndex(0);
     $sheet = $excel->getActiveSheet();
     $sheet->setTitle('課題管理表');
     // ヘッダ設定
     foreach (self::$header as $key => $val) {
         $sheet->setCellValueByColumnAndRow($key, 1, $val);
     }
     // データ設定
     foreach ($this->data as $key => $issue) {
         $row = $key + 2;
         $sheet->setCellValueByColumnAndRow(0, $row, $issue['No']);
         $sheet->setCellValueByColumnAndRow(1, $row, $issue['Title']);
         $sheet->setCellValueByColumnAndRow(2, $row, $issue['Created']);
         $sheet->setCellValueByColumnAndRow(3, $row, $issue['Updated']);
         $sheet->setCellValueByColumnAndRow(4, $row, $issue['Assignee']);
         $sheet->setCellValueByColumnAndRow(5, $row, $issue['State']);
         $sheet->setCellValueByColumnAndRow(6, $row, $issue['Labels']);
         $sheet->setCellValueByColumnAndRow(7, $row, $issue['Milestone']);
     }
     $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
     $writer->save($this->path);
     // 出力
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment; filename=' . basename($this->path) . ';');
     header('Content-Transfer-Encoding: binary');
     header('Content-Length: ' . filesize($this->path));
     readfile($this->path);
     unlink($this->path);
     exit;
 }

 public static function excel($data, $ext = 'xlsx', $filepath = TRUE)
 {
     $excel = new \PHPExcel();
     $excel->setActiveSheetIndex(0);
     $sheet = $excel->getActiveSheet();
     array_walk($data, function (&$v) {
         foreach ($v as $key => $value) {
             !is_scalar($value) && ($v[$key] = @strval($value));
         }
     });
     $sheet->fromArray($data);
     $filepath == TRUE && ($filepath = tempnam(storage_path('utils'), 'excel'));
     switch (strtolower($ext)) {
         case 'xlsx':
             $objWriter = new \PHPExcel_Writer_Excel2007($excel);
             break;
         case 'xls':
             $objWriter = new \PHPExcel_Writer_Excel5($excel);
             break;
         case 'csv':
             $objWriter = new \PHPExcel_Writer_CSV($excel);
             break;
         case 'pdf':
             $objWriter = new \PHPExcel_Writer_PDF($excel);
             break;
         default:
             # code...
             break;
     }
     $objWriter->save($filepath);
     //@unlink($filepath);
     return $filepath;
 }