EasyPOI实现excel导入导出功能

文件上传下载依赖

    <!-- commons-io -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.7</version>
    </dependency>
    <!-- commons-fileupload -->
    <dependency>
        <groupId>commons-fileupload</groupId>
        <artifactId>commons-fileupload</artifactId>
        <version>1.4</version>
    </dependency>

easypoi依赖

    <!--easypoi-->

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.1.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.1.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.1.0</version>
    </dependency>

Spring-service配置文件

    <!-- 对文件上传的支持-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="maxUploadSize" value="50000000"></property>
        <property name="defaultEncoding" value="UTF-8"></property>
    </bean>

1.根据前端获取的文件流实现导入功能

  @RequestMapping("/excel1")
    @ResponseBody
    public String importExcel(MultipartFile file) throws Exception {

        ImportParams params = new ImportParams();
        //去掉标题行
        params.setTitleRows(5);
        
        List<DemoData> list = ExcelImportUtil.importExcel(file.getInputStream(), DemoData.class, params);
        for (int i = 0; i < list.size(); i++) {
            if (null==list.get(i).getExamNo()){
                list.remove(i);
                i--;
            }
        }

        if (easyService.save(list)) {
            return GetResult.getResultObj("ok");
        }
        return GetResult.getResultObj("文件插入失败");
    }

2.实现模板导出功能

  @PostMapping("/excel2")
    public void exportExcel(HttpServletRequest request,HttpServletResponse response){
        String realPath = request.getSession().getServletContext().getRealPath("/")+"Templete/技工报名表模板.xls";
        TemplateExportParams params = new TemplateExportParams(realPath);
        HashMap<String, Object> map = new HashMap<String,Object>();
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();

        if (params != null){
            List<DemoData> demoData = easyService.queryAll();
            for (int i = 0; i < demoData.size(); i++) {
                HashMap<String, String> lm = new HashMap<String, String>();
                lm.put("id", i + 1 + "");
                lm.put("examNo",demoData.get(i).getExamNo());
                lm.put("name",demoData.get(i).getName());
                lm.put("sex",demoData.get(i).getSex());
                lm.put("idNo",demoData.get(i).getIdNo());
                lm.put("homeTown",demoData.get(i).getHomeTown());
                lm.put("highDegree",demoData.get(i).getHighDegree());
                lm.put("workYear",demoData.get(i).getWorkYear());
                lm.put("career",demoData.get(i).getCareer());
                lm.put("workNo",demoData.get(i).getWorkNo());
                lm.put("rank",demoData.get(i).getRank());
                lm.put("rankNo",demoData.get(i).getRankNo());
                lm.put("workCompany",demoData.get(i).getWorkCompany());
                listMap.add(lm);
            }
            map.put("maplist",listMap);
            Workbook workbook = ExcelExportUtil.exportExcel(params, map);
            try {
                export(request, response, workbook,  "技工报名表模板");
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }
    /**
     * export导出请求头设置
     * 防止乱码
     * @param response
     * @param workbook
     * @param fileName
     * @throws Exception
     */
    private static void export(HttpServletRequest request, HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
        response.reset();
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1") + ".xls");
        ServletOutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } finally {
            outStream.close();
        }
    }

3.ajax接收文件流

<script src="https://cdn.staticfile.org/axios/0.18.0/axios.min.js"></script>

    $("#test3").click(function () {

        exportExcel();
    });



    function exportExcel() {

        axios({

            method: 'POST',

            url: "/book/excel2",

            timeout: 5000,

            responseType: 'blob'

        }).then(function (res) {

            var data = res.data;

            var blob = new Blob([data], {type: 'application/octet-stream'});

            var url = URL.createObjectURL(blob);

            var exportLink = document.createElement('a');

            exportLink.setAttribute("download","ajax文件下载.xlsx");

            exportLink.href = url;

            document.body.appendChild(exportLink);

            exportLink.click();

        })

    }

3.模板设置

 4.这里介绍下fe的用法：
三目运算 {{test ? obj:obj2}}
n: 表示 这个cell是数值类型 {{n:}}
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
fn: 格式化数字 {{fn:(obj;###.00)}}
fe: 遍历数据,创建row
!fe: 遍历数据不创建row
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
#fe: 横向遍历
v_fe: 横向遍历值
!if: 删除当前列 {{!if:(test)}}
单引号表示常量值 '' 比如'1' 那么输出的就是 1
&NULL& 空格
]] 换行符 多行遍历导出
sum： 统计数据