PHP PHPExcel_Worksheet::getColumnDimensionByColumn方法代码示例

 protected function writeHeaderRow()
 {
     $i = 0;
     foreach ($this->columns as $column) {
         if (isset($column['header'])) {
             $this->sheet->setCellValueByColumnAndRow($i, $this->j, $column['header']);
         }
         if (isset($column['headerStyles'])) {
             $this->sheet->getStyleByColumnAndRow($i, $this->j)->applyFromArray($column['headerStyles']);
         }
         if (isset($column['width'])) {
             $this->sheet->getColumnDimensionByColumn($i)->setWidth($column['width']);
         }
         ++$i;
     }
     ++$this->j;
 }

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

 /**
  * Render the header row
  */
 protected function renderHeader()
 {
     $columnNumber = 0;
     // Headers
     if ($this->templateVariableContainer->exists('listCaptions')) {
         foreach ($this->templateVariableContainer['listCaptions'] as $columnIdentifier => $caption) {
             /* @var $caption Tx_PtExtlist_Domain_Model_List_Cell */
             $this->activeSheet->setCellValueByColumnAndRow($columnNumber, $this->rowNumber, strip_tags($caption->getValue()));
             $excelSettings = $this->getExcelSettingsByColumnIdentifier($columnIdentifier);
             /**
              * Width
              */
             if (is_array($excelSettings) && array_key_exists('width', $excelSettings)) {
                 $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setWidth($excelSettings['width']);
             } else {
                 $this->activeSheet->getColumnDimensionByColumn($columnNumber)->setAutoSize(true);
             }
             $this->doCellStyling($columnNumber, $columnIdentifier, 'header');
             $columnNumber++;
         }
     }
     $this->rowNumber++;
 }

 /**
  * Sets the width (and other settings) of one column.
  *
  * @param integer $firstcol first column on the range
  * @param integer $lastcol  last column on the range
  * @param integer $width    width to set  (null to set just format without setting the width)
  * @param mixed   $format   The optional format to apply to the columns
  * @param bool    $hidden   The optional hidden attribute
  * @param integer $level    The optional outline level (0-7)
  */
 public function set_column($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0)
 {
     if ($level < 0) {
         $level = 0;
     } else {
         if ($level > 7) {
             $level = 7;
         }
     }
     $i = $firstcol;
     while ($i <= $lastcol) {
         if (isset($width)) {
             $this->worksheet->getColumnDimensionByColumn($i)->setWidth($width);
         }
         $this->worksheet->getColumnDimensionByColumn($i)->setVisible(!$hidden);
         $this->worksheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
         $this->apply_column_format($i, $format);
         $i++;
     }
 }

 /**
  * Write VML comment to XML format
  *
  * @param   PHPExcel_Worksheet              $pWorksheet
  * @param 	PHPExcel_Shared_XMLWriter		$objWriter 			XML Writer
  * @param	string							$pCellReference		Cell reference
  * @param 	PHPExcel_Comment				$pComment			Comment
  * @throws 	PHPExcel_Writer_Exception
  */
 public function _writeVMLComment(PHPExcel_Worksheet $pWorksheet = null, PHPExcel_Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', PHPExcel_Comment $pComment = null)
 {
     // Metadata
     list($column, $row) = PHPExcel_Cell::coordinateFromString($pCellReference);
     $column = PHPExcel_Cell::columnIndexFromString($column);
     $id = 1024 + $column + $row;
     $id = substr($id, 0, 4);
     $cssWidth = $pComment->getWidth();
     $cssHeight = $pComment->getHeight();
     // v:shape
     $objWriter->startElement('v:shape');
     $objWriter->writeAttribute('id', '_x0000_s' . $id);
     $objWriter->writeAttribute('type', '#_x0000_t202');
     $objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $cssWidth . ';height:' . $cssHeight . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden'));
     $objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB());
     $objWriter->writeAttribute('o:insetmode', 'auto');
     // v:fill
     $objWriter->startElement('v:fill');
     $objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB());
     $objWriter->endElement();
     // v:shadow
     $objWriter->startElement('v:shadow');
     $objWriter->writeAttribute('on', 't');
     $objWriter->writeAttribute('color', 'black');
     $objWriter->writeAttribute('obscured', 't');
     $objWriter->endElement();
     // v:path
     $objWriter->startElement('v:path');
     $objWriter->writeAttribute('o:connecttype', 'none');
     $objWriter->endElement();
     // v:textbox
     $objWriter->startElement('v:textbox');
     $objWriter->writeAttribute('style', 'mso-direction-alt:auto');
     // div
     $objWriter->startElement('div');
     $objWriter->writeAttribute('style', 'text-align:left');
     $objWriter->endElement();
     $objWriter->endElement();
     // x:ClientData
     $objWriter->startElement('x:ClientData');
     $objWriter->writeAttribute('ObjectType', 'Note');
     // x:MoveWithCells
     $objWriter->writeElement('x:MoveWithCells', '');
     // x:SizeWithCells
     $objWriter->writeElement('x:SizeWithCells', '');
     // x:Anchor
     // anchor is a nice way to locate the comment sensibly with respect to the sheet's rows/columns, but
     // in order to do so, need to be able to convert roughly to point dimensions from the comments
     // width/height, which are optimized for css
     if (preg_match('/\\s*pt\\s*$/', $cssWidth) && preg_match('/\\s*pt\\s*$/', $cssHeight)) {
         // compute CSS height to an integer # pts
         $width = intval(preg_replace('/\\s*pt\\s*$/', '', $cssWidth));
         $height = intval(preg_replace('/\\s*pt\\s*$/', '', $cssHeight));
         // starting from the row/column this is being placed in try
         // to figure out the row column that should anchor the lower-right
         // corner of the comment
         $clearedWidth = 0;
         $clearedHeight = 0;
         $maxColumns = 2;
         $maxRows = 10;
         // loop incremenets, so decrement both to start
         $curColumn = $column - 1;
         $curRow = $row - 1;
         while ($clearedWidth < $width && $curColumn - $column < $maxColumns) {
             ++$curColumn;
             $dim = $pWorksheet->getColumnDimensionByColumn($curColumn, false);
             $clearedWidth += $dim && $dim->getWidth() > 0 ? $dim->getWidth() : 96;
         }
         while ($clearedHeight < $height && $curRow - $row < $maxRows) {
             ++$curRow;
             $dim = $pWorksheet->getRowDimension($curRow, false);
             $clearedHeight += $dim && $dim->getRowHeight() > 0 ? $dim->getRowHeight() : 14;
         }
         $colBump = 15;
         $rowBump = 10;
         $anchor = $column . ', ' . $colBump . ', ' . ($row - 1) . ', ' . $rowBump . ', ' . $curColumn . ', ' . max($clearedWidth - $width, 0) . ', ' . $curRow . ', ' . max($clearedHeight - $height, 0);
         // lower-right row offset
         $objWriter->writeElement('x:Anchor', $anchor);
     }
     // x:AutoFill
     $objWriter->writeElement('x:AutoFill', 'False');
     // x:Row
     $objWriter->writeElement('x:Row', $row - 1);
     // x:Column
     $objWriter->writeElement('x:Column', $column - 1);
     $objWriter->endElement();
     $objWriter->endElement();
 }

 /**
  * Método que gera a planilha de evolução anual por unidade e ano
  */
 public function evolucaoAnual()
 {
     //Inicializando os filtros usados na página
     $this->filtro->initGets(array("unidade", "ano"));
     //Inicializando os objetos básicos
     $sqlUnidade = new SqlUnidade();
     $sqlSerie = new SqlSerie();
     $sqlEnsino = new SqlEnsino();
     $sqlAfa = new SqlAfa();
     //Contadores em geral
     $linha = 0;
     //Define a linha inicial da impressão da planilha
     $coluna = 0;
     //Define a coluna inicial da planilha
     $colunaInicial = $coluna;
     $r = 1;
     //Contador "R" das planilhas
     //Objetos e variaveis básicas da planilha
     $conceitos = array("D" => "Deficiente", "I" => "Insuficiente", "R" => "Regular", "B" => "Bom", "L" => "Louvor");
     $bimestres = array("1" => "1º Bimestre", "2" => "2º Bimestre", "3" => "3º Bimestre", "4" => "4º Bimestre");
     $bimRomanos = array("1" => "I", "2" => "II", "3" => "III", "4" => "IV");
     //Fazendo as consultas iniciais
     $ensinos = $sqlEnsino->listarTodos($this->filtro);
     $avaliacoea = $sqlAfa;
     $avaliacoes = $afaObj->listarAvaliacoes($unidade, $ano);
     die("ok");
     //Iniciando a geração da planilha
     while ($ensino = $ensinos->fetchObject()) {
         $avaliacoes = $afaObj->buscarAvaliacoes($ensino->pk_ensino, $unidade, $ano);
         //Teste para somente exibir as plainlhas de ensinos que contenham registros
         if (count($avaliacoes)) {
             //Ajustando a primeira coluna
             $this->workSheet->getColumnDimensionByColumn($coluna)->setWidth(33);
             //Trecho referente a primeira planilha
             //PREENCHENDO A PRIMEIRA PLANILHA
             //Definindo o título da planiplha
             $tituloPlanilhas = "TABELA R" . $r++ . " – DISTRIBUIÇÃO PERCENTUAL DOS CONCEITOS " . "FORMATIVOS DOS ALUNOS DO " . strtoupper($ensino->nome_ensino) . " EM CADA BIMESTRE";
             $this->workSheet->setCellValueByColumnAndRow($coluna, ++$linha, $tituloPlanilhas)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FFF2CC")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linha)->getFont()->setBold(TRUE);
             //Tornando a font bolder
             //
             $linhaReferencia = $linha;
             $linha++;
             $coluna++;
             //Criando o cabeçalho da primeira planilha
             foreach ($bimestres as $bimestre) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $bimestre)->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna + 4, $linha);
                 //Mesclando as colunas
                 $coluna += 5;
             }
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             // Adicionando as colunas indicadoras de níveis
             $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, "Série")->getStyleByColumnAndRow($coluna, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
             $this->workSheet->mergeCellsByColumnAndRow($coluna, $linha, $coluna++, ++$linha);
             //Mesclando as colunas
             //
             foreach (range(0, 3) as $indice) {
                 foreach (array_keys($conceitos) as $letra) {
                     $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $letra)->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 }
             }
             //Mesclando o título da planilha
             $this->workSheet->mergeCellsByColumnAndRow($colunaInicial, $linhaReferencia, --$coluna, $linhaReferencia);
             $this->workSheet->getStyleByColumnAndRow($coluna, $linhaReferencia)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             //
             $linha++;
             //Definindo as variaveis para calculo de médias
             $mdD1B = $mdI1B = $mdR1B = $mdB1B = $mdL1B = array();
             $mdD2B = $mdI2B = $mdR2B = $mdB2B = $mdL2B = array();
             $mdD3B = $mdI3B = $mdR3B = $mdB3B = $mdL3B = array();
             $mdD4B = $mdI4B = $mdR4B = $mdB4B = $mdL4B = array();
             $this->workSheet->getStyleByColumnAndRow(--$coluna, $linha)->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))));
             $coluna = $colunaInicial;
             //Resetando o contador de colunas
             //adicionando as séries da primeira coluna
             foreach ($avaliacoes as $avaliacao) {
                 $this->workSheet->setCellValueByColumnAndRow($coluna, $linha, $avaliacao["serie_nome"])->getStyleByColumnAndRow($coluna++, $linha)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => "FCE4D6")), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')))))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                 //Separando os dados
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$qt' . $conceito . $bimestre . 'B = (float) ' . '(isset($avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . strtolower($conceito) . '-' . $bimestre . 'bi"] : 0.0);';
                         eval($comando);
                     }
                     $comando = '$qt' . $bimestre . 'B = (float) (isset($avaliacao["qt-' . $bimestre . 'bi"]) ' . '? $avaliacao["qt-' . $bimestre . 'bi"] : 0.0);';
                     eval($comando);
                 }
                 //Definindo os percentudais
                 foreach (array_keys($bimestres) as $bimestre) {
                     foreach (array_keys($conceitos) as $conceito) {
                         $comando = '$per' . $conceito . $bimestre . 'B = ' . 'Matematica::percentualDe($qt' . $conceito . $bimestre . 'B, $qt' . $bimestre . 'B);';
                         eval($comando);
                         $comando = 'array_push($md' . $conceito . $bimestre . 'B, $per' . $conceito . $bimestre . 'B);';
                         eval($comando);
                     }
//.........这里部分代码省略.........

                     $newstr = '';
                     for ($j = 0; $j < strlen($retstr); ++$j) {
                         $newstr .= $retstr[$j] . chr(0);
                     }
                     $retstr = $newstr;
                     $len = min($charsLeft * 2, $limitpos - $pos);
                     $retstr .= substr($recordData, $pos, $len);
                     $charsLeft -= $len / 2;
                     $isCompressed = false;
                 }
                 $pos += $len;
             }
         }
         // convert to UTF-8
         $retstr = self::_encodeUTF16($retstr, $isCompressed);
         // read additional Rich-Text information, if any
         $fmtRuns = array();
         if ($hasRichText) {
             // list of formatting runs
             for ($j = 0; $j < $formattingRuns; ++$j) {
                 // first formatted character; zero-based
                 $charPos = self::_GetInt2d($recordData, $pos + $j * 4);
                 // index to font record
                 $fontIndex = self::_GetInt2d($recordData, $pos + 2 + $j * 4);
                 $fmtRuns[] = array('charPos' => $charPos, 'fontIndex' => $fontIndex);
             }
             $pos += 4 * $formattingRuns;
         }
         // read additional Asian phonetics information, if any
         if ($hasAsian) {
             // For Asian phonetic settings, we skip the extended string data
             $pos += $extendedRunLength;
         }
         // store the shared sting
         $this->_sst[] = array('value' => $retstr, 'fmtRuns' => $fmtRuns);
     }
     // _getSplicedRecordData() takes care of moving current position in data stream
 }
 /**