|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Codewalkers Forums Sponsor:
|
|
|
|
#1
January 5th, 2008, 05:57 PM
|
|||
|
|||
|
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:
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 Last edited by OldManRiver : January 7th, 2008 at 10:35 AM.
|
|
#2
January 5th, 2008, 06:14 PM
|
||||
|
||||
|
I found a really good excel reader but I can't recall the name of it. I'll dig around and see if I can find it this weekend. It's super easy to use.
__________________
There is no spoon.
|
|
#3
January 7th, 2008, 11:18 AM
|
|||
|
|||
|
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> </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();
?>
Hope this clears up part of the problem, at least where I'm currently stuck at. Thanks! OMR
|
|
#4
January 8th, 2008, 06:06 PM
|
||||
|
||||
|
here's a good excel reader
http://sourceforge.net/docman/display_doc.php?docid=22092&group_id=99160
|
|
#5
January 9th, 2008, 12:21 PM
|
|||
|
|||
|
Almost the Same
icandothat,
This reader is almost the same as the one I have, notice my: Code:
lineno#14 require('Zips/ExcelReader/reader.php'); //Load Excel Reader Class
I see the one in your 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
|
|
#6
January 9th, 2008, 03:17 PM
|
||||
|
||||
|
you should probably use COM objects instead of what you're doing now.
Here's a basic example how how to use PHP and COM to manipulate Excel. $fh = new COM("excel.application"); $fh->Visible = false; $worksheet = $fh->workbooks->add(); $worksheet->Cells(1,1)->value = "hi there"; $worksheet->SaveAs("test.xls"); $fh->quit(); as far as getting it to run some vba macro... hmm not too sure. maybe you can replace the vba with some good old php.
|
|
#7
January 31st, 2008, 11:04 AM
|
|||
|
|||
|
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:
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 100kb and source file is 167 zipped. Will find another place to put it and post link here. Thanks! OMR
|
|
#8
January 31st, 2008, 11:11 AM
|
|||
|
|||
|
Files
|
|
#9
March 13th, 2008, 01:26 AM
|
|||
|
|||
|
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
|
|
#10
March 17th, 2008, 08:33 PM
|
|||
|
|||
|
No COM
Quote:
Ican, Do you have other working examples that stay away from COM. I need this to work effectively on Unix/Linux and COM is not available there, so trying to find the generic way to do this. OMR
|
|
#11
March 17th, 2008, 09:07 PM
|
||||
|
||||
|
ah gotcha, man that's too bad. Lemme poke around and see what I can find.
|
|
#12
March 21st, 2008, 01:41 PM
|
|||
|
|||
|
Work Around
Quote:
Ican, 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
|
|
| Viewing: Codewalkers Forums > PHP Related > PHP Coding > Excel Processing |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
