/**
* 行を完全コピーする
*
* 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 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;
}
/**
*
*/
protected function _addFilters()
{
$pFilters = $this->_content->getElementsByTagName('p');
if (empty($pFilters->length)) {
return false;
}
$filters = array();
foreach ($pFilters->item(0)->childNodes as $child) {
$nodeValue = trim($child->nodeValue);
if (!empty($nodeValue)) {
$filters[] = $nodeValue;
}
}
$this->_mainSheet->getCell('C6')->setValue(implode(' ', $filters))->getStyle()->getAlignment()->setWrapText(true);
$this->_mainSheet->mergeCells('C6:T6');
}
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readNumber()
{
$length = self::_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// offset: 0; size: 2; index to row
$row = self::_GetInt2d($recordData, 0);
// offset: 2; size 2; index to column
$column = self::_GetInt2d($recordData, 2);
$columnString = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell?
/**
* 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();
}
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function _readMergedCells()
{
$spos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $spos + 2);
$recordData = substr($this->_data, $spos + 4, $length);
$spos += 4;
if ($this->_version == self::XLS_BIFF8 && !$this->_readDataOnly) {
$cellRangeAddressList = $this->_readBIFF8CellRangeAddressList($recordData);
foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
$this->_phpSheet->mergeCells($cellRangeAddress);
}
}
$this->_pos += 4 + $length;
}
/**
*
* @param string $range Range in format A1:A1
* @return Formatter
*/
public function mergeAndCenter($range)
{
$this->sheet->mergeCells($range);
$this->sheet->getStyle($range)->getAlignment()->setHorizontal(PhpOffice_PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
return $this;
}
/**
* MERGEDCELLS
*
* This record contains the addresses of merged cell ranges
* in the current sheet.
*
* -- "OpenOffice.org's Documentation of the Microsoft
* Excel File Format"
*/
private function readMergedCells()
{
$length = self::getInt2d($this->data, $this->pos + 2);
$recordData = $this->readRecordData($this->data, $this->pos + 4, $length);
// move stream pointer to next record
$this->pos += 4 + $length;
if ($this->version == self::XLS_BIFF8 && !$this->readDataOnly) {
$cellRangeAddressList = $this->readBIFF8CellRangeAddressList($recordData);
foreach ($cellRangeAddressList['cellRangeAddresses'] as $cellRangeAddress) {
if (strpos($cellRangeAddress, ':') !== false && $this->includeCellRangeFiltered($cellRangeAddress)) {
$this->phpSheet->mergeCells($cellRangeAddress);
}
}
}
}
/**
* 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 = 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->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 mbatik($data, $title)
{
$worksheet = new PHPExcel_Worksheet();
$worksheet->setTitle($title);
// Siswa Start
$worksheet->mergeCells('A3:R3');
$worksheet->setCellValue('A3', 'Data Siswa');
$worksheet->getColumnDimension('A')->setAutoSize(true);
$worksheet->SetCellValue('A4', 'No. Pendaftaran');
$worksheet->getColumnDimension('B')->setAutoSize(true);
$worksheet->SetCellValue('B4', 'NISN');
$worksheet->getColumnDimension('C')->setAutoSize(true);
$worksheet->SetCellValue('C4', 'Nama');
$worksheet->getColumnDimension('D')->setAutoSize(true);
$worksheet->SetCellValue('D4', 'Ikhwan/Akhwat');
$worksheet->getColumnDimension('E')->setAutoSize(true);
$worksheet->SetCellValue('E4', 'Sekolah Asal');
$worksheet->getColumnDimension('F')->setAutoSize(true);
$worksheet->SetCellValue('F4', 'Program');
$worksheet->getColumnDimension('G')->setAutoSize(true);
$worksheet->SetCellValue('G4', 'TTL');
$worksheet->getColumnDimension('H')->setAutoSize(true);
$worksheet->SetCellValue('H4', 'Alamat');
$worksheet->getColumnDimension('I')->setAutoSize(true);
$worksheet->SetCellValue('I4', 'Keluarga');
$worksheet->getColumnDimension('J')->setAutoSize(true);
$worksheet->SetCellValue('J4', 'Kewarganegaraan');
$worksheet->getColumnDimension('K')->setAutoSize(true);
$worksheet->SetCellValue('K4', 'Agama');
$worksheet->getColumnDimension('L')->setAutoSize(true);
$worksheet->SetCellValue('L4', 'Tinggi');
$worksheet->getColumnDimension('M')->setAutoSize(true);
$worksheet->SetCellValue('M4', 'Berat');
$worksheet->getColumnDimension('N')->setAutoSize(true);
$worksheet->SetCellValue('N4', 'Riwayat Penyakit');
$worksheet->getColumnDimension('O')->setAutoSize(true);
$worksheet->SetCellValue('O4', 'Kelainan Jasmani');
$worksheet->getColumnDimension('P')->setAutoSize(true);
$worksheet->SetCellValue('P4', 'Tinggal Bersama');
$worksheet->getColumnDimension('Q')->setAutoSize(true);
$worksheet->SetCellValue('Q4', 'Hobi');
$worksheet->getColumnDimension('R')->setAutoSize(true);
$worksheet->SetCellValue('R4', 'Prestasi');
// End Siswa
// Start Ayah
$worksheet->mergeCells('S3:AF3');
$worksheet->setCellValue('S3', 'Data Ayah');
$worksheet->getColumnDimension('S')->setAutoSize(true);
$worksheet->SetCellValue('S4', 'Nama');
$worksheet->getColumnDimension('T')->setAutoSize(true);
$worksheet->SetCellValue('T4', 'Status');
$worksheet->getColumnDimension('U')->setAutoSize(true);
$worksheet->SetCellValue('U4', 'TTL');
$worksheet->getColumnDimension('V')->setAutoSize(true);
$worksheet->SetCellValue('V4', 'Alamat');
$worksheet->getColumnDimension('W')->setAutoSize(true);
$worksheet->SetCellValue('W4', 'No. Telp');
$worksheet->getColumnDimension('X')->setAutoSize(true);
$worksheet->SetCellValue('X4', 'Hubungan dengan pendaftar');
$worksheet->getColumnDimension('Y')->setAutoSize(true);
$worksheet->SetCellValue('Y4', 'Kewarganegaraan');
$worksheet->getColumnDimension('Z')->setAutoSize(true);
$worksheet->SetCellValue('Z4', 'Agama');
$worksheet->getColumnDimension('AA')->setAutoSize(true);
$worksheet->SetCellValue('AA4', 'Tingkat Pendidikan');
$worksheet->getColumnDimension('AB')->setAutoSize(true);
$worksheet->SetCellValue('AB4', 'Pekerjaan');
$worksheet->getColumnDimension('AC')->setAutoSize(true);
$worksheet->SetCellValue('AC4', 'Jabatan');
$worksheet->getColumnDimension('AD')->setAutoSize(true);
$worksheet->SetCellValue('AD4', 'Instansi');
$worksheet->getColumnDimension('AE')->setAutoSize(true);
$worksheet->SetCellValue('AE4', 'Penghasilan');
$worksheet->getColumnDimension('AF')->setAutoSize(true);
$worksheet->SetCellValue('AF4', 'Jumlah Tanggungan');
// End Ayah
// Start Ibu
$worksheet->mergeCells('AG3:AT3');
$worksheet->setCellValue('AG3', 'Data Ibu');
$worksheet->getColumnDimension('AG')->setAutoSize(true);
$worksheet->SetCellValue('AG4', 'Nama');
$worksheet->getColumnDimension('AH')->setAutoSize(true);
$worksheet->SetCellValue('AH4', 'Status');
$worksheet->getColumnDimension('AI')->setAutoSize(true);
$worksheet->SetCellValue('AI4', 'TTL');
$worksheet->getColumnDimension('AJ')->setAutoSize(true);
$worksheet->SetCellValue('AJ4', 'Alamat');
$worksheet->getColumnDimension('AK')->setAutoSize(true);
$worksheet->SetCellValue('AK4', 'No. Telp');
$worksheet->getColumnDimension('AL')->setAutoSize(true);
$worksheet->SetCellValue('AL4', 'Hubungan dengan pendaftar');
$worksheet->getColumnDimension('AM')->setAutoSize(true);
$worksheet->SetCellValue('AM4', 'Kewarganegaraan');
$worksheet->getColumnDimension('AN')->setAutoSize(true);
$worksheet->SetCellValue('AN4', 'Agama');
$worksheet->getColumnDimension('AO')->setAutoSize(true);
$worksheet->SetCellValue('AO4', 'Tingkat Pendidikan');
$worksheet->getColumnDimension('AP')->setAutoSize(true);
$worksheet->SetCellValue('AP4', 'Pekerjaan');
$worksheet->getColumnDimension('AQ')->setAutoSize(true);
$worksheet->SetCellValue('AQ4', 'Jabatan');
$worksheet->getColumnDimension('AR')->setAutoSize(true);
$worksheet->SetCellValue('AR4', 'Instansi');
$worksheet->getColumnDimension('AS')->setAutoSize(true);
$worksheet->SetCellValue('AS4', 'Penghasilan');
$worksheet->getColumnDimension('AT')->setAutoSize(true);
$worksheet->SetCellValue('AT4', 'Jumlah Tanggungan');
// End Ibu
// Start Wali
$worksheet->mergeCells('AU3:BH3');
$worksheet->setCellValue('AU3', 'Data Wali');
$worksheet->getColumnDimension('AU')->setAutoSize(true);
$worksheet->SetCellValue('AU4', 'Nama');
$worksheet->getColumnDimension('AV')->setAutoSize(true);
$worksheet->SetCellValue('AV4', 'Status');
$worksheet->getColumnDimension('AW')->setAutoSize(true);
$worksheet->SetCellValue('AW4', 'TTL');
$worksheet->getColumnDimension('AX')->setAutoSize(true);
$worksheet->SetCellValue('AX4', 'Alamat');
$worksheet->getColumnDimension('AY')->setAutoSize(true);
$worksheet->SetCellValue('AY4', 'No. Telp');
$worksheet->getColumnDimension('AZ')->setAutoSize(true);
$worksheet->SetCellValue('AZ4', 'Hubungan dengan pendaftar');
$worksheet->getColumnDimension('BA')->setAutoSize(true);
$worksheet->SetCellValue('BA4', 'Kewarganegaraan');
$worksheet->getColumnDimension('BB')->setAutoSize(true);
$worksheet->SetCellValue('BB4', 'Agama');
$worksheet->getColumnDimension('BC')->setAutoSize(true);
$worksheet->SetCellValue('BC4', 'Tingkat Pendidikan');
$worksheet->getColumnDimension('BD')->setAutoSize(true);
$worksheet->SetCellValue('BD4', 'Pekerjaan');
$worksheet->getColumnDimension('BE')->setAutoSize(true);
$worksheet->SetCellValue('BE4', 'Jabatan');
$worksheet->getColumnDimension('BF')->setAutoSize(true);
$worksheet->SetCellValue('BF4', 'Instansi');
$worksheet->getColumnDimension('BG')->setAutoSize(true);
$worksheet->SetCellValue('BG4', 'Penghasilan');
$worksheet->getColumnDimension('BH')->setAutoSize(true);
$worksheet->SetCellValue('BH4', 'Jumlah Tanggungan');
// End Wali
// Start Pembayaran
$worksheet->mergeCells('BI3:BJ3');
$worksheet->setCellValue('BI3', 'Data Pembayaran');
$worksheet->getColumnDimension('BI')->setAutoSize(true);
$worksheet->SetCellValue('BI4', 'Infaq Pendidikan');
$worksheet->getColumnDimension('BJ')->setAutoSize(true);
$worksheet->SetCellValue('BJ4', 'SPP');
// End Pembayaran
// Start Mbatik Isi
$row = 5;
foreach ($data as $registrant) {
//$registrant = new RegistrantEntity();
$rData = $registrant->getRegistrantData();
// Registrant Data
$worksheet->SetCellValue('A' . $row, $registrant->getId());
$worksheet->SetCellValue('B' . $row, $registrant->getNisn());
$worksheet->SetCellValue('C' . $row, $registrant->getName());
$worksheet->SetCellValue('D' . $row, $registrant->getGender() == 'L' ? 'Ikhwan' : 'Akhwat');
$worksheet->SetCellValue('E' . $row, $registrant->getPreviousSchool());
$worksheet->SetCellValue('F' . $row, $registrant->getProgram());
if (!empty($rData)) {
$worksheet->SetCellValue('G' . $row, ucfirst($rData->getBirthPlace()) . ', ' . tgl_indo($rData->getBirthDate()->format('Y m d')));
$worksheet->SetCellValue('H' . $row, $rData->getAddress());
$worksheet->SetCellValue('I' . $row, ucwords($rData->getFamilyCondition()));
$worksheet->SetCellValue('J' . $row, strtoupper($rData->getNationality()));
$worksheet->SetCellValue('K' . $row, ucfirst($rData->getReligion()));
$worksheet->SetCellValue('L' . $row, $rData->getHeight());
$worksheet->SetCellValue('M' . $row, $rData->getWeight());
$worksheet->SetCellValue('N' . $row, $rData->getHospitalSheets(false));
$worksheet->SetCellValue('O' . $row, $rData->getPhysicalAbnormalities(false));
$worksheet->SetCellValue('P' . $row, ucwords($rData->getStayWith()));
$worksheet->SetCellValue('Q' . $row, $rData->getHobbies(false));
$worksheet->SetCellValue('R' . $row, $rData->getAchievements(false));
}
// Registrant Payment
$worksheet->SetCellValue('BI' . $row, $registrant->getInitialCost());
$worksheet->SetCellValue('BJ' . $row, $registrant->getSubscriptionCost());
// Registrant Father
$fData = $registrant->getFather();
if (!empty($fData)) {
$worksheet->SetCellValue('S' . $row, $fData->getName());
$worksheet->SetCellValue('T' . $row, $fData->getStatus());
$worksheet->SetCellValue('U' . $row, ucfirst($fData->getBirthPlace()) . ', ' . tgl_indo($fData->getBirthDate()->format('Y m d')));
$worksheet->SetCellValue('V' . $row, $fData->getAddress());
$worksheet->SetCellValue('W' . $row, $fData->getContact());
$worksheet->SetCellValue('X' . $row, ucwords($fData->getRelation()));
$worksheet->SetCellValue('Y' . $row, strtoupper($fData->getNationality()));
$worksheet->SetCellValue('Z' . $row, ucwords($fData->getReligion()));
$worksheet->SetCellValue('AA' . $row, $fData->getEducationLevel());
$worksheet->SetCellValue('AB' . $row, $fData->getJob());
$worksheet->SetCellValue('AC' . $row, $fData->getPosition());
$worksheet->SetCellValue('AD' . $row, $fData->getCompany());
$worksheet->SetCellValue('AE' . $row, number_format($fData->getIncome(), 0, ',', '.'));
$worksheet->SetCellValue('AF' . $row, $fData->getBurdenCount());
}
// Registrant Mother
$mData = $registrant->getMother();
if (!empty($mData)) {
$worksheet->SetCellValue('AG' . $row, $mData->getName());
$worksheet->SetCellValue('AH' . $row, $mData->getStatus());
$worksheet->SetCellValue('AI' . $row, ucfirst($mData->getBirthPlace()) . ', ' . tgl_indo($mData->getBirthDate()->format('Y m d')));
$worksheet->SetCellValue('AJ' . $row, $mData->getAddress());
$worksheet->SetCellValue('AK' . $row, $mData->getContact());
$worksheet->SetCellValue('AL' . $row, ucwords($mData->getRelation()));
$worksheet->SetCellValue('AM' . $row, strtoupper($mData->getNationality()));
$worksheet->SetCellValue('AN' . $row, ucwords($mData->getReligion()));
$worksheet->SetCellValue('AO' . $row, $mData->getEducationLevel());
$worksheet->SetCellValue('AP' . $row, $mData->getJob());
$worksheet->SetCellValue('AQ' . $row, $mData->getPosition());
$worksheet->SetCellValue('AR' . $row, $mData->getCompany());
$worksheet->SetCellValue('AS' . $row, number_format($mData->getIncome(), 0, ',', '.'));
$worksheet->SetCellValue('AT' . $row, $mData->getBurdenCount());
}
// Registrant Mother
$gData = $registrant->getGuardian();
if (!empty($gData)) {
$worksheet->SetCellValue('AU' . $row, $gData->getName());
$worksheet->SetCellValue('AV' . $row, $gData->getStatus());
$worksheet->SetCellValue('AW' . $row, ucfirst($gData->getBirthPlace()) . ', ' . tgl_indo($gData->getBirthDate()->format('Y m d')));
$worksheet->SetCellValue('AX' . $row, $gData->getAddress());
$worksheet->SetCellValue('AY' . $row, $gData->getContact());
$worksheet->SetCellValue('AZ' . $row, ucwords($gData->getRelation()));
$worksheet->SetCellValue('BA' . $row, strtoupper($gData->getNationality()));
$worksheet->SetCellValue('BB' . $row, ucwords($gData->getReligion()));
$worksheet->SetCellValue('BC' . $row, $gData->getEducationLevel());
$worksheet->SetCellValue('BD' . $row, $gData->getJob());
$worksheet->SetCellValue('BE' . $row, $gData->getPosition());
$worksheet->SetCellValue('BF' . $row, $gData->getCompany());
$worksheet->SetCellValue('BG' . $row, number_format($gData->getIncome(), 0, ',', '.'));
$worksheet->SetCellValue('BH' . $row, $gData->getBurdenCount());
}
// Iteration of Rows
$row++;
}
// End Mbatik Isi
$this->excel->addSheet($worksheet);
}
