/**
* @param $column
* @param $row
*
* @return mixed
*/
public function getValue($column, $row)
{
$cell = $this->objWorksheet->getCellByColumnAndRow($column, $row);
// Find if this is cell is merged with others
foreach ($this->mergedCellsRange as $currMergedRange) {
if ($cell->isInRange($currMergedRange)) {
$currMergedCellsArray = \PHPExcel_Cell::splitRange($currMergedRange);
$cell = $this->objWorksheet->getCell($currMergedCellsArray[0][0]);
break;
}
}
return $cell->getValue();
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null) {
// Open file
global $cnf;
$pFilename= $cnf['path']['Temp'] . $pFilename;
$fileHandle = fopen($pFilename, 'w');
if ($fileHandle === false) {
throw new Exception("Could not open file $pFilename for writing.");
}
// Fetch sheets
$sheets = array();
if (is_null($this->_sheetIndex)) {
$sheets = $this->_phpExcel->getAllSheets();
} else {
$sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
}
// PDF paper size
$paperSize = 'A4';
// Create PDF
$pdf = new FPDF('P', 'pt', $paperSize);
// Loop all sheets
foreach ($sheets as $sheet) {
// PDF orientation
$orientation = 'P';
if ($sheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
$orientation = 'L';
}
// Start sheet
$pdf->SetAutoPageBreak(true);
$pdf->SetFont('Arial', '', 10);
$pdf->AddPage($orientation);
// Get worksheet dimension
$dimension = explode(':', $sheet->calculateWorksheetDimension());
$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
// Calculate column widths
$sheet->calculateColumnWidths();
// Loop trough cells
for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) {
// Line height
$lineHeight = 0;
// Calulate line height
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
$rowDimension = $sheet->getRowDimension($row);
$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight())
);
if ($cellHeight <= 0) {
$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
PHPExcel_Shared_Drawing::cellDimensionToPixels($sheet->getDefaultRowDimension()->getRowHeight())
);
}
if ($cellHeight <= 0) {
$cellHeight = $sheet->getStyleByColumnAndRow($column, $row)->getFont()->getSize();
}
if ($cellHeight > $lineHeight) {
$lineHeight = $cellHeight;
}
}
// Output values
for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
// Start with defaults...
$pdf->SetFont('Arial', '', 10);
$pdf->SetTextColor(0, 0, 0);
$pdf->SetDrawColor(100, 100, 100);
$pdf->SetFillColor(255, 255, 255);
// Coordinates
$startX = $pdf->GetX();
$startY = $pdf->GetY();
// Cell exists?
$cellData = '';
if ($sheet->cellExistsByColumnAndRow($column, $row)) {
if ($sheet->getCellByColumnAndRow($column, $row)->getValue() instanceof PHPExcel_RichText) {
$cellData = $sheet->getCellByColumnAndRow($column, $row)->getValue()->getPlainText();
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
$sheet->getCellByColumnAndRow($column, $row)->getCalculatedValue(),
$sheet->getstyle( $sheet->getCellByColumnAndRow($column, $row)->getCoordinate() )->getNumberFormat()->getFormatCode()
);
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
$sheet->getCellByColumnAndRow($column, $row)->getValue(),
$sheet->getstyle( $sheet->getCellByColumnAndRow($column, $row)->getCoordinate() )->getNumberFormat()->getFormatCode()
);
}
}
}
// Style information
$style = $sheet->getStyleByColumnAndRow($column, $row);
// Cell width
$columnDimension = $sheet->getColumnDimensionByColumn($column);
if ($columnDimension->getWidth() == -1) {
$columnDimension->setAutoSize(true);
$sheet->calculateColumnWidths(false);
}
$cellWidth = PHPExcel_Shared_Drawing::pixelsToPoints(
PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth())
);
// Cell height
$rowDimension = $sheet->getRowDimension($row);
$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight())
);
if ($cellHeight <= 0) {
$cellHeight = $style->getFont()->getSize();
}
// Column span? Rowspan?
$singleCellWidth = $cellWidth;
$singleCellHeight = $cellHeight;
foreach ($sheet->getMergeCells() as $cells) {
if ($sheet->getCellByColumnAndRow($column, $row)->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $sheet->getCellByColumnAndRow($column, $row)->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
$cellWidth = $cellWidth * $colSpan;
$cellHeight = $cellHeight * $rowSpan;
}
break;
}
}
// Cell height OK?
if ($cellHeight < $lineHeight) {
$cellHeight = $lineHeight;
$singleCellHeight = $cellHeight;
}
// Font formatting
$fontStyle = '';
if ($style->getFont()->getBold()) {
$fontStyle .= 'B';
}
if ($style->getFont()->getItalic()) {
$fontStyle .= 'I';
}
if ($style->getFont()->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE) {
$fontStyle .= 'U';
}
$pdf->SetFont('Arial', $fontStyle, $style->getFont()->getSize());
// Text alignment
$alignment = 'L';
switch ($style->getAlignment()->getHorizontal()) {
case PHPExcel_Style_Alignment::HORIZONTAL_CENTER:
$alignment = 'C'; break;
case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT:
$alignment = 'R'; break;
case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY:
$alignment = 'J'; break;
case PHPExcel_Style_Alignment::HORIZONTAL_LEFT:
case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL:
default:
$alignment = 'L'; break;
}
// Text color
$pdf->SetTextColor(
hexdec(substr($style->getFont()->getColor()->getRGB(), 0, 2)),
hexdec(substr($style->getFont()->getColor()->getRGB(), 2, 2)),
hexdec(substr($style->getFont()->getColor()->getRGB(), 4, 2))
);
// Fill color
if ($style->getFill()->getFillType() != PHPExcel_Style_Fill::FILL_NONE) {
$pdf->SetFillColor(
hexdec(substr($style->getFill()->getStartColor()->getRGB(), 0, 2)),
hexdec(substr($style->getFill()->getStartColor()->getRGB(), 2, 2)),
hexdec(substr($style->getFill()->getStartColor()->getRGB(), 4, 2))
);
}
// Border color
$borders = '';
if ($style->getBorders()->getLeft()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
$borders .= 'L';
$pdf->SetDrawColor(
hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 0, 2)),
hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 2, 2)),
hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 4, 2))
);
}
if ($style->getBorders()->getRight()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
$borders .= 'R';
$pdf->SetDrawColor(
hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 0, 2)),
hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 2, 2)),
hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 4, 2))
);
}
if ($style->getBorders()->getTop()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
$borders .= 'T';
$pdf->SetDrawColor(
hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 0, 2)),
hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 2, 2)),
hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 4, 2))
);
}
if ($style->getBorders()->getBottom()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
$borders .= 'B';
$pdf->SetDrawColor(
hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 0, 2)),
hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 2, 2)),
hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 4, 2))
);
}
if ($borders == '') {
$borders = 0;
}
if ($sheet->getShowGridlines()) {
$borders = 'LTRB';
}
// Image?
$iterator = $sheet->getDrawingCollection()->getIterator();
while ($iterator->valid()) {
if ($iterator->current()->getCoordinates() == PHPExcel_Cell::stringFromColumnIndex($column) . ($row + 1)) {
try {
$pdf->Image(
$iterator->current()->getPath(),
$pdf->GetX(),
$pdf->GetY(),
$iterator->current()->getWidth(),
$iterator->current()->getHeight(),
'',
$this->_tempDir
);
} catch (Exception $ex) { }
}
$iterator->next();
}
// Print cell
$pdf->MultiCell(
$cellWidth,
$cellHeight,
$cellData,
$borders,
$alignment,
($style->getFill()->getFillType() == PHPExcel_Style_Fill::FILL_NONE ? 0 : 1)
);
// Coordinates
$endX = $pdf->GetX();
$endY = $pdf->GetY();
// Revert to original Y location
if ($endY > $startY) {
$pdf->SetY($startY);
if ($lineHeight < $lineHeight + ($endY - $startY)) {
$lineHeight = $lineHeight + ($endY - $startY);
}
}
$pdf->SetX($startX + $singleCellWidth);
// Hyperlink?
if ($sheet->getCellByColumnAndRow($column, $row)->hasHyperlink()) {
if (!$sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->isInternal()) {
$pdf->Link(
$startX,
$startY,
$endX - $startX,
$endY - $startY,
$sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->getUrl()
);
}
}
}
// Garbage collect!
$sheet->garbageCollect();
// Next line...
$pdf->Ln($lineHeight);
}
}
// Document info
$pdf->SetTitle($this->_phpExcel->getProperties()->getTitle());
$pdf->SetAuthor($this->_phpExcel->getProperties()->getCreator());
$pdf->SetSubject($this->_phpExcel->getProperties()->getSubject());
$pdf->SetKeywords($this->_phpExcel->getProperties()->getKeywords());
$pdf->SetCreator($this->_phpExcel->getProperties()->getCreator());
// Write to file
fwrite($fileHandle, $pdf->output($pFilename, 'S'));
// Close file
fclose($fileHandle);
}
/**
* Writes all the DEFINEDNAME records (BIFF8).
* So far this is only used for repeating rows/columns (print titles) and print areas
*/
public function writeAllDefinedNamesBiff8()
{
$chunk = '';
// write the print titles (repeating rows, columns), if any
$total_worksheets = count($this->_worksheets);
for ($i = 0; $i < $total_worksheets; ++$i) {
// repeatColumns / repeatRows
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
// Row and column titles have been defined
// Columns to repeat
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
}
}
// write the print areas, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) {
// Print area
$printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea());
$printArea = $printArea[0];
$printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
$printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
$print_rowmin = $printArea[0][1] - 1;
$print_rowmax = $printArea[1][1] - 1;
$print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
$print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
}
}
return $chunk;
}
/**
* Write the NAME record to define the print area and the repeat rows and cols.
*/
private function _writeNames()
{
// total number of sheets
$total_worksheets = $this->_phpExcel->getSheetCount();
// Create the print area NAME records
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
// Write a Name record if the print area has been defined
if ($sheetSetup->isPrintAreaSet()) {
// Print area
$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
$printArea = $printArea[0];
$printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
$printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
$print_rowmin = $printArea[0][1] - 1;
$print_rowmax = $printArea[1][1] - 1;
$print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
$print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
$this->_writeNameShort($i, 0x6, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
}
}
// Create the print title NAME records
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup();
// simultaneous repeatColumns repeatRows
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
$this->_writeNameLong($i, 0x7, $rowmin, $rowmax, $colmin, $colmax);
// (exclusive) either repeatColumns or repeatRows
} else {
if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
// Columns to repeat
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
$this->_writeNameShort($i, 0x7, $rowmin, $rowmax, $colmin, $colmax);
}
}
}
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null)
{
$this->_colors = array();
$phpExcel = $this->_phpExcel;
$workbook = new PHPExcel_Writer_Excel5_Writer($pFilename);
$workbook->setVersion(8);
// Set temp dir
if ($this->_tempDir != '') {
$workbook->setTempDir($this->_tempDir);
}
// Create empty style
$emptyStyle = new PHPExcel_Style();
// Add empty sheets
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $workbook->addWorksheet($sheetName);
}
$allWorksheets = $workbook->worksheets();
// Add full sheet data
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $allWorksheets[$sheetIndex];
$worksheet->setInputEncoding("UTF-8");
$aStyles = $phpSheet->getStyles();
$freeze = $phpSheet->getFreezePane();
if ($freeze) {
list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze);
$worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1));
}
//if ($sheetIndex == $phpExcel->getActiveSheetIndex()) {
// $worksheet->select();
//}
if ($phpSheet->getProtection()->getSheet()) {
$worksheet->protect($phpSheet->getProtection()->getPassword(), true);
}
if (!$phpSheet->getShowGridlines()) {
$worksheet->hideGridLines();
}
$formats = array();
foreach ($phpSheet->getCellCollection() as $cell) {
$row = $cell->getRow() - 1;
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
// Don't break Excel!
if ($row + 1 >= 65569) {
break;
}
$style = $emptyStyle;
if (isset($aStyles[$cell->getCoordinate()])) {
$style = $aStyles[$cell->getCoordinate()];
}
$styleHash = $style->getHashCode();
if (!isset($formats[$styleHash])) {
$formats[$styleHash] = $workbook->addFormat(array('HAlign' => $style->getAlignment()->getHorizontal(), 'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()), 'TextRotation' => $style->getAlignment()->getTextRotation(), 'Bold' => $style->getFont()->getBold(), 'FontFamily' => $style->getFont()->getName(), 'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()), 'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()), 'Size' => $style->getFont()->getSize(), 'NumFormat' => iconv("UTF-8", "Windows-1252", $style->getNumberFormat()->getFormatCode()), 'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()), 'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()), 'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()), 'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()), 'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()), 'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()), 'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()), 'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()), 'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()), 'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()), 'Pattern' => $this->_mapFillType($style->getFill()->getFillType())));
if ($style->getAlignment()->getWrapText()) {
$formats[$styleHash]->setTextWrap();
}
if ($style->getFont()->getItalic()) {
$formats[$styleHash]->setItalic();
}
if ($style->getFont()->getStriketrough()) {
$formats[$styleHash]->setStrikeOut();
}
}
// Write cell value
if ($cell->getValue() instanceof PHPExcel_RichText) {
$worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]);
} else {
// Hyperlink?
if ($cell->hasHyperlink()) {
$worksheet->writeUrl($row, $column, $cell->getHyperlink()->getUrl(), $cell->getValue(), $formats[$styleHash]);
} else {
$worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash]);
}
}
}
$phpSheet->calculateColumnWidths();
foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$worksheet->setColumn($column, $column, $columnDimension->getWidth(), null, $columnDimension->getVisible() ? '0' : '1');
}
foreach ($phpSheet->getRowDimensions() as $rowDimension) {
$worksheet->setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1');
}
foreach ($phpSheet->getMergeCells() as $cells) {
list($first, $last) = PHPExcel_Cell::splitRange($cells);
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
$worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
}
foreach ($phpSheet->getDrawingCollection() as $drawing) {
if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
$filename = $drawing->getPath();
$imagesize = getimagesize($filename);
switch ($imagesize[2]) {
case 1:
$image = imagecreatefromgif($filename);
break;
case 2:
$image = imagecreatefromjpeg($filename);
break;
case 3:
$image = imagecreatefrompng($filename);
break;
default:
continue 2;
}
list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
$worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]);
}
}
// page setup
if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
$worksheet->setLandscape();
}
$worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize());
$worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader());
$worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter());
$worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft());
$worksheet->setMarginRight($phpSheet->getPageMargins()->getRight());
$worksheet->setMarginTop($phpSheet->getPageMargins()->getTop());
$worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom());
}
$workbook->close();
}
/**
* Save PHPExcel to file
*
* @param string $pFileName
* @throws Exception
*/
public function save($pFilename = null) {
$this->_colors = array();
$phpExcel = $this->_phpExcel;
$workbook = new PHPExcel_Writer_Excel5_Writer($pFilename);
$workbook->setVersion(8);
// Set temp dir
if ($this->_tempDir != '') {
$workbook->setTempDir($this->_tempDir);
}
$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
// Add empty sheets
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $workbook->addWorksheet($sheetName);
}
$allWorksheets = $workbook->worksheets();
// Add full sheet data
foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) {
$phpSheet = $phpExcel->getSheet($sheetIndex);
$worksheet = $allWorksheets[$sheetIndex];
$worksheet->setInputEncoding("UTF-8");
// Default style
$emptyStyle = $phpSheet->getDefaultStyle();
$aStyles = $phpSheet->getStyles();
$freeze = $phpSheet->getFreezePane();
if ($freeze) {
list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze);
$worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1));
}
//if ($sheetIndex == $phpExcel->getActiveSheetIndex()) {
// $worksheet->select();
//}
if ($phpSheet->getProtection()->getSheet()) {
$worksheet->protect($phpSheet->getProtection()->getPassword(), true);
}
if (!$phpSheet->getShowGridlines()) {
$worksheet->hideGridLines();
}
$formats = array();
foreach ($phpSheet->getCellCollection() as $cell) {
$row = $cell->getRow() - 1;
$column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
// Don't break Excel!
if ($row + 1 >= 65569) {
break;
}
$style = $emptyStyle;
if (isset($aStyles[$cell->getCoordinate()])) {
$style = $aStyles[$cell->getCoordinate()];
}
$styleHash = $style->getHashCode();
if (!isset($formats[$styleHash])) {
$formats[$styleHash] = $workbook->addFormat(array(
'HAlign' => $style->getAlignment()->getHorizontal(),
'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()),
'TextRotation' => $style->getAlignment()->getTextRotation(),
'Bold' => $style->getFont()->getBold(),
'FontFamily' => $style->getFont()->getName(),
'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()),
'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()),
'Size' => $style->getFont()->getSize(),
//~ 'Script' => $style->getSuperscript(),
'NumFormat' => iconv("UTF-8", "Windows-1252", $style->getNumberFormat()->getFormatCode()),
'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()),
'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()),
'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()),
'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()),
'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()),
'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()),
'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()),
'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()),
'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()),
'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()),
'Pattern' => $this->_mapFillType($style->getFill()->getFillType()),
));
if ($style->getAlignment()->getWrapText()) {
$formats[$styleHash]->setTextWrap();
}
if ($style->getAlignment()->getShrinkToFit()) {
$formats[$styleHash]->setShrinkToFit();
}
if ($style->getFont()->getItalic()) {
$formats[$styleHash]->setItalic();
}
if ($style->getFont()->getStriketrough()) {
$formats[$styleHash]->setStrikeOut();
}
}
// Write cell value
if ($cell->getValue() instanceof PHPExcel_RichText) {
$worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]);
} else {
// Hyperlink?
if ($cell->hasHyperlink()) {
$worksheet->writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl()), $cell->getValue(), $formats[$styleHash]);
} else {
$worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash],$style->getNumberFormat()->getFormatCode());
}
}
}
// Column dimensions
$phpSheet->calculateColumnWidths();
$defaultWidth = null;
if ($phpSheet->getDefaultColumnDimension()->getWidth() >= 0) {
$defaultWidth = $phpSheet->getDefaultColumnDimension()->getWidth();
for ($column = 0; $column < PHPExcel_Cell::columnIndexFromString($phpSheet->getHighestColumn()) - 1; $column++) {
$worksheet->setColumn( $column, $column, $defaultWidth );
}
}
foreach ($phpSheet->getColumnDimensions() as $columnDimension) {
$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
$worksheet->setColumn( $column, $column, $columnDimension->getWidth(), null, ($columnDimension->getVisible() ? '0' : '1'), $columnDimension->getOutlineLevel());
}
// Row dimensions
$defaultHeight = null;
if ($phpSheet->getDefaultRowDimension()->getRowHeight() >= 0) {
$defaultHeight = $phpSheet->getDefaultRowDimension()->getRowHeight();
for ($i = 0; $i < $phpSheet->getHighestRow() - 1; $i++) {
$worksheet->setRow( $i, $defaultHeight );
}
}
foreach ($phpSheet->getRowDimensions() as $rowDimension) {
$worksheet->setRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() );
}
foreach ($phpSheet->getMergeCells() as $cells) {
list($first, $last) = PHPExcel_Cell::splitRange($cells);
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
$worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
}
foreach ($phpSheet->getDrawingCollection() as $drawing) {
if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) {
$filename = $drawing->getPath();
$imagesize = getimagesize($filename);
switch ($imagesize[2]) {
case 1: $image = imagecreatefromgif($filename); break;
case 2: $image = imagecreatefromjpeg($filename); break;
case 3: $image = imagecreatefrompng($filename); break;
default: continue 2;
}
list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates());
$worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]);
}
}
// page setup
if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
$worksheet->setLandscape();
}
$worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize());
$worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader());
$worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter());
$worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft());
$worksheet->setMarginRight($phpSheet->getPageMargins()->getRight());
$worksheet->setMarginTop($phpSheet->getPageMargins()->getTop());
$worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom());
// -------------------------------------------------------------------
// Commented due to bug:
// http://pear.php.net/bugs/bug.php?id=2146
// -------------------------------------------------------------------
// // repeatColumns / repeatRows
// if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
// // Columns to repeat
// if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
// $repeat = $phpSheet->getPageSetup()->getColumnsToRepeatAtLeft();
//
// $worksheet->repeatColumns(PHPExcel_Cell::columnIndexFromString($repeat[0] - 1), PHPExcel_Cell::columnIndexFromString($repeat[1] - 1));
// }
//
// // Rows to repeat
// if ($phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
// $repeat = $phpSheet->getPageSetup()->getRowsToRepeatAtTop();
//
// $worksheet->repeatRows($repeat[0], $repeat[1]);
// }
// }
// -------------------------------------------------------------------
// Commented due to bug:
// http://pear.php.net/bugs/bug.php?id=2146
// -------------------------------------------------------------------
// if ($phpSheet->getPageSetup()->isPrintAreaSet()) {
// // Print area
// $printArea = PHPExcel_Cell::splitRange($phpSheet->getPageSetup()->getPrintArea());
// $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
// $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
//
// $worksheet->printArea(
// $printArea[0][1],
// PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1,
// $printArea[1][1],
// PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1
// );
// }
// Support for print scale
if ($phpSheet->getPageSetup()->getScale()) {
$worksheet->setPrintScale($phpSheet->getPageSetup()->getScale());
}
// Support for fitting to pages
if ($phpSheet->getPageSetup()->getFitToWidth()) {
if ($phpSheet->getPageSetup()->getFitToHeight()) {
// Both properties are set, so use them
// Note: This case is double, see below
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight());
} else {
// Only width given, make assumption about height
$height = 0;
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $height);
}
} else if ($phpSheet->getPageSetup()->getFitToHeight()) {
if ($phpSheet->getPageSetup()->getFitToWidth()) {
// Both properties are set, so use them
// Note: This case is double, see below
$worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight());
} else {
// Only height given, make assumption about width
$width = 0;
$worksheet->fitToPages($width, $phpSheet->getPageSetup()->getFitToHeight());
}
}
// Support for breaks
$vBreaks = array();
$hBreaks = array();
foreach ($phpSheet->getBreaks() as $cell => $breakType) {
// Fetch coordinates
$coordinates = PHPExcel_Cell::coordinateFromString($cell);
// Decide what to do by the type of break
switch ($breakType) {
case PHPExcel_Worksheet::BREAK_COLUMN:
// Add to list of vertical breaks
$vBreaks[] = $coordinates[0];
break;
case PHPExcel_Worksheet::BREAK_ROW:
// Add to list of horizontal breaks
$hBreaks[] = $coordinates[1];
break;
case PHPExcel_Worksheet::BREAK_NONE:
default:
// Nothing to do
break;
}
}
$worksheet->setVPagebreaks($vBreaks);
$worksheet->setHPagebreaks($hBreaks);
}
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
$workbook->close();
}
/**
* Write Defined Name for PrintTitles
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet
* @param int $pSheetId
* @throws Exception
*/
private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
{
// definedName for PrintArea
if ($pSheet->getPageSetup()->isPrintAreaSet()) {
$objWriter->startElement('definedName');
$objWriter->writeAttribute('name', '_xlnm.Print_Area');
$objWriter->writeAttribute('localSheetId', $pSheetId);
// Setting string
$settingString = '';
// Print area
$printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
$printArea[0] = PHPExcel_Cell::absoluteCoordinate($printArea[0]);
$printArea[1] = PHPExcel_Cell::absoluteCoordinate($printArea[1]);
$objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . implode(':', $printArea));
$objWriter->endElement();
}
}
/**
* Store the MERGEDCELLS records for all ranges of merged cells
*/
private function _writeMergedCells()
{
$mergeCells = $this->_phpSheet->getMergeCells();
$countMergeCells = count($mergeCells);
if ($countMergeCells == 0) {
return;
}
// maximum allowed number of merged cells per record
$maxCountMergeCellsPerRecord = 1027;
// record identifier
$record = 0xe5;
// counter for total number of merged cells treated so far by the writer
$i = 0;
// counter for number of merged cells written in record currently being written
$j = 0;
// initialize record data
$recordData = '';
// loop through the merged cells
foreach ($mergeCells as $mergeCell) {
++$i;
++$j;
// extract the row and column indexes
$range = PHPExcel_Cell::splitRange($mergeCell);
list($first, $last) = $range[0];
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last);
$recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1);
// flush record if we have reached limit for number of merged cells, or reached final merged cell
if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) {
$recordData = pack('v', $j) . $recordData;
$length = strlen($recordData);
$header = pack('vv', $record, $length);
$this->_append($header . $recordData);
// initialize for next record, if any
$recordData = '';
$j = 0;
}
}
}
/**
* Update cell range
*
* @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3')
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell range
* @throws PHPExcel_Exception
*/
private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
// Update range
$range = PHPExcel_Cell::splitRange($pCellRange);
$ic = count($range);
for ($i = 0; $i < $ic; ++$i) {
$jc = count($range[$i]);
for ($j = 0; $j < $jc; ++$j) {
if (ctype_alpha($range[$i][$j])) {
$r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows));
$range[$i][$j] = $r[0];
} elseif (ctype_digit($range[$i][$j])) {
$r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows));
$range[$i][$j] = $r[1];
} else {
$range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
}
}
}
// Recreate range string
return PHPExcel_Cell::buildRange($range);
} else {
throw new PHPExcel_Exception("Only cell ranges may be passed to this method.");
}
}
/**
* Extract all cell references in range
*
* @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000)
* @return array Array containing single cell references
*/
public static function extractAllCellReferencesInRange($pRange = 'A1')
{
// Returnvalue
$returnValue = array();
// Explode spaces
$aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange)));
foreach ($aExplodeSpaces as $explodedSpaces) {
// Single cell?
if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) {
$col = 'A';
$row = 1;
list($col, $row) = PHPExcel_Cell::coordinateFromString($explodedSpaces);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
continue;
}
// Range...
$range = PHPExcel_Cell::splitRange($explodedSpaces);
for ($i = 0; $i < count($range); ++$i) {
// Single cell?
if (count($range[$i]) == 1) {
$col = 'A';
$row = 1;
list($col, $row) = PHPExcel_Cell::coordinateFromString($range[$i]);
if (strlen($col) <= 2) {
$returnValue[] = $explodedSpaces;
}
}
// Range...
$rangeStart = $rangeEnd = '';
$startingCol = $startingRow = $endingCol = $endingRow = 0;
list($rangeStart, $rangeEnd) = $range[$i];
list($startingCol, $startingRow) = PHPExcel_Cell::coordinateFromString($rangeStart);
list($endingCol, $endingRow) = PHPExcel_Cell::coordinateFromString($rangeEnd);
// Conversions...
$startingCol = PHPExcel_Cell::columnIndexFromString($startingCol);
$endingCol = PHPExcel_Cell::columnIndexFromString($endingCol);
// Current data
$currentCol = --$startingCol;
$currentRow = $startingRow;
// Loop cells
while ($currentCol < $endingCol) {
$loopColumn = PHPExcel_Cell::stringFromColumnIndex($currentCol);
while ($currentRow <= $endingRow) {
$returnValue[] = $loopColumn . $currentRow;
++$currentRow;
}
++$currentCol;
$currentRow = $startingRow;
}
}
}
// Return value
return $returnValue;
}
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @param boolean $resetLog Flag indicating whether calculation log should be reset or not
* @throws PHPExcel_Calculation_Exception
*/
public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE)
{
// Return value
$returnValue = array();
// echo 'extractNamedRange('.$pRange.')<br />';
if ($pSheet !== NULL) {
$pSheetName = $pSheet->getTitle();
// echo 'Current sheet name is '.$pSheetName.'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference',PHP_EOL;
list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
// echo 'New sheet name is '.$pSheetName,PHP_EOL;
// echo 'Adjusted Range reference is '.$pRange,PHP_EOL;
$pSheet = $this->_workbook->getSheetByName($pSheetName);
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== NULL) {
$pSheet = $namedRange->getWorksheet();
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
$splitRange = PHPExcel_Cell::splitRange($pRange);
// Convert row and column references
if (ctype_alpha($splitRange[0][0])) {
$pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
} elseif (ctype_digit($splitRange[0][0])) {
$pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
}
// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
// if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
// if (!$namedRange->getLocalOnly()) {
// $pSheet = $namedRange->getWorksheet();
// } else {
// return $returnValue;
// }
// }
} else {
return PHPExcel_Calculation_Functions::REF();
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
// var_dump($aReferences);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
$cellValue = NULL;
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
} else {
// Extract cell data for all cells in the range
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
$cellValue = NULL;
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = NULL;
}
}
}
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
}
/**
* Writes all the DEFINEDNAME records (BIFF8).
* So far this is only used for repeating rows/columns (print titles) and print areas
*/
private function _writeAllDefinedNamesBiff8()
{
$chunk = '';
// total number of sheets
$total_worksheets = count($this->_phpExcel->getAllSheets());
// write the print titles (repeating rows, columns), if any
for ($i = 0; $i < $total_worksheets; ++$i) {
// simultaneous repeatColumns repeatRows
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() && $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
// construct formula data manually
$formulaData = pack('Cv', 0x29, 0x17);
// tMemFunc
$formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
// tArea3d
$formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
// tArea3d
$formulaData .= pack('C', 0x10);
// tList
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
// (exclusive) either repeatColumns or repeatRows
} else {
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
// Columns to repeat
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) {
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) {
$repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
}
}
}
// write the print areas, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) {
// Print area
$printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea());
$printArea = $printArea[0];
$printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]);
$printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]);
$print_rowmin = $printArea[0][1] - 1;
$print_rowmax = $printArea[1][1] - 1;
$print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1;
$print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1;
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
}
}
return $chunk;
}
/**
* Writes all the DEFINEDNAME records (BIFF8).
* So far this is only used for repeating rows/columns (print titles) and print areas
*/
private function writeAllDefinedNamesBiff8()
{
$chunk = '';
// Named ranges
if (count($this->phpExcel->getNamedRanges()) > 0) {
// Loop named ranges
$namedRanges = $this->phpExcel->getNamedRanges();
foreach ($namedRanges as $namedRange) {
// Create absolute coordinate
$range = PHPExcel_Cell::splitRange($namedRange->getRange());
for ($i = 0; $i < count($range); $i++) {
$range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
if (isset($range[$i][1])) {
$range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
}
}
$range = PHPExcel_Cell::buildRange($range);
// e.g. Sheet1!$A$1:$B$2
// parse formula
try {
$error = $this->parser->parse($range);
$formulaData = $this->parser->toReversePolish();
// make sure tRef3d is of type tRef3dR (0x3A)
if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
$formulaData = ":" . substr($formulaData, 1);
}
if ($namedRange->getLocalOnly()) {
// local scope
$scope = $this->phpExcel->getIndex($namedRange->getScope()) + 1;
} else {
// global scope
$scope = 0;
}
$chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false));
} catch (PHPExcel_Exception $e) {
// do nothing
}
}
}
// total number of sheets
$total_worksheets = $this->phpExcel->getSheetCount();
// write the print titles (repeating rows, columns), if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
// simultaneous repeatColumns repeatRows
if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
// construct formula data manually
$formulaData = pack('Cv', 0x29, 0x17);
// tMemFunc
$formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);
// tArea3d
$formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);
// tArea3d
$formulaData .= pack('C', 0x10);
// tList
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
// (exclusive) either repeatColumns or repeatRows
} elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) {
// Columns to repeat
if ($sheetSetup->isColumnsToRepeatAtLeftSet()) {
$repeat = $sheetSetup->getColumnsToRepeatAtLeft();
$colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
$colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
} else {
$colmin = 0;
$colmax = 255;
}
// Rows to repeat
if ($sheetSetup->isRowsToRepeatAtTopSet()) {
$repeat = $sheetSetup->getRowsToRepeatAtTop();
$rowmin = $repeat[0] - 1;
$rowmax = $repeat[1] - 1;
} else {
$rowmin = 0;
$rowmax = 65535;
}
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
}
}
// write the print areas, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup();
if ($sheetSetup->isPrintAreaSet()) {
// Print area, e.g. A3:J6,H1:X20
$printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea());
$countPrintArea = count($printArea);
$formulaData = '';
for ($j = 0; $j < $countPrintArea; ++$j) {
$printAreaRect = $printArea[$j];
// e.g. A3:J6
$printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]);
$printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]);
$print_rowmin = $printAreaRect[0][1] - 1;
$print_rowmax = $printAreaRect[1][1] - 1;
$print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
$print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1;
// construct formula data manually because parser does not recognize absolute 3d cell references
$formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
if ($j > 0) {
$formulaData .= pack('C', 0x10);
// list operator token ','
}
}
// store the DEFINEDNAME record
$chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
}
}
// write autofilters, if any
for ($i = 0; $i < $total_worksheets; ++$i) {
$sheetAutoFilter = $this->phpExcel->getSheet($i)->getAutoFilter();
$autoFilterRange = $sheetAutoFilter->getRange();
if (!empty($autoFilterRange)) {
$rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange);
//Autofilter built in name
$name = pack('C', 0xd);
$chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
}
}
return $chunk;
}
/**
* Calculate information about HTML colspan and rowspan which is not always the same as Excel's
*/
private function _calculateSpans()
{
// Identify all cells that should be omitted in HTML due to cell merge.
// In HTML only the upper-left cell should be written and it should have
// appropriate rowspan / colspan attribute
$sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1);
foreach ($sheetIndexes as $sheetIndex) {
$sheet = $this->_phpExcel->getSheet($sheetIndex);
$candidateSpannedRow = array();
// loop through all Excel merged cells
foreach ($sheet->getMergeCells() as $cells) {
list($cells, ) = PHPExcel_Cell::splitRange($cells);
$first = $cells[0];
$last = $cells[1];
list($fc, $fr) = PHPExcel_Cell::coordinateFromString($first);
$fc = PHPExcel_Cell::columnIndexFromString($fc) - 1;
list($lc, $lr) = PHPExcel_Cell::coordinateFromString($last);
$lc = PHPExcel_Cell::columnIndexFromString($lc) - 1;
// loop through the individual cells in the individual merge
$r = $fr - 1;
while ($r++ < $lr) {
// also, flag this row as a HTML row that is candidate to be omitted
$candidateSpannedRow[$r] = $r;
$c = $fc - 1;
while ($c++ < $lc) {
if (!($c == $fc && $r == $fr)) {
// not the upper-left cell (should not be written in HTML)
$this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc));
} else {
// upper-left is the base cell that should hold the colspan/rowspan attribute
$this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1);
}
}
}
}
// Identify which rows should be omitted in HTML. These are the rows where all the cells
// participate in a merge and the where base cells are somewhere above.
$countColumns = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
foreach ($candidateSpannedRow as $rowIndex) {
if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) {
if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) {
$this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex;
}
}
}
// For each of the omitted rows we found above, the affected rowspans should be subtracted by 1
if (isset($this->_isSpannedRow[$sheetIndex])) {
foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) {
$adjustedBaseCells = array();
$c = -1;
$e = $countColumns - 1;
while ($c++ < $e) {
$baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell'];
if (!in_array($baseCell, $adjustedBaseCells)) {
// subtract rowspan by 1
--$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan'];
$adjustedBaseCells[] = $baseCell;
}
}
}
}
// TODO: Same for columns
}
// We have calculated the spans
$this->_spansAreCalculated = true;
}
/**
* Select a range of cells.
*
* @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6'
* @throws PHPExcel_Exception
* @return PHPExcel_Worksheet
*/
public function setSelectedCells($pCoordinate = 'A1')
{
// Uppercase coordinate
$pCoordinate = strtoupper($pCoordinate);
// Convert 'A' to 'A:A'
$pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate);
// Convert '1' to '1:1'
$pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate);
// Convert 'A:C' to 'A1:C1048576'
$pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate);
// Convert '1:3' to 'A1:XFD3'
$pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate);
if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
list($first, ) = PHPExcel_Cell::splitRange($pCoordinate);
$this->_activeCell = $first[0];
} else {
$this->_activeCell = $pCoordinate;
}
$this->_selectedCells = $pCoordinate;
return $this;
}
/**
* Extract range values
*
* @param string &$pRange String based range representation
* @param PHPExcel_Worksheet $pSheet Worksheet
* @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
* @param boolean $resetLog Flag indicating whether calculation log should be reset or not
* @throws PHPExcel_Calculation_Exception
*/
public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = null, $resetLog = true)
{
// Return value
$returnValue = array();
if ($pSheet !== null) {
$pSheetName = $pSheet->getTitle();
if (strpos($pRange, '!') !== false) {
list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
$pSheet = $this->_workbook->getSheetByName($pSheetName);
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if ($namedRange !== null) {
$pSheet = $namedRange->getWorksheet();
$pRange = $namedRange->getRange();
$splitRange = PHPExcel_Cell::splitRange($pRange);
// Convert row and column references
if (ctype_alpha($splitRange[0][0])) {
$pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow();
} elseif (ctype_digit($splitRange[0][0])) {
$pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1];
}
} else {
return PHPExcel_Calculation_Functions::REF();
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
if (!isset($aReferences[1])) {
// Single cell (or single column or row) in range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]);
$cellValue = null;
if ($pSheet->cellExists($aReferences[0])) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
} else {
// Extract cell data for all cells in the range
foreach ($aReferences as $reference) {
// Extract range
list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference);
$cellValue = null;
if ($pSheet->cellExists($reference)) {
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
} else {
$returnValue[$currentRow][$currentCol] = null;
}
}
}
}
// Return
return $returnValue;
}
/**
* Is this cell the master (top left cell) in a merge range (that holds the actual data value)
*
* @return boolean
*/
public function isMergeRangeValueCell()
{
if ($mergeRange = $this->getMergeRange()) {
$mergeRange = PHPExcel_Cell::splitRange($mergeRange);
list($startCell) = $mergeRange[0];
if ($this->getCoordinate() === $startCell) {
return true;
}
}
return false;
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet index
$sheetIndex = $pSheet->getParent()->getIndex($pSheet);
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
if (!$this->_useInlineCss) {
$cssClass = '';
$cssClass = 'column' . $colNum;
} else {
$cssClass = array();
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) {
$this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum];
}
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// initialize
$cellData = '';
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// replace leading spaces on each line with
$cellData = $this->_convertNbsp($cellData);
// convert newline "\n" to '<br>'
$cellData = str_replace("\n", '<br/>', $cellData);
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $cell->getXfIndex();
$cssClass .= ' ' . $cell->getDataType();
} else {
if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) {
$cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]);
}
// General horizontal alignment: Actual horizontal alignment depends on dataType
$sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex());
if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) {
$cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align'];
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', PHPExcel_Cell_DataType::TYPE_NULL, $pSheet);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first[0] == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
//** Necessary redundant code for the sake of PHPExcel_Writer_PDF **
// We must explicitly write the width of the <td> element because TCPDF
// does not recognize e.g. <col style="width:42pt">
$width = 0;
$columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1;
for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) {
if (isset($this->_columnWidths[$sheetIndex][$i])) {
$width += $this->_columnWidths[$sheetIndex][$i];
}
}
$cssClass['width'] = $width . 'pt';
// We must also explicitly write the height of the <td> element because TCPDF
// does not recognize e.g. <tr style="height:50pt">
if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) {
$height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'];
$cssClass['height'] = $height;
}
//** end of redundant code **
$html .= ' style="' . $this->_assembleCSS($cssClass) . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
$html .= $cellData;
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
function getValueMergedCell(PHPExcel $objPHPExcel, $indexSheet = 0, $arr_merged_allCells, $col, $row)
{
$temp = null;
$objPHPExcel->setActiveSheetIndex($indexSheet);
$cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row);
foreach ($arr_merged_allCells[$indexSheet] as $currMergedRange) {
if ($cell->isInRange($currMergedRange)) {
//возвращает массив
/* Array
(
[0] => Array
(
[0] => A25
[1] => A36 ) )*/
$currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange);
$temp = $objPHPExcel->getActiveSheet()->getCell($currMergedCellsArray[0][0])->getFormattedValue();
}
}
return $temp;
}
/**
* Write AutoFilter
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @throws Exception
*/
private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
if ($pSheet->getAutoFilter() != '') {
// autoFilter
$objWriter->startElement('autoFilter');
// Strip any worksheet reference from the filter coordinates
$range = PHPExcel_Cell::splitRange($pSheet->getAutoFilter());
$range = $range[0];
// Strip any worksheet ref
if (strpos($range[0], '!') !== false) {
list($ws, $range[0]) = explode('!', $range[0]);
}
$range = implode(':', $range);
$objWriter->writeAttribute('ref', str_replace('$', '', $range));
$objWriter->endElement();
}
}
/**
* Write Defined Name for autoFilter
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet
* @param int $pSheetId
* @throws Exception
*/
private function _writeDefinedNameForAutofilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
{
// definedName for autoFilter
if ($pSheet->getAutoFilter() != '') {
$objWriter->startElement('definedName');
$objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
$objWriter->writeAttribute('localSheetId', $pSheetId);
$objWriter->writeAttribute('hidden', '1');
// Create absolute coordinate and write as raw text
$range = PHPExcel_Cell::splitRange($pSheet->getAutoFilter());
$range[0] = PHPExcel_Cell::absoluteCoordinate($range[0]);
$range[1] = PHPExcel_Cell::absoluteCoordinate($range[1]);
$range = implode(':', $range);
$objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . $range);
$objWriter->endElement();
}
}
/**
* Write row to HTML file
*
* @param mixed $pFileHandle PHP filehandle
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @throws Exception
*/
private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (!is_null($pFileHandle) && is_array($pValues)) {
// Write row start
fwrite($pFileHandle, ' <tr class="row' . $pRow . '">' . "\r\n");
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = 'column' . $colNum;
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
}
$cellData .= $element->getText();
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode();
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
fwrite($pFileHandle, ' <td');
fwrite($pFileHandle, ' class="' . $cssClass . '"');
if ($colSpan > 1) {
fwrite($pFileHandle, ' colspan="' . $colSpan . '"');
}
if ($rowSpan > 1) {
fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"');
}
fwrite($pFileHandle, '>');
// Image?
$this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate());
// Cell data
fwrite($pFileHandle, $cellData);
// Column end
fwrite($pFileHandle, '</td>' . "\r\n");
}
// Next column
$colNum++;
}
// Write row end
fwrite($pFileHandle, ' </tr>' . "\r\n");
} else {
throw new Exception("Invalid parameters passed.");
}
}
/**
* Write AutoFilter
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @throws PHPExcel_Writer_Exception
*/
private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
$autoFilterRange = $pSheet->getAutoFilter()->getRange();
if (!empty($autoFilterRange)) {
// autoFilter
$objWriter->startElement('autoFilter');
// Strip any worksheet reference from the filter coordinates
$range = PHPExcel_Cell::splitRange($autoFilterRange);
$range = $range[0];
// Strip any worksheet ref
if (strpos($range[0], '!') !== false) {
list($ws, $range[0]) = explode('!', $range[0]);
}
$range = implode(':', $range);
$objWriter->writeAttribute('ref', str_replace('$', '', $range));
$columns = $pSheet->getAutoFilter()->getColumns();
if (count($columns > 0)) {
foreach ($columns as $columnID => $column) {
$rules = $column->getRules();
if (count($rules > 0)) {
$objWriter->startElement('filterColumn');
$objWriter->writeAttribute('colId', $pSheet->getAutoFilter()->getColumnOffset($columnID));
$objWriter->startElement($column->getFilterType());
if ($column->getJoin() == PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND) {
$objWriter->writeAttribute('and', 1);
}
foreach ($rules as $rule) {
if ($column->getFilterType() === PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER && $rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL && $rule->getValue() === '') {
// Filter rule for Blanks
$objWriter->writeAttribute('blank', 1);
} elseif ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER) {
// Dynamic Filter Rule
$objWriter->writeAttribute('type', $rule->getGrouping());
$val = $column->getAttribute('val');
if ($val !== NULL) {
$objWriter->writeAttribute('val', $val);
}
$maxVal = $column->getAttribute('maxVal');
if ($maxVal !== NULL) {
$objWriter->writeAttribute('maxVal', $maxVal);
}
} elseif ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER) {
// Top 10 Filter Rule
$objWriter->writeAttribute('val', $rule->getValue());
$objWriter->writeAttribute('percent', $rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT ? '1' : '0');
$objWriter->writeAttribute('top', $rule->getGrouping() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? '1' : '0');
} else {
// Filter, DateGroupItem or CustomFilter
$objWriter->startElement($rule->getRuleType());
if ($rule->getOperator() !== PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL) {
$objWriter->writeAttribute('operator', $rule->getOperator());
}
if ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP) {
// Date Group filters
foreach ($rule->getValue() as $key => $value) {
if ($value > '') {
$objWriter->writeAttribute($key, $value);
}
}
$objWriter->writeAttribute('dateTimeGrouping', $rule->getGrouping());
} else {
$objWriter->writeAttribute('val', $rule->getValue());
}
$objWriter->endElement();
}
}
$objWriter->endElement();
$objWriter->endElement();
}
}
}
$objWriter->endElement();
}
}
/**
* Extract all cell references in range
*
* @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000)
* @return array Array containing single cell references
*/
public static function extractAllCellReferencesInRange($pRange = 'A1')
{
// Returnvalue
$returnValue = array();
// Explode spaces
$cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange)));
foreach ($cellBlocks as $cellBlock) {
// Single cell?
if (strpos($cellBlock, ':') === false && strpos($cellBlock, ',') === false) {
$returnValue[] = $cellBlock;
continue;
}
// Range...
$ranges = PHPExcel_Cell::splitRange($cellBlock);
foreach ($ranges as $range) {
// Single cell?
if (!isset($range[1])) {
$returnValue[] = $range[0];
continue;
}
// Range...
list($rangeStart, $rangeEnd) = $range;
list($startCol, $startRow) = sscanf($rangeStart, '%[A-Z]%d');
list($endCol, $endRow) = sscanf($rangeEnd, '%[A-Z]%d');
$endCol++;
// Current data
$currentCol = $startCol;
$currentRow = $startRow;
// Loop cells
while ($currentCol != $endCol) {
while ($currentRow <= $endRow) {
$returnValue[] = $currentCol . $currentRow;
++$currentRow;
}
++$currentCol;
$currentRow = $startRow;
}
}
}
// Return value
return $returnValue;
}
/**
* Update cell range
*
* @param string $pCellRange Cell range
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell range
* @throws Exception
*/
private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) {
// Update range
$range = PHPExcel_Cell::splitRange($pCellRange);
for ($i = 0; $i < count($range); ++$i) {
for ($j = 0; $j < count($range[$i]); ++$j) {
$range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows);
}
}
// Recreate range string
return PHPExcel_Cell::buildRange($range);
} else {
throw new Exception("Only cell ranges may be passed to this method.");
}
}
/**
* Temporary method used by style supervisor. Will be removed
*
* @param string $pCell Cell (i.e. A1)
* @throws Exception
* @return PHPExcel_Worksheet
*/
public function setXSelectedCells($pCoordinate = 'A1:A1')
{
// Uppercase coordinate
$pCoordinate = strtoupper($pCoordinate);
if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) {
list($first, ) = PHPExcel_Cell::splitRange($pCoordinate);
$this->_xActiveCell = $first[0];
} else {
$this->_xActiveCell = $pCoordinate;
}
$this->_xSelectedCells = $pCoordinate;
return $this;
}
/**
* Write Defined Name for PrintTitles
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet
* @param int $pSheetId
* @throws PHPExcel_Writer_Exception
*/
private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0)
{
// definedName for PrintArea
if ($pSheet->getPageSetup()->isPrintAreaSet()) {
$objWriter->startElement('definedName');
$objWriter->writeAttribute('name', '_xlnm.Print_Area');
$objWriter->writeAttribute('localSheetId', $pSheetId);
// Setting string
$settingString = '';
// Print area
$printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea());
$chunks = array();
foreach ($printArea as $printAreaRect) {
$printAreaRect[0] = PHPExcel_Cell::absoluteReference($printAreaRect[0]);
$printAreaRect[1] = PHPExcel_Cell::absoluteReference($printAreaRect[1]);
$chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect);
}
$objWriter->writeRawData(implode(',', $chunks));
$objWriter->endElement();
}
}
/**
* Update cell range
*
* @param string $pCellRange Cell range
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to increment
* @param int $pNumRows Number of rows to increment
* @return string Updated cell range
* @throws Exception
*/
private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) {
if (strpos(':', $pCellRange) !== false) {
// Range
$referenceA = '';
$referenceB = '';
list($referenceA, $referenceB) = PHPExcel_Cell::splitRange($pCellRange);
return $this->_updateSingleCellReference($referenceA, $pBefore, $pNumCols, $pNumRows) . ':' . $this->_updateSingleCellReference($referenceB, $pBefore, $pNumCols, $pNumRows);
} else {
throw new Exception("Only cell ranges may be passed to this method.");
}
}
private function getCellValue($sheet, $cellOrCol, $row = null, $fillMerged = false, &$mergedRanges, $format = 'Y.m.d H:i:s')
{
//column set by index
if (is_numeric($cellOrCol)) {
$cell = $sheet->getCellByColumnAndRow($cellOrCol, $row);
} else {
$lastChar = substr($cellOrCol, -1, 1);
if (!is_numeric($lastChar)) {
//column contains only letter, e.g. "A"
$cellOrCol .= $row;
}
$cell = $sheet->getCell($cellOrCol);
}
/**
* для смержанных ячеек ставим одинаковое значение из первой ячейки из диапазона
* но делаем это только для вертикального смерживания
*/
if ($fillMerged) {
foreach ($mergedRanges as $currMergedRange) {
if ($cell->isInRange($currMergedRange)) {
//проверяем авляется ли смерживание вертикальным
list($a, $b) = explode(":", preg_replace("#\\d#", "", $currMergedRange));
if ($a == $b) {
$currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange);
$cell = $sheet->getCell($currMergedCellsArray[0][0]);
break;
}
}
}
}
//simple value
$val = $cell->getValue();
//date
// if(PHPExcel_Shared_Date::isDateTime($cell)) {
// $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val));
// }
//for incorrect formulas take old value
if (substr($val, 0, 1) === '=' && strlen($val) > 1) {
$val = $cell->getOldCalculatedValue();
}
$val = str_replace("\n", "{{n}}", $val);
//удаляем \n из значений, вернее меняем на {{n}}, чтобы для комментариев, востановить \n
//если для полей с условиями есть условие для bg
if ($this->_isBgConditionExists) {
$bgcolor = $cell->getStyle()->getFill()->getStartColor()->getRGB();
if ($bgcolor != '000000' && $bgcolor != 'FFFFFF') {
$val .= "{{{bg:" . $bgcolor . "}}}";
}
//добавляем в конце информацию о цвете
}
//если для полей с условиями есть условие для bold
if ($this->_isBoldConditionExists && $cell->getStyle()->getFont()->getBold()) {
$val .= "{{{format:bold}}}";
//если bold добавляем в конец
}
//если для полей с условиями есть условие для italic
if ($this->_isItalicConditionExists && $cell->getStyle()->getFont()->getItalic()) {
$val .= "{{{format:italic}}}";
//если bold добавляем в конец
}
//если для полей с условиями есть условие для underline
if ($this->_isUnderlineConditionExists && $cell->getStyle()->getFont()->getUnderline() != 'none') {
$val .= "{{{format:underline}}}";
//если bold добавляем в конец
}
// return preg_replace("#[[:blank:]]+#u", " ", $val) . " "; //добавляем в конец пробел, для случаев, когда в колонке последнее значение \
// тут не удаляем пробелы так как при обработке, иногда бывает важно их учитывать
return substr($val, -1) == "\\" ? $val . " " : $val;
//добавляем в конец пробел, для случаев, когда в колонке последнее значение \
}
/**
* Generate row
*
* @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet
* @param array $pValues Array containing cells in a row
* @param int $pRow Row number
* @return string
* @throws Exception
*/
private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0)
{
if (is_array($pValues)) {
// Construct HTML
$html = '';
// Sheet hashcode
$sheetHash = $pSheet->getHashCode();
// Write row start
if (!$this->_useInlineCss) {
$html .= ' <tr class="row' . $pRow . '">' . "\r\n";
} else {
$style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : '';
$html .= ' <tr style="' . $style . '">' . "\r\n";
}
// Write cells
$colNum = 0;
foreach ($pValues as $cell) {
$cellData = ' ';
$cssClass = '';
if (!$this->_useInlineCss) {
$cssClass = 'column' . $colNum;
} else {
$cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : '';
}
$colSpan = 1;
$rowSpan = 1;
$writeCell = true;
// Write cell
// PHPExcel_Cell
if ($cell instanceof PHPExcel_Cell) {
// Value
if ($cell->getValue() instanceof PHPExcel_RichText) {
// Loop trough rich text elements
$elements = $cell->getValue()->getRichTextElements();
foreach ($elements as $element) {
// Rich text start?
if ($element instanceof PHPExcel_RichText_Run) {
$cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">';
if ($element->getFont()->getSuperScript()) {
$cellData .= '<sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '<sub>';
}
}
}
// Convert UTF8 data to PCDATA
$cellText = $element->getText();
$cellData .= htmlspecialchars($cellText);
if ($element instanceof PHPExcel_RichText_Run) {
if ($element->getFont()->getSuperScript()) {
$cellData .= '</sup>';
} else {
if ($element->getFont()->getSubScript()) {
$cellData .= '</sub>';
}
}
$cellData .= '</span>';
}
}
} else {
if ($this->_preCalculateFormulas) {
$cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
} else {
$cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode());
}
// Convert UTF8 data to PCDATA
$cellData = htmlspecialchars($cellData);
}
// Check value
if ($cellData == '') {
$cellData = ' ';
}
// Extend CSS class?
if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) {
if (!$this->_useInlineCss) {
$cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex();
} else {
$cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : '';
}
}
} else {
$cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null);
}
// Hyperlink?
if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) {
$cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>';
}
// Column/rowspan
foreach ($pSheet->getMergeCells() as $cells) {
if ($cell->isInRange($cells)) {
list($first, ) = PHPExcel_Cell::splitRange($cells);
if ($first == $cell->getCoordinate()) {
list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);
} else {
$writeCell = false;
}
break;
}
}
// Write
if ($writeCell) {
// Column start
$html .= ' <td';
if (!$this->_useInlineCss) {
$html .= ' class="' . $cssClass . '"';
} else {
$html .= ' style="' . $cssClass . '"';
}
if ($colSpan > 1) {
$html .= ' colspan="' . $colSpan . '"';
}
if ($rowSpan > 1) {
$html .= ' rowspan="' . $rowSpan . '"';
}
$html .= '>';
// Image?
$html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate());
// Cell data
if ($this->_useInlineCss) {
$html .= '<span style="' . $cssClass . '">';
}
$html .= $cellData;
if ($this->_useInlineCss) {
$html .= '</span>';
}
// Column end
$html .= '</td>' . "\r\n";
}
// Next column
++$colNum;
}
// Write row end
$html .= ' </tr>' . "\r\n";
// Return
return $html;
} else {
throw new Exception("Invalid parameters passed.");
}
}
