精华内容
下载资源
问答
  • EasyExcel导入导出

    2021-02-04 10:30:00
    推荐一篇排版舒服的公众号 EasyExcel,让 excel 导入导出更加简单

    推荐一篇排版舒服的公众号

    EasyExcel,让 excel 导入导出更加简单

    展开全文
  • easyExcel导入导出

    2021-06-03 21:30:46
    项目中会遇到导入Excel的需求,使用POI的话可能会比较麻烦,最近使用阿里的easyExcel过程记录下。下例为本地环境测试: 一、新建Springboot项目并添加依赖 easyExcel依赖: <dependency> <groupId>...

    项目中会遇到导入Excel的需求,使用POI的话可能会比较麻烦,最近使用阿里的easyExcel过程记录下。下例为本地环境测试:

    一、新建Springboot项目并添加依赖

    easyExcel依赖:

           <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>2.2.6</version>
          </dependency>
    

    lombok依赖(不是必需):

          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
              <optional>true</optional>
          </dependency>
    

    二、新建Model

     @Data
    public class Demo extends BaseRowModel {
    
      @ExcelProperty(index = 0, value = "ID")
      private String id;
    
      @ExcelProperty(index = 1,value = "姓名")
      private String name;
    
      @ExcelProperty(index = 2,value = "地址")
      private String address;
    
    }
    

    说明:easyExcel是根据index读取Excel中的每列数据,value可以不用写,但是为了清晰建议写上;如果给前端返回时也用此model,会返回cellStyleMap什么的,可以用@jsonIgnore注解忽略掉。
    本地Excel截图:
    在这里插入图片描述

    三、编写相关utils

    ExcelUtil

    public class ExcelUtil<T> {
    
        /***
         * 读取Excel方法
         * 要求类必须继承BaseRowModel
         */
        public static <T extends BaseRowModel> ArrayList<T> readExcel(MultipartFile excel, Class<T> clazz) {
            try {
                InputStream in = new BufferedInputStream(excel.getInputStream());
                GeneralExcelListener<T> listener = new GeneralExcelListener<>();
                ExcelReader excelReader = new ExcelReader(in, ExcelTypeEnum.XLS, listener);
                excelReader.read(new Sheet(1, 1, clazz));
                return listener.getSuccessDatas();
    
            } catch (Exception e) {
    
                return new ArrayList<T>();
            }
    
        }
    
        /**
         * 导出 Excel :一个 sheet,带表头
         *
         * @param response  HttpServletResponse
         * @param list      数据 list,每个元素为一个 BaseRowModel
         * @param fileName  导出的文件名
         * @param sheetName 导入文件的 sheet 名
         * @param object    映射实体类,Excel 模型
         */
        public static void writeExcel(HttpServletResponse response,
                                      List<? extends BaseRowModel> list,
                                      String fileName, String sheetName,
                                      BaseRowModel object) {
            ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
            Sheet sheet = new Sheet(1, 0, object.getClass());
            sheet.setSheetName(sheetName);
            writer.write(list, sheet);
            writer.finish();
    
        }
    
        /**
         * 导出文件时为Writer生成OutputStream
         */
        private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
            try {
                return response.getOutputStream();
            } catch (IOException e) {
                throw new RuntimeException("导出文件时为Writer生成OutputStream失败!");
            }
        }
    
        /**
         * 返回 ExcelReader
         *
         * @param excel         需要解析的 Excel 文件
         * @param excelListener new ExcelListener()
         */
        private static ExcelReader getReader(MultipartFile excel,
                                             ExcelListener excelListener) {
            String filename = excel.getOriginalFilename();
            if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
                throw new RuntimeException("文件格式错误!");
            }
            InputStream inputStream;
            try {
                inputStream = new BufferedInputStream(excel.getInputStream());
                return new ExcelReader(inputStream, null, excelListener, false);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
    }
    

    ExcelListener

    public class ExcelListener extends AnalysisEventListener {
    
        @Override
        public void invoke(Object o, AnalysisContext analysisContext) {
    
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    
        }
    }
    

    GeneralExcelListener

    public class GeneralExcelListener<T> extends AnalysisEventListener<T> {
        private final Logger logger = LoggerFactory.getLogger(this.getClass());
    
    
        //自定义用于暂时存储data。
        //可以通过实例获取该值
        private ArrayList<T> successDatas = new ArrayList<>();
    
        public GeneralExcelListener() {
    
        }
    
        @Override
        public void invoke(T o, AnalysisContext analysisContext) {
        //此处是读取Excel每行数据,可通过analysisContext进行分析
            successDatas.add(o);
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
    
        }
        public ArrayList<T> getSuccessDatas() {
            return successDatas;
        }
    }
    

    四、使用示例

    导入示例

    @PostMapping(value = "/import")
    public ModelAndView import(@RequestParam(value = "file", required = true) MultipartFile file) {
     
        ModelAndView mv = new ModelAndView();
        //一行代码即可获取解析得到的Excel中的数据
        List<Test> datas = ExcelUtil.readExcel(file, Test.class);
        //然后对获取的数据,在service层进行业务处理
        ResultMsg result = provShareConfigService.insertList(datas);
        return mv;
    }
    

    导出示例

    @PostMapping(value = "/export")
    public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject ob) {
        //前端传来的参数
        String date = ob.getString("date");
        String fileName = "退赔导出";
        String sheetName = "sheet1";
        //从数据库得到的list集合
        List<Test> list = testService.selectAll(date);
        //一行代码即可导出
        ExcelUtil.writeExcel(response, list, fileName, sheetName, new Test());
    }
    

    五、编写测试用例(导入)

    由于前端提供的一般都是MultipartFile,本地测试的话进行了转换。

        @Test
        public void test2() throws IOException {
            File file = new File("C:\\Users\\Light\\Desktop\\demo.xlsx");
            FileInputStream inputStream = new FileInputStream(file);
            MultipartFile multipartFile = new MockMultipartFile(file.getName(),file.getName(),
                    ContentType.APPLICATION_OCTET_STREAM.toString(),inputStream);
            //使用ExcelUtil读取
            List<Demo> datas = ExcelUtil.readExcel(multipartFile, Demo.class);
            System.out.println(datas);
        }
    

    执行后可以看到控制台打印信息:

    [Demo(id=1, name=张三, address=北京), Demo(id=2, name=李四, address=上海), Demo(id=3, name=王五, address=广州), Demo(id=4, name=田六, address=深圳)]
    

    拿到转换后的集合,可以对数据进行处理,然后进行持久化操作。导出自己可以做完持久化后进行尝试。

    参考网上教程: 原文地址

    展开全文
  • easyexcel 导入导出

    2019-10-30 16:21:51
    <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beta5</version> ...
    
     1. <dependency>
                    <groupId>com.alibaba</groupId>
                    <artifactId>easyexcel</artifactId>
                    <version>1.1.2-beta5</version>
                    <exclusions>
                        <exclusion>
                            <groupId>org.ow2.asm</groupId>
                            <artifactId>asm</artifactId>
                        </exclusion>
                    </exclusions>
                </dependency>
    
    
    
     2. @PostMapping(value="/Import")
            public Response importDispatch(@RequestParam("file") MultipartFile file) {
                long start = System.currentTimeMillis();
                Map<String,Object> result = excelUtil.readExcel(file, new DispatchExcelImport(),1);
                List<DispatchExcelImport> dispatchExcelImports = new ArrayList<>();
                Boolean flag = (Boolean) result.get("flag");
                Response response;
                List<Object> list = new ArrayList<>();
                if(flag){
                    list = (List<Object>) result.get("datas");
                    if(!CollectionUtils.isEmpty(list)){
                        dispatchExcelImports = (List)list;
                    }
                }else{
                    return BaseUtil.createResponse(ReturnCode.ERROR, "", Arrays.asList(ReturnMessage.EXCEL_HEAD_ERROR));
                }
               //do something
               
                long end = System.currentTimeMillis();
                System.out.println("=======================导入耗时:" + (end - start));
                return response;
            }
        
       
       
        	
     3. @GetMapping(value = "/Export")
                public void export(HttpServletResponse response, DispatchDTO dispatchDTO) {
                    Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
                    List<DispatchExcelExport> models = new ArrayList<>();
                    List<DispatchImport> exportDatas = dispatchImportDao.selectDataForExport(dispatchDTO);
                    if (CollectionUtils.isEmpty(exportDatas)) {
                        models.add(new DispatchExcelExport());
                    } else {
                        exportDatas.forEach(dispatchData -> {
                            DispatchExcelExport excelExport = new DispatchExcelExport();
                            BaseUtil.copy(dispatchData, excelExport);
                            models.add(excelExport);
                        });
                    }
                    map.put("wenjian", models);
                    excelUtil.createExcel(response, map, ExcelTypeEnum.XLSX, "wenjian");
                }
    
    
    
    
    
     4. @Data 
      public class DispatchExcelImport extends BaseRowModel {
            @ExcelProperty(value = "员编", index = 0)
            private String employeeId;
            @ExcelProperty(value = "姓名", index = 1)
            private String employeeName;
            @ExcelProperty(value = "身份证", index = 2)
            private String identityId;
            @ExcelProperty(value = "城市", index = 3)
            private String cityName; }
    
    
    
    
    
    
    package com.cmbchina.ccd.oa.socialsecurity.util;
    
    import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelReader;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.BaseRowModel;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.util.CollectionUtils;
    import com.cmbchina.ccd.oa.socialsecurity.model.bo.ExcelListener;
    import org.springframework.stereotype.Component;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.BufferedInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    @Component
    public class ExcelUtil {
        private static Sheet initSheet;
    
        static {
            initSheet = new Sheet(1, 0);
            initSheet.setSheetName("sheet");
            //设置自适应宽度
            initSheet.setAutoWidth(Boolean.TRUE);
        }
    
        /**
         * 读取少于1000行数据
         * 数据量少时,同步读取
         *
         * @param file 读取的文件
         * @return
         */
        public List<Object> readLessThan1000Row(MultipartFile file) throws IOException {
            return readLessThan1000RowBySheet(file, null);
        }
    
        /**
         * 读小于1000行数据
         * filePath 文件绝对路径
         * initSheet :
         * sheetNo: sheet页码,默认为1
         * headLineMun: 从第几行开始读取数据,默认为0, 表示从第一行开始读取
         * clazz: 返回数据List<Object> 中Object的类名
         */
        public List<Object> readLessThan1000RowBySheet(MultipartFile file, Sheet sheet) throws IOException {
            if (file == null) {
                //log.info("导入文件为空", file);
                return null;
            }
            sheet = sheet != null ? sheet : initSheet;
            InputStream fileStream = null;
            try {
                fileStream = file.getInputStream();
                return EasyExcelFactory.read(fileStream, sheet);
            } catch (FileNotFoundException e) {
                //log.info("文件有误, 文件:{}", file);
            } finally {
                try {
                    if (fileStream != null) {
                        fileStream.close();
                    }
                } catch (IOException e) {
                    //log.info("excel文件读取失败, 失败原因:{}", e);
                }
            }
            return null;
        }
    
        /**
         * 生成excle
         *
         * @param filePath 绝对路径, 如:/home/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 绝对路径
         * @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);
            } finally {
                try {
                    if (writer != null) {
                        writer.finish();
                    }
                    if (outputStream != null) {
                        outputStream.close();
                    }
    
                } catch (IOException e) {
                    //log.error("excel文件导出失败");
                }
            }
    
        }
    
        /**
         * @Description 导出excel 支持一张表导出多个sheet
         * @Param OutputStream 输出流
         * Map<String, List>  sheetName和每个sheet的数据
         * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
         */
        public void createExcel(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> sheetNameAndDateList, ExcelTypeEnum type, String fileName) {
            // if (checkParam(SheetNameAndDateList, type)) return;
            try {
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                //解决导出文件名中文乱码
                fileName = new String(fileName.getBytes(), "iso8859-1") + DateUtil.todayStr();
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + type.getValue());
                ServletOutputStream out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, type, true);
                setSheet(sheetNameAndDateList, writer);
                writer.finish();
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }
    
    
        /**
         * @Description //setSheet数据
         */
        private void setSheet(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelWriter writer) {
            int sheetNum = 1;
            for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : SheetNameAndDateList.entrySet()) {
                Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
                sheet.setSheetName(stringListEntry.getKey());
                writer.write(stringListEntry.getValue(), sheet);
                sheetNum++;
            }
        }
    
        /**
         * @Description 校验参数
         */
        private static boolean checkParam(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) {
            if (CollectionUtils.isEmpty(SheetNameAndDateList)) {
                //log.error("SheetNameAndDateList不能为空");
                return true;
            } else if (type == null) {
                //log.error("导出的excel类型不能为空");
                return true;
            }
            return false;
        }
    
        /**
         * 读取某个 sheet 的 Excel
         *
         * @param excel    文件
         * @param rowModel 实体类映射,继承 BaseRowModel 类
         * @return Excel 数据 list
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
            return readExcel(excel, rowModel, 1, 1);
        }
    
        /**
         * 读取某个 sheet 的 Excel
         *
         * @param excel    文件
         * @param rowModel 实体类映射,继承 BaseRowModel 类
         * @param sheetNo  sheet 的序号 从1开始
         * @return Excel 数据 list
         */
        public Map<String, Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
            Map<String, Object> result = new HashMap<>();
            ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
            //校验表头
            Boolean flag = false;
            //维护实体类中 没有@ExcelProperty 放置在最后,会被映射出null表头
            String head = excelListener.getImportHeads().replace("null,", "");
            if (head.equals(excelListener.getModelHeads())) {
                flag = true;
            }
            result.put("flag", flag);
            result.put("datas", excelListener.getDatas());
            return result;
        }
    
        /**
         * 读取某个 sheet 的 Excel
         *
         * @param excel       文件
         * @param rowModel    实体类映射,继承 BaseRowModel 类
         * @param sheetNo     sheet 的序号 从1开始
         * @param headLineNum 表头行数,默认为1
         * @return Excel 数据 list
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
            ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
            return excelListener.getDatas();
        }
    
        /**
         * 读取指定sheetName的Excel(多个 sheet)
         *
         * @param excel    文件
         * @param rowModel 实体类映射,继承 BaseRowModel 类
         * @return Excel 数据 list
         * @throws IOException
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
            ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            for (Sheet sheet : reader.getSheets()) {
                if (rowModel != null) {
                    sheet.setClazz(rowModel.getClass());
                }
                //读取指定名称的sheet
                if (sheet.getSheetName().contains(sheetName)) {
                    reader.read(sheet);
                    break;
                }
            }
            return excelListener.getDatas();
        }
    
        /**
         * 返回 ExcelReader
         *
         * @param excel         需要解析的 Excel 文件
         * @param excelListener new ExcelListener()
         * @throws IOException
         */
        private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
            String filename = excel.getOriginalFilename();
            if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
                InputStream is = new BufferedInputStream(excel.getInputStream());
                return new ExcelReader(is, null, excelListener, false);
            } else {
                return null;
            }
        }
    }
    
    
    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,428
精华内容 971
关键字:

easyexcel导入导出