1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

PHP to Excel Tutorial

Discussion in 'PHP' started by mad4, Feb 24, 2006.

  1. #1
    I needed to create a script to output an Excel file and had no idea how to do it. Searching google I found lots of classes to accomplish this but they all looked to be way to complicated for a simple task.

    Having now done this the easy way I thought I should post a quick and simple complete script for anybody else looking to do this.

    
    <?php 
    $line1="ID\tProduct\tColor\tSales\t";
    $line2="1\tPrinter\tGrey\t13\t";
    $line3="2\tCD\tBlue\t15\t";
    $line4="3\tDVD\tRed\t7\t";
    $line5="4\tMonitor\tGreen\t4\t";
    $line6="5\tTelephone\tBlack\t2\t";
    
    $data="$line1\n$line2\n$line3\n$line4\n$line5\n$line6\n";
     
    header("Content-type: application/x-msdownload"); 
    header("Content-Disposition: attachment; filename=extraction.xls"); 
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    print "$header\n$data"; 
    ?>
    PHP:
    Thats all you need for a simple spreadsheet. Open up this file in your browser and see what you get.

    The basics are using \n for a line break and \t for a column break.
    SEMrush
    Hope this is useful for somebody. :)
     
    mad4, Feb 24, 2006 IP
    jestep likes this.
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Great info.

    Do you know how to create a file in excel formatted csv?

    Also, Is there any way to pre-format columns, for say zip codes or some other excel format?
     
    jestep, Feb 24, 2006 IP
  3. mad4

    mad4 Peon

    Messages:
    6,989
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #3
    To create a csv I think you would change the header to be a .csv file instead of a .xls and follow the standard csv format rather than using \t for a column break.

    I am sure that the classes available have many more functions (some even can draw charts) but haven't investigated them in depth.
     
    mad4, Feb 24, 2006 IP
  4. jlofriend

    jlofriend Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I got this..
     
    jlofriend, May 2, 2006 IP
  5. jlofriend

    jlofriend Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    OK
    now it worked
    the problem was that it WAS NOT on the first line
    Sorry!!!
     
    jlofriend, May 2, 2006 IP
  6. Big 'G'

    Big 'G' Member

    Messages:
    89
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    48
    #6
    The solution mad4 shows is good, i may self have found the need for php-excel currently i use the pear package excel writer and have to say it is fairly easy to use, with the exception of sheets required large amounts of formating.
    However in the early stages i found this article to be more than usefull and game me some advanced uses, using the basic function as shown above.
    http://www.phpfreaks.com/tutorials/114/0.php
     
    Big 'G', May 2, 2006 IP
  7. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    Hate to dig up old topics, but does anyone have a php script / function / class etc., that will convert an excel file back to php?

    I need to pull a file from excel into php, not into mysql!
     
    jestep, May 17, 2006 IP
  8. mad4

    mad4 Peon

    Messages:
    6,989
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #8
    php isn't suitable for storing the data from an excel file. Can you not store the data in a text file or sql database?
     
    mad4, May 18, 2006 IP
  9. Big 'G'

    Big 'G' Member

    Messages:
    89
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    48
    #9
    You can import excel file if it is in flat file format. You will ofcourse lose the formating and such
     
    Big 'G', May 18, 2006 IP
  10. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #10
    I know how to do it with a .csv or tab delimited text file. I would like to figure out how to do it with a .xls file instead. I only need the first sheet of the document and I don't need to keep any excel formatting. Saving it as a csv adds an extra step, and for this purpose it creates a lot of extra work.
     
    jestep, May 18, 2006 IP
  11. MilkyJoe

    MilkyJoe Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Hey,

    any ideas on how this could be done without the need of creating a new excel spreadsheet?
    What I am looking to do is simply write into an existing excel spreadsheet so that I may view it at a later point.

    Any help would be much appreciated!

    Milky Joe
     
    MilkyJoe, Aug 24, 2010 IP
  12. iamyuji

    iamyuji Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Thanks for the code. I wonder how you can set the width of the cell.
     
    iamyuji, Jan 5, 2011 IP