Assortment, PHP

Reading and Writing data from Excel Sheets to Database With PHP

Many web applications require data to be uploaded from time to time and most of them are supposed to be stored in the form of tables in the database for future use. In order to make all this simple PHP has a separate libraries called PHP EXCEL READER and SPREADSHEET EXCEL WRITER. The data should be stored in excel sheets in .xls formats. And the library files are to be downloaded and to be included in the main folder

data from excel

1.Download the library files from the following links
2. Getting the library files ready
We need to do some small modifications in the library files to make it work without errors . Download PHP EXCEL READER and SPREADSHEET EXCEL WRITER and unzip the packages and maintain two separate folders one for excel reader and one for spreadsheet writer  in the respective main folder.
Make a folder for excel reader and name it Excel or something(Give a proper name since we need it in specifying the path in the code) and copy the files oleread.inc and reader.php from the unzipped excelreader package and make sure you copy only those two files (Since others are not needed).
In the reader.php file replace the line “require_once ‘Spreadsheet/Excel/Reader/OLERead.php’;”
with
“require_once ‘Spreadsheet/Excel/Reader/OLERead.inc’;”
And find the below function around line no 259 in the same reader.php
“function Spreadsheet_Excel_Reader()
{
$this->_ole =& new OLERead();
$this->setUTFEncoder(‘iconv’);
}”
and alter the above function like the below code
“function Spreadsheet_Excel_Reader()
{
$t = new OLERead();
$this->_ole =& $t;
$this->setUTFEncoder(‘iconv’);
}”
We are done with altering the excel reader files. Now lets make a separate folder called Spreadsheet for the spreadsheet excel writer. Inside the spreadsheet folder create another folder called excel inside the excel folder create two folders called Reader and Writer . Also paste the Writer.php file from the spreadsheet writer package inside this folder.Copy the oleread.inc file from the excel reader package and paste it inside the reader folder. Extract the contents of the e Writer folder from the spreadsheet writer package (the one from the unzipped downloaded folder) and put all the files in the Writer folder(the one we created in the main folder)  you should have BIFFwriter.php, Format.php, Parser.php, Validator.php, Workbook.php, and Worksheet.php in the Writer folder
In short the folder path would be Spreadsheet–>Excel–> Reader folder , Writer folder and Writer.php file
Reader folder –> oleread.inc (From the Excelreader package)
Writer folder –>BIFFwriter.php, Format.php, Parser.php, Validator.php, Workbook.php, and Worksheet.php (From the Spreadsheet writer package)
Now al the library files are ready
Create a separate folder called Upload to save the uploaded excel files
3. Write the HTML code for upload file like below

Filename:



4.The Uploadfile PHP code could be coded like below

<?php
error_reporting(0);
require_once 'Excel/reader.php';

// Use SQL queries and select the table from the DB where the excel file data should be saved

$count=mysql_num_rows($result);
//Count the number of rows from the table and store it in a variable

//here the file extension, type etc are checked and data are extracted from the sheet with the help of library files and stored in separate folder called upload

($count==1)
{
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "
";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "
";
echo "Type: " . $_FILES["file"]["type"] . "
";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB
";
echo "Stored in: " . $_FILES["file"]["tmp_name"];
}
$allowedExts = array("xls");
$extension = end(explode(".", $_FILES["file"]["name"]));
if (($_FILES["file"]["type"] == "text/xls") && ($_FILES["file"]["size"] < 20000) && in_array($extension, $allowedExts))
{
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "
";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "
";
echo "Type: " . $_FILES["file"]["type"] . "
";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB
";
echo "Stored in: " . $_FILES["file"]["tmp_name"];
}
}
else
{
echo "Invalid file";
}
if (file_exists("upload/" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],"upload/" . $_FILES["file"]["name"]);
//echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
}

//Object is created for spreadsheet excel reader class and the data are stored in the table in the db make sure the path name of the file is given correctly

$data = new Spreadsheet_Excel_Reader ("upload/". $_FILES["file"]["name"]);
$data->setOutputEncoding('CP1251');
$data->read ("upload/" . $_FILES["file"]["name"]);

//The X value is associated with the number of columns needed to be saved in DB here there are 3 columns to be inserted so the value is 2 the vname1 2 and 3 are temporary name that save the data from excel sheet and which we pass into the table through SQL query

for ($x = 2; $xsheets[0]["cells"]); $x++)
{
$vname1 = $data->sheets[0]["cells"][$x][1];
$vname2 = $data->sheets[0]["cells"][$x][2];
$vname3 = $data->sheets[0]["cells"][$x][3];

$sql = "INSERT INTO $tablename (columnname1, columnname2, columnname3)
VALUES ('$vname1','$vname2','$vname3')";

mysql_query($sql);
}
mysql_close($con)
?>

The code works like this the upload code checks the file extension, size, and type and uploads the file in a separate folder called upload from which the library file extracts the data and insert it into database under a particular table name. To ensure working of the code the path names of all the files are to be specified correctly. And mention the line  ” error_reporting(0);” in the code to avoid error messages. Add the modified library files in the folder add the upload code and link it correctly it would ensure smooth running of the code.

1 thought on “Reading and Writing data from Excel Sheets to Database With PHP”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s