用excelpoi写一个下载模板。
1.用法
Row row = sheet1.getRow(43);//获取行数
Cell cell = row.getCell(2);//获取列数
cell.setCellValue(String.valueOf(CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_TYPE)));
cell.setCellValue("1");//在里边赋值
Row row =sheet1.createRow(i);//创建类
row.createCell(1);//创建行
XSSFSheet sheet1 = workbook.getSheet("sheet2");//获取想要在第几个sheet页中添加数据。
2.下载文件
@PassToken
public ResMsg downBatchImportDutyTemplate1(HttpServletResponse response) {
//log.info("Controller downloadFinanceExcel start.");
try {
//在template中创建一个叫device.xlsx的文件。
ClassPathResource classPathResource = new ClassPathResource("tempLate/device.xlsx");
InputStream fis = classPathResource.getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(fis);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
String fileName = java.net.URLEncoder.encode("device.xlsx", "UTF-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
ServletOutputStream out = null;
out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
return ResMsg.newInstance(CodeEnum.OK,"设备导出成功");
} catch (Exception e) {
return new ResMsg("-1","设备下载模板失败");
}
}
3.在sheet页中操作sheet页,去添加
@PassToken
@GetMapping(value = "/downDevice1")
public ResMsg downBatchImportDutyTemplate1(HttpServletResponse response) {
//log.info("Controller downloadFinanceExcel start.");
try {
ClassPathResource classPathResource = new ClassPathResource("tempLate/device.xlsx");
InputStream fis = classPathResource.getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet1 = workbook.getSheet("sheet2");
//1.获取设备分类
//从数据库/redis中获取缓存
List<AuthCodeList> codeList = CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_TYPE);
for (int i = 43; i <63; i++) {
//创建行数(不能覆盖已经写的数据,否则将
Row row =sheet1.createRow(i);
}
for (int rownum = 43, a = 0,b =codeList.size(); rownum <= 63 ;
rownum++,a++,b--) {
//Row row = sheet1.createRow(rownum);
XSSFRow row1 = sheet1.getRow(rownum);
row1.createCell(1);
row1.createCell(2);
Cell cell = row1.getCell(1);
Cell cell1 = row1.getCell(2);
if(b == 0){
break;
}else {
cell.setCellValue(String.valueOf(codeList.get(a).getCodeId()));
cell1.setCellValue(String.valueOf(codeList.get(a).getCodeName()));
}
}
//2.获取设备类型
List<AuthCodeList> codeList1 = CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_KND);
for (int rownum = 43, a = 0,b =codeList1.size(); rownum <= 63 ;
rownum++,a++,b--) {
XSSFRow row1 = sheet1.getRow(rownum);
row1.createCell(4);
row1.createCell(5);
Cell cell = row1.getCell(4);
Cell cell1 = row1.getCell(5);
if(b == 0){
break;
}else {
cell.setCellValue(String.valueOf(codeList1.get(a).getCodeId()));
cell1.setCellValue(String.valueOf(codeList1.get(a).getCodeName()));
}
}
//3.获取资源网络域
List<AuthCodeList> codeList2 = CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_DOMAIN);
for (int rownum = 43, a = 0,b =codeList2.size(); rownum <= 63 ;
rownum++,a++,b--) {
XSSFRow row = sheet1.getRow(rownum);
row.createCell(7);
row.createCell(8);
Cell cell = row.getCell(7);
Cell cell1 = row.getCell(8);
if(b == 0){
break;
}else {
cell.setCellValue(String.valueOf(codeList2.get(a).getCodeId()));
cell1.setCellValue(String.valueOf(codeList2.get(a).getCodeName()));
}
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
String fileName = java.net.URLEncoder.encode("device.xlsx", "UTF-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
ServletOutputStream out = null;
out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
return ResMsg.newInstance(CodeEnum.OK,"设备导出成功");
} catch (Exception e) {
return new ResMsg("-1","设备下载模板失败");
}
}