+ Reply to Thread
Results 1 to 8 of 8

Thread: Creating Excel Spreadsheet

  1. #1
    Learning Programmer Paradine is an unknown quantity at this point
    Join Date
    Oct 2006
    Posts
    48

    Creating Excel Spreadsheet

    Using PHP to parse data how can I then convert that data into an Excel Spreadsheet file?

    EX:

    I retrieve A B C
    and then next row I get 1 2 3

    How can I place that in a spreadsheet looking like this:

    A B C
    1
    2
    3

  2. #2
    Co-Administrator John is a glorious beacon of light John is a glorious beacon of light John is a glorious beacon of light John is a glorious beacon of light John is a glorious beacon of light John's Avatar
    Join Date
    Jul 2006
    Age
    21
    Posts
    5,882
    Blog Entries
    25
    I dont believe there is an easy way to do it, I found this code over at phpclasses.org that seems to do what you want

    excelwriter.inc.php
    Code:
    <?php
        
         
    /*
         ###############################################
         ####                                       ####
         ####    Author : Harish Chauhan            ####
         ####    Date   : 31 Dec,2004               ####
         ####    Updated:                           ####
         ####                                       ####
         ###############################################

         */

        
         /*
         * Class is used for save the data into microsoft excel format.
         * It takes data into array or you can write data column vise.
         */


        
    Class ExcelWriter
        
    {
                
            var 
    $fp=null;
            var 
    $error;
            var 
    $state="CLOSED";
            var 
    $newRow=false;
            
            
    /*
            * @Params : $file  : file name of excel file to be created.
            * @Return : On Success Valid File Pointer to file
            *             On Failure return false    
            */
            
            
    function ExcelWriter($file="")
            {
                return 
    $this->open($file);
            }
            
            
    /*
            * @Params : $file  : file name of excel file to be created.
            *             if you are using file name with directory i.e. test/myFile.xls
            *             then the directory must be existed on the system and have permissioned properly
            *             to write the file.
            * @Return : On Success Valid File Pointer to file
            *             On Failure return false    
            */
            
    function open($file)
            {
                if(
    $this->state!="CLOSED")
                {
                    
    $this->error="Error : Another file is opend .Close it to save the file";
                    return 
    false;
                }    
                
                if(!empty(
    $file))
                {
                    
    $this->fp=@fopen($file,"w+");
                }
                else
                {
                    
    $this->error="Usage : New ExcelWriter('fileName')";
                    return 
    false;
                }    
                if(
    $this->fp==false)
                {
                    
    $this->error="Error: Unable to open/create File.You may not have permmsion to write the file.";
                    return 
    false;
                }
                
    $this->state="OPENED";
                
    fwrite($this->fp,$this->GetHeader());
                return 
    $this->fp;
            }
            
            function 
    close()
            {
                if(
    $this->state!="OPENED")
                {
                    
    $this->error="Error : Please open the file.";
                    return 
    false;
                }    
                if(
    $this->newRow)
                {
                    
    fwrite($this->fp,"</tr>");
                    
    $this->newRow=false;
                }
                
                
    fwrite($this->fp,$this->GetFooter());
                
    fclose($this->fp);
                
    $this->state="CLOSED";
                return ;
            }
            
    /* @Params : Void
            *  @return : Void
            * This function write the header of Excel file.
            */
                                         
            
    function GetHeader()
            {
                
    $header = <<<EOH
                    <html xmlns:o="urn:schemas-microsoft-com:office:office"
                    xmlns:x="urn:schemas-microsoft-com:office:excel"
                    xmlns="http://www.w3.org/TR/REC-html40">

                    <head>
                    <meta http-equiv=Content-Type content="text/html; charset=us-ascii">
                    <meta name=ProgId content=Excel.Sheet>
                    <!--[if gte mso 9]><xml>
                     <o:DocumentProperties>
                      <o:LastAuthor>Sriram</o:LastAuthor>
                      <o:LastSaved>2005-01-02T07:46:23Z</o:LastSaved>
                      <o:Version>10.2625</o:Version>
                     </o:DocumentProperties>
                     <o:OfficeDocumentSettings>
                      <o:DownloadComponents/>
                     </o:OfficeDocumentSettings>
                    </xml><![endif]-->
                    <style>
                    <!--table
                        {mso-displayed-decimal-separator:"\.";
                        mso-displayed-thousand-separator:"\,";}
                    @page
                        {margin:1.0in .75in 1.0in .75in;
                        mso-header-margin:.5in;
                        mso-footer-margin:.5in;}
                    tr
                        {mso-height-source:auto;}
                    col
                        {mso-width-source:auto;}
                    br
                        {mso-data-placement:same-cell;}
                    .style0
                        {mso-number-format:General;
                        text-align:general;
                        vertical-align:bottom;
                        white-space:nowrap;
                        mso-rotate:0;
                        mso-background-source:auto;
                        mso-pattern:auto;
                        color:windowtext;
                        font-size:10.0pt;
                        font-weight:400;
                        font-style:normal;
                        text-decoration:none;
                        font-family:Arial;
                        mso-generic-font-family:auto;
                        mso-font-charset:0;
                        border:none;
                        mso-protection:locked visible;
                        mso-style-name:Normal;
                        mso-style-id:0;}
                    td
                        {mso-style-parent:style0;
                        padding-top:1px;
                        padding-right:1px;
                        padding-left:1px;
                        mso-ignore:padding;
                        color:windowtext;
                        font-size:10.0pt;
                        font-weight:400;
                        font-style:normal;
                        text-decoration:none;
                        font-family:Arial;
                        mso-generic-font-family:auto;
                        mso-font-charset:0;
                        mso-number-format:General;
                        text-align:general;
                        vertical-align:bottom;
                        border:none;
                        mso-background-source:auto;
                        mso-pattern:auto;
                        mso-protection:locked visible;
                        white-space:nowrap;
                        mso-rotate:0;}
                    .xl24
                        {mso-style-parent:style0;
                        white-space:normal;}
                    -->
                    </style>
                    <!--[if gte mso 9]><xml>
                     <x:ExcelWorkbook>
                      <x:ExcelWorksheets>
                       <x:ExcelWorksheet>
                        <x:Name>srirmam</x:Name>
                        <x:WorksheetOptions>
                         <x:Selected/>
                         <x:ProtectContents>False</x:ProtectContents>
                         <x:ProtectObjects>False</x:ProtectObjects>
                         <x:ProtectScenarios>False</x:ProtectScenarios>
                        </x:WorksheetOptions>
                       </x:ExcelWorksheet>
                      </x:ExcelWorksheets>
                      <x:WindowHeight>10005</x:WindowHeight>
                      <x:WindowWidth>10005</x:WindowWidth>
                      <x:WindowTopX>120</x:WindowTopX>
                      <x:WindowTopY>135</x:WindowTopY>
                      <x:ProtectStructure>False</x:ProtectStructure>
                      <x:ProtectWindows>False</x:ProtectWindows>
                     </x:ExcelWorkbook>
                    </xml><![endif]-->
                    </head>

                    <body link=blue vlink=purple>
                    <table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse: collapse;table-layout:fixed;'>
    EOH;
                return 
    $header;
            }

            function 
    GetFooter()
            {
                return 
    "</table></body></html>";
            }
            
            
    /*
            * @Params : $line_arr: An valid array
            * @Return : Void
            */
            
            
    function writeLine($line_arr)
            {
                if(
    $this->state!="OPENED")
                {
                    
    $this->error="Error : Please open the file.";
                    return 
    false;
                }    
                if(!
    is_array($line_arr))
                {
                    
    $this->error="Error : Argument is not valid. Supply an valid Array.";
                    return 
    false;
                }
                
    fwrite($this->fp,"<tr>");
                foreach(
    $line_arr as $col)
                    
    fwrite($this->fp,"<td class=xl24 width=64 >$col</td>");
                
    fwrite($this->fp,"</tr>");
            }

            
    /*
            * @Params : Void
            * @Return : Void
            */
            
    function writeRow()
            {
                if(
    $this->state!="OPENED")
                {
                    
    $this->error="Error : Please open the file.";
                    return 
    false;
                }    
                if(
    $this->newRow==false)
                    
    fwrite($this->fp,"<tr>");
                else
                    
    fwrite($this->fp,"</tr><tr>");
                
    $this->newRow=true;    
            }

            
    /*
            * @Params : $value : Coloumn Value
            * @Return : Void
            */
            
    function writeCol($value)
            {
                if(
    $this->state!="OPENED")
                {
                    
    $this->error="Error : Please open the file.";
                    return 
    false;
                }    
                
    fwrite($this->fp,"<td class=xl24 width=64 >$value</td>");
            }
        }
    ?>
    excel_example.php
    Code:
    <?php

        
    include("excelwriter.inc.php");
        
        
    $excel=new ExcelWriter("myXls.xls");
        
        if(
    $excel==false)    
            echo 
    $excel->error;
            
        
    $myArr=array("Name","Last Name","Address","Age");
        
    $excel->writeLine($myArr);

        
    $myArr=array("Sriram","Pandit","23 mayur vihar",24);
        
    $excel->writeLine($myArr);
        
        
    $excel->writeRow();
        
    $excel->writeCol("Manoj");
        
    $excel->writeCol("Tiwari");
        
    $excel->writeCol("80 Preet Vihar");
        
    $excel->writeCol(24);
        
        
    $excel->writeRow();
        
    $excel->writeCol("Harish");
        
    $excel->writeCol("Chauhan");
        
    $excel->writeCol("115 Shyam Park Main");
        
    $excel->writeCol(22);

        
    $myArr=array("Tapan","Chauhan","1st Floor Vasundhra",25);
        
    $excel->writeLine($myArr);
        
        
    $excel->close();
        echo 
    "data is write into myXls.xls Successfully.";
    ?>
    ref:
    PHP Classes - Class: Excel Writer

  3. #3
    Administrator Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan's Avatar
    Join Date
    Nov 2005
    Location
    Hendersonville, NC
    Posts
    24,556
    Blog Entries
    97
    Nice code. Alternatively, you can just write a text file that is comma (,) delimited. Use .xls as the extension and it loads fine in excel. Use line breaks for new rows.

  4. #4
    Newbie Ukelo is an unknown quantity at this point
    Join Date
    Jan 2007
    Posts
    1
    You might want to check the PEAR libraries (h t t p : / / pear.php.net). The "Spreadsheet_Excel_Writer" is a really comprehensive package to generate native Excel files.

  5. #5
    Programming God xtraze is an unknown quantity at this point
    Join Date
    Dec 2006
    Location
    Sri lanka
    Posts
    908
    Quote Originally Posted by Jordan View Post
    Nice code. Alternatively, you can just write a text file that is comma (,) delimited. Use .xls as the extension and it loads fine in excel. Use line breaks for new rows.
    CSV as Jordan has mentioned is far easier than any method lol, but only for small files and can be used for BIGGER ones too but hard to understand if you open with Notepad,

  6. #6
    Administrator Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan is a name known to all Jordan's Avatar
    Join Date
    Nov 2005
    Location
    Hendersonville, NC
    Posts
    24,556
    Blog Entries
    97
    Quote Originally Posted by xtraze View Post
    CSV as Jordan has mentioned is far easier than any method lol, but only for small files and can be used for BIGGER ones too but hard to understand if you open with Notepad,
    Very hard, and to format properly you need to use commas which can get confusing.

  7. #7
    Newbie prakalp is an unknown quantity at this point
    Join Date
    Jan 2007
    Posts
    7
    How interesting that this conversation is taking place. I have been searching without success for information regarding this exact topic.

    The fact remains that there are a number of classes written to handle data to and from the xls format.

    Jordan : Will not the csv format limit itself to single page spreadsheets?


    However, what I have been looking for is a component \ module or a php app that allows a user to input data within a GUI (primarily in the JOOMLA! framework) and produces complex spreadsheets. Alternatively, takes xls spreadsheets, writes to a db (mySQL) and presents the data to the user within the JOOMLA! framework.

    Any ideas or comments?

    Regards,
    Prakalp.

  8. #8
    Newbie prakalp is an unknown quantity at this point
    Join Date
    Jan 2007
    Posts
    7
    Irrelevant to this particular conversation, however, interesting anyways, is this particular site.

    ajaxXLS online based spreadsheet viewer is Live. - Keith Dsouza's Home

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Similar Threads

  1. excel help (i THINK this could be easy...)
    By shackrock in forum General Programming
    Replies: 1
    Last Post: 07-11-2007, 11:10 AM
  2. Help on Excel sheets!!!!!!!!!
    By sania21 in forum Visual Basic Programming
    Replies: 2
    Last Post: 07-02-2007, 05:41 AM
  3. Display an excel sheet
    By engr in forum ASP, ASP.NET and Coldfusion
    Replies: 0
    Last Post: 03-15-2007, 12:00 AM
  4. Creating an analog clock with ActionScript
    By AfTriX in forum Tutorials
    Replies: 2
    Last Post: 01-07-2007, 02:19 AM
  5. Inserting Data into an Excel Spreadsheet
    By Lop in forum C# Programming
    Replies: 3
    Last Post: 06-27-2006, 01:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts