How should I go about exporting MYSQL data into Excel using PHP Excel

Question

This might be a fairly unusual question, but I'm truly stuck here. I'm reading data from a MYSQL database, and creating a table out of this data through PHP using Echo. Now, I'd like to move onto the next step and export this data (the data in the table) to an Excel document. I really like the way PHPExcel works, and how simple it is but I'm having a hard time figuring out the most efficient way of obtaining the data, and putting it in an excel document. Surely, I do not want to manually write out "A1", "A1 data" all the way down to Z1000 - catch my point? I believe that I should loop through everything. Now, here is where the question becomes tricky; is there some way (keep in mind this needs to be done through PHP) to read through the table cell by cell (loop) and assign the values to an array, then just loop through the array like A1, arr[0] etc?

I figured I'd have to use JavaScript to read through the table cell by cell, but then how could I assign the data to an array so that I could then use it as part of a PHP script - since PHPExcel is written in PHP.

Is this question making sense to any of you or am I missing a lot of information here? Let me know and I can edit this question so that it makes some more sense to you all.

Answer 1

<?php

//EDIT YOUR MySQL Connection Info:
$DB_Server = "localhost";        //your MySQL Server
$DB_Username = "root";                 //your MySQL User Name
$DB_Password = "";                //your MySQL Password
$DB_DBName = "school";                //your MySQL Database Name
$DB_TBLName = "s_question";                //your MySQL Table Name

if(isset($_POST['exp_stdque'])) {
    $exstdid = $_POST['expstd'];

//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//edit this to suit your needs
$sql = "Select * from $DB_TBLName WHERE std_id = $exstdid";

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = DATE('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @MYSQL_CONNECT($DB_Server, $DB_Username, $DB_Password)
     or DIE("Couldn't connect to MySQL:<br>" . MYSQL_ERROR() . "<br>" . MYSQL_ERRNO());
//select database
$Db = @MYSQL_SELECT_DB($DB_DBName, $Connect)
     or DIE("Couldn't select database:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());
//execute query
$result = @MYSQL_QUERY($sql,$Connect)
     or DIE("Couldn't execute query:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
IF (ISSET($w) && ($w==1))
{
     $file_type = "msword";
     $file_ending = "doc";
}ELSE {
     $file_type = "vnd.ms-excel";
     $file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
HEADER("Content-Type: application/$file_type");
HEADER("Content-Disposition: attachment; filename=database_dump.$file_ending");
HEADER("Pragma: no-cache");
HEADER("Expires: 0");

/*    Start of Formatting for Word or Excel    */

IF (ISSET($w) && ($w==1)) //check for $w again
{
     /*    FORMATTING FOR WORD DOCUMENTS ('.doc')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\n"; //new line character

     WHILE($row = MYSQL_FETCH_ROW($result))
     {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         FOR($j=0; $j<mysql_num_fields($result);$j++)
         {
         //define field names
         $field_name = MYSQL_FIELD_NAME($result,$j);
         //will show name of fields
         $schema_insert .= "$field_name:\t";
             IF(!ISSET($row[$j])) {
                 $schema_insert .= "NULL".$sep;
                 }
             ELSEIF ($row[$j] != "") {
                 $schema_insert .= "$row[$j]".$sep;
                 }
             ELSE {
                 $schema_insert .= "".$sep;
                 }
         }
         $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
         $schema_insert .= "\t";
         PRINT(TRIM($schema_insert));
         //end of each mysql row
         //creates line to separate data from each MySQL table row
         PRINT "\n----------------------------------------------------\n";
     }
}ELSE{
     /*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\t"; //tabbed character

     //start of printing column names as names of MySQL fields
     FOR ($i = 0; $i < MYSQL_NUM_FIELDS($result); $i++)
     {
         ECHO MYSQL_FIELD_NAME($result,$i) . "\t";
     }
     PRINT("\n");
     //end of printing column names

     //start while loop to get data
     WHILE($row = MYSQL_FETCH_ROW($result))
     {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         FOR($j=0; $j<mysql_num_fields($result);$j++)
         {
             IF(!ISSET($row[$j]))
                 $schema_insert .= "NULL".$sep;
             ELSEIF ($row[$j] != "")
                 $schema_insert .= "$row[$j]".$sep;
             ELSE
                 $schema_insert .= "".$sep;
         }
         $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
         //following fix suggested by Josue (thanks, Josue!)
         //this corrects output in excel when table fields contain \n or \r
         //these two characters are now replaced with a space
         $schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
         $schema_insert .= "\t";
         PRINT(TRIM($schema_insert));
         PRINT "\n";
     }
}
}

?>

Answer 2

The ideal solution is to create a platform-agnostic "Excel" file that can be opened by any spreadsheet program or text editor (not just Excel). By this I mean a basic CSV file.

Fortunately, no outside libraries are necessary: PHP has some built-in functions for reading/writing CSV files. So the steps to get data out of a database and into an Excel-readable csv file:

1. Query the db and iterate over the results: mysql_result^docs
2. Inside the loop that iterates over the DB results, use fputcsv^docs to write the csv file.

Now, you might be wondering how to get the csv file you created onto your own machine instead of on the server where you just created it. Fortunately, there's an easy solution for this as well.

Say you want the csv to be the actual content of the page you're generating. By that I mean, you want the browser to treat the PHP file as if it were itself a csv file.

Storing the CSV file in memory instead of on the disk

Instead of using a real file handle to write the csv in your fputcsv operations, open a file handle in memory to do the same thing. This has the advantage of being faster than writing to disk as well. To open a file handle in memory to use in your fputcsv calls you would do:

$tmp_handle = fopen('php://temp', 'r+');

This will open a file handle to memory instead of on the filesystem. Note that once the file handle hits the default of 2Mb in filesize PHP will switch it transparently to a temporary file on the filesystem. You can avoid this and force the file handle to stay in memory only by using instead:

$tmp_handle = fopen('php://memory', 'r+');

You will then use this handle in your calls to fputcsv:

fputcsv($tmp_handle, $fields);

You can find out more about php://memory and php://temp at the PHP manual entry on the topic.

Getting the browser to think it's a CSV file

The last thing you need to do to convince the browser the page is actually a csv file once you've generated the data is to send the correct HTTP content-type headers along with your data:

// generate my csv data here

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=MyVerySpecial.csv');

// reset the file handle's pointer to the beginning of the stream
rewind($tmp_handle);

// output the contents of the file handle to the browser
echo stream_get_contents($tmp_handle);

Answer 3

I have a suggestion. As an example if someone press the export button you make a POST request to a PHP page and do the same query you did to retrieve data from MySQL. One you get the data set. You can simply loop them and write in to excel. In below code the $dataset is MySQL result set.

$objPHPExcel = new PHPExcel();


$index = 1;
foreach($dataset as $subscriber)
{
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A' . $index, $subscriber['Email']);
    $index ++;
}

If you let users to modify data in the out put table in the screen. Let's say you have text boxes inside the table and you need them to save in to the excel file too. Then you name those elements as an array. Ex: txtName[] So once you get the POST you can handle that programatically.