PHP and Excel

[firedrop84]

I have an existing excel spreadsheet that has all my calculations and formulas on it for report generation. Was wondering if it was possible to export the database query results directly into the spreadsheet (and into the cells required) so that my calculations can be done for my report generation. And then enable this file to be downloaded by the user.

Any help is much appreciated as I’m only just getting started.

Have a good day.

[Toni_montana]

there are many scripts around that are able to export mysql to excel. You might wanna search google for it. It's easyer than writing it yourself.

good luck

rogier

[firedrop84]

thank you for your replay I have done already some search on Google and I laready found scripts and some software that converts. I already know how to convert to Excel sheet. but the problem that I am facing is that I already have an excel sheet that has forumlas. The problem that i have how can i enter some data into an existing excel sheet.

I really approciate your help

Regards,
firedrop

[ober]

You're going to have to use the COM object: [a href=\"http://us2.php.net/manual/en/class.com.php\" target=\"_blank\"]http://us2.php.net/manual/en/class.com.php[/a]

And keep in mind that any Excel file you edit MUST be on the server, not on the client machine!

Code from that page:
[code]<?PHP
$filename = "c:/spreadhseet/test.xls";
$sheet1 = 1;
$sheet2 = "sheet2";
$excel_app = new COM("Excel.application") or Die ("Did not connect");
print "Application name: {$excel_app->Application->value}\n";
print "Loaded version: {$excel_app->Application->version}\n";
$Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");
$Worksheet = $Workbook->Worksheets($sheet1);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("C4");
$excel_cell->activate;
$excel_result = $excel_cell->value;
print "$excel_result\n";
$Worksheet = $Workbook->Worksheets($sheet2);
$Worksheet->activate;
$excel_cell = $Worksheet->Range("C4");
$excel_cell->activate;
$excel_result = $excel_cell->value;
print "$excel_result\n";
#To close all instances of excel:
$Workbook->Close;
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);
?>[/code]

And here's another function that I have, but have never used:
[code]<?php

/***************************/
   function WriteExcel($strPath,$astrSheetName,$astrSQL){
/* This function takes a file save path, and array of sheet names and a corresponding array */
/* of SQL queries for each sheet and created a multi-worksheet excel spreadsheet*/
   $C_NAME=__CLASS__."::".__FUNCTION__;
       $dbs=new clsDB;
       $exapp = new COM("Excel.Application") or Die ("Did not connect");
       $intSheetCount=count($astrSheetName);
       $wkb=$exapp->Workbooks->Add();    
           $exapp->Application->Visible = 1;
       for ($int=0;$int<$intSheetCount;$int++){
           $sheet=$wkb->Worksheets($int+1);
           $sheet->activate;
           $sheet->Name=$astrSheetName[$int];
           $intRow=1;
           $qrySQL=$dbs->GetQry($astrSQL[$int],$C_NAME,__LINE__);
           $rstSQL=$qrySQL->fetchRow(DB_FETCHMODE_ASSOC);
           $astrKeyNames=array_keys($rstSQL);
           $intCols=count($astrKeyNames);
           $qrySQL=$dbs->GetQry($astrSQL[$int],$C_NAME,__LINE__);
           while($rstSQL=$qrySQL->fetchRow(DB_FETCHMODE_ASSOC)){
               $strOut="";//initialize the output string
               for ($int2=0;$int2<$intCols;$int2++){//we start at 1 because don't want to output the table's index
                   if($intRow==1){
                       $strOut=$astrKeyNames[$int2];
                   }else{
                       $strOut=$rstSQL[$astrKeyNames[$int2]];
                   }
                       $sheet->activate;
                       $cell=$sheet->Cells($intRow,($int2+1));//->activate;
                       $cell->Activate;
                         $cell->value = $strOut;
                   }//end of colcount for loop
               $intRow++;
           }//end while loop
       }//end sheetcount for loop
       if (file_exists($strPath)) {unlink($strPath);}
       $wkb->SaveAs($strPath);
       $wkb->Close(false);
       unset($sheet);
       $exapp->Workbooks->Close(false);
       unset($wkb);
       $exapp->Quit;
       unset($exapp);
       unset($dbs);
   }//function WriteExcel
?>[/code]

[firedrop84]

Hello ..

I have been trying to use the code that you have adviced me to you use I mean ober code. It shows me some errors. the error is

Application name: Microsoft Excel Loaded version: 11.0
Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft Office Excel
Description: 'test.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.' in C:\Documents and Settings\12966233\Desktop\xampplite\htdocs\test1.php:16 Stack trace: #0 C:\Documents and Settings\12966233\Desktop\xampplite\htdocs\test1.php(16): variant->Open('test.xls') #1 {main} thrown in C:\Documents and Settings\12966233\Desktop\xampplite\htdocs\test1.php on line 16


and line 16 is $Workbook = $excel_app->Workbooks->Open("$filename") or Die("Did not open $filename $Workbook");

so can anyone help me with this error as I really dont know why it is not working.

Regards,
firedrop