看了很多文章乱糟糟的，自己写了个简单暴力的一眼就懂，没有那么多花里胡哨，表格样式可以通过代码定义，我嫌麻烦
注意日期格式如果是以String类型的方式存到数据库的导出时要转换一次，直接导出格式不对
因为导出excel表格用的是get方式传参，所以如果需要对导出的数据用中文模糊查询，此时 用get传参会出现中文乱码
解决办法：
前端对需要传的中文参数进行一次编码 URLEncoder.encode(传参,“utf-8”);
后台需要再次解码：URLDecoder.decode(接收的参数,“utf-8”);
@RequestMapping(value = "outPutExcel", method = RequestMethod.GET)
@ResponseBody
publicvoid outPutExcel( HttpServletResponse response,String officeid,
String sonid,String nameorphone,String beginTime, String endTime,String option) {
String nString = "";
try{
if(nameorphone != null && nameorphone != "") {
//对前端传的参数解码
nString = URLDecoder.decode(nameorphone,"UTF-8");
}
} catch(UnsupportedEncodingException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
response.reset();
//设置浏览器下载的格式，并以当前时间的毫秒数命名
response.setHeader("Content-Disposition", "attachment;Filename="+ System.currentTimeMillis() + ".xls");
response.setContentType("application/msexcel");
List<PurchaseSum> list = purchaseService.selectPCSum(officeid, sonid, nString, beginTime, endTime, option);
if(list == null && list.isEmpty()) {
thrownewNullPointerException("导出数据源为空");
}
HSSFWorkbook wb = newHSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet0");
HSSFRow rows;
HSSFCell cells;
//设置表格第一行的列名
// 获得表格第一行
rows = sheet.createRow(0);
// 根据需要给第一行每一列设置标题
cells = rows.createCell(0);
cells.setCellValue("客户姓名");
cells = rows.createCell(1);
cells.setCellValue("客户电话");
cells = rows.createCell(2);
cells.setCellValue("下单日期");
cells = rows.createCell(3);
cells.setCellValue("订单号");
cells = rows.createCell(4);
cells.setCellValue("所属分公司");
cells = rows.createCell(5);
cells.setCellValue("签单人");
cells = rows.createCell(6);
cells.setCellValue("品名");
cells = rows.createCell(7);
cells.setCellValue("型号");
cells = rows.createCell(8);
cells.setCellValue("颜色");
cells = rows.createCell(9);
cells.setCellValue("尺寸");
cells = rows.createCell(10);
cells.setCellValue("材质");
cells = rows.createCell(11);
cells.setCellValue("已采购数量(件)");
cells = rows.createCell(12);
cells.setCellValue("采购单价");
cells = rows.createCell(13);
cells.setCellValue("采购总价");
cells = rows.createCell(14);
cells.setCellValue("已出库(件)");
//循环数据库查出来的数据集，对应每一列赋值
//此处list.size()本不应该-1，因为同事在list集合里追加了另一条数据，导致报错故将其去除
for(int i = 0; i < list.size()-1; i++) {
rows = sheet.createRow(i + 1);
cells = rows.createCell(0);
cells.setCellValue(list.get(i).getCustomerName());
cells = rows.createCell(1);
cells.setCellValue(list.get(i).getPhone());
//对日期格式进行转换
cells = rows.createCell(2);
String dateString = list.get(i).getPlaceOrderTime().toString();
Datedate= null;
try{
date= newSimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK).parse(dateString);
} catch(ParseException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
SimpleDateFormat sdf = newSimpleDateFormat("yyyy-MM-dd");
cells.setCellValue(sdf.format(date));
cells = rows.createCell(3);
cells.setCellValue(list.get(i).getOrderNumber());
cells = rows.createCell(4);
cells.setCellValue(list.get(i).getOfficeName());
cells = rows.createCell(5);
cells.setCellValue(list.get(i).getUsername());
cells = rows.createCell(6);
cells.setCellValue(list.get(i).getProductName());
cells = rows.createCell(7);
cells.setCellValue(list.get(i).getType());
cells = rows.createCell(8);
cells.setCellValue(list.get(i).getColor());
cells = rows.createCell(9);
cells.setCellValue(list.get(i).getSize());
cells = rows.createCell(10);
cells.setCellValue(list.get(i).getTexture());
cells = rows.createCell(11);
cells.setCellValue(list.get(i).getPurchasedNumber());
cells = rows.createCell(12);
cells.setCellValue(list.get(i).getPurchaseprice());
cells = rows.createCell(13);
cells.setCellValue(list.get(i).getPurchasePriceSun());
cells = rows.createCell(14);
cells.setCellValue(list.get(i).getOutlibraryNumber());
}
try{
OutputStream oStream = response.getOutputStream();
wb.write(oStream);
oStream.flush();
} catch(FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch(IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
本文来自PHP中文网的java学习教程栏目：https://www.php.cn/java/
