Example #1
7
 /**
  * Save PHPExcel to file
  *
  * @param 	string 		$pFileName
  * @throws 	Exception
  */
 public function save($pFilename = null)
 {
     if (!is_null($this->_spreadSheet)) {
         // Garbage collect...
         foreach ($this->_spreadSheet->getAllSheets() as $sheet) {
             $sheet->garbageCollect();
         }
         // Create new ZIP file and open it for writing
         $objZip = new ZipArchive();
         // Try opening the ZIP file
         if ($objZip->open($pFilename, ZIPARCHIVE::OVERWRITE) !== true) {
             if ($objZip->open($pFilename, ZIPARCHIVE::CREATE) !== true) {
                 throw new Exception("Could not open " . $pFilename . " for writing.");
             }
         }
         // Add media
         $sheetCount = $this->_spreadSheet->getSheetCount();
         for ($i = 0; $i < $sheetCount; ++$i) {
             for ($j = 0; $j < $this->_spreadSheet->getSheet($i)->getDrawingCollection()->count(); ++$j) {
                 if ($this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j) instanceof PHPExcel_Worksheet_BaseDrawing) {
                     $imgTemp = $this->_spreadSheet->getSheet($i)->getDrawingCollection()->offsetGet($j);
                     $objZip->addFromString('media/' . $imgTemp->getFilename(), file_get_contents($imgTemp->getPath()));
                 }
             }
         }
         // Add phpexcel.xml to the document, which represents a PHP serialized PHPExcel object
         $objZip->addFromString('phpexcel.xml', $this->_writeSerialized($this->_spreadSheet, $pFilename));
         // Close file
         if ($objZip->close() === false) {
             throw new Exception("Could not close zip file {$pFilename}.");
         }
     } else {
         throw new Exception("PHPExcel object unassigned.");
     }
 }
Example #2
1
	/**
	 * Save PHPExcel to file
	 *
	 * @param 	string 		$pFileName
	 * @throws 	Exception
	 */
	public function save($pFilename = null) {
		// Open file
		global $cnf;
		$pFilename= $cnf['path']['Temp'] . $pFilename;

		$fileHandle = fopen($pFilename, 'w');
		if ($fileHandle === false) {
			throw new Exception("Could not open file $pFilename for writing.");
		}

		// Fetch sheets
		$sheets = array();
		if (is_null($this->_sheetIndex)) {
			$sheets = $this->_phpExcel->getAllSheets();
		} else {
			$sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
		}

    	// PDF paper size
    	$paperSize = 'A4';

		// Create PDF
		$pdf = new FPDF('P', 'pt', $paperSize);

		// Loop all sheets
		foreach ($sheets as $sheet) {
	    	// PDF orientation
	    	$orientation = 'P';
	    	if ($sheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) {
		    	$orientation = 'L';
	    	}

			// Start sheet
			$pdf->SetAutoPageBreak(true);
			$pdf->SetFont('Arial', '', 10);
			$pdf->AddPage($orientation);

	    	// Get worksheet dimension
	    	$dimension = explode(':', $sheet->calculateWorksheetDimension());
	    	$dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
	    	$dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
	    	$dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
	    	$dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;

	    	// Calculate column widths
	    	$sheet->calculateColumnWidths();

	    	// Loop trough cells
	    	for ($row = $dimension[0][1]; $row <= $dimension[1][1]; $row++) {
	    		// Line height
	    		$lineHeight = 0;

	    		// Calulate line height
	    		for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
	    		    $rowDimension = $sheet->getRowDimension($row);
	    			$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
	    				PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight())
	    			);
	    			if ($cellHeight <= 0) {
		    			$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
		    				PHPExcel_Shared_Drawing::cellDimensionToPixels($sheet->getDefaultRowDimension()->getRowHeight())
		    			);
	    			}
	    			if ($cellHeight <= 0) {
	    				$cellHeight = $sheet->getStyleByColumnAndRow($column, $row)->getFont()->getSize();
	    			}
	    			if ($cellHeight > $lineHeight) {
	    				$lineHeight = $cellHeight;
	    			}
	    		}

	    		// Output values
	    		for ($column = $dimension[0][0]; $column <= $dimension[1][0]; $column++) {
	    			// Start with defaults...
	    			$pdf->SetFont('Arial', '', 10);
	    			$pdf->SetTextColor(0, 0, 0);
	    			$pdf->SetDrawColor(100, 100, 100);
	    			$pdf->SetFillColor(255, 255, 255);

			    	// Coordinates
			    	$startX = $pdf->GetX();
			    	$startY = $pdf->GetY();

	    			// Cell exists?
	    			$cellData = '';
	    			if ($sheet->cellExistsByColumnAndRow($column, $row)) {
	    				if ($sheet->getCellByColumnAndRow($column, $row)->getValue() instanceof PHPExcel_RichText) {
	    					$cellData = $sheet->getCellByColumnAndRow($column, $row)->getValue()->getPlainText();
	    				} else {
		    				if ($this->_preCalculateFormulas) {
		    					$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
		    						$sheet->getCellByColumnAndRow($column, $row)->getCalculatedValue(),
		    						$sheet->getstyle( $sheet->getCellByColumnAndRow($column, $row)->getCoordinate() )->getNumberFormat()->getFormatCode()
		    					);
		    				} else {
		    					$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
		    						$sheet->getCellByColumnAndRow($column, $row)->getValue(),
		    						$sheet->getstyle( $sheet->getCellByColumnAndRow($column, $row)->getCoordinate() )->getNumberFormat()->getFormatCode()
		    					);
		    				}
	    				}
	    			}

	    			// Style information
	    			$style = $sheet->getStyleByColumnAndRow($column, $row);

	    			// Cell width
	    			$columnDimension = $sheet->getColumnDimensionByColumn($column);
	    			if ($columnDimension->getWidth() == -1) {
	    				$columnDimension->setAutoSize(true);
	    				$sheet->calculateColumnWidths(false);
	    			}
	    			$cellWidth = PHPExcel_Shared_Drawing::pixelsToPoints(
	    				PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth())
	    			);

	    			// Cell height
	    			$rowDimension = $sheet->getRowDimension($row);
	    			$cellHeight = PHPExcel_Shared_Drawing::pixelsToPoints(
	    				PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight())
	    			);
	    			if ($cellHeight <= 0) {
	    				$cellHeight = $style->getFont()->getSize();
	    			}

	    			// Column span? Rowspan?
	    			$singleCellWidth = $cellWidth;
	    			$singleCellHeight = $cellHeight;
					foreach ($sheet->getMergeCells() as $cells) {
						if ($sheet->getCellByColumnAndRow($column, $row)->isInRange($cells)) {
							list($first, ) = PHPExcel_Cell::splitRange($cells);

							if ($first == $sheet->getCellByColumnAndRow($column, $row)->getCoordinate()) {
								list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells);

								$cellWidth = $cellWidth * $colSpan;
								$cellHeight = $cellHeight * $rowSpan;
							}

							break;
						}
					}

					// Cell height OK?
					if ($cellHeight < $lineHeight) {
						$cellHeight = $lineHeight;
						$singleCellHeight = $cellHeight;
					}

	    			// Font formatting
	    			$fontStyle = '';
	    			if ($style->getFont()->getBold()) {
	    				$fontStyle .= 'B';
					}
					if ($style->getFont()->getItalic()) {
						$fontStyle .= 'I';
					}
					if ($style->getFont()->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE) {
						$fontStyle .= 'U';
					}
					$pdf->SetFont('Arial', $fontStyle, $style->getFont()->getSize());

					// Text alignment
					$alignment = 'L';
					switch ($style->getAlignment()->getHorizontal()) {
						case PHPExcel_Style_Alignment::HORIZONTAL_CENTER:
							$alignment = 'C'; break;
						case PHPExcel_Style_Alignment::HORIZONTAL_RIGHT:
							$alignment = 'R'; break;
						case PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY:
							$alignment = 'J'; break;
						case PHPExcel_Style_Alignment::HORIZONTAL_LEFT:
						case PHPExcel_Style_Alignment::HORIZONTAL_GENERAL:
						default:
							$alignment = 'L'; break;
					}

					// Text color
					$pdf->SetTextColor(
						hexdec(substr($style->getFont()->getColor()->getRGB(), 0, 2)),
						hexdec(substr($style->getFont()->getColor()->getRGB(), 2, 2)),
						hexdec(substr($style->getFont()->getColor()->getRGB(), 4, 2))
					);

					// Fill color
					if ($style->getFill()->getFillType() != PHPExcel_Style_Fill::FILL_NONE) {
						$pdf->SetFillColor(
							hexdec(substr($style->getFill()->getStartColor()->getRGB(), 0, 2)),
							hexdec(substr($style->getFill()->getStartColor()->getRGB(), 2, 2)),
							hexdec(substr($style->getFill()->getStartColor()->getRGB(), 4, 2))
						);
					}

					// Border color
					$borders = '';
	    			if ($style->getBorders()->getLeft()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
						$borders .= 'L';
						$pdf->SetDrawColor(
							hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 0, 2)),
							hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 2, 2)),
							hexdec(substr($style->getBorders()->getLeft()->getColor()->getRGB(), 4, 2))
						);
					}
	    			if ($style->getBorders()->getRight()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
						$borders .= 'R';
						$pdf->SetDrawColor(
							hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 0, 2)),
							hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 2, 2)),
							hexdec(substr($style->getBorders()->getRight()->getColor()->getRGB(), 4, 2))
						);
					}
	    			if ($style->getBorders()->getTop()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
						$borders .= 'T';
						$pdf->SetDrawColor(
							hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 0, 2)),
							hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 2, 2)),
							hexdec(substr($style->getBorders()->getTop()->getColor()->getRGB(), 4, 2))
						);
					}
	    			if ($style->getBorders()->getBottom()->getBorderStyle() != PHPExcel_Style_Border::BORDER_NONE) {
						$borders .= 'B';
						$pdf->SetDrawColor(
							hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 0, 2)),
							hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 2, 2)),
							hexdec(substr($style->getBorders()->getBottom()->getColor()->getRGB(), 4, 2))
						);
					}
					if ($borders == '') {
						$borders = 0;
					}
					if ($sheet->getShowGridlines()) {
						$borders = 'LTRB';
					}

	    			// Image?
			    	$iterator = $sheet->getDrawingCollection()->getIterator();
			    	while ($iterator->valid()) {
			    		if ($iterator->current()->getCoordinates() == PHPExcel_Cell::stringFromColumnIndex($column) . ($row + 1)) {
			    			try {
				    			$pdf->Image(
				    				$iterator->current()->getPath(),
				    				$pdf->GetX(),
				    				$pdf->GetY(),
				    				$iterator->current()->getWidth(),
				    				$iterator->current()->getHeight(),
				    				'',
				    				$this->_tempDir
				    			);
			    			} catch (Exception $ex) { }
			    		}

			    		$iterator->next();
			    	}

	    			// Print cell
	    			$pdf->MultiCell(
	    				$cellWidth,
	    				$cellHeight,
	    				$cellData,
	    				$borders,
	    				$alignment,
	    				($style->getFill()->getFillType() == PHPExcel_Style_Fill::FILL_NONE ? 0 : 1)
	    			);

			    	// Coordinates
			    	$endX = $pdf->GetX();
			    	$endY = $pdf->GetY();

			    	// Revert to original Y location
			    	if ($endY > $startY) {
			    		$pdf->SetY($startY);
			    		if ($lineHeight < $lineHeight + ($endY - $startY)) {
			    			$lineHeight = $lineHeight + ($endY - $startY);
			    		}
			    	}
			    	$pdf->SetX($startX + $singleCellWidth);

			    	// Hyperlink?
			    	if ($sheet->getCellByColumnAndRow($column, $row)->hasHyperlink()) {
			    		if (!$sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->isInternal()) {
			    			$pdf->Link(
			    				$startX,
			    				$startY,
			    				$endX - $startX,
			    				$endY - $startY,
			    				$sheet->getCellByColumnAndRow($column, $row)->getHyperlink()->getUrl()
			    			);
			    		}
			    	}
				}

				// Garbage collect!
				$sheet->garbageCollect();

				// Next line...
				$pdf->Ln($lineHeight);
	    	}
		}

		// Document info
		$pdf->SetTitle($this->_phpExcel->getProperties()->getTitle());
		$pdf->SetAuthor($this->_phpExcel->getProperties()->getCreator());
		$pdf->SetSubject($this->_phpExcel->getProperties()->getSubject());
		$pdf->SetKeywords($this->_phpExcel->getProperties()->getKeywords());
		$pdf->SetCreator($this->_phpExcel->getProperties()->getCreator());

		// Write to file
		fwrite($fileHandle, $pdf->output($pFilename, 'S'));

		// Close file
		fclose($fileHandle);
	}
 /**
  * Build the Escher object corresponding to the MSODRAWINGGROUP record
  */
 private function _buildWorkbookEscher()
 {
     $escher = null;
     // any drawings in this workbook?
     $found = false;
     foreach ($this->_phpExcel->getAllSheets() as $sheet) {
         if (count($sheet->getDrawingCollection()) > 0) {
             $found = true;
             break;
         }
     }
     // nothing to do if there are no drawings
     if (!$found) {
         return;
     }
     // if we reach here, then there are drawings in the workbook
     $escher = new PHPExcel_Shared_Escher();
     // dggContainer
     $dggContainer = new PHPExcel_Shared_Escher_DggContainer();
     $escher->setDggContainer($dggContainer);
     // set IDCLs (identifier clusters)
     $dggContainer->setIDCLs($this->_IDCLs);
     // this loop is for determining maximum shape identifier of all drawing
     $spIdMax = 0;
     $totalCountShapes = 0;
     $countDrawings = 0;
     foreach ($this->_phpExcel->getAllsheets() as $sheet) {
         $sheetCountShapes = 0;
         // count number of shapes (minus group shape), in sheet
         if (count($sheet->getDrawingCollection()) > 0) {
             ++$countDrawings;
             foreach ($sheet->getDrawingCollection() as $drawing) {
                 ++$sheetCountShapes;
                 ++$totalCountShapes;
                 $spId = $sheetCountShapes | $this->_phpExcel->getIndex($sheet) + 1 << 10;
                 $spIdMax = max($spId, $spIdMax);
             }
         }
     }
     $dggContainer->setSpIdMax($spIdMax + 1);
     $dggContainer->setCDgSaved($countDrawings);
     $dggContainer->setCSpSaved($totalCountShapes + $countDrawings);
     // total number of shapes incl. one group shapes per drawing
     // bstoreContainer
     $bstoreContainer = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer();
     $dggContainer->setBstoreContainer($bstoreContainer);
     // the BSE's (all the images)
     foreach ($this->_phpExcel->getAllsheets() as $sheet) {
         foreach ($sheet->getDrawingCollection() as $drawing) {
             if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
                 $filename = $drawing->getPath();
                 list($imagesx, $imagesy, $imageFormat) = getimagesize($filename);
                 switch ($imageFormat) {
                     case 1:
                         // GIF, not supported by BIFF8, we convert to PNG
                         $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                         ob_start();
                         imagepng(imagecreatefromgif($filename));
                         $blipData = ob_get_contents();
                         ob_end_clean();
                         break;
                     case 2:
                         // JPEG
                         $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG;
                         $blipData = file_get_contents($filename);
                         break;
                     case 3:
                         // PNG
                         $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                         $blipData = file_get_contents($filename);
                         break;
                     case 6:
                         // Windows DIB (BMP), we convert to PNG
                         $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                         ob_start();
                         imagepng(PHPExcel_Shared_Drawing::imagecreatefrombmp($filename));
                         $blipData = ob_get_contents();
                         ob_end_clean();
                         break;
                     default:
                         continue 2;
                 }
                 $blip = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE_Blip();
                 $blip->setData($blipData);
                 $BSE = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE();
                 $BSE->setBlipType($blipType);
                 $BSE->setBlip($blip);
                 $bstoreContainer->addBSE($BSE);
             } else {
                 if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
                     switch ($drawing->getRenderingFunction()) {
                         case PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG:
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG;
                             $renderingFunction = 'imagejpeg';
                             break;
                         case PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF:
                         case PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG:
                         case PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT:
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                             $renderingFunction = 'imagepng';
                             break;
                     }
                     ob_start();
                     call_user_func($renderingFunction, $drawing->getImageResource());
                     $blipData = ob_get_contents();
                     ob_end_clean();
                     $blip = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE_Blip();
                     $blip->setData($blipData);
                     $BSE = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE();
                     $BSE->setBlipType($blipType);
                     $BSE->setBlip($blip);
                     $bstoreContainer->addBSE($BSE);
                 }
             }
         }
     }
     // Set the Escher object
     $this->_writerWorkbook->setEscher($escher);
 }
Example #4
0
 /**
  * Close the Moodle Workbook
  */
 public function close()
 {
     global $CFG;
     foreach ($this->objPHPExcel->getAllSheets() as $sheet) {
         $sheet->setSelectedCells('A1');
     }
     $this->objPHPExcel->setActiveSheetIndex(0);
     $filename = preg_replace('/\\.xlsx?$/i', '', $this->filename);
     $mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
     $filename = $filename . '.xlsx';
     if (is_https()) {
         // HTTPS sites - watch out for IE! KB812935 and KB316431.
         header('Cache-Control: max-age=10');
         header('Expires: ' . gmdate('D, d M Y H:i:s', 0) . ' GMT');
         header('Pragma: ');
     } else {
         //normal http - prevent caching at all cost
         header('Cache-Control: private, must-revalidate, pre-check=0, post-check=0, max-age=0');
         header('Expires: ' . gmdate('D, d M Y H:i:s', 0) . ' GMT');
         header('Pragma: no-cache');
     }
     if (core_useragent::is_ie()) {
         $filename = rawurlencode($filename);
     } else {
         $filename = s($filename);
     }
     header('Content-Type: ' . $mimetype);
     header('Content-Disposition: attachment;filename="' . $filename . '"');
     $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, $this->type);
     $objWriter->save('php://output');
 }
 /**
  * @param integer|callable|string $sheetID
  * @param null $callback
  * @return $this
  * @throws \PHPExcel_Exception
  */
 public function sheet($sheetID, $callback = null)
 {
     // Default
     $isCallable = false;
     // Init a new PHPExcel instance without any worksheets
     if (!$this->excel instanceof PHPExcel) {
         $this->original = $this->excel;
         $this->initClonedExcelObject($this->excel);
         // Clone all connected sheets
         foreach ($this->original->getAllSheets() as $sheet) {
             $this->excel->createSheet()->cloneParent($sheet);
         }
     }
     // Copy the callback when needed
     if (is_callable($sheetID)) {
         $callback = $sheetID;
         $isCallable = true;
     } elseif (is_callable($callback)) {
         $isCallable = true;
     }
     // Clone the loaded excel instance
     $this->sheet = $this->getSheetByIdOrName($sheetID);
     // Do the callback
     if ($isCallable) {
         call_user_func($callback, $this->sheet);
     }
     // Return the sheet
     return $this->sheet;
 }
Example #6
0
 /**
  * Save PHPExcel to file
  *
  * @param	string		$pFileName
  * @throws	Exception
  */
 public function save($pFilename = null)
 {
     // check mbstring.func_overload
     if (ini_get('mbstring.func_overload') != 0) {
         throw new Exception('Multibyte string function overloading in PHP must be disabled.');
     }
     // garbage collect
     $this->_phpExcel->garbageCollect();
     $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
     // initialize colors array
     $this->_colors = array();
     // Initialise workbook writer
     $this->_writerWorkbook = new PHPExcel_Writer_Excel5_Workbook($this->_phpExcel, $this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser);
     // Initialise worksheet writers
     $countSheets = count($this->_phpExcel->getAllSheets());
     for ($i = 0; $i < $countSheets; ++$i) {
         $phpSheet = $this->_phpExcel->getSheet($i);
         $writerWorksheet = new PHPExcel_Writer_Excel5_Worksheet($this->_BIFF_version, $this->_str_total, $this->_str_unique, $this->_str_table, $this->_colors, $this->_parser, $this->_preCalculateFormulas, $phpSheet);
         $this->_writerWorksheets[$i] = $writerWorksheet;
     }
     // add 15 identical cell style Xfs
     // for now, we use the first cellXf instead of cellStyleXf
     $cellXfCollection = $this->_phpExcel->getCellXfCollection();
     for ($i = 0; $i < 15; ++$i) {
         $this->_writerWorkbook->addXfWriter($cellXfCollection[0], true);
     }
     // add all the cell Xfs
     foreach ($this->_phpExcel->getCellXfCollection() as $style) {
         $this->_writerWorkbook->addXfWriter($style, false);
     }
     // initialize OLE file
     $workbookStreamName = $this->_BIFF_version == 0x600 ? 'Workbook' : 'Book';
     $OLE = new PHPExcel_Shared_OLE_PPS_File(PHPExcel_Shared_OLE::Asc2Ucs($workbookStreamName));
     $OLE->init();
     // Write the worksheet streams before the global workbook stream,
     // because the byte sizes of these are needed in the global workbook stream
     $worksheetSizes = array();
     for ($i = 0; $i < $countSheets; ++$i) {
         $this->_writerWorksheets[$i]->close();
         $worksheetSizes[] = $this->_writerWorksheets[$i]->_datasize;
     }
     // add binary data for global workbook stream
     $OLE->append($this->_writerWorkbook->writeWorkbook($worksheetSizes));
     // add binary data for sheet streams
     for ($i = 0; $i < $countSheets; ++$i) {
         $OLE->append($this->_writerWorksheets[$i]->getData());
     }
     $root = new PHPExcel_Shared_OLE_PPS_Root(time(), time(), array($OLE));
     // save the OLE file
     $res = $root->save($pFilename);
     PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);
 }
Example #7
0
 /**
  * Build CSS styles
  *
  * @param	boolean	$generateSurroundingHTML	Generate surrounding HTML style? (html { })
  * @return	array
  * @throws	PHPExcel_Writer_Exception
  */
 public function buildCSS($generateSurroundingHTML = true)
 {
     // PHPExcel object known?
     if (is_null($this->_phpExcel)) {
         throw new PHPExcel_Writer_Exception('Internal PHPExcel object not set to an instance of an object.');
     }
     // Cached?
     if (!is_null($this->_cssStyles)) {
         return $this->_cssStyles;
     }
     // Ensure that spans have been calculated
     if (!$this->_spansAreCalculated) {
         $this->_calculateSpans();
     }
     // Construct CSS
     $css = array();
     // Start styles
     if ($generateSurroundingHTML) {
         // html { }
         $css['html']['font-family'] = 'Calibri, Arial, Helvetica, sans-serif';
         $css['html']['font-size'] = '11pt';
         $css['html']['background-color'] = 'white';
     }
     // table { }
     $css['table']['border-collapse'] = 'collapse';
     if (!$this->_isPdf) {
         $css['table']['page-break-after'] = 'always';
     }
     // .gridlines td { }
     $css['.gridlines td']['border'] = '1px dotted black';
     // .b {}
     $css['.b']['text-align'] = 'center';
     // BOOL
     // .e {}
     $css['.e']['text-align'] = 'center';
     // ERROR
     // .f {}
     $css['.f']['text-align'] = 'right';
     // FORMULA
     // .inlineStr {}
     $css['.inlineStr']['text-align'] = 'left';
     // INLINE
     // .n {}
     $css['.n']['text-align'] = 'right';
     // NUMERIC
     // .s {}
     $css['.s']['text-align'] = 'left';
     // STRING
     // Calculate cell style hashes
     foreach ($this->_phpExcel->getCellXfCollection() as $index => $style) {
         $css['td.style' . $index] = $this->_createCSSStyle($style);
     }
     // Fetch sheets
     $sheets = array();
     if (is_null($this->_sheetIndex)) {
         $sheets = $this->_phpExcel->getAllSheets();
     } else {
         $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
     }
     // Build styles per sheet
     foreach ($sheets as $sheet) {
         // Calculate hash code
         $sheetIndex = $sheet->getParent()->getIndex($sheet);
         // Build styles
         // Calculate column widths
         $sheet->calculateColumnWidths();
         // col elements, initialize
         $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()) - 1;
         $column = -1;
         while ($column++ < $highestColumnIndex) {
             $this->_columnWidths[$sheetIndex][$column] = 42;
             // approximation
             $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = '42pt';
         }
         // col elements, loop through columnDimensions and set width
         foreach ($sheet->getColumnDimensions() as $columnDimension) {
             if (($width = PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth(), $this->_defaultFont)) >= 0) {
                 $width = PHPExcel_Shared_Drawing::pixelsToPoints($width);
                 $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
                 $this->_columnWidths[$sheetIndex][$column] = $width;
                 $css['table.sheet' . $sheetIndex . ' col.col' . $column]['width'] = $width . 'pt';
                 if ($columnDimension->getVisible() === false) {
                     $css['table.sheet' . $sheetIndex . ' col.col' . $column]['visibility'] = 'collapse';
                     $css['table.sheet' . $sheetIndex . ' col.col' . $column]['*display'] = 'none';
                     // target IE6+7
                 }
             }
         }
         // Default row height
         $rowDimension = $sheet->getDefaultRowDimension();
         // table.sheetN tr { }
         $css['table.sheet' . $sheetIndex . ' tr'] = array();
         if ($rowDimension->getRowHeight() == -1) {
             $pt_height = PHPExcel_Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont());
         } else {
             $pt_height = $rowDimension->getRowHeight();
         }
         $css['table.sheet' . $sheetIndex . ' tr']['height'] = $pt_height . 'pt';
         if ($rowDimension->getVisible() === false) {
             $css['table.sheet' . $sheetIndex . ' tr']['display'] = 'none';
             $css['table.sheet' . $sheetIndex . ' tr']['visibility'] = 'hidden';
         }
         // Calculate row heights
         foreach ($sheet->getRowDimensions() as $rowDimension) {
             $row = $rowDimension->getRowIndex() - 1;
             // table.sheetN tr.rowYYYYYY { }
             $css['table.sheet' . $sheetIndex . ' tr.row' . $row] = array();
             if ($rowDimension->getRowHeight() == -1) {
                 $pt_height = PHPExcel_Shared_Font::getDefaultRowHeightByFont($this->_phpExcel->getDefaultStyle()->getFont());
             } else {
                 $pt_height = $rowDimension->getRowHeight();
             }
             $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['height'] = $pt_height . 'pt';
             if ($rowDimension->getVisible() === false) {
                 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['display'] = 'none';
                 $css['table.sheet' . $sheetIndex . ' tr.row' . $row]['visibility'] = 'hidden';
             }
         }
     }
     // Cache
     if (is_null($this->_cssStyles)) {
         $this->_cssStyles = $css;
     }
     // Return
     return $css;
 }
Example #8
0
 /**
  * Calculate offsets for Worksheet BOF records.
  *
  * @access private
  */
 private function calcSheetOffsets()
 {
     $boundsheet_length = 10;
     // fixed length for a BOUNDSHEET record
     // size of Workbook globals part 1 + 3
     $offset = $this->_datasize;
     // add size of Workbook globals part 2, the length of the SHEET records
     $total_worksheets = count($this->phpExcel->getAllSheets());
     foreach ($this->phpExcel->getWorksheetIterator() as $sheet) {
         $offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
     }
     // add the sizes of each of the Sheet substreams, respectively
     for ($i = 0; $i < $total_worksheets; ++$i) {
         $this->worksheetOffsets[$i] = $offset;
         $offset += $this->worksheetSizes[$i];
     }
     $this->biffSize = $offset;
 }
Example #9
0
 function getAllMergedCells(PHPExcel $objPHPExcel)
 {
     $i = 0;
     foreach ($objPHPExcel->getAllSheets() as $sheet) {
         $this->arr_merged_allCells[$i] = $sheet->getMergeCells();
         $i++;
     }
 }
Example #10
0
    /**
     * Autosize column for document
     *
     * @return int
     */
    public static function autosizeColumn(\PHPExcel $objPHPExcel)
    {
        foreach ($objPHPExcel->getAllSheets() as $sheet) {
            $toCol = $sheet->getHighestColumn();

            $toCol++;
            for ($i = 'A'; $i !== $toCol; $i++) {
                $sheet->getColumnDimension($i)->setAutoSize(true);
            }

            $sheet->calculateColumnWidths();
        }
    }
Example #11
0
 /**
  * Writes the MSODRAWINGGROUP record if needed. Possibly split using CONTINUE records.
  */
 private function _writeMsoDrawingGroup()
 {
     // any drawings in this workbook?
     $found = false;
     foreach ($this->_phpExcel->getAllSheets() as $sheet) {
         if (count($sheet->getDrawingCollection()) > 0) {
             $found = true;
         }
     }
     // if there are drawings, then we need to write MSODRAWINGGROUP record
     if ($found) {
         // create intermediate Escher object
         $escher = new PHPExcel_Shared_Escher();
         // dggContainer
         $dggContainer = new PHPExcel_Shared_Escher_DggContainer();
         $escher->setDggContainer($dggContainer);
         // this loop is for determining maximum shape identifier of all drawing
         $spIdMax = 0;
         $totalCountShapes = 0;
         $countDrawings = 0;
         foreach ($this->_phpExcel->getAllsheets() as $sheet) {
             $sheetCountShapes = 0;
             // count number of shapes (minus group shape), in sheet
             if (count($sheet->getDrawingCollection()) > 0) {
                 ++$countDrawings;
                 foreach ($sheet->getDrawingCollection() as $drawing) {
                     ++$sheetCountShapes;
                     ++$totalCountShapes;
                     $spId = $sheetCountShapes | $this->_phpExcel->getIndex($sheet) + 1 << 10;
                     $spIdMax = max($spId, $spIdMax);
                 }
             }
         }
         $dggContainer->setSpIdMax($spIdMax + 1);
         $dggContainer->setCDgSaved($countDrawings);
         $dggContainer->setCSpSaved($totalCountShapes + $countDrawings);
         // total number of shapes incl. one group shapes per drawing
         // bstoreContainer
         $bstoreContainer = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer();
         $dggContainer->setBstoreContainer($bstoreContainer);
         // the BSE's (all the images)
         foreach ($this->_phpExcel->getAllsheets() as $sheet) {
             foreach ($sheet->getDrawingCollection() as $drawing) {
                 if ($drawing instanceof PHPExcel_Worksheet_Drawing) {
                     $filename = $drawing->getPath();
                     list($imagesx, $imagesy, $imageFormat) = getimagesize($filename);
                     switch ($imageFormat) {
                         case 1:
                             // GIF, not supported by BIFF8, we convert to PNG
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                             $imageResource = imagecreatefromgif($filename);
                             ob_start();
                             imagepng($imageResource);
                             $blipData = ob_get_contents();
                             ob_end_clean();
                             break;
                         case 2:
                             // JPEG
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG;
                             $blipData = file_get_contents($filename);
                             break;
                         case 3:
                             // PNG
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                             $blipData = file_get_contents($filename);
                             break;
                         case 6:
                             // Windows DIB (BMP), we convert to PNG
                             $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                             $imageResource = PHPExcel_Shared_Drawing::imagecreatefrombmp($filename);
                             ob_start();
                             imagepng($imageResource);
                             $blipData = ob_get_contents();
                             ob_end_clean();
                             break;
                         default:
                             continue 2;
                     }
                     $blip = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE_Blip();
                     $blip->setData($blipData);
                     $BSE = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE();
                     $BSE->setBlipType($blipType);
                     $BSE->setBlip($blip);
                     $bstoreContainer->addBSE($BSE);
                 } else {
                     if ($drawing instanceof PHPExcel_Worksheet_MemoryDrawing) {
                         switch ($drawing->getRenderingFunction()) {
                             case PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG:
                                 $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG;
                                 $renderingFunction = 'imagejpeg';
                                 break;
                             case PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF:
                             case PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG:
                             case PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT:
                                 $blipType = PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG;
                                 $renderingFunction = 'imagepng';
                                 break;
                         }
                         ob_start();
                         call_user_func($renderingFunction, $drawing->getImageResource());
                         $blipData = ob_get_contents();
                         ob_end_clean();
                         $blip = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE_Blip();
                         $blip->setData($blipData);
                         $BSE = new PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE();
                         $BSE->setBlipType($blipType);
                         $BSE->setBlip($blip);
                         $bstoreContainer->addBSE($BSE);
                     }
                 }
             }
         }
         // write the Escher stream from the intermediate Escher object
         $writer = new PHPExcel_Writer_Excel5_Escher($escher);
         $data = $writer->close();
         $record = 0xeb;
         $length = strlen($data);
         $header = pack("vv", $record, $length);
         return $this->writeData($header . $data);
     }
 }
Example #12
0
 /**
  * Build CSS styles
  *
  * @param	boolean	$generateSurroundingHTML	Generate surrounding HTML style? (html { })
  * @return	array
  * @throws	Exception
  */
 public function buildCSS($generateSurroundingHTML = true)
 {
     // PHPExcel object known?
     if (is_null($this->_phpExcel)) {
         throw new Exception('Internal PHPExcel object not set to an instance of an object.');
     }
     // Cached?
     if (!is_null($this->_cssStyles)) {
         return $this->_cssStyles;
     }
     // Construct CSS
     $css = array();
     // Start styles
     if ($generateSurroundingHTML) {
         // html { }
         $css['html'] = 'font-family: Calibri, Arial, Helvetica, sans-serif; ';
         $css['html'] .= 'font-size: 10pt; ';
         $css['html'] .= 'background-color: white; ';
     }
     // Fetch sheets
     $sheets = array();
     if (is_null($this->_sheetIndex)) {
         $sheets = $this->_phpExcel->getAllSheets();
     } else {
         $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
     }
     // Build styles per sheet
     foreach ($sheets as $sheet) {
         // Calculate hash code
         $hashCode = $sheet->getHashCode();
         // Build styles
         // table.sheetXXXXXX { }
         $css['table.sheet' . $hashCode] = '';
         if ($sheet->getShowGridlines()) {
             $css['table.sheet' . $hashCode] .= 'border: 1px dotted black; ';
         }
         $css['table.sheet' . $hashCode] .= 'page-break-after: always; ';
         // table.sheetXXXXXX td { }
         $css['table.sheet' . $hashCode . ' td'] = $css['table.sheet' . $hashCode];
         // Default column width
         $columnDimension = $sheet->getDefaultColumnDimension();
         $css['table.sheet' . $hashCode . ' td'] .= 'width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px; ';
         if ($columnDimension->getVisible() === false) {
             $css['table.sheet' . $hashCode . ' td'] .= 'display: none; ';
             $css['table.sheet' . $hashCode . ' td'] .= 'visibility: hidden; ';
         }
         // Calculate column widths
         $sheet->calculateColumnWidths();
         foreach ($sheet->getColumnDimensions() as $columnDimension) {
             $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
             // table.sheetXXXXXX td.columnYYYYYY { }
             $css['table.sheet' . $hashCode . ' td.column' . $column] = 'width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px; ';
             if ($columnDimension->getVisible() === false) {
                 $css['table.sheet' . $hashCode . ' td.column' . $column] .= 'display: none; ';
                 $css['table.sheet' . $hashCode . ' td.column' . $column] .= 'visibility: hidden; ';
             }
         }
         // Default row height
         $rowDimension = $sheet->getDefaultRowDimension();
         // table.sheetXXXXXX tr { }
         $css['table.sheet' . $hashCode . ' tr'] = '';
         // height is disproportionately large
         $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
         $css['table.sheet' . $hashCode . ' tr'] .= 'height: ' . $px_height . 'px; ';
         if ($rowDimension->getVisible() === false) {
             $css['table.sheet' . $hashCode . ' tr'] .= 'display: none; ';
             $css['table.sheet' . $hashCode . ' tr'] .= 'visibility: hidden; ';
         }
         // Calculate row heights
         foreach ($sheet->getRowDimensions() as $rowDimension) {
             // table.sheetXXXXXX tr.rowYYYYYY { }
             $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] = '';
             // height is disproportionately large
             $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
             $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'height: ' . $px_height . 'px; ';
             if ($rowDimension->getVisible() === false) {
                 $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'display: none; ';
                 $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'visibility: hidden; ';
             }
         }
         // Calculate cell style hashes
         $cellStyleHashes = new PHPExcel_HashTable();
         $aStyles = $sheet->getStyles();
         $cellStyleHashes->addFromSource($aStyles);
         $addedStyles = array();
         foreach ($aStyles as $style) {
             if (isset($addedStyles[$style->getHashIndex()])) {
                 continue;
             }
             $css['style' . $style->getHashIndex()] = $this->_createCSSStyle($style);
             $addedStyles[$style->getHashIndex()] = true;
         }
     }
     // Cache
     if (is_null($this->_cssStyles)) {
         $this->_cssStyles = $css;
     }
     // Return
     return $css;
 }
Example #13
0
	/**
	 * Generate CSS styles
	 *
	 * @param	boolean	$generateSurroundingHTML	Generate surrounding HTML tags? (<style> and </style>)
	 * @return	string
	 * @throws	Exception
	 */
	public function generateStyles($generateSurroundingHTML = true) {
		// PHPExcel object known?
		if (is_null($this->_phpExcel)) {
			throw new Exception('Internal PHPExcel object not set to an instance of an object.');
		}

		// Construct HTML
		$html = '';

		// Start styles
		if ($generateSurroundingHTML) {
			$html .= '    <style>' . "\r\n";
			$html .= '    <!--' . "\r\n";
			$html .= '      html {' . "\r\n";
			$html .= '        font-family: Calibri, Arial, Helvetica, Sans Serif;' . "\r\n";
			$html .= '        font-size: 10pt;' . "\r\n";
			$html .= '        background-color: white;' . "\r\n";
			$html .= '      }' . "\r\n";
		}

		// Write styles per sheet
		foreach ($this->_phpExcel->getAllSheets() as $sheet) {
			// Calculate hash code
			$hashCode = $sheet->getHashCode();

			// Write styles
			$html .= '      table.sheet' . $hashCode . ', table.sheet' . $hashCode . ' td {' . "\r\n";
			if ($sheet->getShowGridlines()) {
				$html .= '        border: 1px dotted black;' . "\r\n";
			}
			$html .= '        page-break-after: always;' . "\r\n";
			$html .= '      }' . "\r\n";

			// Default column width
			$columnDimension = $sheet->getDefaultColumnDimension();

			$html .= '      table.sheet' . $hashCode . ' td {' . "\r\n";
			$html .= '        width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;' . "\r\n";
			if ($columnDimension->getVisible() === false) {
				$html .= '        display: none;' . "\r\n";
				$html .= '        visibility: hidden;' . "\r\n";
			}
			$html .= '      }' . "\r\n";

			// Calculate column widths
			$sheet->calculateColumnWidths();
			foreach ($sheet->getColumnDimensions() as $columnDimension) {
				$column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;

				$html .= '      table.sheet' . $hashCode . ' td.column' . $column  . ' {' . "\r\n";
				$html .= '        width: ' . PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth()) . 'px;' . "\r\n";
				if ($columnDimension->getVisible() === false) {
					$html .= '        display: none;' . "\r\n";
					$html .= '        visibility: hidden;' . "\r\n";
				}
				$html .= '      }' . "\r\n";
			}

			// Default row height
			$rowDimension = $sheet->getDefaultRowDimension();

			$html .= '      table.sheet' . $hashCode . ' tr {' . "\r\n";
			// height is disproportionately large
			$px_height = round( PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12 );
			$html .= '        height: ' . $px_height . 'px;' . "\r\n";
			if ($rowDimension->getVisible() === false) {
				$html .= '        display: none;' . "\r\n";
				$html .= '        visibility: hidden;' . "\r\n";
			}
			$html .= '      }' . "\r\n";

			// Calculate row heights
			foreach ($sheet->getRowDimensions() as $rowDimension) {
				$html .= '      table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)  . ' {' . "\r\n";
				// height is disproportionately large
				$px_height = round( PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12 );
				$html .= '        height: ' . $px_height . 'px;' . "\r\n";
				if ($rowDimension->getVisible() === false) {
					$html .= '        display: none;' . "\r\n";
					$html .= '        visibility: hidden;' . "\r\n";
				}
				$html .= '      }' . "\r\n";
			}

			// Calculate cell style hashes
			$cellStyleHashes = new PHPExcel_HashTable();
			$cellStyleHashes->addFromSource( $sheet->getStyles() );
			for ($i = 0; $i < $cellStyleHashes->count(); $i++) {
				$html .= $this->_createCSSStyle( $cellStyleHashes->getByIndex($i) );
			}
		}

		// End styles
		if ($generateSurroundingHTML) {
			$html .= '    -->' . "\r\n";
			$html .= '    </style>' . "\r\n";
		}

		// Return
		return $html;
	}
Example #14
0
 /**
  * Generate sheet data
  *
  * @return    string
  * @throws PHPExcel_Writer_Exception
  */
 public function generateSheetData()
 {
     // PHPExcel object known?
     if (is_null($this->_phpExcel)) {
         throw new PHPExcel_Writer_Exception('Internal PHPExcel object not set to an instance of an object.');
     }
     // Ensure that Spans have been calculated?
     if (!$this->_spansAreCalculated) {
         $this->_calculateSpans();
     }
     // Fetch sheets
     $sheets = array();
     if (is_null($this->_sheetIndex)) {
         $sheets = $this->_phpExcel->getAllSheets();
     } else {
         $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
     }
     // Construct HTML
     $html = '';
     // Loop all sheets
     $sheetId = 0;
     foreach ($sheets as $sheet) {
         // Write table header
         $html .= $this->_generateTableHeader($sheet);
         // Get worksheet dimension
         $dimension = explode(':', $sheet->calculateWorksheetDimension());
         $dimension[0] = PHPExcel_Cell::coordinateFromString($dimension[0]);
         $dimension[0][0] = PHPExcel_Cell::columnIndexFromString($dimension[0][0]) - 1;
         $dimension[1] = PHPExcel_Cell::coordinateFromString($dimension[1]);
         $dimension[1][0] = PHPExcel_Cell::columnIndexFromString($dimension[1][0]) - 1;
         // row min,max
         $rowMin = $dimension[0][1];
         $rowMax = $dimension[1][1];
         // calculate start of <tbody>, <thead>
         $tbodyStart = $rowMin;
         $theadStart = $theadEnd = 0;
         // default: no <thead>	no </thead>
         if ($sheet->getPageSetup()->isRowsToRepeatAtTopSet()) {
             $rowsToRepeatAtTop = $sheet->getPageSetup()->getRowsToRepeatAtTop();
             // we can only support repeating rows that start at top row
             if ($rowsToRepeatAtTop[0] == 1) {
                 $theadStart = $rowsToRepeatAtTop[0];
                 $theadEnd = $rowsToRepeatAtTop[1];
                 $tbodyStart = $rowsToRepeatAtTop[1] + 1;
             }
         }
         // Loop through cells
         $row = $rowMin - 1;
         while ($row++ < $rowMax) {
             // <thead> ?
             if ($row == $theadStart) {
                 $html .= '		<thead>' . PHP_EOL;
             }
             // <tbody> ?
             if ($row == $tbodyStart) {
                 $html .= '		<tbody>' . PHP_EOL;
             }
             // Write row if there are HTML table cells in it
             if (!isset($this->_isSpannedRow[$sheet->getParent()->getIndex($sheet)][$row])) {
                 // Start a new rowData
                 $rowData = array();
                 // Loop through columns
                 $column = $dimension[0][0] - 1;
                 while ($column++ < $dimension[1][0]) {
                     // Cell exists?
                     if ($sheet->cellExistsByColumnAndRow($column, $row)) {
                         $rowData[$column] = PHPExcel_Cell::stringFromColumnIndex($column) . $row;
                     } else {
                         $rowData[$column] = '';
                     }
                 }
                 $html .= $this->_generateRow($sheet, $rowData, $row - 1);
             }
             // </thead> ?
             if ($row == $theadEnd) {
                 $html .= '		</thead>' . PHP_EOL;
             }
         }
         $html .= $this->_extendRowsForChartsAndImages($sheet, $row);
         // Close table body.
         $html .= '		</tbody>' . PHP_EOL;
         // Write table footer
         $html .= $this->_generateTableFooter();
         // Writing PDF?
         if ($this->_isPdf) {
             if (is_null($this->_sheetIndex) && $sheetId + 1 < $this->_phpExcel->getSheetCount()) {
                 $html .= '<div style="page-break-before:always" />';
             }
         }
         // Next sheet
         ++$sheetId;
     }
     // Return
     return $html;
 }
Example #15
0
 public function componentResults_normal_sheet()
 {
     $this->custom_columns = entities\CustomDatatype::getAll();
     $this->cc = isset($this->cc) ? $this->cc : 0;
     require realpath(THEBUGGENIE_VENDOR_PATH) . DS . 'phpoffice' . DS . 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php';
     $phpexcel = new \PHPExcel();
     foreach ($phpexcel->getAllSheets() as $index => $sheet) {
         $phpexcel->removeSheetByIndex($index);
     }
     $this->phpexcel = $phpexcel;
     $this->sheet = $phpexcel->createSheet();
 }
Example #16
0
 /**
  * Build CSS styles
  *
  * @param	boolean	$generateSurroundingHTML	Generate surrounding HTML style? (html { })
  * @return	array
  * @throws	Exception
  */
 public function buildCSS($generateSurroundingHTML = true)
 {
     // PHPExcel object known?
     if (is_null($this->_phpExcel)) {
         throw new Exception('Internal PHPExcel object not set to an instance of an object.');
     }
     // Cached?
     if (!is_null($this->_cssStyles)) {
         return $this->_cssStyles;
     }
     // Construct CSS
     $css = array();
     // Start styles
     if ($generateSurroundingHTML) {
         // html { }
         $css['html'] = 'font-family: Calibri, Arial, Helvetica, sans-serif; ';
         $css['html'] .= 'font-size: 10pt; ';
         $css['html'] .= 'background-color: white; ';
     }
     // Fetch sheets
     $sheets = array();
     if (is_null($this->_sheetIndex)) {
         $sheets = $this->_phpExcel->getAllSheets();
     } else {
         $sheets[] = $this->_phpExcel->getSheet($this->_sheetIndex);
     }
     // Build styles per sheet
     foreach ($sheets as $sheet) {
         // Calculate hash code
         $hashCode = $sheet->getHashCode();
         // Build styles
         // table.sheetXXXXXX { }
         $css['table.sheet' . $hashCode] = '';
         if ($sheet->getShowGridlines()) {
             $css['table.sheet' . $hashCode] .= 'border: 1px dotted black; ';
         }
         $css['table.sheet' . $hashCode] .= 'page-break-after: always; ';
         // table.sheetXXXXXX td { }
         $css['table.sheet' . $hashCode . ' td'] = $css['table.sheet' . $hashCode];
         // Calculate column widths
         $sheet->calculateColumnWidths();
         // col elements, initialize
         $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()) - 1;
         for ($column = 0; $column <= $highestColumnIndex; ++$column) {
             $this->_columnWidths[$hashCode][$column] = 42;
             // approximation
             $css['table.sheet' . $hashCode . ' col.col' . $column] = 'width: 42pt';
         }
         // col elements, loop through columnDimensions and set width
         foreach ($sheet->getColumnDimensions() as $columnDimension) {
             if (($width = PHPExcel_Shared_Drawing::cellDimensionToPixels($columnDimension->getWidth())) >= 0) {
                 $width = PHPExcel_Shared_Drawing::pixelsToPoints($width);
                 $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1;
                 $this->_columnWidths[$hashCode][$column] = $width;
                 $css['table.sheet' . $hashCode . ' col.col' . $column] = 'width: ' . $width . 'pt; ';
                 if ($columnDimension->getVisible() === false) {
                     $css['table.sheet' . $hashCode . ' col.col' . $column] .= 'visibility: collapse; ';
                     $css['table.sheet' . $hashCode . ' col.col' . $column] .= '*display: none; ';
                     // target IE6+7
                 }
             }
         }
         // Default row height
         $rowDimension = $sheet->getDefaultRowDimension();
         // table.sheetXXXXXX tr { }
         $css['table.sheet' . $hashCode . ' tr'] = '';
         // height is disproportionately large
         $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
         $css['table.sheet' . $hashCode . ' tr'] .= 'height: ' . $px_height . 'px; ';
         if ($rowDimension->getVisible() === false) {
             $css['table.sheet' . $hashCode . ' tr'] .= 'display: none; ';
             $css['table.sheet' . $hashCode . ' tr'] .= 'visibility: hidden; ';
         }
         // Calculate row heights
         foreach ($sheet->getRowDimensions() as $rowDimension) {
             // table.sheetXXXXXX tr.rowYYYYYY { }
             $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] = '';
             // height is disproportionately large
             $px_height = round(PHPExcel_Shared_Drawing::cellDimensionToPixels($rowDimension->getRowHeight()) / 12);
             $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'height: ' . $px_height . 'px; ';
             if ($rowDimension->getVisible() === false) {
                 $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'display: none; ';
                 $css['table.sheet' . $hashCode . ' tr.row' . ($rowDimension->getRowIndex() - 1)] .= 'visibility: hidden; ';
             }
         }
         // .b {}
         $css['.b'] = 'text-align: center; ';
         // BOOL
         // .e {}
         $css['.e'] = 'text-align: center; ';
         // ERROR
         // .f {}
         $css['.f'] = 'text-align: right; ';
         // FORMULA
         // .inlineStr {}
         $css['.inlineStr'] = 'text-align: left; ';
         // INLINE
         // .n {}
         $css['.n'] = 'text-align: right; ';
         // NUMERIC
         // .s {}
         $css['.s'] = 'text-align: left; ';
         // STRING
         // Calculate cell style hashes
         $cellStyleHashes = new PHPExcel_HashTable();
         $aStyles = $sheet->getStyles();
         $cellStyleHashes->addFromSource($aStyles);
         $addedStyles = array();
         foreach ($aStyles as $style) {
             if (isset($addedStyles[$style->getHashIndex()])) {
                 continue;
             }
             $css['style' . $style->getHashIndex()] = $this->_createCSSStyle($style);
             $addedStyles[$style->getHashIndex()] = true;
         }
     }
     // Cache
     if (is_null($this->_cssStyles)) {
         $this->_cssStyles = $css;
     }
     // Return
     return $css;
 }