Excel in PHP

YoungManRiver · Jan 1, 2008

All,

Have dowloaded and tried the following classes and/or excel tools for PHP:

Code:

Excel Class        No example file to test
Excel Reader       Test 1 fails
Excel Reader       Test 2 writes code and errors to screen
Excel XML          Opens Excel but writes crap in the file
EZ Excel           Only show three java gen'd image markers on page
PHP Excel          Shows only text on page
PHP Excel 1.5.5    Shows only text on page
SAM Excel          Give COM error
TBS Excel          Shows good test page, links open Excel, but file has garbage in it
XLS Gen Test       Only shows PHP code as text
XLS Gen SQL 1      Only shows PHP code as text
XLS Stream D       Opens Excel but writes crap in the file
XLS Stream E       Gives PHP error
XMerge             Displays XML code

I downloaded all the source files into the dir "/Zips" under localhost and then extracted all the files into their distinct folders and called all this with the following HTML file:

Code:

<html>
<table border=2>
   <tr>
   <td>Excel Class<p>No example file to test</td>
   </tr>

   <tr>
   <td><a href="Zips/Excel Reader/example.php">Excel Reader T1</a></td>
   </tr>

   <tr>
   <td><a href="Zips/Excel Reader/example2.php">Excel Reader T2</a></td>
   </tr>

   <tr>
   <td><a href="Zips/Excel XML Parse/sample.php">Excel XML</a></td>
   </tr>

   <tr>
   <td><a href="Zips/EZ Excel/test_easyexcel.html">EZ Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/PHP Excel/Tests/runall.php">PHP Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/PHP Excel 1.5.5/Tests/runall.php">PHP Excel 1.5.5</a></td>
   </tr>

   <tr>
   <td><a href="Zips/SAM Excel/testclass.php">SAM Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/TBS Excel/demo_main.htm">TBS Excel</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Gen/test.phtml">XLS Gen Test</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Gen/test_mysql_1.phtml">XLS Gen SQL 1</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Stream/example_download.php">XLS Stream Download</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XLS Stream/example_export.php">XLS Stream Export</a></td>
   </tr>

   <tr>
   <td><a href="Zips/XMerge/build.xml">XMerge</a></td>
   </tr>
</table>
</html>

The three (3) showing promise are:
Excel XML
TBS Excel
XLS Stream
but more work is needed to find out why they read or write garbage into their files.

YMR

YoungManRiver · Jan 2, 2008

All,

Found the errors coming from TBS were from using "echo" statements which were writing to the screen. This conflicts with the "header" commands being issued in the class declaration. When I turned off all "echos" in my code and on line 2161 of the "tbs_class.php" file, then all worked great.

Wonder if there is a way around all this and/or a way to "clear" before issueing these commands in the class module?

YMR

jpadie · Jan 3, 2008

on your latter question, you could try using output control functions (like buffering) and, if you need, clear the buffer at certain triggers (ob_end_clean(); etc)

YoungManRiver · Jan 7, 2008

All,

Kept hacking on this and got partial solution!

Here is my working code:

Code:

<?php
   define('TITLE1', 'Excel Scrub');
   define('TITLE2', 'Source to Target');
   define('ORGFIL', 'Pricesheet.xls');
   define('ORGSHT', 'Sheet1');
   define('OCLROW', '20 324');
   define('TRGFIL', 'quote.xls');
   define('TRGSHT', 'quote');
   define('TCLROW', '11 57');
   define('USE_DB', 'xl_scrub');
   include('Zips/db_connect.php');                   //Connect to DB
   include('Zips/TBS Excel/tbs_class.php');          //Load TBS Class
   include('Zips/TBS Excel/tbs_plugin_excel.php');   //Load TBS plugin
   require('Zips/ExcelReader/reader.php');           //Load Excel Reader Class
   $err_msg = "";

   function emsg_tbr($myerr) {
      $err_msg = '<tr>'.
                 '<td>&nbsp;</td>'.
                 '<td colspan=2 align=center bgcolor="#ff5555"><b>'.$myerr.'</b></td>'.
                 '<td>&nbsp;</td>'.
                 '</tr>'.

                 '<tr>'.
                 '<td colspan=4>&nbsp;</td>'.
                 '</tr>';
      return $err_msg;
   }

   function open_xcel($myfile,$mysheet) {
      $opn_str = basename($myfile);
      $TBS = new clsTinyButStrong;
      $TBS->PlugIn(TBS_INSTALL,TBS_EXCEL);
      $TBS->LoadTemplate($opn_str);
//      $TBS->MergeBlock('book',$books);               //Process Alternate Cell Mapping
//      $TBS->MergeBlock('tsk1,tsk2',$tasks);          //Process Alternate Cell Mapping
//      $TBS->MergeBlock('emp',$employees);            //Process Alternate Cell Mapping
//      $TBS->PlugIn(TBS_EXCEL,TBS_EXCEL_FILENAME,'result.xls');
      $TBS->PlugIn(TBS_EXCEL,TBS_EXCEL_FILENAME,$opn_str);
      $TBS->Show();
   }

   function XL_proc($infile,$otfile) {
      $flpos = strpos($infile,ORGFIL);
      if ($flpos>0) {
         $rcell = read_xcel($infile,ORGSHT);
         print_r($rcell);
//               $mcell = map_cells($rcell,ORGSHT,TRGSHT);
//               print_r($mcell);
         //      $otfil = next_file($otfile);
         $rc    = write_xcel($otfile,$otfil,$mcell);
         $PROCD = 'Processed';
      } else {
         $err_msg = emsg_tbr("File does not meet filter criteria!");
      }
   }

   function read_xcel($myfile,$mysheet) {
      $data = new SS_XL_RDR() or Die ("Did not connect");
      error_reporting(E_ALL ^ E_NOTICE);
      $data->setOutputEncoding('CP1251');
      $data->setRowColOffset(0);
      $data->read($myfile);
      $bl_pos = strpos(OCLROW," ");
      $sh_rows = trim(substr(OCLROW,$bl_pos))+1;
      $sh_cols = trim(substr(OCLROW,0,$bl_pos))+1;
      $off_row = 1;
      $off_col = 1;
      //echo "Range Rows=>$sh_rows:$sh_cols<=Cols <br>";
      for ($i = 1; $i <= $sh_rows+$off_row; $i++) {
         for ($j = 1; $j <= $sh_cols+$off_col; $j++) {
//                  foreach ($data->sheets[0]['cells'][$i][$j] as $key => $value) {
//               echo "Key => $key Value $value <br>";
//                  }
            $cell[] = array("$i:$j" => $data->sheets[0]['cells'][$i-$off_row][$j-$off_col]);
         }
      }
      return $cell;
   }

   function map_cells($cray,$osht,$tsht) {
      $sql_str = "SELECT * FROM xl_s_map";
      $result = mysql_query($sql_str);
      while ($row = mysql_fetch_assoc($result)) {
         $rwcl = $row['mSrcRC'];
         foreach($cray as $c => $val) {
            if ($c == $rwcl) {
               break;
               $oc = "";
            }
         }
      }
   }

   function write_xcel($myfile,$cel_ray) {
/*
      $ex = new COM("Excel.sheet") or Die ("Did not connect");
      $wkb = $ex->application->Workbooks->Open($workbook) or Die ("Did not open");
      $sheets = $wkb->Worksheets($sheet);                //Select the sheet
      $sheets->activate;                                 //Activate it
*/
   }

   $but_vin = '<button type=submit name=iview onclick="value=\'invw\'">'.
              'View Input File</button>';
   $but_vot = '<button type=submit name=oview onclick="value=\'outv\'">'.
              'View Output File</button>';
   $but_prc = '<button type=submit name=proc onclick="value=\'proc\'">'.
              'Process</button>';
   $but_prt = '<button type=submit name=prnt onclick="value=\'prnt\'">'.
              'Print Output</button>';
   $two_spc = '&nbsp;&nbsp;';
   $but_lin = $but_prc.$two_spc.$but_vot.$two_spc.$but_prt;
   $vot_val = '"D:\Local Files\Company Relations, Projects & Bids\Active\Avid Business Networks\avid phone quote.xls"';

   $vin_val = $HTTP_POST_VARS['infil'];
   $fld_vin = "<input type=file size=80 name=infil value=$vin_val ".
              "onclick=\"document.forms['scrub'].submit();\">";
   $fld_vot = "<input type=text size=100 name=otfil value=$vot_val>";
   $vot_val = 'D:\Local Files\Company Relations, Projects & '.
              'Bids\Active\Avid Business Networks\avid phone quote.xls';
   if ($HTTP_POST_VARS['iview']=='invw') {
      $tstbut = $HTTP_POST_VARS['iview'];
   }
   if ($HTTP_POST_VARS['oview'] == 'outv') {
      $tstbut = $HTTP_POST_VARS['oview'];
   }
   if ($HTTP_POST_VARS['proc']=='proc') {
      $tstbut = $HTTP_POST_VARS['proc'];
   }
   if ($HTTP_POST_VARS['prnt']=='prnt') {
      $tstbut = $HTTP_POST_VARS['prnt'];
   }unset ($HTTP_POST_VARS['proc'],$HTTP_POST_VARS['form'],
            $HTTP_POST_VARS['iview'],$HTTP_POST_VARS['oview'],
            $HTTP_POST_VARS['prnt']);
   switch ($tstbut) {
      case 'invw':   // Open/Edit the INPUT file.
         if ($HTTP_POST_VARS['infil'] != "") {
            $vin_val = $HTTP_POST_VARS['infil'];
            $vin_val = realpath($vin_val);
            if (file_exists($vin_val)) {
               $rc = open_xcel($vin_val, ORGSHT);
            } else {
               $err_msg = emsg_tbr("File => $vin_val <= not found!");
            }
         } else {
               $err_msg = emsg_tbr("Must select a file to procces or read!");
         }
         break;
      case 'outv':   // Open/Edit the OUTPUT file.
         if ($HTTP_POST_VARS['otfil'] != "") {
            if (file_exists($vot_val)) {
               if ($PROCD == 'Processed') {
                  $rc = open_xcel($vot_val, ORGSHT);
               } else {
               $err_msg = emsg_tbr("No OUTPUT File Processed!");
               }
            } else {
               $err_msg = emsg_tbr("File => $vot_val <= not found!");
            }
         }
         break;
      case 'proc':   // Dump the INPUT file into the OUTPUT file.
         if ($HTTP_POST_VARS['infil'] != "") {
            $vin_val = $HTTP_POST_VARS['infil'];
            $vin_val = realpath($vin_val);
            if (file_exists($vin_val)) {
               if ($PROCD != 'Processed') {
                  $rc = XL_proc($vin_val,$vot_val);
               } else {
                  $err_msg = emsg_tbr("File already Processed!");
               }
            } else {
               $err_msg = emsg_tbr("File => $vot_val <= not found!");
            }
         } else {
            $err_msg = emsg_tbr("Must select a file to procces or read!");
         }
         break;
      case 'prnt':
         if ($HTTP_POST_VARS['otfil'] != "") {
            if (file_exists($vot_val)) {
//               $rc = open_xcel($vot_val, ORGSHT);
            } else {
               $err_msg = emsg_tbr("File => $vot_val <= not found!");
            }
         }
         break;
   }
   $TIT1 = TITLE1;
   $TIT2 = TITLE2;
   ob_start();
?>

<html>
<head>
<title>Excel Scrub Processor</title>
</head>

<body>
<FORM name="scrub" METHOD="POST" ACTION="<?php echo $_SERVER['PHP_SELF']; ?>">
<table width='80%' border=0 align=center>
   <tr height=50>
   <td align=center>&nbsp;</td>
   </tr>

   <tr>
   <td align=center>
   <table width='100%' border=0 align=center bgcolor='#55aaee'>
      <tr>
      <td colspan=4 align=center>&nbsp;</td>
      </tr>

      <tr>
      <td colspan=4 align=center>
          <h1><?php echo $TIT1; ?></h1>
          <b><?php echo $TIT2; ?></b>
      </td>
      </tr>

      <tr>
      <td width=25>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td width=25>&nbsp;</td>
      </tr>

      <?php
         if($err_msg != "") echo $err_msg;
      ?>

      <tr>
      <td>&nbsp;</td>
      <td>
        <?php echo $fld_vin; ?>
      </td>
      <td align=left width=100>
      <button type=submit name='iview' onclick="value='invw'">View Input File</button>
      </td>
      <td>&nbsp;</td>
      </tr>

      <tr>
      <td>&nbsp;</td>
      <td colspan=2>
        <?php echo $fld_vot; ?>&nbsp;<b><= Output file</b>
      </td>
      <td>&nbsp;</td>
      </tr>

      <tr>
      <td colspan=4>&nbsp;</td>
      </tr>

      <tr>
      <td colspan=4 align=center>
        <?php echo $but_lin; ?>
      </td>
      </tr>

      <tr>
      <td colspan=4>&nbsp;</td>
      </tr>
   </table>
   </tr>
</table>
</form>
</body>
</html>
<?php
   ob_end_flush();
?>

You can see I'm using two different Excel class libraries, but neither works the way I want and here is why:
[ul]
[li]TBS opens the source file, but copies the sheet and does not use the original[/li]
[li]TBS does not read the source into an array[/li]
[li]ExcelReader does not open the file in native form[/li]
[li]The source form uses VBA to build a secondary sheet when opened[/li]
[li]ExcelReader does not open the file in a way where the VBA secondary sheet can be read[/li]
[/ul]
Result: I get basically a blank array, with no usable data. So I need a little advanced help on how to open the sheet so all VBA processing works and then read into the array for array processing to the target output file.

Hope this clears up part of the problem, at least where I'm currently stuck at.

Thanks!

YMR

YoungManRiver · Jan 9, 2008

All,

A contributor at:

http://forums.codewalkers.com/php-coding-7/excel-processing-868196.html#post167558

Recommended a link to another class library.

That reader is almost the same as the one I have, notice my:

Code:

lineno#14   require('Zips/ExcelReader/reader.php');           //Load Excel Reader Class

statement. I had downloaded one like this but hate long names so renamed "Spreadsheet_Excel_Reader" to "SS_XL_RDR".

The class in the link seems to have a few more extensions, but basic problem with this is it does not open in native mode, therefore the VBA macros do not run and therefore my data array is blank, as the macros build new sheet and then display that with all data. Basic sheet only has 20 fields propogated and those are constants, which do not appear in the final output sheet.

I think I need to run an "exec(filename)" type of command to open the sheet, with the sheet assigned to a handler, then have an excel class, which can read the sheet within the handler.

Not real sure how to make that happen as all my attempt to run Excel from command shell just give errors. That why I was also experimenting with the TBS class as I will actually run/open the file is a way which executes the macros and gives me a viewable screen with all the data displayed.

Trying to see, if they have a "visible" option, so display could be turned off for background processing. I think if I leave out the $TBS->Show(); statement I can do background processing, but not sure. Still experimenting with that. May need a new class to "Run Macros" to avoid the prompt that comes up when $TBS->Show(); displays the file.

Also experimenting with the properties stored in the $TBS->PlugIn function/class to see what I can use to actually read data. Thinking I'm problably going to wind up copying some cell/data reading functions from the "ExcelReader" class into the TBS class to fix this.

Maybe I'm trying to design a whole new Excel class? Not sure?

Anyway, was surprised at lack of IRC support, since this should be fairly routine for many programmers, so started channel #XL-VBA on irc.freenode.net. Maybe we can get some interaction going and fix it. Willing to share the code, when it is done.

Thanks!

YMR

jpadie · Jan 9, 2008

ok. here's a question. what are you trying to achieve by reading the excel files? are you looking just to read or to write files?

for writing files, i'd recommend the PEAR package. it's well maintained.
for reading files, have you considered a simple ODBC connection to excel?

YoungManRiver · Jan 23, 2008

All,

What I need here, on a conceptual basis, code wise is:

Code:

function read_xcel($myfile,$mysheet) {
   $objReader = new PHPxl_Reader;
   $objPHPxl = $objReader->load($myfile);
   $objSheets = $objReader->getActiveSheets();
   $objActiveS = $objReader->setActiveSheet($objSheets);
   $objRange = $objReader->SheetRange($objActiveS);
   foreach ($objRange as $cell) {
      $cellval = array($cell,$objReader->Cell->Value);
   }
   print_r($cellval);
}

Which should give me an array I can parse into MySQL then once I massage all in MySQL I then need something like this:

Code:

function write_xcel($myfile,$mysheet) {
   $objWriter = new PHPxl_Writer;
   $objPHPxl = $objWriter->load($myfile);
   $objActiveS = $objWriter->setActiveSheet($mysheet);
   $sql_str = "SELECT * FROM mydbtable WHERE datatype='data';";
   $result = mysql_query($sql_str);
   while ($row = mysql_fetch_assoc($result)) {
       $rwcl = $row['row_col'];
       $rcdt = $row['datasource'];
       $res = $objWriter->SetCell($rwcl,$rcdt);
   }
   $objWriter->Close;
}

If I can get an example that works like these two hypothetical functions then I can make it all work.

Thanks!

OMR

YoungManRiver · Jan 31, 2008

All,

Maybe you didn't notice, but this project turned out to be much larger than I thought.

Anyway I finally got all the reading done and now working on writing the second file.

I had to break up the output into two (2) MySql tables as 1.) one table deals with header and footer information, for both input and output EXCEL files and 2.) the second table deals with itemized rows.

In particular this is a problem as you notice the output EXCEL file only has ten (10) rows to write into, and the input EXCEL file has 300 rows of data. Now selection (query) of data from the first input file is on the "QTY" column, which we ignore if blank, so usually less than 10 rows are active. But when more than 10 row result from the query, then new rows have to be inserted into the output EXCEL file, conserving the formatting from the row above and then renumbering column one, for all remaining detail item rows.

I'm uploading the source for where I'm at. I used:

[li]TBS to open/view the source file, [/li]
[li]Excel Reader to read my values[/li]

but now have to find a different class to write with.

If you have a single class that can do it all, it would be helpful and jumping in here to help will be appreciated.

I tried but limit on filesize is 50kb and source files are too large zipped. First file is at:

http://forums.codewalkers.com/php-coding-7/excel-processing-868196.html#post170927"]

http://forums.codewalkers.com/php-coding-7/excel-processing-868196.html#post170927

[/URL]

Will find another place to put second one and post link here.

Thanks!

OMR

YoungManRiver · Jan 31, 2008

All files at:

http://forums.devshed.com/newreply.php?do=newreply&noquote=1&p=1959927

YoungManRiver · Mar 12, 2008

All/JPadie,

Hey I got all it working but the write to the new SS.

On the subject of write, when I look at all the docs on the Excel Writer Classes, everyone assumes new file, new workbook, new worksheet.

Nothing could be further from my situation! I have a .XLT template with a specific "NAMED" worksheet that must be written to.

Therefore none of the docs fit my problem and I have not yet figured out how to find my worksheet in the writer. Finding worksheets seems to be a "READER" function not a "WRITER" function, so I seem to be at an impasse right now!

Suggestions on how to approach or solve this portion would help me get around my current road block.

All help greatly appreciated!!!

Thanks!!!

YMR

jpadie · Mar 12, 2008

have you considered directly editing the underlying xml?

http://msdn2.microsoft.com/en-us/li...tnewfileformat_structureoftheofficexmlformats

YoungManRiver · Mar 17, 2008

JPadie,

Trying to stay pure PHP if possible, but yes if necessary!

YMR

YoungManRiver · Mar 21, 2008

Jpadie,

Harish at:

http://www.phpclasses.org/browse/package/2037.html

suggested I use the PEAR class, get it working and then extract the class code to my other class libs when it works.

What do you think?

OMR

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel in PHP

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

jpadie

Technical User

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

jpadie

Technical User

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

jpadie

Technical User

YoungManRiver

IS-IT--Management

YoungManRiver

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor