Something went wrong. Try again.
Open source inventory management system with php and mysql Invoice generation and easy to download invoice in PDF format Lightweight and ea…
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874<?php/** * PHPExcel * * Copyright (c) 2006 - 2014 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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.8.0, 2014-03-02 */
/** PHPExcel root directory */if (!defined('PHPEXCEL_ROOT')) { /** * @ignore */ define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../'); require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');}
/** * PHPExcel_Reader_Gnumeric * * @category PHPExcel * @package PHPExcel_Reader * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel) */class PHPExcel_Reader_Gnumeric extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader{ /** * Formats * * @var array */ private $_styles = array();
/** * Shared Expressions * * @var array */ private $_expressions = array();
private $_referenceHelper = null;
/** * Create a new PHPExcel_Reader_Gnumeric */ public function __construct() { $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter(); $this->_referenceHelper = PHPExcel_ReferenceHelper::getInstance(); }
/** * Can the current PHPExcel_Reader_IReader read the file? * * @param string $pFilename * @return boolean * @throws PHPExcel_Reader_Exception */ public function canRead($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); }
// Check if gzlib functions are available if (!function_exists('gzread')) { throw new PHPExcel_Reader_Exception("gzlib library is not enabled"); }
// Read signature data (first 3 bytes) $fh = fopen($pFilename, 'r'); $data = fread($fh, 2); fclose($fh);
if ($data != chr(0x1F).chr(0x8B)) { return false; }
return true; }
/** * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object * * @param string $pFilename * @throws PHPExcel_Reader_Exception */ public function listWorksheetNames($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); }
$xml = new XMLReader(); $xml->open( 'compress.zlib://'.realpath($pFilename), null, PHPExcel_Settings::getLibXmlLoaderOptions() ); $xml->setParserProperty(2,true);
$worksheetNames = array(); while ($xml->read()) { if ($xml->name == 'gnm:SheetName' && $xml->nodeType == XMLReader::ELEMENT) { $xml->read(); // Move onto the value node $worksheetNames[] = (string) $xml->value; } elseif ($xml->name == 'gnm:Sheets') { // break out of the loop once we've got our sheet names rather than parse the entire file break; } }
return $worksheetNames; }
/** * 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) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); }
$xml = new XMLReader(); $xml->open( 'compress.zlib://'.realpath($pFilename), null, PHPExcel_Settings::getLibXmlLoaderOptions() ); $xml->setParserProperty(2,true);
$worksheetInfo = array(); while ($xml->read()) { if ($xml->name == 'gnm:Sheet' && $xml->nodeType == XMLReader::ELEMENT) { $tmpInfo = array( 'worksheetName' => '', 'lastColumnLetter' => 'A', 'lastColumnIndex' => 0, 'totalRows' => 0, 'totalColumns' => 0, );
while ($xml->read()) { if ($xml->name == 'gnm:Name' && $xml->nodeType == XMLReader::ELEMENT) { $xml->read(); // Move onto the value node $tmpInfo['worksheetName'] = (string) $xml->value; } elseif ($xml->name == 'gnm:MaxCol' && $xml->nodeType == XMLReader::ELEMENT) { $xml->read(); // Move onto the value node $tmpInfo['lastColumnIndex'] = (int) $xml->value; $tmpInfo['totalColumns'] = (int) $xml->value + 1; } elseif ($xml->name == 'gnm:MaxRow' && $xml->nodeType == XMLReader::ELEMENT) { $xml->read(); // Move onto the value node $tmpInfo['totalRows'] = (int) $xml->value + 1; break; } } $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']); $worksheetInfo[] = $tmpInfo; } }
return $worksheetInfo; }
private function _gzfileGetContents($filename) { $file = @gzopen($filename, 'rb'); if ($file !== false) { $data = ''; while (!gzeof($file)) { $data .= gzread($file, 1024); } gzclose($file); } return $data; }
/** * 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) { // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); }
$timezoneObj = new DateTimeZone('Europe/London'); $GMT = new DateTimeZone('UTC');
$gFileData = $this->_gzfileGetContents($pFilename);
// echo '<pre>';// echo htmlentities($gFileData,ENT_QUOTES,'UTF-8');// echo '</pre><hr />';// $xml = simplexml_load_string($gFileData, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $namespacesMeta = $xml->getNamespaces(true);
// var_dump($namespacesMeta);// $gnmXML = $xml->children($namespacesMeta['gnm']);
$docProps = $objPHPExcel->getProperties(); // Document Properties are held differently, depending on the version of Gnumeric if (isset($namespacesMeta['office'])) { $officeXML = $xml->children($namespacesMeta['office']); $officeDocXML = $officeXML->{'document-meta'}; $officeDocMetaXML = $officeDocXML->meta;
foreach($officeDocMetaXML as $officePropertyData) {
$officePropertyDC = array(); if (isset($namespacesMeta['dc'])) { $officePropertyDC = $officePropertyData->children($namespacesMeta['dc']); } foreach($officePropertyDC as $propertyName => $propertyValue) { $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'title' : $docProps->setTitle(trim($propertyValue)); break; case 'subject' : $docProps->setSubject(trim($propertyValue)); break; case 'creator' : $docProps->setCreator(trim($propertyValue)); $docProps->setLastModifiedBy(trim($propertyValue)); break; case 'date' : $creationDate = strtotime(trim($propertyValue)); $docProps->setCreated($creationDate); $docProps->setModified($creationDate); break; case 'description' : $docProps->setDescription(trim($propertyValue)); break; } } $officePropertyMeta = array(); if (isset($namespacesMeta['meta'])) { $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); } foreach($officePropertyMeta as $propertyName => $propertyValue) { $attributes = $propertyValue->attributes($namespacesMeta['meta']); $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'keyword' : $docProps->setKeywords(trim($propertyValue)); break; case 'initial-creator' : $docProps->setCreator(trim($propertyValue)); $docProps->setLastModifiedBy(trim($propertyValue)); break; case 'creation-date' : $creationDate = strtotime(trim($propertyValue)); $docProps->setCreated($creationDate); $docProps->setModified($creationDate); break; case 'user-defined' : list(,$attrName) = explode(':',$attributes['name']); switch ($attrName) { case 'publisher' : $docProps->setCompany(trim($propertyValue)); break; case 'category' : $docProps->setCategory(trim($propertyValue)); break; case 'manager' : $docProps->setManager(trim($propertyValue)); break; } break; } } } } elseif (isset($gnmXML->Summary)) { foreach($gnmXML->Summary->Item as $summaryItem) { $propertyName = $summaryItem->name; $propertyValue = $summaryItem->{'val-string'}; switch ($propertyName) { case 'title' : $docProps->setTitle(trim($propertyValue)); break; case 'comments' : $docProps->setDescription(trim($propertyValue)); break; case 'keywords' : $docProps->setKeywords(trim($propertyValue)); break; case 'category' : $docProps->setCategory(trim($propertyValue)); break; case 'manager' : $docProps->setManager(trim($propertyValue)); break; case 'author' : $docProps->setCreator(trim($propertyValue)); $docProps->setLastModifiedBy(trim($propertyValue)); break; case 'company' : $docProps->setCompany(trim($propertyValue)); break; } } }
$worksheetID = 0; foreach($gnmXML->Sheets->Sheet as $sheet) { $worksheetName = (string) $sheet->Name;// echo '<b>Worksheet: ',$worksheetName,'</b><br />'; if ((isset($this->_loadSheetsOnly)) && (!in_array($worksheetName, $this->_loadSheetsOnly))) { continue; }
$maxRow = $maxCol = 0;
// Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula // cells... during the load, all formulae should be correct, and we're simply bringing the worksheet // name in line with the formula, not the reverse $objPHPExcel->getActiveSheet()->setTitle($worksheetName,false);
if ((!$this->_readDataOnly) && (isset($sheet->PrintInformation))) { if (isset($sheet->PrintInformation->Margins)) { foreach($sheet->PrintInformation->Margins->children('gnm',TRUE) as $key => $margin) { $marginAttributes = $margin->attributes(); $marginSize = 72 / 100; // Default switch($marginAttributes['PrefUnit']) { case 'mm' : $marginSize = intval($marginAttributes['Points']) / 100; break; } switch($key) { case 'top' : $objPHPExcel->getActiveSheet()->getPageMargins()->setTop($marginSize); break; case 'bottom' : $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($marginSize); break; case 'left' : $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($marginSize); break; case 'right' : $objPHPExcel->getActiveSheet()->getPageMargins()->setRight($marginSize); break; case 'header' : $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($marginSize); break; case 'footer' : $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter($marginSize); break; } } } }
foreach($sheet->Cells->Cell as $cell) { $cellAttributes = $cell->attributes(); $row = (int) $cellAttributes->Row + 1; $column = (int) $cellAttributes->Col;
if ($row > $maxRow) $maxRow = $row; if ($column > $maxCol) $maxCol = $column;
$column = PHPExcel_Cell::stringFromColumnIndex($column);
// Read cell? if ($this->getReadFilter() !== NULL) { if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) { continue; } }
$ValueType = $cellAttributes->ValueType; $ExprID = (string) $cellAttributes->ExprID;// echo 'Cell ',$column,$row,'<br />';// echo 'Type is ',$ValueType,'<br />';// echo 'Value is ',$cell,'<br />'; $type = PHPExcel_Cell_DataType::TYPE_FORMULA; if ($ExprID > '') { if (((string) $cell) > '') {
$this->_expressions[$ExprID] = array( 'column' => $cellAttributes->Col, 'row' => $cellAttributes->Row, 'formula' => (string) $cell );// echo 'NEW EXPRESSION ',$ExprID,'<br />'; } else { $expression = $this->_expressions[$ExprID];
$cell = $this->_referenceHelper->updateFormulaReferences( $expression['formula'], 'A1', $cellAttributes->Col - $expression['column'], $cellAttributes->Row - $expression['row'], $worksheetName );// echo 'SHARED EXPRESSION ',$ExprID,'<br />';// echo 'New Value is ',$cell,'<br />'; } $type = PHPExcel_Cell_DataType::TYPE_FORMULA; } else { switch($ValueType) { case '10' : // NULL $type = PHPExcel_Cell_DataType::TYPE_NULL; break; case '20' : // Boolean $type = PHPExcel_Cell_DataType::TYPE_BOOL; $cell = ($cell == 'TRUE') ? True : False; break; case '30' : // Integer $cell = intval($cell); case '40' : // Float $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; break; case '50' : // Error $type = PHPExcel_Cell_DataType::TYPE_ERROR; break; case '60' : // String $type = PHPExcel_Cell_DataType::TYPE_STRING; break; case '70' : // Cell Range case '80' : // Array } } $objPHPExcel->getActiveSheet()->getCell($column.$row)->setValueExplicit($cell,$type); }
if ((!$this->_readDataOnly) && (isset($sheet->Objects))) { foreach($sheet->Objects->children('gnm',TRUE) as $key => $comment) { $commentAttributes = $comment->attributes(); // Only comment objects are handled at the moment if ($commentAttributes->Text) { $objPHPExcel->getActiveSheet()->getComment( (string)$commentAttributes->ObjectBound ) ->setAuthor( (string)$commentAttributes->Author ) ->setText($this->_parseRichText((string)$commentAttributes->Text) ); } } }// echo '$maxCol=',$maxCol,'; $maxRow=',$maxRow,'<br />';// foreach($sheet->Styles->StyleRegion as $styleRegion) { $styleAttributes = $styleRegion->attributes(); if (($styleAttributes['startRow'] <= $maxRow) && ($styleAttributes['startCol'] <= $maxCol)) {
$startColumn = PHPExcel_Cell::stringFromColumnIndex((int) $styleAttributes['startCol']); $startRow = $styleAttributes['startRow'] + 1;
$endColumn = ($styleAttributes['endCol'] > $maxCol) ? $maxCol : (int) $styleAttributes['endCol']; $endColumn = PHPExcel_Cell::stringFromColumnIndex($endColumn); $endRow = ($styleAttributes['endRow'] > $maxRow) ? $maxRow : $styleAttributes['endRow']; $endRow += 1; $cellRange = $startColumn.$startRow.':'.$endColumn.$endRow;// echo $cellRange,'<br />';
$styleAttributes = $styleRegion->Style->attributes();// var_dump($styleAttributes);// echo '<br />';
// We still set the number format mask for date/time values, even if _readDataOnly is true if ((!$this->_readDataOnly) || (PHPExcel_Shared_Date::isDateTimeFormatCode((string) $styleAttributes['Format']))) { $styleArray = array(); $styleArray['numberformat']['code'] = (string) $styleAttributes['Format']; // If _readDataOnly is false, we set all formatting information if (!$this->_readDataOnly) { switch($styleAttributes['HAlign']) { case '1' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL; break; case '2' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT; break; case '4' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT; break; case '8' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER; break; case '16' : case '64' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS; break; case '32' : $styleArray['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY; break; }
switch($styleAttributes['VAlign']) { case '1' : $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP; break; case '2' : $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM; break; case '4' : $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER; break; case '8' : $styleArray['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY; break; }
$styleArray['alignment']['wrap'] = ($styleAttributes['WrapText'] == '1') ? True : False; $styleArray['alignment']['shrinkToFit'] = ($styleAttributes['ShrinkToFit'] == '1') ? True : False; $styleArray['alignment']['indent'] = (intval($styleAttributes["Indent"]) > 0) ? $styleAttributes["indent"] : 0;
$RGB = self::_parseGnumericColour($styleAttributes["Fore"]); $styleArray['font']['color']['rgb'] = $RGB; $RGB = self::_parseGnumericColour($styleAttributes["Back"]); $shade = $styleAttributes["Shade"]; if (($RGB != '000000') || ($shade != '0')) { $styleArray['fill']['color']['rgb'] = $styleArray['fill']['startcolor']['rgb'] = $RGB; $RGB2 = self::_parseGnumericColour($styleAttributes["PatternColor"]); $styleArray['fill']['endcolor']['rgb'] = $RGB2; switch($shade) { case '1' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID; break; case '2' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR; break; case '3' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_GRADIENT_PATH; break; case '4' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN; break; case '5' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY; break; case '6' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID; break; case '7' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL; break; case '8' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS; break; case '9' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKUP; break; case '10' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL; break; case '11' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625; break; case '12' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_GRAY125; break; case '13' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN; break; case '14' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY; break; case '15' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID; break; case '16' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL; break; case '17' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS; break; case '18' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP; break; case '19' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL; break; case '20' : $styleArray['fill']['type'] = PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY; break; } }
$fontAttributes = $styleRegion->Style->Font->attributes();// var_dump($fontAttributes);// echo '<br />'; $styleArray['font']['name'] = (string) $styleRegion->Style->Font; $styleArray['font']['size'] = intval($fontAttributes['Unit']); $styleArray['font']['bold'] = ($fontAttributes['Bold'] == '1') ? True : False; $styleArray['font']['italic'] = ($fontAttributes['Italic'] == '1') ? True : False; $styleArray['font']['strike'] = ($fontAttributes['StrikeThrough'] == '1') ? True : False; switch($fontAttributes['Underline']) { case '1' : $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE; break; case '2' : $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE; break; case '3' : $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING; break; case '4' : $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING; break; default : $styleArray['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE; break; } switch($fontAttributes['Script']) { case '1' : $styleArray['font']['superScript'] = True; break; case '-1' : $styleArray['font']['subScript'] = True; break; }
if (isset($styleRegion->Style->StyleBorder)) { if (isset($styleRegion->Style->StyleBorder->Top)) { $styleArray['borders']['top'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Top->attributes()); } if (isset($styleRegion->Style->StyleBorder->Bottom)) { $styleArray['borders']['bottom'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Bottom->attributes()); } if (isset($styleRegion->Style->StyleBorder->Left)) { $styleArray['borders']['left'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Left->attributes()); } if (isset($styleRegion->Style->StyleBorder->Right)) { $styleArray['borders']['right'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Right->attributes()); } if ((isset($styleRegion->Style->StyleBorder->Diagonal)) && (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}))) { $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes()); $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_BOTH; } elseif (isset($styleRegion->Style->StyleBorder->Diagonal)) { $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->Diagonal->attributes()); $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_UP; } elseif (isset($styleRegion->Style->StyleBorder->{'Rev-Diagonal'})) { $styleArray['borders']['diagonal'] = self::_parseBorderAttributes($styleRegion->Style->StyleBorder->{'Rev-Diagonal'}->attributes()); $styleArray['borders']['diagonaldirection'] = PHPExcel_Style_Borders::DIAGONAL_DOWN; } } if (isset($styleRegion->Style->HyperLink)) { // TO DO $hyperlink = $styleRegion->Style->HyperLink->attributes(); } }// var_dump($styleArray);// echo '<br />'; $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($styleArray); } } }
if ((!$this->_readDataOnly) && (isset($sheet->Cols))) { // Column Widths $columnAttributes = $sheet->Cols->attributes(); $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4; $c = 0; foreach($sheet->Cols->ColInfo as $columnOverride) { $columnAttributes = $columnOverride->attributes(); $column = $columnAttributes['No']; $columnWidth = $columnAttributes['Unit'] / 5.4; $hidden = ((isset($columnAttributes['Hidden'])) && ($columnAttributes['Hidden'] == '1')) ? true : false; $columnCount = (isset($columnAttributes['Count'])) ? $columnAttributes['Count'] : 1; while ($c < $column) { $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth); ++$c; } while (($c < ($column+$columnCount)) && ($c <= $maxCol)) { $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($columnWidth); if ($hidden) { $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setVisible(false); } ++$c; } } while ($c <= $maxCol) { $objPHPExcel->getActiveSheet()->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($c))->setWidth($defaultWidth); ++$c; } }
if ((!$this->_readDataOnly) && (isset($sheet->Rows))) { // Row Heights $rowAttributes = $sheet->Rows->attributes(); $defaultHeight = $rowAttributes['DefaultSizePts']; $r = 0;
foreach($sheet->Rows->RowInfo as $rowOverride) { $rowAttributes = $rowOverride->attributes(); $row = $rowAttributes['No']; $rowHeight = $rowAttributes['Unit']; $hidden = ((isset($rowAttributes['Hidden'])) && ($rowAttributes['Hidden'] == '1')) ? true : false; $rowCount = (isset($rowAttributes['Count'])) ? $rowAttributes['Count'] : 1; while ($r < $row) { ++$r; $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight); } while (($r < ($row+$rowCount)) && ($r < $maxRow)) { ++$r; $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($rowHeight); if ($hidden) { $objPHPExcel->getActiveSheet()->getRowDimension($r)->setVisible(false); } } } while ($r < $maxRow) { ++$r; $objPHPExcel->getActiveSheet()->getRowDimension($r)->setRowHeight($defaultHeight); } }
// Handle Merged Cells in this worksheet if (isset($sheet->MergedRegions)) { foreach($sheet->MergedRegions->Merge as $mergeCells) { if (strpos($mergeCells,':') !== FALSE) { $objPHPExcel->getActiveSheet()->mergeCells($mergeCells); } } }
$worksheetID++; }
// Loop through definedNames (global named ranges) if (isset($gnmXML->Names)) { foreach($gnmXML->Names->Name as $namedRange) { $name = (string) $namedRange->name; $range = (string) $namedRange->value; if (stripos($range, '#REF!') !== false) { continue; }
$range = explode('!',$range); $range[0] = trim($range[0],"'");; if ($worksheet = $objPHPExcel->getSheetByName($range[0])) { $extractedRange = str_replace('$', '', $range[1]); $objPHPExcel->addNamedRange( new PHPExcel_NamedRange($name, $worksheet, $extractedRange) ); } } }
// Return return $objPHPExcel; }
private static function _parseBorderAttributes($borderAttributes) { $styleArray = array();
if (isset($borderAttributes["Color"])) { $RGB = self::_parseGnumericColour($borderAttributes["Color"]); $styleArray['color']['rgb'] = $RGB; }
switch ($borderAttributes["Style"]) { case '0' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_NONE; break; case '1' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_THIN; break; case '2' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUM; break; case '4' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHED; break; case '5' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_THICK; break; case '6' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOUBLE; break; case '7' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_DOTTED; break; case '9' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOT; break; case '10' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT; break; case '11' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_DASHDOTDOT; break; case '12' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT; break; case '13' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT; break; case '3' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_SLANTDASHDOT; break; case '8' : $styleArray['style'] = PHPExcel_Style_Border::BORDER_MEDIUMDASHED; break; } return $styleArray; }
private function _parseRichText($is = '') { $value = new PHPExcel_RichText();
$value->createText($is);
return $value; }
private static function _parseGnumericColour($gnmColour) { list($gnmR,$gnmG,$gnmB) = explode(':',$gnmColour); $gnmR = substr(str_pad($gnmR,4,'0',STR_PAD_RIGHT),0,2); $gnmG = substr(str_pad($gnmG,4,'0',STR_PAD_RIGHT),0,2); $gnmB = substr(str_pad($gnmB,4,'0',STR_PAD_RIGHT),0,2); $RGB = $gnmR.$gnmG.$gnmB;// echo 'Excel Colour: ',$RGB,'<br />'; return $RGB; }
}