SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Addict bimalpoudel's Avatar
    Join Date
    Feb 2009
    Location
    Kathmandu, Nepal
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Include multilined paragraphs in CSV file?

    How can I produce a CSV file that will contain multi-lined paragraph text when opened in Excel?
    Currently, I am suffering form the new line moving into to next line.
    Bimal Poudel @ Sanjaal Framework over Smarty Template Engine
    ASKING INTERESTING QUESTIONS ON SITEPOINT FOURM

    Hire for coding support - PHP/MySQL

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi,

    Use this class
    Code:
     
    <?php
    /*
        This program is free software: you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation, either version 3 of the License, or
        (at your option) any later version.
    
        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.
    
        You should have received a copy of the GNU General Public License
        along with this program.  If not, see <http://www.gnu.org/licenses/>.
    */
    
    /*
        
        Filename    : export.xls.class.php
        Description    : A small light weight PHP class to allow the creation of simple xls excel spreadsheets from array data.
        Version     : 1.01
        Author         : Leenix
        Website        : http://www.leenix.co.uk
    */
    
    /*
        Change Log
            V1 - First Release
             1.01 - Fixed UTF8 Issue
     */
    
    class ExportXLS {
    
        private $filename;    //Filename which the excel file will be returned as
        private $headerArray;    // Array which contains header information
        private $bodyArray;    // Array with the spreadsheet body
        private $rowNo = 0;    // Keep track of the row numbers
    
    
        #Class constructor
        function ExportXLS($filename) { 
            $this->filename = $filename;
        }
    
    
        /*
        -------------------------
        START OF PUBLIC FUNCTIONS
        -------------------------
        */
    
        public function addHeader($header) {
        #Accepts an array or var which gets added to the top of the spreadsheet as a header.
    
            if(is_array($header)) {
                $this->headerArray[] = $header;
            }
            else
            {
                $this->headerArray[][0] = $header;
            }
        }
    
        public function addRow($row) {
        #Accepts an array or var which gets added to the spreadsheet body
    
            if(is_array($row)) {
                #check for multi dim array
                if(is_array($row[0])) {
                    foreach($row as $key=>$array) {
                        $this->bodyArray[] = $array;
                    }
                }
                else
                {
                    $this->bodyArray[] = $row;
                }            
            }
            else
            {
                $this->bodyArray[][0] = $row;
            }
        
        }
        
        public function returnSheet() {
        # returns the spreadsheet as a variable
    
            #build the xls
            return $this->buildXLS();
        }
    
        public function sendFile() {
    
            #build the xls
            $xls = $this->buildXLS();
    
            #send headers
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");
            header("Content-Disposition: attachment;filename=".$this->filename);
            header("Content-Transfer-Encoding: binary ");
    
            echo $xls;
            return 'Should have printed but didnt';
            exit;
        }
    
    
        /*
        --------------------------
        START OF PRIVATE FUNCTIONS
        --------------------------
        */
    
        private function buildXLS() {
        # build and return the xls 
        
            #Excel BOF
            $xls = pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    
            #build headers
            if(is_array($this->headerArray)) {
                $xls .= $this->build($this->headerArray);
            }
    
            #build body
            if(is_array($this->bodyArray)) {
                $xls .= $this->build($this->bodyArray);
            }
    
            $xls .= pack("ss", 0x0A, 0x00);
    
            return $xls;
        }
    
        private function build($array) {
        #build and return the headers 
    
            foreach($array as $key=>$row) {
                $colNo = 0;
                foreach($row as $key2=>$field) {
                    if(is_numeric($field)) {
                        $build .= $this->numFormat($this->rowNo, $colNo, $field);
                    }
                    else
                    {
                        $build .= $this->textFormat($this->rowNo, $colNo, $field);
                    }
    
                    $colNo++;
                }
                $this->rowNo++;
            }
    
            return $build;
        }
    
        private function textFormat($row, $col, $data) {
        # format and return the field as a header
            $data = utf8_decode($data);
            $length = strlen($data);
            $field = pack("ssssss", 0x204, 8 + $length, $row, $col, 0x0, $length);
            $field .= $data;
    
            return $field; 
        }
            
    
        private function numFormat($row, $col, $data) {
        # format and return the field as a header
                $field = pack("sssss", 0x203, 14, $row, $col, 0x0);
                $field .= pack("d", $data); 
            
            return $field; 
        }
    }
    ?>
    Then implement the row headers as an single dimensional array and rows can either be a multi or single dimensional arrays:
    Code:
     
    require_once('./libs/page_queries/pdo_db.php');
    require_once('./libs/common/Session.php');
    require_once('libs/includes/utilities.php');
    $o_Db = new PdoDb();
    $o_Session = new Session();
    $o_Utilities = new Utilities($o_Session);
    $data_id = null;
     $data_id = $o_Session->get('id');
    $sql = "
    SELECT
      id
      , serialized_data
    FROM
     serialized_data
    WHERE
     id = $data_id
    ";
    $o_Db->setSQL($sql);
    $data = $o_Db->runFetchAll();
    $data = $o_Utilities->unSerializeData($data[0]['serialized_data']);
    $head = null;
    $head = $data[0];
    $rows = $data[1];
    $row = array();
    $head_count = count($head);
    $nums_of_rows = null;
    $nums_of_rows = count($rows[0]);
    $index_count = null;
    $index_count = count($rows);
     for($i=0;$i < $nums_of_rows; $i++){
       $rw = array();
       for($y=1;$y <= $index_count; $y++){
         if($rows[$y][$i] != NULL){
           $rw[] = $rows[$y][$i];
         }
       }
       $row[] = $rw;
       $rw = null;
     }
    #include the export-xls.class.php file
    require_once('libs/common/export-xls.class.php');
    $date = date('Y-m-d');
    $file_key = htmlentities($_POST['key']);
    $filename = "$file_key_$date.xls";// The file name you want any resulting file to be called.
    #create an instance of the clas
    $xls = new ExportXLS($filename, $rows);
    #lets set some headers for top of the spreadsheet
     $hdr = "My Test Excel Page Title";// single first col text
    $xls->addHeader($hdr);
    #add blank line
    $hdr = null;
    $xls->addHeader($hdr);
     # header 
    $xls->addRow($head);//add header to xls body
    
    $head = null;
    $xls->addRow($row);//add data to xls body
    $row = null;
    # You can return the xls as a variable to use with;
    # $sheet = $xls->returnSheet();
    #
    # OR
    #
    # You can send the sheet directly to the browser as a file
    #
    $xls->sendFile();
    ?>
    ?>
    Hope this helps.

    Steve
    ictus==""


Tags for this Thread

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
  •