Numeric Overflow converting Excel 2007 to CSV

Topics: Developer Forum, Project Management Forum, User Forum
Jul 28, 2011 at 2:12 PM


I have some very simplistic code which I am using to save an Excel 2007 file as a CSV. I am using PHPExcel 1.7.6.

It seems that any field with a long number in it is written to the CSV as 2147483647. I have tried increaing the PHP precision value to 30 but this doesn't seem to make any difference.

Can someone please give me any pointers as to errors/workarounds.



//require_once 'PHPExcel/PHPExcel/IOFactory.php';
require_once 'PHPExcel/PHPExcel.php';

ini_set('precision', 30);

  $filename = "myfile.xlsx";
  $outFile = dirname($filename)."/".basename(basename($filename, ".xls"), ".xlsx").".csv";

  // get the number of the excel sheet to read
  $excel_sheet = 0;

  // open the Excel file in the reader
  echo "Load from Excel file : $filename";
  $objReader = PHPExcel_IOFactory::createReaderForFile($filename);
  $worksheetNames = $objReader->listWorksheetNames($filename);

  // only read the required worksheet
  echo "Loading Worksheet : $worksheetNames[$excel_sheet]";
  // load the filena
  $objPHPExcel = $objReader->load($filename);

  echo "Write to CSV format : $outFile";
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

  // Echo memory peak usage
  echo "Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB";

  // Echo done
  echo "Done writing files.";

Jul 28, 2011 at 2:21 PM

By the way, this behaviour isn't evident in an Excel 97 file (*.xls)

Aug 2, 2011 at 10:07 AM

*bump* Anyone any ideas, please??

Aug 2, 2011 at 1:15 PM

I get this too. As great as PHPExcel is, there is some flaw with it where the other readers automatically set these values as integers on read, 32-bit systems (as many are) somehow cannot handle "large" integers, and there seems no way to access these values raw at present with PHPExcel so that we can at least set them as strings and/or do something smarter with them...

Aug 2, 2011 at 1:55 PM

I'm actually running PHPExcel on a 64bit system so it shouldn't be an issue!

Bear in mind that if you convert the file to an Excel 97 file format then the number 'overflow' doesn't occur. So it must be something internal to the Excel2007 reader.