How to Export Selected MySQL Data to an Excel Spread Sheet

Question

I am currently working on the export side of my site to allow users the ability to view data held on a MySQL database. Currently I have a working solution that passes all the data selected in a query to a .CSV file.

The problem I am having is that I want to be able to let users select their own data and then be able to export the displayed data to a spread sheet rather than the whole table.

My first thought to solving this problem would be to perhaps store the query executed into a variable and then include it when using the export script.

Would this method be recommended or am I straying on the wrong path? If anyone can help I would be very grateful.

Also if this question is not suitable, please can you justify why. All feedback will be useful for future posts.

have you looked at the following post http://stackoverflow.com/questions/14914957/creating-csv-with-php — Liam Sorsby, Oct 29 '13 at 14:15
@LiamSorsby I have looked at the question that you have suggested. From what I gather, would it be an idea to put selected data into an array and then pass that information on when exporting? — Floyd, Oct 29 '13 at 14:23
Such a solution should already exist. Please use a search engine (maybe try the terms: "php mysql csv export"). If you have a programming question you should ask it. But asking the user for the SQL query to start an export sounds like the worst solution. — feeela, Oct 29 '13 at 14:30
It would only be a god idea to fetch the data into an array then pass the array on to export if you want to use a lot of memory and increase the processing time. Do you want a csv file or an Excel file? — Mark Baker, Oct 29 '13 at 14:30
@feeela I think you have probably got the wrong impression from my question, perhaps maybe my grammar or poor explaining. Users would enter for example a name into a search and then the MySQL query would return the data found in the table. I would then want to export the returned data to a csv file. — Floyd, Oct 29 '13 at 14:36
@MarkBaker Originally I wanted to pass on to a excel file. Whilst doing my research I found that passing to a CSV would be more conventional than Excel. Excel has horrible auto formatting and for the basic data I'm passing through, CSV satisfies my intentions. — Floyd, Oct 29 '13 at 14:39

score 2 · Accepted Answer · answered Oct 29 '13 at 14:33

You need to use phpexcel library :

this full example I wrote it before

<?php
include("classes/PHPExcel.php");
    set_time_limit(0);

$sql="Select * from datbase";

// Execute the database query
$result = mysql_query($sql) or die(mysql_error());

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel(); 
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0); 
// Initialise the Excel row number
$rowCount = 1; 
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){ 
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)

    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['user_id']); 
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['email'])); 

    $objPHPExcel->getActiveSheet()->SetCellValue('c'.$rowCount,$row['email']); 

     $objPHPExcel->getActiveSheet()->SetCellValue('d'.$rowCount,$row['email']); 
     $objPHPExcel->getActiveSheet()->SetCellValue('e'.$rowCount,$row['email']); 
      $objPHPExcel->getActiveSheet()->SetCellValue('f'.$rowCount,$row['email']); 
    // Increment the Excel row counter
    $rowCount++;      

} 

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save('all_data.xlsx'); 



?>

For more information ad examples : https://phpexcel.codeplex.com/wikipage?title=Examples

Thank you for your answer, I will look into this. I have been recommended phpexcel in the past by other users — Floyd, Oct 29 '13 at 14:44

How to Export Selected MySQL Data to an Excel Spread Sheet

1 Answers1