/**
* Write Cols
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @throws Exception
*/
private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
// cols
$objWriter->startElement('cols');
// Check if there is at least one column dimension specified. If not, create one.
if (count($pSheet->getColumnDimensions()) == 0) {
$pSheet->getColumnDimension('A')->setWidth(10);
}
$pSheet->calculateColumnWidths();
// Loop trough column dimensions
foreach ($pSheet->getColumnDimensions() as $colDimension) {
// col
$objWriter->startElement('col');
$objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
$objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
if ($colDimension->getWidth() < 0) {
// No width set, apply default of 10
$objWriter->writeAttribute('width', '10');
} else {
// Width set
$objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth()));
}
// Column visibility
if ($colDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Auto size?
if ($colDimension->getAutoSize()) {
$objWriter->writeAttribute('bestFit', 'true');
} else {
$objWriter->writeAttribute('customWidth', 'true');
}
// Collapsed
if ($colDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($colDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
}
$objWriter->endElement();
}
$objWriter->endElement();
}
/**
*
* @param string $startColName
* @param string $endColName
* @return Formatter
*/
public function autosizeColumns($startColName = null, $endColName = null)
{
if (is_null($startColName) || is_null($endColName)) {
list($startCoordinates, $endCoordinates) = explode(':', $this->sheet->calculateWorksheetDataDimension());
$startColName = is_null($startColName) ? preg_replace('/\\d*/', '', $startCoordinates) : $startColName;
$endColName = is_null($endColName) ? preg_replace('/\\d*/', '', $endCoordinates) : $endColName;
}
$startNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($startColName);
// A = 1
$endNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($endColName);
for ($i = $startNum - 1; $i < $endNum; $i++) {
$this->sheet->getColumnDimensionByColumn($i)->setAutoSize(true);
// A = 0
}
$this->sheet->calculateColumnWidths();
return $this;
}
/**
* Write Cols
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @throws Exception
*/
private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
// cols
if (count($pSheet->getColumnDimensions()) > 0) {
$objWriter->startElement('cols');
$pSheet->calculateColumnWidths();
// Loop through column dimensions
foreach ($pSheet->getColumnDimensions() as $colDimension) {
// col
$objWriter->startElement('col');
$objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
$objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
if ($colDimension->getWidth() < 0) {
// No width set, apply default of 10
$objWriter->writeAttribute('width', '9.10');
} else {
// Width set
$objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth()));
}
// Column visibility
if ($colDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Auto size?
if ($colDimension->getAutoSize()) {
$objWriter->writeAttribute('bestFit', 'true');
}
// Custom width?
if ($colDimension->getWidth() != $pSheet->getDefaultColumnDimension()->getWidth()) {
$objWriter->writeAttribute('customWidth', 'true');
}
// Collapsed
if ($colDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($colDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
}
// Style
$objWriter->writeAttribute('style', $colDimension->getXfIndex());
$objWriter->endElement();
}
$objWriter->endElement();
}
}
/**
* Add data to the beginning of the workbook (note the reverse order)
* and to the end of the workbook.
*
* @access public
* @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
*/
function close()
{
$num_sheets = count($this->_phpSheet->getParent()->getAllSheets());
// Write BOF record
$this->_storeBof(0x10);
// Write PRINTHEADERS
$this->_writePrintHeaders();
// Write PRINTGRIDLINES
$this->_writePrintGridlines();
// Write GRIDSET
$this->_writeGridset();
// Calculate column widths
$this->_phpSheet->calculateColumnWidths();
// Column dimensions
$columnDimensions = $this->_phpSheet->getColumnDimensions();
for ($i = 0; $i < 256; ++$i) {
$hidden = 0;
$level = 0;
$xfIndex = 15;
// there are 15 cell style Xfs
if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
$width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
} else {
$width = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont());
}
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($i);
if (isset($columnDimensions[$columnLetter])) {
$columnDimension = $columnDimensions[$columnLetter];
if ($columnDimension->getWidth() >= 0) {
$width = $columnDimension->getWidth();
}
$hidden = $columnDimension->getVisible() ? 0 : 1;
$level = $columnDimension->getOutlineLevel();
$xfIndex = $columnDimension->getXfIndex() + 15;
// there are 15 cell style Xfs
}
// Components of _colinfo:
// $firstcol first column on the range
// $lastcol last column on the range
// $width width to set
// $xfIndex The optional cell style Xf index to apply to the columns
// $hidden The optional hidden atribute
// $level The optional outline level
$this->_colinfo[] = array($i, $i, $width, $xfIndex, $hidden, $level);
}
// Write GUTS
$this->_writeGuts();
// Write DEFAULTROWHEIGHT
if ($this->_BIFF_version == 0x600) {
$this->_writeDefaultRowHeight();
}
// Write WSBOOL
$this->_writeWsbool();
// Write horizontal and vertical page breaks
$this->_writeBreaks();
// Write page header
$this->_writeHeader();
// Write page footer
$this->_writeFooter();
// Write page horizontal centering
$this->_writeHcenter();
// Write page vertical centering
$this->_writeVcenter();
// Write left margin
$this->_writeMarginLeft();
// Write right margin
$this->_writeMarginRight();
// Write top margin
$this->_writeMarginTop();
// Write bottom margin
$this->_writeMarginBottom();
// Write page setup
$this->_writeSetup();
// Write sheet protection
$this->_writeProtect();
// Write SCENPROTECT
$this->_writeScenProtect();
// Write OBJECTPROTECT
$this->_writeObjectProtect();
// Write sheet password
$this->_writePassword();
// Write DEFCOLWIDTH record
$this->_writeDefcol();
// Write the COLINFO records if they exist
if (!empty($this->_colinfo)) {
$colcount = count($this->_colinfo);
for ($i = 0; $i < $colcount; ++$i) {
$this->_writeColinfo($this->_colinfo[$i]);
}
}
// Write EXTERNCOUNT of external references
if ($this->_BIFF_version == 0x500) {
$this->_writeExterncount($num_sheets);
}
// Write EXTERNSHEET references
if ($this->_BIFF_version == 0x500) {
for ($i = 0; $i < $num_sheets; ++$i) {
$this->_writeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
}
}
// Write sheet dimensions
$this->_writeDimensions();
// Row dimensions
foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
$xfIndex = $rowDimension->getXfIndex() + 15;
// there are 15 cellXfs
$this->_writeRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), $xfIndex, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel());
}
// Write Cells
foreach ($this->_phpSheet->getCellCollection() as $cellID) {
$cell = $this->_phpSheet->getCell($cellID);
$row = $cell->getRow() - 1;
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
// Don't break Excel!
if ($row + 1 > 65536 or $column + 1 > 256) {
break;
}
// Write cell value
$xfIndex = $cell->getXfIndex() + 15;
// there are 15 cell style Xfs
if ($cell->getValue() instanceof PHPExcel_RichText) {
$this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex);
} else {
switch ($cell->getDatatype()) {
case PHPExcel_Cell_DataType::TYPE_STRING:
if ($cell->getValue() === '' or $cell->getValue() === null) {
$this->_writeBlank($row, $column, $xfIndex);
} else {
$this->_writeString($row, $column, $cell->getValue(), $xfIndex);
}
break;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
$calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null;
$this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue);
break;
case PHPExcel_Cell_DataType::TYPE_BOOL:
$this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex);
break;
case PHPExcel_Cell_DataType::TYPE_ERROR:
$this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex);
break;
case PHPExcel_Cell_DataType::TYPE_NUMERIC:
$this->_writeNumber($row, $column, $cell->getValue(), $xfIndex);
break;
}
}
}
// Append
if ($this->_BIFF_version == 0x600) {
$this->_writeMsoDrawing();
}
$this->_writeWindow2();
$this->_writeZoom();
if ($this->_phpSheet->getFreezePane()) {
$this->_writePanes();
}
$this->_writeSelection();
$this->_writeMergedCells();
// Hyperlinks
if ($this->_BIFF_version == 0x600) {
foreach ($this->_phpSheet->getHyperLinkCollection() as $coordinate => $hyperlink) {
list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinate);
$url = $hyperlink->getUrl();
if (strpos($url, 'sheet://') !== false) {
// internal to current workbook
$url = str_replace('sheet://', 'internal:', $url);
} else {
if (preg_match('/^(http:|https:|ftp:|mailto:)/', $url)) {
// URL
// $url = $url;
} else {
// external (local file)
$url = 'external:' . $url;
}
}
$this->_writeUrl($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $url);
}
}
if ($this->_BIFF_version == 0x600) {
$this->_writeDataValidity();
$this->_writeSheetLayout();
$this->_writeSheetProtection();
$this->_writeRangeProtection();
}
$this->_storeEof();
}
/**
* Write styles to file
*
* @param mixed $pFileHandle PHP filehandle
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @throws Exception
*/
private function _writeStyles($pFileHandle = null, PHPExcel_Worksheet $pSheet)
{
if (!is_null($pFileHandle)) {
// Construct HTML
$html = '';
// Start styles
$html .= ' <style>' . "\r\n";
$html .= ' <!--' . "\r\n";
$html .= ' html {' . "\r\n";
$html .= ' font-family: Calibri, Arial, Helvetica, Sans Serif;' . "\r\n";
$html .= ' font-size: 10pt;' . "\r\n";
$html .= ' background-color: white;' . "\r\n";
$html .= ' }' . "\r\n";
$html .= ' table.sheet, table.sheet td {' . "\r\n";
if ($pSheet->getShowGridlines()) {
$html .= ' border: 1px dotted black;' . "\r\n";
}
$html .= ' }' . "\r\n";
// Calculate column widths
$pSheet->calculateColumnWidths();
foreach ($pSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$html .= ' td.column' . $column . ' {' . "\r\n";
$html .= ' width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;' . "\r\n";
if ($columnDimension->getVisible() === false) {
$html .= ' display: none;' . "\r\n";
$html .= ' visibility: hidden;' . "\r\n";
}
$html .= ' }' . "\r\n";
}
// Calculate row heights
foreach ($pSheet->getRowDimensions() as $rowDimension) {
$html .= ' tr.row' . ($rowDimension->getRowIndex() - 1) . ' {' . "\r\n";
// height is disproportionately large
$px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
$html .= ' height: ' . $px_height . 'px;' . "\r\n";
if ($rowDimension->getVisible() === false) {
$html .= ' display: none;' . "\r\n";
$html .= ' visibility: hidden;' . "\r\n";
}
$html .= ' }' . "\r\n";
}
// Calculate cell style hashes
$cellStyleHashes = new PHPExcel_HashTable();
$cellStyleHashes->addFromSource($pSheet->getStyles());
for ($i = 0; $i < $cellStyleHashes->count(); $i++) {
$html .= $this->_createCSSStyle($cellStyleHashes->getByIndex($i));
}
// End styles
$html .= ' -->' . "\r\n";
$html .= ' </style>' . "\r\n";
// Write to file
fwrite($pFileHandle, $html);
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Add data to the beginning of the workbook (note the reverse order)
* and to the end of the workbook.
*
* @access public
* @see PHPExcel_Writer_Excel5_Workbook::storeWorkbook()
*/
function close()
{
$num_sheets = count($this->_phpSheet->getParent()->getAllSheets());
// Write BOF record
$this->_storeBof(0x10);
// Write DEFCOLWIDTH record
$this->_storeDefcol();
// Calculate column widths
$this->_phpSheet->calculateColumnWidths();
// Column dimensions
foreach ($this->_phpSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
if ($column < 256) {
if ($columnDimension->getWidth() >= 0) {
$width = $columnDimension->getWidth();
} else {
if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
$width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
} else {
$width = 8;
}
}
$this->_setColumn($column, $column, $width, null, $columnDimension->getVisible() ? '0' : '1', $columnDimension->getOutlineLevel());
}
}
// Write the COLINFO records if they exist
if (!empty($this->_colinfo)) {
$colcount = count($this->_colinfo);
for ($i = 0; $i < $colcount; ++$i) {
$this->_storeColinfo($this->_colinfo[$i]);
}
}
// Write EXTERNCOUNT of external references
if ($this->_BIFF_version == 0x500) {
$this->_storeExterncount($num_sheets);
}
// Write EXTERNSHEET references
if ($this->_BIFF_version == 0x500) {
for ($i = 0; $i < $num_sheets; ++$i) {
$this->_storeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
}
}
// Write PRINTHEADERS
$this->_storePrintHeaders();
// Write PRINTGRIDLINES
$this->_storePrintGridlines();
// Write GUTS
$this->_storeGuts();
// Write GRIDSET
$this->_storeGridset();
// Write DEFAULTROWHEIGHT
if ($this->_BIFF_version == 0x600) {
$this->_storeDefaultRowHeight();
}
// Write WSBOOL
$this->_storeWsbool();
// Write horizontal and vertical page breaks
$this->_storeBreaks();
// Write page header
$this->_storeHeader();
// Write page footer
$this->_storeFooter();
// Write page horizontal centering
$this->_storeHcenter();
// Write page vertical centering
$this->_storeVcenter();
// Write left margin
$this->_storeMarginLeft();
// Write right margin
$this->_storeMarginRight();
// Write top margin
$this->_storeMarginTop();
/* FIXME: margins are actually appended */
// Write bottom margin
$this->_storeMarginBottom();
// Write page setup
$this->_storeSetup();
// Write sheet protection
$this->_storeProtect();
// Write sheet password
$this->_storePassword();
// Write sheet dimensions
$this->_storeDimensions();
// Write Cells
$aStyles = $this->_phpSheet->getStyles();
$emptyStyle = $this->_phpSheet->getDefaultStyle();
foreach ($this->_phpSheet->getCellCollection() as $cell) {
$row = $cell->getRow() - 1;
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
// Don't break Excel!
if ($row + 1 > 65536 or $column + 1 > 256) {
break;
}
$style = $emptyStyle;
if (isset($aStyles[$cell->getCoordinate()])) {
$style = $aStyles[$cell->getCoordinate()];
}
$styleHashIndex = $style->getHashIndex();
// Write cell value
if ($cell->getValue() instanceof PHPExcel_RichText) {
$this->_writeString($row, $column, $cell->getValue()->getPlainText(), $this->_xfIndexes[$styleHashIndex]);
} else {
switch ($cell->getDatatype()) {
case PHPExcel_Cell_DataType::TYPE_STRING:
if ($cell->getValue() === '' or $cell->getValue() === null) {
$this->_writeBlank($row, $column, $this->_xfIndexes[$styleHashIndex]);
} else {
$this->_writeString($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]);
}
break;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
$this->_writeFormula($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex]);
break;
case PHPExcel_Cell_DataType::TYPE_BOOL:
$this->_writeBoolErr($row, $column, $cell->getValue(), 0, $this->_xfIndexes[$styleHashIndex]);
break;
case PHPExcel_Cell_DataType::TYPE_ERROR:
$this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $this->_xfIndexes[$styleHashIndex]);
break;
default:
$this->_write($row, $column, $cell->getValue(), $this->_xfIndexes[$styleHashIndex], $style->getNumberFormat()->getFormatCode());
break;
}
// Hyperlink?
if ($cell->hasHyperlink()) {
$this->_writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl()));
}
}
}
// Row dimensions
foreach ($this->_phpSheet->getRowDimensions() as $rowDimension) {
$this->_setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1', $rowDimension->getOutlineLevel());
}
// Append
if ($this->_BIFF_version == 0x600) {
$this->_storeMsoDrawing();
}
$this->_storeWindow2();
$this->_storeZoom();
if ($this->_phpSheet->getFreezePane()) {
$this->_storePanes();
}
$this->_storeSelection($this->_selection);
$this->_storeMergedCells();
/* TODO: add data validity */
/*if ($this->_BIFF_version == 0x0600) {
$this->_storeDataValidity();
}*/
if ($this->_BIFF_version == 0x600) {
$this->_storeRangeProtection();
}
$this->_storeEof();
}
