Excel表格导入/导出数据工具类

Excel表格导入/导出数据工具

这里以java语言为类，实现一个简单且较通用的Excel表格数据导入工具类。

1. 自定义注解 ExcelColumn
2. 写导入工具类 ExcelImportUtil

自定义注解 ExcelColumn

@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelColumn {

    /**
     * 字段的含义
     */
    String name();
    /**
     * 配置列的名称,对应A,B,C,D....
     */
    String column();
    /**
     * 是否导出数据
     */
    boolean isExport() default true;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

定义数据类

/**
 * 数据对象
 * @author
 */
@Getter
@Setter
@ToString
public class DataDTO implements Serializable {

    @ExcelColumn(name = "*XXX", column = "A")
    private String a;
    
    @ExcelColumn(name = "xxx", column = "B")
    private String b;
    
    @ExcelColumn(name = "xxx", column = "C")
    private String c;
    // 导入时记录失败原因的字段  按需取
    private String errorMsg;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Excel表格导入导出工具类代码:

@Slf4j
public class ExcelCustomUtil<T> {

    private static final String SUFFIX = "XLS";
    protected Class<T> clazz;

    public ExcelWithPicUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 批量导入， 每个sheet 数据格式一致
     * @param input
     * @param ignore 忽略前多少行,也就是从哪行开始读取数据
     * @return
     * @throws Exception
     */
    public List<T> importBatch(InputStream input, Integer ignore, String suffix) throws Exception {
        List<T> newList = new ArrayList<>();
        Workbook workbook;
        if (SUFFIX.equalsIgnoreCase(suffix)) {
            workbook = new HSSFWorkbook(input);
        } else {
            workbook = new XSSFWorkbook(input);
        }

        Sheet sheet = workbook.getSheetAt(0);
        if(null != sheet){
            List<T> importProcessor = importProcessor(sheet, ignore);
            newList.addAll(importProcessor);
        }

        return newList;
    }

    /**
     * @param sheet
     * @param ignoreRow, 忽略前多少行
     * @return
     * @throws Exception
     */

    protected List<T> importProcessor(Sheet sheet, Integer ignoreRow) throws Exception {
        log.info("执行导入操作");
        int maxCol = 0;
        List<T> list = new ArrayList<T>();
        int rows = sheet.getPhysicalNumberOfRows();
        log.info("importProcessor rows:{}", rows);
        if (rows > 0) {
            // 有数据时才处理
            List<Field> allFields = getMappedFiled(clazz, null);
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<>(16);
            for (Field field : allFields) {
                // 将有注解的field存放到map中.
                if (field.isAnnotationPresent(ExcelColumn.class)) {
                    ExcelColumn attr = field.getAnnotation(ExcelColumn.class);
                    // 获得列号
                    int col = getExcelCol(attr.column());
                    maxCol = Math.max(col, maxCol);
                    fieldsMap.put(col, field);
                }
            }

            log.info("importProcessor fieldsMap:{}, maxCol:{}", JSON.toJSONString(fieldsMap), maxCol);
            // 默认第二行开始为数据行
            if (ignoreRow == null) {
                ignoreRow = 1;
            }

            for (int i = ignoreRow; i < rows; i++) {
                // 从第2行开始取数据,默认第一行是表头.
                Row row = sheet.getRow(i);
                boolean rowEmpty = isRowEmpty(row);
                log.info("importProcessor row:{}, rowEmpty:{}", i, rowEmpty);
                if (rowEmpty) {
                    continue;
                }

                T entity = null;
                for (int j = 0; j <= maxCol; j++) {
                    // 如果不存在实例则新建.
                    entity = (entity == null ? clazz.newInstance() : entity);
                    // 从map中得到对应列的field.
                    Field field = fieldsMap.get(j);
                    if (field == null) {
                        continue;
                    }

                    // 取得类型,并根据对象类型设置值.
                    Class<?> fieldType = field.getType();
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        CellType cellType = cell.getCellType();
                        String c = getCellValue(cellType, cell);
                        log.info("importProcessor row:{}, col:{}, cellType:{}, c:{}", i, j, cellType, c);
                        try {
                            if (String.class == fieldType){
                                field.set(entity, c.trim());
                            } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                                Double dou = Double.parseDouble(c);
                                field.set(entity, dou.intValue());
                            } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                                field.set(entity, Long.valueOf(c));
                            } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                                field.set(entity, Float.valueOf(c));
                            } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                                field.set(entity, Short.valueOf(c));
                            } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                                field.set(entity, Double.valueOf(c));
                            } else if (Character.TYPE == fieldType) {
                                if ((c != null) && (c.length() > 0)) {
                                    field.set(entity, Character.valueOf(c.charAt(0)));
                                }
                            } else if (Date.class == fieldType) {

                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }

                if (entity != null) {
                    list.add(entity);
                }
            }
        }
        return list;
    }

    public static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }

        int firstCellNum = row.getFirstCellNum();
        int lastCellNum = row.getLastCellNum();
        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }

        return true;
    }

    private String getCellValue(CellType cellType, Cell cell) {
        String c;
        if (cellType == CellType.NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            c = df.format(cell.getNumericCellValue());
        } else if (cellType == CellType.BOOLEAN) {
            c = String.valueOf(cell.getBooleanCellValue());
        } else {
            c = cell.getStringCellValue();
        }
        return c;
    }

    /**
     * 将EXCEL中A,B,C,D,E列映射成0,1,2,3,4
     * @param col
     */
    public static int getExcelCol(String col) {
        col = col.toUpperCase();
        // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
        int count = -1;
        char[] cs = col.toCharArray();
        for (int i = 0; i < cs.length; i++) {
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
        }
        return count;
    }

    /**
     * 表头(标题)格式设置
     *
     * @param workbook
     * @return
     */
    private CellStyle createTitleStyle(Workbook workbook) {
        log.info("创建表头格式 createTitleStyle");
        CellStyle cellStyle = workbook.createCellStyle();
        // 填充样式
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 填充色
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 填充色
        cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        // 边框样式
        buildBorder(cellStyle, BorderStyle.THIN);
        // 字体设置
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        cellStyle.setFont(font);
        // 自动换行
        cellStyle.setWrapText(true);

        return cellStyle;
    }

    /**
     * 内容格式设置
     *
     * @param workbook
     * @return
     */
    private CellStyle createContentStyle(Workbook workbook) {
        log.info("创建表头格式 createTitleStyle");
        CellStyle cellStyle = workbook.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 字体设置
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        cellStyle.setFont(font);
        // 自动换行
        cellStyle.setWrapText(true);

        return cellStyle;
    }

    /**
     * 设置边框
     *
     * @param cellStyle
     * @param style
     */
    private void buildBorder(CellStyle cellStyle, BorderStyle style) {
        cellStyle.setBorderBottom(style);
        cellStyle.setBorderTop(style);
        cellStyle.setBorderLeft(style);
        cellStyle.setBorderRight(style);
    }

    /**
     * 自适应宽度(中文支持)
     *
     * @param sheet
     * @param size
     */
    private void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    String value = "";
                    CellType cellType = currentCell.getCellType();
                    if (cellType == CellType.NUMERIC) {
                        currentCell.setCellType(CellType.STRING);
                        value = currentCell.getStringCellValue();
                    } else if (cellType == CellType.STRING) {
                        value = currentCell.getStringCellValue();
                    }
                    int length = value.getBytes().length;
                    if (columnWidth < length) {
                        columnWidth = length;
                    }
                }
            }

            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    /**
     * 得到实体类所有通过注解映射了数据表的字段
     *  递归调用
     */
    protected List<Field> getMappedFiled(Class clazz, List<Field> fields) {
        if (fields == null) {
            fields = new ArrayList<>();
        }
        // 得到所有定义字段
        Field[] allFields = clazz.getDeclaredFields();
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelColumn.class)) {
                fields.add(field);
            }
        }
        if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
            getMappedFiled(clazz.getSuperclass(), fields);
        }
        return fields;
    }

    /**
     * 将数据写入Excel
     * @param dataList
     * @param failData
     * @return
     */
    public String writeDataToExcel(List<T> dataList, boolean failData) {
        String localFileDir = "/tmp/localFile";
        String fileName = UUID.randomUUID().toString() + ".xlsx";
        String localFilePath = FileUtils.createFile(localFileDir, fileName);
        File file = new File(localFilePath);
        try (Workbook workbook = writeDataToExcel(dataList, failData);
             FileOutputStream output = new FileOutputStream(file)) {
            workbook.write(output);
            output.flush();
            return localFilePath;
        } catch (Exception e) {
            log.error("writeDataToExcelAndUpload e:", e);
            throw new BusinessException("将数据写入excel异常");
        }
    }

    public Workbook writeDataToExcel(List<T> dataList, boolean failData) throws Exception{
        List<String> headerNames = new ArrayList<>();
        List<Field> fields = getMappedFiled(clazz, null);
        boolean hasPic = false;
        for (Field field : fields) {
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            headerNames.add(excelColumn.name());
        }

        if (failData) {
            headerNames.add("失败原因");
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        // 创建第一栏，抬头栏
        XSSFRow headRow = sheet.createRow(0);
        headRow.setHeightInPoints(91);
        // 设置单元格类型
        CellStyle headStyle = createTitleStyle(workbook);
        // 创建抬头栏单元格
        for (int j = 0; j < headerNames.size(); j++) {
            XSSFCell cell = headRow.createCell(j);
            if (headerNames.get(j) != null) {
                cell.setCellValue(headerNames.get(j));
                cell.setCellStyle(headStyle);
            }
        }

        int size = dataList.size();
        int maxCol = headerNames.size();
        int fieldCol = fields.size();
        try {
            CellStyle contentStyle = createContentStyle(workbook);
            CellStyle errorMsgStyle = createErrorMsgStyle(workbook);
            for (int i = 0; i < size; i++) {
                XSSFRow row = sheet.createRow(i + 1);
                row.setHeightInPoints(60);
                T vo = dataList.get(i);
                XSSFCell cell;
                for (int j = 0; j < maxCol; j++) {
                    // 当j > fieldCol时
                    if (j > fieldCol - 1 && j == maxCol - 1) {
                        Field field = clazz.getDeclaredField("errorMsg");
                        field.setAccessible(true);
                        Object o = field.get(vo);
                        cell = row.createCell(j);
                        cell.setCellStyle(errorMsgStyle);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(o != null ? o.toString() : "");
                    } else {
                        Field field = fields.get(j);
                        field.setAccessible(true);
                        ExcelColumn attr = field.getAnnotation(ExcelColumn.class);
                        cell = row.createCell(getExcelCol(attr.column()));
                        if (field.getType() == String.class) {
                            cell.setCellStyle(contentStyle);
                            cell.setCellType(CellType.STRING);
                            String cellValue = field.get(vo) == null ? "" : String.valueOf(field.get(vo));
                            cell.setCellValue(cellValue);
                        }
                    }
                }
            }

            // 宽度自适应
        } catch (Exception e) {
            log.error("失败 e:", e);
            throw new Exception("失败");
        }

        return workbook;
    }
}


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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404

这个工具类只是展示了简单的字段导入/导出的读取，如果带图片或者其他特殊要求的字段，可以自定义规则读取。