栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

easyexcel实现简单的上传下载

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

easyexcel实现简单的上传下载

需要导入的pom:

        
        
            com.alibaba
            easyexcel
            2.0.5
        
        
            com.alibaba
            fastjson
            1.2.71
        

controller:

    
    @RequestMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        response.setHeader("Content-Disposition",
                "attachment;fileName=" + URLEncoder.encode("地址.xlsx", "UTF-8"));
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(0).head(tAddressCity.class).build();
        List list = addressService.filefindAll();
        excelWriter.write(list, sheet);
        excelWriter.finish();
    }

    
    @RequestMapping("/downloadModel")
    public void downloadModel(HttpServletResponse response) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition",
                "attachment;fileName=" + URLEncoder.encode("模板.xlsx", "UTF-8"));
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(0).head(tAddressCity.class).build();
        excelWriter.write(Collections.emptyList(), sheet);
        excelWriter.finish();
    }

   @ApiOperation(value = "导入单个sheet数据")
    @PostMapping("/importSheet")
    public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
        try {
            InputStream inputStream = multipartFile.getInputStream();
            //异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
            EasyExcel.read(inputStream, tAddressCity.class, new UserDataListener(addressService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

监听器:
注意: 如何注入一个非被spring管理的类,需要手动注入才能使用

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.yjxxt.crm.bean.tAddressCity;
import com.yjxxt.crm.service.AddressService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;

public class UserDataListener extends AnalysisEventListener {
    private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
    
    private static final int BATCH_COUNT = 3000;
    List list = new ArrayList<>();
    private AddressService addressService;;
    
    public UserDataListener(AddressService addressService) {
        this.addressService = addressService;
    }
    @Override
    public void invoke(tAddressCity data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        LOGGER.info("所有数据解析完成!");
    }
    
    private void saveData() {
        LOGGER.info("{}条数据,开始存储数据库!", list.size());
        for (tAddressCity tAddressCity : list) {
            addressService.insert(tAddressCity);
            System.out.println(tAddressCity.toString());
        }

        LOGGER.info("存储数据库成功!");
    }
}

到处多张表sheet
    @ApiOperation(value = "导入单个sheet数据")
    @PostMapping("/importSheet")
    public void importSheet(@RequestParam("multipartFile") MultipartFile multipartFile) {
        try {
            InputStream inputStream = multipartFile.getInputStream();
            //异步读取所有sheet数据,可在sheet方法参数中指定sheet索引,sheet名称
            EasyExcel.read(inputStream, User.class, new UserDataListener(userService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @ApiOperation(value = "导入多个sheet数据")
    @PostMapping("/importSheets")
    public void importSheets(@RequestParam("multipartFile") MultipartFile multipartFile) {
        ExcelReader excelReader = null;
        try {
            InputStream inputStream = multipartFile.getInputStream();
            excelReader = EasyExcel.read(inputStream).build();
            ReadSheet readSheet1 =
                    //构建ExcelReader对象
                    EasyExcel.readSheet(0).head(User.class).registerReadListener(new UserDataListener(userService)).build();
            ReadSheet readSheet2 =
                    //构建ExcelReader对象
                    EasyExcel.readSheet(1).head(Achievement.class).registerReadListener(new AchievementDataListener(achievementService)).build();
            //这里注意 一定要把sheet1 sheet2 一起传进去
            excelReader.read(readSheet1, readSheet2);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
                excelReader.finish();
            }
        }
    }

    @ApiOperation(value = "导出数据", produces = "application/octet-stream")
    @PostMapping("/simpleWrite")
    public void simpleWrite(HttpServletResponse response) {
        userService.simpleWrite(response);
    }

    @ApiOperation(value = "分页导出数据", produces = "application/octet-stream")
    @PostMapping("/pageWrite")
    public void pageWrite(HttpServletResponse response) {
        userService.pageWrite(response);
    }

    @ApiOperation(value = "分页导出多sheet数据", produces = "application/octet-stream")
    @PostMapping("/pageWriteSheets")
    public void pageWriteSheets(HttpServletResponse response) {
        userService.pageWriteSheets(response);
    }

    @ApiOperation(value = "分页导出多sheet数据发送邮件")
    @PostMapping("/writeSendMail")
    public void writeSendMail(HttpServletResponse response) throws IOException {
        userService.writeSendMail(response);
    }
}

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/961939.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号