Read Excel File or Sheet (.xls) with PHP
Hi,
If you need to do large data migration from an excel sheet to your MySQL database, the most conventional way is to convert the .xls or .xlsx files to simple CSV(comma seperated file) and read it using PHP file read function. However we can also read a .xls file without converting it to a csv file. We can even navigate through the various worksheets in a single file. Click here to download the entire source code. I have used couple of external class files to achieve this.
Lets have a look at the spreadsheet I am using as example:
Code and Explanation:
First we need to include the main class file in our PHP page and initialise an object for it as follows:
include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
Now we are loading the excel file using the above created object as:
$excel->read('sample.xls');
Now we navigate through the rows and columns of the first worksheet in the excel file and display it as a simple HTML table in the browser:
$x=1;
while($x<=$excel->sheets[0]['numRows']) {
echo "\t<tr>\n";
$y=1;
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
echo "\t\t<td>$cell</td>\n";
$y++;
}
echo "\t</tr>\n";
$x++;
}
In the above sheet[0] is used to read cells from the first work sheet, you can change it according to your needs. Now in sheets[0]['cells'], ['cells'] is a 2D array storing the data as shown in the above screen shot. After executing the entire source code, this is the output that is generated:
Lets have a look at the spreadsheet I am using as example:
First we need to include the main class file in our PHP page and initialise an object for it as follows:
include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
Now we are loading the excel file using the above created object as:
$excel->read('sample.xls');
Now we navigate through the rows and columns of the first worksheet in the excel file and display it as a simple HTML table in the browser:
$x=1;
while($x<=$excel->sheets[0]['numRows']) {
echo "\t<tr>\n";
$y=1;
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
echo "\t\t<td>$cell</td>\n";
$y++;
}
echo "\t</tr>\n";
$x++;
}
In the above sheet[0] is used to read cells from the first work sheet, you can change it according to your needs. Now in sheets[0]['cells'], ['cells'] is a 2D array storing the data as shown in the above screen shot. After executing the entire source code, this is the output that is generated:
