How to create Excel file from a modified array returned by getSheet->toArray?

Topics: Developer Forum, User Forum
May 23, 2013 at 2:06 PM
Edited May 23, 2013 at 2:07 PM
Hello!

I am using the following bit of code to read a spreadsheet into array, then process it line by line (and when processing a line, mark cells I modified data with text like 'Modified' or 'Error parsing!').


Now that my code to process data is almost complete, I am looking to save the modified array as an Excel file to return to the user for further checks on the results.

How to do this? I was reading the developer documentation, and it only states how to save Excel file that is of a PHPExcel type object - I dont want to use memory twice and write "hacks" to do this, instead of using my current row iterator which goes over the array I process.


Here is the code I use to read the Excel file:
    /**
     * Initiate the class, and read out the datasheet into memory.
     * @param String $filename Filepath+Filename to the XLSX file.
     * @param Bool $ReadFormatting True to read  data WITH formatting. False by default.
     * @param Int $FirstSheet Number of the first sheet to load, 0 is default.
     * @throws Exception If loading the file fails for some reason.
     */
    function __construct($filename, $ReadFormatting = FALSE, $FirstSheet = 0) {
        parent::__construct();
//        iniset('memory_limit', '128M')
//        $this->PHPExcel = PHPExcel_IOFactory::load($filename);
        $this->PHPExcelReader = new PHPExcel_Reader_Excel2007();  // XLSX file reader.
        if ($ReadFormatting === FALSE) {
            $this->PHPExcelReader->setReadDataOnly();
        }
        try {
            $this->ExcelData = $this->PHPExcelReader->load($filename);
            $this->BezeqInstallDataArr = $this->ExcelData->getSheet($FirstSheet)->toArray(self::EMPTY_CELL_STRING, true, true, true);  // Last parameter makes array with column letters instead of numbers A...Z
            $this->BezeqInstallArrLength = count($this->BezeqInstallDataArr);
            if ($this->BezeqInstallArrLength > 2) {
//                $this->iCurrentDataRow = 3; // Bezeq spreadsheet has 2 headers.
                $this->iCurrentDataRow = 2; // Bezeq spreadsheet has 1 headers.
            }
        } catch (Exception $e) {
            throw new ExcelDataNotLoadedException($e->getMessage(), $e->getCode());
        }
    }
Please, any suggestions how to save Excel file composed of the modified array $this->BezeqInstallDataArr?
May 24, 2013 at 11:35 AM
Why twice the memory? When the sheet is copied to your array, "close" (doc.: 4.3) the workbook - that will free the memory - and create a new workbook in which you put the items you want (possibly with fromArray if appropriate).
Coordinator
May 24, 2013 at 8:49 PM
Why copy the file you've loaded to an array? Why not modify that worksheet with your 'Modified' or 'Error parsing!' annotations and then save it (using a different filename if you want to create a modified copy)
May 28, 2013 at 8:58 AM
Thank you both LWol and MarkBaker, important tips about closing it.

It works now, using like this (for anyone else who might need it):
    $this->ExcelData = new PHPExcel();
    $NewWorkSheet = new PHPExcel_Worksheet($this->ExcelData, "Modification Results");
    $NewWorkSheet->fromArray($this->BezeqDataArr, self::EMPTY_CELL_STRING);
    $this->ExcelData->addSheet($NewWorkSheet, 0);
    $ExcelWriter = new PHPExcel_Writer_Excel2007($this->ExcelData);
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="'.$FileName.'"');
    $ExcelWriter->save('php://output');