POI实现Excel导出时常用方法说明

小咪咪 2023-10-18 17:37 72阅读 0赞

## 场景 ##

SpringBoot中使用POI实现自定义Excel布局式导出

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/89497792][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_89497792]

## 实现 ##

新建XSSFWorkbook对象

XSSFWorkbook book=new XSSFWorkbook();

新建sheet页

Sheet tempSheet = null;
    
    tempSheet = book.createSheet();

sheet赋值

book.setSheetName(0, "霸道流氓");

创建单元格样式对象

XSSFCellStyle alignStyle = book.createCellStyle();

设置单元格水平居中

alignStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

设置单元格水平左对齐

alignStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);

设置单元格水平右对齐

alignStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT);

设置单元格竖直居中

alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

设置单元格竖直上对齐

alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);

设置单元格竖直下对齐

alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);

设置单元格底部边框样式

alignStyle.setBorderBottom(BorderStyle.THIN);

设置单元格左边边框样式

alignStyle.setBorderLeft(BorderStyle.THIN);

设置单元格右边边框样式

alignStyle.setBorderRight(BorderStyle.THIN);

设置单元格顶部边框样式

alignStyle.setBorderTop(BorderStyle.THIN);

设置单元格底部边框颜色

alignStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

设置单元格左边边框颜色

alignStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

设置单元格右边边框颜色

alignStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

设置单元格顶部边框颜色

alignStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

单元格设置字体

org.apache.poi.ss.usermodel.Font ztFont3 = book.createFont();
    ztFont3.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);
    alignStyle3.setFont(ztFont3);

创建行并给指定列赋值

//第一行第三列
    tempSheet.createRow(0).createCell(y+2).setCellValue("第一行第三列");

设置Cell样式

tempSheet.getRow(0).getCell(y+2).setCellStyle(alignStyle5);

合并单元格

//合并单元格Cell  第1行到第1行 第3列到第10列
    CellRangeAddress region = new CellRangeAddress(0,0,y+2,y+9);
    tempSheet.addMergedRegion(region);

设置请求响应类型以及标题

String title = "霸道流氓气质";
    response.reset();
    response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(title + ".xlsx", "UTF-8"))));
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");

生成excel

OutputStream out = response.getOutputStream();
    book.write(out);
    out.flush();
    out.close();

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_89497792]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/89497792