1.PHPExcel
thinkphp PHPExcel放到这个目录下
/** * 导出excel表格 */ function exportExcel($data,$filename){ $xlsTitle = iconv('utf-8', 'gb2312', $filename); //文件名称 //$fileName = $filename . "-" . date('YmdHis'); //or $xlsTitle 文件名称可根据 vendor("PHPExcel.PHPExcel"); $objPHPExcel = new \PHPExcel(); $objPHPExcel->setActiveSheetIndex(0); $dateExcel = $objPHPExcel->getActiveSheet(); $dateExcel->getDefaultStyle()->getFont()->setName('微软雅黑'); $dateExcel->getDefaultStyle()->getFont()->setSize(12); //字体大小 $dateExcel->getStyle('1')->getFont()->setBold(true); //第一行是否加粗 $dateExcel->getStyle('1')->getFont()->setSize(13); //第一行字体大小 $dateExcel->getStyle('1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中 // 设置行高度 $dateExcel->getDefaultRowDimension()->setRowHeight(20); //设置默认行高 $dateExcel->getRowDimension('1')->setRowHeight(30); //第一行行高 $rows = count($data); for ($i = 0; $i < count($data); $i++) { $row = $i + 1; $key = ord("A");//A--65 $key2 = ord("@");//@--64 foreach ($data[$i] as $d) { if ($key > ord("Z")) { $key2 += 1; $key = ord("A"); $col = chr($key2) . chr($key);//超过26个字母时才会启用 } else { if ($key2 >= ord("A")) { $col = chr($key2) . chr($key);//超过26个字母时才会启用 } else { $col = chr($key); } } $dateExcel->setCellValue($col . $row, $d); $key++; } } //边框样式 $styleArray = array( 'borders' => array( 'allborders' => array( //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的 'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框 'color' => array('argb' => '#000'), ), ), ); $dateExcel->getStyle('A1:' . $col . $rows)->applyFromArray($styleArray); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20); ob_end_clean(); //!!!!!!!清除缓冲区,避免乱码 header('pragma:public'); header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"'); header("Content-Disposition:attachment;filename=$filename.xls"); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
参数$data 传入的格式为:
$filename为文件名
2.\t
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=test_data.xls");
//输出内容如下:
echo "姓名"."\t";
echo "生日"."\t[这有一个空格]";
echo "学历"."\t";
echo "\n";
echo "张三"."\t";
echo "1992-10-12"."\t";
echo "本科"."\t";
?>
如果遇到xls打开中文乱码的问题,可以使用mb_convert_encoding("姓名", "gb2312", "UTF-8")这个方法,将UTF-8转为gb2312编码格式。注:这个根据自己的实际情况转码,我自己的是UTF-8输出到xls乱码
在使用\t输出excel的时候,因有时间格式显示不完全,固在时间一列上加了一个空格,输出时间显示,但列宽不够,不会设置,于是选择了第三种方式,可以添加style设置样式
3.table
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=test_data.xls");
$html= ""; foreach ($data as $value){ $html=$html."<tr><td align='center'>".$value[0]."</td>" ."<td align='center'>".$value[1]."</td>" ."<td align='center' >".$value[2]." "."</td>" ."<td align='center' style='vnd.ms-excel.numberformat:yyyy-mm-dd hh:mm'>".$value[3]."</td>" ."<td align='center' style='vnd.ms-excel.numberformat:yyyy-mm-dd hh:mm'>".$value[4]."</td>" ."<td align='center'>".$value[5]."</td>"; } $html="<table border='1'>".$html."</table>"; echo $html;
如果出现乱码,则头信息更换为
header("Content-type: text/html; charset=utf-8"); header("Content-type:application/octet-stream"); header("Accept-Ranges:bytes"); header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=$filename.xls"); header("Pragma: no-cache"); header("Expires: 0");
附:GridView导出Excel常见的5种文本格式
1) 文本:vnd.ms-excel.numberformat:@
2) 日期:vnd.ms-excel.numberformat:yyyy/mm/dd
3) 数字:vnd.ms-excel.numberformat:#,##0.00
4) 货币:vnd.ms-excel.numberformat:¥#,##0.00
5) 百分比:vnd.ms-excel.numberformat: #0.00%