Asked  7 Months ago    Answers:  5   Viewed   46 times

I'm trying to read an Excel file (Office 2003). There is an Excel file that needs to be uploaded and its contents parsed.

Via Google, I can only find answers to these related (and insufficient topics): generating Excel files, reading Excel XML files, reading Excel CSV files, or incomplete abandoned projects. I own Office 2003 so if I need any files from there, they are available. It's installed on my box but isn't and can't be installed on my shared host.

Edit: so far all answers point to PHP-ExcelReader and/or this additional article about how to use it.

 Answers

87

I use PHP-ExcelReader to read xls files, and works great.

Wednesday, March 31, 2021
 
Deyson
answered 7 Months ago
31

Filesize isn't a good measure when using PHPExcel, it's more important to get some idea of the number of cells (rowsxcolumns) in each worksheet.

If you have no need for styling, are you calling:

$objReader->setReadDataOnly(true);

before loading the file?

If you don't need to access all worksheets, or only certain cells within a worksheet, look at using

$objReader->setLoadSheetsOnly(array(1,2))

or

$objReader->setLoadSheetsOnly(1)

or defining a readFilter

Are you using cell caching? If so, what method? That slows down the load time.

Wednesday, March 31, 2021
 
koenHuybrechts
answered 7 Months ago
38

Are you sure that it's fopen that's failing and not your script's timeout setting? The default is usually around 30 seconds or so, and if your file is taking longer than that to read in, it may be tripping that up.

Another thing to consider may be the memory limit on your script - reading the file into an array may trip over this, so check your error log for memory warnings.

If neither of the above are your problem, you might look into using fgets to read the file in line-by-line, processing as you go.

$handle = fopen("/tmp/uploadfile.txt", "r") or die("Couldn't get handle");
if ($handle) {
    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        // Process buffer here..
    }
    fclose($handle);
}

Edit

PHP doesn't seem to throw an error, it just returns false.

Is the path to $rawfile correct relative to where the script is running? Perhaps try setting an absolute path here for the filename.

Monday, June 7, 2021
 
scessor
answered 5 Months ago
59

Ok...the names are deceiving. setActiveSheetIndex also does a get so the solution was this

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("cmt_school_data.xlsx");
$objWorksheet = $objPHPExcel->setActiveSheetIndex(1);
//objWorksheet = $objPHPExcel->getActiveSheet();
echo '<table border=1>' . "n";
foreach ($objWorksheet->getRowIterator() as $row) {
  echo '<tr>' . "n";
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
                                                     // even if it is not set.
                                                     // By default, only cells
                                                     // that are set will be
                                                     // iterated.
  foreach ($cellIterator as $cell) {
    echo '<td>' . $cell->getValue() . '</td>' . "n";
  }
  echo '</tr>' . "n";
}
echo '</table>' . "n";
Wednesday, July 28, 2021
 
Juriy
answered 3 Months ago
29

By default Excel bases the data type for a column on the first 8 rows of data. To change this, you need to update the registry key:

HKLMSoftwareMicrosoftOffice12.0Access Connect EngineEnginesExcel

with the number of rows you want Excel to scan. Set the value to 0 to have it scan all the rows. Note that setting it to zero can slow the Excel loads down a bit, especially on large spreadsheets.

Also, if you are also using Excel 2003, you need to update a second registry key:

HKLMSOFTWAREMicrosoftJet4.0EnginesExcel

Tuesday, August 24, 2021
 
Mirko
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :