/**
* init worksheets
*
* @access protected
*/
protected function load_worksheets()
{
//empty the worksheets
$this->_worksheets = array();
foreach ($this->_spreadsheet->getWorksheetIterator() as $i => $worksheet) {
//create default worksheet
$worksheet = new Worksheet($this->_spreadsheet, $worksheet);
$this->_worksheets[$i] = $worksheet;
}
return $this;
}
/**
* Parse file.
* @param array $columns
* @return array
*/
public function parseFile($columns = [])
{
$content = [];
$this->setSelectedColumn($columns);
if (!$this->isParsed) {
$iterator = $this->excel->getWorksheetIterator();
foreach ($iterator as $this->worksheet) {
if ($this->reader->isSelected($iterator->key())) {
$worksheet = $this->parseWorksheet();
if (!empty($worksheet)) {
$title = $this->worksheet->getTitle();
$content[$title] = $worksheet;
}
}
}
}
$this->isParsed = true;
return $content;
}
/**
* Parse the file
* @param array $columns
* @return SheetCollection
*/
public function parseFile($columns = array())
{
// Init new sheet collection
$workbook = new SheetCollection();
// Set the selected columns
$this->setSelectedColumns($columns);
// If not parsed yet
if (!$this->isParsed) {
// Set worksheet count
$this->w = 0;
// Get selected sheets
$iterator = $this->excel->getWorksheetIterator();
// Loop through the worksheets
foreach ($iterator as $this->worksheet) {
// Check if the sheet might have been selected by it's index
if ($this->reader->isSelectedByIndex($iterator->key())) {
// Parse the worksheet
$worksheet = $this->parseWorksheet();
// If multiple sheets
if ($this->parseAsMultiple()) {
// Push every sheet
$workbook->push($worksheet);
$workbook->setTitle($this->excel->getProperties()->getTitle());
} else {
// Ignore the sheet collection
$workbook = $worksheet;
break;
}
}
$this->w++;
}
}
$this->isParsed = true;
// Return itself
return $workbook;
}
/**
* Update named formulas (i.e. containing worksheet references / named ranges)
*
* @param PHPExcel $pPhpExcel Object to update
* @param string $oldName Old name (name to replace)
* @param string $newName New name
*/
public function updateNamedFormulas(PHPExcel $pPhpExcel, $oldName = '', $newName = '')
{
if ($oldName == '') {
return;
}
foreach ($pPhpExcel->getWorksheetIterator() as $sheet) {
foreach ($sheet->getCellCollection(false) as $cell) {
if (!is_null($cell) && $cell->getDataType() == PHPExcel_Cell_DataType::TYPE_FORMULA) {
$formula = $cell->getValue();
if (strpos($formula, $oldName) !== false) {
$formula = str_replace("'" . $oldName . "'!", "'" . $newName . "'!", $formula);
$formula = str_replace($oldName . "!", $newName . "!", $formula);
$cell->setValueExplicit($formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
}
}
}
}
}
/**
* Calculate offsets for Worksheet BOF records.
*
* @access private
*/
private function calcSheetOffsets()
{
$boundsheet_length = 10;
// fixed length for a BOUNDSHEET record
// size of Workbook globals part 1 + 3
$offset = $this->_datasize;
// add size of Workbook globals part 2, the length of the SHEET records
$total_worksheets = count($this->phpExcel->getAllSheets());
foreach ($this->phpExcel->getWorksheetIterator() as $sheet) {
$offset += $boundsheet_length + strlen(PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($sheet->getTitle()));
}
// add the sizes of each of the Sheet substreams, respectively
for ($i = 0; $i < $total_worksheets; ++$i) {
$this->worksheetOffsets[$i] = $offset;
$offset += $this->worksheetSizes[$i];
}
$this->biffSize = $offset;
}
/**
* Render $page into html.
*
* This method is generally called via double-dispatch, as provided by Visitor\VisitableTrait.
*
* @param PageInterface $page
* @return string
* @throws Exception if no tables found in page.
*/
public function visitPage(PageInterface $page)
{
/** @var WritableInterface[] $writables */
$writables = [$page];
/** @var TableInterface[] $tables */
$tables = [];
while ($writables !== []) {
$writable = array_pop($writables);
if (method_exists($writable, 'getWritable') === true && $writable->getWritable() !== null) {
$writables[] = $writable->getWritable();
}
if (method_exists($writable, 'getWritables') === true) {
$writables = array_merge($writables, array_values($writable->getWritables()));
}
if ($writable instanceof TableInterface) {
$tables[] = $writable;
}
}
if ($tables === []) {
throw new Exception("No tables found in writables.");
}
$objPHPExcel = new \PHPExcel();
foreach ($tables as $table) {
// Create a sheet
$objWorkSheet = $objPHPExcel->createSheet();
if (sizeof($table->getRows()) > 0) {
// Write header
/** @var FieldInterface $field */
foreach (array_values($table->getRows()[0]->getWritableBearer()->getWritables()) as $j => $field) {
$cellIndex = static::excelRow($j) . "1";
$objWorkSheet->setCellValue($cellIndex, $field->getLabel());
$objWorkSheet->getStyle($cellIndex)->getFont()->setBold(true);
}
// Write cells
foreach ($table->getRows() as $i => $row) {
foreach (array_values($row->getWritableBearer()->getWritables()) as $j => $field) {
if ($field->getInitial() !== "") {
$cellIndex = static::excelRow($j) . ($i + 2);
$objWorkSheet->setCellValue($cellIndex, $field->getInitial());
}
}
}
} else {
$objWorkSheet->setCellValue("A1", "No records found");
}
}
// Remove worksheet 0; it was created with the file but we never wrote to it
$objPHPExcel->removeSheetByIndex(0);
// Auto size columns for each worksheet
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));
$sheet = $objPHPExcel->getActiveSheet();
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}
}
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$tmp = tmpfile();
$fileLocation = stream_get_meta_data($tmp)['uri'];
ob_start();
$objWriter->save('php://output');
return ob_get_clean();
}
/**
* Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
* the NAME records.
*/
private function _writeExterns()
{
// Create EXTERNCOUNT with number of worksheets
$this->_writeExterncount(count($this->_phpExcel->getAllSheets()));
// Create EXTERNSHEET for each worksheet
foreach ($this->_phpExcel->getWorksheetIterator() as $sheet) {
$this->_writeExternsheet($sheet->getTitle());
}
}
