 |
|
|
01-01-2008, 10:55 PM
|
Excel in PHP
|
Posts: 24
|
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.
OMR |
|
|
|
|
01-02-2008, 04:30 PM
|
Re: Excel in PHP
|
Posts: 24
|
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?
OMR
|
|
|
|
|
01-03-2008, 10:07 PM
|
Re: Excel in PHP
|
Posts: 24
|
All,
Still have not figured out how to make any of these read the rows and columns into an array or to MySQL.
Help please!
OMR
|
|
|
|
|
01-04-2008, 05:59 AM
|
Re: Excel in PHP
|
Posts: 88
Name: programmer
Location: internet
|
AHHH just wrote a huge tutorial and nice commented and indented code and when I hit submit it asked me to login, went back and all was gone!!!!!!!!
wow, okay I'll try again but its late:
I know nothing about the package above but when I want to put an excel file into my mysql database I use some string and array functions.
For example let's say your excel file is tab delimited. I would first upload the file to say excel.txt then write the following file: read_excel.php: (for this sample I am assuming 10 columns).
PHP Code:
<?
$string = file_get_contents('excel.txt');
//the file is now in a string
$lines = explode("\n", $string);
//the file is now in an array and each line a seperate value
$lines_count = count($lines);
//I need to know how many lines there are for later use
//its 3:50am and I'm falling asleep sorry no more commenting
for ($i=0;$i<$line_count;$i++) {
$line = $lines[$i];
$columns = explode("\t", $line);
$column_count = count($columns);
for ($c=0;$c<$column_count;$c++) {
$data[]= $columns[$c];
}
mysql_query("INSERT INTO table (1,2,3,4,5,6,7,8,9,10) VALUES ("$data[1]","$data[2]","$data[3]","$data[4]","$data[5]","$data[6]","$data[7]","$data[8]","$data[9]","$data[10]")");
}
?>
You would have to have a table already with the name 1,2,3,4,5... obviously change them to the names you want and also change the corresponding code above to reflect those names. Any questions I'll be back on tomorrow. |
|
|
|
|
01-07-2008, 02:52 PM
|
Partially Working!
|
Posts: 24
|
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> </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! |
|
|
|
|
01-09-2008, 01:37 PM
|
More Experimenting
|
Posts: 24
|
All,
A contributor at:
http://forums.codewalkers.com/php-co...tml#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 |
|
|
|
|
01-21-2008, 10:17 PM
|
Re: Excel in PHP
|
Posts: 80
Name: Carlos
|
__________________
-----------------------
Please login or register to view this content. Registration is FREE
|
|
|
|
|
01-31-2008, 12:15 PM
|
Re: Excel in PHP
|
Posts: 24
|
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:
http://forums.devshed.com/php-develo...ml#post1975942
Thanks!
OMR |
|
|
|
|
03-13-2008, 02:19 AM
|
Re: Excel in PHP
|
Posts: 24
|
Quote:
Originally Posted by carloncho
|
But doesn't Write!!
OMR |
|
|
|
|
03-13-2008, 02:20 AM
|
Re: Excel in PHP
|
Posts: 24
|
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
|
|
|
|
|
|
« Reply to Excel in PHP
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
