Not reading xlsx file

Topics: Developer Forum, Project Management Forum, User Forum
Aug 31, 2010 at 8:12 AM

Hi,

I got a prob while reading .xslx file.

The file has around 100 columns and 20000 rows.

Didn't get any output or error.

Kindly advice.

Thanks in advance.

 

- Prema

 

Coordinator
Sep 1, 2010 at 12:16 PM

This doesn't really give us much to help with identifying any problem.

Are you suppressing any errors? what code are you using to read the file? How do you know it's not being read correctly? Is there anything in the xlsx file that might be breaking PHPExcel? We can try to help, but only if you give us something to work with.

Sep 2, 2010 at 5:46 AM

Thanks Mark.

We are not suppressing errors.

How to read more than 10MB size of  .xlsx files ?

Will PHPExcel support more than 10MB .xlsx file to .xls conversion ?

Kindly advice as soon as possible.

Thanks

 

- Prema

 

 

 

 

Coordinator
Sep 2, 2010 at 10:22 AM
Premalatha wrote:

We are not suppressing errors.

 If memory was your issue, I'd still expect to see PHP's standard error message for exceeding the permitted memory.

Premalatha wrote:

How to read more than 10MB size of  .xlsx files ?

Will PHPExcel support more than 10MB .xlsx file to .xls conversion ?

Kindly advice as soon as possible.

PHPExcel doesn't limit size, but PHP itself does limit the memory resources available for scripts, and this can cause problems when trying to work with large files.

File size isn't a good measure for workbook files when working with PHPExcel. The number of rows and columns (ie cells) is more important.

The PHPExcel code itself has a footprint of between 10 and 25MB, depending on which components are being accessed.

At present, each cell in a workbook takes on average 1k of memory (without any caching) or 1.6k on 64-bit PHP - I'll assume 32-bit PHP for the moment - so (for example) a worksheet of 20,000 rows with 100 columns (2,000,000 cells) will be about 1.9GB. With cell cacheing (such as php://temp or DiskISAM), that can be reduced to about a third, so the 20,000 rows by 100 columns will require about 650MB.

The writers may double that requirement.

There are a number of options available to help you reduce the memory usage:

Are you using cell caching with PHPExcel?

require_once './Classes/PHPExcel.php'; 
 
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; 
$cacheSettings = array( ' memoryCacheSize ' => '8MB'); 
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 
 
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objPHPExcel = $objReader->load("test.xlsx"); 

If you only need to access data in your worksheets, and don't need access to the cell formatting, then you can disable reading the formatting information from the workbook:

$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setReadDataOnly(true); 
$objPHPExcel = $objReader->load("test.xlsx"); 

If you only need to access some, but not all of the worksheets in the workbook, you can load only those worksheets:

$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setLoadSheetsOnly( array("Worksheet1", "Worksheet2") ); 
$objPHPExcel = $objReader->load("test.xlsx"); 

if you only want to read certain cells within worksheets, you can add a filter:

class MyReadFilter implements PHPExcel_Reader_IReadFilter 
{ 
    public function readCell($column, $row, $worksheetName = '') { 
        // Read title row and rows 20 - 30 
        if ($row == 1 || ($row >= 20 && $row <= 30)) { 
            return true; 
        } 
 
        return false; 
    } 
} 
 
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); 
$objReader->setReadFilter( new MyReadFilter() ); 
$objPHPExcel = $objReader->load("test.xlsx"); 

All of these techniques can significantly reduce the memory requirements.