springboot中导入导出excel文件

 

原文：https://www.jianshu.com/p/5d67fb720ece

 

在开发中会遇到导入Excel文件或者DBF文件等，这里记录如何更简单的在springboot中导入导出Excel文件，不同于用poi，还需要写大段工具类代码

此处运用别人造好的轮子【easypoi】

 

pom引入

需要的jar包

编写实体类

 

• 此处注意必须要有空构造函数，否则会报错“对象创建错误”（我并未考证）

 

• 关于注解@Excel，其他还有@ExcelCollection，@ExcelEntity ，@ExcelIgnore，@ExcelTarget等，此处我们用不到，可以去官方查看更多

public class ExcelBean {
    @Excel(name="姓名", orderNum="0")
    private String name;
    @Excel(name="性别", replace={"男_2","女_1"}, orderNum="1")
    private String sex;
    @Excel(name="生日", orderNum="2")
    private Date birthday;
    
    public ExcelBean() {
        super();
        // TODO Auto-generated constructor stub
    }
    public ExcelBean(String name, String sex, Date birthday) {
        super();
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    } 
    
    
}

 

 

工具类（虽然比往常用poi简介一些，但还是需要写工具类）

public class ExcelUtils {
        /*public static void exportExcel(){
            
        }*/
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(list, pojoClass, fileName, response, exportParams);

        }
        
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){
            defaultExport(list, pojoClass, fileName, response,new ExportParams(title, sheetName));
        }
        
        public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
        
        public static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams){
            Workbook workbook=ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            if (workbook != null) {
                downLoadExcel(fileName, response, workbook);
            }
        }
        
        public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook){
            
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
            } catch (IOException  e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }    
        }
        
        public static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            Workbook workbook=ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            if (workbook != null) {
                downLoadExcel(fileName, response, workbook);
            }
        }
        /**
         * 泛型方法
         * @param filePath
         * @param titleRows
         * @param headerRows
         * @param pojoClass
         * @return
         */
        public static<T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
            if (StringUtils.nonEmptyString(filePath)) {
                return null;
            }
            ImportParams params=new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list=null;
            
            list=ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            
            return list;
        }
        /**
         * 泛型方法
         * @param file
         * @param titleRows
         * @param headerRows
         * @param pojoClass
         * @return
         */
        public static<T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
            if (file==null) {
                return null;
            }
            ImportParams params=new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list=null;
            try {
                list=ExcelImportUtil.importExcel(file.getInputStream(),pojoClass,params);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return list;
        }
        
                
}

进行测试（导出文件已测试成功，但导入并没有测试）

@Value("${com.excel.name}")
        private String name;
        /**
         * 导出Excel文件
         * @param response
         */
        @RequestMapping("/export")
        public void export(HttpServletResponse response){
            List<ExcelBean> list=new ArrayList<ExcelBean>();
            ExcelBean excel=new ExcelBean(name, "1", new Date());
            ExcelBean excel1=new ExcelBean(name, "2", DateUtils.addDays(new Date(), 3));
            list.add(excel);
            list.add(excel1);
            ExcelUtils.exportExcel(list, "文件标题", "文件头", ExcelBean.class, "Excel文件.xls", response);
        }
        
        /**
         * 导入Excel文件
         */
        @RequestMapping("/import")
        public void importExcel(){
            String filename="C:\\Users\\pc\\Desktop\\流程图.xlsx";
            List<ExcelBean> list=ExcelUtils.importExcel(filename, 1, 1, ExcelBean.class);
        }

上述代码中

@Value("${com.excel.name}")
        private String name;
的写法是把name定义再application中，个人喜好

Excel文件导出测试结果

 

因为没有把Date（）转化格式，所以显示是英文