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