34        define(
'PHPEXCEL_ROOT', dirname(__FILE__) . 
'/../../');
 
   35        require(PHPEXCEL_ROOT . 
'PHPExcel/Autoloader.php');
 
   91                                '<?xml version="1.0"',
 
   92                                '<?mso-application progid="Excel.Sheet"?>' 
  100                $data = fread($fileHandle, 2048);
 
  104                foreach($signature as $match) {
 
  106                        if (strpos(
$data, $match) === 
false) {
 
  113                if(preg_match(
'/<?xml.*encoding=[\'"](.*?)[\'"].*?>/um',
$data,$matches)) {
 
  114                        $this->_charSet = strtoupper($matches[1]);
 
  131                if (!file_exists($pFilename)) {
 
  134                if (!$this->
canRead($pFilename)) {
 
  138                $worksheetNames = array();
 
  141                $namespaces = 
$xml->getNamespaces(
true);
 
  143                $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  145                        $worksheet_ss = 
$worksheet->attributes($namespaces[
'ss']);
 
  149                return $worksheetNames;
 
  162                if (!file_exists($pFilename)) {
 
  166                $worksheetInfo = array();
 
  169                $namespaces = 
$xml->getNamespaces(
true);
 
  172                $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  174                        $worksheet_ss = 
$worksheet->attributes($namespaces[
'ss']);
 
  177                        $tmpInfo[
'worksheetName'] = 
'';
 
  178                        $tmpInfo[
'lastColumnLetter'] = 
'A';
 
  179                        $tmpInfo[
'lastColumnIndex'] = 0;
 
  180                        $tmpInfo[
'totalRows'] = 0;
 
  181                        $tmpInfo[
'totalColumns'] = 0;
 
  183                        if (isset($worksheet_ss[
'Name'])) {
 
  184                                $tmpInfo[
'worksheetName'] = (string) $worksheet_ss[
'Name'];
 
  186                                $tmpInfo[
'worksheetName'] = 
"Worksheet_{$worksheetID}";
 
  196                                        foreach($rowData->Cell as $cell) {
 
  197                                                if (isset($cell->Data)) {
 
  198                                                        $tmpInfo[
'lastColumnIndex'] = max($tmpInfo[
'lastColumnIndex'], $columnIndex);
 
  208                                                $tmpInfo[
'totalRows'] = max($tmpInfo[
'totalRows'], $rowIndex);
 
  214                        $tmpInfo[
'totalColumns'] = $tmpInfo[
'lastColumnIndex'] + 1;
 
  216                        $worksheetInfo[] = $tmpInfo;
 
  220                return $worksheetInfo;
 
  231        public function load($pFilename)
 
  243                $styleAttributeValue = strtolower($styleAttributeValue);
 
  244                foreach($styleList as 
$style) {
 
  245                        if ($styleAttributeValue == strtolower(
$style)) {
 
  246                                $styleAttributeValue = 
$style;
 
  260                $UNIT_OFFSET_MAP = array(0, 36, 73, 109, 146, 182, 219);
 
  262                $widthUnits = 256 * ($pxs / 7);
 
  263                $widthUnits += $UNIT_OFFSET_MAP[($pxs % 7)];
 
  274                $pixels = ($widthUnits / 256) * 7;
 
  275                $offsetWidthUnits = $widthUnits % 256;
 
  276                $pixels += round($offsetWidthUnits / (256 / 7));
 
  282                return chr(hexdec($hex[1]));
 
  296                $fromFormats    = array(
'\-',   
'\ ');
 
  297                $toFormats              = array(
'-',    
' ');
 
  299                $underlineStyles = array (
 
  306                $verticalAlignmentStyles = array (
 
  312                $horizontalAlignmentStyles = array (
 
  321                $timezoneObj = 
new DateTimeZone(
'Europe/London');
 
  322                $GMT = 
new DateTimeZone(
'UTC');
 
  326                if (!file_exists($pFilename)) {
 
  330                if (!$this->
canRead($pFilename)) {
 
  335                $namespaces = 
$xml->getNamespaces(
true);
 
  338                if (isset(
$xml->DocumentProperties[0])) {
 
  339                        foreach(
$xml->DocumentProperties[0] as $propertyName => $propertyValue) {
 
  340                                switch ($propertyName) {
 
  342                                                        $docProps->setTitle(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  345                                                        $docProps->setSubject(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  348                                                        $docProps->setCreator(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  351                                                        $creationDate = strtotime($propertyValue);
 
  352                                                        $docProps->setCreated($creationDate);
 
  355                                                        $docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  358                                                        $lastSaveDate = strtotime($propertyValue);
 
  359                                                        $docProps->setModified($lastSaveDate);
 
  362                                                        $docProps->setCompany(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  365                                                        $docProps->setCategory(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  368                                                        $docProps->setManager(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  371                                                        $docProps->setKeywords(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  374                                                        $docProps->setDescription(self::_convertStringEncoding($propertyValue,$this->_charSet));
 
  379                if (isset(
$xml->CustomDocumentProperties)) {
 
  380                        foreach(
$xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) {
 
  381                                $propertyAttributes = $propertyValue->attributes($namespaces[
'dt']);
 
  382                                $propertyName = preg_replace_callback(
'/_x([0-9a-z]{4})_/',
'PHPExcel_Reader_Excel2003XML::_hex2str',$propertyName);
 
  384                                switch((
string) $propertyAttributes) {
 
  387                                                $propertyValue = trim($propertyValue);
 
  391                                                $propertyValue = (bool) $propertyValue;
 
  395                                                $propertyValue = intval($propertyValue);
 
  399                                                $propertyValue = floatval($propertyValue);
 
  403                                                $propertyValue = strtotime(trim($propertyValue));
 
  406                                $docProps->setCustomProperty($propertyName,$propertyValue,$propertyType);
 
  411                        $style_ss = 
$style->attributes($namespaces[
'ss']);
 
  412                        $styleID = (string) $style_ss[
'ID'];
 
  414                        if ($styleID == 
'Default') {
 
  415                                $this->_styles[
'Default'] = array();
 
  417                                $this->_styles[$styleID] = $this->_styles[
'Default'];
 
  419                        foreach (
$style as $styleType => $styleData) {
 
  420                                $styleAttributes = $styleData->attributes($namespaces[
'ss']);
 
  422                                switch ($styleType) {
 
  424                                                        foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 
  426                                                                $styleAttributeValue = (string) $styleAttributeValue;
 
  427                                                                switch ($styleAttributeKey) {
 
  429                                                                                        if (self::identifyFixedStyleValue($verticalAlignmentStyles,$styleAttributeValue)) {
 
  430                                                                                                $this->_styles[$styleID][
'alignment'][
'vertical'] = $styleAttributeValue;
 
  434                                                                                        if (self::identifyFixedStyleValue($horizontalAlignmentStyles,$styleAttributeValue)) {
 
  435                                                                                                $this->_styles[$styleID][
'alignment'][
'horizontal'] = $styleAttributeValue;
 
  439                                                                                        $this->_styles[$styleID][
'alignment'][
'wrap'] = 
true;
 
  445                                                        foreach($styleData->Border as $borderStyle) {
 
  446                                                                $borderAttributes = $borderStyle->attributes($namespaces[
'ss']);
 
  447                                                                $thisBorder = array();
 
  448                                                                foreach($borderAttributes as $borderStyleKey => $borderStyleValue) {
 
  450                                                                        switch ($borderStyleKey) {
 
  459                                                                                                $borderPosition = strtolower($borderStyleValue);
 
  462                                                                                                $borderColour = substr($borderStyleValue,1);
 
  463                                                                                                $thisBorder[
'color'][
'rgb'] = $borderColour;
 
  467                                                                if (!empty($thisBorder)) {
 
  468                                                                        if (($borderPosition == 
'left') || ($borderPosition == 
'right') || ($borderPosition == 
'top') || ($borderPosition == 
'bottom')) {
 
  469                                                                                $this->_styles[$styleID][
'borders'][$borderPosition] = $thisBorder;
 
  475                                                        foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 
  477                                                                $styleAttributeValue = (string) $styleAttributeValue;
 
  478                                                                switch ($styleAttributeKey) {
 
  480                                                                                        $this->_styles[$styleID][
'font'][
'name'] = $styleAttributeValue;
 
  483                                                                                        $this->_styles[$styleID][
'font'][
'size'] = $styleAttributeValue;
 
  486                                                                                        $this->_styles[$styleID][
'font'][
'color'][
'rgb'] = substr($styleAttributeValue,1);
 
  489                                                                                        $this->_styles[$styleID][
'font'][
'bold'] = 
true;
 
  492                                                                                        $this->_styles[$styleID][
'font'][
'italic'] = 
true;
 
  495                                                                                        if (self::identifyFixedStyleValue($underlineStyles,$styleAttributeValue)) {
 
  496                                                                                                $this->_styles[$styleID][
'font'][
'underline'] = $styleAttributeValue;
 
  503                                                        foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 
  505                                                                switch ($styleAttributeKey) {
 
  507                                                                                        $this->_styles[$styleID][
'fill'][
'color'][
'rgb'] = substr($styleAttributeValue,1);
 
  512                                        case 'NumberFormat' :
 
  513                                                        foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 
  515                                                                $styleAttributeValue = str_replace($fromFormats,$toFormats,$styleAttributeValue);
 
  516                                                                switch ($styleAttributeValue) {
 
  518                                                                                        $styleAttributeValue = 
'dd/mm/yyyy';
 
  521                                                                if ($styleAttributeValue > 
'') {
 
  522                                                                        $this->_styles[$styleID][
'numberformat'][
'code'] = $styleAttributeValue;
 
  527                                                        foreach($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
 
  539                $xml_ss = 
$xml->children($namespaces[
'ss']);
 
  542                        $worksheet_ss = 
$worksheet->attributes($namespaces[
'ss']);
 
  544                        if ((isset($this->_loadSheetsOnly)) && (isset($worksheet_ss[
'Name'])) &&
 
  545                                (!in_array($worksheet_ss[
'Name'], $this->_loadSheetsOnly))) {
 
  554                        if (isset($worksheet_ss[
'Name'])) {
 
  559                                $objPHPExcel->getActiveSheet()->setTitle($worksheetName,
false);
 
  564                                foreach(
$worksheet->Table->Column as $columnData) {
 
  565                                        $columnData_ss = $columnData->attributes($namespaces[
'ss']);
 
  566                                        if (isset($columnData_ss[
'Index'])) {
 
  569                                        if (isset($columnData_ss[
'Width'])) {
 
  570                                                $columnWidth = $columnData_ss[
'Width'];
 
  572                                                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
 
  580                $additionalMergedCells = 0;
 
  583                                        $row_ss = $rowData->attributes($namespaces[
'ss']);
 
  584                                        if (isset($row_ss[
'Index'])) {
 
  585                                                $rowID = (integer) $row_ss[
'Index'];
 
  590                                        foreach($rowData->Cell as $cell) {
 
  592                                                $cell_ss = $cell->attributes($namespaces[
'ss']);
 
  593                                                if (isset($cell_ss[
'Index'])) {
 
  596                                                $cellRange = $columnID.$rowID;
 
  599                                                        if (!$this->
getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
 
  604                                                if ((isset($cell_ss[
'MergeAcross'])) || (isset($cell_ss[
'MergeDown']))) {
 
  605                                                        $columnTo = $columnID;
 
  606                                                        if (isset($cell_ss[
'MergeAcross'])) {
 
  607                                $additionalMergedCells += (int)$cell_ss[
'MergeAcross'];
 
  611                                                        if (isset($cell_ss[
'MergeDown'])) {
 
  612                                                                $rowTo = $rowTo + $cell_ss[
'MergeDown'];
 
  614                                                        $cellRange .= 
':'.$columnTo.$rowTo;
 
  618                                                $cellIsSet = $hasCalculatedValue = 
false;
 
  619                                                $cellDataFormula = 
'';
 
  620                                                if (isset($cell_ss[
'Formula'])) {
 
  621                                                        $cellDataFormula = $cell_ss[
'Formula'];
 
  623                                                        if (isset($cell_ss[
'ArrayRange'])) {
 
  624                                                                $cellDataCSEFormula = $cell_ss[
'ArrayRange'];
 
  627                                                        $hasCalculatedValue = 
true;
 
  629                                                if (isset($cell->Data)) {
 
  630                                                        $cellValue = $cellData = $cell->Data;
 
  632                                                        $cellData_ss = $cellData->attributes($namespaces[
'ss']);
 
  633                                                        if (isset($cellData_ss[
'Type'])) {
 
  634                                                                $cellDataType = $cellData_ss[
'Type'];
 
  635                                                                switch ($cellDataType) {
 
  651                                                                                        $cellValue = (float) $cellValue;
 
  652                                                                                        if (floor($cellValue) == $cellValue) {
 
  653                                                                                                $cellValue = (integer) $cellValue;
 
  658                                                                                        $cellValue = ($cellValue != 0);
 
  670                                                        if ($hasCalculatedValue) {
 
  674                                                                if (substr($cellDataFormula,0,3) == 
'of:') {
 
  675                                                                        $cellDataFormula = substr($cellDataFormula,3);
 
  677                                                                        $temp = explode(
'"',$cellDataFormula);
 
  679                                                                        foreach($temp as &$value) {
 
  682                                                                                        $value = str_replace(array(
'[.',
'.',
']'),
'',$value);
 
  688                                                                        $temp = explode(
'"',$cellDataFormula);
 
  690                                                                        foreach($temp as &$value) {
 
  693                                                                                        preg_match_all(
'/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
 
  697                                                                                        $cellReferences = array_reverse($cellReferences);
 
  700                                                                                        foreach($cellReferences as $cellReference) {
 
  701                                                                                                $rowReference = $cellReference[2][0];
 
  703                                                                                                if ($rowReference == 
'') $rowReference = $rowID;
 
  705                                                                                                if ($rowReference{0} == 
'[') $rowReference = $rowID + trim($rowReference,
'[]');
 
  706                                                                                                $columnReference = $cellReference[4][0];
 
  708                                                                                                if ($columnReference == 
'') $columnReference = $columnNumber;
 
  710                                                                                                if ($columnReference{0} == 
'[') $columnReference = $columnNumber + trim($columnReference,
'[]');
 
  712                                                                                                        $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
 
  719                                                                $cellDataFormula = implode(
'"',$temp);
 
  725                                                        $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue),
$type);
 
  726                                                        if ($hasCalculatedValue) {
 
  728                                                                $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setCalculatedValue($cellValue);
 
  730                                                        $cellIsSet = $rowHasData = 
true;
 
  733                                                if (isset($cell->Comment)) {
 
  735                                                        $commentAttributes = $cell->Comment->attributes($namespaces[
'ss']);
 
  737                                                        if (isset($commentAttributes->Author)) {
 
  738                                                                $author = (string)$commentAttributes->Author;
 
  741                                                        $node = $cell->Comment->Data->asXML();
 
  744                                                        $annotation = strip_tags($node);
 
  746                                                        $objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
 
  747                                                                                                                        ->setAuthor(self::_convertStringEncoding($author ,$this->_charSet))
 
  751                                                if (($cellIsSet) && (isset($cell_ss[
'StyleID']))) {
 
  752                                                        $style = (string) $cell_ss[
'StyleID'];
 
  754                                                        if ((isset($this->_styles[
$style])) && (!empty($this->_styles[
$style]))) {
 
  758                                                                if (!
$objPHPExcel->getActiveSheet()->cellExists($columnID.$rowID)) {
 
  759                                                                        $objPHPExcel->getActiveSheet()->getCell($columnID.$rowID)->setValue(NULL);
 
  761                                                                $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[
$style]);
 
  765                        while ($additionalMergedCells > 0) {
 
  767                            $additionalMergedCells--;
 
  772                                                if (isset($row_ss[
'StyleID'])) {
 
  773                                                        $rowStyle = $row_ss[
'StyleID'];
 
  775                                                if (isset($row_ss[
'Height'])) {
 
  776                                                        $rowHeight = $row_ss[
'Height'];
 
  778                                                        $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
 
  794                if ($charset != 
'UTF-8') {
 
  804                $value->createText(self::_convertStringEncoding($is,$this->_charSet));
 
An exception for terminatinating execution or to throw for unit testing.
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
static columnIndexFromString($pString='A')
Column index from string.
const PROPERTY_TYPE_BOOLEAN
constants
const PROPERTY_TYPE_UNKNOWN
const PROPERTY_TYPE_FLOAT
const PROPERTY_TYPE_INTEGER
const PROPERTY_TYPE_STRING
securityScan($xml)
Scan theXML for use of <!ENTITY to prevent XXE/XEE attacks.
getReadFilter()
Read filter.
_openFile($pFilename)
Open file for reading.
static _convertStringEncoding($string, $charset)
static _pixel2WidthUnits($pxs)
pixel units to excel width units(units of 1/256th of a character width)
static _widthUnits2Pixel($widthUnits)
excel width units(units of 1/256th of a character width) to pixel units
__construct()
Create a new PHPExcel_Reader_Excel2003XML.
static identifyFixedStyleValue($styleList, &$styleAttributeValue)
listWorksheetNames($pFilename)
Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object.
loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
Loads PHPExcel from file into PHPExcel instance.
listWorksheetInfo($pFilename)
Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
canRead($pFilename)
Can the current PHPExcel_Reader_IReader read the file?
load($pFilename)
Loads PHPExcel from file.
static getLibXmlLoaderOptions()
Get default options for libxml loader.
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
static ConvertEncoding($value, $to, $from)
Convert string from one encoding to another.
const HORIZONTAL_CENTER_CONTINUOUS
const UNDERLINE_SINGLEACCOUNTING
const UNDERLINE_DOUBLEACCOUNTING
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'