/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } // Get cell collection $cellCollection = $sheet->getCellCollection(); // Get column count $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); // Write headers $this->_writeHTMLHeader($fileHandle); $this->_writeStyles($fileHandle, $sheet); $this->_writeTableHeader($fileHandle); // Loop trough cells $currentRow = -1; $rowData = array(); foreach ($cellCollection as $cell) { if ($currentRow != $cell->getRow()) { // End previous row? if ($currentRow != -1) { $this->_writeRow($fileHandle, $sheet, $rowData, $currentRow - 1); } // Set current row $currentRow = $cell->getRow(); // Start a new row $rowData = array(); for ($i = 0; $i < $colCount; $i++) { $rowData[$i] = ''; } } // Copy cell $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; $rowData[$column] = $cell; } // End last row? if ($currentRow != -1) { $this->_writeRow($fileHandle, $sheet, $rowData, $currentRow - 1); } // Write footers $this->_writeTableFooter($fileHandle); $this->_writeHTMLFooter($fileHandle); // Close file fclose($fileHandle); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } // Get cell collection $cellCollection = $sheet->getCellCollection(); // Get column count $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); // Loop trough cells $currentRow = -1; $rowData = array(); foreach ($cellCollection as $cell) { if ($currentRow != $cell->getRow()) { // End previous row? if ($currentRow != -1) { $this->_writeLine($fileHandle, $rowData); } // Set current row $currentRow = $cell->getRow(); // Start a new row $rowData = array(); for ($i = 0; $i < $colCount; $i++) { $rowData[$i] = ''; } } // Copy cell $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; if ($cell->getValue() instanceof PHPExcel_RichText) { $rowData[$column] = $cell->getValue()->getPlainText(); } else { if ($this->_preCalculateFormulas) { $rowData[$column] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } else { $rowData[$column] = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } } } // End last row? if ($currentRow != -1) { $this->_writeLine($fileHandle, $rowData); } // Close file fclose($fileHandle); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { if (!is_null($this->_spreadSheet)) { // Create new ZIP file and open it for writing $objZip = new ZipArchive(); // Try opening the ZIP file if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) { throw new Exception("Could not open " . $pFilename . " for writing."); } // Add media for ($i = 0; $i < $this->_spreadSheet->getSheetCount(); $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."); } }
private function setData(\PHPExcel $ea, $data) { //Set up the header $ews = $ea->getSheet(0); $ews->setTitle('Data'); $ews->setCellValue('a1', 'ID'); $ews->setCellValue('b1', 'Season'); $ews->setCellValue('c1', 'Teams'); $ews->setCellValue('d1', 'Self Score'); $ews->setCellValue('e1', 'Opponent Score'); $ews->setCellValue('f1', 'Date Played'); $ews->setCellValue('g1', 'Win/Lose'); $ews->setCellValue('h1', 'Remark'); //Fill data $ews->fromArray($data, ' ', 'A2'); //Apply header some styles $header = 'a1:h1'; $ews->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00ffff00'); $style = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $ews->getStyle($header)->applyFromArray($style); for ($col = ord('a'); $col <= ord('h'); $col++) { $ews->getColumnDimension(chr($col))->setAutoSize(true); } return $ews; }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } if ($this->_useBOM) { // Write the UTF-8 BOM code fwrite($fileHandle, ""); } // Convert sheet to array $cellsArray = $sheet->toArray('', $this->_preCalculateFormulas); // Write rows to file foreach ($cellsArray as $row) { $this->_writeLine($fileHandle, $row); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
public function tablaDescarga() { if (isset($_POST['miHtml'])) { $htmlEntrada = $_POST['miHtml']; $titulo = $_POST['titulo']; $str = $htmlEntrada; $table = str_get_html($str); $rowData = array(); foreach ($table->find('tr') as $row) { // initialize array to store the cell data from each row $flight = array(); foreach ($row->find('td') as $cell) { // push the cell's text to the array $flight[] = trim($cell->plaintext); } $rowData[] = $flight; } error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); // date_default_timezone_set('Europe/London'); if (PHP_SAPI == 'cli') { die('This example should only be run from a Web Browser'); } $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator(label('nombreSistema'))->setLastModifiedBy(label('nombreSistema')); // ->setSubject("Prueba de descarga de tabla de personas") // ->setDescription("Documento de prueba de descarga de tabla de excel desde PHP") // ->setKeywords("office 2007 openxml php") // ->setCategory("Documento de prueba"); $hoja = $objPHPExcel->getSheet(0); $hoja->setTitle($titulo); $hoja->fromArray($rowData, '', 'A1'); $cantidadColumnas = count(array_shift($rowData)); $abcd = array('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's'); $ultimaColumna = $abcd[$cantidadColumnas - 1]; $header = 'a1:' . $ultimaColumna . '1'; $hoja->getStyle($header)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFFFFF00'); $style = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $hoja->getStyle($header)->applyFromArray($style); for ($col = ord('a'); $col <= ord('z'); $col++) { $hoja->getColumnDimension(chr($col))->setAutoSize(true); } header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $titulo . '.xls"'); header('Cache-Control: max-age=0'); header('Cache-Control: max-age=1'); // 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; } }
/** * * @param PHPExcel_Worksheet $hoja * @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla * @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p */ function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false) { $objetoExcel = new PHPExcel(); $hojaInsertar = $objetoExcel->getSheet(0); $hojaInsertar->setTitle('Insertar'); if ($objetoExcel->getSheetCount() > 1) { $hojaActualizar = $objetoExcel->getSheet(1); $hojaActualizar->setTitle('Actualizar'); } else { $hojaActualizar = new PHPExcel_Worksheet(); $hojaActualizar->setTitle('Actualizar'); $objetoExcel->addSheet($hojaActualizar); } $rango = $hoja->calculateWorksheetDataDimension(); if (!$incluirPrimeraFila) { $rango[1] = '2'; } $contenidoExcel = $hoja->rangeToArray($rango); $datos = array(); $datos['insertar'] = array(); $datos['actualizar'] = array(); if ($pk) { $db = new DbConnection(); $db->abrirConexion(); $llavePrimaria = $_SESSION['pk']; foreach ($contenidoExcel as $fila) { $existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]); if ($existe) { $datos['actualizar'][] = $fila; } else { $datos['insertar'][] = $fila; } } $db->cerrarConexion(); } else { foreach ($contenidoExcel as $fila) { $datos['insertar'][] = $fila; } } $hojaInsertar->fromArray($datos['insertar'], null, 'A1', true); $hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true); $escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007'); $escritorExcel->save('excelTmp/tmp_import_upload.xlsx'); return 'excelTmp/tmp_import_upload.xlsx'; }
/** * Write data to a row * * @param array $data * @param bool $finalize * @param null $sheetIndex * @throws \Exception */ public function writeRow(array $data, $finalize = true, $sheetIndex = null) { // get the sheet to write in if (null === $sheetIndex) { $sheet = $this->file->getActiveSheet(); } else { $sheet = $this->file->getSheet($sheetIndex); $this->file->setActiveSheetIndex($sheetIndex); } foreach ($data as $value) { $coordinate = sprintf('%s%d', $this->currentColumn, $this->currentRow); $sheet->setCellValue($coordinate, $value); $this->nextColumn(); } if ($finalize) { $this->nextRow(); } }
/** * Get the sheet by id or name, else get the active sheet * * @param callable|integer|string $sheetID * * @return \PHPExcel_Worksheet */ protected function getSheetByIdOrName($sheetID) { // If is a string, return the sheet by name if (is_string($sheetID)) { return $this->excel->getSheetByName($sheetID); } // Else it should be the sheet index return $this->excel->getSheet($sheetID); }
public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(FALSE); $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new PHPExcel_Writer_Exception("Could not open file $pFilename for writing."); } if ($this->_excelCompatibility) { fwrite($fileHandle, "\xEF\xBB\xBF"); // Enforce UTF-8 BOM Header $this->setEnclosure('"'); // Set enclosure to " $this->setDelimiter(";"); // Set delimiter to a semi-colon $this->setLineEnding("\r\n"); fwrite($fileHandle, 'sep=' . $this->getDelimiter() . $this->_lineEnding); } elseif ($this->_useBOM) { // Write the UTF-8 BOM code if required fwrite($fileHandle, "\xEF\xBB\xBF"); } // Identify the range that we need to extract from the worksheet $maxCol = $sheet->getHighestDataColumn(); $maxRow = $sheet->getHighestDataRow(); // Write rows to file for($row = 1; $row <= $maxRow; ++$row) { // Convert the row to an array... $cellsArray = $sheet->rangeToArray('A'.$row.':'.$maxCol.$row,'', $this->_preCalculateFormulas); // ... and write to the file $this->_writeLine($fileHandle, $cellsArray[0]); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } // Get cell collection $cellCollection = $sheet->getCellCollection(); // Write headers $this->_writeHTMLHeader($fileHandle); $this->_writeStyles($fileHandle, $sheet); $this->_writeTableHeader($fileHandle); // Get worksheet dimension $dimension = explode(':', $sheet->calculateWorksheetDimension()); $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]); $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1; $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]); $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1; // Loop trough cells $rowData = null; for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) { // Start a new row $rowData = array(); // Loop trough columns for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) { // Cell exists? if ($sheet->cellExistsByColumnAndRow($column, $row)) { $rowData[$column] = $sheet->getCellByColumnAndRow($column, $row); } else { $rowData[$column] = ''; } } // Write row $this->_writeRow($fileHandle, $sheet, $rowData, $row - 1); } // Write footers $this->_writeTableFooter($fileHandle); $this->_writeHTMLFooter($fileHandle); // Close file fclose($fileHandle); }
/** * Returns a PHPExcel_Worksheet object if it the sheet index * exists, false otherwise * @param $index * @return bool|\PHPExcel_Worksheet */ private function getSheetByIndex($index) { try { $this->worksheet = $this->workbook->getSheet($index); } catch (\PHPExcel_Exception $e) { //sheet out of bounds return false; } return $this->worksheet; }
/** * TODO: Write record to XLS. * @param Array $fields The elements of this array will be written into a line * of the current XLS file. */ public function putRecord($fields) { foreach ($fields as $colNr => $field) { if (empty($this->columnWidths[$colNr]) || $this->columnWidths[$colNr] < strlen($field)) { $this->columnWidths[$colNr] = strlen($field); $this->phpExcelObj->getSheet($this->sheetNr)->getColumnDimensionByColumn($colNr)->setWidth($this->columnWidths[$colNr]); } $this->phpExcelObj->getSheet($this->sheetNr)->setCellValueByColumnAndRow($colNr, $this->nextRowNr, $field); } $this->nextRowNr++; }
function excel_writer($row_data_excel) { $objPHPEXCEL = new PHPExcel(); $row_len = count($row_data_excel); $row_count = 0; $myWorkSheet = new PHPExcel_WorkSheet($objPHPEXCEL, "班級"); $objPHPEXCEL->addSheet($myWorkSheet, 0); $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, 1, "班級"); $objPHPEXCEL->getSheet(0)->getColumnDimension('A')->setWidth(12); $column = 2; while ($row_count < $row_len) { $objPHPEXCEL->getSheet(0)->setCellValueByColumnAndRow(0, $column, $row_data_excel[$row_count]["class"]); $row_count++; $column++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPEXCEL, 'Excel5'); $objWriter->save("/var/www/sports/67/admin/excel/class.xls"); $objPHPEXCEL->disconnectWorksheets(); unset($objPHPEXCEL); header("Location: http://dpo.nttu.edu.tw/sports/67/admin/excel/class.xls"); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // check mbstring.func_overload if (ini_get('mbstring.func_overload') != 0) { throw new Exception('Multibyte string function overloading in PHP must be disabled.'); } // garbage collect $this->_phpExcel->garbageCollect(); $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = count($this->_phpExcel->getAllSheets()); for ($i = 0; $i < $countSheets; ++$i) { $phpSheet = $this->_phpExcel->getSheet($i); $writerWorksheet = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $phpSheet); $this->_writerWorksheets[$i] = $writerWorksheet; } // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); $OLE->init(); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE)); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); }
/** * Generate sheet data * * @return string * @throws Exception */ public function generateSheetData() { // PHPExcel object known? if (is_null($this->_phpExcel)) { throw new Exception('Internal PHPExcel object not set to an instance of an object.'); } // Fetch sheets $sheets = array(); if (is_null($this->_sheetIndex)) { $sheets = $this->_phpExcel->getAllSheets(); } else { $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex); } // Construct HTML $html = ''; // Loop all sheets foreach ($sheets as $sheet) { // Calculate hash code $hashCode = $sheet->getHashCode(); // Get cell collection $cellCollection = $sheet->getCellCollection(); // Write table header $html .= $this->_generateTableHeader($hashCode); // Get worksheet dimension $dimension = explode(':', $sheet->calculateWorksheetDimension()); $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]); $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1; $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]); $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1; // Loop trough cells $rowData = null; for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) { // Start a new row $rowData = array(); // Loop trough columns for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) { // Cell exists? if ($sheet->cellExistsByColumnAndRow($column, $row)) { $rowData[$column] = $sheet->getCellByColumnAndRow($column, $row); } else { $rowData[$column] = ''; } } // Write row $html .= $this->_generateRow($sheet, $rowData, $row - 1); } // Write table footer $html .= $this->_generateTableFooter(); } // Return return $html; }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // garbage collect $this->_phpExcel->garbageCollect(); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // initialize colors array $this->_colors = array(); // Initialise workbook writer $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser); // Initialise worksheet writers $countSheets = $this->_phpExcel->getSheetCount(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i] = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $this->_phpExcel->getSheet($i)); } // build Escher objects. Escher objects for workbooks needs to be build before Escher object for workbook. $this->_buildWorksheetEschers(); $this->_buildWorkbookEscher(); // add 15 identical cell style Xfs // for now, we use the first cellXf instead of cellStyleXf $cellXfCollection = $this->_phpExcel->getCellXfCollection(); for ($i = 0; $i < 15; ++$i) { $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true); } // add all the cell Xfs foreach ($this->_phpExcel->getCellXfCollection() as $style) { $this->_writerWorkbook->addXfWriter($style, false); } // initialize OLE file $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book'; $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName)); // Write the worksheet streams before the global workbook stream, // because the byte sizes of these are needed in the global workbook stream $worksheetSizes = array(); for ($i = 0; $i < $countSheets; ++$i) { $this->_writerWorksheets[$i]->close(); $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize; } // add binary data for global workbook stream $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes)); // add binary data for sheet streams for ($i = 0; $i < $countSheets; ++$i) { $OLE->append($this->_writerWorksheets[$i]->getData()); } $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE)); // save the OLE file $res = $root->save($pFilename); PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
function exportExcel($users) { Vendor('Excel.PHPExcel'); $objPHPExcel = new \PHPExcel(); $objPHPExcel->getProperties()->setCreator('DHGate')->setLastModifiedBy('DHGate')->setTitle('DHGate Facebook Activity'); $objPHPExcel->getSheet(0)->setCellValue(columnIterater(0) . '1', 'Facebook ID')->setCellValue(columnIterater() . '1', 'Name')->setCellValue(columnIterater() . '1', '创建时间')->setCellValue(columnIterater() . '1', '剩余次数')->setCellValue(columnIterater() . '1', '邀请次数'); $objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(30); $objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(20); $objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(20); $rowIndex = 2; foreach ($users as $row) { $objPHPExcel->getSheet(0)->setCellValue(columnIterater(0) . $rowIndex, '`' . $row['fb_id'])->setCellValue(columnIterater() . $rowIndex, $row['name'])->setCellValue(columnIterater() . $rowIndex, $row['ctime'])->setCellValue(columnIterater() . $rowIndex, $row['guess_count'])->setCellValue(columnIterater() . $rowIndex, $row['invite_accept_count'] . '/' . $row['invite_count']); ++$rowIndex; } //生成xls文件 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="user-数据统计.xls"'); header('Cache-Control: max-age=0'); $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
/** * Get the sheet by id or name, else get the active sheet * @param callable|integer|string $sheetID * @param boolean $isCallable * @throws \PHPExcel_Exception * @return \PHPExcel_Worksheet */ protected function getSheetByIdOrName($sheetID, $isCallable = false) { // If is callback, return the active sheet if ($isCallable) { return $this->excel->getActiveSheet(); } // If is a string, return the sheet by name if (is_string($sheetID)) { return $this->excel->getSheetByName($sheetID); } // Else it should be the sheet index return $this->excel->getSheet($sheetID); }
/** * Opens excel file * @return \Meridius\PhpExcel\Reader * @throws PhpExcelException */ public function open() { $reader = PhpOffice_PHPExcel_IOFactory::createReaderForFile($this->file); if (count($this->sheetsToLoad) > 0) { $reader->setLoadSheetsOnly($this->sheetsToLoad); } $reader->setReadDataOnly(true); $this->excel = $reader->load($this->file); if (is_null($this->sheetName)) { try { $this->activeSheet = $this->excel->getSheet($this->sheetIndex); } catch (Exception $e) { throw new PhpExcelException("Sheet index '{$this->sheetIndex}' is out of range of excel indexes."); } } else { $this->activeSheet = $this->excel->getSheetByName($this->sheetName); } if (!$this->activeSheet instanceof PhpOffice_PHPExcel_Worksheet) { throw new PhpExcelException("Sheet with name '{$this->sheetName}' does not exist in input excel."); } $this->mapHeaders(); return $this; }
public function report($dates, $officials) { $results = new AvailReporterResults(); // For wrapping text \PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder()); $this->wb = $wb = new \PHPExcel(); $ws = $wb->getSheet(); $ws->setTitle('RefAvail'); $ws->getCell('A1')->setValue('Referee Name'); $ws->getCell('B1')->setValue('Referee Info'); $ws->getColumnDimension('A')->setWidth(20); $ws->getColumnDimension('B')->setWidth(20); $col = 'C'; foreach ($dates as $date) { $dt = \DateTime::createFromFormat('Y-m-d', $date); $ws->getCell($col . '1')->setValue($dt->format('D M d')); $ws->getColumnDimension($col)->setWidth(25); $col++; } $row = 2; foreach ($officials as $official) { $ws->getCell('A' . $row)->setValue($official['name']); $info = sprintf("F: %s\n%s\n%s\nR: %s", $official['city'], $official['cell'], $official['home'], $official['rank']); $ws->getCell('B' . $row)->setValue($info); $col = 'C'; foreach ($dates as $date) { $cr = $col . $row; $avail = implode("\n", $official['avail'][$date]); $ws->getCell($cr)->setValue($avail); if ($avail === 'Blocked ALL DAY') { $style = $ws->getStyle($cr); $fill = $style->getFill(); $fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $fill->getStartColor()->setARGB('FFFF6666'); } if ($avail === 'Open All Day') { $style = $ws->getStyle($cr); $fill = $style->getFill(); $fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $fill->getStartColor()->setARGB('FFCCFFCC'); } $col++; } $row++; } $wb->setActiveSheetIndex(0); return $results; }
public static function download_excel_file($data, $data_headers, $totals, $title, $dates) { $letters = range('A', 'Z'); $cells_header = $title . ' for ' . $dates; $objPHPExcel = new \PHPExcel(); $objPHPExcel->getProperties()->setCreator("nginad.com")->setLastModifiedBy("nginad.com")->setTitle("Statistic reports - " . $title)->setSubject("")->setDescription("")->setKeywords("")->setCategory(""); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getSheet(0)->setTitle($title); $objPHPExcel->getActiveSheet()->SetCellValue('A1', $cells_header); $objPHPExcel->getActiveSheet()->mergeCells('A1:' . $letters[count($data_headers) - 1] . '1'); for ($j = 0; $j < count($data_headers); $j++) { $objPHPExcel->getActiveSheet()->SetCellValue($letters[$j] . '3', $data_headers[$j]); } $objPHPExcel->getActiveSheet()->getStyle('A3:' . $letters[count($data_headers) - 1] . '3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD'); if (empty($data)) { $objPHPExcel->getActiveSheet()->SetCellValue('A5', 'No records'); $objPHPExcel->getActiveSheet()->mergeCells('A5:' . $letters[count($data_headers) - 1] . '5'); } else { $i = 4; foreach ($data as $elem) { $elem = array_values((array) $elem); for ($j = 0; $j < count($elem); $j++) { $objPHPExcel->getActiveSheet()->SetCellValue($letters[$j] . $i, $elem[$j]); } $i++; } $i++; $j = 0; foreach ($totals as $key => $elem) { $objPHPExcel->getActiveSheet()->SetCellValue($letters[$j++] . $i, $totals[$key]); } } foreach ($letters as $columnID) { $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true); } $fname = str_replace(" ", "_", $cells_header); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $fname . '.xlsx"'); header('Cache-Control: max-age=0'); $objPHPExcel->setActiveSheetIndex(0); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
/** * Dumps the stats table * @param StatsTable $statsTable * @return string */ public function dump(StatsTable $statsTable) { $excel = new \PHPExcel(); $excel->getDefaultStyle()->applyFromArray($this->getDefaultStyleArray()); $sheet = $excel->getSheet(); $row = 1; $data = $statsTable->getData(); $width = count(reset($data)); // HEADERS // if ($this->enableHeaders) { $headerStyle = new \PHPExcel_Style(); $headerStyle->applyFromArray($this->getHeadersStyleArray()); $col = 0; foreach ($statsTable->getHeaders() as $header) { $sheet->setCellValueByColumnAndRow($col, $row, $header); $col++; } $sheet->duplicateStyle($headerStyle, 'A1:' . \PHPExcel_Cell::stringFromColumnIndex($width - 1) . '1'); $row++; } // DATA // foreach ($statsTable->getData() as $data) { $this->applyValues($sheet, $row, $data, $statsTable->getDataFormats()); $row++; } // AGGREGATIONS // if ($this->enableAggregation) { $this->applyValues($sheet, $row, $statsTable->getAggregations(), $statsTable->getAggregationsFormats(), $this->getAggregationsStyleArray()); } // FINAL FORMATTING // for ($col = 0; $col < $width; $col++) { $sheet->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true); } $xlsDumper = new \PHPExcel_Writer_Excel2007($excel); $pFilename = @tempnam(\PHPExcel_Shared_File::sys_get_temp_dir(), 'phpxltmp'); $xlsDumper->save($pFilename); $contents = file_get_contents($pFilename); @unlink($pFilename); unset($excel); unset($xlsDumper); return $contents; }
/** * Save PHPExcel to file * * @param string $pFilename * @throws PHPExcel_Writer_Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->getWriteDebugLog(); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog(FALSE); $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new PHPExcel_Writer_Exception("Could not open file {$pFilename} for writing."); } if ($this->_excelCompatibility) { // Write the UTF-16LE BOM code fwrite($fileHandle, "ÿþ"); // Excel uses UTF-16LE encoding $this->setEnclosure(); // Default enclosure is " $this->setDelimiter("\t"); // Excel delimiter is a TAB } elseif ($this->_useBOM) { // Write the UTF-8 BOM code fwrite($fileHandle, ""); } // Identify the range that we need to extract from the worksheet $maxCol = $sheet->getHighestColumn(); $maxRow = $sheet->getHighestRow(); // Write rows to file for ($row = 1; $row <= $maxRow; ++$row) { // Convert the row to an array... $this->setEnclosure(''); $cellsArray = $sheet->rangeToArray('A' . $row . ':' . $maxCol . $row, '', $this->_preCalculateFormulas); // ... and write to the file $this->_writeLine($fileHandle, $cellsArray[0]); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance($this->_phpExcel)->getDebugLog()->setWriteDebugLog($saveDebugLog); }
private function writeOneCertificate($wagons, $certificate, $dir) { $amount_wagons_in_file = 15; $file_number = 0; $writed_wagons = 0; $wagons = array_values(array_unique($wagons, SORT_NUMERIC)); while (count($wagons) - $writed_wagons > 0) { $file = new \PHPExcel(); $sheet = $file->getSheet(0); for ($i = 1; $i <= $amount_wagons_in_file && count($wagons) - $writed_wagons > 0; $i++) { $sheet->setCellValue('a' . $i, $wagons[$writed_wagons]); $writed_wagons++; } $file_name = $certificate . '_' . $file_number . '.xlsx'; $excel_path = $dir . '/' . $file_name; $writer = \PHPExcel_IOFactory::createWriter($file, 'Excel2007'); $writer->save($excel_path); $this->writeFilePath($excel_path, $file_name, 'excel', $this->tasks_id); $file_number++; } }
/** * Write content types to XML format * * @param PHPExcel $pPHPExcel * @return string XML Output * @throws Exception */ public function writeContentTypes(PHPExcel $pPHPExcel = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Types $objWriter->startElement('Types'); $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/content-types'); // Theme $this->_writeOverrideContentType($objWriter, '/xl/theme/theme1.xml', 'application/vnd.openxmlformats-officedocument.theme+xml'); // Styles $this->_writeOverrideContentType($objWriter, '/xl/styles.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'); // Rels $this->_writeDefaultContentType($objWriter, 'rels', 'application/vnd.openxmlformats-package.relationships+xml'); // XML $this->_writeDefaultContentType($objWriter, 'xml', 'application/xml'); // VML $this->_writeDefaultContentType($objWriter, 'vml', 'application/vnd.openxmlformats-officedocument.vmlDrawing'); // Workbook $this->_writeOverrideContentType($objWriter, '/xl/workbook.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'); // DocProps $this->_writeOverrideContentType($objWriter, '/docProps/app.xml', 'application/vnd.openxmlformats-officedocument.extended-properties+xml'); $this->_writeOverrideContentType($objWriter, '/docProps/core.xml', 'application/vnd.openxmlformats-package.core-properties+xml'); // Worksheets $sheetCount = $pPHPExcel->getSheetCount(); for ($i = 0; $i < $sheetCount; ++$i) { $this->_writeOverrideContentType($objWriter, '/xl/worksheets/sheet' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'); } // Shared strings $this->_writeOverrideContentType($objWriter, '/xl/sharedStrings.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'); // Add worksheet relationship content types for ($i = 0; $i < $sheetCount; ++$i) { if ($pPHPExcel->getSheet($i)->getDrawingCollection()->count() > 0) { $this->_writeOverrideContentType($objWriter, '/xl/drawings/drawing' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.drawing+xml'); } } // Comments for ($i = 0; $i < $sheetCount; ++$i) { if (count($pPHPExcel->getSheet($i)->getComments()) > 0) { $this->_writeOverrideContentType($objWriter, '/xl/comments' . ($i + 1) . '.xml', 'application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml'); } } // Add media content-types $aMediaContentTypes = array(); $mediaCount = $this->getParentWriter()->getDrawingHashTable()->count(); for ($i = 0; $i < $mediaCount; ++$i) { $extension = ''; $mimeType = ''; if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_Drawing) { $extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getExtension()); $mimeType = $this->_getImageMimeType($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getPath()); } else { if ($this->getParentWriter()->getDrawingHashTable()->getByIndex($i) instanceof PHPExcel_Worksheet_MemoryDrawing) { $extension = strtolower($this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType()); $extension = explode('/', $extension); $extension = $extension[1]; $mimeType = $this->getParentWriter()->getDrawingHashTable()->getByIndex($i)->getMimeType(); } } if (!isset($aMediaContentTypes[$extension])) { $aMediaContentTypes[$extension] = $mimeType; $this->_writeDefaultContentType($objWriter, $extension, $mimeType); } } $sheetCount = $pPHPExcel->getSheetCount(); for ($i = 0; $i < $sheetCount; ++$i) { if (count($pPHPExcel->getSheet()->getHeaderFooter()->getImages()) > 0) { foreach ($pPHPExcel->getSheet()->getHeaderFooter()->getImages() as $image) { if (!isset($aMediaContentTypes[strtolower($image->getExtension())])) { $aMediaContentTypes[strtolower($image->getExtension())] = $this->_getImageMimeType($image->getPath()); $this->_writeDefaultContentType($objWriter, strtolower($image->getExtension()), $aMediaContentTypes[strtolower($image->getExtension())]); } } } } $objWriter->endElement(); // Return return $objWriter->getData(); }
/** * Write sheets * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel $pPHPExcel * @throws PHPExcel_Writer_Exception */ private function _writeSheets(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel $pPHPExcel = null) { // Write sheets $objWriter->startElement('sheets'); $sheetCount = $pPHPExcel->getSheetCount(); for ($i = 0; $i < $sheetCount; ++$i) { // sheet $this->_writeSheet($objWriter, $pPHPExcel->getSheet($i)->getTitle(), $i + 1, $i + 1 + 3, $pPHPExcel->getSheet($i)->getSheetState()); } $objWriter->endElement(); }
public function initializeObject() { $this->objPHPExcel = PHPExcel_IOFactory::load(GeneralUtility::getFileAbsFileName($this->data)); $this->worksheet = $this->objPHPExcel->getSheet(0); $highestColumn = $this->worksheet->getHighestColumn(); // e.g 'F' $this->highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $this->totalRowCount = $this->worksheet->getHighestRow(); $this->header = array(); for ($col = 0; $col < $this->highestColumnIndex; ++$col) { $cell = $this->worksheet->getCellByColumnAndRow($col, 1); $this->header[$col] = $cell->getValue(); } $this->rowPointer++; }
/** * Save PHPExcel to file * * @param string $pFilename * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); $saveDebugLog = PHPExcel_Calculation::getInstance()->writeDebugLog; PHPExcel_Calculation::getInstance()->writeDebugLog = false; $saveArrayReturnType = PHPExcel_Calculation::getArrayReturnType(); PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE); // Open file $fileHandle = fopen($pFilename, 'wb+'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } if ($this->_useBOM) { // Write the UTF-8 BOM code fwrite($fileHandle, ""); } // Identify the range that we need to extract from the worksheet $maxCol = $sheet->getHighestColumn(); $maxRow = $sheet->getHighestRow(); // Write rows to file for ($row = 1; $row <= $maxRow; ++$row) { // Convert the row to an array... $cellsArray = $sheet->rangeToArray('A' . $row . ':' . $maxCol . $row, '', $this->_preCalculateFormulas); // ... and write to the file $this->_writeLine($fileHandle, $cellsArray[0]); } // Close file fclose($fileHandle); PHPExcel_Calculation::setArrayReturnType($saveArrayReturnType); PHPExcel_Calculation::getInstance()->writeDebugLog = $saveDebugLog; }
/** * @param $fromRow * @throws \PHPExcel_Exception */ private function loadData($fromRow) { $sheet = $this->phpExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); for ($row = $fromRow; $row <= $highestRow; $row++) { $data = $sheet->rangeToArray("A{$row}:{$highestColumn}" . $row, NULL, TRUE, FALSE); $this->data[] = $data[0]; } }