PHP PHPExcel_Cell::stringFromColumnIndex方法代码示例

 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 exportXlsx($data, $keys)
 {
     // Create new PHPExcel object
     $objPHPExcel = new \PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
     $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = ['memoryCacheSize' => '8MB'];
     \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach ($keys as $key => $value) {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
     }
     foreach ($data as $key => $answer) {
         foreach ($answer as $k => $value) {
             $columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
             if ($value instanceof \DateTime) {
                 $value = \PHPExcel_Shared_Date::PHPToExcel($value);
                 $objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
             }
             $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
         }
     }
     // Set active sheet index to the first sheet, so Excel opens this as the first sheet
     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     ob_start();
     $objWriter->save('php://output');
     $return = ob_get_clean();
     return $return;
 }

 public function ProcessFileContent()
 {
     $objPHPExcel = PHPExcel_IOFactory::load($this->file);
     // Format is as follows:
     // (gray bg)    [ <description of data> ], <relation1>,    <relationN>
     //              <srcConcept>,              <tgtConcept1>,  <tgtConceptN>
     //              <srcAtomA>,                <tgtAtom1A>,    <tgtAtomNA>
     //              <srcAtomB>,                <tgtAtom1B>,    <tgtAtomNB>
     //              <srcAtomC>,                <tgtAtom1C>,    <tgtAtomNC>
     // Output is function call:
     // InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom)
     // Loop over all worksheets
     foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
         // Loop through all rows
         $highestrow = $worksheet->getHighestRow();
         $highestcolumn = $worksheet->getHighestColumn();
         $highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
         $row = 1;
         // Go to the first row where a table starts.
         for ($i = $row; $i <= $highestrow; $i++) {
             $row = $i;
             if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') {
                 break;
             }
         }
         // We are now at the beginning of a table or at the end of the file.
         $line = array();
         // Line is a buffer of one or more related (subsequent) excel rows
         while ($row <= $highestrow) {
             // Read this line as an array of values
             $values = array();
             // values is a buffer containing the cells in a single excel row
             for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
                 $columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
                 $cell = $worksheet->getCell($columnletter . $row);
                 $cellvalue = (string) $cell->getCalculatedValue();
                 // overwrite $cellvalue in case of datetime
                 // the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
                 if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
                     $cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
                 }
                 $values[] = $cellvalue;
             }
             $line[] = $values;
             // add line (array of values) to the line buffer
             $row++;
             // Is this relation table done? Then we parse the current values into function calls and reset it
             $firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
             if (substr($firstCellInRow, 0, 1) === '[') {
                 // Relation table is complete, so it can be processed.
                 $this->ParseLines($line);
                 $line = array();
             }
         }
         // Last relation table remains to be processed.
         $this->ParseLines($line);
         $line = array();
     }
 }

//.........这里部分代码省略.........
									$docSheet->setShowSummaryRight(false);
								} else {
									$docSheet->setShowSummaryRight(true);
								}

								if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
									$docSheet->setShowSummaryBelow(false);
								} else {
									$docSheet->setShowSummaryBelow(true);
								}
							}

							if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
								if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
									$docSheet->getPageSetup()->setFitToPage(false);
								} else {
									$docSheet->getPageSetup()->setFitToPage(true);
								}
							}

							if (isset($xmlSheet->sheetFormatPr)) {
								if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
									$docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
								}
								if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
									$docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
								}
							}

							if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
								foreach ($xmlSheet->cols->col as $col) {
									for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
										if ($col["style"] && !$this->_readDataOnly) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
										}
										if ($col["bestFit"]) {
											//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
										}
										if ($col["hidden"]) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
										}
										if ($col["collapsed"]) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
										}
										if ($col["outlineLevel"] > 0) {
											$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
										}
										$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));

										if (intval($col["max"]) == 16384) {
											break;
										}
									}
								}
							}

							if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
								if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
									$docSheet->setShowGridlines(true);
								}

								if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
									$docSheet->setPrintGridlines(true);
								}

								if ($xmlSheet->printOptions['horizontalCentered']) {

 /**
  * @inheritDoc
  */
 public function writeHeaderRow($headerRow)
 {
     $this->writeRow($headerRow);
     $lastColumnLetter = \PHPExcel_Cell::stringFromColumnIndex(count($headerRow) - 1);
     $headerRange = "A{$this->currentRowIndex}:{$lastColumnLetter}{$this->currentRowIndex}";
     $this->phpExcel->getActiveSheet()->getStyle($headerRange)->getFont()->setBold(true);
 }

 public function select($source)
 {
     $path = $this->connection;
     $excel = PHPExcel_IOFactory::createReaderForFile($path);
     $excel = $excel->load($path);
     $excRes = new ExcelResult();
     $excelWS = $excel->getActiveSheet();
     $addFields = true;
     $coords = array();
     if ($source->get_source() == '*') {
         $coords['start_row'] = 0;
         $coords['end_row'] = false;
     } else {
         $c = array();
         preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c);
         if (count($c) > 0) {
             $coords['start_row'] = (int) $c[2];
         } else {
             $coords['start_row'] = 0;
         }
         $c = array();
         preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c);
         if (count($c) > 0) {
             $coords['end_row'] = (int) $c[2];
         } else {
             $coords['end_row'] = false;
         }
     }
     $i = $coords['start_row'];
     $end = 0;
     while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) {
         $r = array();
         $emptyNum = 0;
         for ($j = 0; $j < count($this->config->text); $j++) {
             $col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1;
             $cell = $excelWS->getCellByColumnAndRow($col, $i);
             if ($cell->getDataType() == 'f') {
                 $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue();
             } else {
                 $r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue();
             }
             if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') {
                 $emptyNum++;
             }
         }
         if ($emptyNum < count($this->config->text)) {
             $r['id'] = $i;
             $excRes->addRecord($r);
             $end = 0;
         } else {
             if (DHX_IGNORE_EMPTY_ROWS == false) {
                 $r['id'] = $i;
                 $excRes->addRecord($r);
             }
             $end++;
         }
         $i++;
     }
     return $excRes;
 }

 /**
  * exportCollectionSheet
  *
  * default sheet export for collection
  *
  * @param string $object 
  * @param string $fields 
  * @return void
  * @author Brent Shaffer
  */
 public function exportCollectionSheet($collection, $fields, $title = null)
 {
     if ($this->_sheets > 0) {
         $workSheet = $this->_xls->createSheet();
     } else {
         $workSheet = $this->_xls->getActiveSheet();
     }
     $this->_sheets++;
     $workSheet->setTitle($title ? $title : $this->getExportTitle());
     // Initialize coordinate counters
     $row = 1;
     $col = 0;
     foreach ($fields as $field => $label) {
         $workSheet->setCellValueByColumnAndRow($col, $row, $label);
         $workSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
         $col++;
     }
     $row++;
     foreach ($collection as $record) {
         $col = 0;
         foreach ($fields as $field => $label) {
             $workSheet->setCellValueByColumnAndRow($col, $row, $this->exportField($record, $field));
             $col++;
         }
         $row++;
     }
 }

/**
 * 行を完全コピーする
 *
 * http://blog.kotemaru.org/old/2012/04/06.html より
 * @param PHPExcel_Worksheet $sheet
 * @param int $srcRow
 * @param int $dstRow
 * @param int $height
 * @param int $width
 * @throws PHPExcel_Exception
 */
function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
    for ($row = 0; $row < $height; $row++) {
        // セルの書式と値の複製
        for ($col = 0; $col < $width; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
            $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
            $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
            $sheet->setCellValue($dstCell, $cell->getValue());
            $sheet->duplicateStyle($style, $dstCell);
        }
        // 行の高さ複製。
        $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
        $sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
    }
    // セル結合の複製
    // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
    // - $merge="AB16:AC19"
    foreach ($sheet->getMergeCells() as $mergeCell) {
        $mc = explode(":", $mergeCell);
        $col_s = preg_replace("/[0-9]*/", "", $mc[0]);
        $col_e = preg_replace("/[0-9]*/", "", $mc[1]);
        $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow;
        $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow;
        // 複製先の行範囲なら。
        if (0 <= $row_s && $row_s < $height) {
            $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e);
            $sheet->mergeCells($merge);
        }
    }
}

 private static function init($data, $headerTitle = [], $headerRGBColor = 'FCFCFC', $borderColor = 'CCC')
 {
     $phpExcel = new \PHPExcel();
     $phpExcel->getProperties()->setCreator('Affiliates Team')->setTitle('Excel Data');
     $phpExcelSheet = $phpExcel->createSheet(0);
     $phpExcelSheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
     $phpExcelSheet->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3);
     if (!empty($headerTitle)) {
         $tmpData = [];
         $columnKeys = array_keys($headerTitle);
         foreach ($data as $dataTmpRow) {
             $tmpRow = [];
             foreach ($columnKeys as $columnKey) {
                 $tmpRow[$columnKey] = isset($dataTmpRow[$columnKey]) ? $dataTmpRow[$columnKey] : '';
             }
             $tmpData[] = $tmpRow;
         }
         $data = $tmpData;
     }
     $rowLength = count($data);
     if ($rowLength) {
         $keys = array_keys($data[0]);
         if (!empty($headerTitle)) {
             foreach ($keys as &$keysRow) {
                 $keysRow = isset($headerTitle[$keysRow]) ? $headerTitle[$keysRow] : $keysRow;
             }
         }
         $columnLength = count($keys);
         $fromCode = \PHPExcel_Cell::stringFromColumnIndex(0);
         $toCode = \PHPExcel_Cell::stringFromColumnIndex($columnLength - 1);
         for ($i = 0; $i < $columnLength; $i++) {
             $phpExcelSheet->setCellValueByColumnAndRow($i, 1, $keys[$i]);
             $phpExcelSheet->getColumnDimension(chr(65 + $i))->setAutoSize(true);
         }
         // [merging], develop later
         // $phpExcelSheet->mergeCells('A1:A2');
         // $phpExcelSheet->getStyle('A1:A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
         $phpExcelSheet->fromArray($data, ' ', 'A3');
         for ($i = 0; $i < $rowLength; ++$i) {
             $rowIndex = $i + 1;
             if ($rowIndex % 3 == 0) {
                 if ($rowIndex / 3 % 2) {
                     self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F9F9F9');
                 } else {
                     self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F5FMD');
                 }
             } else {
                 self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'FFFFFF');
             }
         }
         self::colorRow($phpExcelSheet, $fromCode, $toCode, 1, $headerRGBColor);
         $headerStyle = ['font' => ['bold' => true], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
         $phpExcelSheet->getStyle($fromCode . '1:' . $toCode . '1')->applyFromArray($headerStyle);
         self::setBorderStyle($phpExcelSheet, $fromCode, $toCode, $rowLength + 2, $borderColor);
     }
     return $phpExcel;
 }

/**
 * The MysqlExportXls function is used to export mysql query result into an .xls file.
 * @param MysqlExportXlsConnectOptions $connectOptions
 * @param MysqlExportXlsFileOptions $fileOptions
 * @return error message. Return empty string on success.
 */
function MysqlExportXls($connectOptions, $fileOptions, $query)
{
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getProperties()->setCreator($fileOptions->creator);
    $objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
    $objPHPExcel->getProperties()->setTitle($fileOptions->title);
    $objPHPExcel->getProperties()->setSubject($fileOptions->subject);
    $objPHPExcel->getProperties()->setDescription($fileOptions->description);
    $objPHPExcel->setActiveSheetIndex(0);
    $activeSheet = $objPHPExcel->getActiveSheet();
    $activeSheet->setTitle($fileOptions->title);
    // connect to mysql
    $link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password);
    if (!$link) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link);
    }
    // use database
    $selectDb = mysql_select_db($connectOptions->useDatabase, $link);
    if (!$selectDb) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link);
    }
    // PHPExcel use utf-8 encoding to save file only !!!
    $setCharset = mysql_set_charset("utf8", $link);
    if (!$setCharset) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link);
    }
    // execute sql
    $result = mysql_query($query, $link);
    if (!$result) {
        return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link);
    }
    // field names
    $columnIndex = 0;
    while ($field = mysql_fetch_field($result)) {
        $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name);
        ++$columnIndex;
    }
    $rowIndex = 2;
    // 1 based, the firset row is for field names.
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $columnIndex = 0;
        foreach ($line as $key => $col_value) {
            $activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2);
            ++$columnIndex;
        }
        ++$rowIndex;
    }
    // free mysql resource
    mysql_free_result($result);
    mysql_close($link);
    // write data into file
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->setPreCalculateFormulas(FALSE);
    // Why true by default ? oh god damn it!
    $objWriter->save($fileOptions->name);
    return "";
}

 protected function cellRange($col1, $row1, $col2, $row2)
 {
     if (is_integer($col1)) {
         $col1 = \PHPExcel_Cell::stringFromColumnIndex($col1);
     }
     if (is_integer($col2)) {
         $col2 = \PHPExcel_Cell::stringFromColumnIndex($col2);
     }
     return sprintf('%s%d:%s%d', $col1, $row1, $col2, $row2);
 }

 public function cellsToMergeByColsRow($c_start = NULL, $c_end = NULL, $r_start = NULL, $r_end = NULL)
 {
     $merge = 'A1:A1';
     if ($c_start >= 0 && $c_end >= 0 && $r_start && $r_end) {
         $col_start = PHPExcel_Cell::stringFromColumnIndex($c_start);
         $col_end = PHPExcel_Cell::stringFromColumnIndex($c_end);
         $merge = $col_start . $r_start . ":" . $col_end . $r_end;
     }
     return $merge;
 }

 public function summary($id)
 {
     $store_audit = StoreAudit::findOrFail($id);
     $summaries = StoreAuditSummary::where('store_audit_id', $id)->get();
     $categories = StoreAuditSummary::getUniqueCategoryForPerpectStore($id);
     $groups = StoreAuditSummary::getUniqueGroupForPerpectStore($id);
     \Excel::create($store_audit->store_name . ' - ' . $store_audit->template_name, function ($excel) use($summaries, $categories, $groups) {
         $excel->sheet('Sheet1', function ($sheet) use($summaries, $categories, $groups) {
             $sheet->row(1, array('ENROLLMENT TYPE', 'AREA', 'STORE CODE', 'STORE NAME', 'KPI'));
             $col = 5;
             $x = array();
             foreach ($categories as $category) {
                 $sheet->setCellValueByColumnAndRow($col, 1, $category->category);
                 $x[$category->category] = $col;
                 $col++;
             }
             $sheet->setCellValueByColumnAndRow($col, 1, 'PS SCORE');
             $row = 2;
             $y = array();
             foreach ($groups as $group) {
                 $sheet->setCellValueByColumnAndRow(4, $row, $group->group);
                 $y[$group->group] = $row;
                 for ($i = 5; $i < 8; $i++) {
                     $sheet->setCellValueByColumnAndRow($i, $row, 'N/A');
                 }
                 // add ps score
                 $ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',1),1,"N/A"))';
                 $sheet->setCellValueByColumnAndRow($col, $row, $ps_code);
                 $row++;
             }
             $sheet->setCellValueByColumnAndRow(4, $row, 'Perfect Store Summary');
             $col = 5;
             $first_row = 2;
             $last_row = $row - 1;
             foreach ($categories as $category) {
                 $cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
                 $sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
                 $col++;
             }
             $cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
             $sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
             // echo '<pre>';
             // print_r($x);
             // print_r($y);
             //  echo '</pre>';
             // dd(1);
             foreach ($summaries as $summary) {
                 $x_point = $x[$summary->category];
                 $y_point = $y[$summary->group];
                 $sheet->setCellValueByColumnAndRow($x_point, $y_point, $summary->passed);
             }
         })->download('xls');
     });
 }

 protected function doExport($objEntity = null, array $arrFields = array())
 {
     switch ($this->type) {
         case Exporter::TYPE_ITEM:
             break;
         case Exporter::TYPE_LIST:
             $objDbResult = $this->getEntities();
             $arrDca = $GLOBALS['TL_DCA'][$this->linkedTable];
             if (!$objDbResult->numRows > 0) {
                 return;
             }
             $intCol = 0;
             $intRow = 1;
             // header
             if ($this->objConfig->addHeaderToExportTable) {
                 foreach ($this->arrHeaderFields as $varValue) {
                     $this->objPhpExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($intCol, $intRow, $varValue);
                     $this->processHeaderRow($intCol);
                     $intCol++;
                 }
                 $intRow++;
             }
             // body
             while ($objDbResult->next()) {
                 $arrRow = $objDbResult->row();
                 $intCol = 0;
                 foreach ($arrRow as $key => $varValue) {
                     $objDc = new \DC_Table($this->linkedTable);
                     $objDc->activeRecord = $objDbResult;
                     $objDc->id = $objDbResult->id;
                     $varValue = $this->localizeFields ? FormSubmission::prepareSpecialValueForPrint($varValue, $arrDca['fields'][$key], $this->linkedTable, $objDc) : $varValue;
                     if (is_array($varValue)) {
                         $varValue = Arrays::flattenArray($varValue);
                     }
                     $this->objPhpExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($intCol, $intRow, html_entity_decode($varValue));
                     $this->objPhpExcel->getActiveSheet()->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($intCol))->setAutoSize(true);
                     $this->processBodyRow($intCol);
                     $intCol++;
                 }
                 $this->objPhpExcel->getActiveSheet()->getRowDimension($intRow)->setRowHeight(-1);
                 $intRow++;
             }
             $this->objPhpExcel->setActiveSheetIndex(0);
             $this->objPhpExcel->getActiveSheet()->setTitle('Export');
             return $this->objPhpExcel;
             break;
     }
     return false;
 }

 /** Export $data into an Excel file
  *
  *  @param $data mixed data retrieved via Model->find operation
  *  @param $options mixed array of options
  *
  */
 function export($modelName, $data, $options = array())
 {
     $needHeader = true;
     $startRow = 2;
     $startCol = 'A';
     $options = Set::merge(array('template' => array('file' => null, 'type' => 'Excel5', 'startRow' => $startRow, 'startCol' => $startCol), 'output' => array('file' => 'export.xls', 'type' => 'Excel5'), 'columnHeaders' => array(), 'fields' => array(), 'format' => array('date' => 'd/m/Y', 'float' => '%.2f')), $options);
     if (empty($data)) {
         trigger_error('No data to export');
         return;
     }
     if (!empty($options['template']['file'])) {
         $template = $options['template'];
         $startRow = $template['startRow'];
         $startCol = $template['startCol'];
         $reader = PHPExcel_IOFactory::createReader($template['type']);
         $xls = $reader->load($template['file']);
         $needHeader = false;
     } else {
         $xls = new PHPExcel();
         $xls->setActiveSheetIndex(0);
     }
     $sheet = $xls->getActiveSheet();
     if ($needHeader) {
         $this->_writeHeaders($xls, $options);
     }
     $Model = ClassRegistry::init($modelName);
     for ($i = 0, $ii = count($data); $i < $ii; $i++) {
         $col = 0;
         $row = $i + $startRow;
         for ($c = 0, $cc = count($options['fields']); $c < $cc; $c++) {
             $currentField = $options['fields'][$c];
             $split = explode('.', $currentField, 2);
             $fieldModel = $split[0];
             $fieldName = $split[1];
             $cell = PHPExcel_Cell::stringFromColumnIndex($col) . $row;
             if (!isset($data[$i][$fieldModel][$fieldName])) {
                 $fieldType = 'string';
                 $fieldValue = '';
             } else {
                 $fieldType = $this->_getColumnType($Model, $fieldModel, $fieldName);
                 $fieldValue = $data[$i][$fieldModel][$fieldName];
             }
             $this->_setCellValue($sheet, $cell, $fieldType, $fieldValue, $options);
             $col++;
         }
     }
     $writer = PHPExcel_IOFactory::createWriter($xls, $options['output']['type']);
     $writer->save($options['output']['file']);
 }