/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Create new PHPExcel
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($this->_sheetIndex);
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
// Open file
$fileHandle = fopen($pFilename, 'r');
if ($fileHandle === false) {
throw new Exception("Could not open file {$pFilename} for reading.");
}
// Loop through file
$rowData = array();
$column = $row = '';
// loop through one row (line) at a time in the file
while (($rowData = fgets($fileHandle)) !== FALSE) {
// convert SYLK encoded $rowData to UTF-8
$rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
// explode each row at semicolons while taking into account that literal semicolon (;)
// is escaped like this (;;)
$rowData = explode("\t", str_replace('¤', ';', str_replace(';', "\t", str_replace(';;', '¤', rtrim($rowData)))));
$dataType = array_shift($rowData);
// Read shared styles
if ($dataType == 'P') {
$formatArray = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'P':
$formatArray['numberformat']['code'] = str_replace($fromFormats, $toFormats, substr($rowDatum, 1));
break;
case 'E':
case 'F':
$formatArray['font']['name'] = substr($rowDatum, 1);
break;
case 'L':
$formatArray['font']['size'] = substr($rowDatum, 1);
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$formatArray['font']['italic'] = true;
break;
case 'D':
$formatArray['font']['bold'] = true;
break;
case 'T':
$formatArray['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'B':
$formatArray['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'L':
$formatArray['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'R':
$formatArray['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
}
}
break;
}
}
$this->_formats['P' . $this->_format++] = $formatArray;
// Read cell value data
} elseif ($dataType == 'C') {
$hasCalculatedValue = false;
$cellData = $cellDataFormula = '';
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'K':
$cellData = substr($rowDatum, 1);
break;
case 'E':
$cellDataFormula = '=' . substr($rowDatum, 1);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only count/replace in alternate array entries
if ($key % 2 == 0) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $row;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $row + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $column;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $column + trim($columnReference, '[]');
}
$A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
$hasCalculatedValue = true;
break;
}
}
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column - 1);
$cellData = PHPExcel_Calculation::_unwrapResult($cellData);
// Set cell value
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setValue($hasCalculatedValue ? $cellDataFormula : $cellData);
if ($hasCalculatedValue) {
$cellData = PHPExcel_Calculation::_unwrapResult($cellData);
$objPHPExcel->getActiveSheet()->getCell($columnLetter . $row)->setCalculatedValue($cellData);
}
// Read cell formatting
} elseif ($dataType == 'F') {
$formatStyle = $columnWidth = $styleSettings = '';
$styleData = array();
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
case 'P':
$formatStyle = $rowDatum;
break;
case 'W':
list($startCol, $endCol, $columnWidth) = explode(' ', substr($rowDatum, 1));
break;
case 'S':
$styleSettings = substr($rowDatum, 1);
for ($i = 0; $i < strlen($styleSettings); ++$i) {
switch ($styleSettings[$i]) {
case 'I':
$styleData['font']['italic'] = true;
break;
case 'D':
$styleData['font']['bold'] = true;
break;
case 'T':
$styleData['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'B':
$styleData['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'L':
$styleData['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
case 'R':
$styleData['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
break;
}
}
break;
}
}
if ($formatStyle > '' && $column > '' && $row > '') {
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($this->_formats[$formatStyle]);
}
if (count($styleData) > 0 && $column > '' && $row > '') {
$columnLetter = PHPExcel_Cell::stringFromColumnIndex($column - 1);
$objPHPExcel->getActiveSheet()->getStyle($columnLetter . $row)->applyFromArray($styleData);
}
if ($columnWidth > '') {
if ($startCol == $endCol) {
$startCol = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
} else {
$startCol = PHPExcel_Cell::stringFromColumnIndex($startCol - 1);
$endCol = PHPExcel_Cell::stringFromColumnIndex($endCol - 1);
$objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
do {
$objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
} while ($startCol != $endCol);
}
}
} else {
foreach ($rowData as $rowDatum) {
switch ($rowDatum[0]) {
case 'C':
case 'X':
$column = substr($rowDatum, 1);
break;
case 'R':
case 'Y':
$row = substr($rowDatum, 1);
break;
}
}
}
}
// Close file
fclose($fileHandle);
// Return
return $objPHPExcel;
}
