/** * @return \PHPExcel */ protected function mockWorkbook() { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $workbook->getProperties()->setTitle('mocked'); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]); $workbook->addSheet($sheet); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]); $workbook->addSheet($sheet); $workbook->setActiveSheetIndex(0); return $workbook; }
/** * @return \PHPExcel */ protected function mockRow() { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->fromArray([['a1', 'b1', 'c1']]); $row = new \PHPExcel_Worksheet_Row($sheet, 1); return $row; }
/** * @return \PHPExcel_Worksheet */ protected function mockSheet() { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $sheet = new \PHPExcel_Worksheet($workbook); $sheet->setTitle('mocked'); $sheet->fromArray([['a1', 'b1'], ['a2', 'b2']]); return $sheet; }
/** * @param string $text * * @return Cell */ protected function mockCell($text = 'text', $settings = false) { $workbook = new \PHPExcel(); $workbook->disconnectWorksheets(); $worksheet = new \PHPExcel_Worksheet($workbook); $workbook->addSheet($worksheet); $cell = $worksheet->setCellValue('A1', $pValue = $text, $returnCell = true); $settings = $settings ?: new ParserSettings(); $cell = new Cell($cell, 1, $settings); return $cell; }
/** * Save file to disk. * * By default, it will not overwrite an existing file with the same name. * * @param string $filename Filename with path * @param string $format A value from \PHPExcel_IOFactory::$_autoResolveClasses * @param bool $canOverWrite Set to true if you want to let an existing file be overwritten upon saving. * * @return bool * @throws FileExistsException * @throws \PHPExcel_Reader_Exception */ public function saveFile($filename, $format, $canOverWrite = false) { $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, $format); if (!$canOverWrite and file_exists($filename)) { throw new FileExistsException(); } $objWriter->save($filename); $this->objPHPExcel->disconnectWorksheets(); unset($this->objPHPExcel); return true; }
/** * Create excel file and store in tmp dir * * @param string $filename * @param string $format * @param bool $disconnect * @return string */ private function writeFile($filename, $format = 'Excel2007', $disconnect = true) { $path = sprintf('%s/%s', $this->path, $filename); $objWriter = \PHPExcel_IOFactory::createWriter($this->file, $format); $objWriter->save($path); if ($disconnect) { $this->file->disconnectWorksheets(); unset($this->file); } $this->fileName = $filename; $this->filePath = $path; return $path; }
public function __destruct() { $this->_xls->disconnectWorksheets(); unset($this->_xls); }
public function create_excel($excel_data = NUll) { //check if the excel data has been set if not exit the excel generation if (count($excel_data) > 0) { $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("HCMP"); $objPHPExcel->getProperties()->setLastModifiedBy($excel_data['doc_creator']); $objPHPExcel->getProperties()->setTitle($excel_data['doc_title']); $objPHPExcel->getProperties()->setSubject($excel_data['doc_title']); $objPHPExcel->getProperties()->setDescription(""); $objPHPExcel->setActiveSheetIndex(0); $rowExec = 1; //Looping through the cells $column = 0; foreach ($excel_data['column_data'] as $column_data) { $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $rowExec, $column_data); $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true); //$objPHPExcel->getActiveSheet()->getStyle($column, $rowExec)->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($column, $rowExec)->getFont()->setBold(true); $column++; } $rowExec = 2; foreach ($excel_data['row_data'] as $row_data) { $column = 0; foreach ($row_data as $cell) { //Looping through the cells per facility $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $rowExec, $cell); $column++; } $rowExec++; } $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Save Excel 2007 file //echo date('H:i:s') . " Write to Excel2007 format\n"; $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); // We'll be outputting an excel file header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: no-store, no-cache, must-revalidate"); header("Cache-Control: post-check=0, pre-check=0", false); header("Pragma: no-cache"); // It will be called file.xls header("Content-Disposition: attachment; filename=" . $excel_data['file_name'] . ".xlsx"); // Write file to the browser $objWriter->save('php://output'); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); // Echo done } }
private function generate_excel($records = 'all') { require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel.php'; require_once APPPATH . 'third_party/phpexcel/Classes/PHPExcel/Writer/Excel2007.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("MDB Order Management System"); $objPHPExcel->getProperties()->setLastModifiedBy("MDB Order Management System"); $objPHPExcel->getProperties()->setTitle("Order Exportation on 03-12-2014"); $objPHPExcel->getProperties()->setSubject("Order Exportation on 03-12-2014"); $objPHPExcel->getProperties()->setDescription("Order Exportation on 03-12-2014"); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->setActiveSheetIndex(0); // Column Header Setting $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Customer No.'); $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Code'); $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Name'); $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Address'); $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Address 2'); $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'City'); $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'State'); $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Zip'); $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Country/Region Code'); $objPHPExcel->getActiveSheet()->SetCellValue('J1', 'Contact Name'); $objPHPExcel->getActiveSheet()->SetCellValue('K1', 'Phone No.'); $objPHPExcel->getActiveSheet()->SetCellValue('L1', 'Ship To Location'); $objPHPExcel->getActiveSheet()->SetCellValue('M1', 'Store Location'); $objPHPExcel->getActiveSheet()->SetCellValue('N1', 'Email'); $row = 2; if ($records == 'all') { $records = $this->portal_model->get_all_records_for_export(); } foreach ($records as $record) { $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, htmlspecialchars_decode($record['CustNo'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $record['Code']); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, htmlspecialchars_decode($record['Name'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, htmlspecialchars_decode($record['Address'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('E' . $row, htmlspecialchars_decode($record['Address2'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('F' . $row, htmlspecialchars_decode($record['City'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('G' . $row, htmlspecialchars_decode($record['State'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('H' . $row, htmlspecialchars_decode($record['Zip'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('I' . $row, htmlspecialchars_decode($record['Country'], ENT_QUOTES)); //$objPHPExcel->getActiveSheet()->SetCellValue('J'.$row, $record['Contact']); $objPHPExcel->getActiveSheet()->SetCellValue('K' . $row, htmlspecialchars_decode($record['Phone'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('L' . $row, htmlspecialchars_decode($record['isShipTo'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('M' . $row, htmlspecialchars_decode($record['isStore'], ENT_QUOTES)); $objPHPExcel->getActiveSheet()->SetCellValue('N' . $row, htmlspecialchars_decode($record['Email'], ENT_QUOTES)); $row++; } $datestring = date('YmdHisu'); $objPHPExcel->getActiveSheet()->setTitle('Simple'); $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save('application/files/retailer-survey-' . $datestring . '.xlsx'); //$objWriter->save(APPPATH . 'third_party/orders-' . date('YmdHisu') . '.csv'); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); header('Pragma: public'); header('Pragma: no-cache'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Content-Description: File Transfer'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8;'); header('Content-Length: ' . filesize(APPPATH . 'third_party/orders-' . $datestring . '.xlsx')); header("Content-Disposition: attachment;filename=transaction-export-" . date('Y-m-d') . ".xlsx"); header('Content-Transfer-Encoding: binary'); ob_clean(); flush(); if (@readfile('application/files/retailer-survey-' . $datestring . '.xlsx')) { return true; } else { return false; } }
public function patient_consumption($period_start = "", $period_end = "") { $patients = array(); $oi_drugs = array(); //get all regimen drugs from OI $sql = "SELECT IF(d.drug IS NULL,rd.drugcode,d.drug) as drugname,'' as drugqty\n FROM regimen_drug rd \n LEFT JOIN regimen r ON r.id=rd.regimen\n LEFT JOIN regimen_service_type rst ON rst.id=r.type_of_service\n LEFT JOIN drugcode d ON d.id=rd.drugcode\n WHERE rst.name LIKE '%oi%'\n AND d.drug NOT LIKE '%cot%'\n GROUP BY drugname"; $query = $this->db->query($sql); $drugs = $query->result_array(); if ($drugs) { foreach ($drugs as $drug) { $oi_drugs[$drug['drugname']] = $drug['drugqty']; } } //get all patients dispensed,drug and in this period $sql = "SELECT pv.patient_id,CONCAT_WS( '/', MONTH( pv.dispensing_date ) , YEAR( pv.dispensing_date ) ) AS Month_Year, group_concat(d.drug) AS ARVDrug, group_concat(pv.quantity) AS ARVQTY\n\t\t\t FROM v_patient_visits pv \n\t\t\t LEFT JOIN drugcode d ON d.id = pv.drug_id\n\t\t\t WHERE pv.dispensing_date\n\t\t\t BETWEEN '" . $period_start . "'\n\t\t\t AND '" . $period_end . "'\n\t\t\t GROUP BY pv.patient_id, CONCAT_WS( '/', MONTH( pv.dispensing_date ) , YEAR( pv.dispensing_date ) )\n\t\t\t ORDER BY pv.patient_id"; $query = $this->db->query($sql); $transactions = $query->result_array(); if ($transactions) { foreach ($transactions as $transaction) { $oi = $oi_drugs; $is_oi = FALSE; //split comma seperated drugs to array $drugs = $transaction['ARVDrug']; $drugs = explode(",", $drugs); //split comma seperated qtys to array $qtys = $transaction['ARVQTY']; $qtys = explode(",", $qtys); foreach ($drugs as $index => $drug) { //add drug qtys to oi if (array_key_exists($drug, $oi)) { $is_oi = TRUE; $oi[$drug] = $qtys[$index]; } } //add drug consumption to patient if ($is_oi == TRUE) { $patients[$transaction['patient_id']] = $oi; } } } //export patient transactions $this->load->library('PHPExcel'); $dir = "Export"; $objPHPExcel = new PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); /*Delete all files in export folder*/ if (is_dir($dir)) { $files = scandir($dir); foreach ($files as $object) { if ($object != "." && $object != "..") { unlink($dir . "/" . $object); } } } else { mkdir($dir); } //get columns $column = array(); $letter = 'A'; while ($letter !== 'AAA') { $column[] = $letter++; } //set col and row indices $col = 0; $row = 1; //wrap header text $objPHPExcel->getActiveSheet()->getStyle('A1:A' . $objPHPExcel->getActiveSheet()->getHighestRow())->getAlignment()->setWrapText(true); //autosize header $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(-1); //print $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, "ARTID"); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, "PERIOD"); foreach ($oi_drugs as $drugname => $header) { $col++; $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $drugname); } //loop through patient transactions foreach ($patients as $art_id => $dispenses) { //reset col and row indices $col = 0; $row++; //write art_id and period reporting $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $art_id); $col++; $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, date("m/Y", strtotime($period_start))); foreach ($dispenses as $drug_id => $drug_qty) { $col++; $objPHPExcel->getActiveSheet()->SetCellValue($column[$col] . $row, $drug_qty); } } //Generate file ob_start(); $period_start = date("F-Y", strtotime($period_start)); $original_filename = "PATIENT DRUG CONSUMPTION[" . $period_start . "].xls"; $filename = $dir . "/" . urldecode($original_filename); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save($filename); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); if (file_exists($filename)) { $filename = str_replace("#", "%23", $filename); redirect($filename); } }
/** * Free memory * * @return void */ public function freeMemory() { $this->_xls->disconnectWorksheets(); unset($this->_xls); }
$params = $_REQUEST; $params['sort_by'] = $price_schema['fields'][Registry::get('addons.price_list.price_list_sorting')]['sort_by']; $params['page'] = $page; $params['skip_view'] = 'Y'; fn_price_list_print_products($params, $worksheet, $counter, $row, $width, $selected_fields, $price_schema, $styles); } foreach ($width as $col => $size) { if ($size > MAX_SIZE) { $size = MAX_SIZE; } $worksheet->getColumnDimension($col)->setWidth($size); } $writer = new PHPExcel_Writer_Excel2007($pexcel); $imp_filename = fn_create_temp_file(); $writer->save($imp_filename); $pexcel->disconnectWorksheets(); unset($pexcel); Storage::instance('assets')->put($filename, array('file' => $imp_filename, 'caching' => true)); fn_echo('<br />' . __('done')); } /** * Checks if server configuration supports xml creation * * @return bool False if some components are not installed, otherwise True */ function fn_price_list_is_xls_supported() { $result = true; // check for ZipArchive class exists // phpexcel does not work without zip support if (!class_exists('ZipArchive')) {
/** * Create sheet from query and dump named file to browser. * @param $query the query. */ function writeXlsx($query) { PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder()); $objPHPExcel = new PHPExcel(); if (!isset($this->rowParser)) { $this->rowParser = new DefaultRowParser(); } $objPHPExcel->getProperties()->setCreator($this->creator); $objPHPExcel->getProperties()->setLastModifiedBy($this->author); $objPHPExcel->getProperties()->setTitle($this->title); $objPHPExcel->getProperties()->setSubject($this->subject); $objPHPExcel->getProperties()->setDescription($this->description); $objPHPExcel->getProperties()->setKeywords($this->keywords); $objPHPExcel->getProperties()->setCategory($this->catagory); global $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $resultSet = $this->dbConn->Execute($query); if ($resultSet === false) { die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>"); } //echo $query; //$colcount = $resultSet->FieldCount(); // start writing in 3rd row, top isf for title and link. $row = 3; $this->tableHeader = $this->rowParser->parseToTableHeader($resultSet); $headCount = count($this->tableHeader); $headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0'))); for ($i = 0; $i < $headCount; $i++) { $name = $this->tableHeader[$i]; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name); $coor = XLSWriter::cellCoordinate($i, $row); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $row++; // get types $this->columnTypes = $this->rowParser->parseTypes($resultSet); $XlsTypes = array(); //error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0); for ($i = 0; $i < count($this->columnTypes); $i++) { $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; //error_log("found type = {$this->columnTypes[$i]} for column {$i}", 0); switch ($this->columnTypes[$i]) { case 'char': case 'bpchar': case 'varchar': case 'text': case 'date': $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; case 'int2': case 'int4': case 'int8': case '_numeric': case 'numeric': case 'float8': $ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC; break; default: $ftype = PHPExcel_Cell_DataType::TYPE_STRING; break; } $XlsTypes[] = $ftype; } $cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000'))); $oldValue = ''; if ($this->firstWeightColumn > 0) { // add weights row $this->weigthsRow = $row; $coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $weightSum = 0; $w = 0; ${$weightLast} = count($this->weights) - 1; for (; $w < count($this->weights); $w++) { $coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row); $weightSum += $this->weights[$w]; $objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); } $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row); $formula = "=SUM({$wBegin}:{$wEnd})"; $objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1); $objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles); $row++; } while (!$resultSet->EOF) { $rowData = $this->rowParser->parse($resultSet); $headCount = count($this->tableHeader); //$resultSet->FieldCount(); $changeColor = false; if ($this->colorChangerColumn >= 0) { if ($oldValue != $rowData[$this->colorChangerColumn]) { $changeColor = true; $oldValue = $rowData[$this->colorChangerColumn]; } } else { if ($this->autoZebra) { $changeColor = true; } } if ($changeColor) { $cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString(); $this->rainBow->getNext(); } $i = 0; for (; $i < $headCount; $i++) { $value = $rowData[$i]; $coor = XLSWriter::cellCoordinate($i, $row); $xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING; //error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0); $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype); if ($this->columnTypes[$i] == 'date') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); } else { if ($this->columnTypes[$i] == 'time') { $objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8); } } $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } if ($this->weightedSumsColumn >= 0) { $weightLast = count($this->weights) - 1; $coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row); $wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow); $wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow); $rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row); $rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row); $wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow); $formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}"; $objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); $objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray); } $row++; $resultSet->moveNext(); } $row = 1; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText); $objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl); $row++; $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title); $objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles); $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles); $rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row); $objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1); $rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1); $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2); // set format $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1); $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0); for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) { $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true); // $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray); } PHPExcel_Calculation::getInstance()->clearCalculationCache(); PHPExcel_Calculation::getInstance()->disableCalculationCache(); PHPExcel_Calculation::getInstance()->calculate(); switch ($this->excelFormat) { case 'Excel2007': $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; break; case 'Excel5': $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $this->mimeType = 'application/vnd.ms-excel'; break; default: $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $this->mimeType = 'text/comma-separated-values'; break; } $tempFile = tempnam('/tmp/', 'PHPEXCEL'); // '/tmp/'.$filename; $objWriter->setPreCalculateFormulas(true); $objWriter->save($tempFile); $fp = @fopen($tempFile, 'r'); if ($fp != false) { header("Content-type: " . $this->mimeType); header("Pragma: public"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . filesize($tempFile)); header("Content-Disposition: attachment; filename=\"{$this->filename}\""); fpassthru($fp); fclose($fp); $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); unlink($tempFile); exit(0); } else { echo "cannot copy file {$tempFile} to out stream\n"; } }
public function array2excel($data, $output = true) { //数组转换为excel文件 $PHPExcel = new PHPExcel(); $WorkSheet = $PHPExcel->getActiveSheet(); set_time_limit(0); @ini_set('memory_limit', '256M'); $row_index = 1; foreach ($data as $row) { //循环赋值 $col_index = 0; foreach ($row as $col) { $WorkSheet->setCellValueByColumnAndRow($col_index, $row_index, $col); $col_index++; } $row_index++; } $Writer = new PHPExcel_Writer_Excel5($PHPExcel); $filename = time(); if ($output) { header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="' . $filename . '.xls"'); header("Content-Transfer-Encoding: binary"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $Writer->save('php://output'); //直接输出 } else { $Writer->save($filename . '.xls'); //生成为文件 } $PHPExcel->disconnectWorksheets(); unset($PHPExcel); }
/** * Save generated archive file or trigger a direct download if class var * $direct_download_archive is set to true * @param PHPExcel $obj_excel * @param String $period * @param String $table_name * @param Int $file_counter * @param Boolean $clear_excel_object_from_memory */ private static function saveArchiveFile($obj_excel, $period, $table_name, $use_timestamp_as_prefix = true, $file_counter = null, $clear_excel_object_from_memory = true) { $save_path = self::generateArchiveFilepath($period, $table_name, $use_timestamp_as_prefix, $file_counter); $obj_excel_writer = new PHPExcel_Writer_Excel5($obj_excel); if (self::$direct_download_archive) { $filename = basename($save_path); header("Content-Type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename={$filename}"); $obj_excel_writer->save('php://output'); } else { $obj_excel_writer->save($save_path); self::$archive_files[$period][$table_name] = str_replace(DIR_FS_ARCHIVES . self::ARCHIVE_PATH, '', $save_path); } if ($clear_excel_object_from_memory) { $obj_excel->disconnectWorksheets(); unset($obj_excel_writer, $obj_excel); } else { unset($obj_excel_writer); } }
/** * Reset the writer * * @return void */ public function _reset() { $this->excel->disconnectWorksheets(); $this->resetValueBinder(); unset($this->parsed); }
/** * Reset the writer * @return void */ protected function _reset() { $this->excel->disconnectWorksheets(); }
/** * Creating a Excel * * @Route /new/example/excel * */ public function exportingExcelAction() { /** * An example of excel exporting */ $this->excel = $this->get('PHPExcel'); $this->excel->createSheet(0); $this->excel->getActiveSheet()->setCellValue('A2', "No")->setCellValue('B2', "Name")->setCellValue('C2', "Last Name"); $objWriter = \PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); $objWriter->save('php://output'); $this->excel->disconnectWorksheets(); $this->app->response()->headers()->set('Content-Disposition', 'attachment; filename="example"'); $this->app->response()->headers()->set('Cache-Control', 'max-age=0'); $this->app->contentType(\Raptor\Raptor::EXCEL); }