将MySQL数据导出到Excel中 [英] Export MySQL data to Excel in PHP
本文介绍了将MySQL数据导出到Excel中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将我的MySQL数据存入Excel文件,但是我的Excel单元格有问题。我的所有文本都转到一个单元格,我想在每个Excel单元格中具有每行的值。这是我的代码:
$ queryexport =(
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer ='。$ fk_customer。'
);
$ row = mysql_fetch_assoc($ queryexport);
$ result = mysql_query($ queryexport);
$ header =''; ($ i = 0; $ i $ $ count; $ i ++)
{
$ header。= mysql_field_name($ result,$ i)
}
while($ row = mysql_fetch_row($ result)){
$ line ='';
foreach($ row as $ value){
if(!isset($ value)|| $ value ==){
$ value =\t;
} else {
$ value = str_replace('','',$ value);
$ value =''。 $值。 ''。\t;
}
$ line。= $ value;
}
$ data。= trim($ line)\\\
;
$ data = str_replace(\r,,$ data);
if($ data ==){
$ data =\\ \\ nno匹配记录found\\\
;
}
}
标头(Content-type:application / vnd.ms-excel; name ='excel');
header(Content-Disposition:attachment; filename = exportfile.xls);
header(Pragma:no-cache);
header(Expires:0);
//输出数据
echo $ header。\\\
$ data;
mysql_close($ conn);`
解决方案
只需尝试以下内容: / p>
PHP部分:
?php
/ *******编辑行3-8 ******* /
$ DB_Server =localhost; // MySQL服务器
$ DB_Username =用户名; // MySQL用户名
$ DB_Password =密码; // MySQL密码
$ DB_DBName =databasename; // MySQL数据库名称
$ DB_TBLName =tablename; // MySQL表名称
$ filename =excelfilename; //文件名
/ *******你不需要编辑下面这一行******* /
//创建MySQL连接
$ sql = select $ from $ DB_TBLName;
$ Connect = @mysql_connect($ DB_Server,$ DB_Username,$ DB_Password)或死(无法连接到MySQL:< br>mysql_error()。< br>mysql_errno );
// select database
$ Db = @mysql_select_db($ DB_DBName,$ Connect)or die(Could not select database:< br>mysql_error()。< br> 。mysql_errno());
//执行查询
$ result = @mysql_query($ sql,$ Connect)or die(无法执行查询:< br>mysql_error()。< br> 。mysql_errno());
$ file_ending =xls;
//浏览器的头信息
header(Content-Type:application / xls);
header(Content-Disposition:attachment; filename = $ filename.xls);
header(Pragma:no-cache);
header(Expires:0);
/ ******* Excel格式开始******* /
//定义分隔符(定义Excel中的列和Excel中的列)
$ sep =\t; //标签字符
//开始打印列名作为MySQL字段的名称
($ i = 0; $ i< mysql_num_fields($ result); $ i ++){
echo mysql_field_name($ result,$ i)。 \t;
}
print(\\\
);
//打印列名结束
//开始while循环获取数据
while($ row = mysql_fetch_row($ result))
{
$ schema_insert = ; ($ j = 0; $ j< mysql_num_fields($ result); $ j ++)
{
if(!isset($ row [$ j]))
$ schema_insert。=NULL$ sep;
elseif($ row [$ j]!=)
$ schema_insert。=$ row [$ j]$ sep;
else
$ schema_insert。=。$ sep;
}
$ schema_insert = str_replace($ sep。$,,$ schema_insert);
$ schema_insert = preg_replace(/ \\\\\\\\\\\\\,$ schema_insert);
$ schema_insert。=\t;
print(trim($ schema_insert));
打印\\\
;
}
?>
我认为这可能有助于您解决问题。
I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:
$queryexport = ("
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer='".$fk_customer."'
");
$row = mysql_fetch_assoc($queryexport);
$result = mysql_query($queryexport);
$header = '';
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}
while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\nno matching records found\n";
}
}
header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");
// output data
echo $header."\n".$data;
mysql_close($conn);`
解决方案
Just Try With The Following :
PHP Part :
<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server
$DB_Username = "username"; //MySQL Username
$DB_Password = "password"; //MySQL Password
$DB_DBName = "databasename"; //MySQL Database Name
$DB_TBLName = "tablename"; //MySQL Table Name
$filename = "excelfilename"; //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/
//create MySQL connection
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");
/*******Start of Formatting for Excel*******/
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
while($row = mysql_fetch_row($result))
{
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
?>
I think this may help you to resolve your problem.
这篇关于将MySQL数据导出到Excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文