/**
* 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);
} elseif ($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()
*/
public 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 columnInfo:
// $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->columnInfo[] = 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->columnInfo)) {
$colcount = count($this->columnInfo);
for ($i = 0; $i < $colcount; ++$i) {
$this->writeColinfo($this->columnInfo[$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\StringHelper::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->fontHashIndex[$element->getFont()->getHashCode()];
} else {
$str_fontidx = 0;
}
$arrcRun[] = array('strlen' => $str_pos, 'fontidx' => $str_fontidx);
// Position FROM
$str_pos += \PHPExcel\Shared\StringHelper::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);
} elseif (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();
}
