I am currently working on an affiliate management system for a client, and he wants to be able to view information from various tables in an MS Excel spreadsheet as well as in tables from the web application.

I thought that I could somehow generate the spreadsheet, then email it so it arrives in his inbox and he is happy and good.

The main problem, though, is figuring out the .xls format. I have no idea! I thought of just creating a csv file that he can import, but I wanted to make it so that 'the idiot manager' could use it, without having to worry about excel's features.

What do others think? I would like the whole admin to be able to be used by this manager who is a bit... umm.... 'fuddy duddy' as a friend of mine would put it. EG he has a computer on his desk cause it looks good, and wants more technology even though he can't use it.

Anyway, any input would be appreciated.

Thanks,

Ross

    The nice thing about excel is it's pretty slutty about it's data formats, so all you have to do is tell it that what you're sending is an excel spreadsheet, then just spit out tab delimitted data and excel will do the rest.

    Start your page with this:

    header ("content-type: application/vnd.ms-excel");
    header("Content-Disposition: filename=somefile.xls");

    then just for next across your data set and print it with \t in between each field, and \n after each record.

      Wow, thanks for that! I thought excel would be totally the opposite: EG it requires some propietry (sp?) format from MS. Okay, I feel much better now 🙂

      Ross

        that's good idea. but how can i get the list for using header()?
        thinks :-)

        yorgo

          I don't know that there is an exhaustive list, but this place:

          http://www.ietf.org/rfc/rfc2616.txt?number=2616

          has a few hundred generic ones listed, and after playing with them, you can get a mime type by using a tool to see what type of file is coming across. Netscape under linux lists it as the title of the save as dialog box when you shift click on a link. See what type it is coming out of an NT server or where ever and that's the content type field.

            3 months later

            ko so how do you send control strings to excel (example: you have your data in the spreadsheet, however one of the fields is 654654654564 andexcel is automatically making the field 65.465465+15)

            any ideas??

              2 months later

              A guy called Christian Novak made this, I have a object oriented version if anyone wants it they can e-mail me. It needs work.
              I have the excel specs if theres anyone out there thats good at hexing.

              http://px.sklar.com/code.html?code_id=488----------------------------

              Excel Functions

              save these functions into a file, i.e. "_excel.php"

              The basic steps to create Excel streams from PHP are
              1. Call xlsBOF()
              2. Write contents into cells by either using xlsWriteNumber(), or
              xlsWriteLabel()
              3. Call xlsEOF()

              "echo" functions can be also replaced by "fwrite" functions to write
              directly to the webserver instead of parsing the contents to the
              browser.

              <?php
              // ----- begin of function library -----
              // Excel begin of file header
              function xlsBOF() {
              echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
              return;
              }
              // Excel end of file footer
              function xlsEOF() {
              echo pack("ss", 0x0A, 0x00);
              return;
              }
              // Function to write a Number (double) into Row, Col
              function xlsWriteNumber($Row, $Col, $Value) {
              echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
              echo pack("d", $Value);
              return;
              }
              // Function to write a label (text) into Row, Col
              function xlsWriteLabel($Row, $Col, $Value ) {
              $L = strlen($Value);
              echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
              echo $Value;
              return;
              }
              // ----- end of function library -----
              ?>

              //
              // To display the contents directly in a MIME compatible browser
              // add the following lines on TOP of your PHP file:

              <?php
              header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
              header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
              header ("Cache-Control: no-cache, must-revalidate");
              header ("Pragma: no-cache");
              header ('Content-type: application/x-msexcel');
              header ("Content-Disposition: attachment; filename=EmplList.xls" );
              header ("Content-Description: PHP/INTERBASE Generated Data" );
              //
              // the next lines demonstrate the generation of the Excel stream
              //
              xlsBOF(); // begin Excel stream
              xlsWriteLabel(0,0,"This is a label"); // write a label in A1, use for dates too
              xlsWriteNumber(0,1,9999); // write a number B1
              xlsEOF(); // close the stream
              ?>

                a year later

                You could also just setup a table which translates nicely into excel format.

                  Write a Reply...