Java Excel导入导出，基于XML和Easy-excel使用

531 查看

1.前言

  • 在工作时，遇到过这样的需求，需要灵活的对工单进行导入或导出，以前自己也做过，但使用不灵活繁琐。我想能不能像配置文件一样可配置的导入导出，那样使用起来就方便许多。

2.SpringMVC项目搭建

  • 创建基于Maven版本管理Springmvc项目，以下是主要的依赖，用jetty作为内嵌的服务器，直接启动。

  • 架包相关依赖pom.xml。

    <!-- 版本定义 -->
    <properties>
        <commons-beanutils.version>1.9.2</commons-beanutils.version>
        <commons-collections.version>3.2.1</commons-collections.version>
        <commons-lang.version>2.6</commons-lang.version>
        <poi.version>3.14</poi.version>
        <xmlbeans.version>2.6.0</xmlbeans.version>
        <spring.version>4.2.6.RELEASE</spring.version>
        <log4j.version>1.2.17</log4j.version>
        <druid.version>1.0.11</druid.version>
    </properties>

    <!-- <dependencyManagement> -->
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- commons -->
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>${commons-beanutils.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>${commons-collections.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>${commons-lang.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.3.2</version>
        </dependency>

        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>


        <!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>${xmlbeans.version}</version>
        </dependency>
        <!-- spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!-- log -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${log4j.version}</version>
        </dependency>

        <!--mybatis start-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.0</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!--mybatis end-->

        <!--spring dao 依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!--spring web依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--spring web依赖 end-->


        <!--spring 其他 依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!--Severlet Web 相关依赖-->
        <!--2个标签库-->
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>standard</artifactId>
            <version>1.1.2</version>
        </dependency>
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.6.5</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>

        <!-- 日志
       java 日志：slf4j 是规范、接口
       log4j\logback\common-logging 是实现
       -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.15</version>
        </dependency>

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.1.3</version>
        </dependency>
        <!-- 实现了slf4j 接口并整合 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.1.3</version>
        </dependency>

        <!--数据库层依赖-->
        <!-- connection pool start-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!-- connection pool end-->

        <!-- Jetty -->
        <dependency>
            <groupId>org.eclipse.jetty.aggregate</groupId>
            <artifactId>jetty-all</artifactId>
            <version>8.1.16.v20140903</version>
            <scope>test</scope>
        </dependency>
        <!-- Jetty Webapp -->
        <dependency>
            <groupId>org.eclipse.jetty</groupId>
            <artifactId>jetty-webapp</artifactId>
            <version>8.1.16.v20140903</version>
            <scope>test</scope>
        </dependency>
        
        <dependency>
            <groupId>org.eclipse.jetty</groupId>
            <artifactId>jetty-jsp</artifactId>
            <version>8.1.16.v20140903</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.8</version>
        </dependency>

        <!-- json插件 -->
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.13</version>
        </dependency>
        <!-- 文件上传 -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.2.2</version>
        </dependency>
        <!--common start-->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>18.0</version>
        </dependency>


        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongo-java-driver</artifactId>
            <version>3.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-mongodb</artifactId>
            <version>1.8.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
            <version>1.11.4.RELEASE</version>
        </dependency>

    </dependencies>

  • 其它相关配置没啥特别，详见源码。

3.Xml配置和Easy-excel使用

  • 下面是员工信息模型。

    //员工模型
    public class EmployeeModel {

    /**
     * ID
     */
    protected Integer id;
    /**
     * 创建时间
     */
    protected Date createTime;
    /**
     * 姓名
     */
    private String name;
    /**
     * 性别 0:男 1:女
     */
    private Integer sex;
    /**
     * 年龄
     */
    private Integer age;
    /**
     * 手机号
     */
    private String mobile;
    /**
     * 状态 0:在职
     *     1:离职
     */
    private Integer status;
    //省略getter setter
    
    }

  • 有如下的Excel文件格式，需要映射成学生实体类。

  • 那么使用easy-excel 如何导入呢？

  • 前一行是属于不规则的数据，从标题行以后才是规则的数据，也就是从规则数据之后才能正常转换成JavaBean。

  • 具体实现代码，首先编写配置文件：excel-config.xml。

    <excels> 
    <excel id="employee" class="org.easy.web.ExcelModel.EmployeeModel" sheetname="员工信息列表" defaultColumnWidth="5000" enableStyle="true">
        <field name="id" title="ID" align="center" isNull="true" uniformStyle="true" columnWidth="5000" />
        <field name="createTime" title="入职时间" pattern="yyyy/MM/dd" isNull="true" uniformStyle="true" columnWidth="5000" />
        <field name="name" title="姓名" align="center" isNull="true" uniformStyle="true" columnWidth="5000" />
        <field name="sex" title="性别" align="center" isNull="true" uniformStyle="true" columnWidth="5000" format="1:女,0:男"/>
        <field name="age" title="年龄" align="center" isNull="true" uniformStyle="true" columnWidth="5000" regex="^[1-9]\d*$" regexErrMsg="必须是数字"/>
        <field name="mobile" title="手机" align="center" isNull="true" uniformStyle="true" columnWidth="5000" regex="^[1][3,4,5,8][0-9]{9}$" regexErrMsg="格式错误"/>
        <field name="status" title="在职状态" align="center" isNull="true" uniformStyle="true" columnWidth="5000" format="1:离职,0:在职" />
    </excel>
    </excels>

  • 解释,每个excel表示一种Excel文件到JavaBean的映射规则,该规则可以是导入和导出。

  • 配置了一个id为employee的映射,要映射对应的JavaBean实体为 EmployeeModel。

<excel id="employee" class="org.easy.web.ExcelModel.EmployeeModel">

  • excel文件中标题为ID的列,把它的值映射到 EmployeeModel.id属性上。

    <field name="id" title="ID"/>

  • isNull属性,表示在excel文件中标题为【年龄】的单元格的值不能为空,并且符合正则【 regex】,当正则校验没有通过时,会提示【xxx行,[ 年龄 ]必须是数字,如果为空同理,xxx行[年龄]不能为空】。

    <field name="age" title="年龄" isNull="false" regex="^[1-9]\d*$" regexErrMsg="必须是数字"/>

  • pattern：不做过多解释,SimpleDateFormat(pattern),导入数据时字符串的值如何转换成日期。支持多种映射pattern，使用【英文逗号】进行分割。

<field name="createTime" title="入职时间" pattern="yyyy/MM/dd" isNull="true" uniformStyle="true" columnWidth="5000" />

  • format属性,观察上面的excel文件结构会发现状态列是中文,那么导入时,可能javaBean中并不是中文,而是数字或其他,那么如何把它转换成数字呢？format就是做这个事情的。导入时它会以【,分割:前面的作为导入时使用的值，:后面的作为导出时使用的值】:后面值进行逆推,导出时同理。思考一个问题？如果这个值不确定如何解决,或者这个值需要到数据库校验？比如是个城市地址，这个时候是需要查询数据库进行比对的，如果地址不存在则抛出错误提示信息的,就说这么多,easy-excel已经做好了,支持自定义的转换器可以解决。

<field name="status" title="在职状态" align="center" isNull="true" uniformStyle="true" columnWidth="5000" format="1:离职,0:在职" />

  • 那么核心的代码是哪几行？

    //导入
    public Result importExcel(@RequestParam(value = "file", required = true) MultipartFile file) throws Exception {
        Boolean excel = StringUtil.isExcel(file.getOriginalFilename());
        if (!excel){
            return Result.wrapErrorResult("该文件不是Excel格式");
        }
        InputStream fis = file.getInputStream();
        ExcelContext context = new ExcelContext("excel/config.xml");
        ExcelUtil<EmployeeModel> excelUtil = new ExcelUtil(excelContext, "employee");
        List<EmployeeModel> stus;
        try {
            stus = excelUtil.importExcel(1, fis);
        } catch (ExcelException e) {//这里主动返回错误信息
            return Result.wrapErrorResult(e.getMessage());
        }
        List<Employee> list=BdUtil.e2OList(stus,Employee.class);
        return Result.wrapSuccessfulResult(list);
    }

    //导出
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        ExcelUtil<EmployeeModel> excelUtil = new ExcelUtil(excelContext, "employee");
        final List<EmployeeModel> list = getEmployeeList();
        List<String> specifyFields = new ArrayList<String>(
        ExcelHeader header=new ExcelHeader() {
            public void buildHeader(Sheet sheet, ExcelDefinition excelDefinition, List<?> beans,Workbook workbook) {
                // 设置第一行样式
                CellStyle style1 = workbook.createCellStyle();
                // 水平居中
                style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                // 垂直居中
                style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                // 设置字体 大小
                Font font1 = workbook.createFont();
                font1.setFontHeightInPoints((short) 18);
                style1.setFont(font1);

                //第一行数据
                Row row1 = sheet.createRow(0);
                Cell cell1 = row1.createCell(0);
                cell1.setCellValue("共导出【"+list.size()+"】条数据");
                // 行高
                row1.setHeight((short) 700);
                cell1.setCellStyle(style1);

                //合并单元格
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                sheet.addMergedRegion(region);
            }
        };
        excelUtil.exportExcel(response,list, header,specifyFields,"员工信息");
    }

  • 只有准备数据、创建上下文、读取excel。。通常在真实的常见创建上下文都可以省略了，因为它会交给spring容器管理，整个jvm中，只保持一个实例就够了。

  • 关于导入配置的一个很重要的属性：resolveFieldValueConverterName。

  • 它是一个ResolveFieldValueConverter接口的实现类：假设我们的Excel中有地址，或者结合业务中可能是其他，都是需要查询数据库，或者经过更复杂的业务逻辑进行校验的，那么我们可以配置它。我们来观察这个接口做了什么事？

    /**
     * 解析Excel值解析接口
     *
     */

    public interface ResolveFieldValueConverter {
    
    /**
     * 操作类型，导入或导出
     */
    enum Type {
        EXPORT, IMPORT
    }
    
    /**
     * 解析配置中Field元素 处理后的值
     * @param bean Excel配置的JavaBean对象
     * @param value Excel原值
     * @param fieldValue FieldValue信息
     * @param type 导入或导出
     * @param rowNum 行号
     * @return 解析结果对应的value
     * @throws Exception
     */
    public Object resolveFieldValue(Object bean,Object value, FieldValue fieldValue, Type type,int rowNum) throws Exception;
    }

  • 核心只有一个方法resolveFieldValue。

  • 我们可以自定义它的实现类，然后把全类名注册到 resolveFieldValueConverterName
    属性上如：假设创建人是需要查询用户表进行校验，如果没有则不允许导入，我们则可以在自定义的实现类抛出一个异常，可以精准的提示用户多少行，哪一个字段【标题】的值错误了。

  • 直接上代码，下面是导入导出封装好工具类。

    /**
     * Excel 导出方法调用(主)
     */
    public class ExcelUtil<T> {

    //创建excel上下文实例,配置文件路径
    private ExcelContext context;
    //Excel配置文件中配置的id
    private String excelId;

    public ExcelUtil(ExcelContext context, String excelId) {
        this.context = context;
        this.excelId = excelId;
    }
    
    /**
     * @param startRow 头部从第几行开始导入
     * @param fis      文件流
     * @return
     * @throws Exception
     */
    public List<T> importExcel(int startRow, InputStream fis) throws Exception {
        //第二个参数需要注意,它是指标题索引的位置,可能你的前几行并不是标题,而是其他信息,
        //比如数据批次号之类的,关于如何转换成javaBean,具体参考配置信息描述
        ExcelImportResult result = context.readExcel(excelId, startRow, fis);
    //        System.out.println(result.getHeader());
        List<T> stus = result.getListBean();
        return stus;
        //这种方式和上面的没有任何区别,底层方法默认标题索引为0
        //context.readExcel(excelId, fis);
    }
    
        /**
         * 导出Excel并下载
         * @param response
         * @param list     结果集,null默认导出模板
         * @param header   自定义表头,null默认无
         * @param specifyFields 导出字段,null导出所有字段
         * @param fileName  下载的文件名
         */
        public void exportExcel(HttpServletResponse response, List<T> list,ExcelHeader header, List<String> specifyFields, String fileName) {
            File file = null;
            OutputStream ops = null;
            OutputStream out = null;
            Workbook workbook = null;
            try {
                /**
                 * Step1:创建临时xlsx文件
                 */
                file = File.createTempFile("tmp", ".xlsx");
                /**
                 * Step2:导出数据写入临时xlsx文件
                 */
                String path = file.getAbsolutePath();
                ops = new FileOutputStream(path);
                //获取POI创建结果
                if (list != null && !list.isEmpty()) {
                    workbook = context.createExcel(excelId, list, header, specifyFields);
    //                workbook = context.createExcel(excelId, list);
                } else {//查询结果为空,导出模板
                    workbook = context.createExcelTemplate(excelId, header, specifyFields);
                }
                workbook.write(ops);
                /**
                 * Step3:下载临时xlsx文件
                 */
                response.reset();
                response.setContentType("application/octet-stream; charset=utf-8");
                fileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
                out = response.getOutputStream();
                out.write(FileUtils.readFileToByteArray(file));
                out.flush();
    
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                file.deleteOnExit();//临时文件若存在,则删除
                try {
                    if (ops != null) {
                        ops.close();
                    }
                    if (workbook != null) {
                        workbook.close();
                    }
                    if (out != null) {
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
    
            }
        }
    
     }

最近更新的内容
你可能感兴趣的文章

2025 - 快车库 - 我的知识库 重庆启连科技有限公司 渝ICP备16002641号-10
企客连连 表单助手 企服开发 榜单123

渝公网安备 50010702501581号