最近同事接收到一个需求,要导出excel,之前我的处理一般都是导出到csv文件,但是发现需求的demo表格中有合并单元格和加粗字体的样式,百度了一下csv貌似不要处理,找一个插件PHPExcel,以上要求都能满足,于是看一个写了一个导出并下载excel的demo
PHPExcel github地址:https://github.com/PHPOffice/PHPExcel
下载PHPExcel扩展,Classes目录里的就是核心文件,复制到你应用下
以下的代码样式一般能满足现在的需求
- include './PHPExcel-1.8/Classes/PHPExcel.php';
-
- $objPHPExcel=new PHPExcel();
-
- $objSheet=$objPHPExcel->getActiveSheet();//获取到sheet
-
- $objSheet->setTitle("demo");//设置sheet名字
-
- $objSheet->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
-
- $objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
-
- $objSheet->getRowDimension(1)->setRowHeight(50);//设置第一行行高
-
- $objSheet->getStyle("A1:Z1")->getFont()->setSize(20)->setBold(true);//设置第一行字体大小和加粗
-
- $objSheet->setCellValue("A1","姓名")->setCellValue("B1","成绩")->setCellValue("C1","性别");//写值
-
- $objSheet->mergeCells("C2:C3");// 合并单元格
- $objSheet->setCellValue("A2","张三")->setCellValue("B2","60")->setCellValue("C2","男");//写值
- $objSheet->setCellValue("A3","李四")->setCellValue("B3","70");
-
- $objSheet->getStyle("A4")->getAlignment()->setWrapText(true);//设置文字自动换行
- $objSheet->setCellValue("A4","王五\n换行")->setCellValue("B4","80");
-
- $objSheet->setCellValue("A5","马六")->setCellValue("B6",' ' ."70.2323123123123123");//解决长数字串显示为科学计数
-
- $objSheet->getStyle("A1:C1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('6fc144');//填充背景颜色
-
- $styleArray = array(
- 'borders' => array(
- 'outline' => array(
- 'style' => PHPExcel_Style_Border::BORDER_THICK,
- 'color' => array('rgb' => "cccccc"),//这里颜色没有#
- ),
- ),
- );
- $objSheet->getStyle("A1:C1")->applyFromArray($styleArray);//设置边框
-
-
-
- $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//生成excel文件 如:Excel2007
-
- browser_export('Excel5','test.xls');//输出到浏览器 设置游览器保存文件名
- $objWriter->save("php://output");
-
- function browser_export($type,$filename){
- if($type=="Excel5"){
- header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
- }else{
- header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
- }
- header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
- header('Cache-Control: max-age=0');//禁止缓存
- }
友情提示:垃圾评论一律封号...