/** * Save PHPExcel to file * * @param string $pFileName * @throws Exception */ public function save($pFilename = null) { // Fetch sheet $sheet = $this->_phpExcel->getSheet($this->_sheetIndex); // Open file $fileHandle = fopen($pFilename, 'w'); if ($fileHandle === false) { throw new Exception("Could not open file {$pFilename} for writing."); } // Get cell collection $cellCollection = $sheet->getCellCollection(); // Get column count $colCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); // Loop trough cells $currentRow = -1; $rowData = array(); foreach ($cellCollection as $cell) { if ($currentRow != $cell->getRow()) { // End previous row? if ($currentRow != -1) { $this->_writeLine($fileHandle, $rowData); } // Set current row $currentRow = $cell->getRow(); // Start a new row $rowData = array(); for ($i = 0; $i < $colCount; $i++) { $rowData[$i] = ''; } } // Copy cell $column = PHPExcel_Cell::columnIndexFromString($cell->getColumn()) - 1; if ($cell->getValue() instanceof PHPExcel_RichText) { $rowData[$column] = $cell->getValue()->getPlainText(); } else { if ($this->_preCalculateFormulas) { $rowData[$column] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } else { $rowData[$column] = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $sheet->getstyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()); } } } // End last row? if ($currentRow != -1) { $this->_writeLine($fileHandle, $rowData); } // Close file fclose($fileHandle); }
/** * 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); }
function getImportExcelData($data, $fields) { global $total_records, $cCharset, $columnIndex; foreach ($data->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); for ($row = 2; $row <= $highestRow; ++$row) { for ($col = 0; $col < $columnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); if (PHPExcel_Shared_Date::isDateTime($cell)) { $date_format = $cell->getParent()->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); $value = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $date_format); if (is_a($value, 'PHPExcel_RichText')) { $value = $value->getPlainText(); } if ($value) { $time = array(); if (strtotime($value)) { $value = strtotime($value); } else { $d_format = ""; for ($i = 0; $i < strlen($date_format); $i++) { $letter = substr(strtolower($date_format), $i, 1); if ($letter == "d" || $letter == "m" || $letter == "y") { if (strpos($d_format, $letter) === false) { $d_format .= $letter; } } } $value = strtotime(localdatetime2db($value, $d_format)); } // $value = PHPExcel_Shared_Date::ExcelToPHP($value); $time = localtime($value, true); $val = $time["tm_year"] + 1900 . "-" . ($time["tm_mon"] + 1) . "-" . $time["tm_mday"] . " " . $time["tm_hour"] . ":" . $time["tm_min"] . ":" . $time["tm_sec"]; } else { $val = NULL; } } else { $error_handler = set_error_handler("empty_error_handler"); $val = PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8'); if (is_a($val, 'PHPExcel_RichText')) { $val = $val->getPlainText(); } if ($error_handler) { set_error_handler($error_handler); } } $arr[$fields[$col]] = $val; } $ret = InsertRecord($arr, $row - 2); $total_records++; } break; } }
/** * Generate row * * @param PHPExcel_Worksheet $pSheet PHPExcel_Worksheet * @param array $pValues Array containing cells in a row * @param int $pRow Row number (0-based) * @return string * @throws PHPExcel_Writer_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); // DomPDF and breaks if ($this->_isPdf && count($pSheet->getBreaks()) > 0) { $breaks = $pSheet->getBreaks(); // check if a break is needed before this row if (isset($breaks['A' . $pRow])) { // close table: </table> $html .= $this->_generateTableFooter(); // insert page break $html .= '<div style="page-break-before:always" />'; // open table again: <table> + <col> etc. $html .= $this->_generateTableHeader($pSheet); } } // Write row start if (!$this->_useInlineCss) { $html .= ' <tr class="row' . $pRow . '">' . PHP_EOL; } 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 . '">' . PHP_EOL; } // Write cells $colNum = 0; foreach ($pValues as $cell) { $coordinate = PHPExcel_Cell::stringFromColumnIndex($colNum) . ($pRow + 1); 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; // initialize $cellData = ' '; // PHPExcel_Cell if ($cell instanceof PHPExcel_Cell) { $cellData = ''; if (is_null($cell->getParent())) { $cell->attach($pSheet); } // Value if ($cell->getValue() instanceof PHPExcel_RichText) { // Loop through 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(), array($this, 'formatColor')); } else { $cellData = PHPExcel_Style_NumberFormat::ToFormattedString($cell->getValue(), $pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(), array($this, 'formatColor')); } $cellData = htmlspecialchars($cellData); if ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSuperScript()) { $cellData = '<sup>' . $cellData . '</sup>'; } elseif ($pSheet->getParent()->getCellXfByIndex($cell->getXfIndex())->getFont()->getSubScript()) { $cellData = '<sub>' . $cellData . '</sub>'; } } // Converts the cell content so that spaces occuring at beginning of each new line are replaced by // Example: " Hello\n to the world" is converted to " Hello\n to the world" $cellData = preg_replace("/(?m)(?:^|\\G) /", ' ', $cellData); // convert newline "\n" to '<br>' $cellData = nl2br($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']; } } } // Hyperlink? if ($pSheet->hyperlinkExists($coordinate) && !$pSheet->getHyperlink($coordinate)->isInternal()) { $cellData = '<a href="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getUrl()) . '" title="' . htmlspecialchars($pSheet->getHyperlink($coordinate)->getTooltip()) . '">' . $cellData . '</a>'; } // Should the cell be written or is it swallowed by a rowspan or colspan? $writeCell = !(isset($this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]) && $this->_isSpannedCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]); // Colspan and Rowspan $colspan = 1; $rowspan = 1; if (isset($this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum])) { $spans = $this->_isBaseCell[$pSheet->getParent()->getIndex($pSheet)][$pRow + 1][$colNum]; $rowSpan = $spans['rowspan']; $colSpan = $spans['colspan']; // Also apply style from last cell in merge to fix borders - // relies on !important for non-none border declarations in _createCSSStyleBorder $endCellCoord = PHPExcel_Cell::stringFromColumnIndex($colNum + $colSpan - 1) . ($pRow + $rowSpan); $cssClass .= ' style' . $pSheet->getCell($endCellCoord)->getXfIndex(); } // 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; $i = $colNum - 1; $e = $colNum + $colSpan - 1; while ($i++ < $e) { 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->_writeImageInCell($pSheet, $coordinate); // Chart? if ($this->_includeCharts) { $html .= $this->_writeChartInCell($pSheet, $coordinate); } // Cell data $html .= $cellData; // Column end $html .= '</td>' . PHP_EOL; } // Next column ++$colNum; } // Write row end $html .= ' </tr>' . PHP_EOL; // Return return $html; } else { throw new PHPExcel_Writer_Exception("Invalid parameters passed."); } }
/** * 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."); } }
/** * 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."); } }
if ($worksheetTitle == "31B") { break; } $highestColumn = $worksheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $detail_jadwal; for ($i = 5; $i < 50; $i = $i + 11) { echo "<BR/>"; $masterjadwal = new MJadwal(); $masterjadwal->KELAS = $worksheetTitle; $masterjadwal->WEEK = $worksheet->getCellByColumnAndRow(1, 1)->getValue(); $masterjadwal->TA = $worksheet->getCellByColumnAndRow(1, 2)->getValue(); $masterjadwal->ID_KUR = $worksheet->getCellByColumnAndRow(1, 3)->getValue(); echo $masterjadwal->KELAS . "<BR>"; $masterjadwal->TANGGAL = PHPExcel_Style_NumberFormat::ToFormattedString($worksheet->getCellByColumnAndRow(1, $i)->getValue(), "YYYY-MM-DD"); echo $masterjadwal->TANGGAL; $masterjadwal->save(); for ($j = $i + 2; $j < $i + 10; $j++) { for ($col = 0; $col < $highestColumnIndex; ++$col) { $detail_jadwal[$col] = $worksheet->getCellByColumnAndRow($col, $j)->getValue(); // echo $worksheet->getCellByColumnAndRow($col, $j)->getValue(); // echo "<BR>"; } // $this->cekMaster($masterjadwal); $this->createJadwal($detail_jadwal, $masterjadwal); } } } ?>
/** * Get no more the 100 rows of the file's data to display the file's preview. * It could augment the fieldsData array * It requires the PHPExcel plugin * @param PHPExcel fileHandle * @param &Array fieldsData The corresponding import fields data * @return Array */ function getPreviewDataFromExcel($fileHandle, &$fieldsData) { global $locale_info; $previewData = array(); $remainNumOfPreviewRows = 100; foreach ($fileHandle->getWorksheetIterator() as $worksheet) { if ($remainNumOfPreviewRows <= 0) { break; } // get the number of rows for the current worksheet $highestRow = $worksheet->getHighestRow(); if ($highestRow > $remainNumOfPreviewRows) { $highestRow = $remainNumOfPreviewRows; } $remainNumOfPreviewRows -= $highestRow; // get a litteral index of the 'highest' column (e.g. 'K') $highestColumn = $worksheet->getHighestColumn(); // get an index number of the 'highest' column (e.g. 11) $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // start traversing rows from the first one that contains columns' names for ($row = 1; $row <= $highestRow; $row++) { $rowData = array(); for ($col = 0; $col < $highestColumnIndex; $col++) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $cellValue = $cell->getValue(); if ($row > 1) { $columnMatched = isset($fieldsData[$col]); if (PHPExcel_Shared_Date::isDateTime($cell)) { $cellDateFormat = $fileHandle->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->getFormatCode(); $cellTextValue = PHPExcel_Style_NumberFormat::ToFormattedString($cellValue, $cellDateFormat); $cellValue = getTimeStamp($cellTextValue, $cellDateFormat); if (!$columnMatched) { $fieldsData[$col] = array(); } $fieldsData[$col]["dateTimeType"] = true; $fieldsData[$col]["requireFormatting"] = true; } else { if ($columnMatched && $fieldsData[$col]["dateTimeType"] && !strlen($dateFormat)) { $dateFormat = ImportPage::extractDateFormat($cellValue); } } } $rowData[] = $cellValue; } $tableData[] = $rowData; } } $previewData["tableData"] = $tableData; if (ImportPage::hasDateTimeFields($fieldsData)) { $previewData["dateFormat"] = !strlen($dateFormat) ? $locale_info["LOCALE_SSHORTDATE"] : $dateFormat; } return $previewData; }
// e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $highestRow = $worksheet->getHighestRow(); // e.g. 10 $kehadiran; for ($i = 2; $i <= $highestRow; $i++) { echo "<BR/>"; // echo "Tanggal : " . PHPExcel_Style_NumberFormat::ToFormattedString($worksheet->getCellByColumnAndRow(1, $i)->getValue(), "YYYY-MM-DD"); //echo $masterjadwal->KELAS . "<BR>"; // $masterjadwal->TANGGAL = PHPExcel_Style_NumberFormat::ToFormattedString($worksheet->getCellByColumnAndRow(1, $i)->getValue(), "YYYY-MM-DD"); //echo $masterjadwal->TANGGAL; for ($col = 0; $col < $highestColumnIndex; ++$col) { // $detail_jadwal[$col] = $worksheet->getCellByColumnAndRow($col, $j)->getValue(); if ($col == 3) { echo PHPExcel_Style_NumberFormat::ToFormattedString($worksheet->getCellByColumnAndRow($col, $i)->getValue(), 'YYYY-MM-DD'); $kehadiran[$col] = PHPExcel_Style_NumberFormat::ToFormattedString($worksheet->getCellByColumnAndRow($col, $i)->getValue(), 'YYYY-MM-DD'); } else { if ($col == 4) { echo PHPExcel_Style_NumberFormat::toFormattedString($worksheet->getCellByColumnAndRow($col, $i)->getCalculatedValue(), 'hh:mm:ss'); $kehadiran[$col] = PHPExcel_Style_NumberFormat::toFormattedString($worksheet->getCellByColumnAndRow($col, $i)->getCalculatedValue(), 'hh:mm:ss'); } else { if ($col == 1 || $col == 5) { echo $worksheet->getCellByColumnAndRow($col, $i)->getValue(); $kehadiran[$col] = $worksheet->getCellByColumnAndRow($col, $i)->getValue(); } } } // echo "<BR>"; } // $this->cekMaster($masterjadwal); $this->cekDaftarhadir($kehadiran);
function getImportExcelData($data, $fields, &$error_message, &$goodlines, $keys, $keys_present, $strOriginalTableName, $pageObject, $cipherer, $autoinc, &$total_records) { global $cCharset; foreach ($data->getWorksheetIterator() as $worksheet) { $highestRow = $worksheet->getHighestRow(); for ($row = 2; $row <= $highestRow; ++ $row) { for ($col = 0; $col < count($fields); ++ $col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); if (PHPExcel_Shared_Date::isDateTime($cell)) { $date_format=$cell->getParent()->getParent()->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode(); $value=PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(),$date_format); if(is_a($value, 'PHPExcel_RichText')) $value = $value->getPlainText(); if($value) { $time=array(); if(strtotime($value)) $value=strtotime($value); else { $d_format=""; for($i=0;$i<strlen($date_format);$i++) { $letter=substr(strtolower($date_format),$i,1); if($letter=="d" || $letter=="m" || $letter=="y") { if(strpos($d_format,$letter)===false) $d_format.=$letter; } } $value=strtotime(localdatetime2db($value,$d_format)); } // $value = PHPExcel_Shared_Date::ExcelToPHP($value); $time=localtime($value,true); $val=($time["tm_year"]+1900)."-".($time["tm_mon"]+1)."-".$time["tm_mday"]." ".$time["tm_hour"].":".$time["tm_min"].":".$time["tm_sec"]; } else $val=NULL; } else { $error_handler=set_error_handler("empty_error_handler"); $val=PHPExcel_Shared_String::ConvertEncoding($cell->getValue(), $cCharset, 'UTF-8'); if(is_a($val, 'PHPExcel_RichText')) $val = $val->getPlainText(); if($error_handler) set_error_handler($error_handler); } preg_match('/^="=(.*|n*)"$/i', $val, $matches); if (array_key_exists(1, $matches)) { $val = '='.$matches[1]; } $arr[$fields[$col]]= $val; } $ret = InsertRecord($arr, $row-2, $error_message, $goodlines, $keys, $keys_present, $strOriginalTableName, $pageObject, $cipherer, $autoinc); $total_records++; } break; } }
/** * 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."); } }