/** * Get font record data * * @return string */ public function writeFont() { $font_outline = 0; $font_shadow = 0; $icv = $this->colorIndex; // Index to color palette if ($this->font->getSuperScript()) { $sss = 1; } elseif ($this->font->getSubScript()) { $sss = 2; } else { $sss = 0; } $bFamily = 0; // Font family $bCharSet = \PHPExcel\Shared\Font::getCharsetFromFontName($this->font->getName()); // Character set $record = 0x31; // Record identifier $reserved = 0x0; // Reserved $grbit = 0x0; // Font attributes if ($this->font->getItalic()) { $grbit |= 0x2; } if ($this->font->getStrikethrough()) { $grbit |= 0x8; } if ($font_outline) { $grbit |= 0x10; } if ($font_shadow) { $grbit |= 0x20; } $data = pack("vvvvvCCCC", $this->font->getSize() * 20, $grbit, $icv, self::mapBold($this->font->getBold()), $sss, self::mapUnderline($this->font->getUnderline()), $bFamily, $bCharSet, $reserved); $data .= \PHPExcel\Shared\StringHelper::UTF8toBIFF8UnicodeShort($this->font->getName()); $length = strlen($data); $header = pack("vv", $record, $length); return $header . $data; }
/** * 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(); }
private static function toCSSArray($style) { $style = str_replace(array("\r", "\n"), "", $style); $temp = explode(';', $style); $style = array(); foreach ($temp as $item) { $item = explode(':', $item); if (strpos($item[1], 'px') !== false) { $item[1] = str_replace('px', '', $item[1]); } if (strpos($item[1], 'pt') !== false) { $item[1] = str_replace('pt', '', $item[1]); $item[1] = \PHPExcel\Shared\Font::fontSizeToPixels($item[1]); } if (strpos($item[1], 'in') !== false) { $item[1] = str_replace('in', '', $item[1]); $item[1] = \PHPExcel\Shared\Font::inchSizeToPixels($item[1]); } if (strpos($item[1], 'cm') !== false) { $item[1] = str_replace('cm', '', $item[1]); $item[1] = \PHPExcel\Shared\Font::centimeterSizeToPixels($item[1]); } $style[$item[0]] = $item[1]; } return $style; }
/** * Convert the height of a cell from user's units to pixels. By interpolation * the relationship is: y = 4/3x. If the height hasn't been set by the user we * use the default value. If the row is hidden we use a value of zero. * * @param \PHPExcel\Worksheet $sheet The sheet * @param integer $row The row index (1-based) * @return integer The width in pixels */ public static function sizeRow($sheet, $row = 1) { // default font of the workbook $font = $sheet->getParent()->getDefaultStyle()->getFont(); $rowDimensions = $sheet->getRowDimensions(); // first find the true row height in pixels (uncollapsed and unhidden) if (isset($rowDimensions[$row]) and $rowDimensions[$row]->getRowHeight() != -1) { // then we have a row dimension $rowDimension = $rowDimensions[$row]; $rowHeight = $rowDimension->getRowHeight(); $pixelRowHeight = (int) ceil(4 * $rowHeight / 3); // here we assume Arial 10 } elseif ($sheet->getDefaultRowDimension()->getRowHeight() != -1) { // then we have a default row dimension with explicit height $defaultRowDimension = $sheet->getDefaultRowDimension(); $rowHeight = $defaultRowDimension->getRowHeight(); $pixelRowHeight = \PHPExcel\Shared\Drawing::pointsToPixels($rowHeight); } else { // we don't even have any default row dimension. Height depends on default font $pointRowHeight = \PHPExcel\Shared\Font::getDefaultRowHeightByFont($font); $pixelRowHeight = \PHPExcel\Shared\Font::fontSizeToPixels($pointRowHeight); } // now find the effective row height in pixels if (isset($rowDimensions[$row]) and !$rowDimensions[$row]->getVisible()) { $effectivePixelRowHeight = 0; } else { $effectivePixelRowHeight = $pixelRowHeight; } return $effectivePixelRowHeight; }
/** * Build CSS styles * * @param boolean $generateSurroundingHTML Generate surrounding HTML style? (html { }) * @return array * @throws \PHPExcel\Writer\Exception */ public function buildCSS($generateSurroundingHTML = true) { // PHPExcel object known? if (is_null($this->phpExcel)) { throw new \PHPExcel\Writer\Exception('Internal PHPExcel object not set to an instance of an object.'); } // Cached? if (!is_null($this->cssStyles)) { return $this->cssStyles; } // Ensure that spans have been calculated if (!$this->spansAreCalculated) { $this->calculateSpans(); } // Construct CSS $css = array(); // Start styles if ($generateSurroundingHTML) { // html { } $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif'; $css['html']['font-size'] = '11pt'; $css['html']['background-color'] = 'white'; } // table { } $css['table']['border-collapse'] = 'collapse'; if (!$this->isPdf) { $css['table']['page-break-after'] = 'always'; } // .gridlines td { } $css['.gridlines td']['border'] = '1px dotted black'; $css['.gridlines th']['border'] = '1px dotted black'; // .b {} $css['.b']['text-align'] = 'center'; // BOOL // .e {} $css['.e']['text-align'] = 'center'; // ERROR // .f {} $css['.f']['text-align'] = 'right'; // FORMULA // .inlineStr {} $css['.inlineStr']['text-align'] = 'left'; // INLINE // .n {} $css['.n']['text-align'] = 'right'; // NUMERIC // .s {} $css['.s']['text-align'] = 'left'; // STRING // Calculate cell style hashes foreach ($this->phpExcel->getCellXfCollection() as $index => $style) { $css['td.style' . $index] = $this->createCSSStyle($style); $css['th.style' . $index] = $this->createCSSStyle($style); } // Fetch sheets $sheets = array(); if (is_null($this->sheetIndex)) { $sheets = $this->phpExcel->getAllSheets(); } else { $sheets[] = $this->phpExcel->getSheet($this->sheetIndex); } // Build styles per sheet foreach ($sheets as $sheet) { // Calculate hash code $sheetIndex = $sheet->getParent()->getIndex($sheet); // Build styles // Calculate column widths $sheet->calculateColumnWidths(); // col elements, initialize $highestColumnIndex = \PHPExcel\Cell::columnIndexFromString($sheet->getHighestColumn()) - 1; $column = -1; while ($column++ < $highestColumnIndex) { $this->columnWidths[$sheetIndex][$column] = 42; // approximation $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt'; } // col elements, loop through columnDimensions and set width foreach ($sheet->getColumnDimensions() as $columnDimension) { if (($width = \PHPExcel\Shared\Drawing::cellDimensionToPixels($columnDimension->getWidth(), $this->defaultFont)) >= 0) { $width = \PHPExcel\Shared\Drawing::pixelsToPoints($width); $column = \PHPExcel\Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $this->columnWidths[$sheetIndex][$column] = $width; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt'; if ($columnDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' col.col' . $column]['visibility'] = 'collapse'; $css['table.sheet' . $sheetIndex . ' col.col' . $column]['*display'] = 'none'; // target IE6+7 } } } // Default row height $rowDimension = $sheet->getDefaultRowDimension(); // table.sheetN tr { } $css['table.sheet' . $sheetIndex . ' tr'] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = Font::getDefaultRowHeightByFont($this->phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden'; } // Calculate row heights foreach ($sheet->getRowDimensions() as $rowDimension) { $row = $rowDimension->getRowIndex() - 1; // table.sheetN tr.rowYYYYYY { } $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = array(); if ($rowDimension->getRowHeight() == -1) { $pt_height = Font::getDefaultRowHeightByFont($this->phpExcel->getDefaultStyle()->getFont()); } else { $pt_height = $rowDimension->getRowHeight(); } $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt'; if ($rowDimension->getVisible() === false) { $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none'; $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden'; } } } // Cache if (is_null($this->cssStyles)) { $this->cssStyles = $css; } // Return return $css; }