/**
* Panes are frozen? (in sheet currently being read). See WINDOW2 record.
*
* @var boolean
*/
private $_frozen;
/**
* Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
*
* @var boolean
*/
private $_isFitToPages;
/**
* Objects. One OBJ record contributes with one entry.
*
* @var array
*/
private $_objs;
/**
* Text Objects. One TXO record corresponds with one entry.
*
* @var array
*/
private $_textObjects;
/**
* Cell Annotations (BIFF8)
*
* @var array
*/
private $_cellNotes;
/**
* The combined MSODRAWINGGROUP data
*
* @var string
*/
private $_drawingGroupData;
/**
* The combined MSODRAWING data (per sheet)
*
* @var string
*/
private $_drawingData;
/**
* Keep track of XF index
*
* @var int
*/
private $_xfIndex;
/**
* Mapping of XF index (that is a cell XF) to final index in cellXf collection
*
* @var array
*/
private $_mapCellXfIndex;
/**
* Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
*
* @var array
*/
private $_mapCellStyleXfIndex;
/**
* The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
*
* @var array
*/
private $_sharedFormulas;
/**
* The shared formula parts in a sheet. One FORMULA record contributes with one value if it
* refers to a shared formula.
*
* @var array
*/
private $_sharedFormulaParts;
/**
* Read data only?
* If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
* If false (the default) it will read data and formatting.
*
* @return boolean
*/
public function getReadDataOnly()
{
return $this->_readDataOnly;
}
/**
* Set read data only
* Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
* Set to false (the default) to advise the Reader to read both data and formatting for cells.
*
* @param boolean $pValue
*
* @return PHPExcel_Reader_Excel5
*/
public function setReadDataOnly($pValue = false)
{
$this->_readDataOnly = $pValue;
return $this;
}
/**
* Get which sheets to load
* Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null
* indicating that all worksheets in the workbook should be loaded.
*
* @return mixed
*/
public function getLoadSheetsOnly()
{
return $this->_loadSheetsOnly;
}
/**
* Set which sheets to load
*
* @param mixed $value
* This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name.
* If NULL, then it tells the Reader to read all worksheets in the workbook
*
* @return PHPExcel_Reader_Excel5
*/
public function setLoadSheetsOnly($value = null)
{
$this->_loadSheetsOnly = is_array($value) ? $value : array($value);
return $this;
}
/**
* Set all sheets to load
* Tells the Reader to load all worksheets from the workbook.
*
* @return PHPExcel_Reader_Excel5
*/
public function setLoadAllSheets()
{
$this->_loadSheetsOnly = null;
return $this;
}
/**
* Read filter
*
* @return PHPExcel_Reader_IReadFilter
*/
public function getReadFilter()
{
return $this->_readFilter;
}
/**
* Set read filter
*
* @param PHPExcel_Reader_IReadFilter $pValue
* @return PHPExcel_Reader_Excel5
*/
public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue)
{
$this->_readFilter = $pValue;
return $this;
}
/**
* Create a new PHPExcel_Reader_Excel5 instance
*/
public function __construct()
{
$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
}
/**
* Can the current PHPExcel_Reader_IReader read the file?
*
* @param string $pFileName
* @return boolean
*/
public function canRead($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
try {
// Use ParseXL for the hard work.
$ole = new PHPExcel_Shared_OLERead();
// get excel data
$res = $ole->read($pFilename);
return true;
} catch (Exception $e) {
return false;
}
}
/**
* Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
*
* @param string $pFilename
* @throws Exception
*/
public function listWorksheetNames($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$worksheetNames = array();
// Read the OLE file
$this->_loadOLE($pFilename);
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
$this->_pos = 0;
$this->_sheets = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
foreach ($this->_sheets as $sheet) {
if ($sheet['sheetType'] != 0x0) {
// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
continue;
}
$worksheetNames[] = $sheet['name'];
}
return $worksheetNames;
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @return PHPExcel
* @throws Exception
*/
public function load($pFilename)
{
// Read the OLE file
$this->_loadOLE($pFilename);
// Initialisations
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// remove 1st sheet
if (!$this->_readDataOnly) {
$this->_phpExcel->removeCellStyleXfByIndex(0);
// remove the default style
$this->_phpExcel->removeCellXfByIndex(0);
// remove the default style
}
// Read the summary information stream (containing meta data)
$this->_readSummaryInformation();
// Read the Additional document summary information stream (containing application-specific meta data)
$this->_readDocumentSummaryInformation();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
// initialize
$this->_pos = 0;
$this->_codepage = 'CP1252';
$this->_formats = array();
$this->_objFonts = array();
$this->_palette = array();
$this->_sheets = array();
$this->_externalBooks = array();
$this->_ref = array();
$this->_definedname = array();
$this->_sst = array();
$this->_drawingGroupData = '';
$this->_xfIndex = '';
$this->_mapCellXfIndex = array();
$this->_mapCellStyleXfIndex = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_XFEXT:
$this->_readXfExt();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNNAME:
$this->_readExternName();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_objFonts as $objFont) {
if (isset($objFont->colorIndex)) {
$color = self::_readColor($objFont->colorIndex, $this->_palette, $this->_version);
$objFont->getColor()->setRGB($color['rgb']);
}
}
foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
// fill start and end color
$fill = $objStyle->getFill();
if (isset($fill->startcolorIndex)) {
$startColor = self::_readColor($fill->startcolorIndex, $this->_palette, $this->_version);
$fill->getStartColor()->setRGB($startColor['rgb']);
}
if (isset($fill->endcolorIndex)) {
$endColor = self::_readColor($fill->endcolorIndex, $this->_palette, $this->_version);
$fill->getEndColor()->setRGB($endColor['rgb']);
}
// border colors
$top = $objStyle->getBorders()->getTop();
$right = $objStyle->getBorders()->getRight();
$bottom = $objStyle->getBorders()->getBottom();
$left = $objStyle->getBorders()->getLeft();
$diagonal = $objStyle->getBorders()->getDiagonal();
if (isset($top->colorIndex)) {
$borderTopColor = self::_readColor($top->colorIndex, $this->_palette, $this->_version);
$top->getColor()->setRGB($borderTopColor['rgb']);
}
if (isset($right->colorIndex)) {
$borderRightColor = self::_readColor($right->colorIndex, $this->_palette, $this->_version);
$right->getColor()->setRGB($borderRightColor['rgb']);
}
if (isset($bottom->colorIndex)) {
$borderBottomColor = self::_readColor($bottom->colorIndex, $this->_palette, $this->_version);
$bottom->getColor()->setRGB($borderBottomColor['rgb']);
}
if (isset($left->colorIndex)) {
$borderLeftColor = self::_readColor($left->colorIndex, $this->_palette, $this->_version);
$left->getColor()->setRGB($borderLeftColor['rgb']);
}
if (isset($diagonal->colorIndex)) {
$borderDiagonalColor = self::_readColor($diagonal->colorIndex, $this->_palette, $this->_version);
$diagonal->getColor()->setRGB($borderDiagonalColor['rgb']);
}
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
if ($sheet['sheetType'] != 0x0) {
// 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module
continue;
}
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
$this->_phpSheet->setSheetState($sheet['sheetState']);
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
// Initialize shared formula parts
$this->_sharedFormulaParts = array();
// Initialize shared formulas
$this->_sharedFormulas = array();
// Initialize text objs
$this->_textObjects = array();
// Initialize cell annotations
$this->_cellNotes = array();
$this->textObjRef = -1;
while ($this->_pos <= $this->_dataSize - 4) {
$code = self::_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$this->_readBof();
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_SCENPROTECT:
$this->_readScenProtect();
break;
case self::XLS_Type_OBJECTPROTECT:
$this->_readObjectProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_SHAREDFMLA:
$this->_readSharedFmla();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_SELECTION:
$this->_readSelection();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Initialisations
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// remove 1st sheet
if (!$this->_readDataOnly) {
$this->_phpExcel->removeCellStyleXfByIndex(0);
// remove the default style
$this->_phpExcel->removeCellXfByIndex(0);
// remove the default style
}
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
// get excel data
$res = $this->_ole->read($pFilename);
$this->_data = $this->_ole->getWorkBook();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
// initialize
$this->_pos = 0;
$this->_codepage = 'CP1252';
$this->_formats = array();
$this->_objFonts = array();
$this->_palette = array();
$this->_sheets = array();
$this->_externalBooks = array();
$this->_ref = array();
$this->_definedname = array();
$this->_sst = array();
$this->_drawingGroupData = '';
$this->_xfIndex = '';
$this->_mapCellXfIndex = array();
$this->_mapCellStyleXfIndex = array();
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
// offset: 0; size: 2; BIFF version
$this->_version = $this->_GetInt2d($this->_data, $pos + 4);
if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) {
return false;
}
// offset: 2; size: 2; type of stream
$substreamType = $this->_GetInt2d($this->_data, $pos + 6);
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$this->_pos += 4 + $length;
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_objFonts as $objFont) {
$color = $this->_readColor($objFont->colorIndex);
$objFont->getColor()->setRGB($color['rgb']);
}
foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) {
// fill start and end color
$startColor = $this->_readColor($objStyle->getFill()->startcolorIndex);
$objStyle->getFill()->getStartColor()->setRGB($startColor['rgb']);
$endColor = $this->_readColor($objStyle->getFill()->endcolorIndex);
$objStyle->getFill()->getEndColor()->setRGB($endColor['rgb']);
// border colors
$borderTopColor = $this->_readColor($objStyle->getBorders()->getTop()->colorIndex);
$objStyle->getBorders()->getTop()->getColor()->setRGB($borderTopColor['rgb']);
$borderRightColor = $this->_readColor($objStyle->getBorders()->getRight()->colorIndex);
$objStyle->getBorders()->getRight()->getColor()->setRGB($borderRightColor['rgb']);
$borderBottomColor = $this->_readColor($objStyle->getBorders()->getBottom()->colorIndex);
$objStyle->getBorders()->getBottom()->getColor()->setRGB($borderBottomColor['rgb']);
$borderLeftColor = $this->_readColor($objStyle->getBorders()->getLeft()->colorIndex);
$objStyle->getBorders()->getLeft()->getColor()->setRGB($borderLeftColor['rgb']);
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
$this->_phpSheet->setSheetState($sheet['sheetState']);
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
// Initialize shared formula parts
$this->_sharedFormulaParts = array();
// Initialize shared formulas
$this->_sharedFormulas = array();
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
// offset: 2; size: 2; type of the following data
$substreamType = $this->_GetInt2d($recordData, 2);
if ($substreamType != self::XLS_Worksheet) {
break 2;
}
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_SHAREDFMLA:
$this->_readSharedFmla();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
case self::XLS_Type_HYPERLINK:
$this->_readHyperLink();
break;
case self::XLS_Type_SHEETLAYOUT:
$this->_readSheetLayout();
break;
case self::XLS_Type_RANGEPROTECTION:
$this->_readRangeProtection();
break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
case self::XLS_Type_CONTINUE:
$this->_readContinue();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// treat MSODRAWING records, sheet-level Escher
if (!$this->_readDataOnly && $this->_drawingData) {
$escherWorksheet = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
$escherWorksheet = $reader->load($this->_drawingData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingData);
// get all spContainers in one long array, so they can be mapped to OBJ records
$allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers();
}
// treat OBJ records
foreach ($this->_objs as $n => $obj) {
// the first shape container never has a corresponding OBJ record, hence $n + 1
$spContainer = $allSpContainers[$n + 1];
// we skip all spContainers that are a part of a group shape since we cannot yet handle those
if ($spContainer->getNestingLevel() > 1) {
continue;
}
// calculate the width and height of the shape
list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
$startOffsetX = $spContainer->getStartOffsetX();
$startOffsetY = $spContainer->getStartOffsetY();
$endOffsetX = $spContainer->getEndOffsetX();
$endOffsetY = $spContainer->getEndOffsetY();
$width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
$height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
// calculate offsetX and offsetY of the shape
$offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
$offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
switch ($obj['type']) {
case 0x8:
// picture
// get index to BSE entry (1-based)
$BSEindex = $spContainer->getOPT(0x104);
$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
$BSE = $BSECollection[$BSEindex - 1];
$blipType = $BSE->getBlipType();
// need check because some blip types are not supported by Escher reader such as EMF
if ($blip = $BSE->getBlip()) {
$ih = imagecreatefromstring($blip->getData());
$drawing = new PHPExcel_Worksheet_MemoryDrawing();
$drawing->setImageResource($ih);
// width, height, offsetX, offsetY
$drawing->setResizeProportional(false);
$drawing->setWidth($width);
$drawing->setHeight($height);
$drawing->setOffsetX($offsetX);
$drawing->setOffsetY($offsetY);
switch ($blipType) {
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
break;
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
break;
}
$drawing->setWorksheet($this->_phpSheet);
$drawing->setCoordinates($spContainer->getStartCoordinates());
}
break;
default:
// other object type
break;
}
}
// treat SHAREDFMLA records
if ($this->_version == self::XLS_BIFF8) {
foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
$formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
$this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
}
}
}
// add the named ranges (defined names)
foreach ($this->_definedname as $definedName) {
if ($definedName['isBuiltInName']) {
switch ($definedName['name']) {
case pack('C', 0x6):
// print area
// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Foo!$C$7:$J$66
// Bar!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$docSheet->getPageSetup()->setPrintArea($extractedRange);
}
}
}
break;
case pack('C', 0x7):
// print titles (repeating rows)
// Assuming BIFF8, there are 3 cases
// 1. repeating rows
// formula looks like this: Sheet!$A$1:$IV$2
// rows 1-2 repeat
// 2. repeating columns
// formula looks like this: Sheet!$A$1:$B$65536
// columns A-B repeat
// 3. both repeating rows and repeating columns
// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Sheet!$A$1:$B$65536
// Sheet!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$coordinateStrings = explode(':', $extractedRange);
if (count($coordinateStrings) == 2) {
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
if ($firstColumn == 'A' and $lastColumn == 'IV') {
// then we have repeating rows
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
} elseif ($firstRow == 1 and $lastRow == 65536) {
// then we have repeating columns
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
}
}
}
}
}
break;
}
} else {
// Extract range
$explodes = explode('!', $definedName['formula']);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, false));
}
}
}
}
return $this->_phpExcel;
}
/**
* Read SpContainer record (Shape Container)
*/
private function _readSpContainer()
{
$length = PHPExcel_Reader_Excel5::_GetInt4d($this->_data, $this->_pos + 4);
$recordData = substr($this->_data, $this->_pos + 8, $length);
// add spContainer to spgrContainer
$spContainer = new PHPExcel_Shared_Escher_DgContainer_SpgrContainer_SpContainer();
$this->_object->addChild($spContainer);
// move stream pointer to next record
$this->_pos += 8 + $length;
// record is a container, read contents
$reader = new PHPExcel_Reader_Excel5_Escher($spContainer);
$escher = $reader->load($recordData);
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$this->_phpExcel = new PHPExcel();
$this->_phpExcel->removeSheetByIndex(0);
// Use ParseXL for the hard work.
$this->_ole = new PHPExcel_Shared_OLERead();
$this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
// get excel data
$res = $this->_ole->read($pFilename);
// oops, something goes wrong (Darko Miljanovic)
if ($res === false) {
// check error code
if ($this->_ole->error == 1) {
// bad file
throw new Exception('The filename ' . $pFilename . ' is not readable');
} elseif ($this->_ole->error == 2) {
throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
}
// check other error codes here (eg bad fileformat, etc...)
}
$this->_data = $this->_ole->getWorkBook();
// total byte size of Excel data (workbook global substream + sheet substreams)
$this->_dataSize = strlen($this->_data);
$this->_pos = 0;
// Parse Workbook Global Substream
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$pos = $this->_pos;
$length = $this->_GetInt2d($this->_data, $pos + 2);
$recordData = substr($this->_data, $pos + 4, $length);
// offset: 0; size: 2; BIFF version
$this->_version = $this->_GetInt2d($this->_data, $pos + 4);
if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) {
return false;
}
// offset: 2; size: 2; type of stream
$substreamType = $this->_GetInt2d($this->_data, $pos + 6);
if ($substreamType != self::XLS_WorkbookGlobals) {
return false;
}
$this->_pos += 4 + $length;
break;
case self::XLS_Type_FILEPASS:
$this->_readFilepass();
break;
case self::XLS_Type_CODEPAGE:
$this->_readCodepage();
break;
case self::XLS_Type_DATEMODE:
$this->_readDateMode();
break;
case self::XLS_Type_FONT:
$this->_readFont();
break;
case self::XLS_Type_FORMAT:
$this->_readFormat();
break;
case self::XLS_Type_XF:
$this->_readXf();
break;
case self::XLS_Type_STYLE:
$this->_readStyle();
break;
case self::XLS_Type_PALETTE:
$this->_readPalette();
break;
case self::XLS_Type_SHEET:
$this->_readSheet();
break;
case self::XLS_Type_EXTERNALBOOK:
$this->_readExternalBook();
break;
case self::XLS_Type_EXTERNSHEET:
$this->_readExternSheet();
break;
case self::XLS_Type_DEFINEDNAME:
$this->_readDefinedName();
break;
case self::XLS_Type_MSODRAWINGGROUP:
$this->_readMsoDrawingGroup();
break;
case self::XLS_Type_SST:
$this->_readSst();
break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// Resolve indexed colors for font, fill, and border colors
// Cannot be resolved already in XF record, because PALETTE record comes afterwards
if (!$this->_readDataOnly) {
foreach ($this->_fonts as &$font) {
$font['color'] = $this->_readColor($font['colorIndex']);
}
foreach ($this->_xf as &$xf) {
// fonts
$xf['font']['color'] = $this->_readColor($xf['font']['colorIndex']);
// fill start and end color
$xf['fill']['startcolor'] = $this->_readColor($xf['fill']['startcolorIndex']);
$xf['fill']['endcolor'] = $this->_readColor($xf['fill']['endcolorIndex']);
// border colors
$xf['borders']['top']['color'] = $this->_readColor($xf['borders']['top']['colorIndex']);
$xf['borders']['right']['color'] = $this->_readColor($xf['borders']['right']['colorIndex']);
$xf['borders']['bottom']['color'] = $this->_readColor($xf['borders']['bottom']['colorIndex']);
$xf['borders']['left']['color'] = $this->_readColor($xf['borders']['left']['colorIndex']);
}
foreach ($this->_builtInStyles as &$builtInStyle) {
// fonts
$builtInStyle['font']['color'] = $this->_readColor($builtInStyle['font']['colorIndex']);
// fill start and end color
$builtInStyle['fill']['startcolor'] = $this->_readColor($builtInStyle['fill']['startcolorIndex']);
$builtInStyle['fill']['endcolor'] = $this->_readColor($builtInStyle['fill']['endcolorIndex']);
// border colors
$builtInStyle['borders']['top']['color'] = $this->_readColor($builtInStyle['borders']['top']['colorIndex']);
$builtInStyle['borders']['right']['color'] = $this->_readColor($builtInStyle['borders']['right']['colorIndex']);
$builtInStyle['borders']['bottom']['color'] = $this->_readColor($builtInStyle['borders']['bottom']['colorIndex']);
$builtInStyle['borders']['left']['color'] = $this->_readColor($builtInStyle['borders']['left']['colorIndex']);
}
}
// treat MSODRAWINGGROUP records, workbook-level Escher
if (!$this->_readDataOnly && $this->_drawingGroupData) {
$escherWorkbook = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook);
$escherWorkbook = $reader->load($this->_drawingGroupData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingGroupData);
}
// Parse the individual sheets
foreach ($this->_sheets as $sheet) {
// check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) {
continue;
}
// add sheet to PHPExcel object
$this->_phpSheet = $this->_phpExcel->createSheet();
$this->_phpSheet->setTitle($sheet['name']);
// default style
if (!$this->_readDataOnly && isset($this->_builtInStyles[0])) {
$this->_phpSheet->getDefaultStyle()->applyFromArray($this->_builtInStyles[0]);
}
$this->_pos = $sheet['offset'];
// Initialize isFitToPages. May change after reading SHEETPR record.
$this->_isFitToPages = false;
// Initialize drawingData
$this->_drawingData = '';
// Initialize objs
$this->_objs = array();
while ($this->_pos < $this->_dataSize) {
$code = $this->_GetInt2d($this->_data, $this->_pos);
switch ($code) {
case self::XLS_Type_BOF:
$length = $this->_GetInt2d($this->_data, $this->_pos + 2);
$recordData = substr($this->_data, $this->_pos + 4, $length);
// move stream pointer to next record
$this->_pos += 4 + $length;
// do not use this version information for anything
// it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream
// offset: 2; size: 2; type of the following data
$substreamType = $this->_GetInt2d($recordData, 2);
if ($substreamType != self::XLS_Worksheet) {
break 2;
}
break;
case self::XLS_Type_PRINTGRIDLINES:
$this->_readPrintGridlines();
break;
case self::XLS_Type_DEFAULTROWHEIGHT:
$this->_readDefaultRowHeight();
break;
case self::XLS_Type_SHEETPR:
$this->_readSheetPr();
break;
case self::XLS_Type_HORIZONTALPAGEBREAKS:
$this->_readHorizontalPageBreaks();
break;
case self::XLS_Type_VERTICALPAGEBREAKS:
$this->_readVerticalPageBreaks();
break;
case self::XLS_Type_HEADER:
$this->_readHeader();
break;
case self::XLS_Type_FOOTER:
$this->_readFooter();
break;
case self::XLS_Type_HCENTER:
$this->_readHcenter();
break;
case self::XLS_Type_VCENTER:
$this->_readVcenter();
break;
case self::XLS_Type_LEFTMARGIN:
$this->_readLeftMargin();
break;
case self::XLS_Type_RIGHTMARGIN:
$this->_readRightMargin();
break;
case self::XLS_Type_TOPMARGIN:
$this->_readTopMargin();
break;
case self::XLS_Type_BOTTOMMARGIN:
$this->_readBottomMargin();
break;
case self::XLS_Type_PAGESETUP:
$this->_readPageSetup();
break;
case self::XLS_Type_PROTECT:
$this->_readProtect();
break;
case self::XLS_Type_PASSWORD:
$this->_readPassword();
break;
case self::XLS_Type_DEFCOLWIDTH:
$this->_readDefColWidth();
break;
case self::XLS_Type_COLINFO:
$this->_readColInfo();
break;
case self::XLS_Type_DIMENSION:
$this->_readDefault();
break;
case self::XLS_Type_ROW:
$this->_readRow();
break;
case self::XLS_Type_DBCELL:
$this->_readDefault();
break;
case self::XLS_Type_RK:
$this->_readRk();
break;
case self::XLS_Type_LABELSST:
$this->_readLabelSst();
break;
case self::XLS_Type_MULRK:
$this->_readMulRk();
break;
case self::XLS_Type_NUMBER:
$this->_readNumber();
break;
case self::XLS_Type_FORMULA:
$this->_readFormula();
break;
case self::XLS_Type_BOOLERR:
$this->_readBoolErr();
break;
case self::XLS_Type_MULBLANK:
$this->_readMulBlank();
break;
case self::XLS_Type_LABEL:
$this->_readLabel();
break;
case self::XLS_Type_BLANK:
$this->_readBlank();
break;
case self::XLS_Type_MSODRAWING:
$this->_readMsoDrawing();
break;
case self::XLS_Type_OBJ:
$this->_readObj();
break;
case self::XLS_Type_WINDOW2:
$this->_readWindow2();
break;
case self::XLS_Type_SCL:
$this->_readScl();
break;
case self::XLS_Type_PANE:
$this->_readPane();
break;
case self::XLS_Type_MERGEDCELLS:
$this->_readMergedCells();
break;
case self::XLS_Type_HYPERLINK:
$this->_readHyperLink();
break;
case self::XLS_Type_RANGEPROTECTION:
$this->_readRangeProtection();
break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
//case self::XLS_Type_IMDATA: $this->_readImData(); break;
case self::XLS_Type_EOF:
$this->_readDefault();
break 2;
default:
$this->_readDefault();
break;
}
}
// treat MSODRAWING records, sheet-level Escher
if (!$this->_readDataOnly && $this->_drawingData) {
$escherWorksheet = new PHPExcel_Shared_Escher();
$reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet);
$escherWorksheet = $reader->load($this->_drawingData);
// debug Escher stream
//$debug = new Debug_Escher(new PHPExcel_Shared_Escher());
//$debug->load($this->_drawingData);
$spContainerCollection = $escherWorksheet->getDgContainer()->getSpgrContainer()->getSpContainerCollection();
}
// treat OBJ records
foreach ($this->_objs as $n => $obj) {
// skip first shape container which holds the shape group, hence $n + 1
$spContainer = $spContainerCollection[$n + 1];
switch ($obj['type']) {
case 0x8:
// picture
// get index to BSE entry (1-based)
$BSEindex = $spContainer->getOPT(0x104);
$BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
$BSE = $BSECollection[$BSEindex - 1];
$blipType = $BSE->getBlipType();
$blip = $BSE->getBlip();
$ih = imagecreatefromstring($blip->getData());
$drawing = new PHPExcel_Worksheet_MemoryDrawing();
$drawing->setImageResource($ih);
switch ($blipType) {
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
break;
case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
$drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
$drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
break;
}
$drawing->setWorksheet($this->_phpSheet);
$drawing->setCoordinates($spContainer->getStartCoordinates());
break;
default:
// other object type
break;
}
}
}
// add the named ranges (defined names)
foreach ($this->_definedname as $definedName) {
if ($definedName['isBuiltInName']) {
switch ($definedName['name']) {
case pack('C', 0x6):
// print area
// in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Foo!$C$7:$J$66
// Bar!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$docSheet->getPageSetup()->setPrintArea($extractedRange);
}
}
}
break;
case pack('C', 0x7):
// print titles (repeating rows)
// Assuming BIFF8, there are 3 cases
// 1. repeating rows
// formula looks like this: Sheet!$A$1:$IV$2
// rows 1-2 repeat
// 2. repeating columns
// formula looks like this: Sheet!$A$1:$B$65536
// columns A-B repeat
// 3. both repeating rows and repeating columns
// formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2
$ranges = explode(',', $definedName['formula']);
// FIXME: what if sheetname contains comma?
foreach ($ranges as $range) {
// $range should look like this one of these
// Sheet!$A$1:$B$65536
// Sheet!$A$1:$IV$2
$explodes = explode('!', $range);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$coordinateStrings = explode(':', $extractedRange);
if (count($coordinateStrings) == 2) {
list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]);
list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]);
if ($firstColumn == 'A' and $lastColumn == 'IV') {
// then we have repeating rows
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow));
} elseif ($firstRow == 1 and $lastRow == 65536) {
// then we have repeating columns
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn));
}
}
}
}
}
break;
}
} else {
// Extract range
$explodes = explode('!', $definedName['formula']);
if (count($explodes) == 2) {
if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) {
$extractedRange = $explodes[1];
$extractedRange = str_replace('$', '', $extractedRange);
$this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, true));
}
}
}
}
return $this->_phpExcel;
}