PHP PHPExcel_Worksheet::getColumnDimension方法代码示例

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

 protected function _signupSheet($event, $includeEndingTerms, $includeNotEndingTerms)
 {
     $sheet = new PHPExcel_Worksheet($this->_excelDoc, 'Signup Sheet for Workshop ' . $event['workshopTitle']);
     // Set up the margins so the header doesn't bleed into the page
     $sheet->getPageMargins()->setTop(1.5);
     // Make a three column page layout
     $sheet->getColumnDimension('A')->setWidth(16);
     $sheet->getColumnDimension('B')->setWidth(16);
     $sheet->getColumnDimension('C')->setWidth(45);
     $config = new Zend_Config_Xml(APPLICATION_PATH . '/configs/config.xml', 'production');
     $date = new DateTime($event['date']);
     $startTime = new DateTime($event['startTime']);
     $endTime = new DateTime($event['endTime']);
     // Set the header on odd pages.
     // The code formatting is off because the header doesn't ignore spaces.
     /*
      * Format:
      *        Title
      *        Room name
      *        date('D, M d, Y') (startTime('g:i A') - endTime('g:i A'))
      *        Instructors  
      * 
      */
     $sheet->getHeaderFooter()->setOddHeader('&C&B&14' . $event['workshopTitle'] . '&14&B&12 ' . chr(10) . $event['location'] . chr(10) . $date->format('l, M d, Y') . '(' . $startTime->format('g:i A') . ' - ' . $endTime->format('g:i A') . ')' . chr(10) . 'Instructor: ' . implode(',', $event['instructors']) . '&12&C');
     // Write Column Headers for the table
     $sheet->setCellValue('A1', 'First Name');
     $sheet->setCellValue('B1', 'Last Name');
     $sheet->setCellValue('C1', 'Signature');
     // reformat it a little bit in a simpler way for us to use it in our
     // spreadsheet printin' loop
     $rows = array();
     foreach ($event['attendeeList'] as $a) {
         $rows[] = array($a['firstName'], $a['lastName']);
     }
     $signin = new PHPExcel_Style();
     $signin->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
     $rowCounter = 3;
     foreach ($rows as $row) {
         $row = array_values($row);
         // put the totals in the row
         $char = self::A;
         foreach ($row as $cell) {
             $sheet->setCellValue(chr($char) . $rowCounter, $cell);
             $char++;
         }
         $rowCounter++;
     }
     $tableHeaderStyle = new PHPExcel_Style();
     $tableHeaderStyle->applyFromArray($this->_tableHeaderStyleArray);
     $tableBodyStyle = new PHPExcel_Style();
     $tableBodyStyle->applyFromArray($this->_contentStyleArray);
     $sheet->setSharedStyle($tableHeaderStyle, 'A1:C1');
     $sheet->setSharedStyle($tableBodyStyle, 'A3:B' . ($rowCounter - 1));
     $sheet->setSharedStyle($signin, 'C3:C' . ($rowCounter - 1));
     return $sheet;
 }

 private function buildHead()
 {
     $column_index = 1;
     foreach ($this->analyze->getCriteriaNames() as $criterion_name) {
         $column_name = Utility::getNameFromNumber($column_index++);
         $this->sheet->setCellValue($column_name . $this->row_index, $criterion_name);
         $this->sheet->getColumnDimension($column_name)->setWidth(30, 0);
         $this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
     }
     $this->row_index++;
 }

 private function buildHead()
 {
     $column_index = 0;
     foreach ($this->responseTableView->getHeaderData() as $header) {
         $column_name = Utility::getNameFromNumber($column_index++);
         $this->sheet->setCellValue($column_name . $this->row_index, $header);
         $this->sheet->getColumnDimension($column_name)->setWidth(30, 0);
         $this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
     }
     $this->row_index++;
 }

 /**
  * 
  */
 protected function _styleWorkSheet()
 {
     $highestColumn = $this->_mainSheet->getHighestColumn();
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
     for ($column = $this->_startCol; $column < $highestColumnIndex; $column++) {
         $this->_mainSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
     }
     $this->_mainSheet->getColumnDimension('B')->setWidth(3);
 }

 /**
  * do layouting (cell borders, font styles, background colors,
  * cell sizing, page setup)
  */
 protected function doLayouting()
 {
     $this->formatDataRowsByType();
     $this->addCellBorders();
     $this->formatHeaders();
     $this->formatSums();
     // auto-width columns:
     for ($x = 0; $x < count($this->activeColumns_arr); $x++) {
         $this->sheet->getColumnDimension(self::excelColumnAddr($x))->setAutoSize(true);
     }
     // auto-height rows:
     foreach ($this->sheet->getRowDimensions() as $rd) {
         $rd->setRowHeight(-1);
     }
     // fixed header when scrolling down in Excel:
     $this->sheet->freezePane(self::excelAddr(0, self::EXCEL_HEADER_OFFSET));
     $pageSetup = $this->sheet->getPageSetup();
     // when printing, show header on every page:
     $pageSetup->setRowsToRepeatAtTopByStartAndEnd(1, self::EXCEL_HEADER_OFFSET);
     // increase chances that page can be printed out without seperating a row's data over more than one page:
     $pageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
 }

 /**
  * Set dimensions of row or column
  *
  * @param \PHPExcel_Worksheet $excelWorksheet
  * @param $excelElement
  * @param $documentElement
  */
 protected function setDimensions(\PHPExcel_Worksheet $excelWorksheet, $excelElement, $documentElement)
 {
     $dimensions = $this->getDimensionsArray($documentElement);
     if (isset($dimensions['column']) && $excelElement instanceof \PHPExcel_Cell) {
         foreach ($dimensions['column'] as $columnKey => $columnValue) {
             $method = 'set' . ucfirst($columnKey);
             $excelWorksheet->getColumnDimension($excelElement->getColumn())->{$method}($columnValue);
         }
     }
     if (isset($dimensions['row'])) {
         foreach ($dimensions['row'] as $rowKey => $rowValue) {
             $method = 'set' . ucfirst($rowKey);
             if ($excelElement instanceof \PHPExcel_Cell) {
                 $excelWorksheet->getRowDimension($excelElement->getRow())->{$method}($rowValue);
             } elseif ($excelElement instanceof \PHPExcel_Worksheet_Row) {
                 $excelWorksheet->getRowDimension($excelElement->getRowIndex())->{$method}($rowValue);
             }
         }
     }
 }

 private function addTransactionPage(PHPExcel_Worksheet $activeSheet)
 {
     $activeSheet->setTitle('Транзакции')->setCellValue('A12', 'Статус')->setCellValue('B12', 'Тип')->setCellValue('C12', 'ID')->setCellValue('D12', 'Дата')->setCellValue('E12', 'IP')->setCellValue('F12', 'ГЕО')->setCellValue('G12', 'URL цели')->setCellValue('H12', 'Источник')->setCellValue('I12', 'Материал')->setCellValue('J12', 'Выплата')->setCellValue('K12', 'Вознаграждение')->setCellValue('L12', 'Зароботок')->setCellValue('M12', 'Цель');
     $row = 13;
     $availableStatuses = ActionsLog::getAvailableStatuses();
     foreach ($this->transactionData['rows'] as $tr) {
         $activeSheet->setCellValue('A' . $row, $availableStatuses[$tr['status']])->setCellValue('B' . $row, $tr['source_type_name'])->setCellValue('C' . $row, $tr['id'])->setCellValue('D' . $row, Yii::app()->dateFormatter->formatDateTime($tr['date']))->setCellValue('E' . $row, $tr['ip'])->setCellValue('F' . $row, $tr['geo'])->setCellValue('G' . $row, $tr['target_url_decoded'])->setCellValue('H' . $row, $tr['source_name'])->setCellValue('I' . $row, $tr['target_name'])->setCellValue('J' . $row, $tr['payment'])->setCellValue('K' . $row, $tr['reward'])->setCellValue('L' . $row, $tr['debit'])->setCellValue('M' . $row, $tr['action_name']);
         $row++;
     }
     $activeSheet->setCellValue('J' . $row, $this->transactionData['total']['payment'])->setCellValue('K' . $row, $this->transactionData['total']['reward'])->setCellValue('L' . $row, $this->transactionData['total']['debit']);
     $activeSheet->getColumnDimension('A')->setWidth(16.3 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(16.43 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(5 * 1.05);
     $activeSheet->getColumnDimension('D')->setWidth(17.86 * 1.05);
     $activeSheet->getColumnDimension('E')->setWidth(14.14 * 1.05);
     $activeSheet->getColumnDimension('F')->setWidth(34 * 1.05);
     $activeSheet->getColumnDimension('G')->setWidth(31 * 1.05);
     $activeSheet->getColumnDimension('H')->setWidth(30.86 * 1.05);
     $activeSheet->getColumnDimension('I')->setWidth(19.14 * 1.05);
     $activeSheet->getColumnDimension('J')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('K')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('L')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('M')->setWidth(30.7 * 1.05);
     $activeSheet->getStyle('A12:M' . $row)->getAlignment()->setWrapText(true);
     $this->formatTable($activeSheet, 'A', '12', 'M', $row, array('formatTotal' => true, 'innerRowHeight' => -1, 'headerRowHeight' => 27));
     $this->addLogo($activeSheet);
     $this->setHeader($activeSheet, $this->getHeaders());
     $this->setPageFit($activeSheet, self::FIT_TO_WIDTH, PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
 }

/**
 * Format the columns of the worksheet according to the attribute definition
 *
 * @param PHPExcel_Worksheet $worksheet
 * @param array $columns
 */
function formatWSColumns($worksheet, $columns)
{
    $fmt_code = array('T' => PHPExcel_Style_NumberFormat::FORMAT_TEXT, 'D' => 'yyyy-mm-dd', 'N' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER, 'F' => '0.0', 'P' => '0.0%', '' => null);
    $highestRow = $worksheet->getHighestRow();
    $colFmt = $columns[0][1];
    $colidx = 0;
    $start = 0;
    $idx = 0;
    foreach ($columns as $col) {
        if ($col[2]) {
            // set the column width if a specific value is defined
            $colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx);
            $worksheet->getColumnDimension($colLetter)->setWidth($col[2]);
        }
        if ($col[3] and $col[3] == 'wrap') {
            // set text wrapping attribute if requested
            $colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx);
            $range = $colLetter . '2:' . $colLetter . $highestRow;
            $worksheet->getStyle($range)->getAlignment()->setWrapText(true);
        }
        if ($colFmt != $col[1]) {
            // assign the format to the range if a format is explicitly required
            $start_col = PHPExcel_Cell::stringFromColumnIndex($start);
            $end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1);
            $range = $start_col . '2:' . $end_col . $highestRow;
            $colFmtSpec = $fmt_code[$colFmt];
            if ($colFmtSpec) {
                $worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec);
            }
            $colFmt = $col[1];
            $start = $start + $idx;
            $idx = 1;
        } else {
            // still in the same format range
            $idx += 1;
        }
        $colidx++;
    }
    $start_col = PHPExcel_Cell::stringFromColumnIndex($start);
    $end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1);
    $range = $start_col . '2:' . $end_col . $highestRow;
    $colFmtSpec = $fmt_code[$colFmt];
    if ($colFmtSpec) {
        // assign up to the end the format if explicitly required
        $worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec);
    }
}

 /**
  * Inserts data into worksheet and returns it
  * 
  * @return PHPExcel_Worksheet
  */
 public function render()
 {
     // Set worksheet header
     $this->_set_row(1, $this->columns, TRUE);
     //set header style
     $obj_style = new PHPExcel_Style();
     $style = Kohana::$config->load('phpexcel.header');
     $obj_style->applyFromArray($style);
     $column_dim = PHPExcel_Cell::stringFromColumnIndex(count($this->columns) - 1);
     $this->_worksheet->setSharedStyle($obj_style, 'A1:' . $column_dim . '1');
     // Set data
     $rows = 0;
     foreach ($this->data as $row => $data) {
         $this->_set_row($row + 2, $data);
         $rows++;
     }
     // Set column styles and width
     $column = 0;
     foreach (array_keys($this->columns) as $key) {
         $column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
         $format = Arr::get($this->formats, $key);
         if ($format !== NULL) {
             $this->_worksheet->getStyle($column_dim . 2 . ':' . $column_dim . (2 + $rows))->getNumberFormat()->setFormatCode($format);
         }
         if ($this->auto_size === TRUE) {
             $this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
         }
         $column++;
     }
     return $this->_worksheet;
 }

 public function export_Uncomplete($file_name, $test = false)
 {
     error_reporting(E_ALL);
     ini_set("display_errors", 1);
     ini_set('max_execution_time', 60);
     ini_set('memory_limit', '256M');
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
     $cacheSettings = array('memoryCacheSize ' => '256MB');
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $this->excel = new PHPExcel();
     $registrant_data = $this->getArrayData();
     $worksheet = new PHPExcel_Worksheet();
     $worksheet->setTitle('Data');
     $worksheet->setCellValue('A1', 'Nomor Pendaftaran');
     $worksheet->getColumnDimension('A')->setAutoSize(true);
     $worksheet->setCellValue('B1', 'Nama');
     $worksheet->getColumnDimension('B')->setAutoSize(true);
     $worksheet->setCellValue('C1', 'I/A');
     $worksheet->getColumnDimension('C')->setAutoSize(true);
     $worksheet->setCellValue('D1', 'Asal Sekolah');
     $worksheet->getColumnDimension('D')->setAutoSize(true);
     $worksheet->setCellValue('E1', 'Contact');
     $worksheet->getColumnDimension('E')->setAutoSize(true);
     $worksheet->setCellValue('F1', 'Status Kekurangan');
     $worksheet->getColumnDimension('F')->setAutoSize(true);
     $row_iterate = 2;
     foreach ($registrant_data as $registrant) {
         if (!$registrant['completed']) {
             $row = [];
             $row[] = $registrant['id'];
             $row[] = strtoupper($registrant['name']);
             $row[] = $registrant['gender'] == 'L' ? 'Ikhwan' : 'Akhwat';
             $row[] = strtoupper($registrant['previousSchool']);
             $row[] = $registrant['cp'];
             $row[] = $registrant['status'];
             $worksheet->fromArray($row, '', 'A' . $row_iterate);
             $row_iterate++;
         }
     }
     $this->excel->removeSheetByIndex(0);
     $this->excel->addSheet($worksheet);
     if ($test) {
         return true;
     } else {
         header('Content-Type: application/vnd.ms-excel');
         header('Content-Disposition: attachment;filename="' . $file_name . '.xls"');
         header('Cache-Control: max-age=0');
         $objWriter = new PHPExcel_Writer_Excel5($this->excel);
         $objWriter->save('php://output');
         exit;
     }
 }

function setExcelHeader2(PHPExcel_Worksheet $sheet)
{
    //$sheet->setCellValue('A1', 'Site Name');
    $sheet->setCellValue('A1', '订单号');
    $sheet->setCellValue('B1', '型号');
    $sheet->setCellValue('C1', '订单价格');
    $sheet->setCellValue('D1', '收货人姓名');
    //$sheet->setCellValue('D1', 'MODEL');
    $sheet->setCellValue('E1', '送货地址');
    $sheet->setCellValue('F1', '邮编');
    $sheet->setCellValue('G1', '客户电话');
    $sheet->setCellValue('H1', '电子邮件');
    $sheet->setCellValue('I1', '备注');
    //$sheet->getColumnDimension('A')->setWidth(30);
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(10);
    $sheet->getColumnDimension('D')->setWidth(30);
    //$sheet->getColumnDimension('D')->setWidth(20);
    $sheet->getColumnDimension('E')->setWidth(40);
    $sheet->getColumnDimension('F')->setWidth(10);
    $sheet->getColumnDimension('G')->setWidth(20);
    $sheet->getColumnDimension('H')->setWidth(20);
    $sheet->getColumnDimension('I')->setWidth(40);
}

 /**
  * Inserts data into worksheet and returns it
  * 
  * @return PHPExcel_Worksheet
  */
 public function render()
 {
     // Set worksheet header
     if ($this->include_names) {
         $this->_set_row(1, $this->columns, TRUE);
         $offset = 2;
     } else {
         $offset = 1;
     }
     // Set data
     $rows = 0;
     foreach ($this->data as $row => $data) {
         $this->_set_row($row + $offset, $data);
         $rows++;
     }
     // Set column styles and width
     $column = 0;
     foreach ($this->columns as $key => $name) {
         $column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
         $format = Arr::get($this->formats, $key);
         if ($format !== NULL) {
             $this->_worksheet->getStyle($column_dim . $offset . ':' . $column_dim . ($offset + $rows))->getNumberFormat()->setFormatCode($format);
         }
         if ($this->auto_size === TRUE) {
             $this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
         }
         $column++;
     }
     return $this->_worksheet;
 }