Easyexcel导入，导出多个sheet到多个excel文件并压缩到一个zip文件

Easyexcel导入导出，支持导出多个sheet到一个excel文件和导出多个sheet到多个excel文件并压缩到一个zip文件

本文地址：

  1. 可以为每个excel文件指定模板，如果模板不存在则使用默认导出
  2. 支持导出多个sheet到一个excel文件
  3. 导出多个sheet到多个excel文件并压缩到一个zip文件
  4. ExcelData和ExcelShellData只是为了简化参数指定文件名、模板名和数据列表、sheet列表

maven导入Easyexcel依赖

        
            com.alibaba
            easyexcel
            3.0.5
            
                
                    org.apache.poi
                    poi
                
                
                    org.apache.poi
                    poi-ooxml-schemas
                
            
        
        
        
            org.apache.poi
            poi-ooxml
            4.1.2

例子代码结构

模板内容

导出效果

  • 访问 http://localhost:8080/excel/export
  • 访问 http://localhost:8080/excel/export/zip

创建三个实体类数据（Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据）

  • Excel1Sheet1.java
package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet1 {
    @ExcelProperty(value = "用户账号", order = 2)
    private String userName;
    @ExcelProperty(value = "用户密码", order = 3)
    private String password;
}
  • Excel1Sheet2.java
package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet2 {
    @ExcelProperty(value = "姓名", order = 1)
    private String realName;
    @ExcelProperty(value = "毕业学校", order = 6)
    private String schoolName;
}
  • Excel2Sheet1.java
package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel2Sheet1 {
    @ExcelProperty(value = "姓名", order = 1)
    private String realName;
    @ExcelProperty(value = "政治面貌", order = 3)
    private String politicalStatus;
}

控制层ExcelController.java

package com.example.demo.excel.controller;

import com.example.demo.excel.entity.Excel1Sheet1;
import com.example.demo.excel.entity.Excel1Sheet2;
import com.example.demo.excel.entity.Excel2Sheet1;
import com.example.demo.excel.util.ExcelUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/excel")
public class ExcelController {

    // 导出到一个压缩文件 包含两个excel，其中一个包含两个sheet，另一个使用模块
    @GetMapping("/export/zip")
    public void exportZip(HttpServletResponse response) {
        List excelData = exportAllData();
        ExcelUtils.exportZip("压缩导出", excelData, response);
    }

    // 导出到一个excel文件 包含两个sheet
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response) {
        List excelData = exportAllData();
        ExcelUtils.ExcelData excelA = excelData.get(0);//只要excelA
        ExcelUtils.exportExcel(excelA, response);
    }

    // 构建数据 Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据
    public List exportAllData() {
        List excelDataList = new ArrayList<>();
        //excelA
        ExcelUtils.ExcelData excelA = new ExcelUtils.ExcelData();
        excelA.setFilename("excelA");
        //excelA->Excel1Sheet1
        List excel1Sheet1s = new ArrayList<>();
        excel1Sheet1s.add(new Excel1Sheet1("azi", "pwd123"));
        excel1Sheet1s.add(new Excel1Sheet1("ruphy", "123456"));
        excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet1s, "Excel1Sheet1", Excel1Sheet1.class));
        //excelA->Excel1Sheet2
        List excel1Sheet2s = new ArrayList<>();
        excel1Sheet2s.add(new Excel1Sheet2("阿紫", "北大"));
        excel1Sheet2s.add(new Excel1Sheet2("子安", "清华"));
        excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet2s, "Excel1Sheet2", Excel1Sheet2.class));
        excelDataList.add(excelA);

        //excelB
        ExcelUtils.ExcelData excelB = new ExcelUtils.ExcelData();
        excelB.setFilename("excelB");//设置excelB的文件名
        excelB.setTemplateFilename("excel-template/excelB.xls");//设置excelB的模板
        //excelB-Excel2Sheet1
        List excel2Sheet1s = new ArrayList<>();
        excel2Sheet1s.add(new Excel2Sheet1("阿紫", "群众"));
        excel2Sheet1s.add(new Excel2Sheet1("子安", "武当会员"));
        excelB.addShellData(new ExcelUtils.ExcelShellData<>(excel2Sheet1s, "Excel2Sheet1", Excel2Sheet1.class));//设置excelB的sheet数据
        excelDataList.add(excelB);
        return excelDataList;
    }
}

导出工具类ExcelUtils.java

点击查看代码 
package com.example.demo.excel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Excel相关处理
 *
 * @author 子安
 */
public class ExcelUtils {

    /**
     * 同步导入(适用于小数据量)
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public static  List importExcel(InputStream is, Class clazz) {
        return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
    }

    /**
     * 导出excel
     *
     * @param list     导出数据集合
     * @param filename 工作表的名称
     * @return 结果
     */
    public static  void exportExcel(List list, String filename, Class clazz, HttpServletResponse response) {
        try {
            response.reset();
            setAttachmentResponseHeader(response, filename);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
            ServletOutputStream os = response.getOutputStream();
            EasyExcel.write(os, clazz).autoCloseStream(false)
                    // 自动适配
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 大数值自动转换 防止失真
                    .registerConverter(new ExcelBigNumberConvert()).sheet(filename).doWrite(list);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    /**
     * 导出多个sheet到一个excel文件  支持模板和直接创建
     */
    public static void exportExcel(ExcelData excelData, HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        try {
            String filename = excelData.getFilename();
            response.reset();
            setAttachmentResponseHeader(response, filename);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
            ServletOutputStream os = response.getOutputStream();
            ExcelWriterBuilder writerBuilder = EasyExcel.write(os).autoCloseStream(false)
                    // 自动适配
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 大数值自动转换 防止失真
                    .registerConverter(new ExcelBigNumberConvert());
            if (excelData.getTemplateFile() != null) {
                writerBuilder.withTemplate(excelData.getTemplateFile());
            }
            excelWriter = writerBuilder.build();
            if (excelData.getTemplateFile() != null) {
                // 注意sheet名称要与模板文件的sheet名称一一对应
                for (ExcelShellData<?> shellData : excelData.getShellDataList()) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(shellData.getSheetName()).build();
                    //FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                    excelWriter.fill(shellData.getList(), writeSheet);
                }
            } else {
                //开始写入excel
                for (ExcelShellData<?> shellData : excelData.getShellDataList()) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(shellData.getSheetName()).head(shellData.getClazz()).build();
                    excelWriter.write(shellData.getList(), writeSheet);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常", e);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 导出多个sheet到多个excel文件，并压缩到一个zip文件 支持模板和直接创建
     */
    public static void exportZip(String zipFilename, List excelDataList, HttpServletResponse response) {
        if (zipFilename == null || zipFilename.isEmpty()) {
            zipFilename = "export";
        } else if (zipFilename.toLowerCase(Locale.ROOT).endsWith(".zip")) {
            zipFilename = zipFilename.substring(0, zipFilename.length() - 4);
        }
        if (excelDataList == null || excelDataList.isEmpty()) {
            throw new RuntimeException("导出数据为空！");
        }
        try {
            // 这里URLEncoder.encode可以防止中文乱码
            zipFilename = URLEncoder.encode(zipFilename, "utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + zipFilename + ".zip");
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            //开始存入
            try (ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream())) {
                try {
                    for (ExcelData excelData : excelDataList) {
                        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                        ExcelWriter excelWriter = null;
                        try {
                            ExcelWriterBuilder builder = EasyExcel.write(outputStream).autoCloseStream(false)
                                    // 自动适配
                                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                                    // 大数值自动转换 防止失真
                                    .registerConverter(new ExcelBigNumberConvert());
                            if (excelData.getTemplateFile() != null) {
                                builder.withTemplate(excelData.getTemplateFile());
                            }
                            excelWriter = builder.build();
                            zipOut.putNextEntry(new ZipEntry(excelData.getFilename()));
                            if (excelData.getTemplateFile() != null) {
                                for (ExcelShellData<?> shellData : excelData.getShellDataList()) {
                                    WriteSheet writeSheet = EasyExcel.writerSheet(shellData.getSheetName()).build();
                                    //FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                                    excelWriter.fill(shellData.getList(), writeSheet);
                                }
                            } else {
                                //开始写入excel
                                for (ExcelShellData<?> shellData : excelData.getShellDataList()) {
                                    WriteSheet writeSheet = EasyExcel.writerSheet(shellData.getSheetName()).head(shellData.getClazz()).build();
                                    excelWriter.write(shellData.getList(), writeSheet);
                                }
                            }
                        } catch (Exception e) {
                            throw new RuntimeException("导出Excel异常", e);
                        } finally {
                            if (excelWriter != null) {
                                excelWriter.finish();
                            }
                        }
                        outputStream.writeTo(zipOut);
                        zipOut.closeEntry();
                    }

                } catch (Exception e) {
                    throw new RuntimeException("导出Excel异常", e);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    private static void setAttachmentResponseHeader(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
        String encode = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
        String percentEncodedFileName = encode.replaceAll("\\+", "%20");
        String contentDispositionValue = "attachment; filename=" +
                percentEncodedFileName + ";filename*=utf-8''" +
                percentEncodedFileName;

        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue);
        response.setHeader("download-filename", percentEncodedFileName);
    }

    /**
     * Excel 数值长度位15位 大于15位的数值转换位字符串
     */
    public static class ExcelBigNumberConvert implements Converter {

        @Override
        public Class supportJavaTypeKey() {
            return Long.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }

        @Override
        public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            Object data = cellData.getData();
            if (data == null) {
                return null;
            }
            String s = String.valueOf(data);
            if (s.matches("^\\d+$")) {
                return Long.parseLong(s);
            }
            return null;
        }

        @Override
        public WriteCellData convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            if (object != null) {
                String str = object.toString();
                if (str.length() > 15) {
                    return new WriteCellData<>(str);
                }
            }
            WriteCellData cellData = new WriteCellData<>(new BigDecimal(object));
            cellData.setType(CellDataTypeEnum.NUMBER);
            return cellData;
        }

    }

    /**
     * excel数据
     */
    public static class ExcelData {
        private List> shellDataList = new ArrayList<>();
        private String filename = "export.xlsx";
        private String templateFilename;

        public List> getShellDataList() {
            return shellDataList;
        }

        public void setFilename(String filename) {
            this.filename = filename;
        }

        public String getTemplateFilename() {
            return templateFilename;
        }

        public void setTemplateFilename(String templateFilename) {
            this.templateFilename = templateFilename;
        }

        public File getTemplateFile() {
            if (templateFilename == null) {
                return null;
            }
            File templateFile = new File(templateFilename);
            if (templateFile.exists() && templateFile.isFile()) {
                return templateFile;
            }
            Resource resource = new ClassPathResource(templateFilename);
            if (resource.exists()) {
                File file = null;
                try {
                    file = resource.getFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                if (file != null) {
                    if (file.exists() && file.isFile()) {
                        return file;
                    }
                    return null;
                }
            }
            return null;
        }

        public void setShellDataList(List> shellDataList) {
            if (shellDataList != null) {
                this.shellDataList = shellDataList;
            }
        }

        public String getFilename() {
            if (filename == null || filename.isEmpty()) {
                filename = "export.xlsx";
            } else {
                String fn = filename.toLowerCase(Locale.ROOT);
                if (!(fn.endsWith(".xlsx") || fn.endsWith(".xls"))) {
                    filename = filename + ".xlsx";
                }
            }
            return filename;
        }

        public void addShellData(ExcelShellData<?> excelShellData) {
            this.shellDataList.add(excelShellData);
        }
    }

    /**
     * sheet数据
     */
    public static class ExcelShellData {
        private List list;
        private String sheetName;
        private Class clazz;

        public ExcelShellData(List list, String sheetName, Class clazz) {
            this.list = list;
            this.sheetName = sheetName;
            this.clazz = clazz;
        }

        public List getList() {
            return list;
        }

        public void setList(List list) {
            this.list = list;
        }

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public Class getClazz() {
            return clazz;
        }

        public void setClazz(Class clazz) {
            this.clazz = clazz;
        }
    }

}



						  
					  
						
							


						

							
							
			java							
						



						

						
						


						

						
					
					

					
					

						
相关

						

				
						
			

																
								

										
JavaWeb的三大作用域

									
									

								 

        

								

							
							

																
								

										
java中常用数组copy方法

									
									

								 

        

								

							
							

																
								

										
6.Java方法

									
									

								 

        

								

							
							

																
								

										
Java web开发：从零到 Run 一个现有的 Spring Boot 前后端分离项目（前端Vue）

									
									

								 

        

								

							
							

																
								

										
javascript基础知识(29) 实现继承的方式

									
									

								 

        

								

							
							

																
								

										
java  正则表达式

									
									

								 

        

								

							
							

																
								

										
JAVA List<Object[]>取值问题

									
									

								 

        

								

							
							

																
								

										
初次接触Java感受

									
									

								 

        

								

							
							

																
								

										
99乘法表   java for循环

									
									

								 

        

								

							
							

																
								

										
基于java.net.socket包的对象传递

									
									

								 

        

								

							
							

																
								

										
Idea Java项目文件夹后面提示百分数怎么去掉

									
									

								 

        

								

							
							

																
								

										
Java8新特性

									
									

								 

        

								

							
							
            
            
            
                                    
           
  			
						

					

					
				

				

														
					
					

					
									

					
									   
标签

										

										
    
										

									

										
										

									

					

					
				


			
		
		
		
			


						





	

	
一品网 冀ICP备14022925号-6