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