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