0

I am getting an error something like below when writing a file

Error: Call to a member function getCacheData() on null 
File E:\websites\d3a-qa\vendor\PHPExcel\PHPExcel\Worksheet.php 
Line: 2554

Line 2554:

public function garbageCollect() {
        // Flush cache
        $this->_cellCollection->getCacheData('A1');

I know this error is coming because $this->_cellCollection is null but I dont know why is it null. I am calling $writer->save($saving_name); where it gives this issue.

I tried doing all sort of things. From backtracing to commenting the disconnectWorksheet code. resetting the variables for PHPExcel object but nothing works.

The funny part is sometimes it works and sometimes it doesn't. There's not much documentation or answers to queries aboout PHPExcel on internet. Therefore at last I have come here so that I could get any answer or at least a hint of what needs to be done.

Thanks in Advance.

EDIT

Here's my code where I am using PHPExcel. It appears that the error is coming at $writer->save($saving_name);.

$parts is an array having several number of XLS that needs to be merged into one XLS sheet.

PS: The error comes only when heavy processing is done (or nearly 45 sheets). For smaller xls counts its working fine.

if(!empty($insertSummary)) {
            $PHPExcel = new \PHPExcel();
            $PHPExcel = $PHPExcel->createSheet();
            $PHPExcel->setTitle(_EXPORT_DES_SUMMARY);
        }

        foreach ($parts as $part) {        
            $bigExcel = '';

            if(!empty($insertSummary))
                $bigExcel = $this->addSummarySheet($insertSummary, $part, $PHPExcel);

            $bigExcel = $this->processPartFiles($part, $bigExcel);

            $writer = \PHPExcel_IOFactory::createWriter($bigExcel, 'Excel5');
            $returnFilename = $dbName . '_' . 'DES' . '_' . date('Y-m-d-h-i-s') . '_' . 'Part_' . ++$count . '.xls';

            // name of file, which needs to be attached during email sending
            $saving_name = _DES_PATH_WEBROOT . DS . $returnFilename;

            $zipFiles[] = $saving_name;
            $writer->save($saving_name);

            //Log::write('debug', 'Line(' . __LINE__ .') memory_get_peak_usage - ' . memory_get_peak_usage(true));
            //Log::write('debug', 'Line(' . __LINE__ .') memory_get_usage - ' . memory_get_usage());
        }

EDIT-2

$this->addSummarySheet()

public function addSummarySheet($insertSummary, $part, $PHPExcel) {
        $keys = array_keys($part);
        $bigExcel = $this->CommonInterface->readXlsOrCsv($insertSummary, false);

        //Copy & paste values of range of cells
        $cellValuesTitle = $bigExcel->getActiveSheet()->rangeToArray('A1:C1');
        $cellValues = $bigExcel->getActiveSheet()->rangeToArray('A' . ($keys[0] + 1) .':C' . ($keys[count($keys) - 1] + 1));
        $bigExcel->removeSheetByIndex(0);

        $bigExcel->addSheet($PHPExcel);
        $bigExcel->getActiveSheet()->fromArray($cellValuesTitle, null, 'A1');
        $bigExcel->getActiveSheet()->fromArray($cellValues, null, 'A2');

        foreach($keys as $rowCount => $sheet) {
            $cellCordinateRow = $rowCount + 2;
            $bigExcel->getActiveSheet()->getCell('A' . $cellCordinateRow)->getHyperlink()->setUrl("sheet://'Data $sheet'!A{$cellCordinateRow}");
        }

        return $bigExcel;
    }

$this->CommonInterface->readXlsOrCsv()

public function readXlsOrCsv($filename = null, $unlinkFile = true, $sheetnames = null) {

        require_once(ROOT . DS . 'vendor' . DS . 'PHPExcel' . DS . 'PHPExcel' . DS . 'IOFactory.php');
        /**  Identify the type of $inputFileName  **/
        $inputFileType = \PHPExcel_IOFactory::identify($filename);
        $objReader = \PHPExcel_IOFactory::createReader($inputFileType);

        if(!empty($sheetnames)) {
            //$objReader->setReadDataOnly(true);
            $objReader->setLoadSheetsOnly($sheetnames);
        }
        $objPHPExcel = $objReader->load($filename);

        //$objPHPExcel = \PHPExcel_IOFactory::load($filename);
        if ($unlinkFile == true)
            $this->unlinkFiles($filename); // Delete The uploaded file
        return $objPHPExcel;
    }
Ashish Choudhary
  • 2,004
  • 1
  • 18
  • 26
  • 1
    This hapless developer abases himself for not providing sufficient documentation (https://github.com/PHPOffice/PHPExcel/wiki/User%20Documentation and http://hitautodestruct.github.io/PHPExcelAPIDocs/) about his useless product, and apologises for never answering queries (http://stackoverflow.com/search?tab=newest&q=%5bphpexcel%5d%20user%3a324584) but perhaps showing a little bit more of your code, or at least telling me what version of PHPExcel you're running, might allow me to at least attempt to guess what is going wrong – Mark Baker Jan 07 '16 at 12:50
  • @MarkBaker I am using v1.8.0. I will edit the Ques with the code where I am using it. – Ashish Choudhary Jan 07 '16 at 12:59
  • Can you show the code for `$this->addSummarySheet();`? – Mark Baker Jan 07 '16 at 16:38
  • I have added the code in question under `EDIT-2` for `addSummarySheet()` and `readXlsOrCsv()`. – Ashish Choudhary Jan 08 '16 at 04:53
  • @MarkBaker. You are in the right direction as when I comment the `$this->addSummarySheet()` the code works fine for larger files as well. The issue is with the `$this->addSummarySheet()` function. I am focusing on debugging this function now. – Ashish Choudhary Jan 08 '16 at 10:30

1 Answers1

0

I've solved the issue I was facing with PHPExcel.

I have changed removed $bigExcel = $this->CommonInterface->readXlsOrCsv($insertSummary, false); from $this->addSummarySheet() and placed it under if(!empty($insertSummary)) { as stated in EDIT 2.

Also, I have added below line of code in $this->addSummarySheet()

$PHPExcelSheet = new \PHPExcel();
$PHPExcelSheet->removeSheetByIndex(0);

$PHPExcel = new \PHPExcel();
$PHPExcel = $PHPExcel->createSheet();
$PHPExcel->setTitle(_EXPORT_DES_SUMMARY);

SO My final code in $this->addSummarySheet() becomes

public function addSummarySheet($insertSummary, $part, $PHPExcel, $summaryExcel) {
        $keys = array_keys($part);
        //$summaryExcel= $this->CommonInterface->readXlsOrCsv($insertSummary, false);

        //Copy & paste values of range of cells
        $cellValuesTitle = $summaryExcel->getActiveSheet()->rangeToArray('A1:C1');
        $cellValues = $summaryExcel->getActiveSheet()->rangeToArray('A' . ($keys[0] + 1) .':C' . ($keys[count($keys) - 1] + 1));

        $PHPExcelSheet = new \PHPExcel();
        $PHPExcelSheet->removeSheetByIndex(0);

        $PHPExcel = new \PHPExcel();
        $PHPExcel = $PHPExcel->createSheet();
        $PHPExcel->setTitle(_EXPORT_DES_SUMMARY);

        $PHPExcelSheet->addSheet($PHPExcel);
        $PHPExcelSheet->getActiveSheet()->fromArray($cellValuesTitle, null, 'A1');
        $PHPExcelSheet->getActiveSheet()->fromArray($cellValues, null, 'A2');

        foreach($keys as $rowCount => $sheet) {
            $cellCordinateRow = $rowCount + 2;
            $PHPExcelSheet->getActiveSheet()->getCell('A' . $cellCordinateRow)->getHyperlink()->setUrl("sheet://'Data $sheet'!A{$cellCordinateRow}");
        }

        return $PHPExcelSheet;
    }

Thanks Mark for providing the $this->addSummarySheet() hint. It truly helped.

Ashish Choudhary
  • 2,004
  • 1
  • 18
  • 26
  • I'm not sure that I did much, besides seeing that the potential errors might lie in that method; but I'm glad you were able to resolve it – Mark Baker Jan 08 '16 at 14:23