/**
* 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];
}
}
