使用PHPExcel类库将数据导出为excel文档
下载PHPExcel类库,http://phpexcel.codeplex.com/,解压后在Class中找到PHPExcel文件夹和PHPExcel.php文件,拷贝到自己的项目代码中,以下是导出为excel文件的方法
<?php function export_data() { require_once(‘PHPExcel.php‘); // 文档名称 $filename = ‘filename‘; // 创建 PHPExcel 对象 $objPHPExcel = new PHPExcel(); // 设置生成的文档属性 $objPHPExcel->getProperties()->setCreator("http://www.jianbin.info") ->setLastModifiedBy("http://www.jianbin.info") ->setTitle("http://www.jianbin.info") ->setSubject("http://www.jianbin.info") ->setDescription("http://www.jianbin.info") ->setKeywords("http://www.jianbin.info") ->setCategory("http://www.jianbin.info"); $col_number = 65; $col_letter = chr($col_number); $row_number = 1; /** $table 数据格式 array( ‘head‘ => array(字段1,字段2,...,字段n), ‘body‘ => array( ‘行1‘ => (数据1,数据2,...,数据n), ‘行2‘ => (数据1,数据2,...,数据n), ... ‘行n‘ => (数据1,数据2,...,数据n), ) ) */ foreach ($table[‘head‘] as $key=>$val){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue("{$col_letter}{$row_number}", $val); $objPHPExcel->getActiveSheet()->getColumnDimension("{$col_letter}")->setAutoSize(true); // 自动列表设置对中文不支持 $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFill()->getStartColor()->setARGB(‘FF58ACFA‘); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->getColor()->setARGB(‘FFFF0000‘); $col_number++; $col_letter = chr($col_number); } $row_number++; // 构造body foreach ($table[‘body‘] as $row){ $col_number = 65; $col_letter = chr($col_number); foreach ($row as $val) { $objPHPExcel->setActiveSheetIndex(0)->setCellValue("{$col_letter}{$row_number}", $val); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle("{$col_letter}{$row_number}")->getBorders()->getTop()->getColor()->setARGB(‘FFFF0000‘); $col_number++; $col_letter = chr($col_number); } $row_number++; } // 重命名工作表 $objPHPExcel->getActiveSheet()->setTitle($filename); // 默认打开工作表1 $objPHPExcel->setActiveSheetIndex(0); // 设置header输出到客户端 header(‘Content-Type: application/vnd.ms-excel‘); header(‘Content-Disposition: attachment;filename="‘.$filename.‘.xls"‘); header(‘Cache-Control: max-age=0‘); // If you‘re serving to IE 9, then the following may be needed header(‘Cache-Control: max-age=1‘); // If you‘re serving to IE over SSL, then the following may be needed header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT‘); // Date in the past header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s‘).‘ GMT‘); // always modified header (‘Cache-Control: cache, must-revalidate‘); // HTTP/1.1 header (‘Pragma: public‘); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5‘); $objWriter->save(‘php://output‘); exit; }
导出后的文件打开效果如下:
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。