从0到1构建MES系统10-Excel导入导出

今天我们来讲一下Excel的导入导出。 在2B的业务系统，Excel的导入导出是必不可少的，特别是报表类型的功能。现在有很多开源的Excel导入导出工具基本上都是基于Apache POI来封装的。

工具库	优点	缺点	适用场景
Apache POI	功能最强大，支持所有 Excel 操作	内存消耗大，API 复杂	复杂格式、小数据量
EasyExcel	高性能，避免 OOM	功能比 POI 少	大数据量导入导出
Hutool	API 简单，快速开发	依赖 POI，功能受限	快速实现基础需求

参考了现有的开源项目，由于有时候需要一些特殊定制化的报表需求，在MES系统中我也封装了一套基于Apache POI的工具类。下面我来详细说明一下如何实现。

1. 自定义注解

@Retention(RetentionPolicy.RUNTIME)  
@Target(ElementType.FIELD)  
public @interface Excel  
{  
    /**  
     * 导出时在excel中排序  
     */  
    int sort() default Integer.MAX_VALUE;  
  
    /**  
     * 导出到Excel中的名字.  
     */    String name() default "";  
  
    /**  
     * 日期格式, 如: yyyy-MM-dd  
     */    String dateFormat() default "";  
  
    /**  
     * 如果是字典类型，请设置字典的type值 (如: sys_user_sex)  
     */    String dictType() default "";  
  
    /**  
     * 读取内容转表达式 (如: 0=男,1=女,2=未知)  
     */    String readConverterExp() default "";  
  
    /**  
     * 分隔符，读取字符串组内容  
     */  
    String separator() default ",";  
  
    /**  
     * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)  
     */    int scale() default -1;  
  
    /**  
     * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN  
     */    int roundingMode() default BigDecimal.ROUND_HALF_EVEN;  
  
    /**  
     * 导出类型（0数字 1字符串）  
     */  
    ColumnType cellType() default ColumnType.STRING;  
  
    /**  
     * 导出时在excel中每个列的高度 单位为字符  
     */  
    double height() default 14;  
  
    /**  
     * 导出时在excel中每个列的宽 单位为字符  
     */  
    double width() default 16;  
  
    /**  
     * 文字后缀,如% 90 变成90%  
     */    String suffix() default "";  
  
    /**  
     * 当值为空时,字段的默认值  
     */  
    String defaultValue() default "";  
  
    /**  
     * 提示信息  
     */  
    String prompt() default "";  
  
    /**  
     * 设置只能选择不能输入的列内容.  
     */    String[] combo() default {};  
  
    /**  
     * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.  
     */    boolean isExport() default true;  
  
    /**  
     * 另一个类中的属性名称,支持多级获取,以小数点隔开  
     */  
    String targetAttr() default "";  
  
    /**  
     * 是否自动统计数据,在最后追加一行统计数据总和  
     */  
    boolean isStatistics() default false;  
  
    /**  
     * 导出字段对齐方式（0：默认；1：靠左；2：居中；3：靠右）  
     */  
    Align align() default Align.AUTO;  
  
    /**  
     * 自定义数据处理器  
     */  
    Class<?> handler() default ExcelHandlerAdapter.class;  
  
    /**  
     * 自定义数据处理器参数  
     */  
    String[] args() default {};  
  
    enum Align  
    {  
        AUTO(0), LEFT(1), CENTER(2), RIGHT(3);  
        private final int value;  
  
        Align(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
  
    /**  
     * 字段类型（0：导出导入；1：仅导出；2：仅导入）  
     */  
    Type type() default Type.ALL;  
  
    enum Type  
    {  
        ALL(0), EXPORT(1), IMPORT(2);  
        private final int value;  
  
        Type(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
  
    enum ColumnType  
    {  
        NUMERIC(0), STRING(1), IMAGE(2);  
        private final int value;  
  
        ColumnType(int value)  
        {  
            this.value = value;  
        }  
  
        public int value()  
        {  
            return this.value;  
        }  
    }  
}

备注： 这里参考了 “若依” 的导入导出方案，自定义注解用来修饰导出实体中需要导出的字段。

2. ExcelUtil工具类

这里选择重要的代码来讲解，如果需要详细代码可以下载 项目源码查看 类路径：com/hgyc/mom/common/util/poi/ExcelUtil.java

2.1 生成导入模板

//Controller接口
//MaterialImportVO为导入的实体类
ExcelUtil<MaterialImportVO> util = new ExcelUtil<>(MaterialImportVO.class);  
util.importTemplateExcel(response, "物料导入模板" );

...

/**  
 * 得到所有定义字段  
 */  
private void createExcelField()  
{  
	//通过反射，获取字段的@Excel注解
    this.fields = getFields();  
    this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());  
    this.maxHeight = getRowHeight();  
}

...

//根据实体类中定义的列字段，创建excel的列
public void writeSheet()  
{  
    // 取出一共有多少个sheet.  
    int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));  
    for (int index = 0; index < sheetNo; index++)  
    {  
        createSheet(sheetNo, index);  
  
        // 产生一行  
        Row row = sheet.createRow(rownum);  
        int column = 0;  
        // 写入各个字段的列头名称  
        for (Object[] os : fields)  
        {  
            Excel excel = (Excel) os[1];  
            this.createCell(excel, row, column++);  
        }  
        if (Excel.Type.EXPORT.equals(type))  
        {  
            fillExcelData(index, row);  
            addStatisticsRow();  
        }  
    }  
}

2.2 获取导入Excel数据

• Controller接口，先初始化ExcelUtil工具类

ExcelUtil<MaterialImportVO> util = new ExcelUtil<>(MaterialImportVO.class);  
List<MaterialImportVO> materialList = util.importExcel(file.getInputStream());

• 获取Excel数据行，获取表头信息和实体定义的注解，把每一行初始化成为一个实体实例，存入一个集合当中。

/**  
 * 对excel表单指定表格索引名转换成list  
 ** @param sheetName 表格索引名  
 * @param titleNum 标题占用行数  
 * @param is 输入流  
 * @return 转换后集合  
 */  
public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception  
{  
    this.type = Excel.Type.IMPORT;  
    this.wb = WorkbookFactory.create(is);  
    List<T> list = new ArrayList<T>();  
    // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet  
    Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);  
    if (sheet == null)  
    {  
        throw new IOException("文件sheet不存在");  
    }  
    boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);  
    Map<String, PictureData> pictures;  
    if (isXSSFWorkbook)  
    {  
        pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);  
    }  
    else  
    {  
        pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);  
    }  
    // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1  
    int rows = sheet.getLastRowNum();  
  
    if (rows > 0)  
    {  
        // 定义一个map用于存放excel列的序号和field.  
        Map<String, Integer> cellMap = new HashMap<String, Integer>();  
        // 获取表头  
        Row heard = sheet.getRow(titleNum);  
        for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)  
        {  
            Cell cell = heard.getCell(i);  
            if (StringUtils.isNotNull(cell))  
            {  
                String value = this.getCellValue(heard, i).toString();  
                cellMap.put(value, i);  
            }  
            else  
            {  
                cellMap.put(null, i);  
            }  
        }  
        // 有数据时才处理 得到类的所有field.  
        List<Object[]> fields = this.getFields();  
        Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();  
        for (Object[] objects : fields)  
        {  
            Excel attr = (Excel) objects[1];  
            Integer column = cellMap.get(attr.name());  
            if (column != null)  
            {  
                fieldsMap.put(column, objects);  
            }  
        }  
        for (int i = titleNum + 1; i <= rows; i++)  
        {  
            // 从第2行开始取数据,默认第一行是表头.  
            Row row = sheet.getRow(i);  
            // 判断当前行是否是空行  
            if (isRowEmpty(row))  
            {  
                continue;  
            }  
            T entity = null;  
            for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())  
            {  
                Object val = this.getCellValue(row, entry.getKey());  
  
                // 如果不存在实例则新建.  
                entity = (entity == null ? clazz.newInstance() : entity);  
                // 从map中得到对应列的field.  
                Field field = (Field) entry.getValue()[0];  
                Excel attr = (Excel) entry.getValue()[1];  
                // 取得类型,并根据对象类型设置值.  
                Class<?> fieldType = field.getType();  
                if (String.class == fieldType)  
                {  
                    String s = Convert.toStr(val);  
                    if (StringUtils.endsWith(s, ".0"))  
                    {  
                        val = StringUtils.substringBefore(s, ".0");  
                    }  
                    else  
                    {  
                        String dateFormat = field.getAnnotation(Excel.class).dateFormat();  
                        if (StringUtils.isNotEmpty(dateFormat))  
                        {  
                            val = parseDateToStr(dateFormat, val);  
                        }  
                        else  
                        {  
                            val = Convert.toStr(val);  
                        }  
                    }  
                }  
                else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))  
                {  
                    val = Convert.toInt(val);  
                }  
                else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))  
                {  
                    val = Convert.toLong(val);  
                }  
                else if (Double.TYPE == fieldType || Double.class == fieldType)  
                {  
                    val = Convert.toDouble(val);  
                }  
                else if (Float.TYPE == fieldType || Float.class == fieldType)  
                {  
                    val = Convert.toFloat(val);  
                }  
                else if (BigDecimal.class == fieldType)  
                {  
                    val = Convert.toBigDecimal(val);  
                }  
                else if (Date.class == fieldType)  
                {  
                    if (val instanceof String)  
                    {  
                        val = DateUtils.parseDate(val);  
                    }  
                    else if (val instanceof Double)  
                    {  
                        val = DateUtil.getJavaDate((Double) val);  
                    }  
                }  
                else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)  
                {  
                    val = Convert.toBool(val, false);  
                }  
                if (StringUtils.isNotNull(fieldType))  
                {  
                    String propertyName = field.getName();  
                    if (StringUtils.isNotEmpty(attr.targetAttr()))  
                    {  
                        propertyName = field.getName() + "." + attr.targetAttr();  
                    }  
                    else if (StringUtils.isNotEmpty(attr.readConverterExp()))  
                    {  
                        val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());  
                    }  
                    else if (StringUtils.isNotEmpty(attr.dictType()))  
                    {  
                        val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());  
                    }  
                    else if (!attr.handler().equals(ExcelHandlerAdapter.class))  
                    {  
                        val = dataFormatHandlerAdapter(val, attr);  
                    }  
                    else if (Excel.ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))  
                    {  
                        PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());  
                        if (image == null)  
                        {  
                            val = "";  
                        }  
                        else  
                        {  
                            byte[] data = image.getData();  
                            val = FileUtils.writeImportBytes(data);  
                        }  
                    }  
                    ReflectUtils.invokeSetter(entity, propertyName, val);  
                }  
            }  
            list.add(entity);  
        }  
    }  
    return list;  
}

3. 导出数据

• 从数据库中查询数据，转换成导出实体类，初始化ExcelUtil，执行exportExcel()方法导出Excel

@Operation(summary = "导出物料基础信息列表", description = "导出物料基础信息列表")  
@PostMapping("/export")  
public void export(HttpServletResponse response, MaterialParam materialParam)  
{  
    Material material = new Material();  
    BeanUtils.copyProperties(materialParam, material);  
    QueryWrapper<Material> wrapper = QueryWrapperUtil.build(material);  
    List<Material> list = materialService.list(wrapper);  
    ExcelUtil<Material> util = new ExcelUtil<>(Material.class);  
    util.exportExcel(response, list, "物料基础信息数据");  
}

• 根据导出实体类的注解信息，把数据写入到Excel中

/**  
 * 创建写入数据到Sheet  
 */
public void writeSheet()  
{  
    // 取出一共有多少个sheet.  
    int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));  
    for (int index = 0; index < sheetNo; index++)  
    {  
        createSheet(sheetNo, index);  
  
        // 产生一行  
        Row row = sheet.createRow(rownum);  
        int column = 0;  
        // 写入各个字段的列头名称  
        for (Object[] os : fields)  
        {  
            Excel excel = (Excel) os[1];  
            this.createCell(excel, row, column++);  
        }  
        if (Excel.Type.EXPORT.equals(type))  
        {  
            fillExcelData(index, row);  
            addStatisticsRow();  
        }  
    }  
}

...

/**  
 * 填充excel数据  
 *   
* @param index 序号  
 * @param row 单元格行  
 */  
public void fillExcelData(int index, Row row)  
{  
    int startNo = index * sheetSize;  
    int endNo = Math.min(startNo + sheetSize, list.size());  
    for (int i = startNo; i < endNo; i++)  
    {  
        row = sheet.createRow(i + 1 + rownum - startNo);  
        // 得到导出对象.  
        T vo = (T) list.get(i);  
        int column = 0;  
        for (Object[] os : fields)  
        {  
            Field field = (Field) os[0];  
            Excel excel = (Excel) os[1];  
            this.addCell(excel, row, vo, field, column++);  
        }  
    }  
}

...

/**  
 * 添加单元格  
 */  
public Cell addCell(Excel attr, Row row, T vo, Field field, int column)  
{  
    Cell cell = null;  
    try  
    {  
        // 设置行高  
        row.setHeight(maxHeight);  
        // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.  
        if (attr.isExport())  
        {  
            // 创建cell  
            cell = row.createCell(column);  
            int align = attr.align().value();  
            cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));  
  
            // 用于读取对象中的属性  
            Object value = getTargetValue(vo, field, attr);  
            String dateFormat = attr.dateFormat();  
            String readConverterExp = attr.readConverterExp();  
            String separator = attr.separator();  
            String dictType = attr.dictType();  
            if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))  
            {  
                cell.setCellValue(parseDateToStr(dateFormat, value));  
            }  
            else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))  
            {  
                cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));  
            }  
            else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))  
            {  
                cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));  
            }  
            else if (value instanceof BigDecimal && -1 != attr.scale())  
            {  
                cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());  
            }  
            else if (!attr.handler().equals(ExcelHandlerAdapter.class))  
            {  
                cell.setCellValue(dataFormatHandlerAdapter(value, attr));  
            }  
            else  
            {  
                // 设置列类型  
                setCellVo(value, attr, cell);  
            }  
            addStatisticsData(column, Convert.toStr(value), attr);  
        }  
    }  
    catch (Exception e)  
    {  
        log.error("导出Excel失败{}", e);  
    }  
    return cell;  
}

本文源码已上传Gitee 开源项目地址：

• 前端仓库
• 后端仓库

欢迎在评论区分享你的技术选型经验，或对本文方案的改进建议！

关注公众号「慧工云创」