Member Avatar for LaughingOtter

Hi!
Long time no post, mostly because I've had to put the mobile coding aside for my real job. Plus some home brewing. And lots of pinball.
I have run into a problem. I am developing an application where an Excel spreadsheet (or tab-delimited text file) can be read in and used as data with the MySQL. This works, except:

I want to be able to start with the Excel2007 reader against the imported Excel spreadsheet. If this fails then the Excel5 reader can generally read anything the Excel2007 reader can't.

My big problem is there doesn't seem to be a graceful way of evaluating this. I'm currently trying something like this:

 if (($new_ext=="xls") or ($new_ext=="xlsx")) {
  try {
   $objReader=PHPExcel_IOFactory::createReader('Excel2007');
   $objPHPExcel=$objReader->load($uploadFilename); }
  catch (Exception $e) {
   $objReader=PHPExcel_IOFactory::createReader('Excel5');
   $objPHPExcel=$objReader->load($uploadFilename); } }

Invoking the reader doesn't cause the error, loading it does.

Short of just analyzing the first 128 bytes of the file, is there a way to try another reader if another one fails?

Thanks!
Steve
PS: If you're in Seattle, send me a message and we'll geek out over beer and pinball.

  • 2 Contributors
  • 2 Replies
  • 535 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by LaughingOtter

Dani AI

Generated

Short note: ’s identify() solution is the right idea and exactly what @LastMitch was steering you toward. For brevity you can also use the IOFactory helper that picks the correct reader for a file in one call (no manual identify + createReader step). Wrap reader creation/load in try/catch so you can handle corrupt or unsupported files cleanly. (hitautodestruct.github.io)

Troubleshooting tips: identify() and load() will throw on unreadable/corrupt files, so catch exceptions and report/log the filename and exception message. XLSX (Excel2007+) readers rely on PHP’s Zip support; a missing ZipArchive extension produces the familiar “Class 'ZipArchive' not found” failure — either enable the php-zip extension on the server or use PHPExcel’s PCLZip fallback (with performance tradeoffs). Check file permissions and temporary upload correctness before calling the reader. (stackoverflow.com)

If you expect large files, reduce memory and time by:

  • enabling cell-caching before instantiating PHPExcel,
  • using setReadDataOnly(true) to ignore formatting,
  • limiting sheets with setLoadSheetsOnly(...),
  • and using a read-filter / chunked-load loop to process rows in blocks instead of loading the whole workbook at once.

Example sketch (not the same as the thread code):

// set cell cache before creating/loading
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '16MB'];
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

// automatic reader selection
$reader = PHPExcel_IOFactory::createReaderForFile($uploadFilename);
$reader->setReadDataOnly(true);
$reader->setReadFilter(new MyChunkReadFilter()); // implement IReadFilter
$spreadsheet = $reader->load($uploadFilename);

Use a configurable chunk read filter to loop through row ranges and free each chunk when done. These techniques will greatly reduce peak memory. (phpspreadsheet.readthedocs.io)

One more important note: PHPExcel was officially deprecated and the project archived — for new or long-lived projects, migrate to PhpSpreadsheet (the maintained successor) when feasible. (github.com)

Recommended Answers

All 2 Replies

Member Avatar for Member #949455

I want to be able to start with the Excel2007 reader against the imported Excel spreadsheet. If this fails then the Excel5 reader can generally read anything the Excel2007 reader can't.

Have you post this question on the PHPExcel forum? The reason is that it's their software and product.

I think someone from there can help you with this.

I think the issue you are having is this:

I have used PHPExcel and also help members with this but in your case it's not code related but more related to the file.

Edited by Member #949455 because: grammer
Member Avatar for LaughingOtter

Very close!
A quick look-around from there got me the exact answer I needed:

 if (($new_ext=="xls") or ($new_ext=="xlsx")) {
  $inputFileType=PHPExcel_IOFactory::identify($uploadFilename);
  $objReader=PHPExcel_IOFactory::createReader($inputFileType); 
  $objPHPExcel=$objReader->load($uploadFilename);
  ...blahblahblah...

Thanks for pointing me in the right direction, LastMitch!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.