欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

EasyExcel的导入导出总结

程序员文章站 2024-03-20 14:47:10
...

1、先在maven中引用EasyExcel的jar包

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

2、如何向单个sheet导出数据

1、定义导出数据对象,可以定义列名和列宽

public class Export {
	//忽略该字段
    @ExcelIgnore
    private Integer column1;
	//定义导出属性
    @ExcelProperty(index = 0, value = "列名")
    @ColumnWidth(10)
    private Integer column2;
}

2、执行导出操作

   @GetMapping(value = "/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<Export> list= new ArrayList<>();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //对中文文件名进行编码
        String fileName = URLEncoder.encode("文件名", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), Export.class).sheet("sheet名称").doWrite(list);
    }

3、如何向多个sheet导出数据

    @GetMapping(value = "/download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws IOException {
        ExcelWriter excelWriter = null;

        List<Export1> list1= new ArrayList<>();

        List<Export2> list2= new ArrayList<>();        
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("文件名", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //向第一个sheet写入数据
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1名称").head(Export1.class).build();
        excelWriter.write(list1, writeSheet1);
        //向第二个sheet写入数据
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2名称").head(Export2.class).build();
        excelWriter.write(list2, writeSheet2);
        excelWriter.finish();
    }

4、如何导入数据

1、首先要定义AnalysisEventListener的实现,如何将从Excel导入的数据进行处理

public class Import {
    @ExcelProperty(index = 0)
    private String index1;
    @ExcelProperty(index = 1)
    private String index2;
}
public class ExcelListener extends AnalysisEventListener<Import> {
    private Service service;
    private List<Object> list = new ArrayList<>();
    public ExcelListener(Service service) {
        super();
        this.service = service;
    }
    @Override
    public void invoke(Import data, AnalysisContext analysisContext) {
        //将Import数据转换成Object对象
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //调用service方法,将list中数据存入数据库
    }
}

2、定义SpringMVC代码如何处理上传的Excel文件

    @RequestMapping(value = "/import")
    @ResponseBody
    public Response import(MultipartFile file, HttpServletRequest request, HttpServletResponse response, ModelAndView mav) throws Exception {
    	//创建自定义AnalysisEventListener实现
        AnalysisEventListener listener = new ExcelListener(service);
        EasyExcel.read(multipartFileToFile(file), Import.class, listener).sheet().doRead();
        return Response.ok();
    }
    //将MultipartFile转换成File对象
    public static File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    public static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
相关标签: 实战案例