/** * 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'); $zipClass = PHPExcel_Settings::getZipClass(); $zip = new $zipClass(); if (!$zip->open($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file."); } // echo '<h1>Meta Information</h1>'; $xml = simplexml_load_string($zip->getFromName("meta.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $namespacesMeta = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesMeta); // echo '</pre><hr />'; $docProps = $objPHPExcel->getProperties(); $officeProperty = $xml->children($namespacesMeta['office']); foreach ($officeProperty 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($propertyValue); break; case 'subject': $docProps->setSubject($propertyValue); break; case 'creator': $docProps->setCreator($propertyValue); $docProps->setLastModifiedBy($propertyValue); break; case 'date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); $docProps->setModified($creationDate); break; case 'description': $docProps->setDescription($propertyValue); break; } } $officePropertyMeta = array(); if (isset($namespacesMeta['dc'])) { $officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']); } foreach ($officePropertyMeta as $propertyName => $propertyValue) { $propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']); $propertyValue = (string) $propertyValue; switch ($propertyName) { case 'initial-creator': $docProps->setCreator($propertyValue); break; case 'keyword': $docProps->setKeywords($propertyValue); break; case 'creation-date': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'user-defined': $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; foreach ($propertyValueAttributes as $key => $value) { if ($key == 'name') { $propertyValueName = (string) $value; } elseif ($key == 'value-type') { switch ($value) { case 'date': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'date'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE; break; case 'boolean': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'bool'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN; break; case 'float': $propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue, 'r4'); $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT; break; default: $propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; } } } $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); break; } } } // echo '<h1>Workbook Content</h1>'; $xml = simplexml_load_string($zip->getFromName("content.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $namespacesContent = $xml->getNamespaces(true); // echo '<pre>'; // print_r($namespacesContent); // echo '</pre><hr />'; $workbook = $xml->children($namespacesContent['office']); foreach ($workbook->body->spreadsheet as $workbookData) { $workbookData = $workbookData->children($namespacesContent['table']); $worksheetID = 0; foreach ($workbookData->table as $worksheetDataSet) { $worksheetData = $worksheetDataSet->children($namespacesContent['table']); // print_r($worksheetData); // echo '<br />'; $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']); // print_r($worksheetDataAttributes); // echo '<br />'; if (isset($this->_loadSheetsOnly) && isset($worksheetDataAttributes['name']) && !in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly)) { continue; } // echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>'; // Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); if (isset($worksheetDataAttributes['name'])) { $worksheetName = (string) $worksheetDataAttributes['name']; // 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); } $rowID = 1; foreach ($worksheetData as $key => $rowData) { // echo '<b>'.$key.'</b><br />'; switch ($key) { case 'table-header-rows': foreach ($rowData as $key => $cellData) { $rowData = $cellData; break; } case 'table-row': $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']); $rowRepeats = isset($rowDataTableAttributes['number-rows-repeated']) ? $rowDataTableAttributes['number-rows-repeated'] : 1; $columnID = 'A'; foreach ($rowData as $key => $cellData) { if ($this->getReadFilter() !== NULL) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } // echo '<b>'.$columnID.$rowID.'</b><br />'; $cellDataText = isset($namespacesContent['text']) ? $cellData->children($namespacesContent['text']) : ''; $cellDataOffice = $cellData->children($namespacesContent['office']); $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']); $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']); // echo 'Office Attributes: '; // print_r($cellDataOfficeAttributes); // echo '<br />Table Attributes: '; // print_r($cellDataTableAttributes); // echo '<br />Cell Data Text'; // print_r($cellDataText); // echo '<br />'; // $type = $formatting = $hyperlink = null; $hasCalculatedValue = false; $cellDataFormula = ''; if (isset($cellDataTableAttributes['formula'])) { $cellDataFormula = $cellDataTableAttributes['formula']; $hasCalculatedValue = true; } if (isset($cellDataOffice->annotation)) { // echo 'Cell has comment<br />'; $annotationText = $cellDataOffice->annotation->children($namespacesContent['text']); $textArray = array(); foreach ($annotationText as $t) { foreach ($t->span as $text) { $textArray[] = (string) $text; } } $text = implode("\n", $textArray); // echo $text,'<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->_parseRichText($text)); } if (isset($cellDataText->p)) { // Consolidate if there are multiple p records (maybe with spans as well) $dataArray = array(); // Text can have multiple text:p and within those, multiple text:span. // text:p newlines, but text:span does not. // Also, here we assume there is no text data is span fields are specified, since // we have no way of knowing proper positioning anyway. foreach ($cellDataText->p as $pData) { if (isset($pData->span)) { // span sections do not newline, so we just create one large string here $spanSection = ""; foreach ($pData->span as $spanData) { $spanSection .= $spanData; } array_push($dataArray, $spanSection); } else { array_push($dataArray, $pData); } } $allCellDataText = implode($dataArray, "\n"); // echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />'; switch ($cellDataOfficeAttributes['value-type']) { case 'string': $type = PHPExcel_Cell_DataType::TYPE_STRING; $dataValue = $allCellDataText; if (isset($dataValue->a)) { $dataValue = $dataValue->a; $cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']); $hyperlink = $cellXLinkAttributes['href']; } break; case 'boolean': $type = PHPExcel_Cell_DataType::TYPE_BOOL; $dataValue = $allCellDataText == 'TRUE' ? True : False; break; case 'percentage': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00; break; case 'currency': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { $dataValue = (int) $dataValue; } $formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE; break; case 'float': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = (double) $cellDataOfficeAttributes['value']; if (floor($dataValue) == $dataValue) { if ($dataValue == (int) $dataValue) { $dataValue = (int) $dataValue; } else { $dataValue = (double) $dataValue; } } break; case 'date': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT); $dateObj->setTimeZone($timezoneObj); list($year, $month, $day, $hour, $minute, $second) = explode(' ', $dateObj->format('Y m d H i s')); $dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hour, $minute, $second); if ($dataValue != floor($dataValue)) { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15 . ' ' . PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; } else { $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15; } break; case 'time': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 ' . implode(':', sscanf($cellDataOfficeAttributes['time-value'], 'PT%dH%dM%dS')))); $formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4; break; } // echo 'Data value is '.$dataValue.'<br />'; // if ($hyperlink !== NULL) { // echo 'Hyperlink is '.$hyperlink.'<br />'; // } } else { $type = PHPExcel_Cell_DataType::TYPE_NULL; $dataValue = NULL; } if ($hasCalculatedValue) { $type = PHPExcel_Cell_DataType::TYPE_FORMULA; // echo 'Formula: ', $cellDataFormula, PHP_EOL; $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); $temp = explode('"', $cellDataFormula); $tKey = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($tKey = !$tKey) { $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1!$2:$3', $value); // Cell range reference in another sheet $value = preg_replace('/\\[([^\\.]+)\\.([^\\.]+)\\]/Ui', '$1!$2', $value); // Cell reference in another sheet $value = preg_replace('/\\[\\.([^\\.]+):\\.([^\\.]+)\\]/Ui', '$1:$2', $value); // Cell range reference $value = preg_replace('/\\[\\.([^\\.]+)\\]/Ui', '$1', $value); // Simple cell reference $value = PHPExcel_Calculation::_translateSeparator(';', ',', $value, $inBraces); } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL; } $colRepeats = isset($cellDataTableAttributes['number-columns-repeated']) ? $cellDataTableAttributes['number-columns-repeated'] : 1; if ($type !== NULL) { for ($i = 0; $i < $colRepeats; ++$i) { if ($i > 0) { ++$columnID; } if ($type !== PHPExcel_Cell_DataType::TYPE_NULL) { for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) { $rID = $rowID + $rowAdjust; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $dataValue, $type); if ($hasCalculatedValue) { // echo 'Forumla result is '.$dataValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->setCalculatedValue($dataValue); } if ($formatting !== NULL) { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode($formatting); } else { $objPHPExcel->getActiveSheet()->getStyle($columnID . $rID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); } if ($hyperlink !== NULL) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rID)->getHyperlink()->setUrl($hyperlink); } } } } } // Merged cells if (isset($cellDataTableAttributes['number-columns-spanned']) || isset($cellDataTableAttributes['number-rows-spanned'])) { if ($type !== PHPExcel_Cell_DataType::TYPE_NULL || !$this->_readDataOnly) { $columnTo = $columnID; if (isset($cellDataTableAttributes['number-columns-spanned'])) { $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] - 2); } $rowTo = $rowID; if (isset($cellDataTableAttributes['number-rows-spanned'])) { $rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1; } $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo; $objPHPExcel->getActiveSheet()->mergeCells($cellRange); } } ++$columnID; } $rowID += $rowRepeats; break; } } ++$worksheetID; } } // Return return $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) { $fromFormats = array('\\-', '\\ '); $toFormats = array('-', ' '); $underlineStyles = array(PHPExcel_Style_Font::UNDERLINE_NONE, PHPExcel_Style_Font::UNDERLINE_DOUBLE, PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, PHPExcel_Style_Font::UNDERLINE_SINGLE, PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING); $verticalAlignmentStyles = array(PHPExcel_Style_Alignment::VERTICAL_BOTTOM, PHPExcel_Style_Alignment::VERTICAL_TOP, PHPExcel_Style_Alignment::VERTICAL_CENTER, PHPExcel_Style_Alignment::VERTICAL_JUSTIFY); $horizontalAlignmentStyles = array(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, PHPExcel_Style_Alignment::HORIZONTAL_LEFT, PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, PHPExcel_Style_Alignment::HORIZONTAL_CENTER, PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); $timezoneObj = new DateTimeZone('Europe/London'); $GMT = new DateTimeZone('UTC'); // Check if file exists if (!file_exists($pFilename)) { throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist."); } if (!$this->canRead($pFilename)) { throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file."); } $xml = simplexml_load_file($pFilename, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); $namespaces = $xml->getNamespaces(true); $docProps = $objPHPExcel->getProperties(); if (isset($xml->DocumentProperties[0])) { foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) { switch ($propertyName) { case 'Title': $docProps->setTitle(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Subject': $docProps->setSubject(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Author': $docProps->setCreator(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Created': $creationDate = strtotime($propertyValue); $docProps->setCreated($creationDate); break; case 'LastAuthor': $docProps->setLastModifiedBy(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'LastSaved': $lastSaveDate = strtotime($propertyValue); $docProps->setModified($lastSaveDate); break; case 'Company': $docProps->setCompany(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Category': $docProps->setCategory(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Manager': $docProps->setManager(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Keywords': $docProps->setKeywords(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; case 'Description': $docProps->setDescription(self::_convertStringEncoding($propertyValue, $this->_charSet)); break; } } } if (isset($xml->CustomDocumentProperties)) { foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { $propertyAttributes = $propertyValue->attributes($namespaces['dt']); $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', 'PHPExcel_Reader_Excel2003XML::_hex2str', $propertyName); $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_UNKNOWN; switch ((string) $propertyAttributes) { case 'string': $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING; $propertyValue = trim($propertyValue); break; case 'boolean': $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN; $propertyValue = (bool) $propertyValue; break; case 'integer': $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_INTEGER; $propertyValue = intval($propertyValue); break; case 'float': $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT; $propertyValue = floatval($propertyValue); break; case 'dateTime.tz': $propertyType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE; $propertyValue = strtotime(trim($propertyValue)); break; } $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType); } } foreach ($xml->Styles[0] as $style) { $style_ss = $style->attributes($namespaces['ss']); $styleID = (string) $style_ss['ID']; // echo 'Style ID = '.$styleID.'<br />'; if ($styleID == 'Default') { $this->_styles['Default'] = array(); } else { $this->_styles[$styleID] = $this->_styles['Default']; } foreach ($style as $styleType => $styleData) { $styleAttributes = $styleData->attributes($namespaces['ss']); // echo $styleType.'<br />'; switch ($styleType) { case 'Alignment': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = (string) $styleAttributeValue; switch ($styleAttributeKey) { case 'Vertical': if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) { $this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue; } break; case 'Horizontal': if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) { $this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue; } break; case 'WrapText': $this->_styles[$styleID]['alignment']['wrap'] = true; break; } } break; case 'Borders': foreach ($styleData->Border as $borderStyle) { $borderAttributes = $borderStyle->attributes($namespaces['ss']); $thisBorder = array(); foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) { // echo $borderStyleKey.' = '.$borderStyleValue.'<br />'; switch ($borderStyleKey) { case 'LineStyle': $thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM; // $thisBorder['style'] = $borderStyleValue; break; case 'Weight': // $thisBorder['style'] = $borderStyleValue; break; case 'Position': $borderPosition = strtolower($borderStyleValue); break; case 'Color': $borderColour = substr($borderStyleValue, 1); $thisBorder['color']['rgb'] = $borderColour; break; } } if (!empty($thisBorder)) { if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') { $this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder; } } } break; case 'Font': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = (string) $styleAttributeValue; switch ($styleAttributeKey) { case 'FontName': $this->_styles[$styleID]['font']['name'] = $styleAttributeValue; break; case 'Size': $this->_styles[$styleID]['font']['size'] = $styleAttributeValue; break; case 'Color': $this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1); break; case 'Bold': $this->_styles[$styleID]['font']['bold'] = true; break; case 'Italic': $this->_styles[$styleID]['font']['italic'] = true; break; case 'Underline': if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) { $this->_styles[$styleID]['font']['underline'] = $styleAttributeValue; } break; } } break; case 'Interior': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; switch ($styleAttributeKey) { case 'Color': $this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1); break; } } break; case 'NumberFormat': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue); switch ($styleAttributeValue) { case 'Short Date': $styleAttributeValue = 'dd/mm/yyyy'; break; } if ($styleAttributeValue > '') { $this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue; } } break; case 'Protection': foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { // echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />'; } break; } } // print_r($this->_styles[$styleID]); // echo '<hr />'; } // echo '<hr />'; $worksheetID = 0; $xml_ss = $xml->children($namespaces['ss']); foreach ($xml_ss->Worksheet as $worksheet) { $worksheet_ss = $worksheet->attributes($namespaces['ss']); if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) { continue; } // echo '<h3>Worksheet: ',$worksheet_ss['Name'],'<h3>'; // // Create new Worksheet $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex($worksheetID); if (isset($worksheet_ss['Name'])) { $worksheetName = self::_convertStringEncoding((string) $worksheet_ss['Name'], $this->_charSet); // 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); } $columnID = 'A'; if (isset($worksheet->Table->Column)) { foreach ($worksheet->Table->Column as $columnData) { $columnData_ss = $columnData->attributes($namespaces['ss']); if (isset($columnData_ss['Index'])) { $columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index'] - 1); } if (isset($columnData_ss['Width'])) { $columnWidth = $columnData_ss['Width']; // echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />'; $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); } ++$columnID; } } $rowID = 1; if (isset($worksheet->Table->Row)) { foreach ($worksheet->Table->Row as $rowData) { $rowHasData = false; $row_ss = $rowData->attributes($namespaces['ss']); if (isset($row_ss['Index'])) { $rowID = (int) $row_ss['Index']; } // echo '<b>Row '.$rowID.'</b><br />'; $columnID = 'A'; foreach ($rowData->Cell as $cell) { $cell_ss = $cell->attributes($namespaces['ss']); if (isset($cell_ss['Index'])) { $columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index'] - 1); } $cellRange = $columnID . $rowID; if ($this->getReadFilter() !== NULL) { if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { continue; } } if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) { $columnTo = $columnID; if (isset($cell_ss['MergeAcross'])) { $columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1); } $rowTo = $rowID; if (isset($cell_ss['MergeDown'])) { $rowTo = $rowTo + $cell_ss['MergeDown']; } $cellRange .= ':' . $columnTo . $rowTo; $objPHPExcel->getActiveSheet()->mergeCells($cellRange); } $cellIsSet = $hasCalculatedValue = false; $cellDataFormula = ''; if (isset($cell_ss['Formula'])) { $cellDataFormula = $cell_ss['Formula']; // added this as a check for array formulas if (isset($cell_ss['ArrayRange'])) { $cellDataCSEFormula = $cell_ss['ArrayRange']; // echo "found an array formula at ".$columnID.$rowID."<br />"; } $hasCalculatedValue = true; } if (isset($cell->Data)) { $cellValue = $cellData = $cell->Data; $type = PHPExcel_Cell_DataType::TYPE_NULL; $cellData_ss = $cellData->attributes($namespaces['ss']); if (isset($cellData_ss['Type'])) { $cellDataType = $cellData_ss['Type']; switch ($cellDataType) { /* const TYPE_STRING = 's'; const TYPE_FORMULA = 'f'; const TYPE_NUMERIC = 'n'; const TYPE_BOOL = 'b'; const TYPE_NULL = 'null'; const TYPE_INLINE = 'inlineStr'; const TYPE_ERROR = 'e'; */ case 'String': $cellValue = self::_convertStringEncoding($cellValue, $this->_charSet); $type = PHPExcel_Cell_DataType::TYPE_STRING; break; case 'Number': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $cellValue = (double) $cellValue; if (floor($cellValue) == $cellValue) { $cellValue = (int) $cellValue; } break; case 'Boolean': $type = PHPExcel_Cell_DataType::TYPE_BOOL; $cellValue = $cellValue != 0; break; case 'DateTime': $type = PHPExcel_Cell_DataType::TYPE_NUMERIC; $cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue)); break; case 'Error': $type = PHPExcel_Cell_DataType::TYPE_ERROR; break; } } if ($hasCalculatedValue) { // echo 'FORMULA<br />'; $type = PHPExcel_Cell_DataType::TYPE_FORMULA; $columnNumber = PHPExcel_Cell::columnIndexFromString($columnID); if (substr($cellDataFormula, 0, 3) == 'of:') { $cellDataFormula = substr($cellDataFormula, 3); // echo 'Before: ',$cellDataFormula,'<br />'; $temp = explode('"', $cellDataFormula); $key = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($key = !$key) { $value = str_replace(array('[.', '.', ']'), '', $value); } } } else { // Convert R1C1 style references to A1 style references (but only when not quoted) // echo 'Before: ',$cellDataFormula,'<br />'; $temp = explode('"', $cellDataFormula); $key = false; foreach ($temp as &$value) { // Only replace in alternate array entries (i.e. non-quoted blocks) if ($key = !$key) { preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE); // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way // through the formula from left to right. Reversing means that we work right to left.through // the formula $cellReferences = array_reverse($cellReferences); // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent, // then modify the formula to use that new reference foreach ($cellReferences as $cellReference) { $rowReference = $cellReference[2][0]; // Empty R reference is the current row if ($rowReference == '') { $rowReference = $rowID; } // Bracketed R references are relative to the current row if ($rowReference[0] == '[') { $rowReference = $rowID + trim($rowReference, '[]'); } $columnReference = $cellReference[4][0]; // Empty C reference is the current column if ($columnReference == '') { $columnReference = $columnNumber; } // Bracketed C references are relative to the current column if ($columnReference[0] == '[') { $columnReference = $columnNumber + trim($columnReference, '[]'); } $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference; $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0])); } } } } unset($value); // Then rebuild the formula string $cellDataFormula = implode('"', $temp); // echo 'After: ',$cellDataFormula,'<br />'; } // echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />'; // $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type); if ($hasCalculatedValue) { // echo 'Formula result is '.$cellValue.'<br />'; $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue); } $cellIsSet = $rowHasData = true; } if (isset($cell->Comment)) { // echo '<b>comment found</b><br />'; $commentAttributes = $cell->Comment->attributes($namespaces['ss']); $author = 'unknown'; if (isset($commentAttributes->Author)) { $author = (string) $commentAttributes->Author; // echo 'Author: ',$author,'<br />'; } $node = $cell->Comment->Data->asXML(); // $annotation = str_replace('html:','',substr($node,49,-10)); // echo $annotation,'<br />'; $annotation = strip_tags($node); // echo 'Annotation: ',$annotation,'<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::_convertStringEncoding($author, $this->_charSet))->setText($this->_parseRichText($annotation)); } if ($cellIsSet && isset($cell_ss['StyleID'])) { $style = (string) $cell_ss['StyleID']; // echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />'; if (isset($this->_styles[$style]) && !empty($this->_styles[$style])) { // echo 'Cell '.$columnID.$rowID.'<br />'; // print_r($this->_styles[$style]); // echo '<br />'; if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) { $objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValue(NULL); } $objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]); } } ++$columnID; } if ($rowHasData) { if (isset($row_ss['StyleID'])) { $rowStyle = $row_ss['StyleID']; } if (isset($row_ss['Height'])) { $rowHeight = $row_ss['Height']; // echo '<b>Setting row height to '.$rowHeight.'</b><br />'; $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); } } ++$rowID; } } ++$worksheetID; } // Return return $objPHPExcel; }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws PHPExcel_Reader_Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Open file to validate $this->_openFile($pFilename); if (!$this->_isValidFormat()) { fclose($this->_fileHandle); throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid HTML file."); } // Close after validating fclose($this->_fileHandle); // Create new PHPExcel while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) { $objPHPExcel->createSheet(); } $objPHPExcel->setActiveSheetIndex($this->_sheetIndex); // Create a new DOM object $dom = new DOMDocument(); // Reload the HTML file into the DOM object $loaded = $dom->loadHTMLFile($pFilename, PHPExcel_Settings::getLibXmlLoaderOptions()); if ($loaded === FALSE) { throw new PHPExcel_Reader_Exception('Failed to load ', $pFilename, ' as a DOM Document'); } // Discard white space $dom->preserveWhiteSpace = false; $row = 0; $column = 'A'; $content = ''; $this->_processDomElement($dom, $objPHPExcel->getActiveSheet(), $row, $column, $content); // echo '<hr />'; // var_dump($this->_dataArray); // Return return $objPHPExcel; }
private function _readRibbon($excel, $customUITarget, $zip) { $baseDir = dirname($customUITarget); $nameCustomUI = basename($customUITarget); // get the xml file (ribbon) $localRibbon = $this->_getFromZipArchive($zip, $customUITarget); $customUIImagesNames = array(); $customUIImagesBinaries = array(); // something like customUI/_rels/customUI.xml.rels $pathRels = $baseDir . '/_rels/' . $nameCustomUI . '.rels'; $dataRels = $this->_getFromZipArchive($zip, $pathRels); if ($dataRels) { // exists and not empty if the ribbon have some pictures (other than internal MSO) $UIRels = simplexml_load_string($dataRels, 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions()); if ($UIRels) { // we need to save id and target to avoid parsing customUI.xml and "guess" if it's a pseudo callback who load the image foreach ($UIRels->Relationship as $ele) { if ($ele["Type"] == 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image') { // an image ? $customUIImagesNames[(string) $ele['Id']] = (string) $ele['Target']; $customUIImagesBinaries[(string) $ele['Target']] = $this->_getFromZipArchive($zip, $baseDir . '/' . (string) $ele['Target']); } } } } if ($localRibbon) { $excel->setRibbonXMLData($customUITarget, $localRibbon); if (count($customUIImagesNames) > 0 && count($customUIImagesBinaries) > 0) { $excel->setRibbonBinObjects($customUIImagesNames, $customUIImagesBinaries); } else { $excel->setRibbonBinObjects(NULL); } } else { $excel->setRibbonXMLData(NULL); $excel->setRibbonBinObjects(NULL); } }
/** * 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($this->securityScan($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); // Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case // Excel 2007+ doesn't differentiate between integer and float, so set the value and dropthru to the next (numeric) case 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 // 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; }