一、多sheet
通过配置动态生成多个工作表(sheet)，自定义sheet的名称，如下效果图：
二、多表头
通过配置生成多表头，效果图如下：
主要核心代码：
// 赋值后，执行合并单元格
log.trace("合并单元格 Row[{}->{}], Col[{}->{}]", i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
CellRangeAddress cra = new CellRangeAddress(i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框三、单元格下拉选择
下载模板中，单元格支持下拉选择，效果图：
主要核心代码：
private void appendCellConstraint(List<ExcelColumnConfig> columns, Sheet sheet) {
int maxRow = SpreadsheetVersion.EXCEL2007.getLastRowIndex(); //65536
for (int i = 0; i < columns.size(); i++) {
ExcelColumnConfig config = columns.get(i);
List<String> constraintVal = config.getMapper().columnConstraint();
if (Objects.isNull(constraintVal) || CollectionUtils.isEmpty(constraintVal)) continue;
if (constraintVal.size() > 60) {
log.warn("下拉选项({})过多 = {}, skip...", config.getDataLabel(), constraintVal.size());
continue;
}
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
DataValidationConstraint constraint = helper.createExplicitListConstraint(constraintVal.toArray(new String[constraintVal.size()]));
CellRangeAddressList regions = new CellRangeAddressList(0, maxRow, i, i);
sheet.addValidationData(helper.createValidation(constraint, regions));
log.debug("为第[{}]列增加了下拉约束{}", i, constraintVal);
}
}4、根据列名匹配转为List<?>
模板列名（单表头）不需要固定顺序，且列名可多可少，通过列名配置字段（匹配不上的列名不做任何处理），转为Map<String,List<?>>,其中key为sheet名称，List<?>中?可以为map,如果想转为具体实体类，采用json转为class即可。
主要核心代码：
/**
* 备注：
* 1、目前只处理单表头，根据标题名称获取取配置信息
* 2、如果是多表头，根据列头获取配置顺序，并表示与Excel列顺序
*/
private List<ExcelColumnConfig> findConfigByExcelColumnName(Row row, List<ExcelColumnConfig> configs, int titleRows, String sheetName) {
if (1 != titleRows) {
AtomicInteger ao = new AtomicInteger(0);
return configs.stream().peek(i -> i.setExcelColumnOrder(ao.getAndIncrement())).collect(Collectors.toList());
}
List<ExcelColumnConfig> result = Lists.newArrayList();
for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
Cell cell = row.getCell(cellIndex);
String cellRawStrVal = Objects.toString(cell, null);
// 把前后空格去掉
cellRawStrVal = !Strings.isNullOrEmpty(cellRawStrVal) ? cellRawStrVal.trim() : null;
if (Strings.isNullOrEmpty(cellRawStrVal)) continue;
// FIXME:_Atomic 这里有点低效，没细看，总体为N*M循环
// 去掉后缀后再判断，避免模板存在后缀:(必填)
String finalCellRawStrVal = cellRawStrVal;
Optional<ExcelColumnConfig> columnConfigOpt = configs.stream()
.filter(i -> {
String dataLabel = StringUtils.substringBefore(i.getDataLabel(), KEY_EXCEL_COLUMN_SUFFIX);
String cellVal = StringUtils.substringBefore(finalCellRawStrVal, KEY_EXCEL_COLUMN_SUFFIX);
return dataLabel.equals(cellVal);
}).findFirst();
if (!columnConfigOpt.isPresent()) {
log.debug("{}-->[{}]未在配置中，跳过", sheetName, cellRawStrVal);
continue;
}
ExcelColumnConfig columnConfig = columnConfigOpt.get();
columnConfig.setExcelColumnOrder(cellIndex);
result.add(columnConfig);
}
Set<String> columnNames = result.stream().map(ExcelColumnConfig::getDataLabel).collect(Collectors.toSet());
if (columnNames.size() != result.size()) {
log.error("标题列名存在重复，不能完成操作");
throw BusinessException.withMessage(ErrorCode.ERR_10001, "标题列名存在重复，不能完成操作");
}
return result;
}工具类代码：
package com.excel.app.utils;
import com.excel.app.utils.exception.BusinessException;
import com.excel.app.utils.exception.ErrorCode;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;
import static org.apache.poi.ss.usermodel.HorizontalAlignment.*;
/**
* 多sheet的excel工具类
* １、通过注解方式导入导出
* 2、调用模板，并在模板基础上进行赋值
* 3、动态创建表头（多表头）与数值导出excel数据
* 4、根据表头列名匹配获取excel数据
*/
@Slf4j @Getter
public class ExportExcelUtil extends BaseExcelTransformToList {
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 计算sheet头部行号,每个sheet都重新初始化
*/
private int rownum;
Map<String, Integer> rownumMap = new LinkedHashMap<>();
/**
* 构造函数（用于包含头部的模板）
*
* @param tplHeaderRownum 模板头部占用行数
* @param tplPath 模板路径文件
* @param mapList 数据列表信息，根据模板内容只用于动态拼装数据
*/
public ExportExcelUtil(int tplHeaderRownum, int tplColunm, boolean isCreateRow, String tplPath, Map<String, List<List<Object>>> mapList) {
try {
File newFile = new File(tplPath);
InputStream is = new FileInputStream(newFile);// 将excel文件转为输入流
XSSFWorkbook workbook = new XSSFWorkbook(is);
this.wb = new SXSSFWorkbook(workbook);//重新new以后，模板内容就读取不到
} catch (Exception e) {
e.printStackTrace();
}
setData(tplHeaderRownum, tplColunm, isCreateRow, mapList);
}
/**
* 构造函数（用于模板）
*
* @param tplHeaderRownum 模板头部占用行数
* @param tplColunm 模板列数占用列数
* @param isCreateRow 是否动态添加行数
* @param mapList 数据列表信息，根据模板内容只用于动态拼装数据
*/
public ExportExcelUtil(int tplHeaderRownum, int tplColunm, boolean isCreateRow, InputStream is, Map<String, List<List<Object>>> mapList) {
try {
XSSFWorkbook workbook = new XSSFWorkbook(is);
this.wb = new SXSSFWorkbook(workbook);
} catch (Exception e) {
e.printStackTrace();
}
setData(tplHeaderRownum, tplColunm, isCreateRow, mapList);
}
/**
* 构造函数（构建模板）
*
* @param is 文件流
*/
public ExportExcelUtil(InputStream is) {
try {
this.wb = new XSSFWorkbook(is);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 动态创建表头以及数据
*
* @param sheets 工作表s
*/
public ExportExcelUtil(List<ExcelSheetConfig> sheets) {
sheets.forEach(config -> {
this.rownum = 0;
List<String> headerLabel = config.getColumns().stream().map(ExcelColumnConfig::getDataLabel).collect(Collectors.toList());
Sheet sheet = getSheet(config.getName());
if (CollectionUtils.isEmpty(config.getGroupHeaders())) {
addSheet(sheet, config.getTitle(), headerLabel);
} else {
addSheetGroupHeader(sheet, config.getTitle(), config.getGroupHeaders());
}
appendCellConstraint(config.getColumns(), sheet);
//data rows...
fillDataRow(config.getColumns(), config.getRows(), sheet, rownum);
createMergedRegion(config.getMergedRegions(), sheet);
});
}
/**
* 合并单元格
*/
private void createMergedRegion(List<MergedRegion> mergedRegions, Sheet sheet) {
if (CollectionUtils.isEmpty(mergedRegions)) return;
mergedRegions.forEach(i -> {
if (i.getFirstRow() == i.getLastRow() && i.getFirstCol() == i.getLastCol()) return;
CellRangeAddress cra = new CellRangeAddress(i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
sheet.addMergedRegion(cra);
});
//TODO 是否需要添加样式???
}
/**
* @param headerList 表头列表
*/
private void addSheetGroupHeader(Sheet sheet, String title, List<GroupHeader> headerList) {
if (Objects.isNull(this.wb)) this.wb = new XSSFWorkbook();
this.styles = createStyles(wb);
// Create title
if (!Strings.isNullOrEmpty(title)) createHeaderRow(title, headerList.size() - 1, sheet, styles);
// Create header
if (headerList == null) {
throw new BusinessException(ErrorCode.ERR_20604);
}
createGroupHeader(headerList, sheet);
}
/**
* 创建分组表头
*/
private void createGroupHeader(List<GroupHeader> groupHeaders, Sheet sheet) {
if (CollectionUtils.isEmpty(groupHeaders)) return;
int finalRowNum = groupHeaders.get(0).getFirstRow();
Row newRow = sheet.getRow(finalRowNum);
if (newRow == null) {
newRow = sheet.createRow(finalRowNum);
}
if (rownum <= finalRowNum) rownum = finalRowNum + 1;
newRow.setHeightInPoints(30);
Row finalNewRow = newRow;
for (GroupHeader i : groupHeaders) {
addCell(finalNewRow, i.getFirstCol(), i.getName(), 2, Class.class);
if (i.getFirstRow() != i.getLastRow() || i.getFirstCol() != i.getLastCol()) {
log.trace("合并单元格 Row[{}->{}], Col[{}->{}]", i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
CellRangeAddress cra = new CellRangeAddress(i.getFirstRow(), i.getLastRow(), i.getFirstCol(), i.getLastCol());
sheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
}
createGroupHeader(i.getChildren(), sheet);
}
}
/**
* 创建跨全部列的标题
*/
private void createHeaderRow(String title, int columnSize, Sheet sheet, Map<String, CellStyle> styles) {
Row titleRow = sheet.createRow(rownum++);// 第一行默认为标题
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), columnSize));
}
private void fillDataRow(List<ExcelColumnConfig> configs, List<Map<String, Object>> rows, Sheet fileSheet, int startRow) {
if (CollectionUtils.isEmpty(rows)) return;
int rowNum = startRow;
for (Map<String, Object> rowData : rows) {
Row row = fileSheet.getRow(rowNum);
if (row == null) {
row = fileSheet.createRow(rowNum);
}
for (int i = 0; i < configs.size(); i++) {
ExcelColumnConfig rowConfig = configs.get(i);
Object val = MapUtils.getObject(rowData, rowConfig.getKey());
addCell(row, i, rowConfig.getMapper().mapToCellVal(val));
}
rowNum++;
}
}
private void appendCellConstraint(List<ExcelColumnConfig> columns, Sheet sheet) {
int maxRow = SpreadsheetVersion.EXCEL2007.getLastRowIndex(); //65536
for (int i = 0; i < columns.size(); i++) {
ExcelColumnConfig config = columns.get(i);
List<String> constraintVal = config.getMapper().columnConstraint();
if (Objects.isNull(constraintVal) || CollectionUtils.isEmpty(constraintVal)) continue;
if (constraintVal.size() > 60) {
log.warn("下拉选项({})过多 = {}, skip...", config.getDataLabel(), constraintVal.size());
continue;
}
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
DataValidationConstraint constraint = helper.createExplicitListConstraint(constraintVal.toArray(new String[constraintVal.size()]));
CellRangeAddressList regions = new CellRangeAddressList(0, maxRow, i, i);
sheet.addValidationData(helper.createValidation(constraint, regions));
log.debug("为第[{}]列增加了下拉约束{}", i, constraintVal);
}
}
/**
* 新增sheet
*/
private void addSheet(Sheet sheet, String title, List<String> headerList) {
if (this.wb == null) {
this.wb = new XSSFWorkbook();
}
this.styles = createStyles(wb);
// add title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new BusinessException(ErrorCode.ERR_20604);
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment = sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, Math.max(colWidth, 3000));
}
}
/**
* 给表格赋值
*
* @param tplHeaderRownum 模板头部占用行数
* @param tplColunm 模板列数占用列数
* @param mapList 生成数据集
*/
public void setData(int tplHeaderRownum, int tplColunm, boolean isCreateRow, Map<String, List<List<Object>>> mapList) {
if (mapList == null) return;
for (Map.Entry<String, List<List<Object>>> entry : mapList.entrySet()) {
int rownum = tplHeaderRownum;
String sheetName = entry.getKey();//sheet名称
if (tplHeaderRownum == 0) {
rownum = rownumMap.get(sheetName);//获取本sheet的row行数
}
//根据sheet名称获取sheet对象
Sheet sheet = getSheet(sheetName);
List<List<Object>> lists = entry.getValue();
//根据内容插入行数
if (isCreateRow) {
for (int i = 1; i < lists.size(); i++) {
sheet.shiftRows(tplHeaderRownum + 1, sheet.getLastRowNum(), 1, true, false);
}
}
for (List<Object> list : lists) {
int colunm = tplColunm;
Row row = sheet.createRow(rownum++);
for (Object val : list) {
this.addCell(row, colunm++, val, 2, Class.class);
}
}
}
}
/**
* 根据模板，按位置插入值
* 从startRow开始插入
*/
public void setListValueByTpl(String sheetName, List<Map<String, Object>> mapList, int startRow) {
Sheet fileSheet = getSheet(sheetName);
int rowNum = startRow;
for (Map<String, Object> map : mapList) {
Row row = fileSheet.getRow(rowNum);
if (row == null) {
row = fileSheet.createRow(rowNum);
}
for (Map.Entry<String, Object> entry : map.entrySet()) {
int column = Integer.parseInt(entry.getKey());
Object val = entry.getValue();
setCellValue(row, column, val);//赋值
}
rowNum++;
}
for (int i = startRow + mapList.size(); i <= fileSheet.getLastRowNum(); i++) {
Row row = fileSheet.getRow(i);
fileSheet.removeRow(row);
}
}
/**
* 根据坐标值赋值
*/
public void setCoordinate(String sheetName, Map<String, Object> coverMap) {
Sheet fileSheet = getSheet(sheetName);
for (Map.Entry<String, Object> entry : coverMap.entrySet()) {
String[] coordinate = entry.getKey().split("-");//获取坐标
Object val = entry.getValue();
int column = Integer.parseInt(coordinate[1]);
int rowNum = Integer.parseInt(coordinate[0]);
Row row = fileSheet.getRow(rowNum);
if (row == null) {
row = fileSheet.createRow(rowNum);
}
setCellValue(row, column, val);
}
}
private void setCellValue(Row row, int column, Object val) {
if (Objects.isNull(val)) return;
Cell cell = row.getCell(column);
if (cell == null) {
cell = row.createCell(column);
}
//赋值
if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
} else if (val instanceof Date) {
cell.setCellValue((Date) val);
} else if (val instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) val).doubleValue());
}
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = Maps.newHashMap();
CellStyle style = wb.createCellStyle();
style.setAlignment(CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBold(true);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CENTER);
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());//单元格背景颜色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);//单元格内字体颜色
headerFont.setColor(IndexedColors.BLACK.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val) {
return this.addCell(row, column, val, 0, Class.class);
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式（1：靠左；2：居中；3：靠右）
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
Cell cell = row.createCell(column);
String cellFormatString = "@";
try {
if (val == null) {
val = "";
} else if (fieldType != Class.class) {
cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
} else {
if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString = "0";
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString = "0";
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString = "0.00";
} else if (val instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) val).doubleValue());
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString = "0.00";
} else if (val instanceof Date) {
cell.setCellValue((Date) val);
cellFormatString = "yyyy-MM-dd HH:mm";
} else {
cell.setCellValue((String) Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype." + val.getClass().getSimpleName() + "Type")).getMethod("setValue", Object.class).invoke(null, val));
}
}
if (val != null) {
if (styles == null) this.styles = createStyles(wb);
CellStyle style = styles.get("data_column_" + column);
if (style == null) {
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
if (!Strings.isNullOrEmpty(cellFormatString)) {
style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
}
styles.put("data_column_" + column, style);
}
cell.setCellStyle(style);
}
} catch (Exception ex) {
cell.setCellValue(val.toString());
}
return cell;
}
/**
* 根据sheet名称获取sheet
*/
public Sheet getSheet(String sheetName) {
if (this.wb == null) {
this.wb = new XSSFWorkbook();
}
Sheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
sheet = wb.createSheet(sheetName);
}
return sheet;
}
/**
* 获取所有的sheetName
*/
public List<String> findAllSheetName() {
List<String> sheetNames = Lists.newArrayList();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
if (Objects.isNull(sheet)) break;
if (Strings.isNullOrEmpty(sheet.getSheetName())) continue;
sheetNames.add(sheet.getSheetName().trim());
}
return sheetNames;
}
/**
* 输出数据流
*
* @param os 输出数据流
*/
public ExportExcelUtil write(OutputStream os) throws IOException {
wb.write(os);
return this;
}
/**
* 输出到文件
*/
public ExportExcelUtil writeFile(String name) throws IOException {
FileOutputStream os = new FileOutputStream(name);
this.write(os);
return this;
}
/**
* 输出到文件
*/
public void createTpl(String filePath) {
try {
File f = new File(filePath); //写文件
//不存在则新增
if (!f.getParentFile().exists()) {
f.getParentFile().mkdirs();
}
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
out.flush();
wb.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 默认第一行为标题
*/
public List<Map<String, Object>> transform(List<ExcelColumnConfig> config, boolean removeHeader, String sheetName) {
Sheet sheet = getSheet(sheetName);
return transform(config, removeHeader ? 1 : 0, sheet);
}
/**
* 默认第一行为标题
*/
public <T> List<T> transform(List<ExcelColumnConfig> config, Class<T> clazz, String sheetName) {
Sheet sheet = getSheet(sheetName);
return transform(config, clazz, 1, sheet);
}
/**
* 用于多表头:skipHeaderCount>1
*/
public List<Map<String, Object>> transform(List<ExcelColumnConfig> config, int skipHeaderCount, int sheetIndex) {
Sheet sheet = this.wb.getSheetAt(sheetIndex);
return transform(config, skipHeaderCount, sheet);
}
/**
* 用于多表头:skipHeaderCount>1
*/
public <T> List<T> transform(List<ExcelColumnConfig> config, Class<T> clazz, int skipHeaderCount, int sheetIndex) {
Sheet sheet = this.wb.getSheetAt(sheetIndex);
return transform(config, clazz, skipHeaderCount, sheet);
}
}
package com.excel.app.utils;
import com.alibaba.fastjson.JSON;
import com.excel.app.utils.exception.BusinessException;
import com.excel.app.utils.exception.ErrorCode;
import com.fasterxml.jackson.annotation.JsonAnySetter;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.BiFunction;
import java.util.stream.Collectors;
import static com.excel.app.utils.Constants.KEY_EXCEL_COLUMN_SUFFIX;
/**
* 1、表头只有一行时，可通过名称匹配取列数据
* 2、多表头时，根据顺序取数据
* 所谓的取数据，就是把Excel转为list<?>中
*/
@Slf4j
public class BaseExcelTransformToList {
/**
* 生成Excel的行列描述
*/
List<ExcelColumnConfig> descColumns(Row titleRow, List<ExcelColumnConfig> configInput, String sheetName) {
List<ExcelColumnConfig> configs = findConfigByExcelColumnName(titleRow, configInput, 1, sheetName);
List<ExcelColumnConfig> result = Lists.newArrayList();
short cellCount = titleRow.getLastCellNum();
for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
ExcelColumnConfig column = getExcelColumnConfig(configs, cellIndex);
Cell cellVal = titleRow.getCell(cellIndex);
String title = "<空列>";
if (!Objects.isNull(cellVal) && !Strings.isNullOrEmpty(cellVal.getStringCellValue())) {
title = cellVal.toString();
}
column.setTitle(title);
result.add(column);
}
return result;
}
private ExcelColumnConfig getExcelColumnConfig(List<ExcelColumnConfig> configs, int finalCellIndex) {
Optional<ExcelColumnConfig> columnConfigOpt = configs.stream()
.filter(i -> !Objects.isNull(i.getExcelColumnOrder()) && i.getExcelColumnOrder() == finalCellIndex).findFirst();
return columnConfigOpt.orElseGet(ExcelColumnConfig::new);
}
/**
* 转换当前sheet为List<Map>数据源
*/
List<Map<String, Object>> transform(List<ExcelColumnConfig> config, int skipHeaderCount, Sheet sheet) {
config.forEach(i -> i.getMapper().getTransformResult().reset());
return handleRows(TO_MAP_LIST, config, skipHeaderCount, sheet);
}
/**
* 根据sheet转为List<T>数据源
*/
<T> List<T> transform(List<ExcelColumnConfig> columns, Class<T> clazz, int skipHeaderCount, Sheet sheet) {
columns.forEach(i -> i.getMapper().getTransformResult().reset());
BiFunction<Row, List<ExcelColumnConfig>, T> mapper = (row, configs) -> {
Map<String, Object> rowMap = toMap(row, configs);
return Objects.isNull(rowMap) ? null : Jsons.transform(rowMap, clazz);
};
return handleRows(mapper, columns, skipHeaderCount, sheet);
}
<T> List<T> handleRows(BiFunction<Row, List<ExcelColumnConfig>, T> mapper, List<ExcelColumnConfig> configInput,
int skipHeaderCount, Sheet sheet) {
List<T> result = Lists.newArrayList();
if (null == sheet) return result;
Row row;
// 默认第一行为标题
Row firstRow = sheet.getRow(0);
List<ExcelColumnConfig> config = findConfigByExcelColumnName(firstRow, configInput, skipHeaderCount, sheet.getSheetName());
for (int i = skipHeaderCount; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
if (Objects.isNull(row)) continue; //当前行有合并数据, 跳过
if (row.getLastCellNum() == -1) break; //已到最后一行
T rowData = mapper.apply(row, config);
if (!Objects.isNull(rowData)) result.add(rowData);
}
int skipCount = sheet.getLastRowNum() - result.size();
if (skipCount > 0) log.debug("转换完成, 共计跳过空行[{}]行", skipCount);
return result;
}
private static final BiFunction<Row, List<ExcelColumnConfig>, Map<String, Object>> TO_MAP_LIST = BaseExcelTransformToList::toMap;
private static Map<String, Object> toMap(Row row, List<ExcelColumnConfig> configs) {
Map<String, Object> rowMap = Maps.newHashMap();
for (int j = 0; j < configs.size(); j++) {
ExcelColumnConfig config = configs.get(j);
Object cellVal = null;
try {
Cell cell = row.getCell(config.getExcelColumnOrder());
String cellRawStrVal = Objects.toString(cell, null);
cellVal = config.getMapper().mapToProperty(cellRawStrVal, cell, row);
} catch (Exception ex) {
log.error("行[ {} ]列[ {} ]转换失败->[{}/{}]", row.getRowNum(), j, ex.getClass(), ex.getLocalizedMessage());
}
// 如果是字符串的，统一去掉前后空格
if (!Objects.isNull(cellVal) && cellVal instanceof String) {
rowMap.put(configs.get(j).getKey(), cellVal.toString().trim());
} else rowMap.put(configs.get(j).getKey(), cellVal);
}
return isAllNullMap(rowMap) ? null : rowMap;
}
/**
* 备注：目前只处理标题为一行
* 1、如果标题不提供或多表头，根据列头获取配置顺序，并表示与Excel列顺序 TODO 需要逐层判断获取标题头，并在程序里根据标题头控制
* 2、标题为一行是，根据标题名称获取取配置信息
*/
private List<ExcelColumnConfig> findConfigByExcelColumnName(Row row, List<ExcelColumnConfig> configs, int titleRows, String sheetName) {
if (1 != titleRows) {
AtomicInteger ao = new AtomicInteger(0);
return configs.stream().peek(i -> i.setExcelColumnOrder(ao.getAndIncrement())).collect(Collectors.toList());
}
List<ExcelColumnConfig> result = Lists.newArrayList();
for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
Cell cell = row.getCell(cellIndex);
String cellRawStrVal = Objects.toString(cell, null);
// 把前后空格去掉
cellRawStrVal = !Strings.isNullOrEmpty(cellRawStrVal) ? cellRawStrVal.trim() : null;
if (Strings.isNullOrEmpty(cellRawStrVal)) continue;
// FIXME:_Atomic 这里有点低效，没细看，总体为N*M循环
// 去掉后缀后再判断，避免模板存在后缀:(必填)
String finalCellRawStrVal = cellRawStrVal;
Optional<ExcelColumnConfig> columnConfigOpt = configs.stream()
.filter(i -> {
String dataLabel = StringUtils.substringBefore(i.getDataLabel(), KEY_EXCEL_COLUMN_SUFFIX);
String cellVal = StringUtils.substringBefore(finalCellRawStrVal, KEY_EXCEL_COLUMN_SUFFIX);
return dataLabel.equals(cellVal);
}).findFirst();
if (!columnConfigOpt.isPresent()) {
log.debug("{}-->[{}]未在配置中，跳过", sheetName, cellRawStrVal);
continue;
}
ExcelColumnConfig columnConfig = columnConfigOpt.get();
columnConfig.setExcelColumnOrder(cellIndex);
result.add(columnConfig);
}
Set<String> columnNames = result.stream().map(ExcelColumnConfig::getDataLabel).collect(Collectors.toSet());
if (columnNames.size() != result.size()) {
log.error("标题列名存在重复，不能完成操作");
throw BusinessException.withMessage(ErrorCode.ERR_10001, "标题列名存在重复，不能完成操作");
}
return result;
}
private static boolean isAllNullMap(Map<String, Object> row) {
Collection<Object> values = row.values();
return values.stream().allMatch(i -> Objects.isNull(i) || Strings.isNullOrEmpty(i.toString()));
}
}
源码结构图：
需要源码在评论区里留下邮箱地址
3512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
