/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) { // Get a copy of the cell collection /*$aTemp = $pSheet->getCellCollection(); $aCellCollection = array(); foreach ($aTemp as $key => $value) { $aCellCollection[$key] = clone $value; }*/ $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore); // Clear cells if we are removing columns or rows $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); // 1. Clear column strips if we are removing columns if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; ++$i) { for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($j) . $i; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // 2. Clear row strips if we are removing rows if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { for ($j = $beforeRow + $pNumRows; $j <= $beforeRow - 1; ++$j) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . $j; $pSheet->removeConditionalStyles($coordinate); if ($pSheet->cellExists($coordinate)) { $pSheet->getCell($coordinate)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_NULL); $pSheet->getCell($coordinate)->setXfIndex(0); } } } } // Loop through cells, bottom-up, and change cell coordinates while ($cell = $pNumCols < 0 || $pNumRows < 0 ? array_shift($aCellCollection) : array_pop($aCellCollection)) { // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols) . ($cell->getRow() + $pNumRows); // Should the cell be updated? Move value and cellXf index from one cell to another. if (PHPExcel_Cell::columnIndexFromString($cell->getColumn()) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && $cell->getRow() >= $beforeRow) { // Update cell styles $pSheet->getCell($newCoordinates)->setXfIndex($cell->getXfIndex()); $cell->setXfIndex(0); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->setCellValue($newCoordinates, $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows)); } else { // Formula should not be adjusted $pSheet->setCellValue($newCoordinates, $cell->getValue()); } // Clear the original cell $pSheet->setCellValue($cell->getCoordinate(), ''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2) . $i; if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $j <= PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols; ++$j) { $pSheet->getCellByColumnAndRow($j, $i)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($j) . $i, $cloned); } } } } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; ++$i) { // Style $coordinate = PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1); if ($pSheet->cellExists($coordinate)) { $xfIndex = $pSheet->getCell($coordinate)->getXfIndex(); $conditionalStyles = $pSheet->conditionalStylesExists($coordinate) ? $pSheet->getConditionalStyles($coordinate) : false; for ($j = $beforeRow; $j <= $beforeRow - 1 + $pNumRows; ++$j) { $pSheet->getCell(PHPExcel_Cell::stringFromColumnIndex($i) . $j)->setXfIndex($xfIndex); if ($conditionalStyles) { $cloned = array(); foreach ($conditionalStyles as $conditionalStyle) { $cloned[] = clone $conditionalStyle; } $pSheet->setConditionalStyles(PHPExcel_Cell::stringFromColumnIndex($i) . $j, $cloned); } } } } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); if (count($aColumnDimensions) > 0) { foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); } // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); if (count($aRowDimensions) > 0) { foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; ++$i) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak($newReference, $value); $pSheet->setBreak($key, PHPExcel_Worksheet::BREAK_NONE); } } // Update worksheet: hyperlinks $aHyperlinkCollection = array_reverse($pSheet->getHyperlinkCollection(), true); foreach ($aHyperlinkCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setHyperlink($newReference, $value); $pSheet->setHyperlink($key, null); } } // Update worksheet: data validations $aDataValidationCollection = array_reverse($pSheet->getDataValidationCollection(), true); foreach ($aDataValidationCollection as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setDataValidation($newReference, $value); $pSheet->setDataValidation($key, null); } } // Update worksheet: merge cells $aMergeCells = $pSheet->getMergeCells(); $aNewMergeCells = array(); // the new array of all merge cells foreach ($aMergeCells as $key => &$value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); $aNewMergeCells[$newReference] = $newReference; } $pSheet->setMergeCells($aNewMergeCells); // replace the merge cells array // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells($newReference, $value, true); $pSheet->unprotectCells($key); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter($this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->freezePane($this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows)); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea($this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows)); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange($this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows)); } } } // Garbage collect $pSheet->garbageCollect(); }
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); }
/** * Добавляет в таблицу сводные данные по отчету * * @param PHPExcel_Worksheet $activeSheet * @param array $reportData * @param $rowc */ protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc) { $activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('E' . $rowc, $reportData['total']['debit'])->setCellValue('F' . $rowc, $reportData['total']['sum'])->setCellValue('H' . $rowc, $reportData['total']['sum_with_vat']); $activeSheet->getStyle('A' . $rowc . ':K' . $rowc)->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'dbe5f1')), 'font' => array('bold' => true, 'color' => array('rgb' => '000000')))); $activeSheet->setCellValue('A' . ($rowc + 2), 'Не оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 2), $reportData['total']['not_paid'])->setCellValue('A' . ($rowc + 3), 'Оплачено (без учета ндс):')->setCellValue('E' . ($rowc + 3), $reportData['total']['paid']); $activeSheet->getStyle('A' . ($rowc + 2) . ':E' . ($rowc + 2))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'f2dddc'))); $activeSheet->getStyle('A' . ($rowc + 3) . ':E' . ($rowc + 3))->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'eaf1dd'))); }
private function buildFooter() { $column_index = 0; foreach ($this->responseTableView->getFooterData() as $footer) { $column_name = Utility::getNameFromNumber($column_index++); $this->sheet->setCellValue($column_name . $this->row_index, $footer); $this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true); } }
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; }
public function render($data) { $column = 'A'; foreach ($data as $value) { $cellCoordinates = $column++ . $this->offset; $this->sheet->setCellValue($cellCoordinates, $value); } $this->offset++; return ''; }
/** * 行を完全コピーする * * http://blog.kotemaru.org/old/2012/04/06.html より * @param PHPExcel_Worksheet $sheet * @param int $srcRow * @param int $dstRow * @param int $height * @param int $width * @throws PHPExcel_Exception */ function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width) { for ($row = 0; $row < $height; $row++) { // セルの書式と値の複製 for ($col = 0; $col < $width; $col++) { $cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row); $style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row); $dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row); $sheet->setCellValue($dstCell, $cell->getValue()); $sheet->duplicateStyle($style, $dstCell); } // 行の高さ複製。 $h = $sheet->getRowDimension($srcRow + $row)->getRowHeight(); $sheet->getRowDimension($dstRow + $row)->setRowHeight($h); } // セル結合の複製 // - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。 // - $merge="AB16:AC19" foreach ($sheet->getMergeCells() as $mergeCell) { $mc = explode(":", $mergeCell); $col_s = preg_replace("/[0-9]*/", "", $mc[0]); $col_e = preg_replace("/[0-9]*/", "", $mc[1]); $row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow; $row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow; // 複製先の行範囲なら。 if (0 <= $row_s && $row_s < $height) { $merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e); $sheet->mergeCells($merge); } } }
/** * @param \PHPExcel_Worksheet $sheet */ public function exportAttributeNames($sheet) { $row = 1; foreach ($this->_standardAttributes as $name => $standardAttribute) { $sheet->setCellValue($standardAttribute->column . $row, $name); } }
/** * @param \PHPExcel_Worksheet $sheet * @param integer $offset * @param array $values */ public function addLine($sheet, $offset, $values) { $column = 'A'; foreach ($values as $value) { $cellCoordinates = $column++ . (string) $offset; $sheet->setCellValue($cellCoordinates, $value); } }
protected function writeData(\PHPExcel_Worksheet $worksheet) { foreach ($this->activeDataProvider->getModels() as $row => $model) { foreach ($this->columns as $col => $column) { $columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2); switch ($column->format) { case Column::FormatRaw: $worksheet->setCellValue($columnIndex, $column->getValue($model)); break; case Column::FormatUri: $worksheet->setCellValue($columnIndex, $column->getValue($model)); $worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"'); break; } } } }
private function buildBody() { $column_index = 0; $row_index = $this->row_index; $column_name = Utility::getNameFromNumber($column_index); foreach ($this->analyze->getAlternativesNames() as $alternative_name) { $this->sheet->setCellValue($column_name . $row_index, $alternative_name); $row_index++; } $column_index = 1; $column_name = Utility::getNameFromNumber($column_index); foreach ($this->analyze->getData() as $alternatives) { $row_index = $this->row_index; foreach ($alternatives as $value) { $this->sheet->setCellValue($column_name . $row_index++, $value); } $column_name = Utility::getNameFromNumber(++$column_index); } }
/** * @param Worksheet $worksheet * @param array $style_h2 * @return Worksheet * @throws \PHPExcel_Exception */ public function exportExcel(Worksheet $worksheet, array $style_h2) { $last_row = $worksheet->getHighestDataRow(); $last_row += 2; $max_col = $worksheet->getHighestDataColumn(); $worksheet->mergeCells("A{$last_row}:{$max_col}{$last_row}"); $worksheet->setCellValue("A{$last_row}", utf8_encode($this->getTitulo())); $worksheet->getStyle("A{$last_row}:{$max_col}{$last_row}")->applyFromArray($style_h2); $worksheet->getRowDimension($last_row)->setRowHeight(20); $last_row += 2; $worksheet->setCellValue("C{$last_row}", utf8_encode('Opción')); $worksheet->setCellValue("D{$last_row}", 'Votos'); $first_row = $last_row; $last_row += 1; foreach ($this->getDatos() as $key => $dato) { $worksheet->setCellValue("B{$last_row}", $key + 1); $worksheet->setCellValue("C{$last_row}", utf8_encode($dato[0])); if (mb_strlen($dato[0]) > 45) { $worksheet->getRowDimension($last_row)->setRowHeight(27); } $worksheet->setCellValue("D{$last_row}", $dato[1]); $last_row++; } $last_row -= 1; $worksheet->getStyle("C{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla('center', true)); $first_row++; $worksheet->getStyle("B{$first_row}:D{$last_row}")->applyFromArray($this->getEstiloTabla()); $first_row -= 1; $top_chart = $first_row - 1; $bottom_chart = $first_row + 12; $chart1 = $this->getChart($first_row, $last_row, $top_chart, $bottom_chart); $worksheet->addChart($chart1); $worksheet->setCellValue("A{$bottom_chart}", ""); return $worksheet; }
/** * write sums for active columns * only if configured in self::$COLUMN_CONFIG */ protected function writeSums() { for ($x = count($this->activeColumns_arr) - 1; $x >= 0; $x--) { $columnName = $this->activeColumns_arr[$x]; $fieldConf = array_key_exists($columnName, self::$COLUMN_CONFIG) ? self::$COLUMN_CONFIG[$columnName] : array(); $doSum = array_key_exists('sum', $fieldConf) ? $fieldConf['sum'] : false; $curAddr = self::excelAddr($x, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr)); if (!$doSum) { if ($x == 0 && $this->anySumsWereAdded) { $this->sheet->setCellValue($curAddr, $this->kga['lang']['total']); } continue; } $this->anySumsWereAdded = true; $this->sheet->setCellValue($curAddr, "=SUM(" . self::excelRange($x, self::EXCEL_HEADER_OFFSET, $x, self::EXCEL_HEADER_OFFSET + count($this->exportData_arr) - 1) . ")"); } }
/** * Sets cells of a single row * * @param int $row * @param mixed $cell_values * @param boolean $header * @return Worksheet */ private function _set_row($row, &$data, $header = FALSE) { $column = 0; $format = NULL; $type = PHPExcel_Cell_DataType::TYPE_STRING; foreach ($this->columns as $key => $name) { $value = NULL; if (is_array($data)) { $value = $data[$key]; } elseif (is_object($data)) { if (method_exists($data, $key)) { $value = $data->{$key}(); } elseif (isset($data->{$key})) { $value = $data->{$key}; } } // Determine cell type and format if ($header === FALSE) { $type = Arr::get($this->types, $key); } // Set cell value $coordinates = PHPExcel_Cell::stringFromColumnIndex($column) . $row; if ($type !== NULL) { $this->_worksheet->setCellValueExplicit($coordinates, $value, $type); } else { $this->_worksheet->setCellValue($coordinates, $value); } $column++; } return $this; }
/** * Добавляет в таблицу сводные данные по отчету * * @param PHPExcel_Worksheet $activeSheet * @param array $reportData * @param $rowc */ protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc) { $activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('C' . $rowc, $reportData['total']['shows'])->setCellValue('D' . $rowc, $reportData['total']['clicks'])->setCellValue('E' . $rowc, $reportData['total']['ctr'])->setCellValue('F' . $rowc, $reportData['total']['clickfraud'])->setCellValue('G' . $rowc, $reportData['total']['price']); $activeSheet->getStyle('A' . $rowc . ':D' . $rowc)->applyFromArray(array('font' => array('bold' => true))); }
/** * Добавляет в таблицу сводные данные по отчету * * @param PHPExcel_Worksheet $activeSheet * @param array $reportData * @param $rowc */ protected function addTableTotal(PHPExcel_Worksheet $activeSheet, array $reportData, $rowc) { $activeSheet->setCellValue('A' . $rowc, 'Итог')->setCellValue('B' . $rowc, $reportData['total']['clicks'])->setCellValue('D' . $rowc, $reportData['total']['price'])->setCellValue('F' . $rowc, $reportData['total']['price_with_vat'])->setCellValue('G' . $rowc, $reportData['total']['debit'])->setCellValue('H' . $rowc, $reportData['total']['debit_with_vat'])->setCellValue('I' . $rowc, $reportData['total']['debit_vat']); }
/** * @param \PHPExcel_Worksheet $sheet * @param integer $row */ public function exportAttributeValues($sheet, $row) { foreach ($this->_attributes as $attribute) { $sheet->setCellValue($attribute->standardAttribute->column . $row, $attribute->value); } }
protected function setHeader(PHPExcel_Worksheet $activeSheet, $headers, $rightColumn = null) { if (null === $rightColumn) { $rightColumn = $activeSheet->getHighestDataColumn(); } $i = 8; foreach ($headers as $title => $value) { $activeSheet->setCellValue('A' . $i, $title)->setCellValue($rightColumn . $i, $value); $i++; } $this->formatHeader($activeSheet, 'A', '8', $rightColumn, $i - 1); return $rightColumn; }
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; } }
/** * Insert a new column, updating all possible related data * * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to insert * @param int $pNumRows Number of rows to insert * @throws Exception */ public function insertNewBefore($pBefore = 'A1', $pNumCols = 0, $pNumRows = 0, PHPExcel_Worksheet $pSheet = null) { // Get a copy of the cell collection /*$aTemp = $pSheet->getCellCollection(); $aCellCollection = array(); foreach ($aTemp as $key => $value) { $aCellCollection[$key] = clone $value; }*/ $aCellCollection = $pSheet->getCellCollection(); // Get coordinates of $pBefore $beforeColumn = 'A'; $beforeRow = 1; list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString( $pBefore ); // Remove cell styles? $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols < 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols > 0) { for ($i = 1; $i <= $highestRow - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1 + $pNumCols ) . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ); } } if ($pNumRows < 0 && $beforeRow - 1 + $pNumRows > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { $pSheet->duplicateStyle( new PHPExcel_Style(), (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow + $pNumRows)) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ); } } // Loop trough cells, bottom-up, and change cell coordinates while ( ($cell = ($pNumCols < 0 || $pNumRows < 0) ? array_shift($aCellCollection) : array_pop($aCellCollection)) ) { // New coordinates $newCoordinates = PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1 + $pNumCols ) . ($cell->getRow() + $pNumRows); // Should the cell be updated? if ( (PHPExcel_Cell::columnIndexFromString( $cell->getColumn() ) >= PHPExcel_Cell::columnIndexFromString($beforeColumn)) && ($cell->getRow() >= $beforeRow) ) { // Update cell styles $pSheet->duplicateStyle( $pSheet->getStyle($cell->getCoordinate()), $newCoordinates . ':' . $newCoordinates ); $pSheet->duplicateStyle( $pSheet->getDefaultStyle(), $cell->getCoordinate() . ':' . $cell->getCoordinate() ); // Insert this cell at its new location if ($cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) { // Formula should be adjusted $pSheet->setCellValue( $newCoordinates , $this->updateFormulaReferences($cell->getValue(), $pBefore, $pNumCols, $pNumRows) ); } else { // Formula should not be adjusted $pSheet->setCellValue($newCoordinates, $cell->getValue()); } // Clear the original cell $pSheet->setCellValue($cell->getCoordinate(), ''); } } // Duplicate styles for the newly inserted cells $highestColumn = $pSheet->getHighestColumn(); $highestRow = $pSheet->getHighestRow(); if ($pNumCols > 0 && PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 > 0) { for ($i = $beforeRow; $i <= $highestRow - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 ) . $i) ), ($beforeColumn . $i) . ':' . (PHPExcel_Cell::stringFromColumnIndex( PHPExcel_Cell::columnIndexFromString($beforeColumn) - 2 + $pNumCols ) . $i) ); } } if ($pNumRows > 0 && $beforeRow - 1 > 0) { for ($i = PHPExcel_Cell::columnIndexFromString($beforeColumn) - 1; $i <= PHPExcel_Cell::columnIndexFromString($highestColumn) - 1; $i++) { // Style $pSheet->duplicateStyle( $pSheet->getStyle( (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1)) ), (PHPExcel_Cell::stringFromColumnIndex($i) . $beforeRow) . ':' . (PHPExcel_Cell::stringFromColumnIndex($i) . ($beforeRow - 1 + $pNumRows)) ); } } // Update worksheet: column dimensions $aColumnDimensions = array_reverse($pSheet->getColumnDimensions(), true); foreach ($aColumnDimensions as $objColumnDimension) { $newReference = $this->updateCellReference($objColumnDimension->getColumnIndex() . '1', $pBefore, $pNumCols, $pNumRows); list($newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objColumnDimension->getColumnIndex() != $newReference) { $objColumnDimension->setColumnIndex($newReference); } } $pSheet->refreshColumnDimensions(); // Update worksheet: row dimensions $aRowDimensions = array_reverse($pSheet->getRowDimensions(), true); foreach ($aRowDimensions as $objRowDimension) { $newReference = $this->updateCellReference('A' . $objRowDimension->getRowIndex(), $pBefore, $pNumCols, $pNumRows); list(, $newReference) = PHPExcel_Cell::coordinateFromString($newReference); if ($objRowDimension->getRowIndex() != $newReference) { $objRowDimension->setRowIndex($newReference); } } $pSheet->refreshRowDimensions(); $copyDimension = $pSheet->getRowDimension($beforeRow - 1); for ($i = $beforeRow; $i <= $beforeRow - 1 + $pNumRows; $i++) { $newDimension = $pSheet->getRowDimension($i); $newDimension->setRowHeight($copyDimension->getRowHeight()); $newDimension->setVisible($copyDimension->getVisible()); $newDimension->setOutlineLevel($copyDimension->getOutlineLevel()); $newDimension->setCollapsed($copyDimension->getCollapsed()); } // Update worksheet: breaks $aBreaks = array_reverse($pSheet->getBreaks(), true); foreach ($aBreaks as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->setBreak( $newReference, $value ); $pSheet->setBreak( $key, PHPExcel_Worksheet::BREAK_NONE ); } } // Update worksheet: merge cells $aMergeCells = array_reverse($pSheet->getMergeCells(), true); foreach ($aMergeCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->mergeCells( $newReference ); $pSheet->unmergeCells( $key ); } } // Update worksheet: protected cells $aProtectedCells = array_reverse($pSheet->getProtectedCells(), true); foreach ($aProtectedCells as $key => $value) { $newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows); if ($key != $newReference) { $pSheet->protectCells( $newReference, $value, true ); $pSheet->unprotectCells( $key ); } } // Update worksheet: autofilter if ($pSheet->getAutoFilter() != '') { $pSheet->setAutoFilter( $this->updateCellReference($pSheet->getAutoFilter(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: freeze pane if ($pSheet->getFreezePane() != '') { $pSheet->setFreezePane( $this->updateCellReference($pSheet->getFreezePane(), $pBefore, $pNumCols, $pNumRows) ); } // Page setup if ($pSheet->getPageSetup()->isPrintAreaSet()) { $pSheet->getPageSetup()->setPrintArea( $this->updateCellReference($pSheet->getPageSetup()->getPrintArea(), $pBefore, $pNumCols, $pNumRows) ); } // Update worksheet: drawings $aDrawings = $pSheet->getDrawingCollection(); foreach ($aDrawings as $objDrawing) { $newReference = $this->updateCellReference($objDrawing->getCoordinates(), $pBefore, $pNumCols, $pNumRows); if ($objDrawing->getCoordinates() != $newReference) { $objDrawing->setCoordinates($newReference); } } // Update workbook: named ranges if (count($pSheet->getParent()->getNamedRanges()) > 0) { foreach ($pSheet->getParent()->getNamedRanges() as $namedRange) { if ($namedRange->getWorksheet()->getHashCode() == $pSheet->getHashCode()) { $namedRange->setRange( $this->updateCellReference($namedRange->getRange(), $pBefore, $pNumCols, $pNumRows) ); } } } // Garbage collect $pSheet->garbageCollect(); }
/** * Sets cells of a single row * * @param int $row * @param mixed $cell_values * @param boolean $header * @return Worksheet */ private function _set_row($row, &$data, $header = FALSE) { $column = 0; $type = PHPExcel_Cell_DataType::TYPE_STRING; foreach (array_keys($this->columns) as $key) { $value = NULL; if (is_array($data)) { $value = isset($data[$key]) ? $data[$key] : ''; } elseif (is_object($data)) { if (method_exists($data, $key)) { $value = $data->{$key}(); } elseif (isset($data->{$key})) { $value = $data->{$key}; } } // Determine cell type and format if ($header === FALSE) { $type = Arr::get($this->types, $key); } $coordinates = PHPExcel_Cell::stringFromColumnIndex($column) . $row; // Options if (is_array($value)) { $options = array_slice($value, 1); $validation = $this->_worksheet->getCell($coordinates)->getDataValidation(); $validation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST); $validation->setAllowBlank(TRUE); $validation->setShowDropDown(TRUE); $validation->setFormula1('"' . join(',', $options) . '"'); $value = $value[0]; } // Set cell value if ($type !== NULL) { $this->_worksheet->setCellValueExplicit($coordinates, $value, $type); } else { $this->_worksheet->setCellValue($coordinates, $value); } $column++; } return $this; }
/** * Устанавливает базовый заголовок * * @param PHPExcel_Worksheet $activeSheet */ protected function setBaseHeader(PHPExcel_Worksheet $activeSheet) { $activeSheet->setCellValue('A4', 'Клиент:')->setCellValue('C4', $this->campaign->client->login)->setCellValue('A5', 'Общий период проведения кампании:')->setCellValue('C5', date('d.m.Y', strtotime($this->campaign->date_start)) . '-' . date('d.m.Y', strtotime($this->campaign->date_end)))->setCellValue('A6', 'Период промежуточного отчета по кампании:')->setCellValue('C6', date('d.m.Y', strtotime($this->dateFrom)) . '-' . date('d.m.Y', strtotime($this->dateTo))); }
/** * Set a cell value * * @param string $pCoordinate Coordinate of the cell * @param mixed $pValue Value of the cell * @return Worksheet */ public function setCellValue($pCoordinate = 'A1', $pValue = null) { $this->sheet->setCellValue($pCoordinate, $pValue); return $this; }
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); }