?
Solved

Mysql export table as excel

Posted on 2016-10-21
4
Medium Priority
?
71 Views
Last Modified: 2016-12-21
Could i let my users export the data from a php page?
I use php and mysql

I have php page and i print a table on this page, which has the data coming from mysql database.

So the question is: could my users download my table as an exel file on my php page?
0
Comment
Question by:Braveheartli
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
Evan Cutler earned 1000 total points
ID: 41854620
PHPExcel does a good job in Post 2007 versions of Excel:
https://phpexcel.codeplex.com/wikipage?title=Examples&referringTitle=Requirements

here's an example:
<?php
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");


// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->SetCellValue('D2', 'world!');

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');

		
// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 41855164
One possibility would be to write the data into a .CSV file.  Most client machines and browsers associate the .CSV file suffix with Excel.  If your data can be represented in a two-dimensional matrix, this is quite feasible.  You might try something like this.
<?php // demo/db_to_csv.php
/**
 * Export a database table to a CSV file for use in Excel
 */
error_reporting(E_ALL);
echo "<pre>";

// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL "GET" ARGUMENT
$table_name = '???';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv
= 'storage'
. DIRECTORY_SEPARATOR
. 'EXPORT_'
. date('Ymdhis')
. "_$table_name"
. '.csv'
;
$fp  = fopen($csv, 'w');
if (!$fp) trigger_error("UNABLE TO OPEN $csv", E_USER_ERROR);

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
if ($res->num_rows == 0)
{
    trigger_error("ERROR: $table_name HAS NO COLUMNS", E_USER_ERROR);
}
else
{
    while ($show_columns = $res->fetch_object())
    {
        $my_columns[] = $show_columns->Field;
    }
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) trigger_error("FAILURE WRITING COLUMN NAMES TO $csv", E_USER_ERROR);

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ITERATE OVER THE DATA SET
while ($row = $res->fetch_row())
{
    // WRITE THE COMMA-SEPARATED VALUES.
    if (!fputcsv($fp, $row)) trigger_error("FAILURE WRITING DATA TO $csv", E_USER_ERROR);
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>" . PHP_EOL;

Open in new window

0
 
LVL 1

Author Closing Comment

by:Braveheartli
ID: 41855169
Thank you
0
 
LVL 1

Author Comment

by:Braveheartli
ID: 41933580
Dear Ray Paseur,
Thank you very much,

Your code works perfectly fine,

however, In the CVS files there are some chracters like ı,ş etc can not be read
is there a way to correct that?

I try to put character set, but it didn't work

// SET THE CHARACTER SET
mysqli_set_charset($conn, "utf8mb4");
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question