Excel Processing - Dev Shed

Dev Shed Forums Sponsor:

January 5th, 2008, 05:56 PM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

Excel Processing

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

January 5th, 2008, 06:59 PM

thedude2010

Contributing User

Join Date: Apr 2005

Posts: 454

Time spent in forums: 1 Week 1 h 45 m
Reputation Power: 57

I can help with writing a spreadsheet using the official pear classes.

PHP Code:


		
			
require 'Spreadsheet/Excel/Writer.php';

$workbook=new Spreadsheet_Excel_Writer("pathtowhereyouwanttosaveyourexcelfile.xls");

$worksheet=&$workbook("worksheetname"); // you can do multiple worksheets just do a different variable and worksheetname



$a=0;$b=0;  // column and row counters



$worksheet->write($a,$b, "whatever"); // $b++ would move one column to the right - $a++ would move one column down



$workbook->close();

Quote:

Originally Posted by 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,

January 7th, 2008, 10:39 AM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

Partially Working!

All,

Have part of this working with this 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:

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

January 9th, 2008, 12:34 PM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

More Experimenting

All,

A contributor at:
http://forums.codewalkers.com/php-c...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!

OMR

January 23rd, 2008, 05:44 PM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

What I Need

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

January 31st, 2008, 11:14 AM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

Almost There

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.

Files attached. Second is EXCEL source, which is large.

Thanks!

OMR

Attached Files

	XLscrub_files1.zip (65.8 KB, 320 views)
	XLscrub_files2.zip (167.1 KB, 711 views)

March 13th, 2008, 01:29 AM

OldManRiver

Contributing User

Join Date: Sep 2005

Posts: 118

Time spent in forums: 17 h 21 m 40 sec
Reputation Power: 7

RoadBlock

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