Creating Excel Spreadsheet

Paradine · #1 (**permalink**) 01-10-2007, 09:12 AM

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

***John*** · #2 (**permalink**) 01-10-2007, 01:31 PM

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

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

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

**Jordan** · #3 (**permalink**) 01-10-2007, 06:04 PM

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.

Ukelo · #4 (**permalink**) 01-11-2007, 08:47 AM

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.

xtraze · #5 (**permalink**) 01-11-2007, 09:46 PM

Quote:

Originally Posted by Jordan

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,

**Jordan** · #6 (**permalink**) 01-15-2007, 09:09 AM

Quote:

Originally Posted by xtraze

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.

prakalp · #7 (**permalink**) 01-18-2007, 09:32 AM

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.

prakalp · #8 (**permalink**) 01-18-2007, 09:45 AM

Irrelevant to this particular conversation, however, interesting anyways, is this particular site.

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

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)