When we are doing large projects,their may arise situation like we have to import data to our database from Excel sheets. This is a basic requirement which is asked by all clients.
So here iam explaining how to extract data from an excel sheet simply using php.
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.
You can get source library from this link Dowload here
I will explain it with an example...
Iam converting the below excel sheet to php format :-
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:
so code will be:-
1) download library file from the source i provided earlier..
2)the sample file would be like this:-
<html>
<head>
<style type="text/css">
table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}
</style>
</head>
<body>
<?php
include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
?>
Sheet 1:<br/><br/>
<table>
<?php
$excel->read('record.xls');
$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++;
}
?>
</table><br/>
?>
</table>
</body>
</html>
So here iam explaining how to extract data from an excel sheet simply using php.
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.
You can get source library from this link Dowload here
I will explain it with an example...
Iam converting the below excel sheet to php format :-
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:
1) download library file from the source i provided earlier..
2)the sample file would be like this:-
<html>
<head>
<style type="text/css">
table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}
</style>
</head>
<body>
<?php
include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
?>
Sheet 1:<br/><br/>
<table>
<?php
$excel->read('record.xls');
$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++;
}
?>
</table><br/>
?>
</table>
</body>
</html>
No comments:
Post a Comment