Reading a xlsm file

Topics: User Forum
Oct 17, 2012 at 10:15 PM
Edited Oct 17, 2012 at 10:16 PM

Hey guys,

I hope that you guys can help me with something.

I have a .xlsm file that is around 40MB, i have 4 sheets in it and in the last sheet (DATA) i have 30000 rows.
Now i only want to load the rows with the value R3211 in colom B.

Next i would like to export these rows to the screen (html), but only those rows.

Is this possible?

 

I have tried it many times but i just don't get it, i can write a file but for reader this website is not that clear,...

 

Thanks!

Coordinator
Oct 21, 2012 at 9:44 AM

The main documentation for PHPExcel can be found in the folder called /Documentation and there are also a lot of examples on how to use the library in the /Tests directory, so you aren't limited to this website for reading how to use PHPExcel.

 

You can limit the loader to only read the DATA sheet using

$objReader->setLoadSheetsOnly('DATA');

 

Then you need to iterate through each row looking at the content of Cell B in that row:

$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
for ($row = 1; $row <= $highestRow; ++$row) {
    if ($objPHPExcel->getActiveSheet()->getCell('B'.$row)->getValue() == 'R3211') {
        $rowData = $objPHPExcel->getActiveSheet()->rangeToArray('B'.$row.':'.$highestColumn.$row);
        var_dump($rowData);
    }
}

If cell B in that row contains the text you're intereseted in, use the rangeToArray method to extract the whole row, and output it to screen (formatted as you want it)

Oct 21, 2012 at 10:31 AM

Thanks Mark,

That helped a lot and i am now able to extract the data to the screen.

There is only one problem and that is the size of the file, my script is crashing because it is telling me there is a 'memory leakage'. For this problem i have used the[Chunk] method and the [Data only] method, but this isn't enough. Now i have converted the whole thing to CSV and now it is working fine,..

Problem is that the server can't convert the file from xlsx to csv, i have to do it myself,...

But thanks for everything :)

Coordinator
Oct 21, 2012 at 3:32 PM

Using cell caching may also help: especially if you have sqlite enabled on your server