POI导出指定字段
产品要求导出改成可以前端选择自定义导出的列作为导出Excel的表头
实体类
@Data
@ApiModel(value = "订单表查询参数")
public class OpExportDTO implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 保函申请id
*/
@ApiModelProperty(value = "保函申请id")
private String guaranteeNo;
/**
* 订单编号
*/
@ApiModelProperty(value = "订单编号")
private String orderNo;
/**
* 保函编号
*/
private String guaranteeCode;
/**
* 提交时间
*
* @return
*/
@ApiModelProperty(value = "提交时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", locale = "zh", timezone = "GMT+8")
private Date createTime;
}
具体实现
/**
* @author: doris
* @date :2022/8/5 15:40
* 导出列的自适应宽度
*/
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public CustomCellWriteHandler() {
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
/**
* @params fieldList 要导入的列 比如["guaranteeCode","orderNo"]
*/
public File exportGuaranteeInfoExcel(List<String> fieldList) {
// excel 表数据
List<List<String>> bodyList = new ArrayList<>();
// excel 表头
List<List<String>> headList = new ArrayList<>();
// 获取所有可以导入的列
Map<String, String> exportFieldList = this.getExportFieldList();
List<OpExportDTO> data = new ArrayList<>();// 从数据库查询或者自己插入
if (!CollectionUtils.isEmpty(fieldList)) {
for (int i = 0; i < fieldList.size(); i++) {
// 添加表头
titleRow.createCell(i).setCellValue(exportFieldList.get(fieldList.get(i)));
}
if (!CollectionUtils.isEmpty(data)) {
for (OpExportDTO opExportDTO : data ) {
bodyList.addAll(this.dataList(queryParameter.getFieldList(), opExportDTO));
}
for (List<String> bodyData : bodyList) {
int lastRowNum = sheet.getLastRowNum();
HSSFRow row = sheet.createRow(lastRowNum + 1);
for (int i = 0; i < bodyData.size(); i++) {
row.createCell(i).setCellValue(bodyData.get(i));
}
}
}
}
File file = new File("G:\\1\\test" +fileName);
hssfWorkbook.write(new FileOutputStream(file));
return file;
}
/**
* 获取所有字段表头
*
* @return
*/
public Map<String, String> getExportFieldList() {
Map<String, String> map = new HashMap<>();
map.put("guaranteeCode", "保函编号");
map.put("orderNo", "订单编号");
map.put("createTime", "提交时间");
map.put("guaranteeRange", "保函类型");
map.put("oState", "订单状态");
map.put("gState", "申请单状态");
return map;
}
/**
* 要导出的字段数据
*/
@SneakyThrows
public static <T> List<List<T>> dataList(List<String> propList, OpExportDTO obj) {
List<List<T>> list = new ArrayList<>();
List<T> data = new ArrayList<>();
//先根据反射获取实体类的class对象
Class objClass = obj.getClass();
//设置实体类属性的集合
Field[] fields = ReflectUtil.getFields(objClass);
for (String prop : propList) {
//循环实体类对象集合
for (Field field : fields) {
field.setAccessible(true);
//判断实体类属性跟特定字段集合名是否一样
if (field.getName().equals(prop)) {
T object = (T) field.get(obj);
// 如果是日期类型转换为yyyy-MM-dd
if (field.getType() == Date.class) {
String dateStr = DateUtils.format((Date) object, DateUtils.PATTERN_DATE_10);
data.add((T) dateStr);
}else{
data.add(object);
}
}
}
}
list.add(data);
return list;
}
POI和EasyExcel对比
我找了个66行文档24列表头的数据,两种方式都循环了25次;方式一是POI,方式二是EasyExcel