PHP PHPExcel_Worksheet::calculateColumnWidths方法代码示例

 /**
  *
  * @param string $startColName
  * @param string $endColName
  * @return Formatter
  */
 public function autosizeColumns($startColName = null, $endColName = null)
 {
     if (is_null($startColName) || is_null($endColName)) {
         list($startCoordinates, $endCoordinates) = explode(':', $this->sheet->calculateWorksheetDataDimension());
         $startColName = is_null($startColName) ? preg_replace('/\\d*/', '', $startCoordinates) : $startColName;
         $endColName = is_null($endColName) ? preg_replace('/\\d*/', '', $endCoordinates) : $endColName;
     }
     $startNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($startColName);
     // A = 1
     $endNum = PhpOffice_PHPExcel_Cell::columnIndexFromString($endColName);
     for ($i = $startNum - 1; $i < $endNum; $i++) {
         $this->sheet->getColumnDimensionByColumn($i)->setAutoSize(true);
         // A = 0
     }
     $this->sheet->calculateColumnWidths();
     return $this;
 }

 /**
  * Write Cols
  *
  * @param	PHPExcel_Shared_XMLWriter			$objWriter		XML Writer
  * @param	PHPExcel_Worksheet					$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
 {
     // cols
     $objWriter->startElement('cols');
     // Check if there is at least one column dimension specified. If not, create one.
     if (count($pSheet->getColumnDimensions()) == 0) {
         $pSheet->getColumnDimension('A')->setWidth(10);
     }
     $pSheet->calculateColumnWidths();
     // Loop trough column dimensions
     foreach ($pSheet->getColumnDimensions() as $colDimension) {
         // col
         $objWriter->startElement('col');
         $objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
         $objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
         if ($colDimension->getWidth() < 0) {
             // No width set, apply default of 10
             $objWriter->writeAttribute('width', '10');
         } else {
             // Width set
             $objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth()));
         }
         // Column visibility
         if ($colDimension->getVisible() == false) {
             $objWriter->writeAttribute('hidden', 'true');
         }
         // Auto size?
         if ($colDimension->getAutoSize()) {
             $objWriter->writeAttribute('bestFit', 'true');
         } else {
             $objWriter->writeAttribute('customWidth', 'true');
         }
         // Collapsed
         if ($colDimension->getCollapsed() == true) {
             $objWriter->writeAttribute('collapsed', 'true');
         }
         // Outline level
         if ($colDimension->getOutlineLevel() > 0) {
             $objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
         }
         $objWriter->endElement();
     }
     $objWriter->endElement();
 }

 /**
  * Write Cols
  *
  * @param	PHPExcel_Shared_XMLWriter			$objWriter		XML Writer
  * @param	PHPExcel_Worksheet					$pSheet			Worksheet
  * @throws	Exception
  */
 private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_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', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
             $objWriter->writeAttribute('max', PHPExcel_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', PHPExcel_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();
     }
 }

 /**
  * 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 styles to file
  * 
  * @param	mixed				$pFileHandle	PHP filehandle
  * @param	PHPExcel_Worksheet 	$pSheet			PHPExcel_Worksheet
  * @throws	Exception
  */
 private function _writeStyles($pFileHandle = null, PHPExcel_Worksheet $pSheet)
 {
     if (!is_null($pFileHandle)) {
         // Construct HTML
         $html = '';
         // Start styles
         $html .= '    <style>' . "\r\n";
         $html .= '    <!--' . "\r\n";
         $html .= '      html {' . "\r\n";
         $html .= '        font-family: Calibri, Arial, Helvetica, Sans Serif;' . "\r\n";
         $html .= '        font-size: 10pt;' . "\r\n";
         $html .= '        background-color: white;' . "\r\n";
         $html .= '      }' . "\r\n";
         $html .= '      table.sheet, table.sheet td {' . "\r\n";
         if ($pSheet->getShowGridlines()) {
             $html .= '        border: 1px dotted black;' . "\r\n";
         }
         $html .= '      }' . "\r\n";
         // Calculate column widths
         $pSheet->calculateColumnWidths();
         foreach ($pSheet->getColumnDimensions() as $columnDimension) {
             $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
             $html .= '      td.column' . $column . ' {' . "\r\n";
             $html .= '        width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;' . "\r\n";
             if ($columnDimension->getVisible() === false) {
                 $html .= '        display: none;' . "\r\n";
                 $html .= '        visibility: hidden;' . "\r\n";
             }
             $html .= '      }' . "\r\n";
         }
         // Calculate row heights
         foreach ($pSheet->getRowDimensions() as $rowDimension) {
             $html .= '      tr.row' . ($rowDimension->getRowIndex() - 1) . ' {' . "\r\n";
             // height is disproportionately large
             $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
             $html .= '        height: ' . $px_height . 'px;' . "\r\n";
             if ($rowDimension->getVisible() === false) {
                 $html .= '        display: none;' . "\r\n";
                 $html .= '        visibility: hidden;' . "\r\n";
             }
             $html .= '      }' . "\r\n";
         }
         // Calculate cell style hashes
         $cellStyleHashes = new PHPExcel_HashTable();
         $cellStyleHashes->addFromSource($pSheet->getStyles());
         for ($i = 0; $i < $cellStyleHashes->count(); $i++) {
             $html .= $this->_createCSSStyle($cellStyleHashes->getByIndex($i));
         }
         // End styles
         $html .= '    -->' . "\r\n";
         $html .= '    </style>' . "\r\n";
         // Write to file
         fwrite($pFileHandle, $html);
     } else {
         throw new Exception("Invalid parameters passed.");
     }
 }

 /**
  * 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 DEFCOLWIDTH record
     $this->_storeDefcol();
     // Calculate column widths
     $this->_phpSheet->calculateColumnWidths();
     // Column dimensions
     foreach ($this->_phpSheet->getColumnDimensions() as $columnDimension) {
         $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
         if ($column < 256) {
             if ($columnDimension->getWidth() >= 0) {
                 $width = $columnDimension->getWidth();
             } else {
                 if ($this->_phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
                     $width = $this->_phpSheet->getDefaultColumnDimension()->getWidth();
                 } else {
                     $width = 8;
                 }
             }
             $this->_setColumn($column, $column, $width, null, $columnDimension->getVisible() ? '0' : '1', $columnDimension->getOutlineLevel());
         }
     }
     // Write the COLINFO records if they exist
     if (!empty($this->_colinfo)) {
         $colcount = count($this->_colinfo);
         for ($i = 0; $i < $colcount; ++$i) {
             $this->_storeColinfo($this->_colinfo[$i]);
         }
     }
     // Write EXTERNCOUNT of external references
     if ($this->_BIFF_version == 0x500) {
         $this->_storeExterncount($num_sheets);
     }
     // Write EXTERNSHEET references
     if ($this->_BIFF_version == 0x500) {
         for ($i = 0; $i < $num_sheets; ++$i) {
             $this->_storeExternsheet($this->_phpSheet->getParent()->getSheet($i)->getTitle());
         }
     }
     // Write PRINTHEADERS
     $this->_storePrintHeaders();
     // Write PRINTGRIDLINES
     $this->_storePrintGridlines();
     // Write GUTS
     $this->_storeGuts();
     // Write GRIDSET
     $this->_storeGridset();
     // Write DEFAULTROWHEIGHT
     if ($this->_BIFF_version == 0x600) {
         $this->_storeDefaultRowHeight();
     }
     // Write WSBOOL
     $this->_storeWsbool();
     // Write horizontal and vertical page breaks
     $this->_storeBreaks();
     // Write page header
     $this->_storeHeader();
     // Write page footer
     $this->_storeFooter();
     // Write page horizontal centering
     $this->_storeHcenter();
     // Write page vertical centering
     $this->_storeVcenter();
     // Write left margin
     $this->_storeMarginLeft();
     // Write right margin
     $this->_storeMarginRight();
     // Write top margin
     $this->_storeMarginTop();
     /* FIXME: margins are actually appended */
     // Write bottom margin
     $this->_storeMarginBottom();
     // Write page setup
     $this->_storeSetup();
     // Write sheet protection
     $this->_storeProtect();
     // Write sheet password
     $this->_storePassword();
     // Write sheet dimensions
     $this->_storeDimensions();
     // Write Cells
     $aStyles = $this->_phpSheet->getStyles();
     $emptyStyle = $this->_phpSheet->getDefaultStyle();
     foreach ($this->_phpSheet->getCellCollection() as $cell) {
         $row = $cell->getRow() - 1;
         $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
         // Don't break Excel!
         if ($row + 1 > 65536 or $column + 1 > 256) {
             break;
         }
//.........这里部分代码省略.........