/**
* 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.");
}
}
