Many of my customers hold data in Excel files. Then, when they decide to build a web based, database driven project controlled by PHP, ask me to move their Excel data into the MySQL. Whether it’s possible and how to do it, will be the content of this post.
Since the PHP allows you to create an OLE compatible COM object with its methods and properties, the solution is more than easy. All you need is take a brief look into the PHP COM Manual Pages to be able to understand the following explanatory example. I wrote this code as a PHP CLI script which seems to me more usable for interaction with other applications.
Before we start, let’s introduce input parameters of the function which is responsible for data retrieve from an Excel file and output it as a matrix, representing the Excel table.
The names of variables were selected to represent their meaning (semantic names) and facilitate the understanding of script work. But if you’re still confused of input parameters or output, don’t be affraid, following examples will clarify it more. So, let’s move forward to the PHP and Excel interaction.
function getDataFromExcel($file, $sheet, $rows, $cols)
{
// COM CREATE
fwrite(STDOUT, "----------------------------------------\r\n");
$excel = new COM("Excel.application") or die ("ERROR: Unable to instantaniate COM!\r\n");
fwrite(STDOUT, "Application name: {$excel->Application->value}\r\n") ;
fwrite(STDOUT, "Loaded version: {$excel->Application->version}\r\n");
fwrite(STDOUT, "----------------------------------------\r\n\r\n");
// DATA RETRIEVAL
$Workbook = $excel->Workbooks->Open($file) or die("ERROR: Unable to open " . $file . "!\r\n");
$Worksheet = $Workbook->Worksheets($sheet);
$Worksheet->Activate;
$i = 0;
foreach ($rows as $row)
{
$i++; $j = 0;
foreach ($cols as $col)
{
$j++;
$cell = $Worksheet->Range($col . $row);
$cell->activate();
$matrix[$i][$j] = $cell->value;
}
}
// COM DESTROY
$Workbook->Close();
unset($Worksheet);
unset($Workbook);
$excel->Workbooks->Close();
$excel->Quit();
unset($excel);
return $matrix;
}
Now, when the key function is defined we can fire an extraction and insertion process:
// define inputs
$xls_path = "C:\\Users\\Teddy\\Documents\\Temp\\excel_data.xls"; // input file
$xls_sheet = 1; // sheet #1 from file excel_data.xls
$xls_rows = range(2, 270, 1); // I want extract rows 2 - 270 from excel_data.xls with step 1 row
$xls_cols = array("A", "B", "C", "D", "E", "F"); // I want to extract columns A - F from excel_data.xls
// initiate MySQL connection
mysql_connect("server", "username", "password") or die("Unable to connect MySQL server!");
mysql_select_db("database") or die("Unable to select requested database!");
// retrieve data from excel
$data = getDataFromExcel($xls_path, $xls_sheet, $xls_rows, $xls_cols);
// insert retrieved data into database
foreach ($data as $line)
{
$i = 0;
foreach ($line as $col => $entry)
{
// create the SET string for INSERT query
$i++;
$string .= "`" . $col . "` = '" . $entry . "'";
if ($i < count($line))
$string .= ", ";
}
mysql_query("INSERT INTO `table` SET " . $string . "");
}
The stated above example is simplified to emphasize the core of process, not necessary details may lead to unclear interpretation. It is supposed that readers have at least basic knowledge of PHP and MySQL.
So, as you can see there’s pretty simple way how to import data from excel file directly to database using PHP. The PHP COM interface allows you to do the same with many other types of application (Word, PowerPoint, etc.).
When trying to close the excel com , by means of
$Workbook->Close();
the excel process still remains in Task Manager and usually the browser hang, like is waiting for something.
To solve this use
$Workbook->Close(false);
thus the process will close.
The “false” means not to save changes in the opened excel.
Cheers
i used this code for excel upload in database but it’s giving
PHP Fatal error: Class ‘COM’ not found…..Please give me the solution for this error …
What OS and PHP version are you running?
Dear sir,
I am using Fedora core 9.0 and PHP5.0
Unfortunately, I personally do not have any experience with Microsoft Office installed on Fedora Core 9.0…
Hi, I have the same error as saurabhjain.
The page show Fatal error: Class ‘COM’ not found ………and I am using php5.0 and windows……..can you show me the solution?
Do you have Microsoft Office successfully installed?
why is that i have errors on line 5 7 8 9 Warning: fwrite(): supplied argument is not a valid stream resource in C:\xampp\htdocs\test\xls-mysql\Untitled-11.php
Hello,
How can i import excel document with unicode characters. It shows ‘?’ when i want to output ə (& #601;)
i want’ make it flexible an automatic to count data record it self.. when i don’t know how much record data in XLS file, can i get it with some function like num_rows or etc. do XLS COM have fuction like that?
Please can anybody let me know if there exists any controls or plug-ins in PHP with the help of which we can create pivot charts and pivot tables in PHP..???????
I get the following error “Microsoft Office Excel cannot access the file”.
he finds it but can’t open it???
I try your code but there is a bug like this:
Warning: fwrite(): supplied argument is not a valid stream resource in C:\xampp\htdocs\backlink\1\exel.php on line 5
Warning: fwrite(): supplied argument is not a valid stream resource in C:\xampp\htdocs\backlink\1\exel.php on line 7
Warning: fwrite(): supplied argument is not a valid stream resource in C:\xampp\htdocs\backlink\1\exel.php on line 8
Warning: fwrite(): supplied argument is not a valid stream resource in C:\xampp\htdocs\backlink\1\exel.php on line 9
Please tell me how tofix it be send me email. thank’s
It’s not a bug of my code, Arif… I never past untested code here. Your problem could probably be solved by reading and understanding the PHP fwrite() function as described in the PHP manual.
Thank you for sharing. It is a great reference. I am glad to see that php can communicate with excel through its COM objects.
As a suggestion to accessing ranges, mainly these ranges are some sort of either db or web queries, they come with defined names. Most of the times, you are opening and closing the file in the background, and don’t see the actual address of the range. Nonetheless, you know the name. Or if you don’t know the name, you can loop through the names and assign the objects to php variables.
For that, you can request the dimension of a range by using $range->rows->count and $range->columns->count. Once you have that, you can loop through the multidimensional range and store it in a php array. This could be done like this:
$e_app= new com("Excel.application"); $wrkBook = $e_app->workbooks->open("abc.xls"); $wrkSht = $wrkBook->worksheets("xyz"); $rr = $wrkSht->range(""); $rows = $rr->rows->count; $cols = $rr->columns->count; for($i=1;$i<=$rows;$i++){ for($j=1; $jcells($i,$j)->value); } }even better, MS Excel QueryTable returns a range called ResultRange. You assign an object to the rusultrange and manipulate it the same way you manipulate the above range.
As for inserting into a db, you may use:
Good luck. This is a very interesting topic.
Thank you for the wonderful piece of code. I am in a hurry to complete a project and your code saved me hours of R&D, esply since i am new to php.
Here is the code i used that finally seems to fix the hanging process issue. There are a couple of things to note. First, I’m using “Excel.sheet” as opposed to “Excel.application”; not sure what the difference is, but it works. Second, when I tried using Workbooks->Close(), it never worked, so instead I use an extra call to ActiveWorkbook->Close(). My only guess as to what’s happening here is that when you make the initial call to the component object model, it opens a default new workbook, so if you open a workbook, you have 2 workbooks open, so you need to close two!
$xl = new COM("Excel.sheet") or Die ("Can't open Excel"); $wkb = $xl->Application->Workbooks->Open($workbook) or Die("Can't open workbook"); // add your code to manipulate the spreadsheet here $wkb->Close(false); $xl->application->ActiveWorkbook->Close(false);