/**
* Get the width of a column in pixels. We use the relationship y = ceil(7x) where
* x is the width in intrinsic Excel units (measuring width in number of normal characters)
* This holds for Arial 10
*
* @param PHPExcel_Worksheet $sheet The sheet
* @param string $col The column
* @return integer The width in pixels
*/
public static function sizeCol($sheet, $col = 'A')
{
// default font of the workbook
$font = $sheet->getParent()->getDefaultStyle()->getFont();
$columnDimensions = $sheet->getColumnDimensions();
// first find the true column width in pixels (uncollapsed and unhidden)
if (isset($columnDimensions[$col]) and $columnDimensions[$col]->getWidth() != -1) {
// then we have column dimension with explicit width
$columnDimension = $columnDimensions[$col];
$width = $columnDimension->getWidth();
$pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
} else {
if ($sheet->getDefaultColumnDimension()->getWidth() != -1) {
// then we have default column dimension with explicit width
$defaultColumnDimension = $sheet->getDefaultColumnDimension();
$width = $defaultColumnDimension->getWidth();
$pixelWidth = PHPExcel_Shared_Drawing::cellDimensionToPixels($width, $font);
} else {
// we don't even have any default column dimension. Width depends on default font
$pixelWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($font, true);
}
}
// now find the effective column width in pixels
if (isset($columnDimensions[$col]) and !$columnDimensions[$col]->getVisible()) {
$effectivePixelWidth = 0;
} else {
$effectivePixelWidth = $pixelWidth;
}
return $effectivePixelWidth;
}
/**
* 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()
{
$_phpSheet = $this->_phpSheet;
$num_sheets = $_phpSheet->getParent()->getSheetCount();
// Write BOF record
$this->_storeBof(0x10);
// Write PRINTHEADERS
$this->_writePrintHeaders();
// Write PRINTGRIDLINES
$this->_writePrintGridlines();
// Write GRIDSET
$this->_writeGridset();
// Calculate column widths
$_phpSheet->calculateColumnWidths();
// Column dimensions
if (($defaultWidth = $_phpSheet->getDefaultColumnDimension()->getWidth()) < 0) {
$defaultWidth = PHPExcel_Shared_Font::getDefaultColumnWidthByFont($_phpSheet->getParent()->getDefaultStyle()->getFont());
}
$columnDimensions = $_phpSheet->getColumnDimensions();
$maxCol = $this->_lastColumnIndex - 1;
for ($i = 0; $i <= $maxCol; ++$i) {
$hidden = 0;
$level = 0;
$xfIndex = 15;
// there are 15 cell style Xfs
$width = $defaultWidth;
$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
$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]);
}
}
$autoFilterRange = $_phpSheet->getAutoFilter()->getRange();
if (!empty($autoFilterRange)) {
// Write AUTOFILTERINFO
$this->_writeAutoFilterInfo();
}
// Write sheet dimensions
$this->_writeDimensions();
// Row dimensions
foreach ($_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 ($_phpSheet->getCellCollection() as $cellID) {
$cell = $_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) {
if ($row > 65535 || $column > 255) {
break;
}
// Write cell value
$xfIndex = $cell->getXfIndex() + 15;
// there are 15 cell style Xfs
$cVal = $cell->getValue();
if ($cVal instanceof PHPExcel_RichText) {
// $this->_writeString($row, $column, $cVal->getPlainText(), $xfIndex);
$arrcRun = array();
$str_len = PHPExcel_Shared_String::CountCharacters($cVal->getPlainText(), 'UTF-8');
$str_pos = 0;
$elements = $cVal->getRichTextElements();
foreach ($elements as $element) {
// FONT Index
if ($element instanceof PHPExcel_RichText_Run) {
$str_fontidx = $this->_fntHashIndex[$element->getFont()->getHashCode()];
} else {
$str_fontidx = 0;
}
$arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
// Position FROM
$str_pos += PHPExcel_Shared_String::CountCharacters($element->getText(), 'UTF-8');
}
$this->_writeRichTextString($row, $column, $cVal->getPlainText(), $xfIndex, $arrcRun);
} else {
switch ($cell->getDatatype()) {
case PHPExcel_Cell_DataType::TYPE_STRING:
case PHPExcel_Cell_DataType::TYPE_NULL:
if ($cVal === '' || $cVal === null) {
$this->_writeBlank($row, $column, $xfIndex);
} else {
$this->_writeString($row, $column, $cVal, $xfIndex);
}
break;
case PHPExcel_Cell_DataType::TYPE_NUMERIC:
$this->_writeNumber($row, $column, $cVal, $xfIndex);
break;
case PHPExcel_Cell_DataType::TYPE_FORMULA:
$calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null;
$this->_writeFormula($row, $column, $cVal, $xfIndex, $calculatedValue);
break;
case PHPExcel_Cell_DataType::TYPE_BOOL:
$this->_writeBoolErr($row, $column, $cVal, 0, $xfIndex);
break;
case PHPExcel_Cell_DataType::TYPE_ERROR:
$this->_writeBoolErr($row, $column, self::_mapErrorCode($cVal), 1, $xfIndex);
break;
}
}
}
// Append
$this->_writeMsoDrawing();
// Write WINDOW2 record
$this->_writeWindow2();
// Write PLV record
$this->_writePageLayoutView();
// Write ZOOM record
$this->_writeZoom();
if ($_phpSheet->getFreezePane()) {
$this->_writePanes();
}
// Write SELECTION record
$this->_writeSelection();
// Write MergedCellsTable Record
$this->_writeMergedCells();
// Hyperlinks
foreach ($_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);
}
$this->_writeDataValidity();
$this->_writeSheetLayout();
// Write SHEETPROTECTION record
$this->_writeSheetProtection();
$this->_writeRangeProtection();
$arrConditionalStyles = $_phpSheet->getConditionalStylesCollection();
if (!empty($arrConditionalStyles)) {
$arrConditional = array();
// @todo CFRule & CFHeader
// Write CFHEADER record
$this->_writeCFHeader();
// Write ConditionalFormattingTable records
foreach ($arrConditionalStyles as $cellCoordinate => $conditionalStyles) {
foreach ($conditionalStyles as $conditional) {
if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS) {
if (!in_array($conditional->getHashCode(), $arrConditional)) {
$arrConditional[] = $conditional->getHashCode();
// Write CFRULE record
$this->_writeCFRule($conditional);
}
}
}
}
}
$this->_storeEof();
}
/**
* 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();
}
