java poi excel导出 客户端下载

公司有个需求是excel表格导出，我就花了点时间整理了一下。首先引入maven依赖包。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

代码如下：

// 1. 创建工作簿
Workbook wb = new XSSFWorkbook();
// 2. 创建表单sheet
Sheet sheet = wb.createSheet("定制vip卡");
//设置单元格的宽度
sheet.setColumnWidth(0, 50 * 256);
sheet.setColumnWidth(1, 50 * 256);

// 创建样式对象
CellStyle cellStyle = wb.createCellStyle();
// 居中显示
cellStyle.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

// 创建样式对象
CellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.LEFT);
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
sheet.addMergedRegion(new CellRangeAddress(1,1,0,1));
sheet.addMergedRegion(new CellRangeAddress(2,2,0,1));
sheet.addMergedRegion(new CellRangeAddress(3,3,0,1));
sheet.addMergedRegion(new CellRangeAddress(4,4,0,1));

// 创建字体对象
Font font = wb.createFont();
font.setFontName("宋体"); // 字体
font.setFontHeightInPoints((short)13); // 字号
cellStyle.setFont(font);
cellStyle1.setFont(font);

Row row0 = sheet.createRow(0);
Cell cel = row0.createCell(0);
cel.setCellValue("定制vip卡");
cel.setCellStyle(cellStyle);

Row row1 = sheet.createRow(1);
cel = row1.createCell(0);
cel.setCellValue("批次数量:100张");
cel.setCellStyle(cellStyle1);

Row row2 = sheet.createRow(2);
cel = row2.createCell(0);
cel.setCellValue("创建时间:2021-01-19");
cel.setCellStyle(cellStyle1);

Row row3 = sheet.createRow(3);
cel = row3.createCell(0);
cel.setCellValue("代理商: xxx公司    手机号:158XXXXXXXX");
cel.setCellStyle(cellStyle1);
Row row4 = sheet.createRow(4);
cel = row4.createCell(0);
cel.setCellValue("激活码有效期:不限期     VIP卡有效期:不限期");
cel.setCellStyle(cellStyle1);

Row row5 = sheet.createRow(5);
String[] title = {"VIP定制卡编号","激活码"};
for (int i = 0; i < title.length; i++) {
   cel = row5.createCell(i);
   cel.setCellValue(title[i]);
   cel.setCellStyle(cellStyle);
}
//模拟数据库查询的数据
List<Map<String,String>> list = Lists.newArrayList();
Map<String,String>a = new HashMap<>();
a.put("id","NO.0001");
a.put("code","wer23er1232");
list.add(a);
Map<String,String>b = new HashMap<>();
b.put("id","NO.0002");
b.put("code","5er23e312ds");
list.add(b);
if (CollectionUtils.isNotEmpty(list)) {
   for (int i = 0; i < list.size(); i++) {
       Map<String,String> map = list.get(i);
       Row row = sheet.createRow(6+i);
       cel = row.createCell(0);
       cel.setCellValue(map.get("id"));
       cel.setCellStyle(cellStyle);
       cel = row.createCell(1);
       cel.setCellValue(map.get("code"));
       cel.setCellStyle(cellStyle);
   }
}
String fileName = "vip定制卡激活码——"+System.currentTimeMillis();
try{
   //将文件存到指定位置
   FileOutputStream pis =new FileOutputStream("C:\\export\\"+fileName+".xls");
   // 4. 写入文件
   wb.write(pis);
   pis.close();
}catch (Exception e) {
   log.info("下载失败");
}

效果图：

如果接口需要客户端自行下载，需要返回response给前端:

把前面的try catch里面代码替换成
try {
       this.setResponseHeader(response, fileName);
       OutputStream os = response.getOutputStream();
       wb.write(os);
       os.flush();
       os.close();
   } catch (Exception e) {
       log.info("下载失败");
   }

方法
public void setResponseHeader(HttpServletResponse response, String fileName) throws Exception{
	 fileName = new String(fileName.getBytes(),"ISO8859-1");
	 response.setContentType("application/octet-stream;charset=ISO8859-1");
	 response.setHeader("Content-Disposition", "attachment;filename="+ fileName +".xls");   //要保存的文件名
	 response.addHeader("Pargam", "no-cache");
	 response.addHeader("Cache-Control", "no-cache");
}