示例#1
1
 /**
  * @return \PHPExcel_Worksheet
  */
 protected function mockSheet()
 {
     $workbook = new \PHPExcel();
     $workbook->disconnectWorksheets();
     $sheet = new \PHPExcel_Worksheet($workbook);
     $sheet->setTitle('mocked');
     $sheet->fromArray([['a1', 'b1'], ['a2', 'b2']]);
     return $sheet;
 }
 public function createTrueFalseArrayAndProductArray($objPHPExcel)
 {
     $firstSheet = $objPHPExcel->getSheet(0);
     $richtigFalschWerte = new PHPExcel_Worksheet($objPHPExcel);
     $richtigFalschWerte->setTitle('RichtigFalschWerte');
     $objPHPExcel->addSheet($richtigFalschWerte);
     $lastColumnRawData = $firstSheet->getHighestColumn();
     $maxColumn = $lastColumnRawData;
     $maxColumn++;
     $anzahlTeilnehmer = $firstSheet->getCell('C4')->getValue();
     //Erreichbare Punktzahl = $anzahlTeilnehmer + 12
     $endrow = $anzahlTeilnehmer + 6;
     $richtigFalschColumn = 'A';
     for ($column = 'G'; $column != $maxColumn; $column++) {
         $erreichbarePunkte = $firstSheet->getCell($column . ($anzahlTeilnehmer + 12))->getValue();
         for ($row = 7; $row <= $endrow; $row++) {
             if ($firstSheet->getCell($column . $row)->getCalculatedValue() >= 0.5 * (double) $erreichbarePunkte) {
                 $richtigFalschWerte->setCellValue($richtigFalschColumn . ($row - 6), 1);
             } else {
                 $richtigFalschWerte->setCellValue($richtigFalschColumn . ($row - 6), 0);
             }
         }
         $richtigFalschColumn++;
     }
     $lastColumnTrueFalseData = $richtigFalschWerte->getHighestColumn();
     $lastRowTrueFalseData = $richtigFalschWerte->getHighestRow();
     $summaryColumn = $lastColumnTrueFalseData;
     $summaryColumn++;
     for ($row = 1; $row <= $lastRowTrueFalseData; $row++) {
         $richtigFalschWerte->setCellValue($summaryColumn . $row, '=SUM( A' . $row . ':' . $lastColumnTrueFalseData . $row . ')');
     }
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 1), '=AVERAGE(' . $summaryColumn . '1:' . $summaryColumn . $lastRowTrueFalseData . ')');
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 2), '=VARP(' . $summaryColumn . '1:' . $summaryColumn . $lastRowTrueFalseData . ')');
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 3), '=SQRT(' . $summaryColumn . ($lastRowTrueFalseData + 2) . ')');
     $richtigFalschProdukte = new PHPExcel_Worksheet($objPHPExcel);
     $richtigFalschProdukte->setTitle('RichtigFalsch Produkte');
     $objPHPExcel->addSheet($richtigFalschProdukte);
     $aufgabenwerte = $richtigFalschWerte->rangeToArray('A1:' . $lastColumnTrueFalseData . $lastRowTrueFalseData, 0, true, false);
     //$produkteAufgaben->fromArray($aufgabenwerte, NULL, 'A1', true);
     $transponierteAufgabenwerte = PHPExcel_Calculation_LookupRef::TRANSPOSE($aufgabenwerte);
     $endmatrix = PHPExcel_Calculation_MathTrig::MMULT($transponierteAufgabenwerte, $aufgabenwerte);
     $richtigFalschProdukte->fromArray($endmatrix, NULL, 'A1', true);
     $lastColumnMMULTData = $richtigFalschProdukte->getHighestColumn();
     $lastRowMMULTData = $richtigFalschProdukte->getHighestRow();
     $maxColumn = $lastColumnMMULTData;
     $maxColumn++;
     $writeRow = $lastRowMMULTData + 2;
     for ($column = 'A'; $column != $maxColumn; $column++) {
         $cell = $richtigFalschProdukte->getCell($column . $writeRow);
         $cell->setValue('=SUM(' . $column . '1:' . $column . $lastRowMMULTData . ')');
     }
 }
 private function addTransactionPage(PHPExcel_Worksheet $activeSheet)
 {
     $activeSheet->setTitle('Транзакции')->setCellValue('A12', 'Статус')->setCellValue('B12', 'Тип')->setCellValue('C12', 'ID')->setCellValue('D12', 'Дата')->setCellValue('E12', 'IP')->setCellValue('F12', 'ГЕО')->setCellValue('G12', 'URL цели')->setCellValue('H12', 'Источник')->setCellValue('I12', 'Материал')->setCellValue('J12', 'Выплата')->setCellValue('K12', 'Вознаграждение')->setCellValue('L12', 'Зароботок')->setCellValue('M12', 'Цель');
     $row = 13;
     $availableStatuses = ActionsLog::getAvailableStatuses();
     foreach ($this->transactionData['rows'] as $tr) {
         $activeSheet->setCellValue('A' . $row, $availableStatuses[$tr['status']])->setCellValue('B' . $row, $tr['source_type_name'])->setCellValue('C' . $row, $tr['id'])->setCellValue('D' . $row, Yii::app()->dateFormatter->formatDateTime($tr['date']))->setCellValue('E' . $row, $tr['ip'])->setCellValue('F' . $row, $tr['geo'])->setCellValue('G' . $row, $tr['target_url_decoded'])->setCellValue('H' . $row, $tr['source_name'])->setCellValue('I' . $row, $tr['target_name'])->setCellValue('J' . $row, $tr['payment'])->setCellValue('K' . $row, $tr['reward'])->setCellValue('L' . $row, $tr['debit'])->setCellValue('M' . $row, $tr['action_name']);
         $row++;
     }
     $activeSheet->setCellValue('J' . $row, $this->transactionData['total']['payment'])->setCellValue('K' . $row, $this->transactionData['total']['reward'])->setCellValue('L' . $row, $this->transactionData['total']['debit']);
     $activeSheet->getColumnDimension('A')->setWidth(16.3 * 1.05);
     $activeSheet->getColumnDimension('B')->setWidth(16.43 * 1.05);
     $activeSheet->getColumnDimension('C')->setWidth(5 * 1.05);
     $activeSheet->getColumnDimension('D')->setWidth(17.86 * 1.05);
     $activeSheet->getColumnDimension('E')->setWidth(14.14 * 1.05);
     $activeSheet->getColumnDimension('F')->setWidth(34 * 1.05);
     $activeSheet->getColumnDimension('G')->setWidth(31 * 1.05);
     $activeSheet->getColumnDimension('H')->setWidth(30.86 * 1.05);
     $activeSheet->getColumnDimension('I')->setWidth(19.14 * 1.05);
     $activeSheet->getColumnDimension('J')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('K')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('L')->setWidth(8.57 * 1.05);
     $activeSheet->getColumnDimension('M')->setWidth(30.7 * 1.05);
     $activeSheet->getStyle('A12:M' . $row)->getAlignment()->setWrapText(true);
     $this->formatTable($activeSheet, 'A', '12', 'M', $row, array('formatTotal' => true, 'innerRowHeight' => -1, 'headerRowHeight' => 27));
     $this->addLogo($activeSheet);
     $this->setHeader($activeSheet, $this->getHeaders());
     $this->setPageFit($activeSheet, self::FIT_TO_WIDTH, PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
 }
示例#4
0
 /**
  * initial PHPExcel settings; build $this->activeColumns_arr
  */
 protected function initialize()
 {
     $this->sheet = $this->setActiveSheetIndex(0);
     $this->sheet->setTitle("Export");
     // build $this->activeColumns_arr:
     $columns_dict = $this->columns_dict;
     unset($columns_dict['cleared']);
     // <- cannot be toggled - so for now, don't show it at all
     $this->activeColumns_arr = array_keys(array_filter($columns_dict, function ($active) {
         return $active;
     }));
     // convert strftime format to Excel date/time formats:
     $this->dateFormat = str_replace('%', '', $this->kga['conf']['date_format_1']);
     // preferring the configurable value over $this->dateformat (hardcoded)
     $this->dateFormat = str_replace('y', 'yy', $this->dateFormat);
     $this->dateFormat = str_replace('Y', 'yyyy', $this->dateFormat);
     $this->dateFormat = str_replace('d', 'dd', $this->dateFormat);
     $this->dateFormat = str_replace('a', 'ddd', $this->dateFormat);
     $this->dateFormat = str_replace('w', 'dddd', $this->dateFormat);
     $this->dateFormat = str_replace('m', 'mm', $this->dateFormat);
     $this->timeFormat = str_replace('%', '', $this->customTimeformat);
     // $this->custom_timeformat is currently hardcoded - but it's better than nothing
     $this->timeFormat = str_replace('H', 'hh', $this->timeFormat);
     $this->timeFormat = str_replace('M', 'mm', $this->timeFormat);
     $this->timeFormat = str_replace('S', 'ss', $this->timeFormat);
     $this->timeFormat = str_replace('I', 'hh', $this->timeFormat);
     $this->timeFormat = str_replace('p', 'AM/PM', $this->timeFormat);
 }
示例#5
0
/**
 * 
 * @param PHPExcel_Worksheet $hoja
 * @param int $pk representa el índice de la columna en el archivo de excel que está asociada con la llave primaria de la tabla
 * @return string rerpesenta el nombre la ruta del archivo creado. Si no se pudo crear el archivo se regresa otra cosa :p
 */
function prepararArchivo($hoja, $pk = false, $incluirPrimeraFila = false)
{
    $objetoExcel = new PHPExcel();
    $hojaInsertar = $objetoExcel->getSheet(0);
    $hojaInsertar->setTitle('Insertar');
    if ($objetoExcel->getSheetCount() > 1) {
        $hojaActualizar = $objetoExcel->getSheet(1);
        $hojaActualizar->setTitle('Actualizar');
    } else {
        $hojaActualizar = new PHPExcel_Worksheet();
        $hojaActualizar->setTitle('Actualizar');
        $objetoExcel->addSheet($hojaActualizar);
    }
    $rango = $hoja->calculateWorksheetDataDimension();
    if (!$incluirPrimeraFila) {
        $rango[1] = '2';
    }
    $contenidoExcel = $hoja->rangeToArray($rango);
    $datos = array();
    $datos['insertar'] = array();
    $datos['actualizar'] = array();
    if ($pk) {
        $db = new DbConnection();
        $db->abrirConexion();
        $llavePrimaria = $_SESSION['pk'];
        foreach ($contenidoExcel as $fila) {
            $existe = $db->existeRegistro($_SESSION['tabla'], $llavePrimaria, $fila[$pk]);
            if ($existe) {
                $datos['actualizar'][] = $fila;
            } else {
                $datos['insertar'][] = $fila;
            }
        }
        $db->cerrarConexion();
    } else {
        foreach ($contenidoExcel as $fila) {
            $datos['insertar'][] = $fila;
        }
    }
    $hojaInsertar->fromArray($datos['insertar'], null, 'A1', true);
    $hojaActualizar->fromArray($datos['actualizar'], null, 'A1', true);
    $escritorExcel = PHPExcel_IOFactory::createWriter($objetoExcel, 'Excel2007');
    $escritorExcel->save('excelTmp/tmp_import_upload.xlsx');
    return 'excelTmp/tmp_import_upload.xlsx';
}
示例#6
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;
示例#7
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;
 }
示例#8
0
 /**
  * Worksheet title getter/setter
  */
 public function title($title = NULL)
 {
     if ($title === NULL) {
         return $this->_worksheet->getTitle();
     } else {
         $this->_worksheet->setTitle($title);
         return $this;
     }
 }
示例#9
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;
 }
示例#10
0
 /**
  * Set title
  *
  * @param string $pValue String containing the dimension of this worksheet
  * @param string $updateFormulaCellReferences bool Flag indicating whether cell references in formulae should
  * be updated to reflect the new sheet name.
  * This should be left as the default true, unless you are
  * certain that no formula cells on any worksheet contain
  * references to this worksheet
  * @return Worksheet
  */
 public function setTitle($pValue = 'Worksheet', $updateFormulaCellReferences = true)
 {
     $this->sheet->setTitle($pValue, $updateFormulaCellReferences);
     return $this;
 }
示例#11
0
$objPHPExcel->getProperties()->setTitle("Raumbelegungsplan")->setSubject("Ferienschule")->setDescription("Belegungsplan für die einzelnen Räume");
$objPHPExcel = new PHPExcel();
//$objPHPExcel->setActiveSheetIndex(0);
$days = array("Mo", "Di", "Mi", "Do", "Fr");
$slots = array(1, 2, 3);
$times = array("9:00-\n10:30", "10:45-\n12:15", "12:30-\n14:00");
$cells = array('A', 'B', 'C', 'D', 'C', 'D', 'E', 'F', 'G');
$sql_rooms = "SELECT * from rooms";
//$rooms = mysqli_query($conn, $sql_rooms) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error('ferienschule'), E_USER_ERROR);
$rooms = get_sql_result($sql_rooms);
$sheet = 1;
while ($room = mysqli_fetch_assoc($rooms)) {
    //create new worksheet for every room and make it active
    $objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
    $objWorksheet->setTitle('' . $room["room_name"]);
    $objPHPExcel->addSheet($objWorksheet);
    $objPHPExcel->setActiveSheetIndex($sheet);
    $sheet++;
    $rowHeight = 130;
    $columnWidth = 60;
    $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
    $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight($rowHeight);
    $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight($rowHeight);
    $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight($rowHeight);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($columnWidth);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth($columnWidth);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth($columnWidth);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth($columnWidth);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth($columnWidth);
    $rowCount = 1;
 /**
  * @covers cymapgt\core\application\spreadsheet\SpreadsheetProcessor\SpreadsheetProcessor::getSheetCount
  */
 public function testGetSheetCount()
 {
     $this->assertInternalType('int', $this->object->getSheetCount());
     $this->assertEquals(1, $this->object->getSheetCount());
     //add the worksheet
     $wSheetObj = new \PHPExcel_Worksheet();
     $wSheetObj->setTitle('Worksheet2');
     $external = false;
     $index = true;
     $this->object->addSheet($wSheetObj, $external, $index);
     $this->assertEquals(2, $this->object->getSheetCount());
     //add the second worksheet
     $wSheetObj2 = new \PHPExcel_Worksheet();
     $wSheetObj2->setTitle('Worksheet3');
     $external2 = false;
     $index2 = true;
     $this->object->addSheet($wSheetObj2, $external2, $index2);
     $this->assertEquals(3, $this->object->getSheetCount());
     $this->object->removeSheet(2);
     $this->assertEquals(2, $this->object->getSheetCount());
 }
示例#13
0
 public function export_Uncomplete($file_name, $test = false)
 {
     error_reporting(E_ALL);
     ini_set("display_errors", 1);
     ini_set('max_execution_time', 60);
     ini_set('memory_limit', '256M');
     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
     $cacheSettings = array('memoryCacheSize ' => '256MB');
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
     $this->excel = new PHPExcel();
     $registrant_data = $this->getArrayData();
     $worksheet = new PHPExcel_Worksheet();
     $worksheet->setTitle('Data');
     $worksheet->setCellValue('A1', 'Nomor Pendaftaran');
     $worksheet->getColumnDimension('A')->setAutoSize(true);
     $worksheet->setCellValue('B1', 'Nama');
     $worksheet->getColumnDimension('B')->setAutoSize(true);
     $worksheet->setCellValue('C1', 'I/A');
     $worksheet->getColumnDimension('C')->setAutoSize(true);
     $worksheet->setCellValue('D1', 'Asal Sekolah');
     $worksheet->getColumnDimension('D')->setAutoSize(true);
     $worksheet->setCellValue('E1', 'Contact');
     $worksheet->getColumnDimension('E')->setAutoSize(true);
     $worksheet->setCellValue('F1', 'Status Kekurangan');
     $worksheet->getColumnDimension('F')->setAutoSize(true);
     $row_iterate = 2;
     foreach ($registrant_data as $registrant) {
         if (!$registrant['completed']) {
             $row = [];
             $row[] = $registrant['id'];
             $row[] = strtoupper($registrant['name']);
             $row[] = $registrant['gender'] == 'L' ? 'Ikhwan' : 'Akhwat';
             $row[] = strtoupper($registrant['previousSchool']);
             $row[] = $registrant['cp'];
             $row[] = $registrant['status'];
             $worksheet->fromArray($row, '', 'A' . $row_iterate);
             $row_iterate++;
         }
     }
     $this->excel->removeSheetByIndex(0);
     $this->excel->addSheet($worksheet);
     if ($test) {
         return true;
     } else {
         header('Content-Type: application/vnd.ms-excel');
         header('Content-Disposition: attachment;filename="' . $file_name . '.xls"');
         header('Cache-Control: max-age=0');
         $objWriter = new PHPExcel_Writer_Excel5($this->excel);
         $objWriter->save('php://output');
         exit;
     }
 }