我的需求：
项目中有一些工程表格需要导出，设计到行列合并，定制样式，原有工具类冗余，内聚性强。所以想写一个可以随意定制 excel 的工具类，工具类满足需求：
- 对于常用的工程表格有模板格式，可以任意插拔。
- 可以操作任意一个sheet页，进行行列合并，逻辑可以根据业务调整。
- 可以对当前存储sheet页数据行进行数据动态插入，单元格操作。
- 有实用的输出方式，比如报文实体，字节数组等。
- 最后轻量，代码逻辑清晰，好维护。
我是这样做的：
- 在构建上基于建造者设计模式
- 将excel的构建逻辑抽象，将数据注入逻辑从构建逻辑中分离出来，采用函数式编程的方式。
- 实现了样式不同的多sheet页构建。
- 在代码上结合原有工具类数据转化部分优点，利用ＪＳＯＮ格式实现数据处理
嗯， 时间有限，没有优化代码，小伙伴们有好的ＩＤＥＡ可以留言。可以基于这个扩展操作方法
使用方法：
public static void main(String[] args) {
// 使用方法
new WorkbookUtil()
// 文件名称
.set File Name(null)
// TODO 初始化一个excel
.initWorkbook()
// TODO 初始化一个sheet页,可自定义逻辑，(HSSFWorkbook,String) ->{ HSSFSheet}
.initSheet(null,null)
// TODO 初始化sheet页表头 ，可自定义逻辑，(HSSFSheet,HeadDetails) ->{ HSSFSheet} 下同
.initSheetHead(null,null,null,null)
// 填充sheet页数据
.setSheetData(null,null,null)
// sheet页数据指定列合并行
.setDataRowMerged(null,null,null)
// sheet页数据指定相邻列合并,可以传入多值，(单行合并，值相同情况)
.setDataCellMerged(null,null,null)
// 当前sheet当前行插入数据 data ：Map<Integer, String> (列索引,数据)
.setSheetRow(null,null)
// 当前sheet当前行强制合并 (firstCol,lastCol) 合并索引[必填]
.setRowMerged(null,null)
// 当前sheet指定行强制合并
.setCellRangeAddress(null,null)
// 初始化表尾
.initSheetFoot(null,null)
// TODO 第二个sheet页..
.initSheet(null,null)
.initSheetHead(null,null,null,null)
.setSheetData(null,null,null)
.initSheetFoot(null,null)
.setDataRowAndCellMerged(null,null,null)
//....
// .builderByte() 输出字节数组
// 直接写入报文
.builderResponseEntity();
}
具的Demo：
基本的表格
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/432ee4a69325562c6266faf0775e4c48.jpeg)
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/879bab051a7607667b26dbdf96849c43.jpeg)
简单的行列合并：
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/3c18c02819bd8cdb18abfd5e5001f1e2.jpeg)
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/cb2789caf415973ac9e1fbe68b6c80b5.jpeg)
稍微复杂的行列合并：
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/1815d45ed9497305e3b142f75076452f.jpeg)
![[建造者模式实战]如何用JAVA 实现基于 POI 的复杂表格导出工具类？](/wp-content/uploads/replace/f3869ee66e0adbaae98f703d093ae749.jpeg)
代码
package com.hhwy.pwps.util.excel;
import java.util.LinkedList;
import java.util.List;
/**
* @author Liruilong
* @Date 2021-01-20 15:37
* @Description:
*/
public class HeadDetails {
private List<HeadDetail> headDetails = new LinkedList<>();
public static class HeadDetail{
private String key;
private String title;
private int width = 50;
//列数据单元格是否对齐
private boolean center = true;
public String getKey() {
return key;
}
public HeadDetail setKey(String key) {
this.key = key;
return this;
}
public String getTitle() {
return title;
}
public HeadDetail setTitle(String title) {
this.title = title;
return this;
}
public int getWidth() {
return width;
}
public HeadDetail setWidth(int width) {
this.width = width;
return this;
}
public boolean isCenter() {
return center;
}
public HeadDetail setCenter(boolean center) {
this.center = center;
return this;
}
}
public HeadDetails add(String key,String title,int width){
this.headDetails.add(new HeadDetail().setTitle(title).setKey(key).setWidth(width));
return this;
}
public HeadDetails add(String key,String title,int width,boolean center){
this.headDetails.add(new HeadDetail().setTitle(title).setKey(key).setWidth(width).setCenter(center));
return this;
}
public HeadDetails add(String key,String title){
this.headDetails.add(new HeadDetail().setTitle(title).setKey(key));
return this;
}
public List<HeadDetail> builder(){
return this.headDetails;
}
public int headSize(){
return this.headDetails.size();
}
}
xxxxxxxxxxbr package com.hhwy.pwps.util.excel;brbrbrimport java.util.LinkedList;brimport java.util.List;brbr/**br * @author Liruilongbr * @Date 2021-01-20 15:37br * @Description:br */brpublic class HeadDetails {brbr private List<HeadDetail> headDetails = new LinkedList<>();brbrbr public static class HeadDetail{br private String key;br private String title;br private int width = 50;br //列数据单元格是否对齐br private boolean center = true;brbr public String getKey() {br return key;br }brbr public HeadDetail setKey(String key) {br this.key = key;br return this;br }brbr public String getTitle() {br return title;br }brbr public HeadDetail setTitle(String title) {br this.title = title;br return this;br }brbr public int getWidth() {br return width;br }brbr public HeadDetail setWidth(int width) {br this.width = width;br return this;br }brbr public boolean isCenter() {br return center;br }brbr public HeadDetail setCenter(boolean center) {br this.center = center;br return this;br }br }brbr public HeadDetails add(String key,String title,int width){br this.headDetails.add(new HeadDetail().setTitle(title).setKey(key).setWidth(width));br return this;br }br public HeadDetails add(String key,String title,int width,boolean center){br this.headDetails.add(new HeadDetail().setTitle(title).setKey(key).setWidth(width).setCenter(center));br return this;br }br public HeadDetails add(String key,String title){br this.headDetails.add(new HeadDetail().setTitle(title).setKey(key));br return this;br }brbrbr public List<HeadDetail> builder(){br return this.headDetails;br }brbr public int headSize(){br return this.headDetails.size();br }brbrbrbrbrbr}br
package com.hhwy.pwps.util.excel;
import com.alibaba.fast JSON .JSONObject;
import org. apache .poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import java.io.ByteArrayOutputStream;
import java.io.IO Exception ;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.*;
import java.util. Function .BiFunction;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* @author Liruilong
* @Date 2021-01-20 17:07
* @Description: excel导出工具类,可以通过 sheetBiFunction 自定义导入 逻辑
*/
public class WorkbookUtil {
public List<HSSFSheet> sheets = new ArrayList<>();
public HSSFWorkbook hssfWorkbook;
public volatile int sheetIndex = 0;
public String fileName = "Excel默认名称.xls";
public WorkbookUtil(String fileName) {
this.fileName = fileName + ".xls";
}
public WorkbookUtil() {
}
public String getFileName() {
return fileName;
}
public WorkbookUtil setFileName(String fileName) {
if (Objects.nonNull(fileName)){
this.fileName = fileName + ".xls";
}
return this;
}
public static void main(String[] args) {
// 使用方法
new WorkbookUtil()
// 文件名称
.setFileName(null)
// TODO 初始化一个excel
.initWorkbook()
// TODO 初始化一个sheet页,可自定义逻辑，(HSSFWorkbook,String) ->{ HSSFSheet}
.initSheet(null,null)
// TODO 初始化sheet页表头 ，可自定义逻辑，(HSSFSheet,HeadDetails) ->{ HSSFSheet} 下同
.initSheetHead(null,null,null,null)
// 填充sheet页数据
.setSheetData(null,null,null)
// sheet页数据指定列合并行
.setDataRowMerged(null,null,null)
// sheet页数据指定相邻列合并,可以传入多值，(单行合并，值相同情况)
.setDataCellMerged(null,null,null)
// 当前sheet当前行插入数据 data ：Map<Integer, String> (列索引,数据)
.setSheetRow(null,null)
// 当前sheet当前行强制合并 (firstCol,lastCol) 合并索引[必填]
.setRowMerged(null,null)
// 当前sheet指定行强制合并
.setCellRangeAddress(null,null)
// 初始化表尾
.initSheetFoot(null,null)
// TODO 第二个sheet页..
.initSheet(null,null)
.initSheetHead(null,null,null,null)
.setSheetData(null,null,null)
.initSheetFoot(null,null)
.setDataRowAndCellMerged(null,null,null)
//....
// .builderByte() 输出字节数组
// 直接写入报文
.builderResponseEntity();
}
/**
* <per>
* <p>行列合并，一般根据特定需求编码，这里没有写默认的逻辑</p>
* <per/>
* @param merged 传递的参数， Map <合并行的列索引,合并列的列索引>
* @param list
* @param sheetBiFunction
* @return com.hhwy.pwps.util.excel.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-24 18:28
**/
public WorkbookUtil setDataRowAndCellMerged(Map<int[], int[]> merged,List<?> list, BiFunction<HSSFSheet, Map<int[], int[]>, HSSFSheet> sheetBiFunction){
HSSFSheet sheet = this.sheets.get(sheetIndex);
if (Objects.nonNull(merged) && Objects.nonNull(list) && Objects.isNull(sheetBiFunction)) {
// 默认行列同时合并逻辑
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));
return this;
}
/**
* <per>
* <p>数据指定列索引合并</p>
* <per/>
*
* @param merged
* @param list
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-22 11:19
**/
public WorkbookUtil setDataCellMerged(Map<Integer, Integer> merged, List<?> list, BiFunction<HSSFSheet, Map<Integer, Integer>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {
int numberOfRows = sheet.getPhysicalNumberOfRows() - list.size();
List<Integer> cellIndex = merged.keySet().stream().collect(Collectors.toList());
for (int j = 0; j < cellIndex.size(); j++) {
int cellIndex_ = cellIndex.get(j);
for (int i = numberOfRows; i < sheet.getPhysicalNumberOfRows(); i++) {
HSSFRow sheetRow = sheet.getRow(i);
String cell1 = sheetRow.getCell(cellIndex_).toString();
String cell2 = sheetRow.getCell(merged.get(cellIndex_)).toString();
if (cell1.equals(cell2)) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(i, i, cellIndex_, merged.get(cellIndex_)));
}
}
}
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));
return this;
}
/**
* <per>
* <p>指定行索引强制合并指定的列数据:CellRangeAddress原始方法调用</p>
* <per/>
* @param merged
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-21 20:26
**/
public WorkbookUtil setCellRangeAddress(Map<int[], int[]> merged, BiFunction<HSSFSheet, Map<int[], int[]>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {
merged.forEach((o1, o2) -> {
sheet.addMergedRegionUnsafe(new CellRangeAddress(o1[0], o1[1], o2[0], o2[1]));
});
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));
return this;
}
/**
* <per>
* <p>数据列合并对应行数据</p>
* <per/>
*
* @param list sheet数据[必填]
* @param merged 列索引[必填]
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-21 15:13
**/
public WorkbookUtil setDataRowMerged(List<Integer> merged, List<?> list, BiFunction<HSSFSheet, List<Integer>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();
if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {
//数据起始行
int numberOfRows = sheet.getPhysicalNumberOfRows();
int first_ = numberOfRows - list.size();
merged.stream().forEach(index -> {
String old = null;
int first = first_;
for (int i = first; i < numberOfRows; i++) {
HSSFRow sheetRow = sheet.getRow(i);
String cell = sheetRow.getCell(index).toString();
//第一行跳过
if (i == first_) {
old = cell;
continue;
}
//合并逻辑
if (!old.equals(cell)) {
if (first != i - 1) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(first, i - 1, index, index));
}
first = i;
old = cell;
// 最后一行判断
} else if (i == numberOfRows - 1) {
if (first != i) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(first, i, index, index));
// cellRangeAddresses.add(new CellRangeAddress(first, i - 1, index, index));
}
}
}
});
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));
return this;
}
/**
* <per>
* <p>当前行实现单元格强制合并</p>
* <per/>
*
* @param merged 合并索引[必填] (firstCol,lastCol)
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-21 14:43
**/
public WorkbookUtil setRowMerged(Map<Integer, Integer> merged, BiFunction<HSSFSheet, Map<Integer, Integer>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {
int numberOfRows = sheet.getPhysicalNumberOfRows();
List<Integer> cellIndex = merged.keySet().stream().collect(Collectors.toList());
for (int i = 0; i < cellIndex.size(); i++) {
int index = cellIndex.get(i);
sheet.addMergedRegionUnsafe(new CellRangeAddress(numberOfRows - 1, numberOfRows - 1, index, merged.get(index)));
}
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));
return this;
}
/**
* <per>
* <p>sheet页指定列索引插入单行数据</p>
* <per/>
*
* @param data 单行数据[必填] Map<Integer, String> (列索引,数据)
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO Specify column index inserts single line data
* @author Liruilong
* @Date 2021-01-21 10:33
**/
public WorkbookUtil setSheetRow(Map<Integer, String> data, BiFunction<HSSFSheet, Map<Integer, String>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
if (Objects.nonNull(data) && Objects.isNull(sheetBiFunction)) {
int numberOfRows = sheet.getPhysicalNumberOfRows();
HSSFRow sheetRow = sheet.createRow(numberOfRows);
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
List<Integer> cellIndex = data.keySet().stream().collect(Collectors.toList());
for (int i = 0; i < cellIndex.size(); i++) {
int index = cellIndex.get(i);
HSSFCell cell = sheetRow.createCell(index);
cell.setCellValue(data.get(index));
cell.setCellStyle(cellStyle);
}
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, data));
return this;
}
/**
* <per>
* <p>sheet页数据填充</p>
* <per/>
*
* @param headDetails 表头[必填]
* @param list 数据[必填]
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-21 8:42
**/
public WorkbookUtil setSheetData(HeadDetails headDetails, List<?> list, BiFunction<HSSFSheet, List<?>, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
List<List<String>> sheetData = new ArrayList<List<String>>();
if (Objects.nonNull(list) && Objects.isNull(sheetBiFunction)) {
//准备数据
list.stream().forEach(o -> {
String jsonString = JSONObject.toJSONStringWithDateFormat(o, "yyyy-MM-dd HH:mm:ss");
// 将单行数据转化为JSON串。
JSONObject json = JSONObject.parseObject(jsonString);
List<String> collect = headDetails.builder().stream().map(headDetail -> {
String key = headDetail.getKey();
Object obj = json.getObject(key, Object.class);
if (obj instanceof String) {
return obj.toString();
} else if (obj instanceof BigDecimal) {
return ((BigDecimal) obj).stripTrailingZeros().toPlainString();
} else {
if (obj != null) {
return obj.toString();
} else {
return " ";
}
}
}).collect(Collectors.toList());
sheetData.add(collect);
});
int numberOfRows = sheet.getPhysicalNumberOfRows();
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//数据解析
for (int rowIndex = 0; rowIndex < sheetData.size(); rowIndex++) {
HSSFRow sheetRow = sheet.createRow(numberOfRows + rowIndex);
List<String> cells = sheetData.get(rowIndex);
for (int cellIndex = 0; cellIndex < cells.size(); cellIndex++) {
HSSFCell cell = sheetRow.createCell(cellIndex);
cell.setCellValue(cells.get(cellIndex));
if (headDetails.builder().get(cellIndex).isCenter()) {
cell.setCellStyle(cellStyle);
}
}
}
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, list));
return this;
}
/**
* <per>
* <p>shell页表尾初始化</p>
* <per/>
*
* @param sheetFoot 表尾[可选]
* @param sheetBiFunction
* @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-20 22:19
**/
public WorkbookUtil initSheetFoot(String sheetFoot, BiFunction<HSSFSheet, String, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.sheets.get(sheetIndex);
if (Objects.nonNull(sheetFoot) && Objects.isNull(sheetBiFunction)) {
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = hssfWorkbook.createFont();
int numberOfRows = sheet.getPhysicalNumberOfRows();
int numberOfCells = sheet.getRow(numberOfRows - 1).getPhysicalNumberOfCells();
HSSFRow sheetRow = sheet.createRow(numberOfRows);
sheetRow.setHeight((short) 800);
font.setFontHeightInPoints((short) 8);
font.setFontName("宋体");
cellStyle.setFont(font);
HSSFCell cell = sheetRow.createCell(0);
cell.setCellValue(sheetFoot);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(numberOfRows, numberOfRows, 0, numberOfCells - 1));
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, sheetFoot));
return this;
}
/**
* <per>
* <p>shell页表头的初始化,可以传入BiFunction自定义初始化逻辑,使用默认值，传入null</p>
* <per/>
*
* @param headDetails 表头[必填]
* @param sheetTitle 标题[可选]
* @param projectName 工程名称[可选]
* @param sheetBiFunction 初始化逻辑
* @return org.apache.poi.hssf.usermodel.HSSFSheet
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-20 17:52
**/
public WorkbookUtil initSheetHead(HeadDetails headDetails, String sheetTitle, String projectName, BiFunction<HSSFSheet, HeadDetails, HSSFSheet> sheetBiFunction) {
//行索引
int rowIndex = 0;
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
HSSFRow sheetRow = null;
HSSFSheet sheet = this.sheets.get(sheetIndex);
// 有标题行时
if (Objects.nonNull(sheetTitle)) {
HSSFCellStyle cellStyleTitle = hssfWorkbook.createCellStyle();
HSSFFont fontTitle = hssfWorkbook.createFont();
//对齐方式
cellStyleTitle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
fontTitle.setFontName("宋体");
//大小
fontTitle.setFontHeightInPoints((short) 21);
cellStyleTitle.setFont(fontTitle);
sheetRow = sheet.createRow(rowIndex++);
sheetRow.setHeight((short) 800);
HSSFCell cell = sheetRow.createCell(0);
cell.setCellValue(sheetTitle);
cell.setCellStyle(cellStyleTitle);
// 合并单元格
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, headDetails.headSize() - 1));
}
// 有项目工程名的时
if (Objects.nonNull(projectName)) {
HSSFCellStyle cellStyleName = hssfWorkbook.createCellStyle();
HSSFFont fontName = hssfWorkbook.createFont();
sheetRow = sheet.createRow(rowIndex++);
HSSFCell cell = sheetRow.createCell(0);
fontName.setBold(true);
fontName.setFontHeightInPoints((short) 10);
fontName.setFontName("微软雅黑");
cellStyleName.setAlignment(HorizontalAlignment.LEFT);
cellStyleName.setFont(fontName);
cell.setCellStyle(cellStyleName);
cell.setCellValue(projectName);
// 合并单元格
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, headDetails.headSize() - 1));
}
sheetRow = sheet.createRow(rowIndex++);
List<HeadDetails.HeadDetail> builder = headDetails.builder();
HSSFFont fontHead = hssfWorkbook.createFont();
fontHead.setBold(true);
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
fontHead.setFontHeightInPoints((short) 10);
fontHead.setFontName("微软雅黑");
cellStyle.setFont(fontHead);
for (int i = 0; i < headDetails.headSize(); i++) {
HSSFCell cell = sheetRow.createCell(i);
//填充单元格数据
HeadDetails.HeadDetail headDetail = builder.get(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(headDetail.getTitle());
sheet.setColumnWidth(i, headDetail.getWidth() * 200);
}
sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, headDetails));
return this;
}
/**
* <per>
* <p>sheet页初始化，设置默认值，可以传入BiFunction自定义初始化逻辑,使用默认值，传入null</p>
* <per/>
*
* @param sheetName sheet页名称[可选]
* @param sheetBiFunction 自定义sheet页规则，使用默认传入 null
* @return org.apache.poi.hssf.usermodel.HSSFSheet
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-20 16:23
**/
public WorkbookUtil initSheet(String sheetName, BiFunction<HSSFWorkbook, String, HSSFSheet> sheetBiFunction) {
HSSFSheet sheet = this.hssfWorkbook.createSheet(Optional.ofNullable(sheetName).orElse("sheet页XX"));
//创建默认样式
sheet.setDefaultColumnWidth(15);
sheet.setDefaultRowHeight((short) 300);
sheets.add(sheetIndex == 0 ? sheetIndex : ++sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(this.hssfWorkbook, sheetName));
return this;
}
/**
* <per>
* <p>Workbook 初始化</p>
* <per/>
*
* @param setCategory 文档类别[可选]
* @param setManager 文档管理员[可选]
* @param setCompany 设置公司信息[可选]
* @param setTitle 文档标题[可选]
* @param setAuthor 文档作者[可选]
* @param setComments 文档备注[可选]
* @return com.hhwy.pwps.managepointins.service.impl.ManagePointIn.WorkbookUtil
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-20 16:27
**/
public WorkbookUtil initWorkbook(String setCategory, String setManager, String setCompany, String setTitle, String setAuthor, String setComments) {
//1. 创建一个 Excel 文档
HSSFWorkbook workbook = new HSSFWorkbook();
//2. 创建文档摘要
workbook.createInformationProperties();
//3. 获取并配置文档信息
DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
//文档类别
docInfo.setCategory(Optional.ofNullable(setCategory).orElse("配网输出报表"));
//文档管理员
docInfo.setManager(Optional.ofNullable(setManager).orElse("配网工程评审平台"));
//设置公司信息
docInfo.setCompany(Optional.ofNullable(setCompany).orElse("XXXXXX"));
docInfo.setDocumentVersion("1.0");
//4. 获取文档摘要信息
SummaryInformation summInfo = workbook.getSummaryInformation();
//文档标题
summInfo.setTitle(Optional.ofNullable(setTitle).orElse("配网输出报表"));
//文档作者
summInfo.setAuthor(Optional.ofNullable(setAuthor).orElse("配网工程评审平台"));
// 创建时间
summInfo.setCreateDateTime(new Date());
// 文档备注
summInfo.setComments(Optional.ofNullable(setComments).orElse(LocalDateTime.now().toString() + " 配网工程评审平台导出"));
this.hssfWorkbook = workbook;
return this;
}
public WorkbookUtil initWorkbook() {
//1. 创建一个 Excel 文档
HSSFWorkbook workbook = new HSSFWorkbook();
this.hssfWorkbook = workbook;
return this;
}
/**
* <per>
* <p>Excel以字节数组输出</p>
* <per/>
*
* @param
* @return byte[]
* @throws
* @Description : TODO Output byte array
* @author Liruilong
* @Date 2021-01-21 8:44
**/
public byte[] builderByte() {
return hssfWorkbook.getBytes();
}
/**
* <per>
* <p>Excel以application/octet-stream形式输出，返回二进制的报文实体</p>
* <per/>
*
* @param
* @return org.springframework.http.ResponseEntity<byte [ ]>
* @throws
* @Description : TODO
* @author Liruilong
* @Date 2021-01-21 8:43
**/
public ResponseEntity<byte[]> builderResponseEntity() {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
HttpHeaders headers = new HttpHeaders();
try {
headers.setContentDispositionFormData("attachment", new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
hssfWorkbook.write(baos);
} catch (IOException e) {
e.printStackTrace();
}
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
}
}
xxxxxxxxxxbr package com.hhwy.pwps.util.excel;brbrimport com.alibaba.fastjson.JSONObject;brimport org.apache.poi.hpsf.DocumentSummaryInformation;brimport org.apache.poi.hpsf.SummaryInformation;brimport org.apache.poi.hssf.usermodel.*;brimport org.apache.poi.ss.usermodel.HorizontalAlignment;brimport org.apache.poi.ss.usermodel.VerticalAlignment;brimport org.apache.poi.ss.util.CellRangeAddress;brimport org.springframework.http.HttpHeaders;brimport org.springframework.http.HttpStatus;brimport org.springframework.http.MediaType;brimport org.springframework.http.ResponseEntity;brbrimport java.io.ByteArrayOutputStream;brimport java.io.IOException;brimport java.math.BigDecimal;brimport java.time.LocalDateTime;brimport java.util.*;brimport java.util.function.BiFunction;brimport java.util.stream.Collectors;brimport java.util.stream.Stream;brbr/**br * @author Liruilongbr * @Date 2021-01-20 17:07br * @Description: excel导出工具类,可以通过 sheetBiFunction 自定义导入 逻辑br */brpublic class WorkbookUtil {br public List<HSSFSheet> sheets = new ArrayList<>();br public HSSFWorkbook hssfWorkbook;br public volatile int sheetIndex = 0;br public String fileName = "Excel默认名称.xls";brbrbr public WorkbookUtil(String fileName) {br this.fileName = fileName + ".xls";br }brbr public WorkbookUtil() {br }brbr public String getFileName() {br return fileName;br }brbr public WorkbookUtil setFileName(String fileName) {br if (Objects.nonNull(fileName)){br this.fileName = fileName + ".xls";br }br return this;br }brbrbr public static void main(String[] args) {brbr // 使用方法br new WorkbookUtil()br // 文件名称br .setFileName(null)br // TODO 初始化一个excelbr .initWorkbook()br // TODO 初始化一个sheet页,可自定义逻辑，(HSSFWorkbook,String) ->{ HSSFSheet}br .initSheet(null,null)br // TODO 初始化sheet页表头 ，可自定义逻辑，(HSSFSheet,HeadDetails) ->{ HSSFSheet} 下同br .initSheetHead(null,null,null,null)br // 填充sheet页数据br .setSheetData(null,null,null)br // sheet页数据指定列合并行br .setDataRowMerged(null,null,null)br // sheet页数据指定相邻列合并,可以传入多值，(单行合并，值相同情况)br .setDataCellMerged(null,null,null)br // 当前sheet当前行插入数据 data ：Map<Integer, String> (列索引,数据)br .setSheetRow(null,null)br // 当前sheet当前行强制合并 (firstCol,lastCol) 合并索引[必填]br .setRowMerged(null,null)br // 当前sheet指定行强制合并br .setCellRangeAddress(null,null)br // 初始化表尾br .initSheetFoot(null,null)br // TODO 第二个sheet页..br .initSheet(null,null)br .initSheetHead(null,null,null,null)br .setSheetData(null,null,null)br .initSheetFoot(null,null)br .setDataRowAndCellMerged(null,null,null)br //....br // .builderByte() 输出字节数组br // 直接写入报文br .builderResponseEntity();brbr }brbrbrbr /**br * <per>br * <p>行列合并，一般根据特定需求编码，这里没有写默认的逻辑</p>br * <per/>br * @param merged 传递的参数，Map<合并行的列索引,合并列的列索引>br * @param listbr * @param sheetBiFunctionbr * @return com.hhwy.pwps.util.excel.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-24 18:28br **/br public WorkbookUtil setDataRowAndCellMerged(Map<int[], int[]> merged,List<?> list, BiFunction<HSSFSheet, Map<int[], int[]>, HSSFSheet> sheetBiFunction){brbr HSSFSheet sheet = this.sheets.get(sheetIndex);br if (Objects.nonNull(merged) && Objects.nonNull(list) && Objects.isNull(sheetBiFunction)) {br // 默认行列同时合并逻辑br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));br return this;br }brbrbr /**br * <per>br * <p>数据指定列索引合并</p>br * <per/>br *br * @param mergedbr * @param listbr * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-22 11:19br **/br public WorkbookUtil setDataCellMerged(Map<Integer, Integer> merged, List<?> list, BiFunction<HSSFSheet, Map<Integer, Integer>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();br if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {br int numberOfRows = sheet.getPhysicalNumberOfRows() - list.size();br List<Integer> cellIndex = merged.keySet().stream().collect(Collectors.toList());br for (int j = 0; j < cellIndex.size(); j++) {br int cellIndex_ = cellIndex.get(j);br for (int i = numberOfRows; i < sheet.getPhysicalNumberOfRows(); i++) {br HSSFRow sheetRow = sheet.getRow(i);br String cell1 = sheetRow.getCell(cellIndex_).toString();br String cell2 = sheetRow.getCell(merged.get(cellIndex_)).toString();br if (cell1.equals(cell2)) {br sheet.addMergedRegionUnsafe(new CellRangeAddress(i, i, cellIndex_, merged.get(cellIndex_)));br }br }br }br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));br return this;br }brbrbr /**br * <per>br * <p>指定行索引强制合并指定的列数据:CellRangeAddress原始方法调用</p>br * <per/>br * @param mergedbr * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-21 20:26br **/br public WorkbookUtil setCellRangeAddress(Map<int[], int[]> merged, BiFunction<HSSFSheet, Map<int[], int[]>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {br merged.forEach((o1, o2) -> {br sheet.addMergedRegionUnsafe(new CellRangeAddress(o1[0], o1[1], o2[0], o2[1]));br });br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));br return this;br }brbr /**br * <per>br * <p>数据列合并对应行数据</p>br * <per/>br *br * @param list sheet数据[必填]br * @param merged 列索引[必填]br * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-21 15:13br **/br public WorkbookUtil setDataRowMerged(List<Integer> merged, List<?> list, BiFunction<HSSFSheet, List<Integer>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();br if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {br //数据起始行br int numberOfRows = sheet.getPhysicalNumberOfRows();br int first_ = numberOfRows - list.size();br merged.stream().forEach(index -> {br String old = null;br int first = first_;br for (int i = first; i < numberOfRows; i++) {br HSSFRow sheetRow = sheet.getRow(i);br String cell = sheetRow.getCell(index).toString();br //第一行跳过br if (i == first_) {br old = cell;br continue;br }br //合并逻辑br if (!old.equals(cell)) {br if (first != i - 1) {br sheet.addMergedRegionUnsafe(new CellRangeAddress(first, i - 1, index, index));br }br first = i;br old = cell;br // 最后一行判断br } else if (i == numberOfRows - 1) {br if (first != i) {br sheet.addMergedRegionUnsafe(new CellRangeAddress(first, i, index, index));br // cellRangeAddresses.add(new CellRangeAddress(first, i - 1, index, index));br }br }br }br });br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));br return this;br }brbr /**br * <per>br * <p>当前行实现单元格强制合并</p>br * <per/>br *br * @param merged 合并索引[必填] (firstCol,lastCol)br * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-21 14:43br **/br public WorkbookUtil setRowMerged(Map<Integer, Integer> merged, BiFunction<HSSFSheet, Map<Integer, Integer>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br if (Objects.nonNull(merged) && Objects.isNull(sheetBiFunction)) {br int numberOfRows = sheet.getPhysicalNumberOfRows();br List<Integer> cellIndex = merged.keySet().stream().collect(Collectors.toList());br for (int i = 0; i < cellIndex.size(); i++) {br int index = cellIndex.get(i);br sheet.addMergedRegionUnsafe(new CellRangeAddress(numberOfRows - 1, numberOfRows - 1, index, merged.get(index)));br }br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, merged));br return this;br }brbr /**br * <per>br * <p>sheet页指定列索引插入单行数据</p>br * <per/>br *br * @param data 单行数据[必填] Map<Integer, String> (列索引,数据)br * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODO Specify column index inserts single line databr * @author Liruilongbr * @Date 2021-01-21 10:33br **/br public WorkbookUtil setSheetRow(Map<Integer, String> data, BiFunction<HSSFSheet, Map<Integer, String>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br if (Objects.nonNull(data) && Objects.isNull(sheetBiFunction)) {br int numberOfRows = sheet.getPhysicalNumberOfRows();br HSSFRow sheetRow = sheet.createRow(numberOfRows);br HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();br cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);br cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);br List<Integer> cellIndex = data.keySet().stream().collect(Collectors.toList());br for (int i = 0; i < cellIndex.size(); i++) {br int index = cellIndex.get(i);br HSSFCell cell = sheetRow.createCell(index);br cell.setCellValue(data.get(index));br cell.setCellStyle(cellStyle);br }br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, data));br return this;br }brbrbr /**br * <per>br * <p>sheet页数据填充</p>br * <per/>br *br * @param headDetails 表头[必填]br * @param list 数据[必填]br * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-21 8:42br **/br public WorkbookUtil setSheetData(HeadDetails headDetails, List<?> list, BiFunction<HSSFSheet, List<?>, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br List<List<String>> sheetData = new ArrayList<List<String>>();br if (Objects.nonNull(list) && Objects.isNull(sheetBiFunction)) {br //准备数据br list.stream().forEach(o -> {br String jsonString = JSONObject.toJSONStringWithDateFormat(o, "yyyy-MM-dd HH:mm:ss");br // 将单行数据转化为JSON串。br JSONObject json = JSONObject.parseObject(jsonString);br List<String> collect = headDetails.builder().stream().map(headDetail -> {br String key = headDetail.getKey();br Object obj = json.getObject(key, Object.class);br if (obj instanceof String) {br return obj.toString();br } else if (obj instanceof BigDecimal) {br return ((BigDecimal) obj).stripTrailingZeros().toPlainString();br } else {br if (obj != null) {br return obj.toString();br } else {br return " ";br }br }br }).collect(Collectors.toList());br sheetData.add(collect);br });br int numberOfRows = sheet.getPhysicalNumberOfRows();br HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();br cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);br cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);br //数据解析br for (int rowIndex = 0; rowIndex < sheetData.size(); rowIndex++) {br HSSFRow sheetRow = sheet.createRow(numberOfRows + rowIndex);br List<String> cells = sheetData.get(rowIndex);br for (int cellIndex = 0; cellIndex < cells.size(); cellIndex++) {br HSSFCell cell = sheetRow.createCell(cellIndex);br cell.setCellValue(cells.get(cellIndex));br if (headDetails.builder().get(cellIndex).isCenter()) {br cell.setCellStyle(cellStyle);br }br }br }br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, list));br return this;br }brbr /**br * <per>br * <p>shell页表尾初始化</p>br * <per/>br *br * @param sheetFoot 表尾[可选]br * @param sheetBiFunctionbr * @return com.hhwy.pwps.managepointins.service.impl.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-20 22:19br **/br public WorkbookUtil initSheetFoot(String sheetFoot, BiFunction<HSSFSheet, String, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.sheets.get(sheetIndex);br if (Objects.nonNull(sheetFoot) && Objects.isNull(sheetBiFunction)) {br HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();br cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);br HSSFFont font = hssfWorkbook.createFont();br int numberOfRows = sheet.getPhysicalNumberOfRows();br int numberOfCells = sheet.getRow(numberOfRows - 1).getPhysicalNumberOfCells();br HSSFRow sheetRow = sheet.createRow(numberOfRows);br sheetRow.setHeight((short) 800);br font.setFontHeightInPoints((short) 8);br font.setFontName("宋体");br cellStyle.setFont(font);br HSSFCell cell = sheetRow.createCell(0);br cell.setCellValue(sheetFoot);br cell.setCellStyle(cellStyle);br sheet.addMergedRegionUnsafe(new CellRangeAddress(numberOfRows, numberOfRows, 0, numberOfCells - 1));br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, sheetFoot));br return this;br }brbrbr /**br * <per>br * <p>shell页表头的初始化,可以传入BiFunction自定义初始化逻辑,使用默认值，传入null</p>br * <per/>br *br * @param headDetails 表头[必填]br * @param sheetTitle 标题[可选]br * @param projectName 工程名称[可选]br * @param sheetBiFunction 初始化逻辑br * @return org.apache.poi.hssf.usermodel.HSSFSheetbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-20 17:52br **/br public WorkbookUtil initSheetHead(HeadDetails headDetails, String sheetTitle, String projectName, BiFunction<HSSFSheet, HeadDetails, HSSFSheet> sheetBiFunction) {br //行索引br int rowIndex = 0;br HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();br HSSFRow sheetRow = null;br HSSFSheet sheet = this.sheets.get(sheetIndex);br // 有标题行时br if (Objects.nonNull(sheetTitle)) {br HSSFCellStyle cellStyleTitle = hssfWorkbook.createCellStyle();br HSSFFont fontTitle = hssfWorkbook.createFont();br //对齐方式br cellStyleTitle.setAlignment(HorizontalAlignment.CENTER_SELECTION);br cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);br fontTitle.setFontName("宋体");br //大小br fontTitle.setFontHeightInPoints((short) 21);br cellStyleTitle.setFont(fontTitle);br sheetRow = sheet.createRow(rowIndex++);br sheetRow.setHeight((short) 800);br HSSFCell cell = sheetRow.createCell(0);br cell.setCellValue(sheetTitle);br cell.setCellStyle(cellStyleTitle);br // 合并单元格br sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, headDetails.headSize() - 1));br }br // 有项目工程名的时br if (Objects.nonNull(projectName)) {br HSSFCellStyle cellStyleName = hssfWorkbook.createCellStyle();br HSSFFont fontName = hssfWorkbook.createFont();br sheetRow = sheet.createRow(rowIndex++);br HSSFCell cell = sheetRow.createCell(0);br fontName.setBold(true);br fontName.setFontHeightInPoints((short) 10);br fontName.setFontName("微软雅黑");br cellStyleName.setAlignment(HorizontalAlignment.LEFT);br cellStyleName.setFont(fontName);br cell.setCellStyle(cellStyleName);br cell.setCellValue(projectName);br // 合并单元格br sheet.addMergedRegionUnsafe(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, headDetails.headSize() - 1));br }brbr br sheetRow = sheet.createRow(rowIndex++);br List<HeadDetails.HeadDetail> builder = headDetails.builder();br HSSFFont fontHead = hssfWorkbook.createFont();br fontHead.setBold(true);br cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);br cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);br fontHead.setFontHeightInPoints((short) 10);br fontHead.setFontName("微软雅黑");br cellStyle.setFont(fontHead);br for (int i = 0; i < headDetails.headSize(); i++) {br HSSFCell cell = sheetRow.createCell(i);br //填充单元格数据br HeadDetails.HeadDetail headDetail = builder.get(i);br cell.setCellStyle(cellStyle);br cell.setCellValue(headDetail.getTitle());br sheet.setColumnWidth(i, headDetail.getWidth() * 200);br }br sheets.add(sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(sheet, headDetails));br return this;br }brbrbr /**br * <per>br * <p>sheet页初始化，设置默认值，可以传入BiFunction自定义初始化逻辑,使用默认值，传入null</p>br * <per/>br *br * @param sheetName sheet页名称[可选]br * @param sheetBiFunction 自定义sheet页规则，使用默认传入 nullbr * @return org.apache.poi.hssf.usermodel.HSSFSheetbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-20 16:23br **/br public WorkbookUtil initSheet(String sheetName, BiFunction<HSSFWorkbook, String, HSSFSheet> sheetBiFunction) {br HSSFSheet sheet = this.hssfWorkbook.createSheet(Optional.ofNullable(sheetName).orElse("sheet页XX"));br //创建默认样式br sheet.setDefaultColumnWidth(15);br sheet.setDefaultRowHeight((short) 300);brbr sheets.add(sheetIndex == 0 ? sheetIndex : ++sheetIndex, Objects.isNull(sheetBiFunction) ? sheet : sheetBiFunction.apply(this.hssfWorkbook, sheetName));br return this;br }brbrbr /**br * <per>br * <p>Workbook 初始化</p>br * <per/>br *br * @param setCategory 文档类别[可选]br * @param setManager 文档管理员[可选]br * @param setCompany 设置公司信息[可选]br * @param setTitle 文档标题[可选]br * @param setAuthor 文档作者[可选]br * @param setComments 文档备注[可选]br * @return com.hhwy.pwps.managepointins.service.impl.ManagePointIn.WorkbookUtilbr * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-20 16:27br **/br public WorkbookUtil initWorkbook(String setCategory, String setManager, String setCompany, String setTitle, String setAuthor, String setComments) {br //1. 创建一个 Excel 文档br HSSFWorkbook workbook = new HSSFWorkbook();br //2. 创建文档摘要br workbook.createInformationProperties();br //3. 获取并配置文档信息br DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();br //文档类别br docInfo.setCategory(Optional.ofNullable(setCategory).orElse("配网输出报表"));br //文档管理员br docInfo.setManager(Optional.ofNullable(setManager).orElse("配网工程评审平台"));br //设置公司信息br docInfo.setCompany(Optional.ofNullable(setCompany).orElse("XXXXXX"));br docInfo.setDocumentVersion("1.0");br //4. 获取文档摘要信息br SummaryInformation summInfo = workbook.getSummaryInformation();br //文档标题br summInfo.setTitle(Optional.ofNullable(setTitle).orElse("配网输出报表"));br //文档作者br summInfo.setAuthor(Optional.ofNullable(setAuthor).orElse("配网工程评审平台"));br // 创建时间br summInfo.setCreateDateTime(new Date());br // 文档备注br summInfo.setComments(Optional.ofNullable(setComments).orElse(LocalDateTime.now().toString() + " 配网工程评审平台导出"));br this.hssfWorkbook = workbook;br return this;br }brbrbr public WorkbookUtil initWorkbook() {br //1. 创建一个 Excel 文档br HSSFWorkbook workbook = new HSSFWorkbook();br this.hssfWorkbook = workbook;br return this;br }brbrbr /**br * <per>br * <p>Excel以字节数组输出</p>br * <per/>br *br * @parambr * @return byte[]br * @throwsbr * @Description : TODO Output byte arraybr * @author Liruilongbr * @Date 2021-01-21 8:44br **/br public byte[] builderByte() {br return hssfWorkbook.getBytes();br }brbrbr /**br * <per>br * <p>Excel以application/octet-stream形式输出，返回二进制的报文实体</p>br * <per/>br *br * @parambr * @return org.springframework.http.ResponseEntity<byte [ ]>br * @throwsbr * @Description : TODObr * @author Liruilongbr * @Date 2021-01-21 8:43br **/br public ResponseEntity<byte[]> builderResponseEntity() {br ByteArrayOutputStream baos = new ByteArrayOutputStream();br HttpHeaders headers = new HttpHeaders();br try {br headers.setContentDispositionFormData("attachment", new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));br headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);br hssfWorkbook.write(baos);br } catch (IOException e) {br e.printStackTrace();br }br return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);br }brbrbrbr}br
