CODEWITHSUNDEEP
phpmysqlexcel
Floyd
Floyd

Reputation: 71

How to Export Selected MySQL Data to an Excel Spread Sheet

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.

Upvotes: 1

Views: 2590

Answers (1)

Ahmad Samilo
Ahmad Samilo

Reputation: 914

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

Upvotes: 2

Related Questions