Posts Tagged ‘EXCEL’

Excel & PHP (again…)

Monday, August 24th, 2009

Damn! It’s been a day and I just keep finding that although some of the COM functions for Excel-PHP are similar to those of ASP there are some that still are hard to get.
First of all to set the value of a cell we have 3 different ways:
Setting the Value property, setting the Formula and setting the FormulaR1C1.
The thing is that when trying to set the FormulaR1C1 with a value that uses de RC syntax the thing throws an unknown exception!!!
This is useless since I can’t figure what the hell is going on!
Also there’s little or none info about Excel COM & PHP on the web…. crap!
As soon as I find whats wrong Ill post it here….

Found something interesting.
A formular1c1 declared like “=SUM(R[-2]C:R[-1]C)” in common VB should look like “=SUM(F(-2)C:F(-1)C)”… weird but I think Im getting closer to a final solution.
Now suppose I have Excel and I have a spanish version where the formula SUM should be SUMAformular1c1 should work with SUM but doing:

$cell->FormulaR1C1 = “=SUM(F(-2)C:F(-1)C)”;

Does not work…. Like Poco I’ll keep on tryin’ 😉

Excel & PHP

Sunday, August 23rd, 2009

These past days I’ve been working with some integrations of some applications and PHP (Crystal reports, Excel, Word, etc).
A few days I found a question in a mailgroup asking somethin about Excel & PHP.
I’ve done something like this but didn’t publish an explanation of how I did it.
I did this through COM objects. I could recommend you to read the manual so that you may learn how to do it but unfotunately there’s few info about this.
So here it is, a sample code with the basic functions.
I learned a lot while searching through Google so do the same, document everything and more important: SHARE!

What you’ll need:
1. Com objects
2. PHP
3. An Excel document.
4. A web server with the proper dll’s or excel installed on it (this I didn’t dig too much so NE comments would be appreciated)

$path = “C:\\Documents and Settings\\Spiro\\My Documents\\sitios\\reto\\cotizaciones\\importados\\”; //set the path to the document

$encabezados = array(); //set a headers array

$exlObj = new COM(“Excel.Application”) or Die (“Did not connect”); //Here we stablish a COM object connection, you may want to set a try – catch to handle any error
$exlObj->Workbooks->Open(“$path$fnombre”); //We open the file set in $fnombre
$exlBook = $exlObj->ActiveWorkBook; //Open the workbook
$exlSheets = $exlBook->Sheets;
$cuantas_hojas = $exlSheets->Count; //how many sheets?
/*In my case there was a need to locate a named range so you may use these part of the script to locate the sheet where the range is.*/
try {
$donde_rango = $exlObj->Range(‘db’)->Worksheet->Name;
catch(Exception $e) {
$donde_rango = ”;
$log_errores[] = “El documento no contiene el rango ‘DB'”; //Info for error log
if ( $donde_rango != ” or true ) {
$sheet = $exlBook->Worksheets($donde_rango); //move to the sheet where the range is
$exlRange = $sheet->Range(‘DB’); //Get the range
$firstAddress = $exlRange->Address; //Get the range address – something like B3:X27 – dont mind about the letters 😉
//Get the upper left corner
$tokstart = strtok($firstAddress,”:”);
//Get the lower right corner
$tokend = strtok(“:”);
//This is why you dont bother about the range having letters
$range_start = $sheet->range($tokstart);
$colstart = $range_start->column;
$rowstart = $range_start->row;
$range_end = $sheet->range($tokend);
$colend = $range_end->column;
$rowend = $range_end->row;
//in case you wish to print the excel document as a nice html table
echo “<table>”;
echo “<thead>”;
echo “<tr>”
//suppose the firs line is where the headers are defined
$fila = $rowstart;
for ( $columna = $colstart; $columna <= $colend; $columna++ ) {
$valor = $sheet->Cells($fila, $columna);
echo “<th>$valor</th>”;
echo “</tr>”;
echo “</thead>”;
//start printing the content
echo “<tbody>”;
for ( $fila = $rowstart + 1; $fila <= $rowend; $fila++ ) {
echo “<tr>”;
for ( $columna = $colstart; $columna <= $colend; $columna++ ) {
$valor = $sheet->Cells($fila, $columna);
echo “<td>$valor</td>”; //print cell content
echo “</tr>”;
echo “</tbody>”;
echo “</table>”;
//close everything


So as you may see its not that difficult once you know where to start.
Take notice that this script only reads an Excel file, if you would like to write one then you should consider find info on Google.

The quickest term to find Google results is:

Hint: notice that you’ll be using COM objects so the code is very similar for ASP and PHP