CODEWITHSUNDEEP
phpexcelphpspreadsheet
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Saving Excel sheets to CSV is slow

Trying to save all (5 page) sheets to separate csv file named as sheets. This code is very slowly, it takes about 5 minutes to extract 5pages with only 30 rows and 4 columns on each. 

<?php 
require 'vendor/autoload.php';

$xls  = 'test.xls';
echo convertXlsCsv($xls);

function convertXlsCsv($xls)
:bool
{
    $result = FALSE; // DEFAULT
    try {
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($xls);
        $sheetCount = $spreadsheet->getSheetCount();
        $loadedSheetNames = $spreadsheet->getSheetNames();
        for ($i = 0; $i < $sheetCount; $i++) {
            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
            $writer->setDelimiter(';');
            $writer->setEnclosure('');
            $writer->setLineEnding("\r\n");
            $writer->setSheetIndex($i);
            $writer->setUseBOM(true); //Writing UTF-8 CSV files
            $writer->save($loadedSheetNames[$i].'.csv');
            $result = TRUE;
        }
    } catch (Exception $e){
        echo "Error";
    }
    return $result;  
}#endfunc

If I use Xlsx code is not working saying Calculation error

So how to quickly save all pages to csv?

Upvotes: 2

Views: 680

Answers (1)

miken32
miken32

Reputation: 42697

I can confirm it's slow for me as well, about 30 seconds for each sheet. The problem appears to be the use of VLOOKUP. If you reduce the search range to only search the filled in cells instead of the entire column, you'll get much faster performance.

=VLOOKUP(A18, codes!A1:B250, 2, FALSE)

Or, if you ensure that the codes sheet is sorted alphabetically you can use the LOOKUP function:

=LOOKUP(A18, codes!A1:A250, codes!B1:B250)

And it will also perform much more quickly.

Note that for some reason you can't fill down with either of these formulae. They require manual entry of the ranges. :(

Upvotes: 1

Related Questions