阿里巴巴开源组件Easy Excel的Excel导入导出

我会带着你远行 2024-04-01 13:11 109阅读 0赞

1.  导出  
    单Sheet导出
    
        @ApiOperation("导出")
            @PostMapping("export")
            public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
                
                List<StorageDailyReportNaturalGasMagic> search = this.getList();
        
                //写入下载命令
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
        
                // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
                String fileName = URLEncoder.encode("日报表", "UTF-8").replaceAll("\\+", "%20");
                //写入文件名
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
                try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), StorageDailyReportNaturalGasMagic.class).build()) {
                    //构建Sheet
                    WriteSheet writeSheet = EasyExcel.writerSheet(0, "天然气").build();
                    //将数据写入Sheet
                    excelWriter.write(search, writeSheet);
                }
            }
    
    多Sheet导出  
    
    
        @RequestMapping("exportExcel")
            @ApiOperation("数据库导出")
            public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
                List<NodeRedPlcDictMagic> list = this.getList();
                // 根据type分组
                Map<String, List<NodeRedPlcDictMagic>> groupBy = list.stream().collect(Collectors.groupingBy(NodeRedPlcDictMagic::getType));
        
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
                String fileName = URLEncoder.encode("PLC字典", "UTF-8").replaceAll("\\+", "%20");
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
                int i = 0;
                
                // 按照type分组导入到不同的Sheet中
                try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), NodeRedPlcDictMagic.class).build()) {
                    for (Map.Entry<String, List<NodeRedPlcDictMagic>> entry : groupBy.entrySet()) {
                        //构建Sheet
                        WriteSheet writeSheet = EasyExcel.writerSheet(i++, entry.getKey()).build();
                        //写入Sheet
                        excelWriter.write(entry.getValue(), writeSheet);
                    }
                }
            }
2.  导入