/** * 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;
function export_xls_fin() { //get submit parameters $col_name = $this->input->get_post("ckod"); $cou_id = $this->input->get_post("kursus"); $current_sem = $this->input->get_post("semtr"); $current_year = $this->input->get_post("tahun"); $status_stu = $this->input->get_post("stts"); //get values $list_fin = $this->m_report->get_fin_detail($col_name, $cou_id, $current_sem, $current_year, $status_stu); //for debug purpose only, safe to delete /*echo"<pre>"; print_r($list_fin); echo"</pre>"; die();*/ //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); if ("" == $col_name && "" == $cou_id) { //name the worksheet $this->excel->getActiveSheet()->setTitle("Semua KV dan Kursus"); $filename = 'FIN_Semua_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx'; //save our workbook as this file name } else { if ("" == $col_name && "" != $cou_id) { //name the worksheet $this->excel->getActiveSheet()->setTitle("Semua KV"); $filename = 'FIN_Kolej_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx'; //save our workbook as this file name } else { if ("" != $col_name && "" == $cou_id) { //name the worksheet $this->excel->getActiveSheet()->setTitle("Semua Kursus"); $filename = 'FIN_Kursus_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx'; //save our workbook as this file name } else { //name the worksheet $this->excel->getActiveSheet()->setTitle($list_fin[0]->col_name . ' - ' . $list_fin[0]->cou_name); $filename = 'FIN_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx'; //save our workbook as this file name } } } $namakolej = ""; $namakursus = ""; if ($current_sem >= 4) { //set the informations $namakolej = $list_fin[0]->col_name; if ("" != $col_name && "" != $cou_id) { $namakursus = $list_fin[0]->cou_name; $this->excel->getActiveSheet()->setCellValue('B3', "KURSUS " . strtoupper($namakursus)); } else { $this->excel->getActiveSheet()->setCellValue('B3', "SEMUA KURSUS"); } $this->excel->getActiveSheet()->setCellValue('B2', "FAIL INDUK NAMA {$namakolej}"); $this->excel->getActiveSheet()->setCellValue('B4', "SEMESTER {$current_sem} TAHUN {$current_year}"); $this->excel->getActiveSheet()->mergeCells('B2:I2'); $this->excel->getActiveSheet()->mergeCells('B3:I3'); $this->excel->getActiveSheet()->mergeCells('B4:I4'); } else { //set the informations $namakolej = $list_fin[0]->col_name; if ("" != $col_name && "" != $cou_id) { $namakursus = $list_fin[0]->cou_name; $this->excel->getActiveSheet()->setCellValue('B3', "KURSUS " . strtoupper($namakursus)); } else { $this->excel->getActiveSheet()->setCellValue('B3', "SEMUA KURSUS"); } $this->excel->getActiveSheet()->setCellValue('B2', "FAIL INDUK NAMA {$namakolej}"); $this->excel->getActiveSheet()->setCellValue('B4', "SEMESTER {$current_sem} TAHUN {$current_year}"); $this->excel->getActiveSheet()->mergeCells('B2:F2'); $this->excel->getActiveSheet()->mergeCells('B3:F3'); $this->excel->getActiveSheet()->mergeCells('B4:F4'); if (file_exists("./uploaded/kvinfo/Logokolej_small.jpg")) { $gdImage = imagecreatefromjpeg('./uploaded/kvinfo/Logokolej_small.jpg'); $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setImageResource($gdImage); //$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); // image rendering. //$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); // nanti image jadi low quality $objDrawing->setOffsetX(60); $objDrawing->setOffsetY(-5); $objDrawing->setCoordinates('G2'); $objDrawing->setWorksheet($this->excel->getActiveSheet()); } if (file_exists("./uploaded/kvinfo/Copkolej_medium.png")) { $copImage = imagecreatefrompng('./uploaded/kvinfo/Copkolej_medium.png'); $transparent = imagecolorallocate($copImage, 0, 0, 0); // set color transperent utk image // Make the background transparent imagecolortransparent($copImage, $transparent); // kalau xde ni, background jadi hitam.. $copDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $copDrawing->setImageResource($copImage); $copDrawing->setOffsetX(60); $copDrawing->setOffsetY(-5); $copDrawing->setCoordinates('H18'); $copDrawing->setWorksheet($this->excel->getActiveSheet()); } $this->excel->getActiveSheet()->mergeCellsByColumnAndRow($pColumn1 = 6, $pRow1 = 2, $pColumn2 = 8, $pRow2 = 4); } //style for the above information $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $this->excel->getActiveSheet()->getStyle('B1:B5')->applyFromArray($styleArray); //apply the style to the cells $highlightCells = array(); //header $excel_header = array('Bil', 'Nama Calon', 'MyKad', 'Angka Giliran', 'Kod Kursus', 'Jantina', 'Kaum', 'Agama'); $ttl = 0; $columnCount = 65; //data $index = 1; $excel_data = array(); foreach ($list_fin as $rowData) { $r = array(); array_push($r, $index); foreach ($rowData as $key => $value) { if ("stu_name" == $key || "stu_mykad" == $key || "stu_matric_no" == $key || "cou_course_code" == $key || "stu_gender" == $key || "stu_race" == $key || "stu_religion" == $key) { if ("stu_religion" == $key) { $tempreligion = $value; if ("NULL" == $tempreligion) { $value = "-"; } } array_push($r, " " . strval(name_strtoupper($value))); } } array_push($excel_data, $r); $index++; } //load the header into position B6 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'B6'); //load the data into position B7 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'B7'); //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFC000')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('B6:I6')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $this->excel->getActiveSheet()->getStyle('B6:I' . (5 + $index))->applyFromArray($borderStyleArray); $this->excel->getActiveSheet()->getStyle('D6:D' . (5 + $index))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //set auto resize for all the columns for ($col = 65; $col <= 90; $col++) { $this->excel->getActiveSheet()->getColumnDimension(chr($col))->setAutoSize(true); } 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) //if you want to save it as .XLSX Excel 2007 format $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007'); //force user to download the Excel file without writing it to server's HD $objWriter->save('php://output'); }
function export_xls_jadual_kedatangan() { $abc = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL'); $centreCode = $this->input->get('kodpusat'); $semester = $this->input->get('semester'); $year = $this->input->get('slct_tahun'); $course = $this->input->get('slct_kursus'); $module = $this->input->get('modul'); $status = $this->input->get('statusID'); $cC = explode("-", $centreCode); $data['student'] = $this->m_result->get_module_taken($cC[0], $semester, $year, $course, $status, $module); $data['nama_kursus'] = $this->m_result->ambik_nama_kursus($course); //$data['nama_modul'] = $this->m_result->get_module_jadual_kedatangan($course); /**FDPO - Safe to be deleted**/ //echo('<pre>');print_r($module);echo('</pre>'); //die(); $index = sizeof($data['student']); //load our new PHPExcel library $this->load->library('excel'); //activate worksheet number 1 $this->excel->setActiveSheetIndex(0); //name the worksheet $this->excel->getActiveSheet()->setTitle("Jadual Kedatangan Calon"); //$highlightCells = array(); //header $excel_header = array('BIL', 'NAMA', 'ANGKA GILIRAN', 'TINDAKAN'); //$excel_jadual = array(); //$data['student'] = $this->m_result->get_module_taken($cC[0], $semester,$year,$course,$status,$module); $filename = 'Jadual Kedatangan Calon_' . $data['nama_kursus']->cou_name . '_' . $module . '.xls'; //save our workbook as this file name //load the header into position A1 $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'A10'); // die(); $ttl = 0; $columnCount = 65; //masukkkan data disini $index = 1; $excel_data = array(); //$j = 0; if (isset($data['student'])) { //array_push($r, $index); $tidakHadir = 1; $hospital = 2; foreach ($data['student'] as $p) { $r = array(); array_push($r, $index); array_push($r, strval(name_strtoupper($p->stu_name))); array_push($r, $p->stu_matric_no); if ($p->na_status == $tidakHadir) { array_push($r, "T"); } else { if ($p->na_status == $hospital) { array_push($r, "H"); } else { array_push($r, "/"); } } array_push($excel_data, $r); $index++; } } //echo "<pre>"; //print_r($excel_data); //echo "</pre>"; //die(); //load the data into position C4 $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'A11'); //set the informations $this->excel->getActiveSheet()->setCellValue('A2', 'Jadual Kedatangan Calon'); $this->excel->getActiveSheet()->mergeCells('A2:Z2'); $this->excel->getActiveSheet()->setCellValue('A3', 'Kursus : ' . $data['nama_kursus']->cou_name); $this->excel->getActiveSheet()->mergeCells('A3:Z3'); $this->excel->getActiveSheet()->setCellValue('A4', 'Semester : ' . $semester); $this->excel->getActiveSheet()->mergeCells('A4:Z4'); $this->excel->getActiveSheet()->setCellValue('A5', 'T = Tidak Hadir'); $this->excel->getActiveSheet()->mergeCells('A5:Z5'); $this->excel->getActiveSheet()->setCellValue('A6', 'H = Hospital'); $this->excel->getActiveSheet()->mergeCells('A6:Z6'); $this->excel->getActiveSheet()->setCellValue('A7', '/ = Hadir'); $this->excel->getActiveSheet()->mergeCells('A7:Z7'); if (file_exists("./uploaded/kvinfo/Logokolej_small.jpg")) { $gdImage = imagecreatefromjpeg('./uploaded/kvinfo/Logokolej_small.jpg'); $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); // image rendering. $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT); // nanti image jadi low quality $objDrawing->setOffsetX(60); $objDrawing->setOffsetY(-5); $objDrawing->setCoordinates('C2'); $objDrawing->setWorksheet($this->excel->getActiveSheet()); } if (file_exists("./uploaded/kvinfo/Copkolej_medium.png")) { $copImage = imagecreatefrompng('./uploaded/kvinfo/Copkolej_medium.png'); $transparent = imagecolorallocate($copImage, 0, 0, 0); // set color transperent utk image // Make the background transparent imagecolortransparent($copImage, $transparent); // kalau xde ni, background jadi hitam.. $copDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $copDrawing->setImageResource($copImage); $copDrawing->setOffsetX(60); $copDrawing->setOffsetY(-5); $copDrawing->setCoordinates('B27'); $copDrawing->setWorksheet($this->excel->getActiveSheet()); } //style for the above information $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)); $this->excel->getActiveSheet()->getStyle('A1:A9')->applyFromArray($styleArray); //apply thee style to the cells //center alignment $styleArrayCenter = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $this->excel->getActiveSheet()->getStyle('D11:D500')->applyFromArray($styleArrayCenter); //apply thee style to the cells //border fill color for header $style_header = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FFC000')), 'font' => array('bold' => true)); $this->excel->getActiveSheet()->getStyle('A10:' . $abc[sizeof($excel_header) - 1] . '10')->applyFromArray($style_header); //apply the border fill //style to set border $borderStyleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN))); $this->excel->getActiveSheet()->getStyle('A10:' . $abc[sizeof($excel_header) - 1] . (9 + $index))->applyFromArray($borderStyleArray); //style for data even row, we will set color for even rows of data $style_even_row = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FDE9D9'))); for ($i = 2; $i < $index; $i++) { if ($i % 2 == 0) { $this->excel->getActiveSheet()->getStyle('A' . ($i + 9) . ':' . $abc[sizeof($excel_header) - 1] . (9 + $i))->applyFromArray($style_even_row); } } //ni untuk on filter //$this->excel->getActiveSheet()->setAutoFilter('A1:'.$abc[(sizeof($excel_header)-1)].($index)); //$this->excel->getActiveSheet()->getCell('A1:'.$abc[(sizeof($excel_header)-1)].'1')->setAutoSize( true ); //apply the border fill //$this->excel->getActiveSheet()->getStyle(''.($index))-> //getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); //set auto resize for all the columns for ($col = 0; $col <= sizeof($excel_header) - 1; $col++) { $this->excel->getActiveSheet()->getColumnDimension($abc[$col])->setAutoSize(true); } //$blocksList = implode (", ", $mark); $objValidation = $this->excel->getActiveSheet()->getCell('K1')->getDataValidation(); /*$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP); $objValidation->setOperator( PHPExcel_Cell_DataValidation::OPERATOR_LESSTHANOREQUAL); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Only Number is permitted!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only numbers between 1.0 and 100.0 are allowed.'); $objValidation->setFormula1(1.0); $objValidation->setFormula2(100.0);*/ $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 1], 1); $this->excel->getActiveSheet()->removeColumn($abc[sizeof($excel_header) + 2], 1); 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) //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'); }
$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'); $ews2->addChart($chart2);
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; }
$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++; } //Автоматическая ширина столбцов foreach (range('B', 'D') as $columnID) {