php excel 另存为,使用PHPExcel类库将数据导出为excel文档

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;

}

导出后的文件打开效果如下：