Reading an Excel File in PHP

Reading an Excel file in PHP

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

How do I use PHPExcel to read data from an Excel file?

Mark Baker was extremely helpful in guiding me to the right answer. I don't use Composer with PHP (I should probably learn), but given that, in order to get this to work I went to the GitHub page for PHPExcel (https://github.com/PHPOffice/PHPExcel), clicked the green Clone and download button, and then the Download ZIP link.

After unzipping the file, I got a folder called PHPExcel-1.8. I moved that folder to the same folder as both the Excel file I wanted to read (in my code below test.xlsx) and the PHP file that has the code below.

The key to getting it to work was inputting the correct path to the IOFactory.php file. It may seem simple to some, but it was tripping me up.

Given the above and Mark Baker's comments, the following code worked perfectly for me (note the commented parts):

<?php

  //Had to change this path to point to IOFactory.php.
  //Do not change the contents of the PHPExcel-1.8 folder at all.
  include('PHPExcel-1.8/Classes/PHPExcel/IOFactory.php');

  //Use whatever path to an Excel file you need.
  $inputFileName = 'test.xlsx';

  try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
  } catch (Exception $e) {
    die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . 
        $e->getMessage());
  }

  $sheet = $objPHPExcel->getSheet(0);
  $highestRow = $sheet->getHighestRow();
  $highestColumn = $sheet->getHighestColumn();

  for ($row = 1; $row <= $highestRow; $row++) { 
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, 
                                    null, true, false);

    //Prints out data in each row.
    //Replace this with whatever you want to do with the data.
    echo '<pre>';
      print_r($rowData);
    echo '</pre>';
  }

Read XLS in PHP using PhpSpreadsheet

I would check with your Client to see if they are using real Excel or some other spreadsheet.

If they are using some other spreadsheet and exporting using a "Export as Excel" functionality that may explain why its not being recognised by PHPSpreadsheet as any of the possible valid excel formats.

In which case, and depending what is in the spreadsheet, it may be worth asking them to export their spreadsheet as a csv (comma delimited values) file, as that is such a simple format it should be a valid output. You could then read it using fgetcsv() function calls instead of having to use PHPSpreadsheet.

How to read a excel file with merged cells in php?

If you can assume that you read the rows in sequence, you can get the cell value and if the cell is blank, use the previous value. This code use ?: to say if it's blank, use $stylecode ...

$stylecode = $sheet->getCell('H'.$row)->getValue() ?: $stylecode;

PHPExcel: Read Excel File Line by Line

Try this code in place of your for loop

$rows = $sheet->rangetoArray('A1:'.$highestColumn . $highestRow, NULL, True, True, False);
foreach ($rows as $row => $cols) {      
    $line = '';
    foreach($cols as $col => $cell){
        $line .= $cell." ... "; // ... or some other separator
    }
    echo rtrim($line,'. ').'<br />';
 } 

Edited To add {} around inner foreach loop

Edited: Adding 2nd option - for creating tabular data

This works for me and creates a table, using the excel sheet name as the table caption

$sheetname = $sheet->getTitle();
$start=1;
$table = "<table><caption>$sheetname</caption>";
$rows = $sheet->rangetoArray('A'.$start.':'.$highestColumn . $highestRow, NULL, True, True, False);
foreach ($rows as $row => $cols) {      
    $line = '<tr class="'.$row.'">';
    foreach($cols as $col => $cell){
        $line .= "<td>".$cell."</td>"; // ... or some other separator           
    }
    if($row == 0) $line = str_replace("td>","th>", $line);
    $table .= $line.'</tr>';
 } 
$table .= "</table>";.

echo $table;

---

Related Topics