/** * 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 Worksheet $sheet The sheet * @param integer $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 = 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 = Shared_Drawing::cellDimensionToPixels($width, $font); } else { // we don't even have any default column dimension. Width depends on default font $pixelWidth = 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; }
/** * Read COLINFO record */ private function _readColInfo() { $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: 2; index to first column in range $fc = $this->_GetInt2d($recordData, 0); // first column index // offset: 2; size: 2; index to last column in range $lc = $this->_GetInt2d($recordData, 2); // first column index // offset: 4; size: 2; width of the column in 1/256 of the width of the zero character $width = $this->_GetInt2d($recordData, 4); // offset: 6; size: 2; index to XF record for default column formatting $xfIndex = $this->_GetInt2d($recordData, 6); // offset: 8; size: 2; option flags // bit: 0; mask: 0x0001; 1= columns are hidden $isHidden = (0x1 & $this->_GetInt2d($recordData, 8)) >> 0; // bit: 10-8; mask: 0x0700; outline level of the columns (0 = no outline) $level = (0x700 & $this->_GetInt2d($recordData, 8)) >> 8; // bit: 12; mask: 0x1000; 1 = collapsed $isCollapsed = (0x1000 & $this->_GetInt2d($recordData, 8)) >> 12; // offset: 10; size: 2; not used for ($i = $fc; $i <= $lc; ++$i) { if ($lc == 255 || $lc == 256) { $this->_phpSheet->getDefaultColumnDimension()->setWidth($width / 256); break; } $this->_phpSheet->getColumnDimensionByColumn($i)->setWidth($width / 256); $this->_phpSheet->getColumnDimensionByColumn($i)->setVisible(!$isHidden); $this->_phpSheet->getColumnDimensionByColumn($i)->setOutlineLevel($level); $this->_phpSheet->getColumnDimensionByColumn($i)->setCollapsed($isCollapsed); $this->_phpSheet->getColumnDimensionByColumn($i)->setXfIndex($this->_mapCellXfIndex[$xfIndex]); } } }
/** * Add data to the beginning of the workbook (note the reverse order) * and to the end of the workbook. * * @access public * @see Writer_Excel5_Workbook::storeWorkbook() */ function close() { $num_sheets = $this->_phpSheet->getParent()->getSheetCount(); // 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 = Shared_Font::getDefaultColumnWidthByFont($this->_phpSheet->getParent()->getDefaultStyle()->getFont()); } $columnLetter = 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 = 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 RichText) { $this->_writeString($row, $column, $cell->getValue()->getPlainText(), $xfIndex); } else { switch ($cell->getDatatype()) { case 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 Cell_DataType::TYPE_FORMULA: $calculatedValue = $this->_preCalculateFormulas ? $cell->getCalculatedValue() : null; $this->_writeFormula($row, $column, $cell->getValue(), $xfIndex, $calculatedValue); break; case Cell_DataType::TYPE_BOOL: $this->_writeBoolErr($row, $column, $cell->getValue(), 0, $xfIndex); break; case Cell_DataType::TYPE_ERROR: $this->_writeBoolErr($row, $column, $this->_mapErrorCode($cell->getValue()), 1, $xfIndex); break; case 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) = 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, Cell::columnIndexFromString($column) - 1, $url); } } if ($this->_BIFF_version == 0x600) { $this->_writeDataValidity(); $this->_writeSheetLayout(); $this->_writeSheetProtection(); $this->_writeRangeProtection(); } $this->_storeEof(); }
/** * Write Cols * * @param Shared_XMLWriter $objWriter XML Writer * @param Worksheet $pSheet Worksheet * @throws Exception */ private function _writeCols(Shared_XMLWriter $objWriter = null, 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', Cell::columnIndexFromString($colDimension->getColumnIndex())); $objWriter->writeAttribute('max', 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', 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(); } }