User not logged in - login - register
Home Calendar Books School Tool Photo Gallery Message Boards Users Statistics Advertise Site Info
go to bottom | |
 Message Boards » » when using php to export a MySQL table to Excel Page [1]  
BigMan157
no u
103354 Posts
user info
edit post

is there a way to alter cell width, cell color, etc.?

i'm exporting it as a datastream, setting $titles to the titles on the MySQL table

$titles = "";
for ($i = 0; $i < $numfieldsXL; $i++) {
   $titles .= mysql_field_name($resultXL, $i) . "\t";
}


then using a while and foreach loop to set $data to all the elements in the table

and then setting it up to be downloaded as an excel sheet
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$Name-$year.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$titles\n$data";


is there anything i could include in that datastream, special characters or statements or something, that will allow me to format the file prior to download?

[Edited on July 26, 2006 at 9:15 PM. Reason : or alternately is they any 'conditioning' that i could somehow put it through prior to DL?]

[Edited on July 26, 2006 at 9:31 PM. Reason : also creating multiple tabs and formatting cell functions would be sweet]

7/26/2006 9:14:12 PM
Wolfmarsh
What?
5975 Posts
user info
edit post

You arent actually creating an excel file.

A real excel file is a binary file.

You are just basically in essence creating a csv file, which you can contain no formatting in.

Search google on how to create an excel binary with php, and go that route.

7/26/2006 9:48:27 PM
agentlion
All American
13936 Posts
user info
edit post

http://pear.php.net/package/Spreadsheet_Excel_Writer

7/26/2006 9:58:28 PM
BigMan157
no u
103354 Posts
user info
edit post

^^thx wolfmarsh, i'll look into that

^yeah, i found that when i was looking for some solutions

7/26/2006 10:15:10 PM
Perlith
All American
7620 Posts
user info
edit post

If you can decipher the XML format Excel 2003 uses, you might be able to go with that. Otherwise, good luck with Google.

7/27/2006 7:27:51 AM
jbtilley
All American
12797 Posts
user info
edit post

Excel accepts html code and does a decent job of displaying it. Try copying and pasting the code below into a new file and rename it such that it has an .xls extension. Open it in excel.


<table>
<tr>
  <td width="200" bgcolor="#c00000"><font color="#ffffff">Test 1</font></td>
  <td width="35" align="center" bgcolor="00c000"><font color="#ffffff">Test 2</font></td>
</tr>
</table>


or even css


<html>
<head>
<style type="text/css">
  td.one {
    color: white;
    background-color: #c00000; 
    vertical-align: middle; }
  td.two {
    color: black;
    background-color: #c0c0c0; 
    text-align: center; }
</style>
</head>
<body>
<table border="1">
<tr>
  <td width="200" class="one">Test 1</td>
  <td width="35" class="two">Test 2</td>
</tr>
</table>
</body>
</html>


You can pretty much write out whatever html you want in php using tables. You can redirect the output to an excel file with:


<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=file.xls"); 
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
?>


at the top of your php file. This will net you the very basic spreadsheet formatting functions.

[Edited on July 27, 2006 at 7:51 AM. Reason : -]

7/27/2006 7:28:40 AM
BobbyDigital
Thots and Prayers
41777 Posts
user info
edit post

bump per user request.

12/6/2006 9:57:34 AM
BigMan157
no u
103354 Posts
user info
edit post

oh yeah almost forgot i wanted this bttted, thx

i finally got back around to working on this and decided to go the route of usingSpreadsheetML or the XML that excel uses

i'm getting errors on the file it makes though and was wondering if anyone is good at this sort of XML and could help me troubleshoot it

12/6/2006 10:52:16 AM
qntmfred
retired
40726 Posts
user info
edit post

if you find a good solution, post it here. this is something on one of my todo lists too, i knew about the pear package but if something better exists, i'd like to look at it

[Edited on December 6, 2006 at 3:09 PM. Reason : .]

12/6/2006 3:08:51 PM
agentlion
All American
13936 Posts
user info
edit post

what exactly are you looking for that the PEAR package can't do....

12/6/2006 4:32:54 PM
BigMan157
no u
103354 Posts
user info
edit post

i don't want to install the PEAR package is my thing

what i'm trying to do is output a SpreadsheetML file going by http://www-128.ibm.com/developerworks/opensource/library/os-phpexcel/

it's just i'm getting frustrated at the XML and i'm not seeing whatever's not working properly

[Edited on December 6, 2006 at 8:06 PM. Reason : ]

12/6/2006 8:05:06 PM
bous
All American
11215 Posts
user info
edit post

anyone know how to parse PDF text in php?

12/6/2006 8:30:29 PM
BigMan157
no u
103354 Posts
user info
edit post

sweet got it working

12/16/2006 1:15:03 AM
qntmfred
retired
40726 Posts
user info
edit post

details?

12/16/2006 10:25:33 AM
BigMan157
no u
103354 Posts
user info
edit post

i just made an Excel sheet as an example to work off, used the Save As...>XML Spreadsheet function, opened it up in wordpad and had the php code generate something similar

i was mainly just having difficulty with the xml code, once i got to the error logs from excel things went much faster

i was doing things like using <cell> instead of <Cell> which will make things not show up, having wrong style names, setting Data as "Number" when it should be "String", etc. - SpreadsheetML is somewhat pickier than HTML or normal XML

here's an example of what the SpreadsheetML looks like with some php code to generate it, but i didn't follow their code so i don't know how good it is: http://www-128.ibm.com/developerworks/opensource/library/os-phpexcel/

12/16/2006 1:11:31 PM
 Message Boards » Tech Talk » when using php to export a MySQL table to Excel Page [1]  
go to top | |
Admin Options : move topic | lock topic

© 2024 by The Wolf Web - All Rights Reserved.
The material located at this site is not endorsed, sponsored or provided by or on behalf of North Carolina State University.
Powered by CrazyWeb v2.39 - our disclaimer.