Excel Processing

[OldManRiver]

All,

 

Trying to get a good run of Excel classes or extentions.  I Googled and downloaded twelve (12) versions of these, including the Official PEAR class set.

 

Need to do the following:

• Read browser selectable Excel SS into array (got the file selection part working and can view, but can not load the array),
  
• Map Arrays (Originating SS to Target SS),
  
• Rewrite Array from Array Map,
  
• Write to target Excel SS from resulting array,
  

Everything I find and read on this, is either lacks examples or is extremely challenged.  All the documentation on these classes and/or extensions are horrible, leaving the in-experienced user/programmer to hash through tons of code to find the correct functions, classes, extension.  Even the PEAR sight has virtually no information on their contribution to this.

 

Almost all say they are/were based on PEAR, but most claim to be modified so PEAR is no longer needed.  I think my problem is a simple one, but it has taken me five(5) days just to find a set where I could just open an Excel file, and then I does it in "download" mode not direct open, so it is a copy of the file, not the source file that finally opens.

 

I just want to step-by-step solve my problem, or at least find a readable reference manual that describes the classes, functions and extension, so I can actually use/call them, with confidence.

 

Please let me know what additional resources I need to look at.

 

Thanks!

 

OMR

 

[OldManRiver]

All,

 

Have part of this working with this 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> </td>'.
                 '<td colspan=2 align=center bgcolor="#ff5555"><b>'.$myerr.'</b></td>'.
                 '<td> </td>'.
                 '</tr>'.

                 '<tr>'.
                 '<td colspan=4> </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 = '  ';
   $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> </td>
   </tr>

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

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

      <tr>
      <td width=25> </td>
      <td> </td>
      <td> </td>
      <td width=25> </td>
      </tr>

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

      <tr>
      <td> </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> </td>
      </tr>

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

      <tr>
      <td colspan=4> </td>
      </tr>

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

      <tr>
      <td colspan=4> </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:

• TBS opens the source file, but copies the sheet and does not use the original
  
• TBS does not read the source into an array
  
• ExcelReader does not open the file in native form
  
• The source form uses VBA to build a secondary sheet when opened
  
• ExcelReader does not open the file in a way where the VBA secondary sheet can be read
  

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!

 

OMR

 

[effigy]

I know little about Excel or these PEAR modules, but I can recommend a Perl module, if you have that option. I've had success with Spreadsheet::ParseExcel.

[OldManRiver]

I know little about Excel or these PEAR modules, but I can recommend a Perl module, if you have that option. I've had success with Spreadsheet::ParseExcel.

 

So how do I use this, without spending more than 70-80 hours to read the code and learn it?

 

OMR

[OldManRiver]

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:

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!

 

OMR

[OldManRiver]

All,

 

I keep playing and messing around with these Excel Classes, but I still cannot do two things:

 

1. Find all the sheets within a workbook,

2. Select the active sheet for reading (mine is sheet9, from the file I must read).

 

If anyone knows how to do this using and existing class, extension, or command set, please let me know.

 

Thanks!

 

OMR

[OldManRiver]

All,

 

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

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:

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

[OldManRiver]

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:

• TBS to open/view the source file,
  
• Excel Reader to read my values
  

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.

 

All files at:

 

http://forums.devshed.com/php-development-5/excel-processing-499904.html#post1975942

 

Thanks!

 

OMR

 

[OldManRiver]

All,

 

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!!!

 

OMR

[OldManRiver]

All,

 

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