1.使用ExcelExportUtil基于模板导出excel数据,listToExcel
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2.0</version>
</dependency>
第一步,导入依赖没啥好说的;
第二步,查询数据库或es数据组装成list对象;
第三步,填充Map<String, Object> excelMap = new HashMap<>();
数据key和excel模板对应,如果一次导出多个,可以用index区分;
public void ExcelExport(HttpServletResponse response,String params) {
//excel参数
Map<String, Object> excelMap = new HashMap<>();
//resources下的doc包下的这个尾缀为xlsx的模板
TemplateExportParams params = new TemplateExportParams(
"doc/模板名称.xlsx", true);
Workbook workBook = null;
try {
excelMap.put("startDate", "2022-01-01");
excelMap.put("endDate", "2022-02-01");
List<String> list = new ArrayList<String>(Arrays.asList("1","2"));//嗯 没错 这里就是要导出的数据
excelMap.put("list", list);
workBook = ExcelExportUtil.exportExcel(params, excelMap);
response.flushBuffer();
workBook.write(response.getOutputStream());
response.setHeader("content-Type", "application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("模板名称.xlsx", "UTF-8"));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("导出异常");
}finally {
if (workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
2.基于org.apache.poi的XSSFWorkbook导出
第一步,导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
第二步,上图哈哈文章来源:https://www.toymoban.com/news/detail-524858.html
public void ExcelExport(HttpServletResponse response,String params) {
Map<String,String> map = new HashMap<>();
map.put("name","张三");
map.put("age","1");
map.put("adre","地址");
List<Map<String,String>> list = new ArrayList<>();
list.add(map);
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet("居民职工结算申报明细单");
//第一行合并
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0,13);
//第二行合并
CellRangeAddress region2 = new CellRangeAddress(1, 1, 0,6);
CellRangeAddress region21 = new CellRangeAddress(1, 1, 7,13);
//第三行合并
CellRangeAddress region3 = new CellRangeAddress(2, 2, 0,6);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region21);
sheet.addMergedRegion(region3);
//创建第一行
XSSFRow row0 = sheet.createRow(0);
XSSFCell cell_00=row0.createCell(0);
XSSFFont font = workbook.createFont();
//字体大小
font.setFontHeightInPoints((short)20);
// 字体加粗
font.setBold(true);
// 设置样式;
XSSFCellStyle style = workbook.createCellStyle();
// 设置水平对齐的样式为居中对齐
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 在样式用应用设置的字体
style.setFont(font);
cell_00.setCellStyle(style);
cell_00.setCellValue("第一行一般是标题,这里是标题的值");
//创建第二行
XSSFRow row1 = sheet.createRow(1);
//第一列
XSSFCellStyle style1 = workbook.createCellStyle();
style1.setAlignment(HorizontalAlignment.LEFT);
style1.setVerticalAlignment(VerticalAlignment.CENTER);
initExcelCell(row1,style1,"第二行第一列的值",0);
//第二列
XSSFCellStyle style1_1 = workbook.createCellStyle();
style1_1.setAlignment(HorizontalAlignment.RIGHT);
style1_1.setVerticalAlignment(VerticalAlignment.CENTER);
String startDate = "2022-01-01";
String endDate = "2022-02-01";
String reportDate = "日期:" + startDate.substring(0, 4) + "年" + startDate.substring(5, 7) + "月" + startDate.substring(8) + "日至" + endDate.substring(0, 4) + "年" + endDate.substring(5, 7) + "月" + endDate.substring(8) + "日";
initExcelCell(row1, style1_1, reportDate, 7);
//创建第三行
XSSFRow row2 = sheet.createRow(2);
//第一列
initExcelCell(row2,style1,"企业名称:",0);
//第二列
initExcelCell(row2,style1,"单位:元",13);
//创建第四行 表头
XSSFRow row3 = sheet.createRow(3);
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
List<String> cellList = Arrays.asList("0", "1", "2", "3", "4", "5", "6", "7",
"8", "9", "10", "11", "12", "13");
//初始化列
for (int i = 0; i < cellList.size(); i++) {
initExcelCell(row3, style2, cellList.get(i), i);
}
//用于底部数据填充找到行数
int j=0;
for(int i=0;i<list.size();i++){
Map r=list.get(i);
XSSFRow rown = sheet.createRow(4+i);
//序号
initExcelCell(rown, style2, i + 1 + "", 0);
//姓名
initExcelCell(rown, style2, r.get("name").toString(), 1);
//年龄
initExcelCell(rown, style2, r.get("age").toString(), 2);
//地址
initExcelCell(rown, style2, r.get("adree").toString(), 3);
j++;
}
j+=4;
//合计列
CellRangeAddress regionx = new CellRangeAddress(j, j, 0,9);
XSSFRow rowx = sheet.createRow(j);
initExcelCell(rowx,style2,"合 计",0);
//给合并单元格加边框
setRegionBorder(BorderStyle.THIN, regionx,sheet);
sheet.addMergedRegion(regionx);
j++;
//最后一行
CellRangeAddress regionl = new CellRangeAddress(j, j, 0, 3);
CellRangeAddress regionl1 = new CellRangeAddress(j, j, 4, 6);
CellRangeAddress regionl2 = new CellRangeAddress(j, j, 7, 9);
CellRangeAddress regionl3 = new CellRangeAddress(j, j, 10, 13);
sheet.addMergedRegion(regionl);
sheet.addMergedRegion(regionl1);
sheet.addMergedRegion(regionl2);
sheet.addMergedRegion(regionl3);
XSSFCellStyle style3 = workbook.createCellStyle();
style3.setAlignment(HorizontalAlignment.LEFT);
style3.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFRow rowx1 = sheet.createRow(j);
//制表日期
initExcelCell(rowx1, style3, "制表日期: " + DateUtils.format(new Date()), 0);
//制表人
initExcelCell(rowx1,style3,"制表人:",4);
sheet.setDefaultRowHeight((short)450);
sheet.setColumnWidth(2, 12*256);
sheet.setColumnWidth(3, 20*256);
try {
response.flushBuffer();
workbook.write(response.getOutputStream());
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition",
"attachment;filename=" + java.net.URLEncoder.encode("jm.xlsx", "UTF-8"));
} catch (Exception e) {
e.printStackTrace();
response.setContentType(MediaType.APPLICATION_JSON_VALUE);
throw new RuntimeException("导出明细异常");
}
}
private void initExcelCell(XSSFRow row, XSSFCellStyle cellStyle, String cellValue,int index) {
XSSFCell cell = row.createCell(index);
cell.setCellStyle(cellStyle);
cell.setCellValue(cellValue);
}
private void setRegionBorder(BorderStyle border, CellRangeAddress region, Sheet sheet){
RegionUtil.setBorderBottom(border,region, sheet);
RegionUtil.setBorderLeft(border,region, sheet);
RegionUtil.setBorderRight(border,region, sheet);
RegionUtil.setBorderTop(border,region, sheet);
}
就是这么简单 没了文章来源地址https://www.toymoban.com/news/detail-524858.html
到了这里,关于ExcelExportUtil基于模板导出excel&POI导出excel的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!