表格样式可以通过代码定义
注意日期格式如果是以String类型的方式存到数据库,以对象的方式导出时要转换一次,直接导出格式不对。以HashMap的方式查询的没有该问题
因为导出excel表格用的是get方式传参,所以如果需要对导出的数据用中文模糊查询,此时用get传参会出现中文乱码
解决办法:
前端对需要传的中文参数进行一次编码 URLEncoder.encode(传参,“utf-8”);
后台需要再次解码:URLDecoder.decode(接收的参数,“utf-8”);
@RequestMapping(value = "outPutExcel", method = RequestMethod.GET)
public void outPutExcel( HttpServletResponse response,String fuzzyQuery ,String beginTime, String endTime) {
//模糊查询防止中文乱码
if (null != fuzzyQuery && !fuzzyQuery.equals("")){
try {
fuzzyQuery = URLDecoder.decode(fuzzyQuery,"UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
response.reset();
//设置浏览器下载的格式,并以当前时间的毫秒数命名
response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
response.setContentType("application/msexcel");
List<HashMap<String, Object>> mapList = queryStatement(beginTime,endTime,fuzzyQuery);
if (mapList.size()<1){
throw new NullPointerException("导出数据源为空");
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet0");
HSSFRow rows;
HSSFCell cells;
//设置表格第一行的列名
// 获得表格第一行
int i =0;
rows = sheet.createRow(i);
String [] strings = {"客户姓名","单号","下单时间","采购单号","店面","采购金额","已付款"};
for (int j = 0;j<strings.length;j++){
// 根据需要给第一行每一列设置标题
cells = rows.createCell(j);
cells.setCellValue(strings[j]);
}
for (HashMap<String,Object> hashMap : mapList) {
i++;
rows = sheet.createRow(i);
//客户姓名
cells = rows.createCell(0);
cells.setCellValue(hashMap.get("consigneeName").toString());
//单号
cells = rows.createCell(1);
cells.setCellValue(hashMap.get("onlineNumber").toString());
//下单时间
cells = rows.createCell(2);
cells.setCellValue(hashMap.get("buyingTime").toString());
//采购单号
cells = rows.createCell(3);
cells.setCellValue(hashMap.get("buyNumber").toString());
//店面
cells = rows.createCell(4);
cells.setCellValue(hashMap.get("companyName").toString());
//采购金额
cells = rows.createCell(5);
cells.setCellValue(hashMap.get("purchaseAmount").toString());
//已付款
cells = rows.createCell(6);
cells.setCellValue(hashMap.get("paymentAmount").toString());
}
try {
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}