php 批量导出excel文件,PHPEXCEL批量导出

// 引入phpexcel核心类文件

require_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel.php';

// 实例化excel类

$objPHPExcel = new PHPExcel();

// 操作第一个工作表

$objPHPExcel->setActiveSheetIndex(0);

// 设置sheet名

$objPHPExcel->getActiveSheet()->setTitle('商品销售明细');

// 设置表格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(60);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

// 列名表头文字加粗

$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);

// 列表头文字居中

$objPHPExcel->getActiveSheet()->getStyle('A1:E1')->getAlignment()

->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// 列名赋值

$objPHPExcel->getActiveSheet()->setCellValue('A1', '商品名称');

$objPHPExcel->getActiveSheet()->setCellValue('B1', '订单号');

$objPHPExcel->getActiveSheet()->setCellValue('C1', '销售数量');

$objPHPExcel->getActiveSheet()->setCellValue('D1', '销售价格');

$objPHPExcel->getActiveSheet()->setCellValue('E1', '下单日期');

// 数据起始行

$row_num = 2;

// 向每行单元格插入数据

foreach($result as $value)

{

// 设置所有垂直居中

$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'E' . $row_num)->getAlignment()

->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// 设置价格为数字格式

$objPHPExcel->getActiveSheet()->getStyle('D' . $row_num)->getNumberFormat()

->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

// 设置单元格数值

$objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['goods_name']);

$objPHPExcel->getActiveSheet()->setCellValueExplicit('B' . $row_num, $value['order_sn'], PHPExcel_Cell_DataType::TYPE_STRING);

$objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['sells_count']);

$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['goods_price']);

$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['add_time']);

$row_num++;

}

$outputFileName = '商品销售明细_' . time() . '.xls';

$xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

header("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:inline;filename="' . $outputFileName . '"');

header("Content-Transfer-Encoding: binary");

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

header("Pragma: no-cache");

$xlsWriter->save("php://output");

echo file_get_contents($outputFileName);

/***param $result 查询的数据

***/