/** * Instantiate a new renderer of the configured type within this container class * * @param PHPExcel $phpExcel PHPExcel object * @throws Exception when PDF library is not configured */ public function __construct(PHPExcel $phpExcel) { $pdfLibraryName = \PHPExcel\Settings::getPdfRendererName(); if (is_null($pdfLibraryName)) { throw new Exception("PDF Rendering library has not been defined."); } $pdfLibraryPath = \PHPExcel\Settings::getPdfRendererPath(); if (is_null($pdfLibraryName)) { throw new Exception("PDF Rendering library path has not been defined."); } $includePath = str_replace('\\', '/', get_include_path()); $rendererPath = str_replace('\\', '/', $pdfLibraryPath); if (strpos($rendererPath, $includePath) === false) { set_include_path(get_include_path() . PATH_SEPARATOR . $pdfLibraryPath); } $rendererName = 'PDF\\' . $pdfLibraryName; $this->renderer = new $rendererName($phpExcel); }
/** * Save PHPExcel to file * * @param string $pFilename * @throws \PHPExcel\Writer\Exception */ public function save($pFilename = null) { if ($this->spreadSheet !== null) { // garbage collect $this->spreadSheet->garbageCollect(); // If $pFilename is php://output or php://stdout, make it a temporary file... $originalFilename = $pFilename; if (strtolower($pFilename) == 'php://output' || strtolower($pFilename) == 'php://stdout') { $pFilename = @tempnam(\PHPExcel\Shared\File::sysGetTempDir(), 'phpxltmp'); if ($pFilename == '') { $pFilename = $originalFilename; } } $saveDebugLog = \PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->getWriteDebugLog(); \PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->setWriteDebugLog(false); $saveDateReturnType = \PHPExcel\Calculation\Functions::getReturnDateType(); \PHPExcel\Calculation\Functions::setReturnDateType(\PHPExcel\Calculation\Functions::RETURNDATE_EXCEL); // Create string lookup table $this->stringTable = array(); for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) { $this->stringTable = $this->getWriterPart('StringTable')->createStringTable($this->spreadSheet->getSheet($i), $this->stringTable); } // Create styles dictionaries $this->styleHashTable->addFromSource($this->getWriterPart('Style')->allStyles($this->spreadSheet)); $this->stylesConditionalHashTable->addFromSource($this->getWriterPart('Style')->allConditionalStyles($this->spreadSheet)); $this->fillHashTable->addFromSource($this->getWriterPart('Style')->allFills($this->spreadSheet)); $this->fontHashTable->addFromSource($this->getWriterPart('Style')->allFonts($this->spreadSheet)); $this->bordersHashTable->addFromSource($this->getWriterPart('Style')->allBorders($this->spreadSheet)); $this->numFmtHashTable->addFromSource($this->getWriterPart('Style')->allNumberFormats($this->spreadSheet)); // Create drawing dictionary $this->drawingHashTable->addFromSource($this->getWriterPart('Drawing')->allDrawings($this->spreadSheet)); // Create new ZIP file and open it for writing $zipClass = \PHPExcel\Settings::getZipClass(); /** @var \ZipArchive $objZip */ $objZip = new $zipClass(); // Retrieve OVERWRITE and CREATE constants from the instantiated zip class // This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP $ro = new \ReflectionObject($objZip); $zipOverWrite = $ro->getConstant('OVERWRITE'); $zipCreate = $ro->getConstant('CREATE'); if (file_exists($pFilename)) { unlink($pFilename); } // Try opening the ZIP file if ($objZip->open($pFilename, $zipOverWrite) !== true) { if ($objZip->open($pFilename, $zipCreate) !== true) { throw new \PHPExcel\Writer\Exception("Could not open " . $pFilename . " for writing."); } } // Add [Content_Types].xml to ZIP file $objZip->addFromString('[Content_Types].xml', $this->getWriterPart('ContentTypes')->writeContentTypes($this->spreadSheet, $this->includeCharts)); //if hasMacros, add the vbaProject.bin file, Certificate file(if exists) if ($this->spreadSheet->hasMacros()) { $macrosCode = $this->spreadSheet->getMacrosCode(); if (!is_null($macrosCode)) { // we have the code ? $objZip->addFromString('xl/vbaProject.bin', $macrosCode); //allways in 'xl', allways named vbaProject.bin if ($this->spreadSheet->hasMacrosCertificate()) { //signed macros ? // Yes : add the certificate file and the related rels file $objZip->addFromString('xl/vbaProjectSignature.bin', $this->spreadSheet->getMacrosCertificate()); $objZip->addFromString('xl/_rels/vbaProject.bin.rels', $this->getWriterPart('RelsVBA')->writeVBARelationships($this->spreadSheet)); } } } //a custom UI in this workbook ? add it ("base" xml and additional objects (pictures) and rels) if ($this->spreadSheet->hasRibbon()) { $tmpRibbonTarget = $this->spreadSheet->getRibbonXMLData('target'); $objZip->addFromString($tmpRibbonTarget, $this->spreadSheet->getRibbonXMLData('data')); if ($this->spreadSheet->hasRibbonBinObjects()) { $tmpRootPath = dirname($tmpRibbonTarget) . '/'; $ribbonBinObjects = $this->spreadSheet->getRibbonBinObjects('data'); //the files to write foreach ($ribbonBinObjects as $aPath => $aContent) { $objZip->addFromString($tmpRootPath . $aPath, $aContent); } //the rels for files $objZip->addFromString($tmpRootPath . '_rels/' . basename($tmpRibbonTarget) . '.rels', $this->getWriterPart('RelsRibbonObjects')->writeRibbonRelationships($this->spreadSheet)); } } // Add relationships to ZIP file $objZip->addFromString('_rels/.rels', $this->getWriterPart('Rels')->writeRelationships($this->spreadSheet)); $objZip->addFromString('xl/_rels/workbook.xml.rels', $this->getWriterPart('Rels')->writeWorkbookRelationships($this->spreadSheet)); // Add document properties to ZIP file $objZip->addFromString('docProps/app.xml', $this->getWriterPart('DocProps')->writeDocPropsApp($this->spreadSheet)); $objZip->addFromString('docProps/core.xml', $this->getWriterPart('DocProps')->writeDocPropsCore($this->spreadSheet)); $customPropertiesPart = $this->getWriterPart('DocProps')->writeDocPropsCustom($this->spreadSheet); if ($customPropertiesPart !== null) { $objZip->addFromString('docProps/custom.xml', $customPropertiesPart); } // Add theme to ZIP file $objZip->addFromString('xl/theme/theme1.xml', $this->getWriterPart('Theme')->writeTheme($this->spreadSheet)); // Add string table to ZIP file $objZip->addFromString('xl/sharedStrings.xml', $this->getWriterPart('StringTable')->writeStringTable($this->stringTable)); // Add styles to ZIP file $objZip->addFromString('xl/styles.xml', $this->getWriterPart('Style')->writeStyles($this->spreadSheet)); // Add workbook to ZIP file $objZip->addFromString('xl/workbook.xml', $this->getWriterPart('Workbook')->writeWorkbook($this->spreadSheet, $this->preCalculateFormulas)); $chartCount = 0; // Add worksheets for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) { $objZip->addFromString('xl/worksheets/sheet' . ($i + 1) . '.xml', $this->getWriterPart('Worksheet')->writeWorksheet($this->spreadSheet->getSheet($i), $this->stringTable, $this->includeCharts)); if ($this->includeCharts) { $charts = $this->spreadSheet->getSheet($i)->getChartCollection(); if (count($charts) > 0) { foreach ($charts as $chart) { $objZip->addFromString('xl/charts/chart' . ($chartCount + 1) . '.xml', $this->getWriterPart('Chart')->writeChart($chart, $this->preCalculateFormulas)); $chartCount++; } } } } $chartRef1 = $chartRef2 = 0; // Add worksheet relationships (drawings, ...) for ($i = 0; $i < $this->spreadSheet->getSheetCount(); ++$i) { // Add relationships $objZip->addFromString('xl/worksheets/_rels/sheet' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeWorksheetRelationships($this->spreadSheet->getSheet($i), $i + 1, $this->includeCharts)); $drawings = $this->spreadSheet->getSheet($i)->getDrawingCollection(); $drawingCount = count($drawings); if ($this->includeCharts) { $chartCount = $this->spreadSheet->getSheet($i)->getChartCount(); } // Add drawing and image relationship parts if ($drawingCount > 0 || $chartCount > 0) { // Drawing relationships $objZip->addFromString('xl/drawings/_rels/drawing' . ($i + 1) . '.xml.rels', $this->getWriterPart('Rels')->writeDrawingRelationships($this->spreadSheet->getSheet($i), $chartRef1, $this->includeCharts)); // Drawings $objZip->addFromString('xl/drawings/drawing' . ($i + 1) . '.xml', $this->getWriterPart('Drawing')->writeDrawings($this->spreadSheet->getSheet($i), $chartRef2, $this->includeCharts)); } // Add comment relationship parts if (count($this->spreadSheet->getSheet($i)->getComments()) > 0) { // VML Comments $objZip->addFromString('xl/drawings/vmlDrawing' . ($i + 1) . '.vml', $this->getWriterPart('Comments')->writeVMLComments($this->spreadSheet->getSheet($i))); // Comments $objZip->addFromString('xl/comments' . ($i + 1) . '.xml', $this->getWriterPart('Comments')->writeComments($this->spreadSheet->getSheet($i))); } // Add header/footer relationship parts if (count($this->spreadSheet->getSheet($i)->getHeaderFooter()->getImages()) > 0) { // VML Drawings $objZip->addFromString('xl/drawings/vmlDrawingHF' . ($i + 1) . '.vml', $this->getWriterPart('Drawing')->writeVMLHeaderFooterImages($this->spreadSheet->getSheet($i))); // VML Drawing relationships $objZip->addFromString('xl/drawings/_rels/vmlDrawingHF' . ($i + 1) . '.vml.rels', $this->getWriterPart('Rels')->writeHeaderFooterDrawingRelationships($this->spreadSheet->getSheet($i))); // Media foreach ($this->spreadSheet->getSheet($i)->getHeaderFooter()->getImages() as $image) { $objZip->addFromString('xl/media/' . $image->getIndexedFilename(), file_get_contents($image->getPath())); } } } // Add media for ($i = 0; $i < $this->getDrawingHashTable()->count(); ++$i) { if ($this->getDrawingHashTable()->getByIndex($i) instanceof \PHPExcel\Worksheet\Drawing) { $imageContents = null; $imagePath = $this->getDrawingHashTable()->getByIndex($i)->getPath(); if (strpos($imagePath, 'zip://') !== false) { $imagePath = substr($imagePath, 6); $imagePathSplitted = explode('#', $imagePath); $imageZip = new ZipArchive(); $imageZip->open($imagePathSplitted[0]); $imageContents = $imageZip->getFromName($imagePathSplitted[1]); $imageZip->close(); unset($imageZip); } else { $imageContents = file_get_contents($imagePath); } $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } elseif ($this->getDrawingHashTable()->getByIndex($i) instanceof \PHPExcel\Worksheet\MemoryDrawing) { ob_start(); call_user_func($this->getDrawingHashTable()->getByIndex($i)->getRenderingFunction(), $this->getDrawingHashTable()->getByIndex($i)->getImageResource()); $imageContents = ob_get_contents(); ob_end_clean(); $objZip->addFromString('xl/media/' . str_replace(' ', '_', $this->getDrawingHashTable()->getByIndex($i)->getIndexedFilename()), $imageContents); } } \PHPExcel\Calculation\Functions::setReturnDateType($saveDateReturnType); \PHPExcel\Calculation::getInstance($this->spreadSheet)->getDebugLog()->setWriteDebugLog($saveDebugLog); // Close file if ($objZip->close() === false) { throw new \PHPExcel\Writer\Exception("Could not close zip file {$pFilename}."); } // If a temporary file was used, copy it to the correct file stream if ($originalFilename != $pFilename) { if (copy($pFilename, $originalFilename) === false) { throw new \PHPExcel\Writer\Exception("Could not copy temporary zip file {$pFilename} to {$originalFilename}."); } @unlink($pFilename); } } else { throw new \PHPExcel\Writer\Exception("PHPExcel object unassigned."); } }
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param PHPExcel $objPHPExcel * @return PHPExcel * @throws 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 Exception("Could not open " . $pFilename . " for reading! File does not exist."); } if (!$this->canRead($pFilename)) { throw new Exception($pFilename . " is an Invalid Spreadsheet file."); } $xml = simplexml_load_string($this->securityScan(file_get_contents($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\Document\Properties::PROPERTY_TYPE_UNKNOWN; switch ((string) $propertyAttributes) { case 'string': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING; $propertyValue = trim($propertyValue); break; case 'boolean': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN; $propertyValue = (bool) $propertyValue; break; case 'integer': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_INTEGER; $propertyValue = intval($propertyValue); break; case 'float': $propertyType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT; $propertyValue = floatval($propertyValue); break; case 'dateTime.tz': $propertyType = \PHPExcel\Document\Properties::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 />'; $this->styles[$styleID] = isset($this->styles['Default']) ? $this->styles['Default'] : array(); 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)) { $additionalMergedCells = 0; 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'])) { $additionalMergedCells += (int) $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; while ($additionalMergedCells > 0) { ++$columnID; $additionalMergedCells--; } } 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; }
* @category PHPExcel * @package PHPExcel * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## */ /** Error reporting */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; $cacheMethod = \PHPExcel\CachedObjectStorageFactory::CACHE_IN_MEMORY_GZIP; if (!\PHPExcel\Settings::setCacheStorageMethod($cacheMethod)) { die($cacheMethod . " caching method is not available" . EOL); } echo date('H:i:s'), " Enable Cell Caching using ", $cacheMethod, " method", EOL; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", EOL; $objPHPExcel = new \PHPExcel\Spreadsheet(); // Set document properties echo date('H:i:s'), " Set properties", EOL; $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file"); // Create a first sheet echo date('H:i:s'), " Add data", EOL; $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname"); $objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname"); $objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
/** * Loads PHPExcel from file into PHPExcel instance * * @param string $pFilename * @param \PHPExcel\Spreadsheet $objPHPExcel * @return \PHPExcel\Spreadsheet * @throws Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new 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 Exception("Could not open " . $pFilename . " for reading! Error opening file."); } // echo '<h1>Meta Information</h1>'; $xml = simplexml_load_string($this->securityScan($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\Document\Properties::PROPERTY_TYPE_STRING; foreach ($propertyValueAttributes as $key => $value) { if ($key == 'name') { $propertyValueName = (string) $value; } elseif ($key == 'value-type') { switch ($value) { case 'date': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'date'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_DATE; break; case 'boolean': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'bool'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_BOOLEAN; break; case 'float': $propertyValue = \PHPExcel\Document\Properties::convertProperty($propertyValue, 'r4'); $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_FLOAT; break; default: $propertyValueType = \PHPExcel\Document\Properties::PROPERTY_TYPE_STRING; } } } $docProps->setCustomProperty($propertyValueName, $propertyValue, $propertyValueType); break; } } } // echo '<h1>Workbook Content</h1>'; $xml = simplexml_load_string($this->securityScan($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 $keyRowData => $cellData) { $rowData = $cellData; break; } 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) { if (isset($t->span)) { foreach ($t->span as $text) { $textArray[] = (string) $text; } } else { $textArray[] = (string) $t; } } $text = implode("\n", $textArray); // echo $text, '<br />'; $objPHPExcel->getActiveSheet()->getComment($columnID . $rowID)->setText($this->parseRichText($text)); // ->setAuthor( $author ) } 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 Exception */ public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel) { // Check if file exists if (!file_exists($pFilename)) { throw new 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; }
private function renderCombinationChart($groupCount, $dimensions, $outputDestination) { require_once \PHPExcel\Settings::getChartRendererPath() . 'jpgraph_line.php'; require_once \PHPExcel\Settings::getChartRendererPath() . 'jpgraph_bar.php'; require_once \PHPExcel\Settings::getChartRendererPath() . 'jpgraph_scatter.php'; require_once \PHPExcel\Settings::getChartRendererPath() . 'jpgraph_regstat.php'; require_once \PHPExcel\Settings::getChartRendererPath() . 'jpgraph_line.php'; $this->renderCartesianPlotArea(); for ($i = 0; $i < $groupCount; ++$i) { $dimensions = null; $chartType = $this->chart->getPlotArea()->getPlotGroupByIndex($i)->getPlotType(); switch ($chartType) { case 'area3DChart': $dimensions = '3d'; // no break // no break case 'areaChart': $this->renderPlotLine($i, true, true, $dimensions); break; case 'bar3DChart': $dimensions = '3d'; // no break // no break case 'barChart': $this->renderPlotBar($i, $dimensions); break; case 'line3DChart': $dimensions = '3d'; // no break // no break case 'lineChart': $this->renderPlotLine($i, false, true, $dimensions); break; case 'scatterChart': $this->renderPlotScatter($i, false); break; case 'bubbleChart': $this->renderPlotScatter($i, true); break; default: $this->graph = null; return false; } } $this->renderLegend(); $this->graph->Stroke($outputDestination); return true; }
<?php namespace PHPExcel\Writer\PDF; /** Require mPDF library */ $pdfRendererClassFile = \PHPExcel\Settings::getPdfRendererPath() . '/mpdf.php'; if (file_exists($pdfRendererClassFile)) { require_once $pdfRendererClassFile; } else { throw new \PHPExcel\Writer\Exception('Unable to load PDF Rendering library'); } /** * PHPExcel_Writer_PDF_mPDF * * Copyright (c) 2006 - 2015 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
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($this->securityScan($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); } }
require_once dirname(__FILE__) . '/../src/Bootstrap.php'; // Change these values to select the Rendering library that you wish to use // and its directory location on your server //$rendererName = \PHPExcel\Settings::PDF_RENDERER_TCPDF; //$rendererName = \PHPExcel\Settings::PDF_RENDERER_MPDF; $rendererName = \PHPExcel\Settings::PDF_RENDERER_DOMPDF; //$rendererLibrary = 'tcPDF5.9'; //$rendererLibrary = 'mPDF5.4'; $rendererLibrary = 'domPDF0.6.0beta3'; $rendererLibraryPath = '/php/libraries/PDF/' . $rendererLibrary; echo date('H:i:s'), " Hide grid lines", EOL; $objPHPExcel->getActiveSheet()->setShowGridLines(false); echo date('H:i:s'), " Set orientation to landscape", EOL; $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel\Worksheet\PageSetup::ORIENTATION_LANDSCAPE); echo date('H:i:s'), " Write to PDF format using {$rendererName}", EOL; if (!\PHPExcel\Settings::setPdfRenderer($rendererName, $rendererLibraryPath)) { die('NOTICE: Please set the $rendererName and $rendererLibraryPath values' . EOL . 'at the top of this script as appropriate for your directory structure'); } $callStartTime = microtime(true); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'PDF'); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.php', '_' . $rendererName . '.pdf', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo date('H:i:s'), " File written to ", str_replace('.php', '_' . $rendererName . '.pdf', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL; echo 'Call time to write Workbook was ', sprintf('%.4f', $callTime), " seconds", EOL; // Echo memory usage echo date('H:i:s'), ' Current memory usage: ', memory_get_usage(true) / 1024 / 1024, " MB", EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", EOL; // Echo done
<?php namespace PHPExcel\Writer\PDF; /** Require DomPDF library */ $pdfRendererClassFile = \PHPExcel\Settings::getPdfRendererPath() . '/dompdf_config.inc.php'; if (file_exists($pdfRendererClassFile)) { require_once $pdfRendererClassFile; } else { throw new \PHPExcel\Writer\Exception('Unable to load PDF Rendering library'); } /** * PHPExcel_Writer_PDF_DomPDF * * Copyright (c) 2006 - 2015 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
<?php namespace PHPExcel\Writer\PDF; /** Require tcPDF library */ $pdfRendererClassFile = \PHPExcel\Settings::getPdfRendererPath() . '/tcpdf.php'; if (file_exists($pdfRendererClassFile)) { $k_path_url = \PHPExcel\Settings::getPdfRendererPath(); require_once $pdfRendererClassFile; } else { throw new \PHPExcel\Writer\Exception('Unable to load PDF Rendering library'); } /** * PHPExcel_Writer_PDF_tcPDF * * Copyright (c) 2006 - 2015 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 *
/** * Create zip object * * @param string $pFilename * @throws \PHPExcel\Writer\Exception * @return ZipArchive */ private function createZip($pFilename) { // Create new ZIP file and open it for writing $zipClass = \PHPExcel\Settings::getZipClass(); $objZip = new $zipClass(); // Retrieve OVERWRITE and CREATE constants from the instantiated zip class // This method of accessing constant values from a dynamic class should work with all appropriate versions of PHP $ro = new ReflectionObject($objZip); $zipOverWrite = $ro->getConstant('OVERWRITE'); $zipCreate = $ro->getConstant('CREATE'); if (file_exists($pFilename)) { unlink($pFilename); } // Try opening the ZIP file if ($objZip->open($pFilename, $zipOverWrite) !== true) { if ($objZip->open($pFilename, $zipCreate) !== true) { throw new \PHPExcel\Writer\Exception("Could not open {$pFilename} for writing."); } } return $objZip; }
* @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version ##VERSION##, ##DATE## */ error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); /** Include PHPExcel */ require_once dirname(__FILE__) . '/../src/Bootstrap.php'; if (!file_exists("05featuredemo.xlsx")) { exit("Please run 05featuredemo.php first." . EOL); } // Use PCLZip rather than ZipArchive to read the Excel2007 OfficeOpenXML file \PHPExcel\Settings::setZipClass(\PHPExcel\Settings::PCLZIP); echo date('H:i:s'), " Load from Excel2007 file", EOL; $callStartTime = microtime(true); $objPHPExcel = \PHPExcel\IOFactory::load("05featuredemo.xlsx"); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo 'Call time to read Workbook was ', sprintf('%.4f', $callTime), " seconds", EOL; // Echo memory usage echo date('H:i:s'), ' Current memory usage: ', memory_get_usage(true) / 1024 / 1024, " MB", EOL; echo date('H:i:s'), " Write to Excel2007 format", EOL; $callStartTime = microtime(true); $objWriter = \PHPExcel\IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); $callEndTime = microtime(true); $callTime = $callEndTime - $callStartTime; echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;