/**
* 导出为CSV,excel文件
* 参数的设置
*/
function downloadExcel(){
require_once('../conn.php');
header('Content-Type: text/html; charset=UTF-8');
$keyword=$_GET['keyword'];
if ($keyword=='全部') {
$sql="SELECT d.equnumber,e.DepName as usedepname,b.fieldname as newname,f.DepName as appdepname, c.fieldname as oldname ,d.Price,d.State FROM request AS A inner join equipment AS d on d.id=a.EquipmentID inner join field AS b on b.fieldid=a.userid inner join field AS c on c.fieldid=a.AppID inner join department AS e on e.depid=a.usedep inner join department AS f on f.depid=a.appdep WHERE 1";
}else{
$sql="SELECT d.equnumber,e.DepName as usedepname,b.fieldname as newname,f.DepName as appdepname, c.fieldname as oldname ,d.Price,d.State FROM request AS A inner join equipment AS d on d.id=a.EquipmentID inner join field AS b on b.fieldid=a.userid inner join field AS c on c.fieldid=a.AppID inner join department AS e on e.depid=a.usedep inner join department AS f on f.depid=a.appdep WHERE d.State='$keyword'";
}
$result=mysqli_query($conn,$sql);
if (!$result) {
die('Could NOT Connect'.mysqli_error($conn));
}
else{
while ($row=mysqli_fetch_array($result,MYSQL_ASSOC)) {
//获取需要导出的数据
$data[]=array("equnumber"=>urlencode($row['equnumber']),"usedepname"=>$row['usedepname'],"usesitename"=>$row['newname'],"appdepname"=>urldecode($row['appdepname']),"appsitename"=>urldecode($row['oldname']),"price"=>urldecode($row['Price']),"state"=>urldecode($row['State']));
}
//设置EXCEL文件第一行的列头
$headlist=array('设备编号', '现使用总部门', '现使用子部门', '原申请部门','原申请子部门','价格','设备状态');
//设置EXCEL文件的文件名字
$fieldname='IT部资产清单'.date("Y.m.d");
csv_export($data,$headlist,$fieldname);
}
}
/**
* 导出excel(csv)
* @data 导出数据
* @headlist 第一行,列名
* @fileName 输出Excel文件名
*/
function csv_export($data = array(), $headlist = array(), $fileName) {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
header('Cache-Control: max-age=0');
//打开PHP文件句柄,php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
//输出Excel列名信息
foreach ($headlist as $key => $value) {
//CSV的Excel支持GBK编码,一定要转换,否则乱码
$headlist[$key] = iconv('utf-8', 'gbk', $value);
}
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $headlist);
//计数器
$num = 0;
//每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
$limit = 100000;
//逐行取出数据,不浪费内存
$count = count($data);
for ($i = 0; $i
$num++;
//刷新一下输出buffer,防止由于数据过多造成问题
if ($limit == $num) {
ob_flush();
flush();
$num = 0;
}
$row = $data[$i];
foreach ($row as $key => $value) {
$row[$key] = iconv('utf-8', 'gbk', $value);
}
fputcsv($fp, $row);
exit();
}
}
因为自己也是小白,所以写的代码看着很幼稚,但是确实可以帮助导出。