Как распечатать отчет MySQL и экспортировать в Excel
У меня есть база данных сотрудников, которую я скопировал в master / slave, и я хотел бы создать отчет, который должен быть печатным и экспортируемым, чтобы преуспеть. Я использую xampp. Вот мой код:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Replication</title>
</head>
<body>
<?php
//connecting to the master server
$con = mysql_connect("localhost","justin","topdeveloper") or $con = false;
//if connection to the master server fails don\t connect to the slave
if ($con!=false) {
mysql_close($con);
//connecting to the slave server
$con = mysql_connect("localhost","root","") or die ("Could not connect to MySQL");
mysql_Select_db('clients',$con);
//the timestampdiff function gets the age of the person by subtracting the date_of_birth from the current date
$result=mysql_query("SELECT `first_name`,`second_name`,`last_name`,`id_number`,`phone_number`,`home_town`, TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) AS `age` FROM `bio_data` ORDER BY age DESC");
echo "<table border='1'>";
echo "<tr align='center'>";
echo"<th><font color='black'>" ."First Name"."</font></th>";
echo"<th><font color='black'>" ."Second Name"."</font></th>";
echo"<th><font color='black'>" ."Last Name"."</font></th>";
echo"<th><font color='black'>" ."Id Number"."</font></th>";
echo"<th><font color='black'>" ."Phone Number"."</font></th>";
echo"<th><font color='black'>" ."Home Town"."</font></th>";
echo"<th><font color='black'>" ."Age"."</font></th>";
echo "</tr>";
while($row = mysql_fetch_array($result)){
//echo "<table border='1'>";
echo "<tr align='center' >";
echo"<td><font color='black'>" .$row['first_name']."</font></td>";
echo"<td><font color='black'>" .$row['second_name']."</font></td>";
echo"<td><font color='black'>". $row['last_name']. "</font></td>";
echo"<td><font color='black'>". $row['id_number']. "</font></td>";
echo"<td><font color='black'>". $row['phone_number']. "</font></td>";
echo"<td><font color='black'>". $row['home_town']. "</font></td>";
echo"<td><font color='black'>". $row['age']. "</font></td>";
echo "</tr>";}
echo "</table>";
mysql_close($con);
}
?></body>
</html>
На данный момент я могу производить отображение записей на странице HTML
Решение
Я смог добавить класс Excel и написать функцию для загрузки файла (работает нормально), но у меня проблема с тем, что файл загружается автоматически при загрузке основного файла (index.php). Ниже приведены мои основные файлы php ,
index.php:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Replication APP</title>
<link rel="stylesheet" type="text/css" href="style/style.css">
</head>
<body>
<div id="header">
<h1>Replication APP</h1>
</div>
<div id="content">
<?php
error_reporting(0);
include("includes/config.php");
include("includes/excelwriter.class.php");
//the timestampdiff function gets the age of the person by subtracting the date_of_birth from the current date
$result=mysql_query("SELECT `first_name`,`second_name`,`last_name`,`id_number`,`phone_number`,`home_town`,
TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) AS `age` FROM `bio_data` ORDER BY age DESC");
echo "<table border='1' align='center'>";
echo "<tr align='center'>";
echo"<th><font color='black'>" ."First Name"."</font></th>";
echo"<th><font color='black'>" ."Second Name"."</font></th>";
echo"<th><font color='black'>" ."Last Name"."</font></th>";
echo"<th><font color='black'>" ."Id Number"."</font></th>";
echo"<th><font color='black'>" ."Phone Number"."</font></th>";
echo"<th><font color='black'>" ."Home Town"."</font></th>";
echo"<th><font color='black'>" ."Age"."</font></th>";
echo "</tr>";
while($row = mysql_fetch_array($result)){
//echo "<table border='1'>";
echo "<tr align='center' >";
echo"<td><font color='black'>" .$row['first_name']."</font></td>";
echo"<td><font color='black'>" .$row['second_name']."</font></td>";
echo"<td><font color='black'>". $row['last_name']. "</font></td>";
echo"<td><font color='black'>". $row['id_number']. "</font></td>";
echo"<td><font color='black'>". $row['phone_number']. "</font></td>";
echo"<td><font color='black'>". $row['home_town']. "</font></td>";
echo"<td><font color='black'>". $row['age']. "</font></td>";
echo "</tr>";}
echo "</table>";
require_once("export.php");
$export=download();
?>
<!--<a href="#" onClick=window.location='report.xls';>Export to Excel</a>-->
<a href="<?php echo $export;?>"> Export to Excel </a>
</div><div id="footer">
Copyright © Justin 2015
</div>
</body>
</html>
export.php:
<?php
error_reporting(0);
function download()
{
$excel=new ExcelWriter("report.xls");
if(!$excel)
{
echo $excel->error;
}
else
{
$A=array("First Name","Second Name","Last Name","Id Number","Phone Number","Home Town","Age");
$excel->writeLine($A);
}// now fetch data from database table, there is a new line create each time loop runs
if(!$result=mysql_query("SELECT `first_name`,`second_name`,`last_name`,`id_number`,`phone_number`,`home_town`, TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE()) AS `age` FROM `bio_data` ORDER BY age DESC"))
{
echo "something terrible happened";
}
else{
while($res=mysql_fetch_array($result))
{
$Arr=array($res['first_name'],$res['second_name'],$res['last_name'],$res['id_number'],$res['phone_number'],$res['home_town'],$res['age']);
$excel->writeLine($Arr);
}
}
if(file_exists($excel))
{
header('Content-Description:File Transfer');
header('Content-Type:application/octet-stream');
header('Content-Disposition:attachment;filename='.basename($excel));
header('Expires:0');
header('Cache-Control:must-revalidate');
header('Pragma:public');
header('Content-Length:'.filesize($excel));
readfile($excel);
exit;
}
}
?>
excelwriter.class.php:
<?php
/*
* Class is used for save the data into microsoft excel format.
* It takes data into array or you can write data column vise.
*/Class ExcelWriter
{
var $fp=null;
var $error;
var $state="CLOSED";
var $newRow=false;
/*
* @Params : $file : file name of excel file to be created.
* @Return : On Success Valid File Pointer to file
* On Failure return false
*/
function ExcelWriter($file="")
{
return $this->open($file);
}
/*
* @Params : $file : file name of excel file to be created.
* if you are using file name with directory i.e. test/myFile.xls
* then the directory must be existed on the system and have permissioned properly
* to write the file.
* @Return : On Success Valid File Pointer to file
* On Failure return false
*/
function open($file)
{
if($this->state!="CLOSED")
{
$this->error="Error : Another file is opend .Close it to save the file";
return false;
}
if(!empty($file))
{
$this->fp=@fopen($file,"w+");
}
else
{
$this->error="Usage : New ExcelWriter('fileName')";
return false;
}
if($this->fp==false)
{
$this->error="Error: Unable to open/create File.You may not have permmsion to write the file.";
return false;
}
$this->state="OPENED";
fwrite($this->fp,$this->GetHeader());
return $this->fp;
}
function close()
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if($this->newRow)
{
fwrite($this->fp,"</tr>");
$this->newRow=false;
}
fwrite($this->fp,$this->GetFooter());
fclose($this->fp);
$this->state="CLOSED";
return ;
}
/* @Params : Void
* @return : Void
* This function write the header of Excel file.
*/
function GetHeader()
{
$header = <<<EOH
<html xmlns:o="urn:schemas-microsoft-com:office:office"xmlns:x="urn:schemas-microsoft-com:office:excel"xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=ProgId content=Excel.Sheet>
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:LastAuthor>Sriram</o:LastAuthor>
<o:LastSaved>2005-01-02T07:46:23Z</o:LastSaved>
<o:Version>10.2625</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
white-space:normal;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>srirmam</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>10005</x:WindowHeight>
<x:WindowWidth>10005</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>135</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse: collapse;table-layout:fixed;'>
EOH;
return $header;
}
function GetFooter()
{
return "</table></body></html>";
}
/*
* @Params : $line_arr: An valid array
* @Return : Void
*/
function writeLine($line_arr)
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if(!is_array($line_arr))
{
$this->error="Error : Argument is not valid. Supply an valid Array.";
return false;
}
fwrite($this->fp,"<tr>");
foreach($line_arr as $col)
fwrite($this->fp,"<td class=xl24 width=64 >$col</td>");
fwrite($this->fp,"</tr>");
}
/*
* @Params : Void
* @Return : Void
*/
function writeRow()
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if($this->newRow==false)
fwrite($this->fp,"<tr>");
else
fwrite($this->fp,"</tr><tr>");
$this->newRow=true;
}
/*
* @Params : $value : Coloumn Value
* @Return : Void
*/
function writeCol($value)
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
fwrite($this->fp,"<td class=xl24 width=64 >$value</td>");
}
}
?>
пожалуйста, помогите. любые предложения по улучшению
Другие решения
Вы можете скачать PHPExcel Демо-код, чем реализовать соответствие вашим требованиям.
PHPExcel — это библиотека, написанная на чистом PHP и предоставляющая набор классов, которые позволяют писать и читать из различных форматов файлов электронных таблиц, таких как Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre / OpenOffice Calc .ods, Gnumeric, PDF, HTML, … Этот проект основан на стандарте Microsoft OpenXML и PHP.