New file |
0,0 → 1,450 |
<?php |
/** |
* PHPExcel |
* |
* Copyright (c) 2006 - 2013 PHPExcel |
* |
* This library is free software; you can redistribute it and/or |
* modify it under the terms of the GNU Lesser General Public |
* License as published by the Free Software Foundation; either |
* version 2.1 of the License, or (at your option) any later version. |
* |
* This library is distributed in the hope that it will be useful, |
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
* Lesser General Public License for more details. |
* |
* You should have received a copy of the GNU Lesser General Public |
* License along with this library; if not, write to the Free Software |
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
* |
* @category PHPExcel |
* @package PHPExcel_Reader |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL |
* @version ##VERSION##, ##DATE## |
*/ |
|
|
/** PHPExcel root directory */ |
if (!defined('PHPEXCEL_ROOT')) { |
/** |
* @ignore |
*/ |
define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../'); |
require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php'); |
} |
|
/** |
* PHPExcel_Reader_SYLK |
* |
* @category PHPExcel |
* @package PHPExcel_Reader |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
*/ |
class PHPExcel_Reader_SYLK extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader |
{ |
/** |
* Input encoding |
* |
* @var string |
*/ |
private $_inputEncoding = 'ANSI'; |
|
/** |
* Sheet index to read |
* |
* @var int |
*/ |
private $_sheetIndex = 0; |
|
/** |
* Formats |
* |
* @var array |
*/ |
private $_formats = array(); |
|
/** |
* Format Count |
* |
* @var int |
*/ |
private $_format = 0; |
|
/** |
* Create a new PHPExcel_Reader_SYLK |
*/ |
public function __construct() { |
$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter(); |
} |
|
/** |
* Validate that the current file is a SYLK file |
* |
* @return boolean |
*/ |
protected function _isValidFormat() |
{ |
// Read sample data (first 2 KB will do) |
$data = fread($this->_fileHandle, 2048); |
|
// Count delimiters in file |
$delimiterCount = substr_count($data, ';'); |
if ($delimiterCount < 1) { |
return FALSE; |
} |
|
// Analyze first line looking for ID; signature |
$lines = explode("\n", $data); |
if (substr($lines[0],0,4) != 'ID;P') { |
return FALSE; |
} |
|
return TRUE; |
} |
|
/** |
* Set input encoding |
* |
* @param string $pValue Input encoding |
*/ |
public function setInputEncoding($pValue = 'ANSI') |
{ |
$this->_inputEncoding = $pValue; |
return $this; |
} |
|
/** |
* Get input encoding |
* |
* @return string |
*/ |
public function getInputEncoding() |
{ |
return $this->_inputEncoding; |
} |
|
/** |
* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns) |
* |
* @param string $pFilename |
* @throws PHPExcel_Reader_Exception |
*/ |
public function listWorksheetInfo($pFilename) |
{ |
// Open file |
$this->_openFile($pFilename); |
if (!$this->_isValidFormat()) { |
fclose ($this->_fileHandle); |
throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); |
} |
$fileHandle = $this->_fileHandle; |
rewind($fileHandle); |
|
$worksheetInfo = array(); |
$worksheetInfo[0]['worksheetName'] = 'Worksheet'; |
$worksheetInfo[0]['lastColumnLetter'] = 'A'; |
$worksheetInfo[0]['lastColumnIndex'] = 0; |
$worksheetInfo[0]['totalRows'] = 0; |
$worksheetInfo[0]['totalColumns'] = 0; |
|
// Loop through file |
$rowData = array(); |
|
// loop through one row (line) at a time in the file |
$rowIndex = 0; |
while (($rowData = fgets($fileHandle)) !== FALSE) { |
$columnIndex = 0; |
|
// 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); |
if ($dataType == 'C') { |
// Read cell value data |
foreach($rowData as $rowDatum) { |
switch($rowDatum{0}) { |
case 'C' : |
case 'X' : |
$columnIndex = substr($rowDatum,1) - 1; |
break; |
case 'R' : |
case 'Y' : |
$rowIndex = substr($rowDatum,1); |
break; |
} |
|
$worksheetInfo[0]['totalRows'] = max($worksheetInfo[0]['totalRows'], $rowIndex); |
$worksheetInfo[0]['lastColumnIndex'] = max($worksheetInfo[0]['lastColumnIndex'], $columnIndex); |
} |
} |
} |
|
$worksheetInfo[0]['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex']); |
$worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1; |
|
// Close file |
fclose($fileHandle); |
|
return $worksheetInfo; |
} |
|
/** |
* Loads PHPExcel from file |
* |
* @param string $pFilename |
* @return PHPExcel |
* @throws PHPExcel_Reader_Exception |
*/ |
public function load($pFilename) |
{ |
// Create new PHPExcel |
$objPHPExcel = new PHPExcel(); |
|
// Load into this instance |
return $this->loadIntoExisting($pFilename, $objPHPExcel); |
} |
|
/** |
* Loads PHPExcel from file into PHPExcel instance |
* |
* @param string $pFilename |
* @param PHPExcel $objPHPExcel |
* @return PHPExcel |
* @throws PHPExcel_Reader_Exception |
*/ |
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) |
{ |
// Open file |
$this->_openFile($pFilename); |
if (!$this->_isValidFormat()) { |
fclose ($this->_fileHandle); |
throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); |
} |
$fileHandle = $this->_fileHandle; |
rewind($fileHandle); |
|
// Create new PHPExcel |
while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) { |
$objPHPExcel->createSheet(); |
} |
$objPHPExcel->setActiveSheetIndex( $this->_sheetIndex ); |
|
$fromFormats = array('\-', '\ '); |
$toFormats = array('-', ' '); |
|
// 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); |
$key = false; |
foreach($temp as &$value) { |
// Only count/replace in alternate array entries |
if ($key = !$key) { |
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); |
if (isset($this->_formats[$formatStyle])) { |
$objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($this->_formats[$formatStyle]); |
} |
} |
if ((!empty($styleData)) && ($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; |
} |
|
/** |
* Get sheet index |
* |
* @return int |
*/ |
public function getSheetIndex() { |
return $this->_sheetIndex; |
} |
|
/** |
* Set sheet index |
* |
* @param int $pValue Sheet index |
* @return PHPExcel_Reader_SYLK |
*/ |
public function setSheetIndex($pValue = 0) { |
$this->_sheetIndex = $pValue; |
return $this; |
} |
|
} |