/** * @param $column * @param $row * * @return mixed */ public function getValue($column, $row) { $cell = $this->objWorksheet->getCellByColumnAndRow($column, $row); // Find if this is cell is merged with others foreach ($this->mergedCellsRange as $currMergedRange) { if ($cell->isInRange($currMergedRange)) { $currMergedCellsArray = \PHPExcel_Cell::splitRange($currMergedRange); $cell = $this->objWorksheet->getCell($currMergedCellsArray[0][0]); break; } } return $cell->getValue(); }
/** * 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); }
/** * Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas */ public function writeAllDefinedNamesBiff8() { $chunk = ''; // write the print titles (repeating rows, columns), if any $total_worksheets = count($this->_worksheets); for ($i = 0; $i < $total_worksheets; ++$i) { // repeatColumns / repeatRows if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) { // Row and column titles have been defined // Columns to repeat if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) { $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) { $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); } } // write the print areas, if any for ($i = 0; $i < $total_worksheets; ++$i) { if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) { // Print area $printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea()); $printArea = $printArea[0]; $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]); $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]); $print_rowmin = $printArea[0][1] - 1; $print_rowmax = $printArea[1][1] - 1; $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1; $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1; // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true)); } } return $chunk; }
/** * Write the NAME record to define the print area and the repeat rows and cols. */ private function _writeNames() { // total number of sheets $total_worksheets = $this->_phpExcel->getSheetCount(); // Create the print area NAME records for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); // Write a Name record if the print area has been defined if ($sheetSetup->isPrintAreaSet()) { // Print area $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea()); $printArea = $printArea[0]; $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]); $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]); $print_rowmin = $printArea[0][1] - 1; $print_rowmax = $printArea[1][1] - 1; $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1; $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1; $this->_writeNameShort($i, 0x6, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); } } // Create the print title NAME records for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->_phpExcel->getSheet($i)->getPageSetup(); // simultaneous repeatColumns repeatRows if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; $this->_writeNameLong($i, 0x7, $rowmin, $rowmax, $colmin, $colmax); // (exclusive) either repeatColumns or repeatRows } else { if ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } $this->_writeNameShort($i, 0x7, $rowmin, $rowmax, $colmin, $colmax); } } } }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { $this->_colors = array(); $phpExcel = $this->_phpExcel; $workbook = new PHPExcel_Writer_Excel5_Writer($pFilename); $workbook->setVersion(8); // Set temp dir if ($this->_tempDir != '') { $workbook->setTempDir($this->_tempDir); } // Create empty style $emptyStyle = new PHPExcel_Style(); // Add empty sheets foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $workbook->addWorksheet($sheetName); } $allWorksheets = $workbook->worksheets(); // Add full sheet data foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $allWorksheets[$sheetIndex]; $worksheet->setInputEncoding("UTF-8"); $aStyles = $phpSheet->getStyles(); $freeze = $phpSheet->getFreezePane(); if ($freeze) { list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze); $worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1)); } //if ($sheetIndex == $phpExcel->getActiveSheetIndex()) { // $worksheet->select(); //} if ($phpSheet->getProtection()->getSheet()) { $worksheet->protect($phpSheet->getProtection()->getPassword(), true); } if (!$phpSheet->getShowGridlines()) { $worksheet->hideGridLines(); } $formats = array(); foreach ($phpSheet->getCellCollection() as $cell) { $row = $cell->getRow() - 1; $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 >= 65569) { break; } $style = $emptyStyle; if (isset($aStyles[$cell->getCoordinate()])) { $style = $aStyles[$cell->getCoordinate()]; } $styleHash = $style->getHashCode(); if (!isset($formats[$styleHash])) { $formats[$styleHash] = $workbook->addFormat(array('HAlign' => $style->getAlignment()->getHorizontal(), 'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()), 'TextRotation' => $style->getAlignment()->getTextRotation(), 'Bold' => $style->getFont()->getBold(), 'FontFamily' => $style->getFont()->getName(), 'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()), 'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()), 'Size' => $style->getFont()->getSize(), 'NumFormat' => iconv("UTF-8", "Windows-1252", $style->getNumberFormat()->getFormatCode()), 'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()), 'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()), 'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()), 'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()), 'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()), 'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()), 'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()), 'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()), 'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()), 'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()), 'Pattern' => $this->_mapFillType($style->getFill()->getFillType()))); if ($style->getAlignment()->getWrapText()) { $formats[$styleHash]->setTextWrap(); } if ($style->getFont()->getItalic()) { $formats[$styleHash]->setItalic(); } if ($style->getFont()->getStriketrough()) { $formats[$styleHash]->setStrikeOut(); } } // Write cell value if ($cell->getValue() instanceof PHPExcel_RichText) { $worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]); } else { // Hyperlink? if ($cell->hasHyperlink()) { $worksheet->writeUrl($row, $column, $cell->getHyperlink()->getUrl(), $cell->getValue(), $formats[$styleHash]); } else { $worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash]); } } } $phpSheet->calculateColumnWidths(); foreach ($phpSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $worksheet->setColumn($column, $column, $columnDimension->getWidth(), null, $columnDimension->getVisible() ? '0' : '1'); } foreach ($phpSheet->getRowDimensions() as $rowDimension) { $worksheet->setRow($rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, $rowDimension->getVisible() ? '0' : '1'); } foreach ($phpSheet->getMergeCells() as $cells) { list($first, $last) = PHPExcel_Cell::splitRange($cells); list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last); $worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1); } foreach ($phpSheet->getDrawingCollection() as $drawing) { if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) { $filename = $drawing->getPath(); $imagesize = getimagesize($filename); switch ($imagesize[2]) { case 1: $image = imagecreatefromgif($filename); break; case 2: $image = imagecreatefromjpeg($filename); break; case 3: $image = imagecreatefrompng($filename); break; default: continue 2; } list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates()); $worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]); } } // page setup if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) { $worksheet->setLandscape(); } $worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize()); $worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader()); $worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter()); $worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft()); $worksheet->setMarginRight($phpSheet->getPageMargins()->getRight()); $worksheet->setMarginTop($phpSheet->getPageMargins()->getTop()); $worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom()); } $workbook->close(); }
/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { $this->_colors = array(); $phpExcel = $this->_phpExcel; $workbook = new PHPExcel_Writer_Excel5_Writer($pFilename); $workbook->setVersion(8); // Set temp dir if ($this->_tempDir != '') { $workbook->setTempDir($this->_tempDir); } $saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType(); PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL); // Add empty sheets foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $workbook->addWorksheet($sheetName); } $allWorksheets = $workbook->worksheets(); // Add full sheet data foreach ($phpExcel->getSheetNames() as $sheetIndex => $sheetName) { $phpSheet = $phpExcel->getSheet($sheetIndex); $worksheet = $allWorksheets[$sheetIndex]; $worksheet->setInputEncoding("UTF-8"); // Default style $emptyStyle = $phpSheet->getDefaultStyle(); $aStyles = $phpSheet->getStyles(); $freeze = $phpSheet->getFreezePane(); if ($freeze) { list($column, $row) = PHPExcel_Cell::coordinateFromString($freeze); $worksheet->freezePanes(array($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1)); } //if ($sheetIndex == $phpExcel->getActiveSheetIndex()) { // $worksheet->select(); //} if ($phpSheet->getProtection()->getSheet()) { $worksheet->protect($phpSheet->getProtection()->getPassword(), true); } if (!$phpSheet->getShowGridlines()) { $worksheet->hideGridLines(); } $formats = array(); foreach ($phpSheet->getCellCollection() as $cell) { $row = $cell->getRow() - 1; $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; // Don't break Excel! if ($row + 1 >= 65569) { break; } $style = $emptyStyle; if (isset($aStyles[$cell->getCoordinate()])) { $style = $aStyles[$cell->getCoordinate()]; } $styleHash = $style->getHashCode(); if (!isset($formats[$styleHash])) { $formats[$styleHash] = $workbook->addFormat(array( 'HAlign' => $style->getAlignment()->getHorizontal(), 'VAlign' => $this->_mapVAlign($style->getAlignment()->getVertical()), 'TextRotation' => $style->getAlignment()->getTextRotation(), 'Bold' => $style->getFont()->getBold(), 'FontFamily' => $style->getFont()->getName(), 'Color' => $this->_addColor($workbook, $style->getFont()->getColor()->getRGB()), 'Underline' => $this->_mapUnderline($style->getFont()->getUnderline()), 'Size' => $style->getFont()->getSize(), //~ 'Script' => $style->getSuperscript(), 'NumFormat' => iconv("UTF-8", "Windows-1252", $style->getNumberFormat()->getFormatCode()), 'Bottom' => $this->_mapBorderStyle($style->getBorders()->getBottom()->getBorderStyle()), 'Top' => $this->_mapBorderStyle($style->getBorders()->getTop()->getBorderStyle()), 'Left' => $this->_mapBorderStyle($style->getBorders()->getLeft()->getBorderStyle()), 'Right' => $this->_mapBorderStyle($style->getBorders()->getRight()->getBorderStyle()), 'BottomColor' => $this->_addColor($workbook, $style->getBorders()->getBottom()->getColor()->getRGB()), 'TopColor' => $this->_addColor($workbook, $style->getBorders()->getTop()->getColor()->getRGB()), 'RightColor' => $this->_addColor($workbook, $style->getBorders()->getRight()->getColor()->getRGB()), 'LeftColor' => $this->_addColor($workbook, $style->getBorders()->getLeft()->getColor()->getRGB()), 'FgColor' => $this->_addColor($workbook, $style->getFill()->getStartColor()->getRGB()), 'BgColor' => $this->_addColor($workbook, $style->getFill()->getEndColor()->getRGB()), 'Pattern' => $this->_mapFillType($style->getFill()->getFillType()), )); if ($style->getAlignment()->getWrapText()) { $formats[$styleHash]->setTextWrap(); } if ($style->getAlignment()->getShrinkToFit()) { $formats[$styleHash]->setShrinkToFit(); } if ($style->getFont()->getItalic()) { $formats[$styleHash]->setItalic(); } if ($style->getFont()->getStriketrough()) { $formats[$styleHash]->setStrikeOut(); } } // Write cell value if ($cell->getValue() instanceof PHPExcel_RichText) { $worksheet->write($row, $column, $cell->getValue()->getPlainText(), $formats[$styleHash]); } else { // Hyperlink? if ($cell->hasHyperlink()) { $worksheet->writeUrl($row, $column, str_replace('sheet://', 'internal:', $cell->getHyperlink()->getUrl()), $cell->getValue(), $formats[$styleHash]); } else { $worksheet->write($row, $column, $cell->getValue(), $formats[$styleHash],$style->getNumberFormat()->getFormatCode()); } } } // Column dimensions $phpSheet->calculateColumnWidths(); $defaultWidth = null; if ($phpSheet->getDefaultColumnDimension()->getWidth() >= 0) { $defaultWidth = $phpSheet->getDefaultColumnDimension()->getWidth(); for ($column = 0; $column < PHPExcel_Cell::columnIndexFromString($phpSheet->getHighestColumn()) - 1; $column++) { $worksheet->setColumn( $column, $column, $defaultWidth ); } } foreach ($phpSheet->getColumnDimensions() as $columnDimension) { $column = PHPExcel_Cell::columnIndexFromString($columnDimension->getColumnIndex()) - 1; $worksheet->setColumn( $column, $column, $columnDimension->getWidth(), null, ($columnDimension->getVisible() ? '0' : '1'), $columnDimension->getOutlineLevel()); } // Row dimensions $defaultHeight = null; if ($phpSheet->getDefaultRowDimension()->getRowHeight() >= 0) { $defaultHeight = $phpSheet->getDefaultRowDimension()->getRowHeight(); for ($i = 0; $i < $phpSheet->getHighestRow() - 1; $i++) { $worksheet->setRow( $i, $defaultHeight ); } } foreach ($phpSheet->getRowDimensions() as $rowDimension) { $worksheet->setRow( $rowDimension->getRowIndex() - 1, $rowDimension->getRowHeight(), null, ($rowDimension->getVisible() ? '0' : '1'), $rowDimension->getOutlineLevel() ); } foreach ($phpSheet->getMergeCells() as $cells) { list($first, $last) = PHPExcel_Cell::splitRange($cells); list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last); $worksheet->mergeCells($firstRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1); } foreach ($phpSheet->getDrawingCollection() as $drawing) { if ($drawing instanceof PHPExcel_Worksheet_BaseDrawing) { $filename = $drawing->getPath(); $imagesize = getimagesize($filename); switch ($imagesize[2]) { case 1: $image = imagecreatefromgif($filename); break; case 2: $image = imagecreatefromjpeg($filename); break; case 3: $image = imagecreatefrompng($filename); break; default: continue 2; } list($column, $row) = PHPExcel_Cell::coordinateFromString($drawing->getCoordinates()); $worksheet->insertBitmap($row - 1, PHPExcel_Cell::columnIndexFromString($column) - 1, $image, $drawing->getOffsetX(), $drawing->getOffsetY(), $drawing->getWidth() / $imagesize[0], $drawing->getHeight() / $imagesize[1]); } } // page setup if ($phpSheet->getPageSetup()->getOrientation() == PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE) { $worksheet->setLandscape(); } $worksheet->setPaper($phpSheet->getPageSetup()->getPaperSize()); $worksheet->setHeader($phpSheet->getHeaderFooter()->getOddHeader(), $phpSheet->getPageMargins()->getHeader()); $worksheet->setFooter($phpSheet->getHeaderFooter()->getOddFooter(), $phpSheet->getPageMargins()->getFooter()); $worksheet->setMarginLeft($phpSheet->getPageMargins()->getLeft()); $worksheet->setMarginRight($phpSheet->getPageMargins()->getRight()); $worksheet->setMarginTop($phpSheet->getPageMargins()->getTop()); $worksheet->setMarginBottom($phpSheet->getPageMargins()->getBottom()); // ------------------------------------------------------------------- // Commented due to bug: // http://pear.php.net/bugs/bug.php?id=2146 // ------------------------------------------------------------------- // // repeatColumns / repeatRows // if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet() || $phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) { // // Columns to repeat // if ($phpSheet->getPageSetup()->isColumnsToRepeatAtLeftSet()) { // $repeat = $phpSheet->getPageSetup()->getColumnsToRepeatAtLeft(); // // $worksheet->repeatColumns(PHPExcel_Cell::columnIndexFromString($repeat[0] - 1), PHPExcel_Cell::columnIndexFromString($repeat[1] - 1)); // } // // // Rows to repeat // if ($phpSheet->getPageSetup()->isRowsToRepeatAtTopSet()) { // $repeat = $phpSheet->getPageSetup()->getRowsToRepeatAtTop(); // // $worksheet->repeatRows($repeat[0], $repeat[1]); // } // } // ------------------------------------------------------------------- // Commented due to bug: // http://pear.php.net/bugs/bug.php?id=2146 // ------------------------------------------------------------------- // if ($phpSheet->getPageSetup()->isPrintAreaSet()) { // // Print area // $printArea = PHPExcel_Cell::splitRange($phpSheet->getPageSetup()->getPrintArea()); // $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]); // $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]); // // $worksheet->printArea( // $printArea[0][1], // PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1, // $printArea[1][1], // PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1 // ); // } // Support for print scale if ($phpSheet->getPageSetup()->getScale()) { $worksheet->setPrintScale($phpSheet->getPageSetup()->getScale()); } // Support for fitting to pages if ($phpSheet->getPageSetup()->getFitToWidth()) { if ($phpSheet->getPageSetup()->getFitToHeight()) { // Both properties are set, so use them // Note: This case is double, see below $worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight()); } else { // Only width given, make assumption about height $height = 0; $worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $height); } } else if ($phpSheet->getPageSetup()->getFitToHeight()) { if ($phpSheet->getPageSetup()->getFitToWidth()) { // Both properties are set, so use them // Note: This case is double, see below $worksheet->fitToPages($phpSheet->getPageSetup()->getFitToWidth(), $phpSheet->getPageSetup()->getFitToHeight()); } else { // Only height given, make assumption about width $width = 0; $worksheet->fitToPages($width, $phpSheet->getPageSetup()->getFitToHeight()); } } // Support for breaks $vBreaks = array(); $hBreaks = array(); foreach ($phpSheet->getBreaks() as $cell => $breakType) { // Fetch coordinates $coordinates = PHPExcel_Cell::coordinateFromString($cell); // Decide what to do by the type of break switch ($breakType) { case PHPExcel_Worksheet::BREAK_COLUMN: // Add to list of vertical breaks $vBreaks[] = $coordinates[0]; break; case PHPExcel_Worksheet::BREAK_ROW: // Add to list of horizontal breaks $hBreaks[] = $coordinates[1]; break; case PHPExcel_Worksheet::BREAK_NONE: default: // Nothing to do break; } } $worksheet->setVPagebreaks($vBreaks); $worksheet->setHPagebreaks($hBreaks); } PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType); $workbook->close(); }
/** * Write Defined Name for PrintTitles * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet * @param int $pSheetId * @throws Exception */ private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0) { // definedName for PrintArea if ($pSheet->getPageSetup()->isPrintAreaSet()) { $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', '_xlnm.Print_Area'); $objWriter->writeAttribute('localSheetId', $pSheetId); // Setting string $settingString = ''; // Print area $printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea()); $printArea[0] = PHPExcel_Cell::absoluteCoordinate($printArea[0]); $printArea[1] = PHPExcel_Cell::absoluteCoordinate($printArea[1]); $objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . implode(':', $printArea)); $objWriter->endElement(); } }
/** * Store the MERGEDCELLS records for all ranges of merged cells */ private function _writeMergedCells() { $mergeCells = $this->_phpSheet->getMergeCells(); $countMergeCells = count($mergeCells); if ($countMergeCells == 0) { return; } // maximum allowed number of merged cells per record $maxCountMergeCellsPerRecord = 1027; // record identifier $record = 0xe5; // counter for total number of merged cells treated so far by the writer $i = 0; // counter for number of merged cells written in record currently being written $j = 0; // initialize record data $recordData = ''; // loop through the merged cells foreach ($mergeCells as $mergeCell) { ++$i; ++$j; // extract the row and column indexes $range = PHPExcel_Cell::splitRange($mergeCell); list($first, $last) = $range[0]; list($firstColumn, $firstRow) = PHPExcel_Cell::coordinateFromString($first); list($lastColumn, $lastRow) = PHPExcel_Cell::coordinateFromString($last); $recordData .= pack('vvvv', $firstRow - 1, $lastRow - 1, PHPExcel_Cell::columnIndexFromString($firstColumn) - 1, PHPExcel_Cell::columnIndexFromString($lastColumn) - 1); // flush record if we have reached limit for number of merged cells, or reached final merged cell if ($j == $maxCountMergeCellsPerRecord or $i == $countMergeCells) { $recordData = pack('v', $j) . $recordData; $length = strlen($recordData); $header = pack('vv', $record, $length); $this->_append($header . $recordData); // initialize for next record, if any $recordData = ''; $j = 0; } } }
/** * Update cell range * * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws PHPExcel_Exception */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = PHPExcel_Cell::splitRange($pCellRange); $ic = count($range); for ($i = 0; $i < $ic; ++$i) { $jc = count($range[$i]); for ($j = 0; $j < $jc; ++$j) { if (ctype_alpha($range[$i][$j])) { $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[0]; } elseif (ctype_digit($range[$i][$j])) { $r = PHPExcel_Cell::coordinateFromString($this->_updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[1]; } else { $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } } // Recreate range string return PHPExcel_Cell::buildRange($range); } else { throw new PHPExcel_Exception("Only cell ranges may be passed to this method."); } }
/** * Extract all cell references in range * * @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000) * @return array Array containing single cell references */ public static function extractAllCellReferencesInRange($pRange = 'A1') { // Returnvalue $returnValue = array(); // Explode spaces $aExplodeSpaces = explode(' ', str_replace('$', '', strtoupper($pRange))); foreach ($aExplodeSpaces as $explodedSpaces) { // Single cell? if (strpos($explodedSpaces, ':') === false && strpos($explodedSpaces, ',') === false) { $col = 'A'; $row = 1; list($col, $row) = PHPExcel_Cell::coordinateFromString($explodedSpaces); if (strlen($col) <= 2) { $returnValue[] = $explodedSpaces; } continue; } // Range... $range = PHPExcel_Cell::splitRange($explodedSpaces); for ($i = 0; $i < count($range); ++$i) { // Single cell? if (count($range[$i]) == 1) { $col = 'A'; $row = 1; list($col, $row) = PHPExcel_Cell::coordinateFromString($range[$i]); if (strlen($col) <= 2) { $returnValue[] = $explodedSpaces; } } // Range... $rangeStart = $rangeEnd = ''; $startingCol = $startingRow = $endingCol = $endingRow = 0; list($rangeStart, $rangeEnd) = $range[$i]; list($startingCol, $startingRow) = PHPExcel_Cell::coordinateFromString($rangeStart); list($endingCol, $endingRow) = PHPExcel_Cell::coordinateFromString($rangeEnd); // Conversions... $startingCol = PHPExcel_Cell::columnIndexFromString($startingCol); $endingCol = PHPExcel_Cell::columnIndexFromString($endingCol); // Current data $currentCol = --$startingCol; $currentRow = $startingRow; // Loop cells while ($currentCol < $endingCol) { $loopColumn = PHPExcel_Cell::stringFromColumnIndex($currentCol); while ($currentRow <= $endingRow) { $returnValue[] = $loopColumn . $currentRow; ++$currentRow; } ++$currentCol; $currentRow = $startingRow; } } } // Return value return $returnValue; }
/** * Extract range values * * @param string &$pRange String based range representation * @param PHPExcel_Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not * @throws PHPExcel_Calculation_Exception */ public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = NULL, $resetLog = TRUE) { // Return value $returnValue = array(); // echo 'extractNamedRange('.$pRange.')<br />'; if ($pSheet !== NULL) { $pSheetName = $pSheet->getTitle(); // echo 'Current sheet name is '.$pSheetName.'<br />'; // echo 'Range reference is '.$pRange.'<br />'; if (strpos($pRange, '!') !== false) { // echo '$pRange reference includes sheet reference',PHP_EOL; list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true); // echo 'New sheet name is '.$pSheetName,PHP_EOL; // echo 'Adjusted Range reference is '.$pRange,PHP_EOL; $pSheet = $this->_workbook->getSheetByName($pSheetName); } // Named range? $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet); if ($namedRange !== NULL) { $pSheet = $namedRange->getWorksheet(); // echo 'Named Range '.$pRange.' ('; $pRange = $namedRange->getRange(); $splitRange = PHPExcel_Cell::splitRange($pRange); // Convert row and column references if (ctype_alpha($splitRange[0][0])) { $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow(); } elseif (ctype_digit($splitRange[0][0])) { $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1]; } // echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />'; // if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) { // if (!$namedRange->getLocalOnly()) { // $pSheet = $namedRange->getWorksheet(); // } else { // return $returnValue; // } // } } else { return PHPExcel_Calculation_Functions::REF(); } // Extract range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange); // var_dump($aReferences); if (!isset($aReferences[1])) { // Single cell (or single column or row) in range list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]); $cellValue = NULL; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } else { // Extract cell data for all cells in the range foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference); // echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />'; $cellValue = NULL; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = NULL; } } } // print_r($returnValue); // echo '<br />'; } // Return return $returnValue; }
/** * Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas */ private function _writeAllDefinedNamesBiff8() { $chunk = ''; // total number of sheets $total_worksheets = count($this->_phpExcel->getAllSheets()); // write the print titles (repeating rows, columns), if any for ($i = 0; $i < $total_worksheets; ++$i) { // simultaneous repeatColumns repeatRows if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() && $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) { $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; // construct formula data manually $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax); // tArea3d $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255); // tArea3d $formulaData .= pack('C', 0x10); // tList // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); // (exclusive) either repeatColumns or repeatRows } else { if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet() || $this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($this->_phpExcel->getSheet($i)->getPageSetup()->isColumnsToRepeatAtLeftSet()) { $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($this->_phpExcel->getSheet($i)->getPageSetup()->isRowsToRepeatAtTopSet()) { $repeat = $this->_phpExcel->getSheet($i)->getPageSetup()->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); } } } // write the print areas, if any for ($i = 0; $i < $total_worksheets; ++$i) { if ($this->_phpExcel->getSheet($i)->getPageSetup()->isPrintAreaSet()) { // Print area $printArea = PHPExcel_Cell::splitRange($this->_phpExcel->getSheet($i)->getPageSetup()->getPrintArea()); $printArea = $printArea[0]; $printArea[0] = PHPExcel_Cell::coordinateFromString($printArea[0]); $printArea[1] = PHPExcel_Cell::coordinateFromString($printArea[1]); $print_rowmin = $printArea[0][1] - 1; $print_rowmax = $printArea[1][1] - 1; $print_colmin = PHPExcel_Cell::columnIndexFromString($printArea[0][0]) - 1; $print_colmax = PHPExcel_Cell::columnIndexFromString($printArea[1][0]) - 1; // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true)); } } return $chunk; }
/** * Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas */ private function writeAllDefinedNamesBiff8() { $chunk = ''; // Named ranges if (count($this->phpExcel->getNamedRanges()) > 0) { // Loop named ranges $namedRanges = $this->phpExcel->getNamedRanges(); foreach ($namedRanges as $namedRange) { // Create absolute coordinate $range = PHPExcel_Cell::splitRange($namedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]); } } $range = PHPExcel_Cell::buildRange($range); // e.g. Sheet1!$A$1:$B$2 // parse formula try { $error = $this->parser->parse($range); $formulaData = $this->parser->toReversePolish(); // make sure tRef3d is of type tRef3dR (0x3A) if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) { $formulaData = ":" . substr($formulaData, 1); } if ($namedRange->getLocalOnly()) { // local scope $scope = $this->phpExcel->getIndex($namedRange->getScope()) + 1; } else { // global scope $scope = 0; } $chunk .= $this->writeData($this->writeDefinedNameBiff8($namedRange->getName(), $formulaData, $scope, false)); } catch (PHPExcel_Exception $e) { // do nothing } } } // total number of sheets $total_worksheets = $this->phpExcel->getSheetCount(); // write the print titles (repeating rows, columns), if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup(); // simultaneous repeatColumns repeatRows if ($sheetSetup->isColumnsToRepeatAtLeftSet() && $sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; // construct formula data manually $formulaData = pack('Cv', 0x29, 0x17); // tMemFunc $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax); // tArea3d $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255); // tArea3d $formulaData .= pack('C', 0x10); // tList // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); // (exclusive) either repeatColumns or repeatRows } elseif ($sheetSetup->isColumnsToRepeatAtLeftSet() || $sheetSetup->isRowsToRepeatAtTopSet()) { // Columns to repeat if ($sheetSetup->isColumnsToRepeatAtLeftSet()) { $repeat = $sheetSetup->getColumnsToRepeatAtLeft(); $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1; $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1; } else { $colmin = 0; $colmax = 255; } // Rows to repeat if ($sheetSetup->isRowsToRepeatAtTopSet()) { $repeat = $sheetSetup->getRowsToRepeatAtTop(); $rowmin = $repeat[0] - 1; $rowmax = $repeat[1] - 1; } else { $rowmin = 0; $rowmax = 65535; } // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax); // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true)); } } // write the print areas, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetSetup = $this->phpExcel->getSheet($i)->getPageSetup(); if ($sheetSetup->isPrintAreaSet()) { // Print area, e.g. A3:J6,H1:X20 $printArea = PHPExcel_Cell::splitRange($sheetSetup->getPrintArea()); $countPrintArea = count($printArea); $formulaData = ''; for ($j = 0; $j < $countPrintArea; ++$j) { $printAreaRect = $printArea[$j]; // e.g. A3:J6 $printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]); $printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]); $print_rowmin = $printAreaRect[0][1] - 1; $print_rowmax = $printAreaRect[1][1] - 1; $print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1; $print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1; // construct formula data manually because parser does not recognize absolute 3d cell references $formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax); if ($j > 0) { $formulaData .= pack('C', 0x10); // list operator token ',' } } // store the DEFINEDNAME record $chunk .= $this->writeData($this->writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true)); } } // write autofilters, if any for ($i = 0; $i < $total_worksheets; ++$i) { $sheetAutoFilter = $this->phpExcel->getSheet($i)->getAutoFilter(); $autoFilterRange = $sheetAutoFilter->getRange(); if (!empty($autoFilterRange)) { $rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange); //Autofilter built in name $name = pack('C', 0xd); $chunk .= $this->writeData($this->writeShortNameBiff8($name, $i + 1, $rangeBounds, true)); } } return $chunk; }
/** * Calculate information about HTML colspan and rowspan which is not always the same as Excel's */ private function _calculateSpans() { // Identify all cells that should be omitted in HTML due to cell merge. // In HTML only the upper-left cell should be written and it should have // appropriate rowspan / colspan attribute $sheetIndexes = $this->_sheetIndex !== null ? array($this->_sheetIndex) : range(0, $this->_phpExcel->getSheetCount() - 1); foreach ($sheetIndexes as $sheetIndex) { $sheet = $this->_phpExcel->getSheet($sheetIndex); $candidateSpannedRow = array(); // loop through all Excel merged cells foreach ($sheet->getMergeCells() as $cells) { list($cells, ) = PHPExcel_Cell::splitRange($cells); $first = $cells[0]; $last = $cells[1]; list($fc, $fr) = PHPExcel_Cell::coordinateFromString($first); $fc = PHPExcel_Cell::columnIndexFromString($fc) - 1; list($lc, $lr) = PHPExcel_Cell::coordinateFromString($last); $lc = PHPExcel_Cell::columnIndexFromString($lc) - 1; // loop through the individual cells in the individual merge $r = $fr - 1; while ($r++ < $lr) { // also, flag this row as a HTML row that is candidate to be omitted $candidateSpannedRow[$r] = $r; $c = $fc - 1; while ($c++ < $lc) { if (!($c == $fc && $r == $fr)) { // not the upper-left cell (should not be written in HTML) $this->_isSpannedCell[$sheetIndex][$r][$c] = array('baseCell' => array($fr, $fc)); } else { // upper-left is the base cell that should hold the colspan/rowspan attribute $this->_isBaseCell[$sheetIndex][$r][$c] = array('xlrowspan' => $lr - $fr + 1, 'rowspan' => $lr - $fr + 1, 'xlcolspan' => $lc - $fc + 1, 'colspan' => $lc - $fc + 1); } } } } // Identify which rows should be omitted in HTML. These are the rows where all the cells // participate in a merge and the where base cells are somewhere above. $countColumns = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); foreach ($candidateSpannedRow as $rowIndex) { if (isset($this->_isSpannedCell[$sheetIndex][$rowIndex])) { if (count($this->_isSpannedCell[$sheetIndex][$rowIndex]) == $countColumns) { $this->_isSpannedRow[$sheetIndex][$rowIndex] = $rowIndex; } } } // For each of the omitted rows we found above, the affected rowspans should be subtracted by 1 if (isset($this->_isSpannedRow[$sheetIndex])) { foreach ($this->_isSpannedRow[$sheetIndex] as $rowIndex) { $adjustedBaseCells = array(); $c = -1; $e = $countColumns - 1; while ($c++ < $e) { $baseCell = $this->_isSpannedCell[$sheetIndex][$rowIndex][$c]['baseCell']; if (!in_array($baseCell, $adjustedBaseCells)) { // subtract rowspan by 1 --$this->_isBaseCell[$sheetIndex][$baseCell[0]][$baseCell[1]]['rowspan']; $adjustedBaseCells[] = $baseCell; } } } } // TODO: Same for columns } // We have calculated the spans $this->_spansAreCalculated = true; }
/** * Select a range of cells. * * @param string $pCoordinate Cell range, examples: 'A1', 'B2:G5', 'A:C', '3:6' * @throws PHPExcel_Exception * @return PHPExcel_Worksheet */ public function setSelectedCells($pCoordinate = 'A1') { // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); // Convert 'A' to 'A:A' $pCoordinate = preg_replace('/^([A-Z]+)$/', '${1}:${1}', $pCoordinate); // Convert '1' to '1:1' $pCoordinate = preg_replace('/^([0-9]+)$/', '${1}:${1}', $pCoordinate); // Convert 'A:C' to 'A1:C1048576' $pCoordinate = preg_replace('/^([A-Z]+):([A-Z]+)$/', '${1}1:${2}1048576', $pCoordinate); // Convert '1:3' to 'A1:XFD3' $pCoordinate = preg_replace('/^([0-9]+):([0-9]+)$/', 'A${1}:XFD${2}', $pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { list($first, ) = PHPExcel_Cell::splitRange($pCoordinate); $this->_activeCell = $first[0]; } else { $this->_activeCell = $pCoordinate; } $this->_selectedCells = $pCoordinate; return $this; }
/** * Extract range values * * @param string &$pRange String based range representation * @param PHPExcel_Worksheet $pSheet Worksheet * @return mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned. * @param boolean $resetLog Flag indicating whether calculation log should be reset or not * @throws PHPExcel_Calculation_Exception */ public function extractNamedRange(&$pRange = 'A1', PHPExcel_Worksheet $pSheet = null, $resetLog = true) { // Return value $returnValue = array(); if ($pSheet !== null) { $pSheetName = $pSheet->getTitle(); if (strpos($pRange, '!') !== false) { list($pSheetName, $pRange) = PHPExcel_Worksheet::extractSheetTitle($pRange, true); $pSheet = $this->_workbook->getSheetByName($pSheetName); } // Named range? $namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet); if ($namedRange !== null) { $pSheet = $namedRange->getWorksheet(); $pRange = $namedRange->getRange(); $splitRange = PHPExcel_Cell::splitRange($pRange); // Convert row and column references if (ctype_alpha($splitRange[0][0])) { $pRange = $splitRange[0][0] . '1:' . $splitRange[0][1] . $namedRange->getWorksheet()->getHighestRow(); } elseif (ctype_digit($splitRange[0][0])) { $pRange = 'A' . $splitRange[0][0] . ':' . $namedRange->getWorksheet()->getHighestColumn() . $splitRange[0][1]; } } else { return PHPExcel_Calculation_Functions::REF(); } // Extract range $aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange); if (!isset($aReferences[1])) { // Single cell (or single column or row) in range list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($aReferences[0]); $cellValue = null; if ($pSheet->cellExists($aReferences[0])) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } else { // Extract cell data for all cells in the range foreach ($aReferences as $reference) { // Extract range list($currentCol, $currentRow) = PHPExcel_Cell::coordinateFromString($reference); $cellValue = null; if ($pSheet->cellExists($reference)) { $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog); } else { $returnValue[$currentRow][$currentCol] = null; } } } } // Return return $returnValue; }
/** * Is this cell the master (top left cell) in a merge range (that holds the actual data value) * * @return boolean */ public function isMergeRangeValueCell() { if ($mergeRange = $this->getMergeRange()) { $mergeRange = PHPExcel_Cell::splitRange($mergeRange); list($startCell) = $mergeRange[0]; if ($this->getCoordinate() === $startCell) { return true; } } return false; }
/** * Generate row * * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number * @return string * @throws Exception */ private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (is_array($pValues)) { // Construct HTML $html = ''; // Sheet index $sheetIndex = $pSheet->getParent()->getIndex($pSheet); // Write row start if (!$this->_useInlineCss) { $html .= ' <tr class="row' . $pRow . '">' . "\r\n"; } else { $style = isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) ? $this->_assembleCSS($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]) : ''; $html .= ' <tr style="' . $style . '">' . "\r\n"; } // Write cells $colNum = 0; foreach ($pValues as $cell) { if (!$this->_useInlineCss) { $cssClass = ''; $cssClass = 'column' . $colNum; } else { $cssClass = array(); if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum])) { $this->_cssStyles['table.sheet' . $sheetIndex . ' td.column' . $colNum]; } } $colSpan = 1; $rowSpan = 1; $writeCell = true; // Write cell // initialize $cellData = ''; // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop trough rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . $this->_assembleCSS($this->_createCSSStyleFont($element->getFont())) . '">'; if ($element->getFont()->getSuperScript()) { $cellData .= '<sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '<sub>'; } } } // Convert UTF8 data to PCDATA $cellText = $element->getText(); $cellData .= htmlspecialchars($cellText); if ($element instanceof PHPExcel_RichText_Run) { if ($element->getFont()->getSuperScript()) { $cellData .= '</sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '</sub>'; } } $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode()); } // Convert UTF8 data to PCDATA $cellData = htmlspecialchars($cellData); } // replace leading spaces on each line with $cellData = $this->_convertNbsp($cellData); // convert newline "\n" to '<br>' $cellData = str_replace("\n", '<br/>', $cellData); // Check value if ($cellData == '') { $cellData = ' '; } // Extend CSS class? if (!$this->_useInlineCss) { $cssClass .= ' style' . $cell->getXfIndex(); $cssClass .= ' ' . $cell->getDataType(); } else { if (isset($this->_cssStyles['td.style' . $cell->getXfIndex()])) { $cssClass = array_merge($cssClass, $this->_cssStyles['td.style' . $cell->getXfIndex()]); } // General horizontal alignment: Actual horizontal alignment depends on dataType $sharedStyle = $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex()); if ($sharedStyle->getAlignment()->getHorizontal() == PHPExcel_Style_Alignment::HORIZONTAL_GENERAL && isset($this->_cssStyles['.' . $cell->getDataType()]['text-align'])) { $cssClass['text-align'] = $this->_cssStyles['.' . $cell->getDataType()]['text-align']; } } } else { $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', PHPExcel_Cell_DataType::TYPE_NULL, $pSheet); } // Hyperlink? if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) { $cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>'; } // Column/rowspan foreach ($pSheet->getMergeCells() as $cells) { if ($cell->isInRange($cells)) { list($first, ) = PHPExcel_Cell::splitRange($cells); if ($first[0] == $cell->getCoordinate()) { list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells); } else { $writeCell = false; } break; } } // Write if ($writeCell) { // Column start $html .= ' <td'; if (!$this->_useInlineCss) { $html .= ' class="' . $cssClass . '"'; } else { //** Necessary redundant code for the sake of PHPExcel_Writer_PDF ** // We must explicitly write the width of the <td> element because TCPDF // does not recognize e.g. <col style="width:42pt"> $width = 0; $columnIndex = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; for ($i = $columnIndex; $i < $columnIndex + $colSpan; ++$i) { if (isset($this->_columnWidths[$sheetIndex][$i])) { $width += $this->_columnWidths[$sheetIndex][$i]; } } $cssClass['width'] = $width . 'pt'; // We must also explicitly write the height of the <td> element because TCPDF // does not recognize e.g. <tr style="height:50pt"> if (isset($this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height'])) { $height = $this->_cssStyles['table.sheet' . $sheetIndex . ' tr.row' . $pRow]['height']; $cssClass['height'] = $height; } //** end of redundant code ** $html .= ' style="' . $this->_assembleCSS($cssClass) . '"'; } if ($colSpan > 1) { $html .= ' colspan="' . $colSpan . '"'; } if ($rowSpan > 1) { $html .= ' rowspan="' . $rowSpan . '"'; } $html .= '>'; // Image? $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate()); // Cell data $html .= $cellData; // Column end $html .= '</td>' . "\r\n"; } // Next column ++$colNum; } // Write row end $html .= ' </tr>' . "\r\n"; // Return return $html; } else { throw new Exception("Invalid parameters passed."); } }
function getValueMergedCell(PHPExcel $objPHPExcel, $indexSheet = 0, $arr_merged_allCells, $col, $row) { $temp = null; $objPHPExcel->setActiveSheetIndex($indexSheet); $cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row); foreach ($arr_merged_allCells[$indexSheet] as $currMergedRange) { if ($cell->isInRange($currMergedRange)) { //возвращает массив /* Array ( [0] => Array ( [0] => A25 [1] => A36 ) )*/ $currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange); $temp = $objPHPExcel->getActiveSheet()->getCell($currMergedCellsArray[0][0])->getFormattedValue(); } } return $temp; }
/** * Write AutoFilter * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { if ($pSheet->getAutoFilter() != '') { // autoFilter $objWriter->startElement('autoFilter'); // Strip any worksheet reference from the filter coordinates $range = PHPExcel_Cell::splitRange($pSheet->getAutoFilter()); $range = $range[0]; // Strip any worksheet ref if (strpos($range[0], '!') !== false) { list($ws, $range[0]) = explode('!', $range[0]); } $range = implode(':', $range); $objWriter->writeAttribute('ref', str_replace('$', '', $range)); $objWriter->endElement(); } }
/** * Write Defined Name for autoFilter * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet * @param int $pSheetId * @throws Exception */ private function _writeDefinedNameForAutofilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0) { // definedName for autoFilter if ($pSheet->getAutoFilter() != '') { $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', '_xlnm._FilterDatabase'); $objWriter->writeAttribute('localSheetId', $pSheetId); $objWriter->writeAttribute('hidden', '1'); // Create absolute coordinate and write as raw text $range = PHPExcel_Cell::splitRange($pSheet->getAutoFilter()); $range[0] = PHPExcel_Cell::absoluteCoordinate($range[0]); $range[1] = PHPExcel_Cell::absoluteCoordinate($range[1]); $range = implode(':', $range); $objWriter->writeRaw('\'' . $pSheet->getTitle() . '\'!' . $range); $objWriter->endElement(); } }
/** * Write row to HTML file * * @param mixed $pFileHandle PHP filehandle * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number * @throws Exception */ private function _writeRow($pFileHandle = null, PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (!is_null($pFileHandle) && is_array($pValues)) { // Write row start fwrite($pFileHandle, ' <tr class="row' . $pRow . '">' . "\r\n"); // Write cells $colNum = 0; foreach ($pValues as $cell) { $cellData = ' '; $cssClass = 'column' . $colNum; $colSpan = 1; $rowSpan = 1; $writeCell = true; // Write cell // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop trough rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">'; } $cellData .= $element->getText(); if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } } // Check value if ($cellData == '') { $cellData = ' '; } // Extend CSS class? if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) { $cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashCode(); } } else { $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null); } // Hyperlink? if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) { $cellData = '<a href="' . $cell->getHyperlink()->getUrl() . '" title="' . $cell->getHyperlink()->getTooltip() . '">' . $cellData . '</a>'; } // Column/rowspan foreach ($pSheet->getMergeCells() as $cells) { if ($cell->isInRange($cells)) { list($first, ) = PHPExcel_Cell::splitRange($cells); if ($first == $cell->getCoordinate()) { list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells); } else { $writeCell = false; } break; } } // Write if ($writeCell) { // Column start fwrite($pFileHandle, ' <td'); fwrite($pFileHandle, ' class="' . $cssClass . '"'); if ($colSpan > 1) { fwrite($pFileHandle, ' colspan="' . $colSpan . '"'); } if ($rowSpan > 1) { fwrite($pFileHandle, ' rowspan="' . $rowSpan . '"'); } fwrite($pFileHandle, '>'); // Image? $this->_writeImageInCell($pFileHandle, $pSheet, $cell->getCoordinate()); // Cell data fwrite($pFileHandle, $cellData); // Column end fwrite($pFileHandle, '</td>' . "\r\n"); } // Next column $colNum++; } // Write row end fwrite($pFileHandle, ' </tr>' . "\r\n"); } else { throw new Exception("Invalid parameters passed."); } }
/** * Write AutoFilter * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws PHPExcel_Writer_Exception */ private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { $autoFilterRange = $pSheet->getAutoFilter()->getRange(); if (!empty($autoFilterRange)) { // autoFilter $objWriter->startElement('autoFilter'); // Strip any worksheet reference from the filter coordinates $range = PHPExcel_Cell::splitRange($autoFilterRange); $range = $range[0]; // Strip any worksheet ref if (strpos($range[0], '!') !== false) { list($ws, $range[0]) = explode('!', $range[0]); } $range = implode(':', $range); $objWriter->writeAttribute('ref', str_replace('$', '', $range)); $columns = $pSheet->getAutoFilter()->getColumns(); if (count($columns > 0)) { foreach ($columns as $columnID => $column) { $rules = $column->getRules(); if (count($rules > 0)) { $objWriter->startElement('filterColumn'); $objWriter->writeAttribute('colId', $pSheet->getAutoFilter()->getColumnOffset($columnID)); $objWriter->startElement($column->getFilterType()); if ($column->getJoin() == PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND) { $objWriter->writeAttribute('and', 1); } foreach ($rules as $rule) { if ($column->getFilterType() === PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER && $rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL && $rule->getValue() === '') { // Filter rule for Blanks $objWriter->writeAttribute('blank', 1); } elseif ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER) { // Dynamic Filter Rule $objWriter->writeAttribute('type', $rule->getGrouping()); $val = $column->getAttribute('val'); if ($val !== NULL) { $objWriter->writeAttribute('val', $val); } $maxVal = $column->getAttribute('maxVal'); if ($maxVal !== NULL) { $objWriter->writeAttribute('maxVal', $maxVal); } } elseif ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER) { // Top 10 Filter Rule $objWriter->writeAttribute('val', $rule->getValue()); $objWriter->writeAttribute('percent', $rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT ? '1' : '0'); $objWriter->writeAttribute('top', $rule->getGrouping() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP ? '1' : '0'); } else { // Filter, DateGroupItem or CustomFilter $objWriter->startElement($rule->getRuleType()); if ($rule->getOperator() !== PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL) { $objWriter->writeAttribute('operator', $rule->getOperator()); } if ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP) { // Date Group filters foreach ($rule->getValue() as $key => $value) { if ($value > '') { $objWriter->writeAttribute($key, $value); } } $objWriter->writeAttribute('dateTimeGrouping', $rule->getGrouping()); } else { $objWriter->writeAttribute('val', $rule->getValue()); } $objWriter->endElement(); } } $objWriter->endElement(); $objWriter->endElement(); } } } $objWriter->endElement(); } }
/** * Extract all cell references in range * * @param string $pRange Range (e.g. A1 or A1:A10 or A1:A10 A100:A1000) * @return array Array containing single cell references */ public static function extractAllCellReferencesInRange($pRange = 'A1') { // Returnvalue $returnValue = array(); // Explode spaces $cellBlocks = explode(' ', str_replace('$', '', strtoupper($pRange))); foreach ($cellBlocks as $cellBlock) { // Single cell? if (strpos($cellBlock, ':') === false && strpos($cellBlock, ',') === false) { $returnValue[] = $cellBlock; continue; } // Range... $ranges = PHPExcel_Cell::splitRange($cellBlock); foreach ($ranges as $range) { // Single cell? if (!isset($range[1])) { $returnValue[] = $range[0]; continue; } // Range... list($rangeStart, $rangeEnd) = $range; list($startCol, $startRow) = sscanf($rangeStart, '%[A-Z]%d'); list($endCol, $endRow) = sscanf($rangeEnd, '%[A-Z]%d'); $endCol++; // Current data $currentCol = $startCol; $currentRow = $startRow; // Loop cells while ($currentCol != $endCol) { while ($currentRow <= $endRow) { $returnValue[] = $currentCol . $currentRow; ++$currentRow; } ++$currentCol; $currentRow = $startRow; } } } // Return value return $returnValue; }
/** * Update cell range * * @param string $pCellRange Cell range * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = PHPExcel_Cell::splitRange($pCellRange); for ($i = 0; $i < count($range); ++$i) { for ($j = 0; $j < count($range[$i]); ++$j) { $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } // Recreate range string return PHPExcel_Cell::buildRange($range); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
/** * Temporary method used by style supervisor. Will be removed * * @param string $pCell Cell (i.e. A1) * @throws Exception * @return PHPExcel_Worksheet */ public function setXSelectedCells($pCoordinate = 'A1:A1') { // Uppercase coordinate $pCoordinate = strtoupper($pCoordinate); if (strpos($pCoordinate, ':') !== false || strpos($pCoordinate, ',') !== false) { list($first, ) = PHPExcel_Cell::splitRange($pCoordinate); $this->_xActiveCell = $first[0]; } else { $this->_xActiveCell = $pCoordinate; } $this->_xSelectedCells = $pCoordinate; return $this; }
/** * Write Defined Name for PrintTitles * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet * @param int $pSheetId * @throws PHPExcel_Writer_Exception */ private function _writeDefinedNameForPrintArea(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pSheetId = 0) { // definedName for PrintArea if ($pSheet->getPageSetup()->isPrintAreaSet()) { $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', '_xlnm.Print_Area'); $objWriter->writeAttribute('localSheetId', $pSheetId); // Setting string $settingString = ''; // Print area $printArea = PHPExcel_Cell::splitRange($pSheet->getPageSetup()->getPrintArea()); $chunks = array(); foreach ($printArea as $printAreaRect) { $printAreaRect[0] = PHPExcel_Cell::absoluteReference($printAreaRect[0]); $printAreaRect[1] = PHPExcel_Cell::absoluteReference($printAreaRect[1]); $chunks[] = '\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . implode(':', $printAreaRect); } $objWriter->writeRawData(implode(',', $chunks)); $objWriter->endElement(); } }
/** * Update cell range * * @param string $pCellRange Cell range * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos(':', $pCellRange) !== false) { // Range $referenceA = ''; $referenceB = ''; list($referenceA, $referenceB) = PHPExcel_Cell::splitRange($pCellRange); return $this->_updateSingleCellReference($referenceA, $pBefore, $pNumCols, $pNumRows) . ':' . $this->_updateSingleCellReference($referenceB, $pBefore, $pNumCols, $pNumRows); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
private function getCellValue($sheet, $cellOrCol, $row = null, $fillMerged = false, &$mergedRanges, $format = 'Y.m.d H:i:s') { //column set by index if (is_numeric($cellOrCol)) { $cell = $sheet->getCellByColumnAndRow($cellOrCol, $row); } else { $lastChar = substr($cellOrCol, -1, 1); if (!is_numeric($lastChar)) { //column contains only letter, e.g. "A" $cellOrCol .= $row; } $cell = $sheet->getCell($cellOrCol); } /** * для смержанных ячеек ставим одинаковое значение из первой ячейки из диапазона * но делаем это только для вертикального смерживания */ if ($fillMerged) { foreach ($mergedRanges as $currMergedRange) { if ($cell->isInRange($currMergedRange)) { //проверяем авляется ли смерживание вертикальным list($a, $b) = explode(":", preg_replace("#\\d#", "", $currMergedRange)); if ($a == $b) { $currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange); $cell = $sheet->getCell($currMergedCellsArray[0][0]); break; } } } } //simple value $val = $cell->getValue(); //date // if(PHPExcel_Shared_Date::isDateTime($cell)) { // $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val)); // } //for incorrect formulas take old value if (substr($val, 0, 1) === '=' && strlen($val) > 1) { $val = $cell->getOldCalculatedValue(); } $val = str_replace("\n", "{{n}}", $val); //удаляем \n из значений, вернее меняем на {{n}}, чтобы для комментариев, востановить \n //если для полей с условиями есть условие для bg if ($this->_isBgConditionExists) { $bgcolor = $cell->getStyle()->getFill()->getStartColor()->getRGB(); if ($bgcolor != '000000' && $bgcolor != 'FFFFFF') { $val .= "{{{bg:" . $bgcolor . "}}}"; } //добавляем в конце информацию о цвете } //если для полей с условиями есть условие для bold if ($this->_isBoldConditionExists && $cell->getStyle()->getFont()->getBold()) { $val .= "{{{format:bold}}}"; //если bold добавляем в конец } //если для полей с условиями есть условие для italic if ($this->_isItalicConditionExists && $cell->getStyle()->getFont()->getItalic()) { $val .= "{{{format:italic}}}"; //если bold добавляем в конец } //если для полей с условиями есть условие для underline if ($this->_isUnderlineConditionExists && $cell->getStyle()->getFont()->getUnderline() != 'none') { $val .= "{{{format:underline}}}"; //если bold добавляем в конец } // return preg_replace("#[[:blank:]]+#u", " ", $val) . " "; //добавляем в конец пробел, для случаев, когда в колонке последнее значение \ // тут не удаляем пробелы так как при обработке, иногда бывает важно их учитывать return substr($val, -1) == "\\" ? $val . " " : $val; //добавляем в конец пробел, для случаев, когда в колонке последнее значение \ }
/** * Generate row * * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number * @return string * @throws Exception */ private function _generateRow(PHPExcel_Worksheet $pSheet, $pValues = null, $pRow = 0) { if (is_array($pValues)) { // Construct HTML $html = ''; // Sheet hashcode $sheetHash = $pSheet->getHashCode(); // Write row start if (!$this->_useInlineCss) { $html .= ' <tr class="row' . $pRow . '">' . "\r\n"; } else { $style = isset($this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' tr.row' . $pRow] : ''; $html .= ' <tr style="' . $style . '">' . "\r\n"; } // Write cells $colNum = 0; foreach ($pValues as $cell) { $cellData = ' '; $cssClass = ''; if (!$this->_useInlineCss) { $cssClass = 'column' . $colNum; } else { $cssClass = isset($this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum]) ? $this->_cssStyles['table.sheet' . $sheetHash . ' td.column' . $colNum] : ''; } $colSpan = 1; $rowSpan = 1; $writeCell = true; // Write cell // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop trough rich text elements $elements = $cell->getValue()->getRichTextElements(); foreach ($elements as $element) { // Rich text start? if ($element instanceof PHPExcel_RichText_Run) { $cellData .= '<span style="' . str_replace("\r\n", '', $this->_createCSSStyleFont($element->getFont())) . '">'; if ($element->getFont()->getSuperScript()) { $cellData .= '<sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '<sub>'; } } } // Convert UTF8 data to PCDATA $cellText = $element->getText(); $cellData .= htmlspecialchars($cellText); if ($element instanceof PHPExcel_RichText_Run) { if ($element->getFont()->getSuperScript()) { $cellData .= '</sup>'; } else { if ($element->getFont()->getSubScript()) { $cellData .= '</sub>'; } } $cellData .= '</span>'; } } } else { if ($this->_preCalculateFormulas) { $cellData = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } // Convert UTF8 data to PCDATA $cellData = htmlspecialchars($cellData); } // Check value if ($cellData == '') { $cellData = ' '; } // Extend CSS class? if (array_key_exists($cell->getCoordinate(), $pSheet->getStyles())) { if (!$this->_useInlineCss) { $cssClass .= ' style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex(); } else { $cssClass .= isset($this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()]) ? $this->_cssStyles['style' . $pSheet->getStyle($cell->getCoordinate())->getHashIndex()] : ''; } } } else { $cell = new PHPExcel_Cell(PHPExcel_Cell::stringFromColumnIndex($colNum), $pRow + 1, '', null, null); } // Hyperlink? if ($cell->hasHyperlink() && !$cell->getHyperlink()->isInternal()) { $cellData = '<a href="' . htmlspecialchars($cell->getHyperlink()->getUrl()) . '" title="' . htmlspecialchars($cell->getHyperlink()->getTooltip()) . '">' . $cellData . '</a>'; } // Column/rowspan foreach ($pSheet->getMergeCells() as $cells) { if ($cell->isInRange($cells)) { list($first, ) = PHPExcel_Cell::splitRange($cells); if ($first == $cell->getCoordinate()) { list($colSpan, $rowSpan) = PHPExcel_Cell::rangeDimension($cells); } else { $writeCell = false; } break; } } // Write if ($writeCell) { // Column start $html .= ' <td'; if (!$this->_useInlineCss) { $html .= ' class="' . $cssClass . '"'; } else { $html .= ' style="' . $cssClass . '"'; } if ($colSpan > 1) { $html .= ' colspan="' . $colSpan . '"'; } if ($rowSpan > 1) { $html .= ' rowspan="' . $rowSpan . '"'; } $html .= '>'; // Image? $html .= $this->_writeImageTagInCell($pSheet, $cell->getCoordinate()); // Cell data if ($this->_useInlineCss) { $html .= '<span style="' . $cssClass . '">'; } $html .= $cellData; if ($this->_useInlineCss) { $html .= '</span>'; } // Column end $html .= '</td>' . "\r\n"; } // Next column ++$colNum; } // Write row end $html .= ' </tr>' . "\r\n"; // Return return $html; } else { throw new Exception("Invalid parameters passed."); } }