Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having problem HTML as excel file pushed through PHP 1

Status
Not open for further replies.
southbeach

southbeach

Programmer
Joined
Jan 22, 2008
Messages
879
Location
US
To render reports in excel I simply produce the report as I normally do in HTML, I then dump code to a file and name the file with an XLS extension.

Once I push the file to web, browser then gives me option to open or save. I am able to open the file in excel with a warning (it says that the file is not excel format). The file opens nicely and my content looks just as it does online.

My problem is that if I try to use excel standard formulas to get totals it does not work - It is as if the data is not there at all; I get zero for the totals ...

Is there a solution to this or must I re-write my code?

Thank you all in advance!


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Sort by date Sort by votes
are you including things like commas in your php output? ie. 1,000.00? if so excel may well treat that as a string. ditto if you include currency sigils etc.
 
Upvote 0 Downvote
No, I am not putting out any special editing or format, just straight numbers with two fraction position when applicable.



--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
XLS is a binary format so that would take a bit of work to create from PHP. I'm assuming that as Excel is saying it's a bad format it's a text file = CSV maybe ??.
If it is is CSV you can't embed formulai in it, it's just a flat data format.
There is a library from MS on codeplex which allows you to create a sheet from PHP but I think it's the 2007 XML format (i've mentioned it here before)
Tell us more though.
 
Upvote 0 Downvote
I am not pushing a CSV file, I am actually pushing HTML code but named the file using XLS extension.

Here is an example of the actual code I am pushing (stripped down to shrink size and it is still large) - Sorry:
Code: 
	<html>
	<head><title>ON HAND REPORT</title>
<style>
	.tablegrid	{
border-collapse:collapse;
margin-top:2px;
margin-bottom: 2px;
font-family: arial;
font-size: 11px;
}
.tablegrid {background-color:#FFF; size: -1px;}
.tablegrid th, .tablegrid td	{border:1px solid #CCC; padding:1px;}
.tablegrid th			{background-color:#191970; color:#FFF;}
.tablegrid .r0 td	{}
.tablegrid .r1 td	{background-color:#CCC;}
.tablegrid .over td	{background-color:#FF8800;}
.tablegrid .sortedAsc		{background-color:#66CC66 !important;}
.tablegrid .sortedDesc		{background-color:#CC6666 !important;}
.tablegrid a {text-decoration: underline; color: #191970; }

.sheading { color: white; }

}</style></head>
	<body>
	
		<%response.ContentType="application/vnd.ms-excel"%>
		<html>
		<head>
		<title>TITLE</title>
		</head>
		<body>
		<table width="100%" border="0" cellpadding="0" cellspacing="0" class="tablegrid">
			<tr class="sheading">
				<td style="background-color: #191970;">SHIP NAME</td>
				<td style="background-color: #191970;">PO # / WHSE</td>
				<td style="background-color: #191970;" align="right">CTNS&nbsp;</td>
				<td style="background-color: #191970;" align="right">KGS&nbsp;</td>
				<td style="background-color: #191970;" align="right">VOL KGS&nbsp;</td>
				<td style="background-color: #191970;">VENDOR</td>
				<td style="background-color: #191970;">RECEIVED</td>
				<td style="background-color: #191970;">HZ</td>
				<td style="background-color: #191970;">OS</td>
				<td style="background-color: #191970;">BD</td>
				<td style="background-color: #191970;">FM</td>
				<td style="background-color: #191970;">LOCATION</td>
			</tr>
		<tr class="snormal">
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;" align="right">1&nbsp;</td>
			<td style="background-color: #FFF;" align="right">2.6&nbsp;</td>
			<td style="background-color: #FFF;" align="right" >2&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;03/17/09</td>
			<td style="background-color: #FFF;" align="center">&nbsp;</td>
			<td style="background-color: #FFF;" align="center">&nbsp;</td>
			<td style="background-color: #FFF;" align="center"></td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
		</tr>
		<tr class="snormal">
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right">1&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right">3.6&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right" >1&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;02/27/09</td>
			<td style="background-color: #E2E0FF;" align="center">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="center">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="center"></td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
		</tr>
		<tr class="snormal">
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;" align="right">1&nbsp;</td>
			<td style="background-color: #FFF;" align="right">.1&nbsp;</td>
			<td style="background-color: #FFF;" align="right"  style="color: red; font-weight: bold;" >1&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;01/19/09</td>
			<td style="background-color: #FFF;" align="center">&nbsp;</td>
			<td style="background-color: #FFF;" align="center">&nbsp;</td>
			<td style="background-color: #FFF;" align="center"></td>
			<td style="background-color: #FFF;">&nbsp;</td>
			<td style="background-color: #FFF;">&nbsp;</td>
		</tr>
		<tr class="snormal">
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right">1&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right">250&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="right" >80&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;03/17/09</td>
			<td style="background-color: #E2E0FF;" align="center">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="center">&nbsp;</td>
			<td style="background-color: #E2E0FF;" align="center"></td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
			<td style="background-color: #E2E0FF;">&nbsp;</td>
		</tr>
</table></body></html>

If you were to take the above code, and save it to a file named test.xls and then open test.xls using excel, you will see what happens when attempting to open the file. You can then see that the data looks very clean, nice colors and font but you cannot do sum() on any of the columns no matter if you save the file "as", change cells format, etc.

I have a class I can use to create pure excel (or at least something that will not give me such problems) but I want to retain the font capabilities and cell/row coloring.

Regards,


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
Yes sylk would be good, the wikipedia article has good stuff in it, try a look at which is always a good source of info.
A common method is to use Lotus-123 format which is a bit easier the .XLS and might be a few libraries around to support it. Excel support 123 stuff. Just make sure your mime types are set ok.
An alternative method is to script Excel in the browser. You instantiate Excel as an Active-x , write VBA manipulation statements and away you go. You have to trust active-x objects which can be a bit scary.
Finaly there are the Office components which has a spreadsheet in it but lacks full Excel functinalaity. Depends on what you want to do.
 
Upvote 0 Downvote
I would imagine Excel is gracefully degrading I'm really suprised that it works at all. After all it knows it's not an .XLS file bit not actualy what it is.
also the
Code: 
<%response.ContentType="application/vnd.ms-excel"%>
Looks a bit ASP as well.
How do you get it to be .xls I thought you would need a content-disposition header.
I must be missing something really obvious here but I'll have a play with it.
Just to confirm, if this the output from the .PHP???
 
Upvote 0 Downvote
having tested it, i concur with feherke. the spaces are breaking the numbers
 
Upvote 0 Downvote
Removing the &nbsp; solved my problem ... Thank you guys!!!



--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
ingresman

excel is very forgiving about accepting formatted html tables as valid files.
note that at least for excel for mac, i get a lot of manky rows at the top. i guess this is the css component.
 
Upvote 0 Downvote
ingresman,

You can try this yourself, it takes no more than pushing an HTML table to browser. The key is to use tables so that you can produce the row/column layout which is typical of excel and instead of pushing a file with an extension HTM or HTML, you will use XLS.

In my case, I use CSS to format content but I could use style attributes and leave the CSS code out.

Also, through a loop, I place the entire table on a variable; I then write the table to a file and jump to the newly created file
Code: 
$url = '[URL unfurl="true"]http://mydomain.com/MyReports/'.$filename;[/URL]
$ofile = fopen($filename,"a+");
$wfile = fwrite($ofile,$content);
$xfile = fclose($ofile);

header( 'Location: '.$url ) ;

Regards,


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cdlvj
  • Locked
  • Question Question
PHP Debugging
Replies
1
Views
731
cdlvj
cdlvj
W
  • Locked
  • Question Question
how to send checked row data in php
Replies
0
Views
610
wiltang2004
W
SashaBuilder3
  • Locked
  • Question Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
1K
SashaBuilder3
SashaBuilder3
PeDa
  • Locked
  • Question Question
Unwanted spaces in php HTML mail
Replies
4
Views
695
PeDa
PeDa
PeDa
  • Locked
  • Question Question
unwanted spaces inserted into the middle of php-generated html email body
Replies
2
Views
509
PeDa
PeDa

Part and Inventory Search

Sponsor

Back
Top