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%