function ExportExcelRecord($arrdata, $datatype, $numberRow, $objPHPExcel, $pageObj)
{
    global $cCharset, $locale_info;
    $col = -1;
    $objASIndex = $objPHPExcel->setActiveSheetIndex(0);
    $objASheet = $objPHPExcel->getActiveSheet();
    $rowDim = $objASIndex->getRowDimension($numberRow + 1);
    foreach ($arrdata as $field => $data) {
        $col++;
        $colLetter = PHPExcel_Cell::stringFromColumnIndex($col);
        $colDim = $objASIndex->getColumnDimension($colLetter);
        if ($datatype[$field] == "binary") {
            if (!$data) {
                continue;
            }
            if (!function_exists("imagecreatefromstring")) {
                $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, "LONG BINARY DATA - CANNOT BE DISPLAYED");
                continue;
            }
            $error_handler = set_error_handler("empty_error_handler");
            $gdImage = imagecreatefromstring($data);
            if ($error_handler) {
                set_error_handler($error_handler);
            }
            if ($gdImage) {
                $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
                $objDrawing->setImageResource($gdImage);
                $objDrawing->setCoordinates($colLetter . ($row + 1));
                $objDrawing->setWorksheet($objASheet);
                $width = $objDrawing->getWidth() * 0.143;
                $height = $objDrawing->getHeight() * 0.75;
                if ($rowDim->getRowHeight() < $height) {
                    $rowDim->setRowHeight($height);
                }
                $colDimSh = $objASheet->getColumnDimension($colLetter);
                $colDimSh->setAutoSize(false);
                if ($colDim->getWidth() < $width) {
                    $colDim->setWidth($width);
                }
            }
        } elseif ($datatype[$field] == "file") {
            $arr = my_json_decode($row[$field]);
            if (count($arr) == 0) {
                $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
                if ($data == "<img src=\"images/no_image.gif\" />") {
                    $arr[] = array("name" => "images/no_image.gif");
                } else {
                    if (substr($data, 0, 1) == '=') {
                        $data = '="' . str_replace('"', '""', $data) . '"';
                    }
                    $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
                    continue;
                }
            }
            $offsetY = 0;
            $height = 0;
            foreach ($arr as $img) {
                if (!file_exists($img["name"]) || !$img["name"]) {
                    $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
                    if (substr($data, 0, 1) == '=') {
                        $data = '="' . str_replace('"', '""', $data) . '"';
                    }
                    $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
                    continue;
                }
                $objDrawing = new PHPExcel_Worksheet_Drawing();
                $objDrawing->setPath($img["name"]);
                $objDrawing->setCoordinates($colLetter . ($numberRow + 1));
                $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                $objDrawing->setOffsetY($offsetY);
                $width = $objDrawing->getWidth() * 0.143;
                $height = $height + $objDrawing->getHeight() * 0.75;
                $offsetY = $offsetY + $objDrawing->getHeight();
                if ($rowDim->getRowHeight() < $height) {
                    $rowDim->setRowHeight($height);
                }
                $colDimSh = $objASheet->getColumnDimension($colLetter);
                $colDimSh->setAutoSize(false);
                if ($colDim->getWidth() < $width) {
                    $colDim->setWidth($width);
                }
            }
        } else {
            $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
            if (substr($data, 0, 1) == '=') {
                $data = '="' . str_replace('"', '""', $data) . '"';
            }
            $objASIndex->setCellValueByColumnAndRow($col, $numberRow + 1, $data);
            if ($datatype[$field] == "date") {
                $objStyle = $objASIndex->getStyle($colLetter . ($numberRow + 1));
                $objNumFrm = $objStyle->getNumberFormat();
                $objNumFrm->setFormatCode($locale_info["LOCALE_SSHORTDATE"] . " hh:mm:ss");
            }
        }
    }
}
Example #2
0
 /**
  * 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;
 }
Example #3
0
 /**
  * Loads PHPExcel from file
  *
  * @param 	string 		$pFilename
  * @throws 	Exception
  */
 public function load($pFilename)
 {
     // Check if file exists
     if (!file_exists($pFilename)) {
         throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
     }
     // Initialisations
     $this->_phpExcel = new PHPExcel();
     $this->_phpExcel->removeSheetByIndex(0);
     // Use ParseXL for the hard work.
     $this->_ole = new PHPExcel_Shared_OLERead();
     $this->_encoderFunction = function_exists('mb_convert_encoding') ? 'mb_convert_encoding' : 'iconv';
     // get excel data
     $res = $this->_ole->read($pFilename);
     // oops, something goes wrong (Darko Miljanovic)
     if ($res === false) {
         // check error code
         if ($this->_ole->error == 1) {
             // bad file
             throw new Exception('The filename ' . $pFilename . ' is not readable');
         } elseif ($this->_ole->error == 2) {
             throw new Exception('The filename ' . $pFilename . ' is not recognised as an Excel file');
         }
         // check other error codes here (eg bad fileformat, etc...)
     }
     $this->_data = $this->_ole->getWorkBook();
     // total byte size of Excel data (workbook global substream + sheet substreams)
     $this->_dataSize = strlen($this->_data);
     $this->_pos = 0;
     // 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->_fonts as &$font) {
             $font['color'] = $this->_readColor($font['colorIndex']);
         }
         foreach ($this->_xf as &$xf) {
             // fonts
             $xf['font']['color'] = $this->_readColor($xf['font']['colorIndex']);
             // fill start and end color
             $xf['fill']['startcolor'] = $this->_readColor($xf['fill']['startcolorIndex']);
             $xf['fill']['endcolor'] = $this->_readColor($xf['fill']['endcolorIndex']);
             // border colors
             $xf['borders']['top']['color'] = $this->_readColor($xf['borders']['top']['colorIndex']);
             $xf['borders']['right']['color'] = $this->_readColor($xf['borders']['right']['colorIndex']);
             $xf['borders']['bottom']['color'] = $this->_readColor($xf['borders']['bottom']['colorIndex']);
             $xf['borders']['left']['color'] = $this->_readColor($xf['borders']['left']['colorIndex']);
         }
         foreach ($this->_builtInStyles as &$builtInStyle) {
             // fonts
             $builtInStyle['font']['color'] = $this->_readColor($builtInStyle['font']['colorIndex']);
             // fill start and end color
             $builtInStyle['fill']['startcolor'] = $this->_readColor($builtInStyle['fill']['startcolorIndex']);
             $builtInStyle['fill']['endcolor'] = $this->_readColor($builtInStyle['fill']['endcolorIndex']);
             // border colors
             $builtInStyle['borders']['top']['color'] = $this->_readColor($builtInStyle['borders']['top']['colorIndex']);
             $builtInStyle['borders']['right']['color'] = $this->_readColor($builtInStyle['borders']['right']['colorIndex']);
             $builtInStyle['borders']['bottom']['color'] = $this->_readColor($builtInStyle['borders']['bottom']['colorIndex']);
             $builtInStyle['borders']['left']['color'] = $this->_readColor($builtInStyle['borders']['left']['colorIndex']);
         }
     }
     // 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']);
         // default style
         if (!$this->_readDataOnly && isset($this->_builtInStyles[0])) {
             $this->_phpSheet->getDefaultStyle()->applyFromArray($this->_builtInStyles[0]);
         }
         $this->_pos = $sheet['offset'];
         // Initialize isFitToPages. May change after reading SHEETPR record.
         $this->_isFitToPages = false;
         // Initialize drawingData
         $this->_drawingData = '';
         // Initialize objs
         $this->_objs = 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_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_RANGEPROTECTION:
                     $this->_readRangeProtection();
                     break;
                     //case self::XLS_Type_IMDATA:				$this->_readImData();					break;
                 //case self::XLS_Type_IMDATA:				$this->_readImData();					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);
             $spContainerCollection = $escherWorksheet->getDgContainer()->getSpgrContainer()->getSpContainerCollection();
         }
         // treat OBJ records
         foreach ($this->_objs as $n => $obj) {
             // skip first shape container which holds the shape group, hence $n + 1
             $spContainer = $spContainerCollection[$n + 1];
             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();
                     $blip = $BSE->getBlip();
                     $ih = imagecreatefromstring($blip->getData());
                     $drawing = new PHPExcel_Worksheet_MemoryDrawing();
                     $drawing->setImageResource($ih);
                     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;
             }
         }
     }
     // 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, true));
                 }
             }
         }
     }
     return $this->_phpExcel;
 }
Example #4
0
 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');
 }
Example #5
0
 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');
 }
Example #6
0
Шадыев Рахматулла');
$sheet->mergeCells('A1:C1');
$sheet->setCellValueByColumnAndRow(1, 2, 'Заявка');
$sheet->setCellValueByColumnAndRow(0, 3, 'Фото');
$sheet->setCellValueByColumnAndRow(1, 3, 'Наименование');
$sheet->setCellValueByColumnAndRow(2, 3, 'Кол-во (шт.)');
$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);
Example #7
0
 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');
 }
Example #8
0
/**
 * 导出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;
}
Example #9
0
 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;
 }
Example #10
0
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;
}
Example #11
0
$ews2->getStyle("g1")->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FFFF00')));
//$ews2->fromArray($mdata, ' ', 'A2');
$ews2->setCellValue('a2', "='Raw_Data'!A2");
$ews2->setCellValue('b2', "='Raw_Data'!B2");
$ews2->setCellValue('c2', "='Raw_Data'!C2");
$ews2->setCellValue('d2', "='Raw_Data'!D2");
//=B2+Alpha*('Raw Data'!B3-B2)
for ($i = 3; $i < $ca + 2; $i++) {
    $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');
Example #12
0
function ExportExcelRecord($arrdata, $datatype, $row, $objPHPExcel, $pageObj)
{
    global $cCharset, $locale_info;
    $col = -1;
    $objASIndex = $objPHPExcel->setActiveSheetIndex(0);
    $objASheet = $objPHPExcel->getActiveSheet();
    $rowDim = $objASIndex->getRowDimension($row + 1);
    foreach ($arrdata as $field => $data) {
        $col++;
        $colLetter = PHPExcel_Cell::stringFromColumnIndex($col);
        $colDim = $objASIndex->getColumnDimension($colLetter);
        if ($datatype[$field] == "binary") {
            if (!$data) {
                continue;
            }
            if (!function_exists("imagecreatefromstring")) {
                $objASIndex->setCellValueByColumnAndRow($col, $row + 1, "LONG BINARY DATA - CANNOT BE DISPLAYED");
                continue;
            }
            $error_handler = set_error_handler("empty_error_handler");
            $gdImage = imagecreatefromstring($data);
            if ($error_handler) {
                set_error_handler($error_handler);
            }
            if ($gdImage) {
                $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
                $objDrawing->setImageResource($gdImage);
                $objDrawing->setCoordinates($colLetter . ($row + 1));
                $objDrawing->setWorksheet($objASheet);
                $width = $objDrawing->getWidth() * 0.143;
                $height = $objDrawing->getHeight() * 0.75;
                if ($rowDim->getRowHeight() < $height) {
                    $rowDim->setRowHeight($height);
                }
                $colDimSh = $objASheet->getColumnDimension($colLetter);
                $colDimSh->setAutoSize(false);
                if ($colDim->getWidth() < $width) {
                    $colDim->setWidth($width);
                }
            }
        } elseif ($datatype[$field] == "file") {
            if (!file_exists($pageObj->pSet->getUploadFolder($field) . $data) || !$data) {
                continue;
            }
            $objDrawing = new PHPExcel_Worksheet_Drawing();
            $objDrawing->setPath($pageObj->pSet->getUploadFolder($field) . $data);
            $objDrawing->setCoordinates($colLetter . ($row + 1));
            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
            $width = $objDrawing->getWidth() * 0.143;
            $height = $objDrawing->getHeight() * 0.75;
            if ($rowDim->getRowHeight() < $height) {
                $rowDim->setRowHeight($height);
            }
            $colDimSh = $objASheet->getColumnDimension($colLetter);
            $colDimSh->setAutoSize(false);
            if ($colDim->getWidth() < $width) {
                $colDim->setWidth($width);
            }
        } else {
            $data = PHPExcel_Shared_String::ConvertEncoding($data, 'UTF-8', $cCharset);
            $objASIndex->setCellValueByColumnAndRow($col, $row + 1, $data);
            if ($datatype[$field] == "date") {
                $objStyle = $objASIndex->getStyle($colLetter . ($row + 1));
                $objNumFrm = $objStyle->getNumberFormat();
                $objNumFrm->setFormatCode($locale_info["LOCALE_SSHORTDATE"] . " hh:mm:ss");
            }
        }
    }
}
 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;
}
Example #15
0
    /**
	 * 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;
include_once 'DBUtil.php';
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include 'PHPExcel/Reader/Excel2007.php';
//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);
Example #17
0
/** 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;
Example #18
0
 public function action_index()
 {
     $action = $this->request->param('id');
     $ids = array_keys(Arr::get($_POST, 'job', array()));
     if (!$ids) {
         throw new HTTP_Exception_404('Not found');
     }
     $regs = DB::select('region_id')->from('user_regions')->where('user_id', '=', User::current('id'))->execute()->as_array('region_id', 'region_id');
     $query = array();
     if ($regs) {
         $query['region'] = array('$in' => array_values($regs));
     }
     if (!Group::current('show_all_jobs')) {
         $query['$or'] = array(array('ex' => intval(User::current('company_id'))), array('companies' => intval(User::current('company_id'))));
     }
     $query['_id'] = array('$in' => $ids);
     $jobs = Database_Mongo::collection('jobs')->find($query);
     $static = array_flip(explode(',', Group::current('columns')));
     $header = array('Ticket ID');
     $types = DB::select('id', 'name')->from('job_types')->execute()->as_array('id', 'name');
     $companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
     if ($action == 'excel') {
         $result = DB::select()->from('attachments')->where('job_id', 'IN', $ids)->and_where('uploaded', '>', 0)->and_where('folder', '<>', 'Signatures')->execute()->as_array();
         $attachments = array();
         $attachments_list = array();
         foreach ($result as $row) {
             $attachments[$row['job_id']] = Arr::get($attachments, $row['job_id'], 0) + 1;
             if (preg_match('/^image\\/.*$/i', $row['mime'])) {
                 $attachments_list[$row['job_id']][] = $row['id'];
             }
         }
     } elseif ($ids && $static['attachments']) {
         $attachments = DB::select('job_id', DB::expr('COUNT(*) as cnt'))->from('attachments')->where('job_id', 'IN', $ids)->and_where('uploaded', '>', 0)->and_where('folder', '<>', 'Signatures')->group_by('job_id')->execute()->as_array('job_id', 'cnt');
     } else {
         $attachments = array();
     }
     if (Group::current('allow_assign')) {
         $result = Database_Mongo::collection('submissions')->aggregate(array(array('$match' => array('job_key' => array('$in' => $ids), 'active' => 1)), array('$group' => array('_id' => '$job_key', 'count' => array('$sum' => 1)))));
         $submissions = array();
         foreach (Arr::get($result, 'result', array()) as $value) {
             $submissions[$value['_id']] = $value['count'];
         }
     }
     if (isset($static['last_update'])) {
         $header[] = 'Last update';
     }
     if (isset($static['last_submit'])) {
         $header[] = 'Last submit';
     }
     if (isset($static['status']) && Group::current('show_all_jobs')) {
         $header[] = 'Job status';
     }
     if (isset($static['types'])) {
         $header[] = 'Assigned works';
     }
     if (isset($static['companies'])) {
         $header[] = 'Assigned companies';
     }
     if (isset($static['ex'])) {
         $header[] = 'Previously assigned companies';
     }
     if (isset($static['pending'])) {
         $header[] = 'Pending submissions';
     }
     if (isset($static['attachments'])) {
         $header[] = 'Attachments';
     }
     foreach (Columns::get_search() as $id => $type) {
         $header[] = Columns::get_name($id);
     }
     if ($action == 'excel') {
         $excel = new PHPExcel();
         $sheet = $excel->getActiveSheet();
         $sheet->setTitle('Search Results');
         $sheet->fromArray($header, NULL, 'A1');
         $i = 1;
         foreach (range('A', $sheet->getHighestDataColumn()) as $col) {
             $sheet->getColumnDimension($col)->setAutoSize(true);
         }
         $x = $attachments_list ? max(array_map('count', $attachments_list)) : 0;
         foreach (range(0, $x - 1) as $col) {
             $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(count($header) + $col))->setWidth(14);
         }
     } else {
         $file = tmpfile();
         fputcsv($file, $header);
     }
     foreach ($jobs as $ticket) {
         $row = array($ticket['_id']);
         if (isset($static['last_update'])) {
             $row[] = date('d-m-Y H:i', Arr::get($ticket, 'last_update', $ticket['created']));
         }
         if (isset($static['last_submit'])) {
             $row[] = Arr::get($ticket, 'last_submit') ? date('d-m-Y H:i', $ticket['last_submit']) : '';
         }
         if (isset($static['status']) && Group::current('show_all_jobs')) {
             $row[] = Arr::get(Enums::$statuses, Arr::get($ticket, 'status', 0), 'Unknown');
         }
         if (isset($static['types'])) {
             if (Group::current('allow_assign')) {
                 $row[] = implode(', ', array_intersect_key($types, Arr::get($ticket, 'assigned', array())));
             } else {
                 $row[] = implode(', ', array_intersect_key($types, array_filter(Arr::get($ticket, 'assigned', array()), function ($x) {
                     return $x == User::current('company_id');
                 })));
             }
         }
         if (isset($static['companies'])) {
             $row[] = implode(', ', array_intersect_key($companies, array_flip(Arr::get($ticket, 'assigned', array()))));
         }
         if (isset($static['ex'])) {
             $row[] = implode(', ', array_intersect_key($companies, array_flip(Arr::get($ticket, 'ex', array()))));
         }
         if (isset($static['pending'])) {
             $row[] = Arr::get($submissions, $ticket['_id']);
         }
         if (isset($static['attachments'])) {
             $row[] = Arr::get($attachments, $ticket['_id']);
         }
         foreach (Columns::get_search() as $id => $type) {
             $row[] = Arr::path($ticket, array('data', $id)) ? Columns::output($ticket['data'][$id], Columns::get_type($id), true) : '';
         }
         if ($action == 'excel') {
             $i++;
             $sheet->fromArray($row, NULL, 'A' . $i);
             $x = count($row);
             if (isset($attachments_list[$ticket['_id']])) {
                 $sheet->getRowDimension($i)->setRowHeight(80);
             }
             foreach (Arr::get($attachments_list, $ticket['_id'], array()) as $image) {
                 if (!file_exists(DOCROOT . 'storage/' . $image . '.thumb')) {
                     if (!file_exists(DOCROOT . 'storage/' . $image)) {
                         continue;
                     }
                     $data = file_get_contents(DOCROOT . 'storage/' . $image);
                     $er = error_reporting(0);
                     $img = imagecreatefromstring($data);
                     error_reporting($er);
                     if (!$img) {
                         continue;
                     }
                     $x = imagesx($img);
                     $y = imagesy($img);
                     $size = max($x, $y);
                     $x = round($x / $size * 96);
                     $y = round($y / $size * 96);
                     $thumb = imagecreatetruecolor($x, $y);
                     imagealphablending($thumb, false);
                     imagesavealpha($thumb, true);
                     imagecopyresampled($thumb, $img, 0, 0, 0, 0, $x, $y, imagesx($img), imagesy($img));
                     imagepng($thumb, DOCROOT . 'storage/' . $image . '.thumb', 9);
                 }
                 $data = file_get_contents(DOCROOT . 'storage/' . $image . '.thumb');
                 $img = imagecreatefromstring($data);
                 $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
                 $objDrawing->setImageResource($img);
                 $coord = PHPExcel_Cell::stringFromColumnIndex($x++);
                 $objDrawing->setCoordinates($coord . $i);
                 $objDrawing->setOffsetY(5);
                 $objDrawing->setResizeProportional(true);
                 $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
                 $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                 $objDrawing->setWorksheet($sheet);
                 //$sheet->getCell($coord . $i)->setHyperlink(new PHPExcel_Cell_Hyperlink(URL::site('download/attachment/' . $image, 'http'), 'Show Image'));
             }
         } else {
             fputcsv($file, $row);
         }
     }
     if ($action == 'excel') {
         $name = tempnam(sys_get_temp_dir(), 'excel');
         header('Content-type: application/xlsx');
         header('Content-disposition: filename="SearchResults.xlsx"');
         $writer = new PHPExcel_Writer_Excel2007($excel);
         $writer->save($name);
         readfile($name);
         unlink($name);
     } else {
         fseek($file, 0);
         header('Content-type: text/csv');
         header('Content-disposition: filename="SearchResults.csv"');
         fpassthru($file);
         fclose($file);
     }
     die;
 }