/** Include PHPExcel */ require_once '../Classes/PHPExcel.php'; // Create new PHPExcel object echo date('H:i:s'), " Create new PHPExcel object", PHP_EOL; $objPHPExcel = new PHPExcel(); // Set document properties echo date('H:i:s'), " Set document properties", PHP_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"); // Generate an image echo date('H:i:s'), " Generate an image", PHP_EOL; $gdImage = @imagecreatetruecolor(120, 20) or die('Cannot Initialize new GD image stream'); $textColor = imagecolorallocate($gdImage, 255, 255, 255); imagestring($gdImage, 1, 5, 5, 'Created with PHPExcel', $textColor); // Add a drawing to the worksheet echo date('H:i:s'), " Add a drawing to the worksheet", PHP_EOL; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(36); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); echo date('H:i:s'), " Write to Excel2007 format", PHP_EOL; $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); echo date('H:i:s'), " File written to ", str_replace('.php', '.xlsx', __FILE__), PHP_EOL; // Echo memory peak usage echo date('H:i:s'), " Peak memory usage: ", memory_get_peak_usage(true) / 1024 / 1024, " MB", PHP_EOL; // Echo done echo date('H:i:s'), " Done writing files", PHP_EOL;
/** * Loads PHPExcel from file * * @param string $pFilename * @throws Exception */ public function load($pFilename) { // Initialisations $this->_phpExcel = new PHPExcel(); $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet if (!$this->_readDataOnly) { $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style $this->_phpExcel->removeCellXfByIndex(0); // remove the default style } // Use ParseXL for the hard work. $this->_ole = new PHPExcel_Shared_OLERead(); // get excel data $res = $this->_ole->read($pFilename); $this->_data = $this->_ole->getWorkBook(); // total byte size of Excel data (workbook global substream + sheet substreams) $this->_dataSize = strlen($this->_data); // initialize $this->_pos = 0; $this->_codepage = 'CP1252'; $this->_formats = array(); $this->_objFonts = array(); $this->_palette = array(); $this->_sheets = array(); $this->_externalBooks = array(); $this->_ref = array(); $this->_definedname = array(); $this->_sst = array(); $this->_drawingGroupData = ''; $this->_xfIndex = ''; $this->_mapCellXfIndex = array(); $this->_mapCellStyleXfIndex = array(); // Parse Workbook Global Substream while ($this->_pos < $this->_dataSize) { $code = $this->_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $pos = $this->_pos; $length = $this->_GetInt2d($this->_data, $pos + 2); $recordData = substr($this->_data, $pos + 4, $length); // offset: 0; size: 2; BIFF version $this->_version = $this->_GetInt2d($this->_data, $pos + 4); if ($this->_version != self::XLS_BIFF8 && $this->_version != self::XLS_BIFF7) { return false; } // offset: 2; size: 2; type of stream $substreamType = $this->_GetInt2d($this->_data, $pos + 6); if ($substreamType != self::XLS_WorkbookGlobals) { return false; } $this->_pos += 4 + $length; break; case self::XLS_Type_FILEPASS: $this->_readFilepass(); break; case self::XLS_Type_CODEPAGE: $this->_readCodepage(); break; case self::XLS_Type_DATEMODE: $this->_readDateMode(); break; case self::XLS_Type_FONT: $this->_readFont(); break; case self::XLS_Type_FORMAT: $this->_readFormat(); break; case self::XLS_Type_XF: $this->_readXf(); break; case self::XLS_Type_STYLE: $this->_readStyle(); break; case self::XLS_Type_PALETTE: $this->_readPalette(); break; case self::XLS_Type_SHEET: $this->_readSheet(); break; case self::XLS_Type_EXTERNALBOOK: $this->_readExternalBook(); break; case self::XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break; case self::XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break; case self::XLS_Type_MSODRAWINGGROUP: $this->_readMsoDrawingGroup(); break; case self::XLS_Type_SST: $this->_readSst(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } // Resolve indexed colors for font, fill, and border colors // Cannot be resolved already in XF record, because PALETTE record comes afterwards if (!$this->_readDataOnly) { foreach ($this->_objFonts as $objFont) { $color = $this->_readColor($objFont->colorIndex); $objFont->getColor()->setRGB($color['rgb']); } foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) { // fill start and end color $startColor = $this->_readColor($objStyle->getFill()->startcolorIndex); $objStyle->getFill()->getStartColor()->setRGB($startColor['rgb']); $endColor = $this->_readColor($objStyle->getFill()->endcolorIndex); $objStyle->getFill()->getEndColor()->setRGB($endColor['rgb']); // border colors $borderTopColor = $this->_readColor($objStyle->getBorders()->getTop()->colorIndex); $objStyle->getBorders()->getTop()->getColor()->setRGB($borderTopColor['rgb']); $borderRightColor = $this->_readColor($objStyle->getBorders()->getRight()->colorIndex); $objStyle->getBorders()->getRight()->getColor()->setRGB($borderRightColor['rgb']); $borderBottomColor = $this->_readColor($objStyle->getBorders()->getBottom()->colorIndex); $objStyle->getBorders()->getBottom()->getColor()->setRGB($borderBottomColor['rgb']); $borderLeftColor = $this->_readColor($objStyle->getBorders()->getLeft()->colorIndex); $objStyle->getBorders()->getLeft()->getColor()->setRGB($borderLeftColor['rgb']); } } // treat MSODRAWINGGROUP records, workbook-level Escher if (!$this->_readDataOnly && $this->_drawingGroupData) { $escherWorkbook = new PHPExcel_Shared_Escher(); $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook); $escherWorkbook = $reader->load($this->_drawingGroupData); // debug Escher stream //$debug = new Debug_Escher(new PHPExcel_Shared_Escher()); //$debug->load($this->_drawingGroupData); } // Parse the individual sheets foreach ($this->_sheets as $sheet) { // check if sheet should be skipped if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) { continue; } // add sheet to PHPExcel object $this->_phpSheet = $this->_phpExcel->createSheet(); $this->_phpSheet->setTitle($sheet['name']); $this->_phpSheet->setSheetState($sheet['sheetState']); $this->_pos = $sheet['offset']; // Initialize isFitToPages. May change after reading SHEETPR record. $this->_isFitToPages = false; // Initialize drawingData $this->_drawingData = ''; // Initialize objs $this->_objs = array(); // Initialize shared formula parts $this->_sharedFormulaParts = array(); // Initialize shared formulas $this->_sharedFormulas = array(); while ($this->_pos < $this->_dataSize) { $code = $this->_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // do not use this version information for anything // it is unreliable (OpenOffice doc, 5.8), use only version information from the global stream // offset: 2; size: 2; type of the following data $substreamType = $this->_GetInt2d($recordData, 2); if ($substreamType != self::XLS_Worksheet) { break 2; } break; case self::XLS_Type_PRINTGRIDLINES: $this->_readPrintGridlines(); break; case self::XLS_Type_DEFAULTROWHEIGHT: $this->_readDefaultRowHeight(); break; case self::XLS_Type_SHEETPR: $this->_readSheetPr(); break; case self::XLS_Type_HORIZONTALPAGEBREAKS: $this->_readHorizontalPageBreaks(); break; case self::XLS_Type_VERTICALPAGEBREAKS: $this->_readVerticalPageBreaks(); break; case self::XLS_Type_HEADER: $this->_readHeader(); break; case self::XLS_Type_FOOTER: $this->_readFooter(); break; case self::XLS_Type_HCENTER: $this->_readHcenter(); break; case self::XLS_Type_VCENTER: $this->_readVcenter(); break; case self::XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break; case self::XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break; case self::XLS_Type_TOPMARGIN: $this->_readTopMargin(); break; case self::XLS_Type_BOTTOMMARGIN: $this->_readBottomMargin(); break; case self::XLS_Type_PAGESETUP: $this->_readPageSetup(); break; case self::XLS_Type_PROTECT: $this->_readProtect(); break; case self::XLS_Type_PASSWORD: $this->_readPassword(); break; case self::XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break; case self::XLS_Type_COLINFO: $this->_readColInfo(); break; case self::XLS_Type_DIMENSION: $this->_readDefault(); break; case self::XLS_Type_ROW: $this->_readRow(); break; case self::XLS_Type_DBCELL: $this->_readDefault(); break; case self::XLS_Type_RK: $this->_readRk(); break; case self::XLS_Type_LABELSST: $this->_readLabelSst(); break; case self::XLS_Type_MULRK: $this->_readMulRk(); break; case self::XLS_Type_NUMBER: $this->_readNumber(); break; case self::XLS_Type_FORMULA: $this->_readFormula(); break; case self::XLS_Type_SHAREDFMLA: $this->_readSharedFmla(); break; case self::XLS_Type_BOOLERR: $this->_readBoolErr(); break; case self::XLS_Type_MULBLANK: $this->_readMulBlank(); break; case self::XLS_Type_LABEL: $this->_readLabel(); break; case self::XLS_Type_BLANK: $this->_readBlank(); break; case self::XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break; case self::XLS_Type_OBJ: $this->_readObj(); break; case self::XLS_Type_WINDOW2: $this->_readWindow2(); break; case self::XLS_Type_SCL: $this->_readScl(); break; case self::XLS_Type_PANE: $this->_readPane(); break; case self::XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break; case self::XLS_Type_HYPERLINK: $this->_readHyperLink(); break; case self::XLS_Type_SHEETLAYOUT: $this->_readSheetLayout(); break; case self::XLS_Type_RANGEPROTECTION: $this->_readRangeProtection(); break; //case self::XLS_Type_IMDATA: $this->_readImData(); break; //case self::XLS_Type_IMDATA: $this->_readImData(); break; case self::XLS_Type_CONTINUE: $this->_readContinue(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } // treat MSODRAWING records, sheet-level Escher if (!$this->_readDataOnly && $this->_drawingData) { $escherWorksheet = new PHPExcel_Shared_Escher(); $reader = new PHPExcel_Reader_Excel5_Escher($escherWorksheet); $escherWorksheet = $reader->load($this->_drawingData); // debug Escher stream //$debug = new Debug_Escher(new PHPExcel_Shared_Escher()); //$debug->load($this->_drawingData); // get all spContainers in one long array, so they can be mapped to OBJ records $allSpContainers = $escherWorksheet->getDgContainer()->getSpgrContainer()->getAllSpContainers(); } // treat OBJ records foreach ($this->_objs as $n => $obj) { // the first shape container never has a corresponding OBJ record, hence $n + 1 $spContainer = $allSpContainers[$n + 1]; // we skip all spContainers that are a part of a group shape since we cannot yet handle those if ($spContainer->getNestingLevel() > 1) { continue; } // calculate the width and height of the shape list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates()); list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates()); $startOffsetX = $spContainer->getStartOffsetX(); $startOffsetY = $spContainer->getStartOffsetY(); $endOffsetX = $spContainer->getEndOffsetX(); $endOffsetY = $spContainer->getEndOffsetY(); $width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX); $height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY); // calculate offsetX and offsetY of the shape $offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024; $offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256; switch ($obj['type']) { case 0x8: // picture // get index to BSE entry (1-based) $BSEindex = $spContainer->getOPT(0x104); $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection(); $BSE = $BSECollection[$BSEindex - 1]; $blipType = $BSE->getBlipType(); // need check because some blip types are not supported by Escher reader such as EMF if ($blip = $BSE->getBlip()) { $ih = imagecreatefromstring($blip->getData()); $drawing = new PHPExcel_Worksheet_MemoryDrawing(); $drawing->setImageResource($ih); // width, height, offsetX, offsetY $drawing->setResizeProportional(false); $drawing->setWidth($width); $drawing->setHeight($height); $drawing->setOffsetX($offsetX); $drawing->setOffsetY($offsetY); switch ($blipType) { case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG: $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG); break; case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG: $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG); $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG); break; } $drawing->setWorksheet($this->_phpSheet); $drawing->setCoordinates($spContainer->getStartCoordinates()); } break; default: // other object type break; } } // treat SHAREDFMLA records if ($this->_version == self::XLS_BIFF8) { foreach ($this->_sharedFormulaParts as $cell => $baseCell) { $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell); $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA); } } } // add the named ranges (defined names) foreach ($this->_definedname as $definedName) { if ($definedName['isBuiltInName']) { switch ($definedName['name']) { case pack('C', 0x6): // print area // in general, formula looks like this: Foo!$C$7:$J$66,Bar!$A$1:$IV$2 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? foreach ($ranges as $range) { // $range should look like this one of these // Foo!$C$7:$J$66 // Bar!$A$1:$IV$2 $explodes = explode('!', $range); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $docSheet->getPageSetup()->setPrintArea($extractedRange); } } } break; case pack('C', 0x7): // print titles (repeating rows) // Assuming BIFF8, there are 3 cases // 1. repeating rows // formula looks like this: Sheet!$A$1:$IV$2 // rows 1-2 repeat // 2. repeating columns // formula looks like this: Sheet!$A$1:$B$65536 // columns A-B repeat // 3. both repeating rows and repeating columns // formula looks like this: Sheet!$A$1:$B$65536,Sheet!$A$1:$IV$2 $ranges = explode(',', $definedName['formula']); // FIXME: what if sheetname contains comma? foreach ($ranges as $range) { // $range should look like this one of these // Sheet!$A$1:$B$65536 // Sheet!$A$1:$IV$2 $explodes = explode('!', $range); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $coordinateStrings = explode(':', $extractedRange); if (count($coordinateStrings) == 2) { list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[0]); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($coordinateStrings[1]); if ($firstColumn == 'A' and $lastColumn == 'IV') { // then we have repeating rows $docSheet->getPageSetup()->setRowsToRepeatAtTop(array($firstRow, $lastRow)); } elseif ($firstRow == 1 and $lastRow == 65536) { // then we have repeating columns $docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($firstColumn, $lastColumn)); } } } } } break; } } else { // Extract range $explodes = explode('!', $definedName['formula']); if (count($explodes) == 2) { if ($docSheet = $this->_phpExcel->getSheetByName($explodes[0])) { $extractedRange = $explodes[1]; $extractedRange = str_replace('$', '', $extractedRange); $this->_phpExcel->addNamedRange(new PHPExcel_NamedRange((string) $definedName['name'], $docSheet, $extractedRange, false)); } } } } return $this->_phpExcel; }
/** * Panes are frozen? (in sheet currently being read). See WINDOW2 record. * * @var boolean */ private $_frozen; /** * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record. * * @var boolean */ private $_isFitToPages; /** * Objects. One OBJ record contributes with one entry. * * @var array */ private $_objs; /** * Text Objects. One TXO record corresponds with one entry. * * @var array */ private $_textObjects; /** * Cell Annotations (BIFF8) * * @var array */ private $_cellNotes; /** * The combined MSODRAWINGGROUP data * * @var string */ private $_drawingGroupData; /** * The combined MSODRAWING data (per sheet) * * @var string */ private $_drawingData; /** * Keep track of XF index * * @var int */ private $_xfIndex; /** * Mapping of XF index (that is a cell XF) to final index in cellXf collection * * @var array */ private $_mapCellXfIndex; /** * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection * * @var array */ private $_mapCellStyleXfIndex; /** * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value. * * @var array */ private $_sharedFormulas; /** * The shared formula parts in a sheet. One FORMULA record contributes with one value if it * refers to a shared formula. * * @var array */ private $_sharedFormulaParts; /** * Read data only? * If this is true, then the Reader will only read data values for cells, it will not read any formatting information. * If false (the default) it will read data and formatting. * * @return boolean */ public function getReadDataOnly() { return $this->_readDataOnly; } /** * Set read data only * Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information. * Set to false (the default) to advise the Reader to read both data and formatting for cells. * * @param boolean $pValue * * @return PHPExcel_Reader_Excel5 */ public function setReadDataOnly($pValue = false) { $this->_readDataOnly = $pValue; return $this; } /** * Get which sheets to load * Returns either an array of worksheet names (the list of worksheets that should be loaded), or a null * indicating that all worksheets in the workbook should be loaded. * * @return mixed */ public function getLoadSheetsOnly() { return $this->_loadSheetsOnly; } /** * Set which sheets to load * * @param mixed $value * This should be either an array of worksheet names to be loaded, or a string containing a single worksheet name. * If NULL, then it tells the Reader to read all worksheets in the workbook * * @return PHPExcel_Reader_Excel5 */ public function setLoadSheetsOnly($value = null) { $this->_loadSheetsOnly = is_array($value) ? $value : array($value); return $this; } /** * Set all sheets to load * Tells the Reader to load all worksheets from the workbook. * * @return PHPExcel_Reader_Excel5 */ public function setLoadAllSheets() { $this->_loadSheetsOnly = null; return $this; } /** * Read filter * * @return PHPExcel_Reader_IReadFilter */ public function getReadFilter() { return $this->_readFilter; } /** * Set read filter * * @param PHPExcel_Reader_IReadFilter $pValue * @return PHPExcel_Reader_Excel5 */ public function setReadFilter(PHPExcel_Reader_IReadFilter $pValue) { $this->_readFilter = $pValue; return $this; } /** * Create a new PHPExcel_Reader_Excel5 instance */ public function __construct() { $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter(); } /** * Can the current PHPExcel_Reader_IReader read the file? * * @param string $pFileName * @return boolean */ public function canRead($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } try { // Use ParseXL for the hard work. $ole = new PHPExcel_Shared_OLERead(); // get excel data $res = $ole->read($pFilename); return true; } catch (Exception $e) { return false; } } /** * Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object * * @param string $pFilename * @throws Exception */ public function listWorksheetNames($pFilename) { // Check if file exists if (!file_exists($pFilename)) { throw new Exception("Could not open " . $pFilename . " for reading! File does not exist."); } $worksheetNames = array(); // Read the OLE file $this->_loadOLE($pFilename); // total byte size of Excel data (workbook global substream + sheet substreams) $this->_dataSize = strlen($this->_data); $this->_pos = 0; $this->_sheets = array(); // Parse Workbook Global Substream while ($this->_pos < $this->_dataSize) { $code = self::_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $this->_readBof(); break; case self::XLS_Type_SHEET: $this->_readSheet(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } foreach ($this->_sheets as $sheet) { if ($sheet['sheetType'] != 0x0) { // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module continue; } $worksheetNames[] = $sheet['name']; } return $worksheetNames; } /** * Loads PHPExcel from file * * @param string $pFilename * @return PHPExcel * @throws Exception */ public function load($pFilename) { // Read the OLE file $this->_loadOLE($pFilename); // Initialisations $this->_phpExcel = new PHPExcel(); $this->_phpExcel->removeSheetByIndex(0); // remove 1st sheet if (!$this->_readDataOnly) { $this->_phpExcel->removeCellStyleXfByIndex(0); // remove the default style $this->_phpExcel->removeCellXfByIndex(0); // remove the default style } // Read the summary information stream (containing meta data) $this->_readSummaryInformation(); // Read the Additional document summary information stream (containing application-specific meta data) $this->_readDocumentSummaryInformation(); // total byte size of Excel data (workbook global substream + sheet substreams) $this->_dataSize = strlen($this->_data); // initialize $this->_pos = 0; $this->_codepage = 'CP1252'; $this->_formats = array(); $this->_objFonts = array(); $this->_palette = array(); $this->_sheets = array(); $this->_externalBooks = array(); $this->_ref = array(); $this->_definedname = array(); $this->_sst = array(); $this->_drawingGroupData = ''; $this->_xfIndex = ''; $this->_mapCellXfIndex = array(); $this->_mapCellStyleXfIndex = array(); // Parse Workbook Global Substream while ($this->_pos < $this->_dataSize) { $code = self::_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $this->_readBof(); break; case self::XLS_Type_FILEPASS: $this->_readFilepass(); break; case self::XLS_Type_CODEPAGE: $this->_readCodepage(); break; case self::XLS_Type_DATEMODE: $this->_readDateMode(); break; case self::XLS_Type_FONT: $this->_readFont(); break; case self::XLS_Type_FORMAT: $this->_readFormat(); break; case self::XLS_Type_XF: $this->_readXf(); break; case self::XLS_Type_XFEXT: $this->_readXfExt(); break; case self::XLS_Type_STYLE: $this->_readStyle(); break; case self::XLS_Type_PALETTE: $this->_readPalette(); break; case self::XLS_Type_SHEET: $this->_readSheet(); break; case self::XLS_Type_EXTERNALBOOK: $this->_readExternalBook(); break; case self::XLS_Type_EXTERNNAME: $this->_readExternName(); break; case self::XLS_Type_EXTERNSHEET: $this->_readExternSheet(); break; case self::XLS_Type_DEFINEDNAME: $this->_readDefinedName(); break; case self::XLS_Type_MSODRAWINGGROUP: $this->_readMsoDrawingGroup(); break; case self::XLS_Type_SST: $this->_readSst(); break; case self::XLS_Type_EOF: $this->_readDefault(); break 2; default: $this->_readDefault(); break; } } // Resolve indexed colors for font, fill, and border colors // Cannot be resolved already in XF record, because PALETTE record comes afterwards if (!$this->_readDataOnly) { foreach ($this->_objFonts as $objFont) { if (isset($objFont->colorIndex)) { $color = self::_readColor($objFont->colorIndex, $this->_palette, $this->_version); $objFont->getColor()->setRGB($color['rgb']); } } foreach ($this->_phpExcel->getCellXfCollection() as $objStyle) { // fill start and end color $fill = $objStyle->getFill(); if (isset($fill->startcolorIndex)) { $startColor = self::_readColor($fill->startcolorIndex, $this->_palette, $this->_version); $fill->getStartColor()->setRGB($startColor['rgb']); } if (isset($fill->endcolorIndex)) { $endColor = self::_readColor($fill->endcolorIndex, $this->_palette, $this->_version); $fill->getEndColor()->setRGB($endColor['rgb']); } // border colors $top = $objStyle->getBorders()->getTop(); $right = $objStyle->getBorders()->getRight(); $bottom = $objStyle->getBorders()->getBottom(); $left = $objStyle->getBorders()->getLeft(); $diagonal = $objStyle->getBorders()->getDiagonal(); if (isset($top->colorIndex)) { $borderTopColor = self::_readColor($top->colorIndex, $this->_palette, $this->_version); $top->getColor()->setRGB($borderTopColor['rgb']); } if (isset($right->colorIndex)) { $borderRightColor = self::_readColor($right->colorIndex, $this->_palette, $this->_version); $right->getColor()->setRGB($borderRightColor['rgb']); } if (isset($bottom->colorIndex)) { $borderBottomColor = self::_readColor($bottom->colorIndex, $this->_palette, $this->_version); $bottom->getColor()->setRGB($borderBottomColor['rgb']); } if (isset($left->colorIndex)) { $borderLeftColor = self::_readColor($left->colorIndex, $this->_palette, $this->_version); $left->getColor()->setRGB($borderLeftColor['rgb']); } if (isset($diagonal->colorIndex)) { $borderDiagonalColor = self::_readColor($diagonal->colorIndex, $this->_palette, $this->_version); $diagonal->getColor()->setRGB($borderDiagonalColor['rgb']); } } } // treat MSODRAWINGGROUP records, workbook-level Escher if (!$this->_readDataOnly && $this->_drawingGroupData) { $escherWorkbook = new PHPExcel_Shared_Escher(); $reader = new PHPExcel_Reader_Excel5_Escher($escherWorkbook); $escherWorkbook = $reader->load($this->_drawingGroupData); // debug Escher stream //$debug = new Debug_Escher(new PHPExcel_Shared_Escher()); //$debug->load($this->_drawingGroupData); } // Parse the individual sheets foreach ($this->_sheets as $sheet) { if ($sheet['sheetType'] != 0x0) { // 0x00: Worksheet, 0x02: Chart, 0x06: Visual Basic module continue; } // check if sheet should be skipped if (isset($this->_loadSheetsOnly) && !in_array($sheet['name'], $this->_loadSheetsOnly)) { continue; } // add sheet to PHPExcel object $this->_phpSheet = $this->_phpExcel->createSheet(); $this->_phpSheet->setTitle($sheet['name']); $this->_phpSheet->setSheetState($sheet['sheetState']); $this->_pos = $sheet['offset']; // Initialize isFitToPages. May change after reading SHEETPR record. $this->_isFitToPages = false; // Initialize drawingData $this->_drawingData = ''; // Initialize objs $this->_objs = array(); // Initialize shared formula parts $this->_sharedFormulaParts = array(); // Initialize shared formulas $this->_sharedFormulas = array(); // Initialize text objs $this->_textObjects = array(); // Initialize cell annotations $this->_cellNotes = array(); $this->textObjRef = -1; while ($this->_pos <= $this->_dataSize - 4) { $code = self::_GetInt2d($this->_data, $this->_pos); switch ($code) { case self::XLS_Type_BOF: $this->_readBof(); break; case self::XLS_Type_PRINTGRIDLINES: $this->_readPrintGridlines(); break; case self::XLS_Type_DEFAULTROWHEIGHT: $this->_readDefaultRowHeight(); break; case self::XLS_Type_SHEETPR: $this->_readSheetPr(); break; case self::XLS_Type_HORIZONTALPAGEBREAKS: $this->_readHorizontalPageBreaks(); break; case self::XLS_Type_VERTICALPAGEBREAKS: $this->_readVerticalPageBreaks(); break; case self::XLS_Type_HEADER: $this->_readHeader(); break; case self::XLS_Type_FOOTER: $this->_readFooter(); break; case self::XLS_Type_HCENTER: $this->_readHcenter(); break; case self::XLS_Type_VCENTER: $this->_readVcenter(); break; case self::XLS_Type_LEFTMARGIN: $this->_readLeftMargin(); break; case self::XLS_Type_RIGHTMARGIN: $this->_readRightMargin(); break; case self::XLS_Type_TOPMARGIN: $this->_readTopMargin(); break; case self::XLS_Type_BOTTOMMARGIN: $this->_readBottomMargin(); break; case self::XLS_Type_PAGESETUP: $this->_readPageSetup(); break; case self::XLS_Type_PROTECT: $this->_readProtect(); break; case self::XLS_Type_SCENPROTECT: $this->_readScenProtect(); break; case self::XLS_Type_OBJECTPROTECT: $this->_readObjectProtect(); break; case self::XLS_Type_PASSWORD: $this->_readPassword(); break; case self::XLS_Type_DEFCOLWIDTH: $this->_readDefColWidth(); break; case self::XLS_Type_COLINFO: $this->_readColInfo(); break; case self::XLS_Type_DIMENSION: $this->_readDefault(); break; case self::XLS_Type_ROW: $this->_readRow(); break; case self::XLS_Type_DBCELL: $this->_readDefault(); break; case self::XLS_Type_RK: $this->_readRk(); break; case self::XLS_Type_LABELSST: $this->_readLabelSst(); break; case self::XLS_Type_MULRK: $this->_readMulRk(); break; case self::XLS_Type_NUMBER: $this->_readNumber(); break; case self::XLS_Type_FORMULA: $this->_readFormula(); break; case self::XLS_Type_SHAREDFMLA: $this->_readSharedFmla(); break; case self::XLS_Type_BOOLERR: $this->_readBoolErr(); break; case self::XLS_Type_MULBLANK: $this->_readMulBlank(); break; case self::XLS_Type_LABEL: $this->_readLabel(); break; case self::XLS_Type_BLANK: $this->_readBlank(); break; case self::XLS_Type_MSODRAWING: $this->_readMsoDrawing(); break; case self::XLS_Type_OBJ: $this->_readObj(); break; case self::XLS_Type_WINDOW2: $this->_readWindow2(); break; case self::XLS_Type_SCL: $this->_readScl(); break; case self::XLS_Type_PANE: $this->_readPane(); break; case self::XLS_Type_SELECTION: $this->_readSelection(); break; case self::XLS_Type_MERGEDCELLS: $this->_readMergedCells(); break;
oci_execute($projectDateParse); $projectDate = oci_fetch_array($projectDateParse)['PD']; //Set properties, isi teks ini bisa anda lihat //di file excel yang dihasilkan, klik kanan file tersebut //dan pilih properties. $objPHPExcel->getProperties()->setCreator("PT. Weltes Energi Nusantara")->setLastModifiedBy("{$username}")->setTitle("Site Erection Project Report for {$jobName}")->setSubject("Site Erection Project Report for {$jobName}")->setDescription("Site Erection Project Report for {$jobName}")->setKeywords("Site Erection Project Report for {$jobName}")->setCategory("Site Erection Project Report"); $gdImage = imagecreatefromjpeg('logo_weltes_resized.jpg'); // Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n"; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setCoordinates('A1'); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(200); $objDrawing->setWidth(200); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $styleTitle = array('font' => array('bold' => true, 'underline' => true, 'shrinkToFit' => true, 'size' => 11, 'name' => 'Trebuchet'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $styleDTNow = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)); $styleArray1 = array('font' => array('bold' => true, 'shrinkToFit' => true, 'size' => 8, 'name' => 'Verdana')); $styleArray2 = array('font' => array('bold' => true, 'shrinkToFit' => true, 'size' => 8, 'name' => 'Verdana'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $styleArrayRight = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $styleBorder = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $styleBold = array('font' => array('bold' => true, 'shrinkToFit' => true, 'size' => 8, 'name' => 'Verdana')); $styleTitle = array('font' => array('bold' => true, 'shrinkToFit' => true, 'size' => 14, 'name' => 'Verdana'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)); $stylefont8 = array('font' => array('size' => 8, 'name' => 'Verdana')); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A4', "PROJECT"); $objPHPExcel->getActiveSheet()->getStyle("A4")->applyFromArray($styleArray2); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A5', "OWNER"); $objPHPExcel->getActiveSheet()->getStyle("A5")->applyFromArray($styleArray2);
public function getExcel() { //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle('REKAPITULASI KEGIATAN PP P2TL'); //set cell A1 content with some text $this->excel->getActiveSheet()->setCellValue('B1', 'PT PLN (PERSERO) DISTRIBUSI'); $this->excel->getActiveSheet()->setCellValue('B2', 'JAWA BARAT DAN BANTEN'); $this->excel->getActiveSheet()->setCellValue('B3', 'AREA BEKASI - RAYON BEKASI KOTA'); $this->excel->getActiveSheet()->setCellValue('A4', 'REKAPITULASI KEGIATAN PP P2TL PT .YASA EKPANSIA SEJAHTERA'); $this->excel->getActiveSheet()->setCellValue('A5', 'BULAN JUNI TAHUN 2015'); $this->excel->getActiveSheet()->setCellValue('A8', 'NO. URUT'); // Ukuran Kolom $this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(4); // Ukuran Baris $this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1); //change the font size $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(8); $this->excel->getActiveSheet()->getStyle('B2')->getFont()->setSize(8); $this->excel->getActiveSheet()->getStyle('B3')->getFont()->setSize(8); $this->excel->getActiveSheet()->getStyle('A4')->getFont()->setSize(12); $this->excel->getActiveSheet()->getStyle('A5')->getFont()->setSize(11); $this->excel->getActiveSheet()->getStyle('A8')->getFont()->setSize(9); // make the font become bold $this->excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true); $this->excel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true); //merge cell A1 until D1 $this->excel->getActiveSheet()->mergeCells('B1:E1'); $this->excel->getActiveSheet()->mergeCells('B2:E2'); $this->excel->getActiveSheet()->mergeCells('B3:E3'); $this->excel->getActiveSheet()->mergeCells('A4:P4'); $this->excel->getActiveSheet()->mergeCells('A5:P5'); // Merge Row $this->excel->getActiveSheet()->mergeCells('A8:A11'); // Inserting Image $gdImage = imagecreatefromjpeg(base_url() . 'assets/logopln.jpg'); // Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n"; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setWidth(25); $objDrawing->setHeight(60); $objDrawing->setCoordinates('A1'); $objDrawing->setWorksheet($this->excel->getActiveSheet()); //set aligment to center for that merged cell (A1 to D1) $this->excel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle('A5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $this->excel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $filename = 'just_some_random_name.xls'; //save our workbook as this file name header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }
protected function writeDatamatrix(Deposit $deposit, $path = null) { if (!$path) { $path = dirname($this->xlsFile) . '/' . $deposit->getNumber() . '.png'; } $key = $this->getKey($deposit); if (!function_exists('getDataMatrix')) { require_once __DIR__ . '/Barcode.php'; } $im = getDataMatrix($key); $objDrawing = new \PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('DATAMATRIX'); $objDrawing->setDescription('POST DATAMATRIX'); $objDrawing->setImageResource($im); $objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG); $objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG); $objDrawing->setWorksheet($this->objExcel->getActiveSheet()); $objDrawing->setCoordinates('BE18'); $objDrawing->setHeight(140); $objDrawing->setWidth(140); $objDrawing->setResizeProportional(100); return $path; }
//copiamos el excel de plantilla a su salido final $fileName = "./matriculas/Matricula" . time() . ".xlsx"; copy("./matriculas/template.xlsx", $fileName); //tomamos el archivo original y lo copiamos como una plantilla $objReader = new PHPExcel_Reader_Excel2007(); $objPHPExcel = $objReader->load($fileName); //agregamos el logo del colegio $gdImage = imagecreatefromjpeg('./matriculas/LogoColegio.jpg'); // Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n"; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('LogoColegio'); $objDrawing->setDescription('LogoColegio'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(180); $objDrawing->setCoordinates("D1"); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); // Set properties $objPHPExcel->getProperties()->setCreator("Isaac Reyes"); $objPHPExcel->getProperties()->setLastModifiedBy("Isaac Reyes"); $objPHPExcel->getProperties()->setTitle("Archivo de Matricula"); $objPHPExcel->getProperties()->setSubject("Archivo de Matricula"); $objPHPExcel->getProperties()->setDescription("Archivo de Matricula"); $objPHPExcel->setActiveSheetIndex(0); // Rename sheet //echo date('H:i:s') . " Rename sheet\n"; $objPHPExcel->getActiveSheet()->setTitle('Matricula'); // Save Excel 2007 file //echo date('H:i:s') . " Write to Excel2007 format\n"; // Add some data
$ews2->setCellValue('a' . $i, "='Raw_Data'!A" . $i); $ews2->setCellValue('b' . $i, "=B" . ($i - 1) . "+G1*('Raw_Data'!B" . $i . "-B" . ($i - 1) . ")"); $ews2->setCellValue('c' . $i, "=C" . ($i - 1) . "+G1*('Raw_Data'!C" . $i . "-C" . ($i - 1) . ")"); $ews2->setCellValue('d' . $i, "=D" . ($i - 1) . "+G1*('Raw_Data'!D" . $i . "-D" . ($i - 1) . ")"); } $ews2->getRowDimension(1)->setRowHeight(59); $ews2->getColumnDimension('L')->setWidth(42); $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setCoordinates('L1'); $objDrawing->setWidth(300); $objDrawing->setHeight(38); $objDrawing->setOffsetX(20); $objDrawing->setOffsetY(25); $objDrawing->setWorksheet($ews2); $dsl2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$B$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$C$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$D$1', NULL, 1)); $xal2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$A$2:$A$' . $ca, NULL, $ca)); $dsv2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$B$2:$B$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$C$2:$C$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$D$2:$D$' . $ca, NULL, $ca)); $dsv2[0]->setPointMarker('none'); $dsv2[1]->setPointMarker('none'); $dsv2[2]->setPointMarker('none'); $ds2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv2) - 1), $dsl2, $xal2, $dsv2); $pa2 = new PHPExcel_Chart_PlotArea(NULL, array($ds2)); $legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false); $chart2 = new PHPExcel_Chart('chart2', NULL, $legend2, $pa2, true, 0, NULL, NULL); $chart2->setTopLeftPosition('F3'); $chart2->setBottomRightPosition('Z22');
/** * 导出excel表格 * * @param $datas 二维数据 * @param $name excel表格的名称,不包含.xlsx * 填充表格的数据 * @example $datas['title'] = array('col1','col2','col3','col4'); * $datas['result'] = array(array('v11','v12','v13','v14') * array('v21','v22','v23','v24')); * @return 直接浏览器输出excel表格 注意这个函数前不能有任何形式的输出 * */ function arrayToExcel($datas, $name = '', $output = 'php://output') { resetTimeMemLimit(); if (empty($name)) { $name = 'export_' . date("Y_m_d_H_i_s"); } // 便于处理大的大型excel表格,存储在磁盘缓存中 $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM; PHPExcel_Settings::setCacheStorageMethod($cacheMethod); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator('icc'); $objPHPExcel->getProperties()->setLastModifiedBy('icc'); $objPHPExcel->getProperties()->setTitle($name); $objPHPExcel->getProperties()->setSubject($name); $objPHPExcel->getProperties()->setDescription($name); $objPHPExcel->setActiveSheetIndex(0); $total = count($datas['title']); for ($i = 0; $i < $total; $i++) { $objPHPExcel->getActiveSheet()->getColumnDimension(excelTitle($i))->setAutoSize(true); $objPHPExcel->getActiveSheet()->SetCellValue(excelTitle($i) . '1', $datas['title'][$i]); } $i = 2; foreach ($datas['result'] as $data) { $j = 0; foreach ($data as $cell) { // 判断是否为图片,如果是图片,那么绘制图片 if (is_array($cell) && isset($cell['type']) && $cell['type'] == 'image') { $coordinate = excelTitle($j) . $i; $cellName = isset($cell['name']) ? $cell['name'] : ''; $cellDesc = isset($cell['desc']) ? $cell['desc'] : ''; $cellType = isset($cell['type']) ? $cell['type'] : ''; $cellUrl = isset($cell['url']) ? $cell['url'] : ''; $cellHeight = isset($cell['height']) ? intval($cell['height']) : 0; if ($cellType == 'image') { if ($cellHeight == 0) { $cellHeight = 20; } $image = imagecreatefromstring(file_get_contents($cellUrl)); $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName($cellName); $objDrawing->setDescription($cellDesc); $objDrawing->setImageResource($image); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight($cellHeight); $objDrawing->setCoordinates($coordinate); // 填充到某个单元格 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight($cellHeight); } else { $objPHPExcel->getActiveSheet()->setCellValueExplicit($coordinate, $cellName, PHPExcel_Cell_DataType::TYPE_STRING); } // 添加链接 $objPHPExcel->getActiveSheet()->getCell($coordinate)->getHyperlink()->setUrl($cellUrl); $objPHPExcel->getActiveSheet()->getCell($coordinate)->getHyperlink()->setTooltip($cellName . ':' . $cellDesc); } else { if (is_array($cell)) { $objPHPExcel->getActiveSheet()->setCellValueExplicit(excelTitle($j) . $i, json_encode($cell), PHPExcel_Cell_DataType::TYPE_STRING); } else { $objPHPExcel->getActiveSheet()->setCellValueExplicit(excelTitle($j) . $i, $cell, PHPExcel_Cell_DataType::TYPE_STRING); } } $j++; } $i++; } $objPHPExcel->getActiveSheet()->setTitle('Sheet1'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); if ($output === 'php://output') { header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $name . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter->save($output); exit; } $objWriter->save($output); return true; }
<?php require_once '../../../dbinfo.inc.php'; //include file PHPExcel dan konfigurasi database require_once '../PHPExcel.php'; // Buat object PHPExcel $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("PT. Weltes Energi Nusantara")->setLastModifiedBy("1")->setTitle("Site Erection Project Report for 1")->setSubject("Site Erection Project Report for 1")->setDescription("Site Erection Project Report for 1")->setKeywords("Site Erection Project Report for 1")->setCategory("Site Erection Project Report"); $gdImage = imagecreatefromjpeg('logo_weltes_resized.jpg'); // Add a drawing to the worksheetecho date('H:i:s') . " Add a drawing to the worksheet\n"; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image'); $objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setCoordinates('B15'); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(140); $objDrawing->setWidth(140); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="nettweight' . 1 . '_' . 2 . '.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit;
function fn_price_list_print_product_data($product, &$worksheet, $row, &$width, $selected_fields, $styles, $options_variants = array()) { $col = 'A'; foreach ($selected_fields as $field => $active) { $worksheet->getStyle($col . $row)->applyFromArray($row % 2 == 0 ? $styles['field_simple_odd'] : $styles['field_simple']); if ($field == 'image') { $image_data = fn_image_to_display($product['main_pair'], Registry::get('settings.Thumbnails.product_lists_thumbnail_width'), Registry::get('settings.Thumbnails.product_lists_thumbnail_height')); if (!empty($image_data)) { $mime_type = fn_get_file_type($image_data['absolute_path']); $src = $image_data['absolute_path']; $image_width = $image_data['width']; $image_height = $image_data['height']; if ($mime_type == 'image/gif' && function_exists('imagecreatefromgif')) { $img_res = imagecreatefromgif($src); } elseif ($mime_type == 'image/jpeg' && function_exists('imagecreatefromjpeg')) { $img_res = imagecreatefromjpeg($src); } elseif ($mime_type == 'image/png' && function_exists('imagecreatefrompng')) { $img_res = imagecreatefrompng($src); } else { $img_res = false; } if ($img_res) { if (!isset($width[$col]) || $width[$col] < $image_width) { $width[$col] = $image_width * IMAGE_WIDTH_PERCENT; } $img_descr = $image_data['alt']; $drawing = new PHPExcel_Worksheet_MemoryDrawing(); $drawing->setName($img_descr); $drawing->setDescription($img_descr); $drawing->setImageResource($img_res); $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $drawing->setHeight($image_height); $drawing->setWorksheet($worksheet); $worksheet->getRowDimension($row)->setRowHeight($image_height * IMAGE_HEIGHT_PERCENT); $drawing->setCoordinates($col . $row); } } } else { if ($field == 'price') { $product[$field] = fn_format_price($product[$field], CART_PRIMARY_CURRENCY, null, false); } if (!isset($width[$col]) || $width[$col] < strlen($product[$field])) { $width[$col] = strlen($product[$field]); } if (!empty($options_variants) && $field == 'product') { $options = array(); foreach ($options_variants as $option_id => $variant_id) { $option = $product['product_options'][$option_id]['option_name'] . ': ' . $product['product_options'][$option_id]['variants'][$variant_id]['variant_name']; $options[] = $option; if ($width[$col] < strlen($option)) { $width[$col] = strlen($options); } } $options = implode("\n", $options); $worksheet->setCellValue($col . $row, $product['product'] . "\n" . $options); } elseif ($field == 'price') { $worksheet->getCell($col . $row)->setValueExplicit($product[$field], PHPExcel_Cell_DataType::TYPE_STRING); } else { $worksheet->setCellValue($col . $row, $product[$field]); } } $col++; } return true; }
public function writeImageOnExcel($objPHPExcel, $img_url, $coordinates) { $gdImage = $this->createImageFromFile($img_url); $objDrawing = new \PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $objDrawing->setHeight(350); $objDrawing->setCoordinates($coordinates); return $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); }
function foo($products) { // Подключаем класс для работы с excel require_once '../Classes/PHPExcel.php'; // Подключаем класс для вывода данных в формате excel require_once '../Classes/PHPExcel/Writer/Excel5.php'; // Создаем объект класса PHPExcel $xls = new PHPExcel(); // Устанавливаем индекс активного листа $xls->setActiveSheetIndex(0); // Получаем активный лист $sheet = $xls->getActiveSheet(); // Подписываем лист $sheet->setTitle('Таблица умножения'); //Стиль для заголовков $styleHeaders = array('font' => array('bold' => true, 'size' => 14)); //Тестовый json //$json = '{"a":1,"b":2,"c":3,"d":4,"e":5}'; //$test_array = json_decode($json); //Тестовый массив //$test_array = array("a" => "orange", "b" => "banana", "c" => "apple"); //Заголовки $sheet->getColumnDimension('A')->setWidth(10); //Центруем текст по горизонтали $sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("B1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("C1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //Задаем стили $sheet->getStyle("A1")->applyFromArray($styleHeaders); $sheet->getStyle("B1")->applyFromArray($styleHeaders); $sheet->getStyle("C1")->applyFromArray($styleHeaders); //Задаем высоту верхней строки $sheet->getRowDimension(1)->setRowHeight(50); //Заносим значения $sheet->setCellValueByColumnAndRow(0, 1, 'Фото'); $sheet->setCellValueByColumnAndRow(1, 1, 'Наименование'); $sheet->setCellValueByColumnAndRow(2, 1, 'Кол-во (шт.)'); //Данные $row_count = 2; foreach ($products as &$prod) { //Изображение $imagePath = realpath(dirname(dirname(__FILE__))) . '/files/originals/' . $prod->image->url; if (file_exists($imagePath)) { $logo = new PHPExcel_Worksheet_MemoryDrawing(); $logo->setName($prod->name); $gdImage = imagecreatefromjpeg($imagePath); $logo->setImageResource($gdImage); $logo->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $logo->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $logo->setHeight(50); $logo->setCoordinates("A" . $row_count); $logo->setOffsetX(2); $logo->setOffsetY(5); $sheet->getRowDimension($row_count)->setRowHeight(45); $logo->setWorksheet($sheet); } //$sheet->setCellValueByColumnAndRow(0, $row_count, $prod->image->url); $sheet->setCellValueByColumnAndRow(1, $row_count, $prod->name); $sheet->setCellValueByColumnAndRow(2, $row_count, $prod->summ); $sheet->getStyle("B" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("B" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $sheet->getStyle("C" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("C" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $row_count++; } //Автоматическая ширина столбцов foreach (range('B', 'D') as $columnID) { $sheet->getColumnDimension($columnID)->setAutoSize(true); } //$row_count=1; //foreach($test_array as $key => $value){ //$sheet->setCellValueByColumnAndRow(0, $row_count, $key); //$sheet->setCellValueByColumnAndRow(1, $row_count, $value); //$row_count++; //} // Выводим HTTP-заголовки header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=matrix.xls"); // Выводим содержимое файла $objWriter = new PHPExcel_Writer_Excel5($xls); $objWriter->save('php://output'); return $retval; }
$sheet->getHeaderFooter()->setOddHeader('&CТекст верхнего колонтитула'); //Данные $row_count = 4; foreach ($products as &$prod) { //Изображение //$imagePath = realpath(dirname(dirname(__FILE__))) . '../files/originals/' . $prod->image->url; //$imagePath = realpath(dirname(dirname(__FILE__))) . '../files/originals/' . $prod->image->url; $imagePath = '/home/v/vdmgrup/rostokgroup.ru/public_html/files/originals/' . $prod->image->url; if (file_exists($imagePath)) { $logo = new PHPExcel_Worksheet_MemoryDrawing(); $logo->setName($prod->name); $gdImage = imagecreatefromjpeg($imagePath); $logo->setImageResource($gdImage); $logo->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $logo->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); $logo->setHeight(50); $logo->setCoordinates("A" . $row_count); $logo->setOffsetX(2); $logo->setOffsetY(5); $sheet->getRowDimension($row_count)->setRowHeight(45); $logo->setWorksheet($sheet); } //$sheet->setCellValueByColumnAndRow(0, $row_count, $prod->image->url); $sheet->setCellValueByColumnAndRow(1, $row_count, $prod->name); $sheet->setCellValueByColumnAndRow(2, $row_count, $prod->summ); $sheet->getStyle("B" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("B" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $sheet->getStyle("C" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $sheet->getStyle("C" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $row_count++; }