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);When i trying to Upload Excel Sheet I am getting below this Error… Its working fine in Wamp…
When I move to Linux Server & PHP Version 5.2.13 getting below this error…
Fatal error: Class ‘COM’ not found in PHP
If you want to have Excel COM available, the MS Office must be installed on the system and it probably is not on Linux…
Even I am getting following error,
Fatal error: Cannot instantiate non-existent class: com
Is there any workaround for using Excel COM on Linux machine?
Andy, please take a look at my last reply to Raja’s comment, right above yours. You can also find more answers on this error here in these comments…
Hi Teddy, this post has helped me literally no-end.. I’ve been using it to strip data out of a spreadsheet, load it into an array then pump it into an html page. However… I’ve just installed Office 2010 and suddenly my script won’t execute. I’m left with error ‘Microsoft Excel can’t access filename.xlsx. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook’, relating to the line $excel->Workbooks->Open($file) etc;
I have a feeling this is purely because of Excel 2010 (upgraded from 2007).. this subject is not at all documented online so I was wondering if you had any experience with 2010 making the script fail.
Thanks in advance!
Steve
Hi Steve. I’m sorry you’ve encountered this issue but I have no experience with Office 2010, still using 2003 (I use to write more programs than documents in Office :-)). It looks as if new Office would handle new files bit differently… May be someone else here could post a clue… Anyway, I’ll try to ask my friends and once I’ll have something it’ll be posted here immediately.
While is is very cool to be able to do, for most cases it seems it would be a far easier solution to save / export your excel data to a CSV and import it in MySQL.
Hi i tried your code in a machine with windows 7 and office 2007, and it works, but in another machine with te same OS and same excel it doesn’t works, in both computers i used xamp, the message that appears on screen is:
Fatal error: Uncaught exception ‘com_exception’ with message ‘Source: Microsoft Office ExcelDescription: No se puede obtener acceso al archivo ‘C:\Users\jhernandez\Documents\Centiva\BDConsupago.xls’. Puede haber varios motivos: � El nombre del archivo o la ruta no existen. � Otro programa est� usando el archivo. � El libro que est� intentando guardar tiene el mismo nombre que otro libro que est� abierto en estos momentos.’ in C:\xampp\htdocs\consupago\prueba\restaFechas.php:15 Stack trace: #0 C:\xampp\htdocs\consupago\prueba\restaFechas.php(15): variant->Open(‘C:\Users\jherna…’) #1 C:\xampp\htdocs\consupago\prueba\restaFechas.php(42): getDataFromExcel(‘C:\Users\jherna…’, 1, Array, Array) #2 C:\xampp\htdocs\consupago\prueba\restaFechas.php(62): altaUsuarios(‘C:\Users\jherna…’, 2, 1621, 1) #3 {main} thrown in C:\xampp\htdocs\consupago\prueba\restaFechas.php on line 15
Sounds like people are giving you a hard time Teddy! To the various people complaining about errors you should maybe try reading either the comments or the PHP documentation before complaining.
Anyway, Teddy, you should really wrap anything to do with COM in a try catch block and if an exception is caught then close the application otherwise you will be left with hundreds of instances of the application running on your server. Same goes with where you are using die, this is bad and will cause your server to crash.
You should also use ignore_user_abort(true) as users who stop the page if it is taking time to work with a large spreadsheet(more than likely with COM). In addition to this you should check to see if the user has aborted and if so then close the application and then end the execution.
Other people may be interested in noting that the correct permissions need to be set up in Windows before they can use COM like so.
There are also various wrappers for reading and writing to spreadsheets that deal with all of this so you don’t have to write the code.
Thanks Matthew for your constructive comment. My article has explanatory and demonstrative purpose only, so I didn’t want to mess up the code with related stuff which could be usefull but make core code unclear on the other hand…
Hello, I have this spreadsheet http://cid-5054dd243c01e71b.office.live.com/view.aspx/.Documents/Livro1.xlsx (public on the web).
if I input two values (example 15 and 0,817)
I would like to verify the spreadsheet above
and return the value 0,8135
because I found 15 in the Column 1, Row 9
and I found 0,817 in the Column 9, Row 1
So I would like to have the value in the Cell(9,9) from the spreadsheet which is 0,8135
In another webpage (my blog, for example), How could I get 0,8135
Using javascript or other ?
I appreciate any help about this issue.
Thank you for your time,
Flavio
is it possible to create a web page where i can view the excel file and save the changes to excel file usibg php code . the thing is the user needs to open a excel file in alocal system not with the browser . if its possible means let me know the solution.
hi, i’m tried your code, but gett the error like this :
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\USO\com.php on line 5
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\USO\com.php on line 7
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\USO\com.php on line 8
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\USO\com.php on line 9
Fatal error: Uncaught exception ‘com_exception’ with message ‘Source: Microsoft ExcelDescription: Microsoft Excel cannot access the file ‘E:\new\als.xlsx’. There are several possible reasons: • The file name or path does not exist. • The file is being used by another program. • The workbook you are trying to save has the same name as a currently open workbook.’ in C:\xampp\htdocs\USO\com.php:12 Stack trace: #0 C:\xampp\htdocs\USO\com.php(12): variant->Open(‘E:\new\als…’) #1 C:\xampp\htdocs\USO\com.php(50): getDataFromExcel(‘E:\new\als…’, 2, Array, Array) #2 {main} thrown in C:\xampp\htdocs\USO\com.php on line 12
i’m using php 5.3.0 and windows 7, and ms.excel 2010
,,
is it something to do with php version i’m using??
thx b4..
Notice: Use of undefined constant STDOUT – assumed ‘STDOUT’ in C:\xampp\htdocs\kapil\excel.php on line 5
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\kapil\excel.php on line 5
Notice: Use of undefined constant STDOUT – assumed ‘STDOUT’ in C:\xampp\htdocs\kapil\excel.php on line 7
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\manish\excel.php on line 7
Notice: Use of undefined constant STDOUT – assumed ‘STDOUT’ in C:\xampp\htdocs\kapil\excel.php on line 8
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\kapil\excel.php on line 8
Notice: Use of undefined constant STDOUT – assumed ‘STDOUT’ in C:\xampp\htdocs\kapil\excel.php on line 9
Warning: fwrite() expects parameter 1 to be resource, string given in C:\xampp\htdocs\kapil\excel.php on line 9
Notice: Undefined variable: string in C:\xampp\htdocs\kapil\excel.php on line 63