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

admin2024-05-15  0

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;
}

定义数据类

/**
 * 数据对象
 * @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;
    }
}

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;
    }
}


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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明原文出处。如若内容造成侵权/违法违规/事实不符,请联系SD编程学习网:675289112@qq.com进行投诉反馈,一经查实,立即删除!