• easypoi一行代码搞定excel导入导出


    参考 https://www.jianshu.com/p/5d67fb720ece

    pom引入

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

      

    编写实体类

    此处注意必须要有空构造函数,否则会报错“对象创建错误”
    关于注解@Excel,其他还有@ExcelCollection,@ExcelEntity ,@ExcelIgnore,@ExcelTarget等,此处我们用不到,可以去官方查看更多

    比如
    import cn.afterturn.easypoi.excel.annotation.Excel;
    
    import java.util.Date;
    
    public class Person {
    
        @Excel(name = "姓名", orderNum = "0")
        private String name;
    
        @Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
        private String sex;
    
        @Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2")
        private Date birthday;
    
        public Person(String name, String sex, Date birthday) {
            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;
        }
    }
    

      

    导入导出公用方法

    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);
        }
    
        private 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);
        }
    
        private 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) {
                throw new NormalException(e.getMessage());
            }
        }
    private 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);
        }
    
        public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
            if (StringUtils.isBlank(filePath)){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            }catch (NoSuchElementException e){
                throw new NormalException("模板不能为空");
            } catch (Exception e) {
                e.printStackTrace();
                throw new NormalException(e.getMessage());
            }
            return list;
        }
        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 (NoSuchElementException e){
                throw new NormalException("excel文件不能为空");
            } catch (Exception e) {
                throw new NormalException(e.getMessage());
            }
            return list;
        }

    测试

      @RequestMapping("export")
        public void export(HttpServletResponse response){
    
            //模拟从数据库获取需要导出的数据
            List<Person> personList = new ArrayList<>();
            Person person1 = new Person("路飞","1",new Date());
            Person person2 = new Person("娜美","2", DateUtils.addDate(new Date(),3));
            Person person3 = new Person("索隆","1", DateUtils.addDate(new Date(),10));
            Person person4 = new Person("小狸猫","1", DateUtils.addDate(new Date(),-10));
            personList.add(person1);
            personList.add(person2);
            personList.add(person3);
            personList.add(person4);
    
            //导出操作
            FileUtil.exportExcel(personList,"花名册","草帽一伙",Person.class,"海贼王.xls",response);
        }
    
        @RequestMapping("importExcel")
        public void importExcel(){
            String filePath = "F:\海贼王.xls";
            //解析excel,
            List<Person> personList = FileUtil.importExcel(filePath,1,1,Person.class);
            //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
            System.out.println("导入数据一共【"+personList.size()+"】行");
    
            //TODO 保存数据库
        }

    测试导入

  • 相关阅读:
    length()
    matlab mod()&rem()
    tf调试函数
    64位win7+PCL1.6.0+VS2010,64位win10+PCL1.6.0+VS2010
    pcl 1.8 + VS 2010 在win7 x64下的配置
    Qt在vs2010下的配置
    VS2010 win7 QT4.8.0,实现VS2010编译调试Qt程序,QtCreator静态发布程序
    [POI2012]ROZ-Fibonacci Representation (贪心)
    CF 666C & 牛客 36D
    数位dp练习
  • 原文地址:https://www.cnblogs.com/yachao1120/p/12844842.html
Copyright © 2020-2023  润新知