效果图:
不多说,直接上代码:
if ($_REQUEST['act'] == 'export') { ini_set('memory_limit','500M'); set_time_limit(0); $sql = "SELECT " . "goods_id, " // 编号 . "goods_name, " // 商品名称 . "goods_sn, " // 货号 . "shop_price, " // 价格 . "is_on_sale, " // 上架 . "is_best, " // 精品 . "is_new, " // 新品 . "is_hot, " // 热销 . "sort_order, " // 推荐排序 . "goods_img, " // 图片 . "goods_number " // 库存 . "FROM " . $GLOBALS['ecs']->table('goods') . 'AS g ' ; $res = $GLOBALS['db']->getAll($sql); // 引入phpexcel核心类文件 require_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel.php'; require_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel/Writer/Excel2007.php'; // 实例化excel类 $objPHPExcel = new PHPExcel(); $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); // 操作第一个工作表 $objPHPExcel->setActiveSheetIndex(0); // 设置sheet名 $objPHPExcel->getActiveSheet()->setTitle('商品列表'); // 设置表格宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20); //设置行高 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);//第一行 行高20 $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(80);//默认行高80 // 列名表头文字加粗 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true); // 列表头文字居中 $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->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', '上架'); $objPHPExcel->getActiveSheet()->setCellValue('F1', '精品'); $objPHPExcel->getActiveSheet()->setCellValue('G1', '新品'); $objPHPExcel->getActiveSheet()->setCellValue('H1', '热销'); $objPHPExcel->getActiveSheet()->setCellValue('I1', '推荐排序'); $objPHPExcel->getActiveSheet()->setCellValue('J1', '库存'); $objPHPExcel->getActiveSheet()->setCellValue('K1', '图片'); // 数据起始行 $row_num = 2; // 向每行单元格插入数据 foreach($res as $value) { // 设置所有垂直居中 $objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'J' . $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()->getStyle('E' . $row_num . ':' . 'H' . $row_num)->getAlignment() ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置单元格数值 $objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['goods_id']); $objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $value['goods_name']); $objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['goods_sn']); $objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, $value['shop_price']); $objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['is_on_sale'] ? '√' : '×'); $objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['is_best'] ? '√' : '×'); $objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['is_new'] ? '√' : '×'); $objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['is_hot'] ? '√' : '×'); $objPHPExcel->getActiveSheet()->setCellValue('I' . $row_num, $value['sort_order']); $objPHPExcel->getActiveSheet()->setCellValue('J' . $row_num, $value['goods_number']); // 图片生成 $objDrawing[$row_num] = new PHPExcel_Worksheet_Drawing(); $objDrawing[$row_num]->setPath('../'.$value['goods_img']); // 设置宽度高度 $objDrawing[$row_num]->setHeight(80);//照片高度 $objDrawing[$row_num]->setWidth(80); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing[$row_num]->setCoordinates('K'.$row_num); // 图片偏移距离 $objDrawing[$row_num]->setOffsetX(12); $objDrawing[$row_num]->setOffsetY(12); $objDrawing[$row_num]->setWorksheet($objPHPExcel->getActiveSheet()); $row_num++; } $outputFileName = 'goods_' . 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); }