1

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.

Improve this question
7
  • 3
    Simple: export a comma-delimited file. It just so happens PHP has a built-in function that does this: fputcsv ... Excel will open it right up Commented Jan 25, 2012 at 2:09
  • any reason not to use csv format? then you wont be limited to one spreadsheet application, smaller files and build in php functions for handling the data. Commented Jan 25, 2012 at 2:10
  • @rdlowrey either way, I need to somehow loop through the table to retrieve the values of which will be exported. So, my question remains - how to do this? Commented Jan 25, 2012 at 4:50
  • @Evan (1) Query the db and iterate over the mysql result (2) Inside the loop, use fputcsv to write the csv file. (3) The end. If you can't make it work, come back with the code you've tried and ask another question. Commented Jan 25, 2012 at 4:52
  • That wouldn't be a problem at all then. That's what I'll do, thanks. Commented Jan 25, 2012 at 4:55

3 Answers 3

Reset to default
3

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_resultdocs
  2. Inside the loop that iterates over the DB results, use fputcsvdocs 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);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

hehe this was not necessary, really. A well deserved answer.
the only problem that remains is that I cannot seem to write a file to local disk using fopen. I'm always getting the error: " failed to open stream: No such file or directory " and the code I'm using is as follows: $filename = trim("C:/file.csv"); $fp = fopen($filename, 'w');
Well for one thing, why would you use trim on a string that you already know doesn't have any extraneous characters on the ends? Second, while I've never used PHP in a windows environment, I would guess your filename should look like this: "C:\\file.csv" (note how there are two backslashes -- you need an extra one to escape the primary backslash)
I was trimming to be extra precautions as I've been at this over an hour now. However, using the path above creates a file in the remote directory called C:\file.csv. Very odd.
Perhaps I misunderstood what you were trying say? The fopen call is going to execute on the filesystem of whichever server is executing your php request. If that server is not the computer you're working on, it won't save it locally, it will save it on the remote server. If the PHP process is executing on your local machine, you'll see the file written to your local C:\ directory. A remote PHP process can't write to your local hard drive. Imagine what would happen if using my web site I could just write whatever information I wanted onto your PC. Bad stuff, that's what would happen.
|
0

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.

Improve this answer

Comments

0 
<?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";
     }
}
}

?>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.