PhpNo Comments

default thumbnail

We can export the mysql table data into a excel file. Here is a code to export the table data. Just connect your database and mention table name.


$host = "localhost"; // your db host (ip/dn)
$user = "root"; // your db's privileged user account
$password = ""; // and it's password
$db_name = "serviceprovider"; // db name
$db = new mysqli($host,$user,$password,$db_name) or die(mysqli_error());
$select = "SELECT * FROM `order` ORDER BY o_id DESC";

mysqli_query($db,'SET NAMES utf8;');
$export = mysqli_query($db,$select); 
$fields = mysqli_num_fields($export); // by KAOSFORGE
while ($property = mysqli_fetch_field($export)) {
	 $col_title .= '<Cell ss:StyleID="2"><Data ss:Type="String">'.$property->name.'</Data></Cell>';

$col_title = '<Row>'.$col_title.'</Row>';

while($row = mysqli_fetch_row($export)) {
    $line = '';
    foreach($row as $value) {
        if ((!isset($value)) OR ($value == "")) {
            $value = '<Cell ss:StyleID="1"><Data ss:Type="String"></Data></Cell>\t';
        } else {
            $value = str_replace('"', '', $value);
            $value = '<Cell ss:StyleID="1"><Data ss:Type="String">' . $value . '</Data></Cell>\t';
        $line .= $value;
    $data .= trim("<Row>".$line."</Row>")."\n";

$data = str_replace("\r","",$data);

header("Content-Type: application/;");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
header("Expires: 0");

$xls_header = '<?xml version="1.0" encoding="utf-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Style ss:ID="1">
<Alignment ss:Horizontal="Left"/>
<Style ss:ID="2">
<Alignment ss:Horizontal="Left"/>
<Font ss:Bold="1"/>

<Worksheet ss:Name="Export">

$xls_footer = '</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

print $xls_header.$col_title.$data.$xls_footer;


Save this code in a file and run this file. It will ask to save the excel file.

Be the first to post a comment.

Add a comment