Excel Processing

[OldManRiver]

All,

Looking for help with reading/processing/writing Excel in PHP.

Have post on this at:

Excel Processing - Codewalkers

All help appreciated!

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:

• [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.

All files at:

Excel Processing - Dev Shed

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

[Xnuiem]

Since I always work in the *nix world, there is no native way (i.e. COM) to read Excel files, since they dont exist on that OS, as they are MS only.

However, what we end up doing is writing out just HTML, tossing the header() of Excel files, and it works wonderfully. Excel reads it just like a big table. Use normal HTML tables, and any inline elements you want. It doesnt understand CSS that much, but you can play with it a little, just keep it inline instead of using classes or ids.

[OldManRiver]

Originally Posted by Xnuiem

Since I always work in the *nix world, there is no native way (i.e. COM) to read Excel files, since they dont exist on that OS, as they are MS only.

However, what we end up doing is writing out just HTML, tossing the header() of Excel files, and it works wonderfully. Excel reads it just like a big table. Use normal HTML tables, and any inline elements you want. It doesnt understand CSS that much, but you can play with it a little, just keep it inline instead of using classes or ids.

X,

Appreciate the input and I already can read the Excel, now I'm writing back to pre-defined .XLT template file.

Do you know any source that might help me there?

OMR

[Xnuiem]

I hate to admit it, but I am at a total loss. I have never even tried that before.

I forwarded an email with this thread to a couple of friends that may know, hopefully they will either reply to me or directly to this thread.

Other than that, hope you figure it out. Please post back either way, I would love to know.

[OldManRiver]

Originally Posted by Xnuiem

I hate to admit it, but I am at a total loss. I have never even tried that before.

I forwarded an email with this thread to a couple of friends that may know, hopefully they will either reply to me or directly to this thread.

Other than that, hope you figure it out. Please post back either way, I would love to know.

X,

Harish at:

Class: Excel Writer (excel, xls, excel writer, excelwriter) - PHP Classes

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