SpringBoot导出xlsx文章目录
- SpringBoot导出xlsx
- 1.需求
- 2.架构选定
- 2.Hutool 工具类
- 3.POI 工具类
- 4.前端响应方法
在Sprongboot项目中,导出数据为xlsx,然后放入HttpServletResponse中,响应给客户端;
2.架构选定-
Hutool
优点:
- hutool对poi *** 作进行大量的封装,可以简单调用工具类进行快速开发;
缺点:
- 不能进行细粒度开发,比如给每个单元格设置不同的的背景色,hutool没有提供相应的工具类;
- API网址:https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter
-
Apache POI
优点:
- 提供丰富的api *** 作,能细化到单元格进行 *** 作;
缺点:
-
增加开发时间成本;
-
API网址:https://poi.apache.org/components/spreadsheet/quick-guide.html
-
API使用博客推荐:https://blog.csdn.net/qq_42651904/article/details/88221392?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0.highlightwordscore&spm=1001.2101.3001.4242.1
-
使用范例博客:https://www.cnblogs.com/jike1219/p/11182303.html
1. 依赖:
cn.hutool hutool-all5.7.17
2. 工具类:
public static void export(List
合并策略类
import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.AllArgsConstructor; import lombok.Data; import lombok.experimental.Accessors; @AllArgsConstructor @Data @Accessors(chain = true) @ApiModel("Excel 导出参数") public class MergeExport { @ApiModelProperty("起始行,0开始") private int firstRow; @ApiModelProperty("结束行,0开始") private int lastRow; @ApiModelProperty("起始列,0开始") private int firstColumn; @ApiModelProperty("结束列,0开始") private int lastColumn; @ApiModelProperty("合并单元格后的内容") private Object content; }
3. 导出Excel结果:
1. 依赖
commons-fileupload commons-fileupload1.3.1 commons-io commons-io2.5 org.apache.poi poi3.17 org.apache.poi poi-ooxml3.17
2. 工具类
public static void createXSS(String sheetName, List> title, List
> data,List merge, HttpServletResponse response ,String fileName,int distance){ log.info("export start ..."); log.info("导出标题:{}",JSONUtil.toJsonPrettyStr(title)); log.info("导出数据:{}",JSONUtil.toJsonPrettyStr(data)); log.info("合并策略:{}",JSONUtil.toJsonPrettyStr(merge)); // 创建新HSSFWorkbook,对应一个Excel文件 // HSSFWorkbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); // 在workbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet sheet = wb.createSheet(sheetName); sheet.setDefaultColumnWidth(15); sheet.setVerticallyCenter(true); List colourStyle = getBackgroundColour(wb); for (int i = 0; i < merge.size(); i++) { MergeExport mergeExport = merge.get(i); CellRangeAddress rangeAddress = new CellRangeAddress(mergeExport.getFirstRow(), mergeExport.getLastRow(), mergeExport.getFirstColumn(), mergeExport.getLastColumn()); sheet.addMergedRegion(rangeAddress); } //声明列对象 XSSFCell cell = null; // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 XSSFRow row = sheet.createRow(0); row.setHeightInPoints(30); List firstTitle = title.get(0); cell = row.createCell(0); cell.setCellStyle(colourStyle.get(0)); int firstCol = 1; for (int i = 0; i < firstTitle.size(); i++) { cell = row.createCell(firstCol); cell.setCellValue(firstTitle.get(i)); cell.setCellStyle(colourStyle.get(i+1)); firstCol+=distance; } cell = row.createCell(data.get(0).size()-1); cell.setCellStyle(colourStyle.get(colourStyle.size()-1)); for (int i = 1; i < title.size(); i++) { XSSFRow tempRow = sheet.createRow(i); tempRow.setHeightInPoints(30); List otherTitle = title.get(i); for (int j = 0; j < otherTitle.size(); j++) { cell = tempRow.createCell(j); cell.setCellValue(otherTitle.get(j)); setUpColor(cell,colourStyle,j,otherTitle.size()); } } for (int i = 0; i < data.size(); i++) { XSSFRow tmpeRow = sheet.createRow(i+title.size()); List values = new ArrayList (); values.addAll(data.get(i).values()); for (int j = 0; j < values.size(); j++) { cell = tmpeRow.createCell(j); cell.setCellValue(values.get(j)); setUpColor(cell,colourStyle,j,values.size()); } } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition","attachment;filename="+fileName); ServletOutputStream resp = null; try { resp = response.getOutputStream(); wb.write(resp); resp.flush(); resp.close(); log.info("export sucess ....."); } catch (Exception e) { log.info("export erro:{}",e.getMessage()); e.printStackTrace(); } }
调用方法
private static void setUpColor(XSSFCell cell,Listcolor,int j,int distance){ List > total = new ArrayList<>(); total.add(Arrays.asList(0)); int p = (distance - 1) / (color.size() - 1); int temp = 1; for (int i = 1; i < color.size(); i++) { total.add(Arrays.stream(NumberUtil.range(temp,temp + p -1)).boxed().collect(Collectors.toList())); temp = temp + p ; } for (int i = 0; i < total.size(); i++) { List
list = total.get(i); if(list.contains(j)){ cell.setCellStyle(color.get(i)); return; } } } private static List getBackgroundColour(XSSFWorkbook wb){ List list = new ArrayList<>(); XSSFCellStyle greeyStyle = wb.createCellStyle(); // greeyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_80_PERCENT.getIndex()); greeyStyle.setFillForegroundColor(new XSSFColor(new Color(217,217,217))); greeyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置水平对齐的样式为居中对齐; greeyStyle.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; greeyStyle.setVerticalAlignment(VerticalAlignment.CENTER); greeyStyle.setBorderBottom(BorderStyle.THIN); greeyStyle.setBorderLeft(BorderStyle.THIN); greeyStyle.setBorderTop(BorderStyle.THIN); greeyStyle.setBorderRight(BorderStyle.THIN); list.add(greeyStyle); XSSFCellStyle blueStyle = wb.createCellStyle(); // blueStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex()); blueStyle.setFillForegroundColor(new XSSFColor(new Color(221,235,247))); blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置水平对齐的样式为居中对齐; blueStyle.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; blueStyle.setVerticalAlignment(VerticalAlignment.CENTER); blueStyle.setBorderBottom(BorderStyle.THIN); blueStyle.setBorderLeft(BorderStyle.THIN); blueStyle.setBorderTop(BorderStyle.THIN); blueStyle.setBorderRight(BorderStyle.THIN); list.add(blueStyle); XSSFCellStyle yellowStyle = wb.createCellStyle(); // yellowStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex()); yellowStyle.setFillForegroundColor(new XSSFColor(new Color(255,242,204))); yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置水平对齐的样式为居中对齐; yellowStyle.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER); yellowStyle.setBorderBottom(BorderStyle.THIN); yellowStyle.setBorderLeft(BorderStyle.THIN); yellowStyle.setBorderTop(BorderStyle.THIN); yellowStyle.setBorderRight(BorderStyle.THIN); list.add(yellowStyle); XSSFCellStyle greenStyle = wb.createCellStyle(); // greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex()); greenStyle.setFillForegroundColor(new XSSFColor(new Color(226,239,218))); greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置水平对齐的样式为居中对齐; greenStyle.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; greenStyle.setVerticalAlignment(VerticalAlignment.CENTER); greenStyle.setBorderBottom(BorderStyle.THIN); greenStyle.setBorderLeft(BorderStyle.THIN); greenStyle.setBorderTop(BorderStyle.THIN); greenStyle.setBorderRight(BorderStyle.THIN); list.add(greenStyle); return list; }
3.导出结果
存在两列标题,第一列进行了合并,并设置了不同的背景色:
async exportFile(val){ // 设置参数 this[val] = true const param = { startTime:this.pickNowTopDate[0], endTime:this.pickNowTopDate[1] } // 调用后端方法 const res = val == 'exportVisitsInfo' ? await exportVisitsInfo(param) : val == 'exportViewsInfo' ? await exportViewsInfo(param) : await exportApplyInfo(param) // 对响应Response的处理 接收xlsx文件 if(res.data){ this[val] = false let filename = res.headers['content-disposition'] filename = filename.split('filename=')[1] let link = document.createElement("a"); let blogw = new Blob([res.data],{type:"application/vnd.ms-excel;charset=utf-8"}) let objectUrl = window.URL.createObjectURL(blogw); link.href = objectUrl; link.download = filename; link.click(); window.URL.revokeObjectURL(objectUrl) } }
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)