Results 1 to 10 of 10

Thread: Export MySQL data to Excel in PHP

  1. #1
    Join Date
    Jan 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Export MySQL data to Excel in PHP

    I've been able to export any data from MySQL to a csv file. But I've been having problems with stripping out all html code, and whenever there is a comma in the data, it sends all data to the next cell in excel. I included a str_replace function, but that is still not entirely working.

    Let me show you what I've already done.


    PHP Code:
    <?php if (!$HTTP_GET_VARS['submit']) { ?>

    <?php
    echo "Export and Save Customer Data onto your Local Machine";
    echo 
    '<form action="'$phpself.'">';
    echo 
    '<input type="submit" value="Export" name="submit"></form>';
    ?>

    <?php
    }
    else
    {
    $contents="Board Category,Questions,Answers\n";
    $user_query mysql_query('select
    f.faqdesk_id
    , c.categories_id
    , f.faqdesk_question
    , c.categories_name
    , f.faqdesk_answer_short
    from faqdesk_description as f
    join faqdesk_to_categories as f2c
    on f.faqdesk_id = f2c.faqdesk_id
    join faqdesk_categories_description as c
    on f2c.categories_id = c.categories_id'
    );
    while(
    $row mysql_fetch_array($user_query))
    {
    $contents.=$row[categories_name].",";
    $contents.=$row[faqdesk_question].",";
    $answer str_replace(',''\,'$row[faqdesk_answer_short]); // escape internalt commas
    $contents.=$answer."\n";
    }
    $contents strip_tags($contents); // remove html and php tags etc.
    Header("Content-Disposition: attachment; filename=export.csv");
    print 
    $contents;

    ?>
    It stipped out html tags, but it is still leaving in &nbsp;

    I'm sure there is a fix or replacement function for that. However, the commas are still giving separation problems, even with the str_replace function.

    You can see the excel results here: www.mastertheboard.com/board_topic_export3.php

    It is a list of questions and answers for the military soldiers in my units.
    There is also some other formmating issues having to do with numbered bullets. But I'm not sure how that is fixed.
    Any ideas?
    Last edited by Snookerman; 05-10-2009 at 03:59 PM. Reason: added [php] and [icode] tags

  2. #2
    Join Date
    Feb 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    $text = str_replace("&nbsp;"," ",$text);

    that will replace all &nbsp; in $text to space.

  3. #3
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    PHP Code:
    <?php
    class database
    {
        private 
    $db_handle;
        private 
    $user_name;
        private 
    $password;
        private 
    $data_base;
        private 
    $host_name;
        private 
    $sql;
        private 
    $results;

        function 
    __construct($host="localhost",$user,$passwd)
        {
            
    $this->db_handle mysql_connect($host,$user,$passwd);
        }

        function 
    dbSelect($db)
        {
            
    $this->data_base $db;
            if(!
    mysql_select_db($this->data_base$this->db_handle))
            {
                
    error_log(mysql_error(), 3"/phplog.err");
                die(
    "Error connecting to Database");
            }
        }
        
        function 
    executeSql($sql_stmt)
        {
            
    $this->sql $sql_stmt;
            
    $this->result mysql_query($this->sql);
        }
        function 
    returnResults()
        {
            return 
    $this->result;
        }
    }

    $user "root";
    $passwd "";
    $db "newTest"
    $sql "SELECT * FROM EMP ORDER BY empname";

    $dbObject = new database($host,$user,$passwd);
    $dbObject->dbSelect($db);
    $dbObject->executeSql($sql);


    $res $dbObject->returnResults();

    $newFileName "emp_names.csv";

    $fpWrite fopen("C:\\$newFileName""w");

    $nameStr "";

    while(
    $record mysql_fetch_object($res))
    {
        
    $name $record->empname;
        
        
    $nameArray explode(",",$name);
        
        if(
    count($nameArray) > 1)
        {
                
    $nameTemp "";
                for(
    $i=0;$i count($nameArray); $i++)
                {
                    
    $nameTemp $nameTemp $nameArray[$i];
                    
                    if(
    $i != (count($nameArray) - 1))
                        
    $nameTemp $nameTemp "&sbquo;";
                }
                
    $name $nameTemp;
        }
        
        
    $nameStr $nameStr.$name.",";
    }


    fwrite($fpWrite,$nameStr);

    echo 
    "File operation has been completed successfully!<br><br>";
    ?>
    Try the above code it needs optimization but the core thing works that mean it retrieves database fields from the DB and insert (in this case as only one record you can change it accoridng to your needs I just want to show how we can keep , within a db field)

  4. #4
    Join Date
    Dec 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    I want to design a site with form being send to my e-mail account and i want to be creating a profile for the visitor that send me an e-mail through the form....I was think of using MS Access as a Database since is just for 10-15 profile.

    I want the MS Access to stand as the Database the will be check their username and password whether is correct...before they can log in into there profiles.

    I was also looking for a script that i can use as a progress report which will look likes a plug in when they click an Hyper link in there profile(The script should display in percentage).

    Thanks

  5. #5
    Join Date
    May 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Exporting mysql data to excel

    Hiiii,

    I want to export mysql data to excel file with php

    There are the things i want :
    1. It should display the table on a page and we will be able to select the field with a radio button.
    2. Then the selected fields should get exported to a excel file when we click on the submit button.
    3. On the top of the excel file, it should display the field names and below it should show all the records present in the table.

    Does anybody know how to do this ........i will be thankful if anybody will put the source code for this ...i need it ...its urgent.

  6. #6
    Join Date
    Jul 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    i have one drop down list which is dynamically generate reports.can someone help me out to generate excel report based on that drop down list?Thanks in advance.

  7. #7
    Join Date
    Nov 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    This is class export very good UTF-8 encoding. I know it when i working
    1. File load data
    PHP Code:
    <?php

    // load library
    require 'php-excel.class.php';

    // create a simple 2-dimensional array
    $data = array(
            
    => array ('Name''Surname'),
            array(
    'Schwarz''Oliver'),
            array(
    'Test''Peter')
            );

    // generate file (constructor parameters are optional)
    $xls = new Excel_XML('UTF-8'false'Workflow Management');
    $xls->addArray($data);
    $xls->generateXML('Output_Report_WFM');

    ?>
    2.File Library. Create name of it is : php-excel.class.php
    PHP Code:
    <?php

    /**
     * Simple excel generating from PHP5
     *
     * @package Utilities
     * @license http://www.opensource.org/licenses/mit-license.php
     * @author Oliver Schwarz <oliver.schwarz@gmail.com>
     * @version 1.0
     */

    /**
     * Generating excel documents on-the-fly from PHP5
     * 
     * Uses the excel XML-specification to generate a native
     * XML document, readable/processable by excel.
     * 
     * @package Utilities
     * @subpackage Excel
     * @author Oliver Schwarz <oliver.schwarz@vaicon.de>
     * @version 1.1
     * 
     * @todo Issue #4: Internet Explorer 7 does not work well with the given header
     * @todo Add option to give out first line as header (bold text)
     * @todo Add option to give out last line as footer (bold text)
     * @todo Add option to write to file
     */
    class Excel_XML
    {

        
    /**
         * Header (of document)
         * @var string
         */
            
    private $header "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";

            
    /**
             * Footer (of document)
             * @var string
             */
            
    private $footer "</Workbook>";

            
    /**
             * Lines to output in the excel document
             * @var array
             */
            
    private $lines = array();

            
    /**
             * Used encoding
             * @var string
             */
            
    private $sEncoding;
            
            
    /**
             * Convert variable types
             * @var boolean
             */
            
    private $bConvertTypes;
            
            
    /**
             * Worksheet title
             * @var string
             */
            
    private $sWorksheetTitle;

            
    /**
             * Constructor
             * 
             * The constructor allows the setting of some additional
             * parameters so that the library may be configured to
             * one's needs.
             * 
             * On converting types:
             * When set to true, the library tries to identify the type of
             * the variable value and set the field specification for Excel
             * accordingly. Be careful with article numbers or postcodes
             * starting with a '0' (zero)!
             * 
             * @param string $sEncoding Encoding to be used (defaults to UTF-8)
             * @param boolean $bConvertTypes Convert variables to field specification
             * @param string $sWorksheetTitle Title for the worksheet
             */
            
    public function __construct($sEncoding 'UTF-8'$bConvertTypes false$sWorksheetTitle 'Table1')
            {
                    
    $this->bConvertTypes $bConvertTypes;
                
    $this->setEncoding($sEncoding);
                
    $this->setWorksheetTitle($sWorksheetTitle);
            }
            
            
    /**
             * Set encoding
             * @param string Encoding type to set
             */
            
    public function setEncoding($sEncoding)
            {
                
    $this->sEncoding $sEncoding;
            }

            
    /**
             * Set worksheet title
             * 
             * Strips out not allowed characters and trims the
             * title to a maximum length of 31.
             * 
             * @param string $title Title for worksheet
             */
            
    public function setWorksheetTitle ($title)
            {
                    
    $title preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/"""$title);
                    
    $title substr ($title031);
                    
    $this->sWorksheetTitle $title;
            }

            
    /**
             * Add row
             * 
             * Adds a single row to the document. If set to true, self::bConvertTypes
             * checks the type of variable and returns the specific field settings
             * for the cell.
             * 
             * @param array $array One-dimensional array with row content
             */
            
    private function addRow ($array)
            {
                
    $cells "";
                    foreach (
    $array as $k => $v):
                            
    $type 'String';
                            if (
    $this->bConvertTypes === true && is_numeric($v)):
                                    
    $type 'Number';
                            endif;
                            
    $v htmlentities($vENT_COMPAT$this->sEncoding);
                            
    $cells .= "<Cell><Data ss:Type=\"$type\">" $v "</Data></Cell>\n"
                    endforeach;
                    
    $this->lines[] = "<Row>\n" $cells "</Row>\n";
            }

            
    /**
             * Add an array to the document
             * @param array 2-dimensional array
             */
            
    public function addArray ($array)
            {
                    foreach (
    $array as $k => $v)
                            
    $this->addRow ($v);
            }


            
    /**
             * Generate the excel file
             * @param string $filename Name of excel file to generate (...xls)
             */
            
    public function generateXML ($filename 'excel-export')
            {
                    
    // correct/validate filename
                    
    $filename preg_replace('/[^aA-zZ0-9\_\-]/'''$filename);
            
                    
    // deliver header (as recommended in php manual)
                    
    header("Content-Type: application/vnd.ms-excel; charset=" $this->sEncoding);
                    
    header("Content-Disposition: inline; filename=\"" $filename ".xls\"");

                    
    // print out document to the browser
                    // need to use stripslashes for the damn ">"
                    
    echo stripslashes (sprintf($this->header$this->sEncoding));
                    echo 
    "\n<Worksheet ss:Name=\"" $this->sWorksheetTitle "\">\n<Table>\n";
                    foreach (
    $this->lines as $line)
                            echo 
    $line;

                    echo 
    "</Table>\n</Worksheet>\n";
                    echo 
    $this->footer;
            }

    }

    ?>
    [B]- Keep copyright when using it.
    Last edited by ddadmin; 11-15-2010 at 08:42 PM.

  8. #8
    Join Date
    Jan 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Hi

    Same thing how can i do value from database(mysql).plz reply me.

  9. #9
    Join Date
    Apr 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    HI. I know this thread is 1 year old.

    I have a problem on this issue. I have a table with 20 columns and how do I put in array all these values​​? I try like this but don't work. help me?

    $xls = new Excel_XML('UTF-8', false, 'Workflow Management');
    while($samthing=DBarray($select))
    {
    $data=array($samthing['name'],$samthing['phon',....);
    $xls->addArray($data);

    }
    $xls->generateXML('Output_Report_WFM');

  10. #10
    Join Date
    May 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Array and mysql database

    The class is extra! I tested class and it's work fine.
    If you want to export more columns from table in excel here is example:

    <?php
    require 'php-excel.class.php';
    require 'konekcija.php';

    $data[] = array('Oblast', 'Oznaka','Duzina');
    $sql = mysql_query("SELECT OBLAST,OZNAKA,DUZINA FROM pravci") or die("died");
    while($row = mysql_fetch_array($sql)){
    $data[] = array($row['OBLAST'],$row['OZNAKA'],$row['DUZINA']);
    }

    // generate file (constructor parameters are optional)
    $xls = new Excel_XML('UTF-8', false, 'Workflow Management');
    $xls->addArray($data);
    $xls->generateXML('Eksport_pravci');
    ?>

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
  •