pom 引入:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
传入表单
/**
* @throws
* @description
*/
@RequestMapping(“/exportPayOrderList”)
public void orderListExport(@RequestParam String exportParam
, HttpServletResponse response
, HttpServletRequest request) throws UnsupportedEncodingException {
System.out.println(“exportParam-------------------->>” + exportParam);
exportParam = URLDecoder.decode(exportParam, “utf-8”);
exportParam = exportParam.replaceAll(“””, “”“);
exportParam = exportParam.replaceAll(”,“, “,”);
exportParam = exportParam.replaceAll(”【“, “[”);
exportParam = exportParam.replaceAll(”】", “]”);
BillVoPm billVoPm = JSONObject.parseObject(exportParam, BillVoPm.class);
billManagePayService.exportOrderList(billVoPm, response, request);
}
/**
* @throws
* @descriptio接口层
*/
void exportOrderList(BillVoPm hotelAdditionOrderVO
, HttpServletResponse response
, HttpServletRequest request) ;
//s实现层
@SneakyThrows
@Override
public void exportOrderList(BillVoPm mapValue, HttpServletResponse response, HttpServletRequest request) {
log.info("下载数据data={}—> ", mapValue.getMonthData());
if (Objects.isNull(mapValue)) {
throw new SecurityException(“请传入数据!”);
}
List orderList = mapValue.getMonthData();
String tempFileName = “账单信息@” + DateUtil.currentTime(DateUtil.YYYY_MM_DD_PATTERN) + “.xls”;
String filenameEncoder = URLDecoder.decode(tempFileName, “UTF-8”);
String agent = request.getHeader(“User-Agent”).toLowerCase();
response.setContentType(“application/x-download;charset=utf-8”);
if ((agent.contains(“safari”) || agent.contains(“iphone”)) && !agent.contains(“chrome”)){
//如果是苹果浏览器
log.info(“苹果浏览器”);
response.setHeader(“Content-Disposition”, “attachment; filename=” + new String(tempFileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
}else {
//其他浏览器
log.info(“其他浏览器”);
filenameEncoder = new String(tempFileName.getBytes(“UTF-8”), “ISO-8859-1”);
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/x-msdownload”);
response.setHeader(“Content-Disposition”, “attachment;filename=” + filenameEncoder);
}
Workbook workbook = buildWordBook(orderList);
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (Exception e) {
log.info(“年账单列表失败----------->>{}”, e.getMessage());
} finally {
if (null != outputStream) {
try {
outputStream.close();
} catch (Exception e) {
log.info(“关闭输出流失败----------->>{}”, e.getMessage());
}
}
if (null != workbook) {
try {
workbook.close();
} catch (Exception e) {
log.info(“关闭workbook流失败----------->>{}”, e.getMessage());
}
}
}
}
private Workbook buildWordBook(List<BillVo> orderList) {
Workbook wb = new SXSSFWorkbook(10000);
String sheetName = "账单列表";
Sheet sh = wb.createSheet(sheetName);
createTitleCell2(wb, sh);
for (int rownum = 0; rownum < orderList.size(); rownum++) {
Row row = sh.createRow(rownum + 3);
// 月份
String additionOrderId = orderList.get(rownum).getTime();
Cell cell = row.createCell(0);
cell.setCellValue(additionOrderId == null ? "" : additionOrderId);
// 订单汇总
String providerName = orderList.get(rownum).getCount();
Cell cell1 = row.createCell(1);
cell1.setCellValue(providerName == null ? "" : providerName);
// 金额汇总
String totalPrice = orderList.get(rownum).getFee();
Cell cell2 = row.createCell(2);
cell2.setCellValue(totalPrice == null ? "" : totalPrice);
}
return wb;
}
private void createTitleCell2(Workbook wb, Sheet sh) {
List<String> headers2 = new ArrayList<String>() {{
add("月份");
add("订单汇总");
add("金额汇总");
}};
((SXSSFWorkbook) wb).setCompressTempFiles(true); //使用gzip压缩,减小空间占用
//设置样式
CellStyle style = wb.createCellStyle();
CellStyle style1 = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 居中
style1.setWrapText(true);
style1.setAlignment(HorizontalAlignment.CENTER);
style1.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体样式
Font font = wb.createFont();
// font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 24);//设置字体大小
// 把字体应用到当前的样式
style.setFont(font);
//设置表格标题
Row rowTitle = sh.createRow(0);
sh.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
Cell titleCell = rowTitle.createCell(0);
titleCell.setCellValue("账单数据");
titleCell.setCellStyle(style);
//设置每一列的宽度,注意 要乘以256,因为1代表excel中一个字符的1/256的宽度
sh.setColumnWidth(0, 25 * 256);
sh.setColumnWidth(1, 25 * 256);
sh.setColumnWidth(2, 15 * 256);
Row rowHeader = sh.createRow(1);
// 设置表格标题
for (int i = 0, j = headers2.size(); i < j; i++) {
Cell cellHeader = rowHeader.createCell(i);
cellHeader.setCellValue(headers2.get(i));
cellHeader.setCellStyle(style1);
}
log.info("账单数据下载成功------");
}