公司有个需求是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");
}