public void export2(HttpServletRequest req, HttpServletResponse resp, String type) throws IOException {
//列名
List<String> headers = Arrays.asList("产品名称", "订单ID", "城市", "柜机编号", "小区名称");
List<Map<String, String>> exportDatas = new ArrayList<Map<String, String>>();
List<OrderProductAttr> orderProductAttrList = orderProductAttrService.findDataPage(orderProductAttr, pager);
//将查询结果,存放进列值
for (OrderProductAttr productAttr : orderProductAttrList) {
Map<String,String> map=new HashMap<>();
map.put("产品名称", productAttr.getProduct_name());
map.put("订单ID", productAttr.getOrder_id());
map.put("城市", productAttr.getDispatch_name());
map.put("柜机编号", productAttr.getCabinet_id());
map.put("小区名称", productAttr.getVillage());
exportDatas.add(map);
}
//导出
exportExcel(req,resp,headers,exportDatas);
}
public void exportExcel(HttpServletRequest req, HttpServletResponse resp, List<String> headers, List<Map<String, String>> exportDatas) throws IOException {
// 创建一个工作薄
SXSSFWorkbook wb = new SXSSFWorkbook();
//创建sheet
Sheet sh = wb.createSheet("订单信息");
//设置表头字体
Font headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setColor(HSSFColor.WHITE.index);
headFont.setFontHeightInPoints((short) 10);// 字体大小
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
// 设置表头样式
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headStyle.setLocked(true);
headStyle.setWrapText(true);// 自动换行
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setFillForegroundColor(HSSFColor.GREEN.index);
// 设置普通单元格字体
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 9);
// 设置普通单元格样式
CellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
style.setWrapText(true);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft((short) 1);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderRight((short) 1);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
//设置单位格样式为文本
DataFormat dataFormat = wb.createDataFormat();
style.setDataFormat(dataFormat.getFormat("@"));
/**
* 设置列名
*/
Row sheetRow = sh.createRow(0);
Cell cell = null;
for (int i = 0; i < headers.size(); i++) {
cell = sheetRow.createCell(i);
cell.setCellValue(headers.get(i));
cell.setCellStyle(headStyle);
}
/**
* 设置列值
*/
int rows = 1;
for (Map<String, String> data : exportDatas) {
Row row = sh.createRow(rows++);
int initCellNo = 0;
int titleSize = headers.size();
for (int i = 0; i < titleSize; i++) {
String key = headers.get(i);
Object object = data.get(key);
if (object == null) {
row.createCell(initCellNo).setCellValue("");
} else {
row.createCell(initCellNo).setCellValue(String.valueOf(object));
}
initCellNo++;
}
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = "订单信息" + sdf.format(new Date()) + ".xlsx";
resp.setContentType("application/octet-stream;charset=utf-8");
resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
ServletOutputStream out = resp.getOutputStream();
wb.write(out);
out.close();
}