Excel file export

Anon · Mar 25, 2001

Hello,
I have an PHP-site with dynamic data in a HTML-table and with a button the user should have the possibility to export this to excel.

I don`t want to use .csv. Is there another way (as easy as possible).

Thanks in advance
Lydia

einhverfr · Mar 25, 2001

The easiest way, I think would be to leave it in HTML (Excel can read it). Other than that and CSV, I don't know of any other way.

mauricio · Mar 25, 2001

yes...

<?php
header ("Content-Type: application/msexcel");
?>
<table>
<tr>
<td>Sample</td>
</tr>
</table>

Anon · Mar 25, 2001

If you are interested, I created a library which produces native format (ie: binary) Excel spreadsheets (XLS) in PHP4. The same library can also support createion of Lotus Spreadsheets (WK1), HTML webpages (HTM), Comma Separated Values (CSV), Database files (DBF), WordPerfect Merge Files (WP5), and (soon) XML documents (XML).

Let me know if this is what you are looking for.
-- Michael
Darkstreak Consulting
www.darkstreak.com

Usage example:
<?php
require_once("deliveryfmt_functions.php"); # Load the library
deliveryfmt_initialize(); # Initialize the library
deliveryfmt_set_format('xls', false); # Select Excel (.xls) formatting

header('Content-type: '.deliveryfmt_mimetype()); # MIME-type
header('Content-disposition: attachment;filename="results.'.deliveryfmt_format_extension().'"'); # Try to name the output file
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); # Date in the past
header('Last-Modified: '.gmdate("D, d M Y H:i:s").' GMT'); # always modified
$nocache = 'Cache-Control: no-cache, must-revalidate'; # HTTP/1.1
if ($SERVER_PROTOCOL == 'HTTP/1.0') { $nocache = 'Pragma: no-cache'; } # HTTP/1.0
header($nocache);
header('Content-Transfer-Encoding: binary'); # Binary data

deliveryfmt_header(); # Set up Excel file header

Two different ways of using the functions. One way is to set the column names,

then just pass in array rows of data:

$colnames = array(1=>'banana', 2=>'apple', 3=>'pear');
deliveryfmt_set_column_names($colnames);
$data = array('banana'=>'whatever', 'apple'=>'you', 'pear'=>'like');
deliveryfmt_put_array($data);
$data = array('banana'=>'foo', 'apple'=>'3.14159265', 'pear'=>'2');
deliveryfmt_put_array($data);
$data = array('banana'=>'bar', 'apple'=>'Mac', 'pear'=>'Bartlet');
deliveryfmt_put_array($data);

The second way is to create a two dimensional array and have the library

do most of the work:

$data = array('first'=>array('banana'=>'whatever', 'apple'=>'you', 'pear'=>'like'),

'second'=>array('banana'=>'foo', 'apple'=>'3.14159265', 'pear'=>'2'),

'third'=>array('banana'=>'bar', 'apple'=>'Mac', 'pear'=>'Bartlet'));

deliveryfmt_put_2d_array($data);

deliveryfmt_trailer(); # Add Excel file trailer

$buffer = deliveryfmt_finish(); # Finish up the Excel file and return the contents
echo $buffer; # Send it to the waiting browser
?>

Anon · Mar 27, 2001

i am very interested for that libary. can you explain me more about her or can you send it to my emailadress or is there any webpage i can download it? thanks for your help.
yogoo

mikes · Mar 28, 2001

Michael,

I too am interested in the library you have written. Is it possible for you to post it, or a link to it, here?

If that isn't possible, could you e-mail me a copy or information?

Thanks,
Mike

Anon · Mar 28, 2001

Since I've received a number of requests for the library, I'll be posting it on my company's website (www.darkstreak.com). It should be available in day or so. Right now I'm busy fighting off a spammer.

-- Michael
Darkstreak Consulting
www.darkstreak.com

rapidhost · Apr 27, 2001

Hi there,

I am wanting to write a column title to the excel dynamic output, without using the large class.

Can anyone point out how to do this directly?

Thanks,
Darryl

Anon · May 9, 2001

I have downloaded the example file and the library file. When I go to run example.php, All I receive are a bunch of warning messages that say that the header has already been sent. Can anyone help me on this one?

Rob

Anon · May 9, 2001

Rob,
Thanks for the interest in the formatting library. No, you do not need to do anything as drastic as a recompile. What PHP is telling you is that you have output something to the web browser before calling the header() function. Most likely you have some whitespace at the top of your file, or in one of the include()/require() libraries you are loading. That is a common problem encountered when using header() in PHP.

Let me know if you need any further assistance.

-- Michael
Darkstreak Consulting
www.darkstreak.com

heejung · Jul 3, 2001

Michael,

I've downloaded your library and tried testing the example.php file.... What i get is a web page w/ lots of squares & other funny symbols between the entered data:

BANANA APPLE PEARwhatever you like fooñÔÈSû! @@ bar MacBartlet

help!

thanks,
hjl

Anon · Jul 3, 2001

You wrote:

i get is a web page w/ lots of squares &
other funny symbols between the entered data

Good! That means the library is correctly generating the binary file. Unforunately either your webserver is returning the wrong Content-type for the data, or your web browser is not configured to map that Content-type to an application that can handle the data. Either way your web browser winds up attempting to display binary data as text. The result: boxes and weird looking characters for anything that isn't a printable character.

You said you are using the example from my website. Have you modified it in any way? The header() portion is important in getting binary files transferred correctly by the webserver to the web browser.

When you try to retreive data from a web server, your web browser checks what type of data (the MIME-type given in the Content-type header line) the webserver is sending. If it understands it, or has a plug-in that understands it, or has a helper application that understands it, then the content is displayed using the method which claimed to understand it. If nothing understood the Content-type, then the web browser should prompt you to specify a helper application to use to for the Content-type, or to save the data to disk as a file.

Something is causing your web browser to get a Content-type that it thinks it should handled internally as text.

Check the following:
1) Make sure that the header() lines have not been modified. Only modify them if you really understand how the HTTP protocol works.
2) Make sure that the headers are the first thing sent to the webserver. You would be receiving a PHP warning if this is not the case, so this most likely isn't your problem.
3) Check to make sure that your webserver is respecting the header() values. Telnet into port 80 on your webserver and do a: (case sensitive)
GET /path/to/your/page.php HTTP/1.0
[blank line]
You should see some header lines, then a bunch of garbage which looks like what you saw before. Check that the header lines are what you expect them to be.
4) Check your web browser's configuration to see how it thinks it should handle the Content-type specified in the header lines on #3 above.

Let me know if this helped.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon · Jul 4, 2001

thanks, michael!

ok. i realized that the sample code had "Content-disposition" as opposed to "Content-Disposition". so, once i fix that, i get the save to disk dialog. but then i get the following error:

"Internet Explorer cannot download example.php from xxx. Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later."

Even though the file exists and i have permission to it...

Anon · Jul 8, 2001

You wrote:

i realized that the sample code had "Content-disposition" as opposed to
"Content-Disposition". so, once i fix that, i get the save to disk dialog.
but then i get the following error:
"Internet Explorer cannot download example.php from xxx. Internet Explorer
was not able to open this Internet site. The requested site is either
unavailable or cannot be found. Please try again later."

Let me guess... You are using some release of Microsoft Internet Explorer. Right? Microsoft has frequently changed how they handle HTTP headers between releases of Internet Exploiter. For example, the M$ IE 5.00.3314.2101 (SP2; q295106) accepts "Cache-Control: no-cache, must-validate", while M$ IE 5.50.4522.1800 (SP1; Q279328; q283908; Q286043; Q286045; q290108) doesn't. <curse target="Microsoft" intensity="+1">&$#$*^{@)$!!</curse>} You "D"/"d" discovery in the "Content-Disposition" header is another example of Microsoft changing header processing between versions.

I've modified the example code to follow a stricter interpretation of RFC2616. It should work as expected now. That is of course assuming that your particular version of IE doesn't contain the cache validation bug from a number of sub-releases ago. And also assuming the web server you are using doesn't rely on a particular formatting of the header lines. And assuming that your client's proxy server doesn't get picky about how the headers are formed. And so on, and so on.

By the way... Version 1.4 is now available at:
http://www.darkstreak.com/projects/deliveryfmt_functions/
Version 1.4 adds FoxPro support, callback hooks for codepage conversion and a minor bugfix in blank column handling. If you are interested in codepage conversions, please let me know.

Please take a minute to fill out a Technology Evaluation Form to let me know what you think of the library. Evaluations are the only way we really get any feedback on whether we should continue to develop the library or not. Lack of feedback means that people aren't interested in the library. The less people are interested in the library, then more likely it is to get discontinued or at least lowered in priority when we get too busy.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon · Jul 16, 2001

can you explain this easily, please.

Anon · Jul 16, 2001

You wrote:

can you explain this easily, please.

Explain which?
1) The problem with how IE handles header() lines like "Content-Disposition"?
2) The steps in discovering the webbrowser/webserver configuration problem?

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

gofrm · Aug 14, 2001

i think, i have downloaded the newest version of your library.
I downloaded it 14th July 2001.
But i get the same errormessage as descibed earlier in this thread. (file does not exist... try later...).
I am using IE 5.50.4134.0600.

When i output the xls-content generated by your example on the screen, i get this string:

"BANANA APPLE PEARwhatever you like fooñÔÈSû! @@ bar MacBartlet".

For me, it seems that this string is a little bit short. i can't believe, that this is the content of a valid xls-file. is there something missing?

Anon · Aug 19, 2001

You wrote:

When i output the xls-content generated by
your example on the screen, i get this
string:

"BANANA APPLE PEARwhatever
you like fooñÔÈSû! @@
bar MacBartlet".

For me, it seems that this string is a
little bit short. i can't believe, that
this is the content of a valid xls-file.
is there something missing?

Actually, that looks fine.

But i get the same errormessage as descibed
earlier in this thread. (file does not
exist... try later...). I am using IE
5.50.4134.0600.

How about posting some source code and a URL or two for testing?

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon · Aug 28, 2001

At first, thanks for the libriary Michael.

I can succesfully import data into Excel, but
now I have a problem with Cyrillic or Baltic letters. I\'m not a big expert, so asking for advice on how to use charset in this libriary. In html code I use
\"charset=windows-1257\" for browsers to switch to the right encoding.

Thanks again.

Anon · Aug 28, 2001

The FoxPro file format supports a codepage identifier in the file format. All the other formats (including Excel) assume the data is already in a particular character set. To get around these, the current version (v1.4) of the library supports calling a codepage conversion function. Using a codepage conversion function would allow you to output codepage windows-1257 characters for data retrieved from a database which was storing it in a different codepage.

Codepage conversion functions are easy to write. The only gotcha is that you need to have copies of the codepages you are going "From" and "To". The only reason I mention this is that the last person I worked with on conversion function could never really tell me what codepage he used. So we couldn't write the conversion function. If you know what codepage your data is starting in and what it needs to go to, then writing the function only takes a few minutes.

-- Michael
Darkstreak Computing & Innovations
michael@darkstreak.com