近期做一个项目涉及到商品信息的批量导出与导入,遂记录了下来,框架是tp框架3.2.3(tp5.0性质是一样的,无非是加载方法与所放目录不一样罢了),运用的是phpexcel,闲话不多说,上代码
1、首先是下载phpexcel(下载地址:http://phpexcel.codeplex.com/releases/view/119187),下载完后文件放入ThinkPHP\Library\Vendor下,解压完重命名为PHPExcel
2、控制器引入及操作
public function index(){ Vendor('PHPExcel.Classes.PHPExcel.IOFactory'); //引入phpexcel $title = '订单列表'.date('YmdHis',time()); //定义文件名 $objPHPExcel = new \PHPExcel(); for($j=A;$j<=K;$j++){ // 设置宽度 $objPHPExcel->getActiveSheet()->getColumnDimension($j)->setWidth(20); // 设置水平居中 $objPHPExcel->getActiveSheet()->getStyle($j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置背景颜色 $objPHPExcel->getActiveSheet()->getStyle($j)->getFill($j)->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle($j)->getFill($j)->getStartColor()->setARGB('FFEEEEEE'); } // 设置行高度 for($j=1;$j<=100;$j++){ $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight(22); $objPHPExcel->getActiveSheet()->getStyle($j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平居中 $objPHPExcel->getActiveSheet()->getStyle($j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } // 表头 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '订单号') ->setCellValue('B1', '商品编号') ->setCellValue('C1', '商品名称') ->setCellValue('D1', '商品数量') ->setCellValue('E1', '商品单价') ->setCellValue('F1', '订单总额') ->setCellValue('G1', '下单时间'); $data = array(); //订单数据 foreach($data as $k => $v){ $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($k + 2), $v['order_sn']); $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($k + 2), $v['goods_num']); $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($k + 2), $v['goods_name']); $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($k + 2), $v['order_num']); $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($k + 2), $v['goods_price']); $objPHPExcel->getActiveSheet(0)->setCellValue('F' . ($k + 2), $v['goods_totalprice']); $objPHPExcel->getActiveSheet(0)->setCellValue('G' . ($k + 2), $v['order_time']); } $objPHPExcel->getActiveSheet()->setTitle($title); $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="'.$title.'.xls"'); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } }
phpexcel是非常强大的,不但可以设置宽高、背景颜色,而且还可以设置字体,合并单元格、拆分单元格等等,在这里就不一一列举了(一般常用的设置这些就够了),有兴趣的可以自己研究下,phpexcel的导入,将在下篇博文中详解