apache poi excel 导入导出
水一篇 凑个数（啊 水文章好羞耻啊！请原谅我私密马赛！）
apache poi excel 导入导出，支持多 sheet 页导出、单元格自动合并、自定义导出和导入等。
全网最强，没有之一！
注：本文为 V2 版本，V1 版本已被覆盖！
文章目录
1 使用示例
1.1 导出
public void exportExcel(HttpServletResponse response) {
ObjectMapper objectMapper = new ObjectMapper(); // jackson 提供的 json 工具
try {
// 要导出的数据（listHero() 方法在下文）
List<HeroVo> list = this.listHero();
List<Map<String, Object>> data = objectMapper.convertValue(list, new TypeReference<>() {});
// 导出 其中参数二（buildColumn() 方法在下文） 参数四为行合并条件 参数五为列合并条件（当不需要合并时 四五传 null 即可）
ExcelUtils.exportExcel(response, this.buildColumn(), data, (upRow, downRow) -> {
if (CollectionUtils.isEmpty(upRow) || CollectionUtils.isEmpty(downRow)) {
return false;
}
return (Objects.equals(upRow.get("gender"), downRow.get("gender"))
&& Objects.equals(upRow.get("region"), downRow.get("region"))
&& Objects.equals(upRow.get("type"), downRow.get("type"))ß);
}, (row) -> true);
} catch (Exception e) {
LOGGER.error("Failed to export hero-data to excel!", e);
}
}
导出结果示例（只列举了部分导出结果，还支持更多行列组合合并）：
-
行合并和列合并同时存在时（即参数四和五都不为空且对应字段的 ExcelColumn 对象的 isRowMerge 和 isColumnMerge 都为 true 时）：
-
只有行合并时（即参数四不为空但参数五为空且对应字段的 ExcelColumn 对象的 isRowMerge 和 isColumnMerge 分别为 true 和 false 时）：
-
只有列合并时（即参数四为空但参数五不为空且对应字段的 ExcelColumn 对象的 isRowMerge 和 isColumnMerge 分别为 false 和 true 时）：
1.2 导入
public void importExcel(MultipartFile file) { // 导入就比较简单咯
List<HeroVo> list;
ObjectMapper objectMapper = new ObjectMapper();
try {
List<Map<String, Object>> maps = ExcelUtils.parseImportData(file, file.getOriginalFilename(), this.buildColumn(), null, null);
list = objectMapper.convertValue(maps, new TypeReference<>() {});
} catch (Exception e) {
LOGGER.error("Failed to import hero-excel!", e);
throw new GlobalException("Failed to import hero-excel!", e);
}
list.forEach(System.out::println);
}
1.3 实体类、buildColumn() 和 listHero() 定义
这三者在导入导出时可通用。
-
HeroVo
@Data @Builder @ToString @NoArgsConstructor @AllArgsConstructor public class HeroVo implements Serializable { @Serial private static final long serialVersionUID = -4766348996168128770L; private String title; // 称号 private String name; // 名称 private String gender; // 性别 private String region; // 地区 private String type; // 类型 private String passive; // 被动 private String q; // Q private String w; // W private String e; // E private String r; // R } -
buildColumn()
private List<ExcelColumn> buildColumn() { List<ExcelColumn> list = new ArrayList<>(); list.add(ExcelColumn.builder() .column("title") .columnName("称号") .width(5000) .build()); list.add(ExcelColumn.builder() .column("name") .columnName("名称") .width(5000) .build()); list.add(ExcelColumn.builder() .column("gender") .columnName("性别") .width(3000) .isRowMerge(true) .isColumnMerge(true) .build()); list.add(ExcelColumn.builder() .column("region") .columnName("地区") .width(5000) .isRowMerge(true) .isColumnMerge(true) .build()); list.add(ExcelColumn.builder() .column("type") .columnName("类型") .width(3000) .isRowMerge(true) .isColumnMerge(true) .build()); list.add(ExcelColumn.builder() .column("passive") .columnName("被动") .width(5000) .build()); list.add(ExcelColumn.builder() .column("q") .columnName("Q") .width(5000) .build()); list.add(ExcelColumn.builder() .column("w") .columnName("W") .width(5000) .build()); list.add(ExcelColumn.builder() .column("e") .columnName("E") .width(5000) .build()); list.add(ExcelColumn.builder() .column("r") .columnName("R") .width(5000) .build()); return list; } -
listHero()
private List<HeroVo> listHero() { List<HeroVo> list = new ArrayList<>(); list.add(HeroVo.builder() .title("影流之主") .name("劫") .gender("男") .region("艾欧尼亚") .type("刺客") .passive("影忍法！灭魂劫") .q("影奥义·诸刃") .w("影奥义·分身") .e("影奥义·鬼斩") .r("禁奥义·瞬狱影杀阵") .build()); list.add(HeroVo.builder() .title("影流之镰") .name("凯隐") .gender("男") .region("艾欧尼亚") .type("刺客") .passive("暗裔魔镰") .q("巨镰横扫") .w("利刃纵贯") .e("掠影步") .r("裂舍影") .build()); list.add(HeroVo.builder() .title("无极剑圣") .name("易") .gender("男") .region("艾欧尼亚") .type("刺客") .passive("双重打击") .q("阿尔法突袭") .w("冥想") .e("无极剑道") .r("高原血统") .build()); list.add(HeroVo.builder() .title("九尾妖狐") .name("阿狸") .gender("女") .region("艾欧尼亚") .type("刺客") .passive("摄魂夺魄") .q("欺诈宝珠") .w("妖异狐火") .e("魅惑妖术") .r("灵魂突袭") .build()); list.add(HeroVo.builder() .title("刀锋舞者") .name("艾瑞莉娅") .gender("女") .region("艾欧尼亚") .type("战士") .passive("艾欧尼亚热诚") .q("利刃冲击") .w("距破之舞") .e("比翼双刃") .r("先锋之刃") .build()); list.add(HeroVo.builder() .title("齐天大圣") .name("孙悟空") .gender("男") .region("艾欧尼亚") .type("战士") .passive("金刚不坏") .q("粉碎打击") .w("真假猴王") .e("腾云突击") .r("大闹天空") .build()); list.add(HeroVo.builder() .title("疾风剑豪") .name("亚索") .gender("男") .region("艾欧尼亚") .type("战士") .passive("浪客之道") .q("斩钢闪") .w("风之障壁") .e("踏前斩") .r("狂风绝息斩") .build()); list.add(HeroVo.builder() .title("盲僧") .name("李青") .gender("男") .region("艾欧尼亚") .type("战士") .passive("疾风骤雨") .q("天音波/回音击") .w("金钟罩/铁布衫") .e("天雷破/摧筋断骨") .r("猛龙摆尾") .build()); list.add(HeroVo.builder() .title("诡术妖姬") .name("乐芙兰") .gender("女") .region("诺克萨斯") .type("刺客") .passive("镜花水月") .q("恶意魔印") .w("魔影迷踪") .e("幻影锁链") .r("故技重施") .build()); list.add(HeroVo.builder() .title("不详之刃") .name("卡特琳娜") .gender("女") .region("诺克萨斯") .type("刺客") .passive("贪婪") .q("弹射之刃") .w("伺机待发") .e("瞬步") .r("死亡莲花") .build()); list.add(HeroVo.builder() .title("潮汐海灵") .name("菲兹") .gender("男") .region("比尔吉沃特") .type("刺客") .passive("伶俐斗士") .q("淘气打击") .w("海石三叉戟") .e("古灵精怪") .r("巨鲨来袭") .build()); list.add(HeroVo.builder() .title("放逐之刃") .name("锐雯") .gender("女") .region("诺克萨斯") .type("战士") .passive("符文之刃") .q("折翼之舞") .w("镇魂怒吼") .e("勇往直前") .r("放逐之锋") .build()); list.add(HeroVo.builder() .title("德玛西亚之力") .name("盖伦") .gender("男") .region("德玛西亚") .type("战士") .passive("坚韧") .q("致命打击") .w("勇气") .e("审判") .r("德玛西亚正义") .build()); list.add(HeroVo.builder() .title("德玛西亚皇子") .name("嘉文四世") .gender("男") .region("德玛西亚") .type("战士") .passive("战争律动") .q("巨龙撞击") .w("黄金圣盾") .e("德邦军旗") .r("天崩地裂") .build()); list.add(HeroVo.builder() .title("德邦总管") .name("赵信") .gender("男") .region("德玛西亚") .type("战士") .passive("果决") .q("三重爪击") .w("风斩电刺") .e("无畏冲锋") .r("新月护卫") .build()); return list; }
2 ExcelColumn
/**
* @author: ******
* @date: 2024/6/12 21:52
* @description: excel column
* 导入：需设置 column、columnName(可选)、columnNames(可选)、index(可选) 三个可选项任选其一
* 导出：需设置 column、columnName、width(可选)、isRowMerge(可选)、isColumnMerge(可选)
*
* 注：若有需要 则可添加 height(高度)、CellStyle(单元格样式)等更多属性
* 并在 {@link ExcelUtils#exportMultipleExcel(HttpServletResponse, String, String, List, int, BiPredicate, Predicate, FourConsumer)}
* 方法的相应位置进行设置
*/
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class ExcelColumn implements Serializable {
@Serial
private static final long serialVersionUID = -14668049202148498L;
/**
* 列对应实体属性名（驼峰名）
*/
private String column;
/**
* 列名（导入导出时使用）
* 导出：必须设置
* 导入：可选（一般情况下使用该字段）
*/
private String columnName;
/**
* 列名集合（导入时使用）
* 当多个 excel 或多个 sheet 中 "名称不同的列" 需要导入到同一个字段中时使用
*/
private List<String> columnNames;
/**
* 列序号（导入时使用）
* 当 excel 中存在相同名称的列时则可使用其对应列序号（别质疑 需求方真的给了这么一个模板）
*/
private Integer index;
/**
* 列宽（导出时使用）
* 默认值 3000
*/
private int width = 3000;
/**
* 是否为行合并
*/
private boolean isRowMerge = false;
/**
* 是否为列合并
*/
private boolean isColumnMerge = false;
}
3 MultipleSheetExcel
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class MultipleSheetExcel implements Serializable {
@Serial
private static final long serialVersionUID = -1469875755370531986L;
/**
* 标题
*/
private String titleName;
/**
* sheet 名称
*/
private String sheetName;
/**
* 列配置
*/
private List<ExcelColumn> columns;
/**
* 数据
*/
private List<Map<String, Object>> data;
}
4 ExcelExport
public enum ExcelTypeEnum {
XLS("xls"),
XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
5 ExcelImportType
public enum ExcelImportType {
// 可以定义特殊 excel 枚举类型 以便在导入时进行特殊处理（由具体业务或要导入的 excel 模版决定）
}
6 FourConsumer
@FunctionalInterface
public interface FourConsumer<A, B, C, D> {
/**
* 接收四个参数无返回值的操作 用来实现自定义导出
* @param a MultipleSheetExcel: 每个 sheet 对应的对象
* @param b Sheet: 当前要导出的 sheet
* @param c CellStyle: 全局单元格样式（若需自定义 则可无需此参数）
* @param d Boolean: 当前 sheet 页是否有标题影响 excel 第一行的渲染）
*/
void accept(A a, B b, C c, D d);
}
7 ExcelUtils
/**
* @author: ******
* @date: 2024/6/12 12:52
* @description: excel export/import
*
* 导出核心方法: exportMultipleExcel()
* HttpServletResponse response: http response
* String fileName: 文件名
* String excelType: xcel 类型（xls/xlsx）
* List<MultipleSheetExcel> list: 要导出的数据（多个 sheet）
* int dataSize: 总数据量
* BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition: 行合并条件（接收相邻两行数据）
* Predicate<Map<String, Object>> columnMergeCondition: 列合并条件（接收当前行数据）
* FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction:
* 自定义合并（接收当前 MultipleSheetExcel、Sheet、CellStyle、Boolean 为参），
* 总之 若以下逻辑不能满足您的需求 则尽情在 customMergeAction 接口中自定义实现。
*
* 导入核心方法: parseImportData()
* MultipartFile file: 导入的文件
* String fileName: 文件名
* List<ExcelColumn> columns: 对应列
* ExcelImportType type: 特殊类型 可根据此类型进行特殊处理（可为空）
* Map<String, Object> params: 对每行数据进行 补充（可为空）
*/
public class ExcelUtils {
private static final Log LOGGER = LogFactory.getLog(ExcelUtils.class);
public static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
public static final int MAX_ROW_2003 = 65536;
public static final int MAX_ROW_2007 = 1048576;
public static final String DEFAULT_SHEET = "sheet";
private static final Integer DEFAULT_COLUMN_WIDTH = 3000;
private static final String DEFAULT_CELL_VALUE= "";
/**
* export multiple sheet excel
* @param response
* @param columns
* @param data
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,
List<Map<String, Object>> data) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
exportExcel(response, generateName(), columns, data,
null, null, null);
}
/**
* export excel
* @param response
* @param fileName
* @param columns
* @param data
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelColumn> columns,
List<Map<String, Object>> data) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
if (!StringUtils.hasText(fileName)) {
fileName = generateName();
}
exportMultipleExcel(response, buildMultipleSheetExcel(columns, data, null), fileName,
null, null, null);
}
/**
* export multiple sheet excel
* @param response
* @param columns
* @param data
* @param rowMergeCondition
* @param columnMergeCondition
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns, List<Map<String, Object>> data,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
exportExcel(response, generateName(), columns, data, rowMergeCondition, columnMergeCondition, null);
}
/**
* export multiple sheet excel
* @param response
* @param fileName
* @param columns
* @param data
* @param rowMergeCondition
* @param columnMergeCondition
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelColumn> columns,
List<Map<String, Object>> data,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
if (!StringUtils.hasText(fileName)) {
fileName = generateName();
}
exportExcel(response, fileName, columns, data, rowMergeCondition, columnMergeCondition, null);
}
/**
* export excel
* @param response
* @param columns
* @param data
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns, List<Map<String, Object>> data,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
exportMultipleExcel(response, buildMultipleSheetExcel(columns, data, null), generateName(),
rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export excel
* @param response
* @param fileName
* @param columns
* @param data
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelColumn> columns,
List<Map<String, Object>> data,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
if (!StringUtils.hasText(fileName)) {
fileName = generateName();
}
exportMultipleExcel(response, buildMultipleSheetExcel(columns, data, null), fileName,
rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export excel
* @param response
* @param columns
* @param data
* @param excelType
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,
List<Map<String, Object>> data, String excelType,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
return;
}
List<MultipleSheetExcel> list = buildMultipleSheetExcel(columns, data, excelType);
exportMultipleExcel(response, generateName(), excelType, list, calculateSize(list),
rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export multiple sheet excel
* @param response
* @param list
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
exportMultipleExcel(response, list, generateName(), rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export multiple sheet excel
* @param response
* @param excelType
* @param list
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportMultipleExcel(HttpServletResponse response, String excelType, List<MultipleSheetExcel> list,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
exportMultipleExcel(response, generateName(), excelType, list, calculateSize(list),
rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export multiple sheet excel
* @param response
* @param list
* @param fileName
* @param rowMergeCondition
* @param columnMergeCondition
* @param customMergeAction
* @throws Exception
*/
public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list, String fileName,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction) throws Exception {
if (!StringUtils.hasText(fileName)) {
fileName = generateName();
}
exportMultipleExcel(response, fileName, ExcelTypeEnum.XLSX.getValue(), list, calculateSize(list),
rowMergeCondition, columnMergeCondition, customMergeAction);
}
/**
* export multiple sheet excel
* @param response http response
* @param fileName 文件名
* @param excelType excel 类型（xls/xlsx）
* @param list 要导出的数据（多个 sheet）
* @param dataSize 总数据量
* @param rowMergeCondition 行合并条件（接收相邻两行数据）
* @param columnMergeCondition 列合并条件（接收当前行数据）
* @param customMergeAction 自定义合并（接收当前 MultipleSheetExcel、Sheet、CellStyle、Boolean 为参）
* 总之 若以下逻辑不能满足您的需求 则尽情在 customMergeAction 接口中自定义实现
* @throws Exception
*/
public static void exportMultipleExcel(HttpServletResponse response, String fileName, String excelType,
List<MultipleSheetExcel> list, int dataSize,
BiPredicate<Map<String, Object>, Map<String, Object>> rowMergeCondition,
Predicate<Map<String, Object>> columnMergeCondition,
FourConsumer<MultipleSheetExcel, Sheet, CellStyle, Boolean> customMergeAction)
throws Exception {
// 创建工作簿
Workbook workbook = createWorkbook(excelType, dataSize);
// 定义全局单元格样式（若需自定义样式 则可在 ExcelColumn 类中定义 CellStyle 属性并进行配置）
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
String sheetName;
List<ExcelColumn> columns;
List<Map<String, Object>> data;
Sheet sheet;
Row title;
Cell titleCell;
Row heads;
boolean hasTitle = false;
for (MultipleSheetExcel sheetExcel : list) {
if (CollectionUtils.isEmpty(columns = sheetExcel.getColumns()) || CollectionUtils.isEmpty(data = sheetExcel.getData())) {
LOGGER.error("The columns or data cannot be empty!");
continue;
}
if (!StringUtils.hasText(sheetName = sheetExcel.getSheetName())) {
sheetName = DEFAULT_SHEET;
}
// 创建 sheet
sheet = workbook.createSheet(sheetName);
if (StringUtils.hasText(sheetExcel.getTitleName())) {
hasTitle = true;
}
// 处理标题行
if (hasTitle) {
title = sheet.createRow(0);
titleCell = title.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size()));
titleCell.setCellValue(sheetName);
CellStyle titleStyle = workbook.createCellStyle();
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleCell.setCellStyle(titleStyle);
heads = sheet.createRow(1);
} else {
heads = sheet.createRow(0);
}
// 定义列名样式
Cell headCell;
CellStyle headStyle = workbook.createCellStyle();
Font headFont = workbook.createFont();
headFont.setFontHeightInPoints((short) 12);
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);
// 处理列名行
int width;
ExcelColumn excelColumn;
for (int i = 0; i < columns.size(); i++) {
excelColumn = columns.get(i);
if ((width = excelColumn.getWidth()) <= 0) {
width = DEFAULT_COLUMN_WIDTH;
}
headCell = heads.createCell(i);
headCell.setCellValue(excelColumn.getColumnName());
headCell.setCellStyle(headStyle);
sheet.setColumnWidth(i, width);
}
// 若存在自定义合并 则直接调用自定义逻辑
if (customMergeAction != null) {
customMergeAction.accept(sheetExcel, sheet, dataStyle, hasTitle);
continue;
}
int size = data.size(), firstRow = 0, lastRow, firstColumn = -1;
boolean isRowMerge, isColumnMerge;
Row row;
Cell dataCell;
Object value;
ExcelColumn column;
Map<String, Object> map;
for (int i = 0; i < size; i++) {
map = data.get(i);
if (hasTitle) {
row = sheet.createRow(i + 2);
} else {
row = sheet.createRow(i + 1);
}
// 计算 firstRow、lastRow
if ((i + 1) < size && rowMergeCondition != null) {
isRowMerge = rowMergeCondition.test(map, data.get(i + 1));
} else {
isRowMerge = false;
}
if (firstRow < 1) {
firstRow = row.getRowNum();
}
lastRow = row.getRowNum();
// 处理当前行数据
for (int j = 0; j < columns.size(); j++) {
column = columns.get(j);
if (ObjectUtils.isEmpty(value = map.get(column.getColumn()))) {
value = DEFAULT_CELL_VALUE;
}
// 计算 firstColumn
if (column.isColumnMerge()) {
if (columnMergeCondition != null) {
isColumnMerge = columnMergeCondition.test(map);
} else {
isColumnMerge = false;
}
} else {
isColumnMerge = false;
}
if (column.isColumnMerge() && isColumnMerge && firstColumn < 0) {
firstColumn = j;
}
// 处理单元格合并
if (!isRowMerge && !isColumnMerge) {
// 行列合并、列合并
if (firstRow <= lastRow && firstColumn > -1 && (j - firstColumn) > 1) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, j - 1));
firstColumn = -1;
}
// 行合并
else if (column.isRowMerge() && firstRow < lastRow) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, j, j));
}
}
dataCell = row.createCell(j);
dataCell.setCellValue(String.valueOf(value));
dataCell.setCellStyle(dataStyle);
}
if (!isRowMerge && (i + 1) < size) {
firstRow = row.getRowNum() + 1;
}
}
hasTitle = false;
}
downloadExcel(response, fileName, excelType, workbook);
}
/**
* download
* @param response
* @param fileName
* @param excelType
* @param workbook
* @throws IOException
*/
public static void downloadExcel(HttpServletResponse response, String fileName, String excelType, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName + "." + excelType, StandardCharsets.UTF_8));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* create workbook
* @param excelType
* @param size
* @return
*/
private static Workbook createWorkbook(String excelType, int size) {
if (ExcelTypeEnum.XLS.getValue().equals(excelType)) {
return new HSSFWorkbook();
} else if (size < 100000) {
return new XSSFWorkbook();
} else {
return new SXSSFWorkbook();
}
}
/**
* build MultipleSheetExcel
* @param columns
* @param data
* @param excelType
* @return
*/
private static List<MultipleSheetExcel> buildMultipleSheetExcel(List<ExcelColumn> columns,
List<Map<String, Object>> data, String excelType) {
int maxSize;
if (StringUtils.hasText(excelType) && ExcelTypeEnum.XLS.getValue().equals(excelType)) {
maxSize = MAX_ROW_2003;
} else {
maxSize = MAX_ROW_2007;
}
int sheets;
if ((data.size() % maxSize) > 0) {
sheets = (data.size() / maxSize) + 1;
} else {
sheets = data.size() / maxSize;
}
int remainNumber = data.size(), fromIndex, toIndex;
List<MultipleSheetExcel> list = new ArrayList<>(sheets);
for (int i = 0; i < sheets; i++) {
fromIndex = i * maxSize;
toIndex = fromIndex + Math.min(remainNumber, maxSize);
list.add(MultipleSheetExcel.builder()
.sheetName(DEFAULT_SHEET + (i + 1))
.columns(columns)
.data(data.subList(fromIndex, toIndex))
.build());
remainNumber = remainNumber - (toIndex - fromIndex);
}
return list;
}
/**
* calculate data size
* @param list
* @return
*/
private static int calculateSize(List<MultipleSheetExcel> list) {
int size = 0;
List<Map<String, Object>> data;
for (MultipleSheetExcel sheetExcel : list) {
if (CollectionUtils.isEmpty(data = sheetExcel.getData())) {
continue;
}
size += data.size();
}
return size;
}
/**
* generate name
* @return
*/
private static String generateName() {
LocalDateTime now = LocalDateTime.now();
return DATE_TIME_FORMATTER.format(now);
}
/* export end */
/* import start */
/**
* parse import data
* @param file 导入的文件
* @param fileName 文件名
* @param columns 对应列
* @param type 特殊类型 可根据此类型进行特殊处理（可为空）
* @param params 对每行数据进行 补充（可为空）
* @return
*/
public static List<Map<String, Object>> parseImportData(MultipartFile file, String fileName, List<ExcelColumn> columns,
ExcelImportType type, Map<String, Object> params) {
Workbook workbook;
try (InputStream inputStream = file.getInputStream()) {
if (ExcelUtils.isXlsExcelFile(fileName)) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}
} catch (Exception e) {
throw new GlobalException("导入失败！", e);
}
DataFormatter formatter = new DataFormatter();
Sheet sheet;
Iterator<Sheet> sheetIterator = workbook.sheetIterator();
Map<String, Object> map;
List<Map<String, Object>> objects = new ArrayList<>();
while (sheetIterator.hasNext()) {
sheet = sheetIterator.next();
if (sheet.getLastRowNum() < 2) {
continue;
}
Row row;
boolean hasValue = false;
Map<Integer, String> heads = null;
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
row = rowIterator.next();
Cell cell;
Iterator<Cell> cellIterator = row.cellIterator();
if (row.getRowNum() == 0) {
heads = new HashMap<>();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
heads.put(cell.getColumnIndex(), cell.getStringCellValue());
}
continue;
}
String column;
Object cellValue;
map = new HashMap<>();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
if (!StringUtils.hasText(column = determineColumn(columns, heads, cell.getColumnIndex()))) {
continue;
}
if (isMergedCell(sheet, cell)) {
cellValue = getMergedCellValue(sheet, cell, formatter);
} else {
cellValue = formatter.formatCellValue(cell);
}
if (!ObjectUtils.isEmpty(cellValue)) {
hasValue = true;
}
map.put(column, cellValue);
}
if (!hasValue) {
continue;
}
if (!ObjectUtils.isEmpty(type)) {
// 针对特殊类型 excel 进行特殊处理 如计算合并等
}
// 对每行数据进行补充
if (!CollectionUtils.isEmpty(params)) {
String key;
Object value;
for (Map.Entry<String, Object> entry : params.entrySet()) {
if (!StringUtils.hasText(key = entry.getKey()) || ObjectUtils.isEmpty(value = entry.getValue())) {
continue;
}
map.put(key, value);
}
}
objects.add(map);
hasValue = false;
}
}
return objects;
}
/**
* get merged cell value
* @param sheet
* @param cell
* @param formatter
* @return
*/
private static Object getMergedCellValue(Sheet sheet, Cell cell, DataFormatter formatter) {
Row firstRow;
Cell firstCell;
for (CellRangeAddress region : sheet.getMergedRegions()) {
if (!region.isInRange(cell)) {
continue;
}
firstRow = sheet.getRow(region.getFirstRow());
firstCell = firstRow.getCell(region.getFirstColumn());
return formatter.formatCellValue(firstCell);
}
return null;
}
/**
* determine merge cell
* @param sheet
* @param cell
* @return
*/
private static boolean isMergedCell(Sheet sheet, Cell cell) {
for (CellRangeAddress region : sheet.getMergedRegions()) {
if (region.isInRange(cell)) {
return true;
}
}
return false;
}
/**
* determine column
* @param columns
* @param heads
* @param columnIndex
* @return
*/
private static String determineColumn(List<ExcelColumn> columns, Map<Integer, String> heads, int columnIndex) {
String column, columnName;
List<String> columnNames;
for (ExcelColumn excelColumn : columns) {
if (!StringUtils.hasText(column = excelColumn.getColumn())) {
continue;
}
if ((!ObjectUtils.isEmpty(excelColumn.getIndex()) && columnIndex == excelColumn.getIndex())
|| (StringUtils.hasText(columnName = excelColumn.getColumnName()) && !CollectionUtils.isEmpty(heads)
&& columnName.equals(heads.get(columnIndex)))
|| (!CollectionUtils.isEmpty(columnNames = excelColumn.getColumnNames()) && !CollectionUtils.isEmpty(heads)
&& columnNames.contains(heads.get(columnIndex)))) {
return column;
}
}
return null;
}
/* import end */
/**
* is excel
* @param fileName
* @return
*/
public static boolean isExcelFile(String fileName) {
Pattern pattern = Pattern.compile("\\.(xls|xlsx|xlsm)$");
Matcher matcher = pattern.matcher(fileName);
return matcher.find();
}
/**
* is xls excel
* @param fileName
* @return
*/
public static boolean isXlsExcelFile(String fileName) {
Pattern pattern = Pattern.compile("\\.(xls)$");
Matcher matcher = pattern.matcher(fileName);
return matcher.find();
}
}
毁灭吧！！！
扫一扫
1887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
