EasyExcel 通过模板导入、导出、下载模板

迈不过友情╰ 2024-02-05 19:53 92阅读 0赞

EasyExcel 通过模板 导入、导出、下载模板

import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import javax.validation.constraints.NotBlank;
    import javax.validation.constraints.Pattern;
    import java.io.Serializable;
    
    /**
     * @author ext.libo15
     * @title: 导入员工花名册
     */
    @Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @HeadRowHeight(value = 40)
    public class StudentExcel implements Serializable {
        
    
        private static final long serialVersionUID = -5144055068797033748L;
    
        /**
         * 姓名
         */
        @ExcelProperty(value = "姓名", index = 0)
        @ColumnWidth(value = 20)
        @NotBlank(message="姓名不能为空")
        private String name;
    
        /**
         * 性别 1：男 ：2女
         */
        @ExcelProperty(value = "性别\r1：男 ：2女", index = 1)
        @ColumnWidth(value = 20)
        @NotBlank(message="性别不能为空")
        private String gender;
    
        /**
         * 出生日期
         */
        @ExcelProperty(value = "出生日期", index = 2)
        @ColumnWidth(value = 20)
        @NotBlank(message="出生日期不能为空")
        @DateTimeStr(format ="yyyy-MM-dd", message = "出生日期格式错误，正确格式为：yyyy-MM-dd")
        private String birthday;
    
        /**
         * 年龄
         */
        @ExcelProperty(value = "年龄", index = 3)
        @ColumnWidth(value = 20)
        @NotBlank(message="年龄不能为空")
        private String age;
    
    
        /**
         * 身份证号码
         */
        @ExcelProperty(value = "身份证号码", index = 4)
        @ColumnWidth(value = 20)
        @NotBlank(message="身份证号码不能为空")
        @Pattern(regexp = "^\\d{15}$|^\\d{17}[0-9Xx]$",message = "身份证格式不正确!")//长度18，0-9
        private String cardNo;
    
        /**
         * 联系电话
         */
        @ExcelProperty(value = "联系电话", index = 5)
        @ColumnWidth(value = 20)
        @NotBlank(message="联系电话不能为空")
        @Pattern(regexp = "^[1][3,4,5,7,8][0-9]{9}$",message = "联系电话格式不正确!")//长度11
        private String telephone;
    
        /**
         * 是否已婚 1：是  2：否
         */
        @ExcelProperty(value = "是否已婚", index = 6)
        @ColumnWidth(value = 20)
        @NotBlank(message="是否已婚不能为空")
        private String marryStatus;
    
    
        /**
         * 银行卡号
         */
        @ExcelProperty(value = "银行卡号", index = 8)
        @ColumnWidth(value = 20)
        @NotBlank(message="银行卡号不能为空")
        @Pattern(regexp = "^([1-9]{1})(\\d{14}|\\d{18})$",message = "银行卡号格式不正确!")//长度11
        private String bankCardNo;

Student 对象（你的业务对象，就是数据库对应的对象）

public class Student implements Serializable {
        
    
        private static final long serialVersionUID = -5144055068797033748L;
    
        /**
         * 姓名
         */
        private String name;
    
        /**
         * 性别 1：男 ：2女
         */
        private int gender;
    
        /**
         * 出生日期
         */
        private Date birthday;
    
        /**
         * 年龄
         */
        private int age;
    
    
        /**
         * 身份证号码
         */
        private String cardNo;
    
        /**
         * 联系电话
         */
        private String telephone;
    
        /**
         * 是否已婚 1：是  2：否
         */
        private int marryStatus;
    
        /**
         * 银行卡号
         */
        private String bankCardNo;
    
        /**
         * 时间
         */
        private Date dimissionDate;

Controller 代码：

/**
         　　* 导入
         　　*/
        @RequestMapping(value="/import")
        public void importExcel(HttpServletRequest request, HttpServletResponse response,@RequestBody MultipartFile file) throws Exception {
        
            staffManageService.importExcel(file);
        }
    
    
        /**
         　　* 导出
         　　*/
        @RequestMapping(value="/export",method = RequestMethod.POST)
        public void batchExport(HttpServletResponse response) throws ParseException {
        
            staffManageService.export(response,staffRosterVo);
        }
        /**
         　　* 下载模板
         　　*
         　　* @param
         　　* @return
         　　* @throws Exception
         　　* @author ext.libo15
         　　*/
        @GetMapping(value="/download")
        public void download(HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException {
        
            FileUtil.downloadExcel(request,response);
        }
    }

2.下载模板  
这里其实 Controller + FileUtil 工具类，下载模板的操作已经做好了。工具类我放最下面了

\*\*

3.导入  
\*\*

/**
         * 导入
         * @param file
         */
        @Override
        public void importExcel(MultipartFile file) throws Exception {
        
                List<Student> ExcelList = null;
                String validate = "";
                //校验文件大小 100M
                boolean size = FileUtil.checkFileSize(file.getSize(), 100, "M");
                if(size == false){
        
                    //文件过大啦，只能100M
                }
                //excel读取数据
                staffExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(StaffDataExcel.class).sheet().doReadSync();
                if (ExcelList.size() > 1000) {
        
                    //最多导入1000条哦
                }
                // excel数据校验
                validate = ValidatorUtils.beanValidate(ExcelList);
                if(!validate.equals("success")){
        
                    //参数异常提示  如：姓名不能为空哦~
                }
                //把Excel数据转换成你对应的对象集合List
                List<Student> studentList = ExcelUtil.excelListConvertList(ExcelList);
                //拿到数据后就可以 入库操作啦 -----我自己的入库就不给你们写了
                StudentService.importExcel(studentList );
        }

4.导出

/**
         * 导出
         * @param response
         * @param staffRosterVo
         * @return
         * @throws ParseException
         */
        @Override
        public void export(HttpServletResponse response) throws ParseException {
        
            String fileName = "导出的文件名称";
            String sheetName = "导出的 sheetName ";
           
            List<Student> list = studentService.findList();
            List<StaffDataExportExcel> studentExcelList = ExcelUtil.listConvertexcelList(list );
            try {
        
               ExcelUtil.writeExcel(response, studentExcelList , fileName, sheetName, StaffDataExportExcel.class);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }

5.工具类 FileUtil ：

import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    
    public class FileUtil {
        
    
        /**
         * 判断文件大小
         *
         * @param len
         *            文件长度
         * @param size
         *            限制大小
         * @param unit
         *            限制单位（B,K,M,G）
         * @return
         */
        public static boolean checkFileSize(Long len, int size, String unit) {
        
    //        long len = file.length();
            double fileSize = 0;
            if ("B".equals(unit.toUpperCase())) {
        
                fileSize = (double) len;
            } else if ("K".equals(unit.toUpperCase())) {
        
                fileSize = (double) len / 1024;
            } else if ("M".equals(unit.toUpperCase())) {
        
                fileSize = (double) len / 1048576;
            } else if ("G".equals(unit.toUpperCase())) {
        
                fileSize = (double) len / 1073741824;
            }
            if (fileSize > size) {
        
                return false;
            }
            return true;
        }
    	
    	
        public static void downLoadFile(String filePath, String fileName, HttpServletResponse response) {
        
            try (InputStream inStream = new FileInputStream(filePath);
                 OutputStream os = response.getOutputStream()) {
        
                response.setHeader("Content-Disposition", "attachment; fileName=" + URLEncoder.encode(fileName, "UTF-8"));
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.setCharacterEncoding("utf-8");
                byte[] buff = new byte[100];
                int len;
                while ((len = inStream.read(buff)) > 0) {
        
                    os.write(buff, 0, len);
                }
                os.flush();
            } catch (Exception e) {
        
                e.Exception ();
            }
        }
    
    	//下载模板
        public static void downloadExcel(HttpServletRequest request, HttpServletResponse response){
        
            //方法一：直接下载路径下的文件模板（这种方式貌似在SpringCloud和Springboot中，打包成JAR包时，无法读取到指定路径下面的文件，不知道记错没，你们可以自己尝试下！！！）
            try {
        
            	//文件名称
                String fileName = "importStaffRoster.xlsx";
                //设置要下载的文件的名称
                response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
                //通知客服文件的MIME类型
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                //获取文件的路径
                String filePath = FileUtil.class.getResource("/template/" + fileName).getPath();
                FileInputStream input = new FileInputStream(filePath);
                OutputStream out = response.getOutputStream();
                byte[] b = new byte[2048];
                int len;
                while ((len = input.read(b)) != -1) {
        
                    out.write(b, 0, len);
                }
                //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
                response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
                input.close();
            } catch (Exception ex) {
        
                ex.printStackTrace();
            }
    
        }
    
    }

6.工具类 ValidatorUtils （导入的时候参数校验）

import cn.hutool.core.lang.Assert;
    import org.apache.commons.lang3.ObjectUtils;
    
    import javax.validation.ConstraintViolation;
    import javax.validation.Valid;
    import javax.validation.Validation;
    import javax.validation.Validator;
    import java.util.*;
    
    public class ValidatorUtils {
        
        private static final Validator validator = Validation.buildDefaultValidatorFactory()
                .getValidator();
    
        public static String beanValidate(@Valid Object obj) throws IllegalArgumentException {
        
            if (obj instanceof Collection) {
        
                ValidCollection<Object> validCollection = new ValidCollection<>();
                validCollection.setList((Collection) obj);
                return validate(validCollection);
            } else {
        
                return validate(obj);
            }
        }
    
        public static String validate(@Valid Object obj) {
        
            try {
        
                Map<String, String> validatedMsg = new HashMap<>();
                Set<ConstraintViolation<Object>> constraintViolations = validator.validate(obj);
                String validate = "";
                String message = "";
                for (ConstraintViolation<Object> c : constraintViolations) {
        
                    validate = c.getPropertyPath().toString();
                    validate = validate.substring(5,6);
                    int unm = Integer.valueOf(validate) +1;
                    validatedMsg.put( "表格第"+unm+"条 ", c.getMessage());
                }
                Assert.isTrue(ObjectUtils.isEmpty(constraintViolations), validatedMsg.toString());
            } catch (IllegalArgumentException e) {
        
                e.printStackTrace();
                return e.getMessage();
            }
            return "success";
        }
    
    
        private static class ValidCollection<T> {
        
            @Valid
            private Collection<T> list;
    
            public Collection<T> getList() {
        
                return list;
            }
    
            public void setList(Collection<T> list) {
        
                this.list = list;
            }
        }
    }

工具类

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExcelUtil {
        
        /**
         * 导出
         * @param response
         * @param data
         * @param fileName
         * @param sheetName
         * @param clazz
         * @throws Exception
         */
        public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        
            //表头样式
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置表头居中对齐
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置内容靠左对齐
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
        }
        private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            return response.getOutputStream();
        }
    
    	/*
    	* Excel转换实体类对象
    	*/
        public static List<Student> excelListConvertList(List<StudentExcel> studentExcelList) throws ParseException {
        
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            List<Student> list = new ArrayList<>();
            for (StudentExcel s : studentExcelList) {
        
                Student student = Student.builder()
                        .name(staffDataExcel.getname())
                        .age(staffDataExcel.age())
                        .city(staffDataExcel.getCity())
                        .sex(staffDataExcel.getsex().equals("男")?1:2)
                        .birthday(sdf.parse(staffDataExcel.getBirthday()))
                        .build();
                list.add(student);
            }
            return list ;
        }
    
    	/*
    	*  导出的List集合 转换 Excel
    	*/
        public static List<StudentExportExcel> listConvertexcelList(List<Student> student) throws ParseException {
        
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            List<StudentExcel> list= new ArrayList<>();
            for (Student s : student) {
        
                StudentExcel excel = StudentExcel.builder()
                        .name(staffDataExcel.getname())
                        .age(staffDataExcel.age())
                        .city(staffDataExcel.getCity())
                        .sex(staffDataExcel.getsex().equals("男")?1:2)
                        .birthday(sdf.parse(staffDataExcel.getBirthday()))
                        .build();
                        .build();
                list.add(excel);
            }
            return list;
        }
    
    }

7.校验日期注解

import javax.validation.Constraint;
    import javax.validation.Payload;
    import java.lang.annotation.*;
    
    @Target({
        ElementType.FIELD, ElementType.PARAMETER})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    @Constraint(validatedBy = CheckDateTimeCaseValidator.class)
    public @interface DateTimeStr {
        
    
        String message() default "{javax.validation.constraints.DateTimeStr.message}";
    
    
        String format() default "yyyy-MM-dd";
    
        Class<?>[] groups() default {
        };
    
        Class<? extends Payload>[] payload() default {
        };
    }