当前位置:首页 > 服务端 > Springboot使用EasyExcel(仅限自己收藏)

Springboot使用EasyExcel(仅限自己收藏)

2022年09月16日 18:21:18服务端12
  1. pom文件依赖
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.2-beta5</version>
    </dependency>
    

      

  2. ExporExcelController 文件中
    @RequestMapping("/exce")
    @Controller
    public class ExporExcelController {
        //植入Service
        @Resource(name = "idemoService")
        private DemoService demoService;
    
        /*
         *直接输出数据到前台
         */
        @RequestMapping("/export")
        public String ExporExcel(HttpServletResponse response) throws Exception { //throws IOException {
            ExcelWriter writer = null;
            OutputStream outputStream = response.getOutputStream();
            try {
                //添加响应头信息
                response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls");
                response.setContentType("application/msexcel;charset=UTF-8");//设置类型
                response.setHeader("Pragma", "No-cache");//设置头
                response.setHeader("Cache-Control", "no-cache");//设置头
                response.setDateHeader("Expires", 0);//设置日期头
    
                //实例化 ExcelWriter
                writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLS, true);
    
                //实例化表单
                Sheet sheet = new Sheet(1, 0, Catagory.class);
                sheet.setSheetName("测试");
                //获取数据
                List<Catagory> catagoryList = demoService.findAllToExcel();
                //System.out.println(catagoryList.size());
                //输出
                writer.write(catagoryList, sheet);
                writer.finish();
                outputStream.flush();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    response.getOutputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
    
        /*
         *
         */
        @RequestMapping("/import")
        @ResponseBody
        public void importExcel(@RequestParam("file") MultipartFile file) throws IOException {
           
                InputStream inputStream = file.getInputStream();
                //实例化实现了AnalysisEventListener接口的类
                ExcelListener listener = new ExcelListener();
                //传入参数
                ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
                //读取信息
                excelReader.read(new Sheet(1, 1, Catagory.class));
                //获取数据
                List<Object> list = listener.getDatas();
                List<Catagory> catagoryList = new ArrayList<Catagory>();
                Catagory catagory = new Catagory();
    
                //转换数据类型,并插入到数据库
                for (int i = 0; i < list.size(); i++) {
                    catagory = (Catagory) list.get(i);
                      System.out.println(catagory);
                    demoService.addForExcel(catagory);
                }
            
    
        }
    /*
    *在服务器上生成excel
     */
        @RequestMapping("/exportserver")
        @ResponseBody
        public String writeBySimple() {
            /*
            System.out.println(System.getProperty("user.dir"));
            String path =System.getProperty("user.dir")+"\\Index"; //所创建文件目录
            */
            String path = System.getProperty("user.dir")+"\\target\\classes\\static";
            String filePath = "tmpexcel";
            File f = new File(path+"\\"+filePath);
            if(!f.exists()) {
                f.mkdirs(); //创建目录
            }
    
    
            String fileName = "测试.xlsx";
            List<List<Object>> data = new ArrayList<>();
            data.add(Arrays.asList("111", "222", "333"));
            data.add(Arrays.asList("111", "222", "333"));
            data.add(Arrays.asList("111", "222", "333"));
            List<String> head = Arrays.asList("表头1", "表头2", "表头3");
            ExcelUtil.writeBySimple(path+"\\"+filePath+"\\"+fileName, data, head);
            return filePath+"\\"+fileName;
            //return null;
        }
    }
    

     

  3.  DeamDao中

    @Component("DemoEmDAO")
    public interface DemoDAO {
        //excel查询所有
        @Select("select id,name,sex,age from em")
        public List<Catagory> findAllToExcel() throws Exception;
        //添加
        public int addForExcel(Catagory model);
    }
    

      

  4. Model中
    @EqualsAndHashCode(callSuper = true)
    @Data
    public class Catagory extends BaseRowModel {
        @ExcelProperty(value = "id", index = 0)
        private Integer id;
    
        @ExcelProperty(value = "姓名", index = 1)
        private String name;
    
        @ExcelProperty(value = "性别", index = 2)
        private String sex;
    
        @ExcelProperty(value = "年龄", index = 3)
        private Integer age;
    
    }
    

      

  5. DemoService中
    public interface DemoService {
    
        //excel查询所有
        public List<Catagory> findAllToExcel() throws Exception;
        //添加
        public int addForExcel(Catagory model);
    }
    

      

  6. DemoServiceImpl中
    @Service("idemoService")
    public class DemoServiceImpl implements DemoService {
        @Resource(name = "DemoEmDAO")
        private DemoDAO dao;
        @Override
        public List<Catagory> findAllToExcel() throws Exception {
           return dao.findAllToExcel();
        }
    
        @Override
        public int addForExcel(Catagory model) {
            dao.addForExcel(model);
            return 0;
        }
    }
    

      

  7. ExcelListener中
    public class ExcelListener extends AnalysisEventListener {
        //可以通过实例获取该值
        private List<Object> datas = new ArrayList<Object>();
    
        public void invoke(Object o, AnalysisContext analysisContext) {
            datas.add(o);//数据存储到list,供批量处理,或后续自己业务逻辑处理。
            doSomething(o);//根据自己业务做处理
        }
    
        private void doSomething(Object object) {
            //1、入库调用接口
        }
    
        public List<Object> getDatas() {
            return datas;
        }
        public void setDatas(List<Object> datas) {
            this.datas = datas;
        }
    
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            // datas.clear();//解析结束销毁不用的资源
        }
    }
    

      

  8. ExcleUtil中
    @Slf4j
    public class ExcelUtil {
      //  private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
        private static Sheet initSheet;
    
        static {
            initSheet = new Sheet(1, 0);
            initSheet.setSheetName("sheet");
            //设置自适应宽度
            initSheet.setAutoWidth(Boolean.TRUE);
        }
    
        /**
         * 读取少于1000行数据
         * @param filePath 文件绝对路径
         * @return
         */
        public static List<Object> readLessThan1000Row(String filePath){
            return readLessThan1000RowBySheet(filePath,null);
        }
    
        /**
         * 读小于1000行数据, 带样式
         * filePath 文件绝对路径
         * initSheet :
         *      sheetNo: sheet页码,默认为1
         *      headLineMun: 从第几行开始读取数据,默认为0, 表示从第一行开始读取
         *      clazz: 返回数据List<Object> 中Object的类名
         */
        public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet){
            if(!StringUtils.hasText(filePath)){
                return null;
            }
            Logger log = LoggerFactory.getLogger(ExcelUtil.class);
            sheet = sheet != null ? sheet : initSheet;
    
            InputStream fileStream = null;
            try {
                fileStream = new FileInputStream(filePath);
                return EasyExcelFactory.read(fileStream, sheet);
            } catch (FileNotFoundException e) {
                log.info("找不到文件或文件路径错误, 文件:{}", filePath);
            }finally {
                try {
                    if(fileStream != null){
                        fileStream.close();
                    }
                } catch (IOException e) {
                    log.info("excel文件读取失败, 失败原因:{}", e);
    
                }
            }
            return null;
        }
    
        /**
         * 读大于1000行数据
         * @param filePath 文件觉得路径
         * @return
         */
        public static List<Object> readMoreThan1000Row(String filePath){
            return readMoreThan1000RowBySheet(filePath,null);
        }
    
        /**
         * 读大于1000行数据, 带样式
         * @param filePath 文件觉得路径
         * @return
         */
        public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet){
            if(!StringUtils.hasText(filePath)){
                return null;
            }
            Logger log = LoggerFactory.getLogger(ExcelUtil.class);
            sheet = sheet != null ? sheet : initSheet;
    
            InputStream fileStream = null;
            try {
                fileStream = new FileInputStream(filePath);
                ExcelListener excelListener = new ExcelListener();
                EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
                return excelListener.getDatas();
            } catch (FileNotFoundException e) {
                log.error("找不到文件或文件路径错误, 文件:{}", filePath);
            }finally {
                try {
                    if(fileStream != null){
                        fileStream.close();
                    }
                } catch (IOException e) {
                    log.error("excel文件读取失败, 失败原因:{}", e);
                }
            }
            return null;
        }
    
        /**
         * 生成excle
         * @param filePath  绝对路径, 如:/home/chenmingjian/Downloads/aaa.xlsx
         * @param data 数据源
         * @param head 表头
         */
        public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head){
            writeSimpleBySheet(filePath,data,head,null);
        }
    
        /**
         * 生成excle
         * @param filePath 绝对路径, 如:/home/chenmingjian/Downloads/aaa.xlsx
         * @param data 数据源
         * @param sheet excle页面样式
         * @param head 表头
         */
        public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet){
            sheet = (sheet != null) ? sheet : initSheet;
    
            if(head != null){
                List<List<String>> list = new ArrayList<>();
                head.forEach(h -> list.add(Collections.singletonList(h)));
                sheet.setHead(list);
            }
    
            OutputStream outputStream = null;
            ExcelWriter writer = null;
            try {
                outputStream = new FileOutputStream(filePath);
                writer = EasyExcelFactory.getWriter(outputStream);
                writer.write1(data,sheet);
            } catch (FileNotFoundException e) {
               // log.error("找不到文件或文件路径错误, 文件:{}", filePath);
                System.out.println("找不到文件或文件路径错误, 文件:{}"+ filePath);
            }finally {
                try {
                    if(writer != null){
                        writer.finish();
                    }
    
                    if(outputStream != null){
                        outputStream.close();
                    }
    
                } catch (IOException e) {
                  //  log.error("excel文件导出失败, 失败原因:{}", e);
                    System.out.println("excel文件导出失败, 失败原因:{}");
                }
            }
    
        }
    
        /**
         * 生成excle
         * @param filePath 绝对路径, 如:/home/chenmingjian/Downloads/aaa.xlsx
         * @param data 数据源
         */
        public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){
            writeWithTemplateAndSheet(filePath,data,null);
        }
    
        /**
         * 生成excle
         * @param filePath 绝对路径, 如:/home/chenmingjian/Downloads/aaa.xlsx
         * @param data 数据源
         * @param sheet excle页面样式
         */
        public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){
            if(CollectionUtils.isEmpty(data)){
                return;
            }
    
            sheet = (sheet != null) ? sheet : initSheet;
            sheet.setClazz(data.get(0).getClass());
    
            OutputStream outputStream = null;
            ExcelWriter writer = null;
            try {
                outputStream = new FileOutputStream(filePath);
                writer = EasyExcelFactory.getWriter(outputStream);
                writer.write(data,sheet);
            } catch (FileNotFoundException e) {
              //  log.error("找不到文件或文件路径错误, 文件:{}", filePath);
                System.out.println("找不到文件或文件路径错误, 文件:{}"+ filePath);
            }finally {
                try {
                    if(writer != null){
                        writer.finish();
                    }
    
                    if(outputStream != null){
                        outputStream.close();
                    }
                } catch (IOException e) {
                  //  log.error("excel文件导出失败, 失败原因:{}", e);
                    System.out.println("excel文件导出失败, 失败原因:{}");
                }
            }
    
        }
    
        /**
         * 生成多Sheet的excle
         * @param filePath 绝对路径, 如:/home/chenmingjian/Downloads/aaa.xlsx
         * @param multipleSheelPropetys
         */
        public static void writeWithMultipleSheel(String filePath,List<MultipleSheelPropety> multipleSheelPropetys){
            if(CollectionUtils.isEmpty(multipleSheelPropetys)){
                return;
            }
    
            OutputStream outputStream = null;
            ExcelWriter writer = null;
            try {
                outputStream = new FileOutputStream(filePath);
                writer = EasyExcelFactory.getWriter(outputStream);
                for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                    Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                    if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){
                        sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                    }
                    writer.write(multipleSheelPropety.getData(), sheet);
                }
    
            } catch (FileNotFoundException e) {
               // log.error("找不到文件或文件路径错误, 文件:{}", filePath);
                System.out.println("找不到文件或文件路径错误, 文件:{}"+ filePath);
            }finally {
                try {
                    if(writer != null){
                        writer.finish();
                    }
    
                    if(outputStream != null){
                        outputStream.close();
                    }
                } catch (IOException e) {
                   // log.error("excel文件导出失败, 失败原因:{}", e);
                    System.out.println("excel文件导出失败, 失败原因:{}");
                }
            }
    
        }
    
    }
    

      

  9. JsonDateSerializer中
    public class JsonDateSerializer extends JsonSerializer<Date> {
        private SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        @Override
        public void serialize(Date date, JsonGenerator gen, SerializerProvider provider)
                throws IOException, JsonProcessingException {
            String value = dateFormat.format(date);
            gen.writeString(value);
        }
    }
    

      

  10. MultipleSheelPropety中
    @Data
    public  class MultipleSheelPropety {
    
        private List<? extends BaseRowModel> data;
      //可以通过实例获取该值
       // private List<Object> data = new ArrayList<Object>();
        private Sheet sheet;
    
        public List<? extends BaseRowModel> getData() {
            return data;
        }
        public void setDatas(List<? extends BaseRowModel> data) {
            this.data = data;
        }
    
        public Sheet getSheet() {
            return sheet;
        }
    
        public void setSheet(Sheet sheet) {
            this.sheet = sheet;
        }
    }
    

      

  11. DemoMapper.xml中
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.exceldemo.demo.mapper.DemoDAO" >
        <resultMap id="BaseResultMap" type="com.exceldemo.demo.entity.Em" >
            <id column="id" property="id" jdbcType="INTEGER" />
            <result column="name" property="name" jdbcType="VARCHAR" />
            <result column="sex" property="sex" jdbcType="TIMESTAMP" />
            <result column="age" property="age" jdbcType="INTEGER" />
            <result column="createDate" property="createDate" jdbcType="TIMESTAMP" />
        </resultMap>
        <!--01.查询所有记录-->
        <select id="findAll" resultType="com.exceldemo.demo.entity.Em">
             select * from em
         </select>
        <!--excel导入-->
        <insert id="addForExcel">
            insert into em(name,sex,age,createDate) values(#{name},#{sex},#{age},now())
        </insert>
    </mapper>
    

      

  12. html文件
    <!DOCTYPE html>
    <html lang="zh-CN" xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="utf-8" />
        <title>excel导入导出</title>
        <script type="text/javascript" th:src="@{/js/jquery-3.4.1.min.js}"></script>
    
    </head>
    <body>
    <!--表单提交-->
    <form action="/exce/import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" id="file"/>
        <input type="submit" value="表单提交">
    
    </form>
    <!--通过js提交-->
    <input type="file" name="file" id="file2"/>
    <input name="btnSubmit" type="button" value="JS导入" onclick="fnImpotExcel()"/>
    <br>
    <input name="btnSubmit" type="button" value="导出excel1(直接输出excel数据)" onclick="window.open('/exce/export')"/>
    <input name="btnSubmit" type="button" value="导出excel2(服务器上生成excel文件,返回文件地址)" onclick="fnExportExcel('/exce/exportserver')"/>
    </body>
    </html>
    <script>
        function fnImpotExcel(){
            var blob = document.getElementById('file2').files[0];
    
            var xhr = new XMLHttpRequest();
    
            xhr.onreadystatechange = function() {
                if(xhr.readyState == 4) {
                    if(xhr.responseText) {
                        alert(xhr.responseText);
                        // if(slices == 0) {
                        //  alert(2)
                        // }
                    }
    
                }
            };
    
    //构造form数据
            var fd = new FormData();
            fd.append("file", blob);
            fd.append("name", blob.name);
            xhr.open("POST", "/exce/import", false);
    
            //设置二进制文边界件头
            //xhr.setRequestHeader("X_Requested_With", location.href.split("/")[3].replace(/[^a-z]+/g, '$'));
            xhr.send(fd);
            /*
                $.ajax({
                    url: "/exce/import",
                    type: "post",
                    data: {field:document.getElementById('file2').files[0]},
                    success: function (data) {alert(data);
                        console.log(data);
                      //  data = decodeURIComponent(decodeURIComponent (data)).split(",");
                       // $.each(a,function (k,v) {
                         //   $("[data-id="+a[k]+"]").append(decodeURIComponent(decodeURIComponent (data[k])));
                       // })
                    }
                })
    
             */
        }
        //导出excel
        function fnExportExcel(url){
            $.ajax({
                url: url,
                type: "post",
                success: function (data) {
                    if(data!="") window.open("/"+data);
                }
            })
        }
    </script>
    

      

  13. SQL文件
    DROP TABLE IF EXISTS `em`;
    CREATE TABLE `em` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `sex` varchar(10) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `createDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of em
    -- ----------------------------
    INSERT INTO `em` VALUES ('3', '张西', '女', '63', '2019-11-07 23:02:28');
    INSERT INTO `em` VALUES ('4', '兰六', '男', '7', '2019-11-07 23:02:28');
    INSERT INTO `em` VALUES ('5', '兰六11', '男', '11', '2019-11-07 23:05:28');
    

      

作者:一生无过
来源链接:https://www.cnblogs.com/dinghaoran/p/12462671.html

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/server/42015.html

标签: EasyExcel
分享给朋友:

“Springboot使用EasyExcel(仅限自己收藏)” 的相关文章

POIEasyExcel

POI与EasyExcel 数据导出excel 数据导入程序 POI 数据加入内存再操作 消耗内存 // 工作簿 2003 65535行 Workbook workbook = new HSSFWorkbook...

EasyExcel导入导出

EasyExcel导入导出

maven依赖 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</g...

java使用EasyExcel操作Excel

  一、EasyExcel简介 1、EasyExcel特点 Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行,但是一旦并发上来...

28.EasyExcel操作excel进行读和写

28.EasyExcel操作excel进行读和写

 一、EasyExcel简介 1、Excel导入导出的应用场景 1、数据导入:减轻录入工作量 2、数据导出:统计信息归档 3、数据传输:异构系统之间数据传输 2、EasyExcel特点...

EasyExcel引入

EasyExcel引入

好久没更了,都在有道云上面记录,没时间搬过来。 easyexcel是最近项目做优化涉及的一个改善点吧。   简介         导出是后台管理系统的常用功能,...

easyexcel注解

11个注解 @ExcelProperty @ColumnWith 列宽 @ContentFontStyle 文本字体样式 @ContentLoopMerge 文本合并 @ContentRowHeight 文本行高度...

easyexcel往已存在的excel文件里追加数据

File templateFile = new File(filePath, fileName); File destFile = new File(filePath, "test.xlsx"); try { if...

EasyExcel简单使用

1.添加依赖 com.alibaba easyexcel 2.1.1...

excel开发

easyexcelhttps://www.jianshu.com/p/78cc1729fbd1 https://blog.csdn.net/daisy_apk/article/details/84635620invoke方法每解析一行执行一次,anal...

EasyExcel实现文件导出

官网:https://www.yuque.com/easyexcel/doc/easyexcel 导出 准备工作 引入依赖 <!--EasyExcel相关依赖--> <dependency> <groupId>com.alibab...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。