public function output()
{
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
$objSheet = $objPHPExcel->setActiveSheetIndex(0);
$col = 0;
$row = 1;
if (isset($this->header)) {
foreach ($this->header as $v) {
$cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
$objSheet->setCellValue($cell, $v);
$col++;
}
$row++;
$col = 0;
}
foreach ($this->content as $rowValue) {
foreach ($rowValue as $_v) {
$cell = \PHPExcel_Cell::stringFromColumnIndex($col) . $row;
$objSheet->setCellValue($cell, $_v);
$col++;
}
$row++;
$col = 0;
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($this->title);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$this->browserExport($this->type, $this->filename);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $this->type);
$objWriter->save('php://output');
}
public static function exportXlsx($data, $keys)
{
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Roadiz CMS")->setLastModifiedBy("Roadiz CMS")->setCategory("");
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '8MB'];
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel->setActiveSheetIndex(0);
foreach ($keys as $key => $value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value);
}
foreach ($data as $key => $answer) {
foreach ($answer as $k => $value) {
$columnAlpha = \PHPExcel_Cell::stringFromColumnIndex($k);
if ($value instanceof \DateTime) {
$value = \PHPExcel_Shared_Date::PHPToExcel($value);
$objPHPExcel->getActiveSheet()->getStyle($columnAlpha . (2 + $key))->getNumberFormat()->setFormatCode('dd.mm.yyyy hh:MM:ss');
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($k, 2 + $key, $value);
}
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_start();
$objWriter->save('php://output');
$return = ob_get_clean();
return $return;
}
public function ProcessFileContent()
{
$objPHPExcel = PHPExcel_IOFactory::load($this->file);
// Format is as follows:
// (gray bg) [ <description of data> ], <relation1>, <relationN>
// <srcConcept>, <tgtConcept1>, <tgtConceptN>
// <srcAtomA>, <tgtAtom1A>, <tgtAtomNA>
// <srcAtomB>, <tgtAtom1B>, <tgtAtomNB>
// <srcAtomC>, <tgtAtom1C>, <tgtAtomNC>
// Output is function call:
// InsPair($relation,$srcConcept,$srcAtom,$tgtConcept,$tgtAtom)
// Loop over all worksheets
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
// Loop through all rows
$highestrow = $worksheet->getHighestRow();
$highestcolumn = $worksheet->getHighestColumn();
$highestcolumnnr = PHPExcel_Cell::columnIndexFromString($highestcolumn);
$row = 1;
// Go to the first row where a table starts.
for ($i = $row; $i <= $highestrow; $i++) {
$row = $i;
if (substr($worksheet->getCell('A' . $row)->getValue(), 0, 1) === '[') {
break;
}
}
// We are now at the beginning of a table or at the end of the file.
$line = array();
// Line is a buffer of one or more related (subsequent) excel rows
while ($row <= $highestrow) {
// Read this line as an array of values
$values = array();
// values is a buffer containing the cells in a single excel row
for ($columnnr = 0; $columnnr < $highestcolumnnr; $columnnr++) {
$columnletter = PHPExcel_Cell::stringFromColumnIndex($columnnr);
$cell = $worksheet->getCell($columnletter . $row);
$cellvalue = (string) $cell->getCalculatedValue();
// overwrite $cellvalue in case of datetime
// the @ is a php indicator for a unix timestamp (http://php.net/manual/en/datetime.formats.compound.php), later used for typeConversion
if (PHPExcel_Shared_Date::isDateTime($cell) && !empty($cellvalue)) {
$cellvalue = '@' . (string) PHPExcel_Shared_Date::ExcelToPHP($cellvalue);
}
$values[] = $cellvalue;
}
$line[] = $values;
// add line (array of values) to the line buffer
$row++;
// Is this relation table done? Then we parse the current values into function calls and reset it
$firstCellInRow = (string) $worksheet->getCell('A' . $row)->getCalculatedValue();
if (substr($firstCellInRow, 0, 1) === '[') {
// Relation table is complete, so it can be processed.
$this->ParseLines($line);
$line = array();
}
}
// Last relation table remains to be processed.
$this->ParseLines($line);
$line = array();
}
}
/**
* Loads PHPExcel from file
*
* @param string $pFilename
* @throws Exception
*/
public function load($pFilename)
{
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
// Initialisations
$excel = new PHPExcel;
$excel->removeSheetByIndex(0);
if (!$this->_readDataOnly) {
$excel->removeCellStyleXfByIndex(0); // remove the default style
$excel->removeCellXfByIndex(0); // remove the default style
}
$zip = new ZipArchive;
$zip->open($pFilename);
$rels = simplexml_load_string($this->_getFromZipArchive($zip, "_rels/.rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($rels->Relationship as $rel) {
switch ($rel["Type"]) {
case "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties":
$xmlCore = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}"));
if ($xmlCore) {
$xmlCore->registerXPathNamespace("dc", "http://purl.org/dc/elements/1.1/");
$xmlCore->registerXPathNamespace("dcterms", "http://purl.org/dc/terms/");
$xmlCore->registerXPathNamespace("cp", "http://schemas.openxmlformats.org/package/2006/metadata/core-properties");
$docProps = $excel->getProperties();
$docProps->setCreator((string) self::array_item($xmlCore->xpath("dc:creator")));
$docProps->setLastModifiedBy((string) self::array_item($xmlCore->xpath("cp:lastModifiedBy")));
$docProps->setCreated(strtotime(self::array_item($xmlCore->xpath("dcterms:created")))); //! respect xsi:type
$docProps->setModified(strtotime(self::array_item($xmlCore->xpath("dcterms:modified")))); //! respect xsi:type
$docProps->setTitle((string) self::array_item($xmlCore->xpath("dc:title")));
$docProps->setDescription((string) self::array_item($xmlCore->xpath("dc:description")));
$docProps->setSubject((string) self::array_item($xmlCore->xpath("dc:subject")));
$docProps->setKeywords((string) self::array_item($xmlCore->xpath("cp:keywords")));
$docProps->setCategory((string) self::array_item($xmlCore->xpath("cp:category")));
}
break;
case "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument":
$dir = dirname($rel["Target"]);
$relsWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/_rels/" . basename($rel["Target"]) . ".rels")); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$relsWorkbook->registerXPathNamespace("rel", "http://schemas.openxmlformats.org/package/2006/relationships");
$sharedStrings = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings']"));
$xmlStrings = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
if (isset($xmlStrings) && isset($xmlStrings->si)) {
foreach ($xmlStrings->si as $val) {
if (isset($val->t)) {
$sharedStrings[] = PHPExcel_Shared_String::ControlCharacterOOXML2PHP( (string) $val->t );
} elseif (isset($val->r)) {
$sharedStrings[] = $this->_parseRichText($val);
}
}
}
$worksheets = array();
foreach ($relsWorkbook->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet") {
$worksheets[(string) $ele["Id"]] = $ele["Target"];
}
}
$styles = array();
$cellStyles = array();
$xpath = self::array_item($relsWorkbook->xpath("rel:Relationship[@Type='http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles']"));
$xmlStyles = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$xpath[Target]")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$numFmts = null;
if ($xmlStyles && $xmlStyles->numFmts[0]) {
$numFmts = $xmlStyles->numFmts[0];
}
if (isset($numFmts) && !is_null($numFmts)) {
$numFmts->registerXPathNamespace("sml", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
}
if (!$this->_readDataOnly && $xmlStyles) {
foreach ($xmlStyles->cellXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($xf["numFmtId"]) {
if (isset($numFmts)) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
}
}
if ((int)$xf["numFmtId"] < 164) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
}
}
//$numFmt = str_replace('mm', 'i', $numFmt);
//$numFmt = str_replace('h', 'H', $numFmt);
$style = (object) array(
"numFmt" => $numFmt,
"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
"alignment" => $xf->alignment,
"protection" => $xf->protection,
);
$styles[] = $style;
// add style to cellXf collection
$objStyle = new PHPExcel_Style;
$this->_readStyle($objStyle, $style);
$excel->addCellXf($objStyle);
}
foreach ($xmlStyles->cellStyleXfs->xf as $xf) {
$numFmt = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
if ($numFmts && $xf["numFmtId"]) {
$tmpNumFmt = self::array_item($numFmts->xpath("sml:numFmt[@numFmtId=$xf[numFmtId]]"));
if (isset($tmpNumFmt["formatCode"])) {
$numFmt = (string) $tmpNumFmt["formatCode"];
} else if ((int)$xf["numFmtId"] < 165) {
$numFmt = PHPExcel_Style_NumberFormat::builtInFormatCode((int)$xf["numFmtId"]);
}
}
$cellStyle = (object) array(
"numFmt" => $numFmt,
"font" => $xmlStyles->fonts->font[intval($xf["fontId"])],
"fill" => $xmlStyles->fills->fill[intval($xf["fillId"])],
"border" => $xmlStyles->borders->border[intval($xf["borderId"])],
"alignment" => $xf->alignment,
"protection" => $xf->protection,
);
$cellStyles[] = $cellStyle;
// add style to cellStyleXf collection
$objStyle = new PHPExcel_Style;
$this->_readStyle($objStyle, $cellStyle);
$excel->addCellStyleXf($objStyle);
}
}
$dxfs = array();
if (!$this->_readDataOnly && $xmlStyles) {
if ($xmlStyles->dxfs) {
foreach ($xmlStyles->dxfs->dxf as $dxf) {
$style = new PHPExcel_Style;
$this->_readStyle($style, $dxf);
$dxfs[] = $style;
}
}
if ($xmlStyles->cellStyles)
{
foreach ($xmlStyles->cellStyles->cellStyle as $cellStyle) {
if (intval($cellStyle['builtinId']) == 0) {
if (isset($cellStyles[intval($cellStyle['xfId'])])) {
// Set default style
$style = new PHPExcel_Style;
$this->_readStyle($style, $cellStyles[intval($cellStyle['xfId'])]);
// normal style, currently not using it for anything
}
}
}
}
}
$xmlWorkbook = simplexml_load_string($this->_getFromZipArchive($zip, "{$rel['Target']}")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
// Set base date
if ($xmlWorkbook->workbookPr) {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900);
if (isset($xmlWorkbook->workbookPr['date1904'])) {
$date1904 = (string)$xmlWorkbook->workbookPr['date1904'];
if ($date1904 == "true" || $date1904 == "1") {
PHPExcel_Shared_Date::setExcelCalendar(PHPExcel_Shared_Date::CALENDAR_MAC_1904);
}
}
}
$sheetId = 0; // keep track of new sheet id in final workbook
$oldSheetId = -1; // keep track of old sheet id in final workbook
$countSkippedSheets = 0; // keep track of number of skipped sheets
$mapSheetId = array(); // mapping of sheet ids from old to new
if ($xmlWorkbook->sheets)
{
foreach ($xmlWorkbook->sheets->sheet as $eleSheet) {
++$oldSheetId;
// Check if sheet should be skipped
if (isset($this->_loadSheetsOnly) && !in_array((string) $eleSheet["name"], $this->_loadSheetsOnly)) {
++$countSkippedSheets;
$mapSheetId[$oldSheetId] = null;
continue;
}
// Map old sheet id in original workbook to new sheet id.
// They will differ if loadSheetsOnly() is being used
$mapSheetId[$oldSheetId] = $oldSheetId - $countSkippedSheets;
// Load sheet
$docSheet = $excel->createSheet();
$docSheet->setTitle((string) $eleSheet["name"]);
$fileWorksheet = $worksheets[(string) self::array_item($eleSheet->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$xmlSheet = simplexml_load_string($this->_getFromZipArchive($zip, "$dir/$fileWorksheet")); //~ http://schemas.openxmlformats.org/spreadsheetml/2006/main");
$sharedFormulas = array();
if (isset($eleSheet["state"]) && (string) $eleSheet["state"] != '') {
$docSheet->setSheetState( (string) $eleSheet["state"] );
}
if (isset($xmlSheet->sheetViews) && isset($xmlSheet->sheetViews->sheetView)) {
if (isset($xmlSheet->sheetViews->sheetView['zoomScale'])) {
$docSheet->getSheetView()->setZoomScale( intval($xmlSheet->sheetViews->sheetView['zoomScale']) );
}
if (isset($xmlSheet->sheetViews->sheetView['zoomScaleNormal'])) {
$docSheet->getSheetView()->setZoomScaleNormal( intval($xmlSheet->sheetViews->sheetView['zoomScaleNormal']) );
}
if (isset($xmlSheet->sheetViews->sheetView['showGridLines'])) {
$docSheet->setShowGridLines((string)$xmlSheet->sheetViews->sheetView['showGridLines'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['showRowColHeaders'])) {
$docSheet->setShowRowColHeaders((string)$xmlSheet->sheetViews->sheetView['showRowColHeaders'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView['rightToLeft'])) {
$docSheet->setRightToLeft((string)$xmlSheet->sheetViews->sheetView['rightToLeft'] ? true : false);
}
if (isset($xmlSheet->sheetViews->sheetView->pane)) {
if (isset($xmlSheet->sheetViews->sheetView->pane['topLeftCell'])) {
$docSheet->freezePane( (string)$xmlSheet->sheetViews->sheetView->pane['topLeftCell'] );
} else {
$xSplit = 0;
$ySplit = 0;
if (isset($xmlSheet->sheetViews->sheetView->pane['xSplit'])) {
$xSplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['xSplit']);
}
if (isset($xmlSheet->sheetViews->sheetView->pane['ySplit'])) {
$ySplit = 1 + intval($xmlSheet->sheetViews->sheetView->pane['ySplit']);
}
$docSheet->freezePaneByColumnAndRow($xSplit, $ySplit);
}
}
if (isset($xmlSheet->sheetViews->sheetView->selection)) {
if (isset($xmlSheet->sheetViews->sheetView->selection['sqref'])) {
$sqref = (string)$xmlSheet->sheetViews->sheetView->selection['sqref'];
$sqref = explode(' ', $sqref);
$sqref = $sqref[0];
$docSheet->setSelectedCells($sqref);
}
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->tabColor)) {
if (isset($xmlSheet->sheetPr->tabColor['rgb'])) {
$docSheet->getTabColor()->setARGB( (string)$xmlSheet->sheetPr->tabColor['rgb'] );
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->outlinePr)) {
if (isset($xmlSheet->sheetPr->outlinePr['summaryRight']) && $xmlSheet->sheetPr->outlinePr['summaryRight'] == false) {
$docSheet->setShowSummaryRight(false);
} else {
$docSheet->setShowSummaryRight(true);
}
if (isset($xmlSheet->sheetPr->outlinePr['summaryBelow']) && $xmlSheet->sheetPr->outlinePr['summaryBelow'] == false) {
$docSheet->setShowSummaryBelow(false);
} else {
$docSheet->setShowSummaryBelow(true);
}
}
if (isset($xmlSheet->sheetPr) && isset($xmlSheet->sheetPr->pageSetUpPr)) {
if (isset($xmlSheet->sheetPr->pageSetUpPr['fitToPage']) && $xmlSheet->sheetPr->pageSetUpPr['fitToPage'] == false) {
$docSheet->getPageSetup()->setFitToPage(false);
} else {
$docSheet->getPageSetup()->setFitToPage(true);
}
}
if (isset($xmlSheet->sheetFormatPr)) {
if (isset($xmlSheet->sheetFormatPr['customHeight']) && ((string)$xmlSheet->sheetFormatPr['customHeight'] == '1' || strtolower((string)$xmlSheet->sheetFormatPr['customHeight']) == 'true') && isset($xmlSheet->sheetFormatPr['defaultRowHeight'])) {
$docSheet->getDefaultRowDimension()->setRowHeight( (float)$xmlSheet->sheetFormatPr['defaultRowHeight'] );
}
if (isset($xmlSheet->sheetFormatPr['defaultColWidth'])) {
$docSheet->getDefaultColumnDimension()->setWidth( (float)$xmlSheet->sheetFormatPr['defaultColWidth'] );
}
}
if (isset($xmlSheet->cols) && !$this->_readDataOnly) {
foreach ($xmlSheet->cols->col as $col) {
for ($i = intval($col["min"]) - 1; $i < intval($col["max"]); ++$i) {
if ($col["style"] && !$this->_readDataOnly) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setXfIndex(intval($col["style"]));
}
if ($col["bestFit"]) {
//$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setAutoSize(true);
}
if ($col["hidden"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setVisible(false);
}
if ($col["collapsed"]) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setCollapsed(true);
}
if ($col["outlineLevel"] > 0) {
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setOutlineLevel(intval($col["outlineLevel"]));
}
$docSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($i))->setWidth(floatval($col["width"]));
if (intval($col["max"]) == 16384) {
break;
}
}
}
}
if (isset($xmlSheet->printOptions) && !$this->_readDataOnly) {
if ($xmlSheet->printOptions['gridLinesSet'] == 'true' && $xmlSheet->printOptions['gridLinesSet'] == '1') {
$docSheet->setShowGridlines(true);
}
if ($xmlSheet->printOptions['gridLines'] == 'true' || $xmlSheet->printOptions['gridLines'] == '1') {
$docSheet->setPrintGridlines(true);
}
if ($xmlSheet->printOptions['horizontalCentered']) {
$docSheet->getPageSetup()->setHorizontalCentered(true);
}
if ($xmlSheet->printOptions['verticalCentered']) {
$docSheet->getPageSetup()->setVerticalCentered(true);
}
}
if ($xmlSheet && $xmlSheet->sheetData && $xmlSheet->sheetData->row) {
foreach ($xmlSheet->sheetData->row as $row) {
if ($row["ht"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setRowHeight(floatval($row["ht"]));
}
if ($row["hidden"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setVisible(false);
}
if ($row["collapsed"]) {
$docSheet->getRowDimension(intval($row["r"]))->setCollapsed(true);
}
if ($row["outlineLevel"] > 0) {
$docSheet->getRowDimension(intval($row["r"]))->setOutlineLevel(intval($row["outlineLevel"]));
}
if ($row["s"] && !$this->_readDataOnly) {
$docSheet->getRowDimension(intval($row["r"]))->setXfIndex(intval($row["s"]));
}
foreach ($row->c as $c) {
$r = (string) $c["r"];
$cellDataType = (string) $c["t"];
$value = null;
$calculatedValue = null;
// Read cell?
if (!is_null($this->getReadFilter())) {
$coordinates = PHPExcel_Cell::coordinateFromString($r);
if (!$this->getReadFilter()->readCell($coordinates[0], $coordinates[1], $docSheet->getTitle())) {
continue;
}
}
// echo '<b>Reading cell '.$coordinates[0].$coordinates[1].'</b><br />';
// print_r($c);
// echo '<br />';
// echo 'Cell Data Type is '.$cellDataType.': ';
//
// Read cell!
switch ($cellDataType) {
case "s":
// echo 'String<br />';
if ((string)$c->v != '') {
$value = $sharedStrings[intval($c->v)];
if ($value instanceof PHPExcel_RichText) {
$value = clone $value;
}
} else {
$value = '';
}
break;
case "b":
// echo 'Boolean<br />';
if (!isset($c->f)) {
$value = $this->_castToBool($c);
} else {
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToBool');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
case "inlineStr":
// echo 'Inline String<br />';
$value = $this->_parseRichText($c->is);
break;
case "e":
// echo 'Error<br />';
if (!isset($c->f)) {
$value = $this->_castToError($c);
} else {
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToError');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
default:
// echo 'Default<br />';
if (!isset($c->f)) {
// echo 'Not a Formula<br />';
$value = $this->_castToString($c);
} else {
// echo 'Treat as Formula<br />';
// Formula
$this->_castToFormula($c,$r,$cellDataType,$value,$calculatedValue,$sharedFormulas,'_castToString');
// echo '$calculatedValue = '.$calculatedValue.'<br />';
}
break;
}
// echo 'Value is '.$value.'<br />';
// Check for numeric values
if (is_numeric($value) && $cellDataType != 's') {
if ($value == (int)$value) $value = (int)$value;
elseif ($value == (float)$value) $value = (float)$value;
elseif ($value == (double)$value) $value = (double)$value;
}
// Rich text?
if ($value instanceof PHPExcel_RichText && $this->_readDataOnly) {
$value = $value->getPlainText();
}
$cell = $docSheet->getCell($r);
// Assign value
if ($cellDataType != '') {
$cell->setValueExplicit($value, $cellDataType);
} else {
$cell->setValue($value);
}
if (!is_null($calculatedValue)) {
$cell->setCalculatedValue($calculatedValue);
}
// Style information?
if ($c["s"] && !$this->_readDataOnly) {
// no style index means 0, it seems
$cell->setXfIndex(isset($styles[intval($c["s"])]) ?
intval($c["s"]) : 0);
}
}
}
}
$conditionals = array();
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->conditionalFormatting) {
foreach ($xmlSheet->conditionalFormatting as $conditional) {
foreach ($conditional->cfRule as $cfRule) {
if (
(
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_NONE ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CELLIS ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT ||
(string)$cfRule["type"] == PHPExcel_Style_Conditional::CONDITION_EXPRESSION
) && isset($dxfs[intval($cfRule["dxfId"])])
) {
$conditionals[(string) $conditional["sqref"]][intval($cfRule["priority"])] = $cfRule;
}
}
}
foreach ($conditionals as $ref => $cfRules) {
ksort($cfRules);
$conditionalStyles = array();
foreach ($cfRules as $cfRule) {
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType((string)$cfRule["type"]);
$objConditional->setOperatorType((string)$cfRule["operator"]);
if ((string)$cfRule["text"] != '') {
$objConditional->setText((string)$cfRule["text"]);
}
if (count($cfRule->formula) > 1) {
foreach ($cfRule->formula as $formula) {
$objConditional->addCondition((string)$formula);
}
} else {
$objConditional->addCondition((string)$cfRule->formula);
}
$objConditional->setStyle(clone $dxfs[intval($cfRule["dxfId"])]);
$conditionalStyles[] = $objConditional;
}
// Extract all cell references in $ref
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($ref);
foreach ($aReferences as $reference) {
$docSheet->getStyle($reference)->setConditionalStyles($conditionalStyles);
}
}
}
$aKeys = array("sheet", "objects", "scenarios", "formatCells", "formatColumns", "formatRows", "insertColumns", "insertRows", "insertHyperlinks", "deleteColumns", "deleteRows", "selectLockedCells", "sort", "autoFilter", "pivotTables", "selectUnlockedCells");
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
foreach ($aKeys as $key) {
$method = "set" . ucfirst($key);
$docSheet->getProtection()->$method($xmlSheet->sheetProtection[$key] == "true");
}
}
if (!$this->_readDataOnly && $xmlSheet && $xmlSheet->sheetProtection) {
$docSheet->getProtection()->setPassword((string) $xmlSheet->sheetProtection["password"], true);
if ($xmlSheet->protectedRanges->protectedRange) {
foreach ($xmlSheet->protectedRanges->protectedRange as $protectedRange) {
$docSheet->protectCells((string) $protectedRange["sqref"], (string) $protectedRange["password"], true);
}
}
}
if ($xmlSheet && $xmlSheet->autoFilter && !$this->_readDataOnly) {
$docSheet->setAutoFilter((string) $xmlSheet->autoFilter["ref"]);
}
if ($xmlSheet && $xmlSheet->mergeCells && $xmlSheet->mergeCells->mergeCell && !$this->_readDataOnly) {
foreach ($xmlSheet->mergeCells->mergeCell as $mergeCell) {
$docSheet->mergeCells((string) $mergeCell["ref"]);
}
}
if ($xmlSheet && $xmlSheet->pageMargins && !$this->_readDataOnly) {
$docPageMargins = $docSheet->getPageMargins();
$docPageMargins->setLeft(floatval($xmlSheet->pageMargins["left"]));
$docPageMargins->setRight(floatval($xmlSheet->pageMargins["right"]));
$docPageMargins->setTop(floatval($xmlSheet->pageMargins["top"]));
$docPageMargins->setBottom(floatval($xmlSheet->pageMargins["bottom"]));
$docPageMargins->setHeader(floatval($xmlSheet->pageMargins["header"]));
$docPageMargins->setFooter(floatval($xmlSheet->pageMargins["footer"]));
}
if ($xmlSheet && $xmlSheet->pageSetup && !$this->_readDataOnly) {
$docPageSetup = $docSheet->getPageSetup();
if (isset($xmlSheet->pageSetup["orientation"])) {
$docPageSetup->setOrientation((string) $xmlSheet->pageSetup["orientation"]);
}
if (isset($xmlSheet->pageSetup["paperSize"])) {
$docPageSetup->setPaperSize(intval($xmlSheet->pageSetup["paperSize"]));
}
if (isset($xmlSheet->pageSetup["scale"])) {
$docPageSetup->setScale(intval($xmlSheet->pageSetup["scale"]), false);
}
if (isset($xmlSheet->pageSetup["fitToHeight"]) && intval($xmlSheet->pageSetup["fitToHeight"]) >= 0) {
$docPageSetup->setFitToHeight(intval($xmlSheet->pageSetup["fitToHeight"]), false);
}
if (isset($xmlSheet->pageSetup["fitToWidth"]) && intval($xmlSheet->pageSetup["fitToWidth"]) >= 0) {
$docPageSetup->setFitToWidth(intval($xmlSheet->pageSetup["fitToWidth"]), false);
}
if (isset($xmlSheet->pageSetup["firstPageNumber"]) && isset($xmlSheet->pageSetup["useFirstPageNumber"]) &&
((string)$xmlSheet->pageSetup["useFirstPageNumber"] == 'true' || (string)$xmlSheet->pageSetup["useFirstPageNumber"] == '1')) {
$docPageSetup->setFirstPageNumber(intval($xmlSheet->pageSetup["firstPageNumber"]));
}
}
if ($xmlSheet && $xmlSheet->headerFooter && !$this->_readDataOnly) {
$docHeaderFooter = $docSheet->getHeaderFooter();
if (isset($xmlSheet->headerFooter["differentOddEven"]) &&
((string)$xmlSheet->headerFooter["differentOddEven"] == 'true' || (string)$xmlSheet->headerFooter["differentOddEven"] == '1')) {
$docHeaderFooter->setDifferentOddEven(true);
} else {
$docHeaderFooter->setDifferentOddEven(false);
}
if (isset($xmlSheet->headerFooter["differentFirst"]) &&
((string)$xmlSheet->headerFooter["differentFirst"] == 'true' || (string)$xmlSheet->headerFooter["differentFirst"] == '1')) {
$docHeaderFooter->setDifferentFirst(true);
} else {
$docHeaderFooter->setDifferentFirst(false);
}
if (isset($xmlSheet->headerFooter["scaleWithDoc"]) &&
((string)$xmlSheet->headerFooter["scaleWithDoc"] == 'false' || (string)$xmlSheet->headerFooter["scaleWithDoc"] == '0')) {
$docHeaderFooter->setScaleWithDocument(false);
} else {
$docHeaderFooter->setScaleWithDocument(true);
}
if (isset($xmlSheet->headerFooter["alignWithMargins"]) &&
((string)$xmlSheet->headerFooter["alignWithMargins"] == 'false' || (string)$xmlSheet->headerFooter["alignWithMargins"] == '0')) {
$docHeaderFooter->setAlignWithMargins(false);
} else {
$docHeaderFooter->setAlignWithMargins(true);
}
$docHeaderFooter->setOddHeader((string) $xmlSheet->headerFooter->oddHeader);
$docHeaderFooter->setOddFooter((string) $xmlSheet->headerFooter->oddFooter);
$docHeaderFooter->setEvenHeader((string) $xmlSheet->headerFooter->evenHeader);
$docHeaderFooter->setEvenFooter((string) $xmlSheet->headerFooter->evenFooter);
$docHeaderFooter->setFirstHeader((string) $xmlSheet->headerFooter->firstHeader);
$docHeaderFooter->setFirstFooter((string) $xmlSheet->headerFooter->firstFooter);
}
if ($xmlSheet && $xmlSheet->rowBreaks && $xmlSheet->rowBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->rowBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak("A$brk[id]", PHPExcel_Worksheet::BREAK_ROW);
}
}
}
if ($xmlSheet && $xmlSheet->colBreaks && $xmlSheet->colBreaks->brk && !$this->_readDataOnly) {
foreach ($xmlSheet->colBreaks->brk as $brk) {
if ($brk["man"]) {
$docSheet->setBreak(PHPExcel_Cell::stringFromColumnIndex($brk["id"]) . "1", PHPExcel_Worksheet::BREAK_COLUMN);
}
}
}
if ($xmlSheet && $xmlSheet->dataValidations && !$this->_readDataOnly) {
foreach ($xmlSheet->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation["sqref"]);
$rangeSet = explode(' ',$range);
foreach($rangeSet as $range) {
$stRange = $docSheet->shrinkRangeToFit($range);
// Extract all cell references in $range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($stRange);
foreach ($aReferences as $reference) {
// Create validation
$docValidation = $docSheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation["type"]);
$docValidation->setErrorStyle((string) $dataValidation["errorStyle"]);
$docValidation->setOperator((string) $dataValidation["operator"]);
$docValidation->setAllowBlank($dataValidation["allowBlank"] != 0);
$docValidation->setShowDropDown($dataValidation["showDropDown"] == 0);
$docValidation->setShowInputMessage($dataValidation["showInputMessage"] != 0);
$docValidation->setShowErrorMessage($dataValidation["showErrorMessage"] != 0);
$docValidation->setErrorTitle((string) $dataValidation["errorTitle"]);
$docValidation->setError((string) $dataValidation["error"]);
$docValidation->setPromptTitle((string) $dataValidation["promptTitle"]);
$docValidation->setPrompt((string) $dataValidation["prompt"]);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
}
}
}
}
// Add hyperlinks
$hyperlinks = array();
if (!$this->_readDataOnly) {
// Locate hyperlink relations
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink") {
$hyperlinks[(string)$ele["Id"]] = (string)$ele["Target"];
}
}
}
// Loop through hyperlinks
if ($xmlSheet && $xmlSheet->hyperlinks) {
foreach ($xmlSheet->hyperlinks->hyperlink as $hyperlink) {
// Link url
$linkRel = $hyperlink->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
foreach (PHPExcel_Cell::extractAllCellReferencesInRange($hyperlink['ref']) as $cellReference) {
$cell = $docSheet->getCell( $cellReference );
if (isset($linkRel['id'])) {
$cell->getHyperlink()->setUrl( $hyperlinks[ (string)$linkRel['id'] ] );
}
if (isset($hyperlink['location'])) {
$cell->getHyperlink()->setUrl( 'sheet://' . (string)$hyperlink['location'] );
}
// Tooltip
if (isset($hyperlink['tooltip'])) {
$cell->getHyperlink()->setTooltip( (string)$hyperlink['tooltip'] );
}
}
}
}
}
// Add comments
$comments = array();
$vmlComments = array();
if (!$this->_readDataOnly) {
// Locate comment relations
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments") {
$comments[(string)$ele["Id"]] = (string)$ele["Target"];
}
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlComments[(string)$ele["Id"]] = (string)$ele["Target"];
}
}
}
// Loop through comments
foreach ($comments as $relName => $relPath) {
// Load comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
$commentsFile = simplexml_load_string($this->_getFromZipArchive($zip, $relPath) );
// Utility variables
$authors = array();
// Loop through authors
foreach ($commentsFile->authors->author as $author) {
$authors[] = (string)$author;
}
// Loop through contents
foreach ($commentsFile->commentList->comment as $comment) {
$docSheet->getComment( (string)$comment['ref'] )->setAuthor( $authors[(string)$comment['authorId']] );
$docSheet->getComment( (string)$comment['ref'] )->setText( $this->_parseRichText($comment->text) );
}
}
// Loop through VML comments
foreach ($vmlComments as $relName => $relPath) {
// Load VML comments file
$relPath = PHPExcel_Shared_File::realpath(dirname("$dir/$fileWorksheet") . "/" . $relPath);
$vmlCommentsFile = simplexml_load_string( $this->_getFromZipArchive($zip, $relPath) );
$vmlCommentsFile->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$shapes = $vmlCommentsFile->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
if (isset($shape['style'])) {
$style = (string)$shape['style'];
$fillColor = strtoupper( substr( (string)$shape['fillcolor'], 1 ) );
$column = null;
$row = null;
$clientData = $shape->xpath('.//x:ClientData');
if (is_array($clientData) && count($clientData) > 0) {
$clientData = $clientData[0];
if ( isset($clientData['ObjectType']) && (string)$clientData['ObjectType'] == 'Note' ) {
$temp = $clientData->xpath('.//x:Row');
if (is_array($temp)) $row = $temp[0];
$temp = $clientData->xpath('.//x:Column');
if (is_array($temp)) $column = $temp[0];
}
}
if (!is_null($column) && !is_null($row)) {
// Set comment properties
$comment = $docSheet->getCommentByColumnAndRow($column, $row + 1);
$comment->getFillColor()->setRGB( $fillColor );
// Parse style
$styleArray = explode(';', str_replace(' ', '', $style));
foreach ($styleArray as $stylePair) {
$stylePair = explode(':', $stylePair);
if ($stylePair[0] == 'margin-left') $comment->setMarginLeft($stylePair[1]);
if ($stylePair[0] == 'margin-top') $comment->setMarginTop($stylePair[1]);
if ($stylePair[0] == 'width') $comment->setWidth($stylePair[1]);
if ($stylePair[0] == 'height') $comment->setHeight($stylePair[1]);
if ($stylePair[0] == 'visibility') $comment->setVisible( $stylePair[1] == 'visible' );
}
}
}
}
}
// Header/footer images
if ($xmlSheet && $xmlSheet->legacyDrawingHF && !$this->_readDataOnly) {
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$vmlRelationship = '';
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing") {
$vmlRelationship = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
}
}
if ($vmlRelationship != '') {
// Fetch linked images
$relsVML = simplexml_load_string($this->_getFromZipArchive($zip, dirname($vmlRelationship) . '/_rels/' . basename($vmlRelationship) . '.rels' )); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsVML->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$drawings[(string) $ele["Id"]] = self::dir_add($vmlRelationship, $ele["Target"]);
}
}
// Fetch VML document
$vmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $vmlRelationship));
$vmlDrawing->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$hfImages = array();
$shapes = $vmlDrawing->xpath('//v:shape');
foreach ($shapes as $shape) {
$shape->registerXPathNamespace('v', 'urn:schemas-microsoft-com:vml');
$imageData = $shape->xpath('//v:imagedata');
$imageData = $imageData[0];
$imageData = $imageData->attributes('urn:schemas-microsoft-com:office:office');
$style = self::toCSSArray( (string)$shape['style'] );
$hfImages[ (string)$shape['id'] ] = new PHPExcel_Worksheet_HeaderFooterDrawing();
if (isset($imageData['title'])) {
$hfImages[ (string)$shape['id'] ]->setName( (string)$imageData['title'] );
}
$hfImages[ (string)$shape['id'] ]->setPath("zip://$pFilename#" . $drawings[(string)$imageData['relid']], false);
$hfImages[ (string)$shape['id'] ]->setResizeProportional(false);
$hfImages[ (string)$shape['id'] ]->setWidth($style['width']);
$hfImages[ (string)$shape['id'] ]->setHeight($style['height']);
$hfImages[ (string)$shape['id'] ]->setOffsetX($style['margin-left']);
$hfImages[ (string)$shape['id'] ]->setOffsetY($style['margin-top']);
$hfImages[ (string)$shape['id'] ]->setResizeProportional(true);
}
$docSheet->getHeaderFooter()->setImages($hfImages);
}
}
}
}
// ----: Make sure drawings and graph are loaded differently!
if ($zip->locateName(dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels")) {
$relsWorksheet = simplexml_load_string($this->_getFromZipArchive($zip, dirname("$dir/$fileWorksheet") . "/_rels/" . basename($fileWorksheet) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$drawings = array();
foreach ($relsWorksheet->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing") {
$drawings[(string) $ele["Id"]] = self::dir_add("$dir/$fileWorksheet", $ele["Target"]);
}
}
if ($xmlSheet->drawing && !$this->_readDataOnly) {
foreach ($xmlSheet->drawing as $drawing) {
$fileDrawing = $drawings[(string) self::array_item($drawing->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "id")];
$relsDrawing = simplexml_load_string($this->_getFromZipArchive($zip, dirname($fileDrawing) . "/_rels/" . basename($fileDrawing) . ".rels") ); //~ http://schemas.openxmlformats.org/package/2006/relationships");
$images = array();
if ($relsDrawing && $relsDrawing->Relationship) {
foreach ($relsDrawing->Relationship as $ele) {
if ($ele["Type"] == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image") {
$images[(string) $ele["Id"]] = self::dir_add($fileDrawing, $ele["Target"]);
}
}
}
$xmlDrawing = simplexml_load_string($this->_getFromZipArchive($zip, $fileDrawing))->children("http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
if ($xmlDrawing->oneCellAnchor) {
foreach ($xmlDrawing->oneCellAnchor as $oneCellAnchor) {
if ($oneCellAnchor->pic->blipFill) {
$blip = $oneCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $oneCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing;
$objDrawing->setName((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($oneCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($oneCellAnchor->from->col) . ($oneCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($oneCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($oneCellAnchor->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
if ($xmlDrawing->twoCellAnchor) {
foreach ($xmlDrawing->twoCellAnchor as $twoCellAnchor) {
if ($twoCellAnchor->pic->blipFill) {
$blip = $twoCellAnchor->pic->blipFill->children("http://schemas.openxmlformats.org/drawingml/2006/main")->blip;
$xfrm = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->xfrm;
$outerShdw = $twoCellAnchor->pic->spPr->children("http://schemas.openxmlformats.org/drawingml/2006/main")->effectLst->outerShdw;
$objDrawing = new PHPExcel_Worksheet_Drawing;
$objDrawing->setName((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "name"));
$objDrawing->setDescription((string) self::array_item($twoCellAnchor->pic->nvPicPr->cNvPr->attributes(), "descr"));
$objDrawing->setPath("zip://$pFilename#" . $images[(string) self::array_item($blip->attributes("http://schemas.openxmlformats.org/officeDocument/2006/relationships"), "embed")], false);
$objDrawing->setCoordinates(PHPExcel_Cell::stringFromColumnIndex($twoCellAnchor->from->col) . ($twoCellAnchor->from->row + 1));
$objDrawing->setOffsetX(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->colOff));
$objDrawing->setOffsetY(PHPExcel_Shared_Drawing::EMUToPixels($twoCellAnchor->from->rowOff));
$objDrawing->setResizeProportional(false);
$objDrawing->setWidth(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cx")));
$objDrawing->setHeight(PHPExcel_Shared_Drawing::EMUToPixels(self::array_item($xfrm->ext->attributes(), "cy")));
if ($xfrm) {
$objDrawing->setRotation(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($xfrm->attributes(), "rot")));
}
if ($outerShdw) {
$shadow = $objDrawing->getShadow();
$shadow->setVisible(true);
$shadow->setBlurRadius(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "blurRad")));
$shadow->setDistance(PHPExcel_Shared_Drawing::EMUTopixels(self::array_item($outerShdw->attributes(), "dist")));
$shadow->setDirection(PHPExcel_Shared_Drawing::angleToDegrees(self::array_item($outerShdw->attributes(), "dir")));
$shadow->setAlignment((string) self::array_item($outerShdw->attributes(), "algn"));
$shadow->getColor()->setRGB(self::array_item($outerShdw->srgbClr->attributes(), "val"));
$shadow->setAlpha(self::array_item($outerShdw->srgbClr->alpha->attributes(), "val") / 1000);
}
$objDrawing->setWorksheet($docSheet);
}
}
}
}
}
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string)$definedName;
$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string)$definedName['localSheetId'] != '' && (string)$definedName['localSheetId'] == $sheetId) {
// Switch on type
switch ((string)$definedName['name']) {
case '_xlnm._FilterDatabase':
$docSheet->setAutoFilter($extractedRange);
break;
case '_xlnm.Print_Titles':
// Split $extractedRange
$extractedRange = explode(',', $extractedRange);
// Set print titles
foreach ($extractedRange as $range) {
$matches = array();
// check for repeating columns, e g. 'A:A' or 'A:D'
if (preg_match('/^([A-Z]+)\:([A-Z]+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setColumnsToRepeatAtLeft(array($matches[1], $matches[2]));
}
// check for repeating rows, e.g. '1:1' or '1:5'
elseif (preg_match('/^(\d+)\:(\d+)$/', $range, $matches)) {
$docSheet->getPageSetup()->setRowsToRepeatAtTop(array($matches[1], $matches[2]));
}
}
break;
case '_xlnm.Print_Area':
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
$docSheet->getPageSetup()->setPrintArea($extractedRange);
break;
default:
break;
}
}
}
}
// Next sheet id
++$sheetId;
}
// Loop through definedNames
if ($xmlWorkbook->definedNames) {
foreach ($xmlWorkbook->definedNames->definedName as $definedName) {
// Extract range
$extractedRange = (string)$definedName;
$extractedRange = preg_replace('/\'(\w+)\'\!/', '', $extractedRange);
$extractedRange = str_replace('$', '', $extractedRange);
// Valid range?
if (stripos((string)$definedName, '#REF!') !== false || $extractedRange == '') {
continue;
}
// Some definedNames are only applicable if we are on the same sheet...
if ((string)$definedName['localSheetId'] != '') {
// Local defined name
// Switch on type
switch ((string)$definedName['name']) {
case '_xlnm._FilterDatabase':
case '_xlnm.Print_Titles':
case '_xlnm.Print_Area':
break;
default:
$range = explode('!', (string)$definedName);
if (count($range) == 2) {
$range[0] = str_replace("''", "'", $range[0]);
$range[0] = str_replace("'", "", $range[0]);
if ($worksheet = $docSheet->getParent()->getSheetByName($range[0])) {
$extractedRange = str_replace('$', '', $range[1]);
$scope = $docSheet->getParent()->getSheet((string)$definedName['localSheetId']);
$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $worksheet, $extractedRange, true, $scope) );
}
}
break;
}
} else if (!isset($definedName['localSheetId'])) {
// "Global" definedNames
$locatedSheet = null;
$extractedSheetName = '';
if (strpos( (string)$definedName, '!' ) !== false) {
// Extract sheet name
$extractedSheetName = PHPExcel_Worksheet::extractSheetTitle( (string)$definedName, true );
$extractedSheetName = $extractedSheetName[0];
// Locate sheet
$locatedSheet = $excel->getSheetByName($extractedSheetName);
// Modify range
$range = explode('!', $extractedRange);
$extractedRange = isset($range[1]) ? $range[1] : $range[0];
}
if (!is_null($locatedSheet)) {
$excel->addNamedRange( new PHPExcel_NamedRange((string)$definedName['name'], $locatedSheet, $extractedRange, false) );
}
}
}
}
}
if (!$this->_readDataOnly) {
// active sheet index
$activeTab = intval($xmlWorkbook->bookViews->workbookView["activeTab"]); // refers to old sheet index
// keep active sheet index if sheet is still loaded, else first sheet is set as the active
if (isset($mapSheetId[$activeTab]) && $mapSheetId[$activeTab] !== null) {
$excel->setActiveSheetIndex($mapSheetId[$activeTab]);
} else {
if ($excel->getSheetCount() == 0)
{
$excel->createSheet();
}
$excel->setActiveSheetIndex(0);
}
}
break;
}
}
return $excel;
}
/**
* @inheritDoc
*/
public function writeHeaderRow($headerRow)
{
$this->writeRow($headerRow);
$lastColumnLetter = \PHPExcel_Cell::stringFromColumnIndex(count($headerRow) - 1);
$headerRange = "A{$this->currentRowIndex}:{$lastColumnLetter}{$this->currentRowIndex}";
$this->phpExcel->getActiveSheet()->getStyle($headerRange)->getFont()->setBold(true);
}
public function select($source)
{
$path = $this->connection;
$excel = PHPExcel_IOFactory::createReaderForFile($path);
$excel = $excel->load($path);
$excRes = new ExcelResult();
$excelWS = $excel->getActiveSheet();
$addFields = true;
$coords = array();
if ($source->get_source() == '*') {
$coords['start_row'] = 0;
$coords['end_row'] = false;
} else {
$c = array();
preg_match("/^([a-zA-Z]+)(\\d+)/", $source->get_source(), $c);
if (count($c) > 0) {
$coords['start_row'] = (int) $c[2];
} else {
$coords['start_row'] = 0;
}
$c = array();
preg_match("/:(.+)(\\d+)\$/U", $source->get_source(), $c);
if (count($c) > 0) {
$coords['end_row'] = (int) $c[2];
} else {
$coords['end_row'] = false;
}
}
$i = $coords['start_row'];
$end = 0;
while ($coords['end_row'] == false && $end < $this->emptyLimit || $coords['end_row'] !== false && $i < $coords['end_row']) {
$r = array();
$emptyNum = 0;
for ($j = 0; $j < count($this->config->text); $j++) {
$col = PHPExcel_Cell::columnIndexFromString($this->config->text[$j]['name']) - 1;
$cell = $excelWS->getCellByColumnAndRow($col, $i);
if ($cell->getDataType() == 'f') {
$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getCalculatedValue();
} else {
$r[PHPExcel_Cell::stringFromColumnIndex($col)] = $cell->getValue();
}
if ($r[PHPExcel_Cell::stringFromColumnIndex($col)] == '') {
$emptyNum++;
}
}
if ($emptyNum < count($this->config->text)) {
$r['id'] = $i;
$excRes->addRecord($r);
$end = 0;
} else {
if (DHX_IGNORE_EMPTY_ROWS == false) {
$r['id'] = $i;
$excRes->addRecord($r);
}
$end++;
}
$i++;
}
return $excRes;
}
/**
* exportCollectionSheet
*
* default sheet export for collection
*
* @param string $object
* @param string $fields
* @return void
* @author Brent Shaffer
*/
public function exportCollectionSheet($collection, $fields, $title = null)
{
if ($this->_sheets > 0) {
$workSheet = $this->_xls->createSheet();
} else {
$workSheet = $this->_xls->getActiveSheet();
}
$this->_sheets++;
$workSheet->setTitle($title ? $title : $this->getExportTitle());
// Initialize coordinate counters
$row = 1;
$col = 0;
foreach ($fields as $field => $label) {
$workSheet->setCellValueByColumnAndRow($col, $row, $label);
$workSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col))->setAutoSize(true);
$col++;
}
$row++;
foreach ($collection as $record) {
$col = 0;
foreach ($fields as $field => $label) {
$workSheet->setCellValueByColumnAndRow($col, $row, $this->exportField($record, $field));
$col++;
}
$row++;
}
}
/**
* 行を完全コピーする
*
* http://blog.kotemaru.org/old/2012/04/06.html より
* @param PHPExcel_Worksheet $sheet
* @param int $srcRow
* @param int $dstRow
* @param int $height
* @param int $width
* @throws PHPExcel_Exception
*/
function copyRows(PHPExcel_Worksheet $sheet, $srcRow, $dstRow, $height, $width)
{
for ($row = 0; $row < $height; $row++) {
// セルの書式と値の複製
for ($col = 0; $col < $width; $col++) {
$cell = $sheet->getCellByColumnAndRow($col, $srcRow + $row);
$style = $sheet->getStyleByColumnAndRow($col, $srcRow + $row);
$dstCell = PHPExcel_Cell::stringFromColumnIndex($col) . (string) ($dstRow + $row);
$sheet->setCellValue($dstCell, $cell->getValue());
$sheet->duplicateStyle($style, $dstCell);
}
// 行の高さ複製。
$h = $sheet->getRowDimension($srcRow + $row)->getRowHeight();
$sheet->getRowDimension($dstRow + $row)->setRowHeight($h);
}
// セル結合の複製
// - $mergeCell="AB12:AC15" 複製範囲の物だけ行を加算して復元。
// - $merge="AB16:AC19"
foreach ($sheet->getMergeCells() as $mergeCell) {
$mc = explode(":", $mergeCell);
$col_s = preg_replace("/[0-9]*/", "", $mc[0]);
$col_e = preg_replace("/[0-9]*/", "", $mc[1]);
$row_s = (int) preg_replace("/[A-Z]*/", "", $mc[0]) - $srcRow;
$row_e = (int) preg_replace("/[A-Z]*/", "", $mc[1]) - $srcRow;
// 複製先の行範囲なら。
if (0 <= $row_s && $row_s < $height) {
$merge = $col_s . (string) ($dstRow + $row_s) . ":" . $col_e . (string) ($dstRow + $row_e);
$sheet->mergeCells($merge);
}
}
}
private static function init($data, $headerTitle = [], $headerRGBColor = 'FCFCFC', $borderColor = 'CCC')
{
$phpExcel = new \PHPExcel();
$phpExcel->getProperties()->setCreator('Affiliates Team')->setTitle('Excel Data');
$phpExcelSheet = $phpExcel->createSheet(0);
$phpExcelSheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$phpExcelSheet->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3);
if (!empty($headerTitle)) {
$tmpData = [];
$columnKeys = array_keys($headerTitle);
foreach ($data as $dataTmpRow) {
$tmpRow = [];
foreach ($columnKeys as $columnKey) {
$tmpRow[$columnKey] = isset($dataTmpRow[$columnKey]) ? $dataTmpRow[$columnKey] : '';
}
$tmpData[] = $tmpRow;
}
$data = $tmpData;
}
$rowLength = count($data);
if ($rowLength) {
$keys = array_keys($data[0]);
if (!empty($headerTitle)) {
foreach ($keys as &$keysRow) {
$keysRow = isset($headerTitle[$keysRow]) ? $headerTitle[$keysRow] : $keysRow;
}
}
$columnLength = count($keys);
$fromCode = \PHPExcel_Cell::stringFromColumnIndex(0);
$toCode = \PHPExcel_Cell::stringFromColumnIndex($columnLength - 1);
for ($i = 0; $i < $columnLength; $i++) {
$phpExcelSheet->setCellValueByColumnAndRow($i, 1, $keys[$i]);
$phpExcelSheet->getColumnDimension(chr(65 + $i))->setAutoSize(true);
}
// [merging], develop later
// $phpExcelSheet->mergeCells('A1:A2');
// $phpExcelSheet->getStyle('A1:A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$phpExcelSheet->fromArray($data, ' ', 'A3');
for ($i = 0; $i < $rowLength; ++$i) {
$rowIndex = $i + 1;
if ($rowIndex % 3 == 0) {
if ($rowIndex / 3 % 2) {
self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F9F9F9');
} else {
self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'F5FMD');
}
} else {
self::colorRow($phpExcelSheet, $fromCode, $toCode, $i + 3, 'FFFFFF');
}
}
self::colorRow($phpExcelSheet, $fromCode, $toCode, 1, $headerRGBColor);
$headerStyle = ['font' => ['bold' => true], 'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]];
$phpExcelSheet->getStyle($fromCode . '1:' . $toCode . '1')->applyFromArray($headerStyle);
self::setBorderStyle($phpExcelSheet, $fromCode, $toCode, $rowLength + 2, $borderColor);
}
return $phpExcel;
}
/**
* The MysqlExportXls function is used to export mysql query result into an .xls file.
* @param MysqlExportXlsConnectOptions $connectOptions
* @param MysqlExportXlsFileOptions $fileOptions
* @return error message. Return empty string on success.
*/
function MysqlExportXls($connectOptions, $fileOptions, $query)
{
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($fileOptions->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
$objPHPExcel->getProperties()->setTitle($fileOptions->title);
$objPHPExcel->getProperties()->setSubject($fileOptions->subject);
$objPHPExcel->getProperties()->setDescription($fileOptions->description);
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setTitle($fileOptions->title);
// connect to mysql
$link = mysql_connect($connectOptions->host, $connectOptions->userName, $connectOptions->password);
if (!$link) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not connect: ' . mysql_error($link);
}
// use database
$selectDb = mysql_select_db($connectOptions->useDatabase, $link);
if (!$selectDb) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not select database' . mysql_error($link);
}
// PHPExcel use utf-8 encoding to save file only !!!
$setCharset = mysql_set_charset("utf8", $link);
if (!$setCharset) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Could not set charset' . mysql_error($link);
}
// execute sql
$result = mysql_query($query, $link);
if (!$result) {
return __FILE__ . ":" . __FUNCTION__ . ':' . 'Query failed: ' . mysql_error($link);
}
// field names
$columnIndex = 0;
while ($field = mysql_fetch_field($result)) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $field->name);
++$columnIndex;
}
$rowIndex = 2;
// 1 based, the firset row is for field names.
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$columnIndex = 0;
foreach ($line as $key => $col_value) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $col_value === null ? "" : $col_value, PHPExcel_Cell_DataType::TYPE_STRING2);
++$columnIndex;
}
++$rowIndex;
}
// free mysql resource
mysql_free_result($result);
mysql_close($link);
// write data into file
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
// Why true by default ? oh god damn it!
$objWriter->save($fileOptions->name);
return "";
}
protected function cellRange($col1, $row1, $col2, $row2)
{
if (is_integer($col1)) {
$col1 = \PHPExcel_Cell::stringFromColumnIndex($col1);
}
if (is_integer($col2)) {
$col2 = \PHPExcel_Cell::stringFromColumnIndex($col2);
}
return sprintf('%s%d:%s%d', $col1, $row1, $col2, $row2);
}
public function cellsToMergeByColsRow($c_start = NULL, $c_end = NULL, $r_start = NULL, $r_end = NULL)
{
$merge = 'A1:A1';
if ($c_start >= 0 && $c_end >= 0 && $r_start && $r_end) {
$col_start = PHPExcel_Cell::stringFromColumnIndex($c_start);
$col_end = PHPExcel_Cell::stringFromColumnIndex($c_end);
$merge = $col_start . $r_start . ":" . $col_end . $r_end;
}
return $merge;
}
public function summary($id)
{
$store_audit = StoreAudit::findOrFail($id);
$summaries = StoreAuditSummary::where('store_audit_id', $id)->get();
$categories = StoreAuditSummary::getUniqueCategoryForPerpectStore($id);
$groups = StoreAuditSummary::getUniqueGroupForPerpectStore($id);
\Excel::create($store_audit->store_name . ' - ' . $store_audit->template_name, function ($excel) use($summaries, $categories, $groups) {
$excel->sheet('Sheet1', function ($sheet) use($summaries, $categories, $groups) {
$sheet->row(1, array('ENROLLMENT TYPE', 'AREA', 'STORE CODE', 'STORE NAME', 'KPI'));
$col = 5;
$x = array();
foreach ($categories as $category) {
$sheet->setCellValueByColumnAndRow($col, 1, $category->category);
$x[$category->category] = $col;
$col++;
}
$sheet->setCellValueByColumnAndRow($col, 1, 'PS SCORE');
$row = 2;
$y = array();
foreach ($groups as $group) {
$sheet->setCellValueByColumnAndRow(4, $row, $group->group);
$y[$group->group] = $row;
for ($i = 5; $i < 8; $i++) {
$sheet->setCellValueByColumnAndRow($i, $row, 'N/A');
}
// add ps score
$ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $ps_code);
$row++;
}
$sheet->setCellValueByColumnAndRow(4, $row, 'Perfect Store Summary');
$col = 5;
$first_row = 2;
$last_row = $row - 1;
foreach ($categories as $category) {
$cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
$col++;
}
$cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
// echo '<pre>';
// print_r($x);
// print_r($y);
// echo '</pre>';
// dd(1);
foreach ($summaries as $summary) {
$x_point = $x[$summary->category];
$y_point = $y[$summary->group];
$sheet->setCellValueByColumnAndRow($x_point, $y_point, $summary->passed);
}
})->download('xls');
});
}
protected function doExport($objEntity = null, array $arrFields = array())
{
switch ($this->type) {
case Exporter::TYPE_ITEM:
break;
case Exporter::TYPE_LIST:
$objDbResult = $this->getEntities();
$arrDca = $GLOBALS['TL_DCA'][$this->linkedTable];
if (!$objDbResult->numRows > 0) {
return;
}
$intCol = 0;
$intRow = 1;
// header
if ($this->objConfig->addHeaderToExportTable) {
foreach ($this->arrHeaderFields as $varValue) {
$this->objPhpExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($intCol, $intRow, $varValue);
$this->processHeaderRow($intCol);
$intCol++;
}
$intRow++;
}
// body
while ($objDbResult->next()) {
$arrRow = $objDbResult->row();
$intCol = 0;
foreach ($arrRow as $key => $varValue) {
$objDc = new \DC_Table($this->linkedTable);
$objDc->activeRecord = $objDbResult;
$objDc->id = $objDbResult->id;
$varValue = $this->localizeFields ? FormSubmission::prepareSpecialValueForPrint($varValue, $arrDca['fields'][$key], $this->linkedTable, $objDc) : $varValue;
if (is_array($varValue)) {
$varValue = Arrays::flattenArray($varValue);
}
$this->objPhpExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($intCol, $intRow, html_entity_decode($varValue));
$this->objPhpExcel->getActiveSheet()->getColumnDimension(\PHPExcel_Cell::stringFromColumnIndex($intCol))->setAutoSize(true);
$this->processBodyRow($intCol);
$intCol++;
}
$this->objPhpExcel->getActiveSheet()->getRowDimension($intRow)->setRowHeight(-1);
$intRow++;
}
$this->objPhpExcel->setActiveSheetIndex(0);
$this->objPhpExcel->getActiveSheet()->setTitle('Export');
return $this->objPhpExcel;
break;
}
return false;
}
/** Export $data into an Excel file
*
* @param $data mixed data retrieved via Model->find operation
* @param $options mixed array of options
*
*/
function export($modelName, $data, $options = array())
{
$needHeader = true;
$startRow = 2;
$startCol = 'A';
$options = Set::merge(array('template' => array('file' => null, 'type' => 'Excel5', 'startRow' => $startRow, 'startCol' => $startCol), 'output' => array('file' => 'export.xls', 'type' => 'Excel5'), 'columnHeaders' => array(), 'fields' => array(), 'format' => array('date' => 'd/m/Y', 'float' => '%.2f')), $options);
if (empty($data)) {
trigger_error('No data to export');
return;
}
if (!empty($options['template']['file'])) {
$template = $options['template'];
$startRow = $template['startRow'];
$startCol = $template['startCol'];
$reader = PHPExcel_IOFactory::createReader($template['type']);
$xls = $reader->load($template['file']);
$needHeader = false;
} else {
$xls = new PHPExcel();
$xls->setActiveSheetIndex(0);
}
$sheet = $xls->getActiveSheet();
if ($needHeader) {
$this->_writeHeaders($xls, $options);
}
$Model = ClassRegistry::init($modelName);
for ($i = 0, $ii = count($data); $i < $ii; $i++) {
$col = 0;
$row = $i + $startRow;
for ($c = 0, $cc = count($options['fields']); $c < $cc; $c++) {
$currentField = $options['fields'][$c];
$split = explode('.', $currentField, 2);
$fieldModel = $split[0];
$fieldName = $split[1];
$cell = PHPExcel_Cell::stringFromColumnIndex($col) . $row;
if (!isset($data[$i][$fieldModel][$fieldName])) {
$fieldType = 'string';
$fieldValue = '';
} else {
$fieldType = $this->_getColumnType($Model, $fieldModel, $fieldName);
$fieldValue = $data[$i][$fieldModel][$fieldName];
}
$this->_setCellValue($sheet, $cell, $fieldType, $fieldValue, $options);
$col++;
}
}
$writer = PHPExcel_IOFactory::createWriter($xls, $options['output']['type']);
$writer->save($options['output']['file']);
}
public function getHeader($currentSheetNum, $startRow, $startColumn)
{
$this->isFilepathReadable();
$headerArray = array();
$startColumn = PHPExcel_Cell::columnIndexFromString($startColumn) - 1;
$currentSheet = $this->PHPExcel->getSheet($currentSheetNum);
$lastColumn = $currentSheet->getHighestColumn();
$lastColumn = PHPExcel_Cell::columnIndexFromString($lastColumn) - 1;
for ($column = $startColumn; $column <= $lastColumn; $column++) {
$cell = $currentSheet->getCellByColumnAndRow($column, $startRow)->getCalculatedValue();
if (!is_null($cell)) {
$headerArray[PHPExcel_Cell::stringFromColumnIndex($column)] = $cell;
}
}
return $headerArray;
}
function _headers()
{
$i = 0;
foreach ($this->data[0] as $field => $value) {
if (!in_array($field, $this->blacklist)) {
$columnName = Inflector::humanize($field);
$this->sheet->setCellValueByColumnAndRow($i++, 4, $columnName);
}
}
$this->sheet->getStyle('A4')->getFont()->setBold(true);
$this->sheet->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('A4')->getFill()->getStartColor()->setRGB('969696');
$this->sheet->duplicateStyle($this->sheet->getStyle('A4'), 'B4:' . $this->sheet->getHighestColumn() . '4');
for ($j = 1; $j < $i; $j++) {
$this->sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($j))->setAutoSize(true);
}
}
protected function writeData(\PHPExcel_Worksheet $worksheet)
{
foreach ($this->activeDataProvider->getModels() as $row => $model) {
foreach ($this->columns as $col => $column) {
$columnIndex = \PHPExcel_Cell::stringFromColumnIndex($col) . ($row + 2);
switch ($column->format) {
case Column::FormatRaw:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
break;
case Column::FormatUri:
$worksheet->setCellValue($columnIndex, $column->getValue($model));
$worksheet->getCell($columnIndex)->getHyperlink()->setUrl('"' . $column->getValue($model) . '"');
break;
}
}
}
}
public function getdata()
{
$type = strtolower(pathinfo($this->casefile, PATHINFO_EXTENSION));
if (!file_exists($this->casefile)) {
return false;
}
//根据不同类型分别操作
if ($type == 'xlsx' || $type == 'xls') {
$objPHPExcel = PHPExcel_IOFactory::load($this->casefile);
} else {
if ($type == 'csv') {
$objReader = PHPExcel_IOFactory::createReader('CSV')->setDelimiter(',')->setInputEncoding('GBK')->setEnclosure('"')->setLineEnding("\r\n")->setSheetIndex(0);
$objPHPExcel = $objReader->load($this->casefile);
} else {
return false;
}
}
//选择标签页
$sheet = $objPHPExcel->getSheet(0);
//获取行数与列数,注意列数需要转换
$highestRowNum = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnNum = PHPExcel_Cell::columnIndexFromString($highestColumn);
//取得字段，这里测试表格中的第一行为数据的字段，因此先取出用来作后面数组的键名
$filed = array();
for ($i = 0; $i < $highestColumnNum; $i++) {
$cellName = PHPExcel_Cell::stringFromColumnIndex($i) . '1';
$cellVal = $sheet->getCell($cellName)->getValue();
//取得列内容
$filed[] = $cellVal;
}
//开始取出数据并存入数组
$data = array();
for ($i = 2; $i <= $highestRowNum; $i++) {
//ignore row 1
$row = array();
for ($j = 0; $j < $highestColumnNum; $j++) {
$cellName = PHPExcel_Cell::stringFromColumnIndex($j) . $i;
$cellVal = $sheet->getCell($cellName)->getValue();
$row[$filed[$j]] = $cellVal;
}
$data[] = $row;
}
return $data;
}
public function dry()
{
$result = new ImportResult();
$worksheet = $this->phpExcel->setActiveSheetIndex(0);
$rows = [];
for ($start = 2, $current = $start; $current <= $worksheet->getHighestRow(); ++$current) {
$rows[] = $worksheet->rangeToArray("A{$current}:" . \PHPExcel_Cell::stringFromColumnIndex(count($this->columns) - 1) . "{$current}", false, false, false);
}
foreach ($rows as $rowIndex => $row) {
$row = $row[0];
$currentModel = null;
foreach ($this->columns as $columnIndex => $column) {
/** @var Column $column */
$value = trim($row[$columnIndex]);
if ($column instanceof PKColumn) {
// Удаление
if ($value === '0') {
continue 2;
}
// Обновление
if ($value !== '') {
if (!isset($this->models[$value])) {
throw new \Exception('Неизвестный ID');
}
$currentModel = $this->models[$value];
$result->addKeeping($currentModel);
continue;
}
// Добавление
if ($value === '') {
$currentModel = new $this->activeDataProvider->query->modelClass(['scenario' => 'admin-create', 'category_id' => $this->activeDataProvider->query->primaryModel->primaryKey]);
$result->addUploaded($currentModel);
}
continue;
}
if (!$column->compareValue($currentModel, $value)) {
$result->addUpdated($currentModel, $column, $value);
}
}
}
foreach ($this->models as $model) {
$result->is($model);
}
return $result;
}
/**
* Get data from an excel file.
* @param String $file_path - the path of the excel file
* @return an array contains data which is read from the
* excel file.
*/
public function get_data_from_excel($file_path)
{
$file_ext = substr($file_path, -3);
if (strtolower($file_ext) == 'xls') {
$reader = PHPExcel_IOFactory::createReader('Excel5');
} else {
$reader = PHPExcel_IOFactory::createReader('Excel2007');
}
$obj = $reader->load($file_path);
$sheet = $obj->getSheet(0);
$number_of_rows = $sheet->getHighestRow();
$number_of_columns = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
$data = array();
for ($row = 1; $row <= $number_of_rows; ++$row) {
for ($column = 0; $column < $number_of_columns; ++$column) {
$columnName = PHPExcel_Cell::stringFromColumnIndex($column);
$data[$row - 1][$column] = $sheet->getCellByColumnAndRow($column, $row)->getValue();
}
}
return $data;
}
/**
* Parses an excel file into an array
*
* @param string $strFile Excel file name
* @param int $intOffset
* @param array $arrStruct Column structure
* @param string $strCheck Only add the item if the value for this column is set
*/
function parseExcel($strFile, $arrStruct, $intOffset = 1, $strCheck = false)
{
include_once 'lib/phpexcel/Classes/PHPExcel.php';
$exlObject = PHPExcel_IOFactory::load($strFile);
$exlSheet = $exlObject->getSheet(0);
$intRows = $exlSheet->getHighestRow();
$arrData = array();
for ($r = $intOffset; $r <= $intRows; $r++) {
$pass = false;
$arrItem = array();
foreach ($arrStruct as $col => $label) {
PHPExcel_Cell::stringFromColumnIndex($col) . $r;
if ($label) {
$arrItem[$label] = trim($exlSheet->getCellByColumnAndRow($col, $r)->getValue());
}
}
if ($strCheck && isset($arrItem[$strCheck]) && $arrItem[$strCheck] != '' || !$strCheck) {
$arrData[] = $arrItem;
}
}
return $arrData;
}
/**
* The ExportXlsFromArray function is used to dump an array into xls file.
*/
function ExportXlsFromArray(ExportXlsFileOptions $fileOptions, array $array)
{
if (count($array) == 0 || count($array[0]) == 0) {
return "Array is emtpty.";
}
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator($fileOptions->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($fileOptions->lastModifiedBy);
$objPHPExcel->getProperties()->setTitle($fileOptions->title);
$objPHPExcel->getProperties()->setSubject($fileOptions->subject);
$objPHPExcel->getProperties()->setDescription($fileOptions->description);
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->setTitle($fileOptions->title);
$fields = array();
// field names
$columnIndex = 0;
foreach ($array[0] as $key => $value) {
$fields[] = $key;
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . '1', $key);
++$columnIndex;
}
$rowIndex = 2;
// 1 based, the firset row is for field names.
foreach ($array as $line) {
$columnIndex = 0;
foreach ($fields as $field) {
$activeSheet->SetCellValue(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, !isset($line[$field]) || $line[$field] === null ? "" : $line[$field], PHPExcel_Cell_DataType::TYPE_STRING2);
++$columnIndex;
}
++$rowIndex;
}
// write data into file
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->setPreCalculateFormulas(FALSE);
// Why true by default ? oh god damn it!
$objWriter->save($fileOptions->name);
return "";
}
public function __construct(\SplFileObject $file, $headerRowNumber = null, $activeSheet = null, $readOnly = true, $maxRows = null, $maxCol = null)
{
$this->file = $file;
$this->activeSheet = $activeSheet;
$this->reader = \PHPExcel_IOFactory::createReaderForFile($file->getPathName());
$this->reader->setReadDataOnly($readOnly);
if (!is_null($headerRowNumber)) {
$this->headerRowNumber = $headerRowNumber;
$headerReader = clone $this->reader;
$headerReader->setReadFilter(new ReadFilter($headerRowNumber + 1));
/** @var \PHPExcel $excel */
$excel = $headerReader->load($file->getPathname());
if (null !== $activeSheet) {
$excel->setActiveSheetIndex($activeSheet);
}
$rows = $excel->getActiveSheet()->toArray();
$this->columnHeaders = $rows[$headerRowNumber];
//set max col from header length if not already given
if (is_null($maxCol)) {
$maxCol = \PHPExcel_Cell::stringFromColumnIndex(count($this->columnHeaders) - 1);
}
}
$this->setBoundaries($maxRows, $maxCol);
}
/**
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
* The SDK incorrectly states that the height should be expressed as a
* percentage of 1024.
*
* @access private
* @param integer $col_start Col containing upper left corner of object
* @param integer $row_start Row containing top left corner of object
* @param integer $x1 Distance to left side of object
* @param integer $y1 Distance to top of object
* @param integer $width Width of image frame
* @param integer $height Height of image frame
*/
function _positionImage($col_start, $row_start, $x1, $y1, $width, $height)
{
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
$width -= PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1)) {
$height -= PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero eight or width.
//
if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) == 0) {
return;
}
if (PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_start + 1) * 256;
$x2 = $width / PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024;
// Distance to right side of object
$y2 = $height / PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $row_end + 1) * 256;
// Distance to bottom of object
$this->_writeObjPicture($col_start, $x1, $row_start, $y1, $col_end, $x2, $row_end, $y2);
}
/**
* Loads PHPExcel from file into PHPExcel instance
*
* @param string $pFilename
* @param PHPExcel $objPHPExcel
* @return PHPExcel
* @throws Exception
*/
public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
{
$fromFormats = array('\\-', '\\ ');
$toFormats = array('-', ' ');
$underlineStyles = array(PHPExcel_Style_Font::UNDERLINE_NONE, PHPExcel_Style_Font::UNDERLINE_DOUBLE, PHPExcel_Style_Font::UNDERLINE_DOUBLEACCOUNTING, PHPExcel_Style_Font::UNDERLINE_SINGLE, PHPExcel_Style_Font::UNDERLINE_SINGLEACCOUNTING);
$verticalAlignmentStyles = array(PHPExcel_Style_Alignment::VERTICAL_BOTTOM, PHPExcel_Style_Alignment::VERTICAL_TOP, PHPExcel_Style_Alignment::VERTICAL_CENTER, PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$horizontalAlignmentStyles = array(PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, PHPExcel_Style_Alignment::HORIZONTAL_LEFT, PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, PHPExcel_Style_Alignment::HORIZONTAL_CENTER, PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
// Check if file exists
if (!file_exists($pFilename)) {
throw new Exception("Could not open " . $pFilename . " for reading! File does not exist.");
}
$xml = simplexml_load_file($pFilename);
$namespaces = $xml->getNamespaces(true);
// echo '<pre>';
// print_r($namespaces);
// echo '</pre><hr />';
//
// echo '<pre>';
// print_r($xml);
// echo '</pre><hr />';
//
$docProps = $objPHPExcel->getProperties();
foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) {
switch ($propertyName) {
case 'Title':
$docProps->setTitle($propertyValue);
break;
case 'Subject':
$docProps->setSubject($propertyValue);
break;
case 'Author':
$docProps->setCreator($propertyValue);
break;
case 'Created':
$creationDate = strtotime($propertyValue);
$docProps->setCreated($creationDate);
break;
case 'LastAuthor':
$docProps->setLastModifiedBy($propertyValue);
break;
case 'Company':
$docProps->setCompany($propertyValue);
break;
case 'Category':
$docProps->setCategory($propertyValue);
break;
case 'Keywords':
$docProps->setKeywords($propertyValue);
break;
case 'Description':
$docProps->setDescription($propertyValue);
break;
}
}
foreach ($xml->Styles[0] as $style) {
$style_ss = $style->attributes($namespaces['ss']);
$styleID = (string) $style_ss['ID'];
// echo 'Style ID = '.$styleID.'<br />';
if ($styleID == 'Default') {
$this->_styles['Default'] = array();
} else {
$this->_styles[$styleID] = $this->_styles['Default'];
}
foreach ($style as $styleType => $styleData) {
$styleAttributes = $styleData->attributes($namespaces['ss']);
// echo $styleType.'<br />';
switch ($styleType) {
case 'Alignment':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'Vertical':
if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['vertical'] = $styleAttributeValue;
}
break;
case 'Horizontal':
if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue;
}
break;
case 'WrapText':
$this->_styles[$styleID]['alignment']['wrap'] = true;
break;
}
}
break;
case 'Borders':
foreach ($styleData->Border as $borderStyle) {
$borderAttributes = $borderStyle->attributes($namespaces['ss']);
$thisBorder = array();
foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) {
// echo $borderStyleKey.' = '.$borderStyleValue.'<br />';
switch ($borderStyleKey) {
case 'LineStyle':
$thisBorder['style'] = PHPExcel_Style_Border::BORDER_MEDIUM;
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Weight':
// $thisBorder['style'] = $borderStyleValue;
break;
case 'Position':
$borderPosition = strtolower($borderStyleValue);
break;
case 'Color':
$borderColour = substr($borderStyleValue, 1);
$thisBorder['color']['rgb'] = $borderColour;
break;
}
}
if (count($thisBorder) > 0) {
if ($borderPosition == 'left' || $borderPosition == 'right' || $borderPosition == 'top' || $borderPosition == 'bottom') {
$this->_styles[$styleID]['borders'][$borderPosition] = $thisBorder;
}
}
}
break;
case 'Font':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = (string) $styleAttributeValue;
switch ($styleAttributeKey) {
case 'FontName':
$this->_styles[$styleID]['font']['name'] = $styleAttributeValue;
break;
case 'Size':
$this->_styles[$styleID]['font']['size'] = $styleAttributeValue;
break;
case 'Color':
$this->_styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
case 'Bold':
$this->_styles[$styleID]['font']['bold'] = true;
break;
case 'Italic':
$this->_styles[$styleID]['font']['italic'] = true;
break;
case 'Underline':
if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) {
$this->_styles[$styleID]['font']['underline'] = $styleAttributeValue;
}
break;
}
}
break;
case 'Interior':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
switch ($styleAttributeKey) {
case 'Color':
$this->_styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1);
break;
}
}
break;
case 'NumberFormat':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
$styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue);
switch ($styleAttributeValue) {
case 'Short Date':
$styleAttributeValue = 'dd/mm/yyyy';
break;
}
if ($styleAttributeValue > '') {
$this->_styles[$styleID]['numberformat']['code'] = $styleAttributeValue;
}
}
break;
case 'Protection':
foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) {
// echo $styleAttributeKey.' = '.$styleAttributeValue.'<br />';
}
break;
}
}
// print_r($this->_styles[$styleID]);
// echo '<hr />';
}
// echo '<hr />';
$worksheetID = 0;
foreach ($xml->Worksheet as $worksheet) {
$worksheet_ss = $worksheet->attributes($namespaces['ss']);
if (isset($this->_loadSheetsOnly) && isset($worksheet_ss['Name']) && !in_array($worksheet_ss['Name'], $this->_loadSheetsOnly)) {
continue;
}
// Create new Worksheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($worksheetID);
if (isset($worksheet_ss['Name'])) {
$worksheetName = (string) $worksheet_ss['Name'];
$objPHPExcel->getActiveSheet()->setTitle($worksheetName);
}
$columnID = 'A';
foreach ($worksheet->Table->Column as $columnData) {
$columnData_ss = $columnData->attributes($namespaces['ss']);
if (isset($columnData_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($columnData_ss['Index'] - 1);
}
if (isset($columnData_ss['Width'])) {
$columnWidth = $columnData_ss['Width'];
// echo '<b>Setting column width for '.$columnID.' to '.$columnWidth.'</b><br />';
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4);
}
++$columnID;
}
$rowID = 1;
foreach ($worksheet->Table->Row as $rowData) {
$row_ss = $rowData->attributes($namespaces['ss']);
if (isset($row_ss['Index'])) {
$rowID = (int) $row_ss['Index'];
}
// echo '<b>Row '.$rowID.'</b><br />';
if (isset($row_ss['StyleID'])) {
$rowStyle = $row_ss['StyleID'];
}
if (isset($row_ss['Height'])) {
$rowHeight = $row_ss['Height'];
// echo '<b>Setting row height to '.$rowHeight.'</b><br />';
$objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight);
}
$columnID = 'A';
foreach ($rowData->Cell as $cell) {
$cell_ss = $cell->attributes($namespaces['ss']);
if (isset($cell_ss['Index'])) {
$columnID = PHPExcel_Cell::stringFromColumnIndex($cell_ss['Index'] - 1);
}
$cellRange = $columnID . $rowID;
if (isset($cell_ss['MergeAcross']) || isset($cell_ss['MergeDown'])) {
$columnTo = $columnID;
if (isset($cell_ss['MergeAcross'])) {
$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1);
}
$rowTo = $rowID;
if (isset($cell_ss['MergeDown'])) {
$rowTo = $rowTo + $cell_ss['MergeDown'];
}
$cellRange .= ':' . $columnTo . $rowTo;
$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
}
$hasCalculatedValue = false;
$cellDataFormula = '';
if (isset($cell_ss['Formula'])) {
$cellDataFormula = $cell_ss['Formula'];
$hasCalculatedValue = true;
}
if (isset($cell->Data)) {
$cellValue = $cellData = $cell->Data;
$type = PHPExcel_Cell_DataType::TYPE_NULL;
$cellData_ss = $cellData->attributes($namespaces['ss']);
if (isset($cellData_ss['Type'])) {
$cellDataType = $cellData_ss['Type'];
switch ($cellDataType) {
/*
const TYPE_STRING = 's';
const TYPE_FORMULA = 'f';
const TYPE_NUMERIC = 'n';
const TYPE_BOOL = 'b';
const TYPE_NULL = 's';
const TYPE_INLINE = 'inlineStr';
const TYPE_ERROR = 'e';
*/
case 'String':
$type = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case 'Number':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = (double) $cellValue;
if (floor($cellValue) == $cellValue) {
$cellValue = (int) $cellValue;
}
break;
case 'Boolean':
$type = PHPExcel_Cell_DataType::TYPE_BOOL;
$cellValue = $cellValue != 0;
break;
case 'DateTime':
$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
$cellValue = PHPExcel_Shared_Date::PHPToExcel(strtotime($cellValue));
break;
case 'Error':
$type = PHPExcel_Cell_DataType::TYPE_ERROR;
break;
}
}
if ($hasCalculatedValue) {
$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
$columnNumber = PHPExcel_Cell::columnIndexFromString($columnID);
// Convert R1C1 style references to A1 style references (but only when not quoted)
$temp = explode('"', $cellDataFormula);
foreach ($temp as $key => &$value) {
// Only replace in alternate array entries (i.e. non-quoted blocks)
if ($key % 2 == 0) {
preg_match_all('/(R(\\[?-?\\d*\\]?))(C(\\[?-?\\d*\\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE);
// Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
// through the formula from left to right. Reversing means that we work right to left.through
// the formula
$cellReferences = array_reverse($cellReferences);
// Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
// then modify the formula to use that new reference
foreach ($cellReferences as $cellReference) {
$rowReference = $cellReference[2][0];
// Empty R reference is the current row
if ($rowReference == '') {
$rowReference = $rowID;
}
// Bracketed R references are relative to the current row
if ($rowReference[0] == '[') {
$rowReference = $rowID + trim($rowReference, '[]');
}
$columnReference = $cellReference[4][0];
// Empty C reference is the current column
if ($columnReference == '') {
$columnReference = $columnNumber;
}
// Bracketed C references are relative to the current column
if ($columnReference[0] == '[') {
$columnReference = $columnNumber + trim($columnReference, '[]');
}
$A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference - 1) . $rowReference;
$value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0]));
}
}
}
unset($value);
// Then rebuild the formula string
$cellDataFormula = implode('"', $temp);
}
// echo 'Cell '.$columnID.$rowID.' is a '.$type.' with a value of '.(($hasCalculatedValue) ? $cellDataFormula : $cellValue).'<br />';
//
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit($hasCalculatedValue ? $cellDataFormula : $cellValue, $type);
if ($hasCalculatedValue) {
// echo 'Forumla result is '.$cellValue.'<br />';
$objPHPExcel->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue);
}
}
if (isset($cell_ss['StyleID'])) {
$style = (string) $cell_ss['StyleID'];
// echo 'Cell style for '.$columnID.$rowID.' is '.$style.'<br />';
if (isset($this->_styles[$style]) && count($this->_styles[$style]) > 0) {
// echo 'Cell '.$columnID.$rowID.'<br />';
// print_r($this->_styles[$style]);
// echo '<br />';
if (!$objPHPExcel->getActiveSheet()->cellExists($columnID . $rowID)) {
$objPHPExcel->getActiveSheet()->setCellValue($columnID . $rowID, NULL);
}
$objPHPExcel->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->_styles[$style]);
}
}
++$columnID;
}
++$rowID;
}
++$worksheetID;
}
// Return
return $objPHPExcel;
}
/**
* Accepts a range, returning it as a range that falls within the current highest row and column of the worksheet
*
* @param string $range
* @return string Adjusted range value
*/
public function shrinkRangeToFit($range)
{
$maxCol = $this->getHighestColumn();
$maxRow = $this->getHighestRow();
$maxCol = PHPExcel_Cell::columnIndexFromString($maxCol);
$rangeBlocks = explode(' ', $range);
foreach ($rangeBlocks as &$rangeSet) {
$rangeBoundaries = PHPExcel_Cell::getRangeBoundaries($rangeSet);
if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[0][0]) > $maxCol) {
$rangeBoundaries[0][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol);
}
if ($rangeBoundaries[0][1] > $maxRow) {
$rangeBoundaries[0][1] = $maxRow;
}
if (PHPExcel_Cell::columnIndexFromString($rangeBoundaries[1][0]) > $maxCol) {
$rangeBoundaries[1][0] = PHPExcel_Cell::stringFromColumnIndex($maxCol);
}
if ($rangeBoundaries[1][1] > $maxRow) {
$rangeBoundaries[1][1] = $maxRow;
}
$rangeSet = $rangeBoundaries[0][0] . $rangeBoundaries[0][1] . ':' . $rangeBoundaries[1][0] . $rangeBoundaries[1][1];
}
unset($rangeSet);
$stRange = implode(' ', $rangeBlocks);
return $stRange;
}
/**
* Convert 1-cell anchor coordinates to 2-cell anchor coordinates
* This function is ported from PEAR Spreadsheet_Writer_Excel with small modifications
*
* Calculate the vertices that define the position of the image as required by
* the OBJ record.
*
* +------------+------------+
* | A | B |
* +-----+------------+------------+
* | |(x1,y1) | |
* | 1 |(A1)._______|______ |
* | | | | |
* | | | | |
* +-----+----| BITMAP |-----+
* | | | | |
* | 2 | |______________. |
* | | | (B2)|
* | | | (x2,y2)|
* +---- +------------+------------+
*
* Example of a bitmap that covers some of the area from cell A1 to cell B2.
*
* Based on the width and height of the bitmap we need to calculate 8 vars:
* $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2.
* The width and height of the cells are also variable and have to be taken into
* account.
* The values of $col_start and $row_start are passed in from the calling
* function. The values of $col_end and $row_end are calculated by subtracting
* the width and height of the bitmap from the width and height of the
* underlying cells.
* The vertices are expressed as a percentage of the underlying cell width as
* follows (rhs values are in pixels):
*
* x1 = X / W *1024
* y1 = Y / H *256
* x2 = (X-1) / W *1024
* y2 = (Y-1) / H *256
*
* Where: X is distance from the left side of the underlying cell
* Y is distance from the top of the underlying cell
* W is the width of the cell
* H is the height of the cell
*
* @param PHPExcel_Worksheet $sheet
* @param string $coordinates E.g. 'A1'
* @param integer $offsetX Horizontal offset in pixels
* @param integer $offsetY Vertical offset in pixels
* @param integer $width Width in pixels
* @param integer $height Height in pixels
* @return array
*/
public static function oneAnchor2twoAnchor($sheet, $coordinates, $offsetX, $offsetY, $width, $height)
{
list($column, $row) = PHPExcel_Cell::coordinateFromString($coordinates);
$col_start = PHPExcel_Cell::columnIndexFromString($column) - 1;
$row_start = $row - 1;
$x1 = $offsetX;
$y1 = $offsetY;
// Initialise end cell to the same as the start cell
$col_end = $col_start;
// Col containing lower right corner of object
$row_end = $row_start;
// Row containing bottom right corner of object
// Zero the specified offset if greater than the cell dimensions
if ($x1 >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start))) {
$x1 = 0;
}
if ($y1 >= self::sizeRow($sheet, $row_start + 1)) {
$y1 = 0;
}
$width = $width + $x1 - 1;
$height = $height + $y1 - 1;
// Subtract the underlying cell widths to find the end cell of the image
while ($width >= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end))) {
$width -= self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end));
++$col_end;
}
// Subtract the underlying cell heights to find the end cell of the image
while ($height >= self::sizeRow($sheet, $row_end + 1)) {
$height -= self::sizeRow($sheet, $row_end + 1);
++$row_end;
}
// Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell
// with zero height or width.
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) == 0) {
return;
}
if (self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) == 0) {
return;
}
if (self::sizeRow($sheet, $row_start + 1) == 0) {
return;
}
if (self::sizeRow($sheet, $row_end + 1) == 0) {
return;
}
// Convert the pixel values to the percentage value expected by Excel
$x1 = $x1 / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_start)) * 1024;
$y1 = $y1 / self::sizeRow($sheet, $row_start + 1) * 256;
$x2 = $width / self::sizeCol($sheet, PHPExcel_Cell::stringFromColumnIndex($col_end)) * 1024;
// Distance to right side of object
$y2 = $height / self::sizeRow($sheet, $row_end + 1) * 256;
// Distance to bottom of object
$startCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_start) . ($row_start + 1);
$endCoordinates = PHPExcel_Cell::stringFromColumnIndex($col_end) . ($row_end + 1);
$twoAnchor = array('startCoordinates' => $startCoordinates, 'startOffsetX' => $x1, 'startOffsetY' => $y1, 'endCoordinates' => $endCoordinates, 'endOffsetX' => $x2, 'endOffsetY' => $y2);
return $twoAnchor;
}
/**
* 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.");
}
}
/**
* Update single cell reference
*
* @param string $pCellReference Single cell reference
* @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 reference
* @throws Exception
*/
private function _updateSingleCellReference($pCellReference = 'A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
if (strpos($pCellReference, ':') === false && strpos($pCellReference, ',') === false) {
// Get coordinates of $pBefore
$beforeColumn = 'A';
$beforeRow = 1;
list($beforeColumn, $beforeRow) = PHPExcel_Cell::coordinateFromString($pBefore);
// Get coordinates
$newColumn = 'A';
$newRow = 1;
list($newColumn, $newRow) = PHPExcel_Cell::coordinateFromString($pCellReference);
// Make sure the reference can be used
if ($newColumn == '' && $newRow == '') {
return $pCellReference;
}
// Verify which parts should be updated
$updateColumn = PHPExcel_Cell::columnIndexFromString($newColumn) >= PHPExcel_Cell::columnIndexFromString($beforeColumn) && strpos($newColumn, '$') === false && strpos($beforeColumn, '$') === false;
$updateRow = $newRow >= $beforeRow && strpos($newRow, '$') === false && strpos($beforeRow, '$') === false;
// Create new column reference
if ($updateColumn) {
$newColumn = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($newColumn) - 1 + $pNumCols);
}
// Create new row reference
if ($updateRow) {
$newRow = $newRow + $pNumRows;
}
// Return new reference
return $newColumn . $newRow;
} else {
throw new Exception("Only single cell references may be passed to this method.");
}
}
