导入POI的包

1
2
3
4
5
6
7
8
9
10
11
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

创建excel写入工厂类-ExcelWriterBuilder(核心)

1.创建ExcelWriterBuilder对象(有参、无参)

2.创建Sheet方法(buildSheet)

3.导入list对象数据(importDataToExcel)

4.将数据持久化到本地(createExcelToLocal)或者将数据写入到下载的返回(buildDownLoadExcel)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
package com.reptile.sanction.factory;

import com.reptile.sanction.annotation.ExcelCellTitle;
import com.reptile.sanction.entity.ImportExcel;
import com.reptile.sanction.util.WorkSheetBuilderUtil;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ObjectUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

public class ExcelWriterBuilder {

    /**
     * work book
     */
    private Workbook writeWorkbook;

    /**
     * work sheet
     */
    private Sheet writeSheet;

    /**
     * excel file
     */
    private File file;

    /**
     * excel 文件名称
     */
    private String fileName;

    /**
     * 创建excel title
     * @param tClass
     */
    private void createExcelHeader(Class tClass) {
        CellStyle headerStyle = WorkSheetBuilderUtil.buildHeadCellStyle(this.writeWorkbook);
        List<String> titles = getCellHeader(tClass);
        Row row = WorkSheetBuilderUtil.createRow(this.writeSheet, 0);
        for (int i=0; i < titles.size(); i++) {
            WorkSheetBuilderUtil.createCell(row, i, headerStyle).setCellValue(titles.get(i));
        }
    }

    /**
     * 填充数据到Excel表格
     * @param writeTable 数据
     */
    private void addContent(List<? extends ImportExcel> writeTable) {
        int rowIndex = 1;
        for (ImportExcel dto : writeTable) {
            Row row = WorkSheetBuilderUtil.createRow(this.writeSheet, rowIndex);
            dto.addOneRowOfDataToExcel(row);
            rowIndex++;
        }
    }

    /**
     * 获取excel titile
     * @param tClass
     * @return
     */
    private List<String> getCellHeader(Class<T> tClass) {
        List<String> cellTitle = Arrays.stream(tClass.getDeclaredFields())
                .sorted(Comparator.comparing(field -> field.getAnnotation(ExcelCellTitle.class).order()))
                .map(field -> field.getAnnotation(ExcelCellTitle.class).title())
                .collect(Collectors.toList());
        return cellTitle;
    }

    /**
     * 创建excel文件到本地目录
     * @throws IOException
     */
    public void createExcelToLocal() throws IOException {
        FileOutputStream fileOut = null;

        try {
            fileOut = new FileOutputStream(this.fileName);
            this.writeWorkbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (!ObjectUtils.isEmpty(fileOut)) {
                fileOut.close();
            }
        }
    }

    /**
     * 构造函数， 创建work boot
     */
    public ExcelWriterBuilder() {
        this.writeWorkbook = new XSSFWorkbook();
    }

    /**
     * 构造函数
     * @param fileName 文件名称
     */
    public ExcelWriterBuilder(String fileName) {
        this();
        this.fileName = fileName;
    }

    /**
     * 创建excel sheet 文件
     * @param sheetName sheet name
     * @return
     */
    public ExcelWriterBuilder buildSheet(String sheetName) {
        this.writeSheet = this.writeWorkbook.createSheet(sheetName);
        return this;
    }

    /**
     * 将数据导入到excel
     * @param writeTable
     * @param tClass
     */
    public void importDataToExcel(List<? extends ImportExcel> writeTable, Class tClass) {
        this.createExcelHeader(tClass);
        this.addContent(writeTable);
    }


}

Sheet标准工具类-WorkSheetBuilderUtil

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package com.reptile.sanction.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;

public class WorkSheetBuilderUtil {

    private static void setCellBorder(CellStyle cellStyle) {
        //边框颜色和宽度设置
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
        cellStyle.setBorderTop(BorderStyle.THIN);// 设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
    }

    private static void setCellBackground(CellStyle cellStyle) {
        //设置背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }

    private static void setCellFont(CellStyle cellStyle, Workbook writeWorkbook) {
        //粗体字设置
        Font font = writeWorkbook.createFont();
        cellStyle.setFont(font);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
    }

    public static Row createRow(Sheet sheet, int rowNum) {
        sheet.setColumnWidth(0, 20*256);
        sheet.setColumnWidth(1, 15*256);
        sheet.setColumnWidth(2, 15*256);
        sheet.setColumnWidth(3, 15*256);
        sheet.setColumnWidth(4, 15*256);
        sheet.setColumnWidth(5, 15*256);
        sheet.setColumnWidth(6, 15*256);
        sheet.setColumnWidth(7, 15*256);
        sheet.setColumnWidth(8, 20*256);
        sheet.setColumnWidth(9, 30*256);
        sheet.setColumnWidth(10, 30*256);
        sheet.setColumnWidth(11, 15*256);
        sheet.setColumnWidth(12, 30*256);
        sheet.setColumnWidth(13, 30*256);
        sheet.setColumnWidth(14, 35*256);
        sheet.setColumnWidth(15, 15*256);
        sheet.setColumnWidth(16, 15*256);
        sheet.setColumnWidth(17, 200*256);
        return sheet.createRow(rowNum);
    }

    public static Cell createCell(Row row, int colNum) {
        Cell cell = row.createCell(colNum);
        return cell;
    }

    public static Cell createCell(Row row, int colNum, CellStyle cellStyle) {
        Cell cell = row.createCell(colNum);
        cell.setCellStyle(cellStyle);
        return cell;
    }

    public static CellStyle buildHeadCellStyle(Workbook writeWorkbook) {
        CellStyle style = writeWorkbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(CellStyle.ALIGN_CENTER);
        setCellBorder(style);
        setCellBackground(style);
        setCellFont(style, writeWorkbook);
        return style;
    }
}

Excel标题描述和排序注解类-ExcelCellTitle

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.reptile.sanction.annotation;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCellTitle {

    //默认值
    String value() default "";

    //excel中的序号
    int order() default 0;

    //表头
    String title() default "";
}

Excel导入对象类标准接口-ImportExcel

1
2
3
4
5
6
7
8
9
10
11
12
13
package com.reptile.sanction.entity;

import org.apache.poi.ss.usermodel.Row;

public interface ImportExcel {

    /**
     * 添加数据到EXCEL中的row中
     * @param row
     */
    void addOneRowOfDataToExcel(Row row);
}

Excel导入数据集合的对象类示例-NameDetail

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
package com.reptile.sanction.entity;

import com.reptile.sanction.annotation.ExcelCellTitle;
import com.reptile.sanction.util.WorkSheetBuilderUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.Row;

@Data
public class NameDetail implements ImportExcel{
    //制裁类型
    @ExcelCellTitle(order = 1, title = "制裁类型")
    private String sanctionType;

    //名称1
    @ExcelCellTitle(order = 1, title = "名称1")
    private String nameOne;

    //名称2
    @ExcelCellTitle(order = 1, title = "名称2")
    private String nameTwo;

    //名称3
    @ExcelCellTitle(order = 1, title = "名称3")
    private String nameThree;

    //名称4
    @ExcelCellTitle(order = 1, title = "名称4")
    private String nameForth;

    //职称
    @ExcelCellTitle(order = 1, title = "职称")
    private String jobName;

    //头衔
    @ExcelCellTitle(order = 1, title = "头衔")
    private String title;

    //出生日期
    @ExcelCellTitle(order = 1, title = "出生日期")
    private String birthDate;

    //出生地点
    @ExcelCellTitle(order = 1, title = "出生地点")
    private String birthAddress;

    //足够确认身份的别名
    @ExcelCellTitle(order = 1, title = "足够确认身份的别名")
    private String sufficientlyIdentifyingAliases;

    //不足够确认身份的别名
    @ExcelCellTitle(order = 1, title = "不足够确认身份的别名")
    private String insufficientlyIdentifyingAliases;

    //国籍
    @ExcelCellTitle(order = 1, title = "国籍")
    private String national;

    //护照编号
    @ExcelCellTitle(order = 1, title = "护照编号")
    private String passportNumber;

    //国内身份证编号
    @ExcelCellTitle(order = 1, title = "国内身份证编号")
    private String identifyNumber;

    //地址
    @ExcelCellTitle(order = 1, title = "地址")
    private String address;

    //列入黑名单日期
    @ExcelCellTitle(order = 1, title = "列入黑名单日期")
    private String blackDate;

    //修正日期
    @ExcelCellTitle(order = 1, title = "修正日期")
    private String modifyDate;

    //其他信息
    @ExcelCellTitle(order = 1, title = "其他信息")
    private String otherInfo;

    @Override
    public void addOneRowOfDataToExcel(Row row) {
        WorkSheetBuilderUtil.createCell(row,0).setCellValue(this.getSanctionType());
        WorkSheetBuilderUtil.createCell(row,1).setCellValue(this.getNameOne());
        WorkSheetBuilderUtil.createCell(row,2).setCellValue(this.getNameTwo());
        WorkSheetBuilderUtil.createCell(row,3).setCellValue(this.getNameThree());
        WorkSheetBuilderUtil.createCell(row,4).setCellValue(this.getNameForth());
        WorkSheetBuilderUtil.createCell(row,5).setCellValue(this.getJobName());
        WorkSheetBuilderUtil.createCell(row,6).setCellValue(this.getTitle());
        WorkSheetBuilderUtil.createCell(row,7).setCellValue(this.getBirthDate());
        WorkSheetBuilderUtil.createCell(row,8).setCellValue(this.getBirthAddress());
        WorkSheetBuilderUtil.createCell(row,9).setCellValue(this.getSufficientlyIdentifyingAliases());
        WorkSheetBuilderUtil.createCell(row,10).setCellValue(this.getInsufficientlyIdentifyingAliases());
        WorkSheetBuilderUtil.createCell(row,11).setCellValue(this.getNational());
        WorkSheetBuilderUtil.createCell(row,12).setCellValue(this.getPassportNumber());
        WorkSheetBuilderUtil.createCell(row,13).setCellValue(this.getIdentifyNumber());
        WorkSheetBuilderUtil.createCell(row,14).setCellValue(this.getAddress());
        WorkSheetBuilderUtil.createCell(row,15).setCellValue(this.getBlackDate());
        WorkSheetBuilderUtil.createCell(row,16).setCellValue(this.getModifyDate());
        WorkSheetBuilderUtil.createCell(row,17).setCellValue(this.getOtherInfo());
    }
}

使用方法示例-将数据写入excel,并将excel持久化到本地

1
2
3
ExcelWriterBuilder builderUtil = new ExcelWriterBuilder(this.getClass().getResource("/").getPath()+"联合国安全理事会的综合制裁名单.xlsx");
builderUtil.buildSheet("sheet1").importDataToExcel(nameDetails, NameDetail.class);
builderUtil.createExcelToLocal();

使用方法示例-将数据写入excel,并将excel放入到response

1
2
3
ExcelWriterBuilderUtil builderUtil = new ExcelWriterBuilderUtil("反馈解析结果记录.xlsx");
builderUtil.buildSheet("sheet1").importDataToExcel(reportParseResultDTOS, ReportParseResultDTO.class);
builderUtil.buildDownLoadExcel(response);

工具类方法一

java解析excel通过表头,映射到具体的java实体对象,可以使用该工具类方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
package com.geping.etl.utils;

import com.geping.etl.east2.exception.BusinessEnum;
import com.geping.etl.east2.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelUtil {

    public static <T> List<T> readExcelForObject(Workbook wb, Class<T> clazz, Map<String, String> titleMappingCode) {
        //excel处理第一页
        Sheet sheetAt = wb.getSheetAt(0);
        //第一行为标题行
        Row header = sheetAt.getRow(0);

        //fieldName->row.title->index
        int[] fieldMapRow = new int[header.getLastCellNum()+1];
        Field[] fields = clazz.getDeclaredFields();
        for(int i=header.getFirstCellNum(); i< header.getLastCellNum(); i++){
            boolean mappingFlag = false;
            String value = getStringByCell(header.getCell(i), wb);
            for(int j=0; j<fields.length; j++) {
                if (titleMappingCode.get(value) != null &&
                        titleMappingCode.get(value).equals(fields[j].getName())) {
                    fieldMapRow[i] = j;
                    mappingFlag = true;
                }
            }
            if (!mappingFlag) {
                log.error("field not match excel title, cellValue = {}, mappingCode = {}", value, titleMappingCode.get(value));
                throw new BusinessException(BusinessEnum.ExcelMappingError);
            }
        }
        ArrayList<T> list = new ArrayList<>();
        try {
            for(int i=1; i<=sheetAt.getLastRowNum(); i++){
                T instance = clazz.newInstance();
                for(int j=0; j<header.getLastCellNum(); j++){
                    String value = getStringByCell( sheetAt.getRow(i).getCell(j), wb);
                    fields[fieldMapRow[j]].setAccessible(true);
                    fields[fieldMapRow[j]].set(instance, value);
                }
                list.add(instance);
            }
        } catch (Exception e) {
            log.error("field not match excel title, e = {}", e);
            throw new BusinessException(BusinessEnum.ExcelMappingError);
        }

        return list;
    }


    public static String getStringByCell(Cell cell, Workbook wb){
        DataFormatter objDefaultFormat = new DataFormatter();
        FormulaEvaluator objFormulaEvaluator = null;
        if(wb instanceof  HSSFWorkbook) {
            objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
        }else{
            objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
        }

        objFormulaEvaluator.evaluate(cell);
        return objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
    }
}

工具类方法二

工具类方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
package com.geping.etl.utils;

import com.geping.etl.east2.exception.BusinessEnum;
import com.geping.etl.east2.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
public class ExcelUtil {

    /**
     * @description: 支持并发写入，data类型为map，data和header的key一致写入一列，excel列顺序按header的顺序输出
     * @params: [wb-excel对象, dataList-数据集合, header-表头映射关系, startNo-起始编号，为0则会写入表头]
     * @return: void
     * @date: 2023/2/22
     */
    public static void writeExcelForMap(Workbook wb, List<Map<String,Object>> dataList, Map<String, String> header, int startNo) {
        Sheet sheetAt = wb.getSheetAt(0);
        int size = startNo==0?dataList.size()+1:dataList.size();
        for(int i=0; i<size; i++) {
            Row dataRow = sheetAt.createRow(startNo+i);
            int column = 0;
            if(startNo == 0 && i ==0){
                for(Map.Entry<String,String> entry: header.entrySet()){
                    String cellValue = entry.getValue();
                    Cell cell = dataRow.createCell(column);
                    cell.setCellValue(cellValue);
                    column++;
                }
            }else {
                int index = i;
                if(startNo == 0){
                    index--;
                }
                for (Map.Entry<String, String> entry : header.entrySet()) {
                    String cellValue = dataList.get(index).get(entry.getKey()) != null ? dataList.get(index).get(entry.getKey()).toString() : "";
                    Cell cell = dataRow.createCell(column);
                    cell.setCellValue(cellValue);
                    column++;
                }
            }
        }
    }

    /**
     * @description: 支持并发写入，data类型为map，data的field和header的key一致写入一列，excel列顺序按header的顺序输出和行顺序按dataList的顺序输出
     * @params: [wb-excel对象, dataList-数据集合, header-表头映射关系, startNo-起始编号，为0则会写入表头, clazz-写入对象类型]
     * @return: void
     * @date: 2023/2/22
     */
    public static <T> void writeExcelForObject(SXSSFWorkbook wb, List<T> dataList, Map<String, String> header, int startNo, Class<T> clazz) {
        Sheet sheetAt = wb.getSheetAt(0);
        int size = startNo==0?dataList.size()+1:dataList.size();
        for(int i=0; i<size; i++) {
            Row dataRow = sheetAt.createRow(startNo + i);
            int column = 0;
            if (startNo == 0 && i == 0) {
                for (Map.Entry<String, String> entry : header.entrySet()) {
                    String cellValue = entry.getValue();
                    Cell cell = dataRow.createCell(column);
                    cell.setCellValue(cellValue);
                    column++;
                }
            } else {
                int index = i;
                if (startNo == 0) {
                    index--;
                }
                try {
                    for (Map.Entry<String, String> entry : header.entrySet()) {
                        for (Field field : clazz.getDeclaredFields()) {
                            if (field.getName().equals(entry.getKey())) {
                                field.setAccessible(true);
                                String cellValue = field.get(dataList.get(index))!=null?field.get(dataList.get(index)).toString():"";
                                Cell cell = dataRow.createCell(column);
                                cell.setCellValue(cellValue);
                                column++;
                                break;
                            }
                        }
                    }
                } catch (Exception e) {
                    log.error("writeExcelForObject write excel error, e = {}", e);
                    throw new BusinessException(BusinessEnum.ExcelMappingError);
                }
            }
        }
    }

    public static <T> List<T> readExcelForObject(Workbook wb, Class<T> clazz, Map<String, String> titleMappingCode) {
        //excel处理第一页
        Sheet sheetAt = wb.getSheetAt(0);
        //第一行为标题行
        Row header = sheetAt.getRow(0);

        //fieldName->row.title->index
        int[] fieldMapRow = new int[header.getLastCellNum()+1];
        Field[] fields = clazz.getDeclaredFields();
        for(int i=header.getFirstCellNum(); i< header.getLastCellNum(); i++){
            boolean mappingFlag = false;
            String value = getStringByCell(header.getCell(i), wb);
            for(int j=0; j<fields.length; j++) {
                if (titleMappingCode.get(value) != null &&
                        titleMappingCode.get(value).equals(fields[j].getName())) {
                    fieldMapRow[i] = j;
                    mappingFlag = true;
                }
            }
            if (!mappingFlag) {
                log.error("field not match excel title, cellValue = {}, mappingCode = {}", value, titleMappingCode.get(value));
                throw new BusinessException(BusinessEnum.ExcelMappingError);
            }
        }
        ArrayList<T> list = new ArrayList<>();
        try {
            for(int i=1; i<=sheetAt.getLastRowNum(); i++){
                T instance = clazz.newInstance();
                for(int j=0; j<header.getLastCellNum(); j++){
                    String value = getStringByCell( sheetAt.getRow(i).getCell(j), wb);
                    fields[fieldMapRow[j]].setAccessible(true);
                    fields[fieldMapRow[j]].set(instance, value);
                }
                list.add(instance);
            }
        } catch (Exception e) {
            log.error("field not match excel title, e = {}", e);
            throw new BusinessException(BusinessEnum.ExcelMappingError);
        }

        return list;
    }


    public static String getStringByCell(Cell cell, Workbook wb){
        DataFormatter objDefaultFormat = new DataFormatter();
        FormulaEvaluator objFormulaEvaluator = null;
        if(wb instanceof  HSSFWorkbook) {
            objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
        }else{
            objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
        }

        objFormulaEvaluator.evaluate(cell);
        return objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
    }


}


表头映射例子如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.geping.etl.utils;

import java.util.HashMap;
import java.util.Map;

public interface ExcelTitleMappingField {
    Map<String, String> BX_TEMPLATE_FILE = new HashMap(){
        {
            put("id","templateId");
            put("job_id", "jobId");
            put("rule_id", "ruleId");
            put("rule_desc", "ruleDesc");
            put("data_date", "dateDate");
            put("org_no", "orgNo");
            put("level", "level1");
            put("tab_name1", "tabName1");
            put("tab_name2", "tabName2");
            put("table_ch_name", "tabChName");
            put("pk_name", "pkName");
            put("col_name", "colName");
            put("上季度问题数据量", "lastQuarterProblemData");
            put("上季度数据总量", "lastQuarterData");
            put("上季度问题占比", "lastQuarterProblemPercent");
            put("本季度问题数据量", "quarterProblemData");
            put("本季度数据总量", "quarterData");
            put("本季度问题占比", "quarterProblemPercent");
            put("本季度问题数据量-上季度问题数据量", "quarterProblemAdd");
            put("（本季度问题数据量-上季度问题数据量）/上季度问题数据量", "quarterProblemAddpercent");
            put("except_sample", "exceptSample");
            put("bank_name", "bankName");
            put("机构简称", "bankShortName");
            put("importDate", "importDate");
            put("reason", "reason");
            put("reasonName", "reasonName");
            put("reasonTime", "reasonTime");
            put("dataStatus", "dataStatus");
            put("sqlRecord", "sqlRecord");
        }
    };
}

excel文件类型格式xls和xlsx加载的区别

加载流对象的使用,xlsx使用的是XssF,xls使用的是HssF.获取单元值的时候,也是一样的.如图: