springboot项目导出excel 合并单元格表格

痛定思痛。 2023-01-22 11:53 356阅读 0赞

### springboot项目导出excel 合并单元格表格 ###

*   *  导出效果
     *  业务controller
     *  业务数据
     *  业务实体类
     *  注解MyExcel.java
     *  注解 MyExcels
     *  导出工具类MyExcelUtil.java

## 导出效果 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70][]

## 业务controller ##

/**
         * 导出学员学习数据统计列表
         */
        @RequiresPermissions("project:userLearningData:export")
        @PostMapping("/export")
        @ResponseBody
        public AjaxResult export(UserLearningData userLearningData)
        {
            PageHelper.startPage(1, 10, null);
            List<UserLearningData> list = userLearningDataService.selectUserLearningDataList(userLearningData);
            MyExcelUtil<UserLearningData> util = new MyExcelUtil<UserLearningData>(UserLearningData.class);
            return util.exportExcel(list, "userLearningData",true);
        }

## 业务数据 ##

/**
         * 查询学员学习数据统计列表
         * 
         * @param userLearningData 学员学习数据统计信息
         * @return 学员学习数据统计集合
         */
    	@Override
    	public List<UserLearningData> selectUserLearningDataList(UserLearningData userLearningData)
    	{
    		List<UserLearningData> list = userLearningDataMapper.selectUserLearningDataList(userLearningData);
    		if (CollectionUtils.isNotEmpty(list)) {
    			for (UserLearningData uld : list) {
    				//用户名
    				AppUser appUser = appUserMapper.selectUserById(uld.getUserId());
    				uld.setUserName(appUser.getUserName());
    				//年份 激活日期
    				UserExtend userExtend = userExtendMapper.selectUserExtendById(uld.getUserExtendId());
    				uld.setActivateDate(userExtend.getActivateDate());
    				uld.setYear(userExtend.getYear());
    				//商品等级
    				Goods goods = goodsMapper.selectGoodsById(userExtend.getGoodsId());
    				uld.setGoodsLevel(goods.getGoodsLevel());
    				//学习进度
    				Long learningDuration = uld.getLearningDuration();
    				Long totalDuration = uld.getTotalDuration();
    				if (learningDuration != null && totalDuration != null && totalDuration != 0) {
    					double v = learningDuration.doubleValue() / totalDuration * 100;
    					BigDecimal bigDecimal = BigDecimal.valueOf(v);
    					BigDecimal decimal = bigDecimal.setScale(1, BigDecimal.ROUND_HALF_UP);
    					uld.setLearningRate(decimal);
    				}
    				//听课时长
    				Long listeningDuration = uld.getListeningDuration();
    				double v1 = listeningDuration.doubleValue() / 60 / 60;
    				long round = Math.round(v1);
    				uld.setListeningDuration(round);
    				//科目成绩信息
    				List<UserExaminationResults> userExaminationResults = userExaminationResultsMapper.selectUserExaminationByUserExtendId(uld.getUserExtendId());
    				if (CollectionUtils.isNotEmpty(userExaminationResults)) {
    					List<Map> scoreParams = new ArrayList<>();
    					int size = userExaminationResults.size();
    					for (int i = 0; i < size; i++) {
    						UserExaminationResults uer = userExaminationResults.get(i);
    						Map m = new HashMap(4);
    						m.put("actualScore",uer.getActualScore());
    						m.put("estimatedScore",uer.getEstimatedScore());
    						m.put("accuracy",uer.getAccuracy());
    						scoreParams.add(m);
    					}
    					uld.setScoreParams(scoreParams);
    				}
    				//反馈意见
    				UserFeedback uf = userFeedbackMapper.selectUserFeedbackByUserExtendId(uld.getUserExtendId());
    				if (uf != null) {
    					// 获取意见内容
    					String opinionContent = uf.getOpinionContent();
    					if (StringUtil.isNotEmptyString(opinionContent)) {
    						//查询指定编码的内容
    						String opinion = dictMapper.selectDictByKeyCode(Convert.toStrArray(opinionContent));
    						uld.setOpinionContent(opinion);
    					}
    					//获取 其他 的内容
    					if (StringUtil.isNotEmptyString(uf.getOtherContent())) {
    						uld.setOtherContent(uf.getOtherContent());
    					}
    				}
    			}
    		}
    		return list;
    	}

## 业务实体类 ##

package com.dongao.project.userlearningdata.domain;
    
    import com.dongao.project.annotation.MyExcel;
    import com.dongao.project.annotation.MyExcels;
    import com.ruoyi.framework.web.domain.BaseEntity;
    import org.apache.commons.lang3.builder.ToStringBuilder;
    import org.apache.commons.lang3.builder.ToStringStyle;
    
    import java.math.BigDecimal;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    /**
     * 学员学习数据统计表 qs_study_user_learning_data
     * 
     * @author dongao
     * @date 2021-05-17
     */
    public class UserLearningData extends BaseEntity
    {
    	private static final long serialVersionUID = 1L;
    	
    	/** 主键id */
    	private Long id;
    	/** 用户拓展id */
    	private Long userExtendId;
    	/** 用户id */
    	private Long userId;
    	/** 已学学习时长 */
    	private Long learningDuration;
    	/** 总学习时长 */
    	private Long totalDuration;
    	/** 听课次数 */
    	private Integer listeningNumber;
    	/** 做题多少套 */
    	private Integer paperNumber;
    	/** 展示的老师ids 逗号分隔 */
    	private String showTeachers;
    	/** 备考总的知识点 */
    	private Integer allKnowledge;
    	/** 已掌握的知识点数 */
    	private Integer masterKnowledge;
    	/** 逻辑删除0.无效1.有效 */
    	private Integer isValid;
    	/** 用户名 */
    	@MyExcel(name="用户名")
    	private String userName;
    	/** 年份 */
    	@MyExcel(name="年份")
    	private Integer year;
    	/** 商品等级 */
    	@MyExcel(name="商品等级",readConverterExp="1=单老师,2=全老师")
    	private Integer goodsLevel;
    	/** 激活时间 */
    	@MyExcel(name="激活时间",dateFormat = "yyyy-MM-dd HH:mm:ss")
    	private Date activateDate;
    	/**学习进度*/
    	@MyExcel(name="学习进度(%)")
    	private BigDecimal learningRate;
    	/** 听课时长单位s */
    	@MyExcel(name="听课(h)")
    	private Long listeningDuration;
    	/** 做题题数 */
    	@MyExcel(name="刷题")
    	private Integer questionNumber;
    	@MyExcels({
    			@MyExcel(name="实际分数",targetAttr = "0.actualScore",parentName = "初级会计实务"),
    			@MyExcel(name="预估分数",targetAttr = "0.estimatedScore",parentName = "初级会计实务"),
    			@MyExcel(name="正确率",targetAttr = "0.accuracy",parentName = "初级会计实务"),
    			@MyExcel(name="实际分数",targetAttr = "1.actualScore",parentName = "经济法基础"),
    			@MyExcel(name="预估分数",targetAttr = "1.estimatedScore",parentName = "经济法基础"),
    			@MyExcel(name="正确率",targetAttr = "1.accuracy",parentName = "经济法基础")
    	})
    	private List<Map> scoreParams;
    	/** 反馈的内容 */
    	@MyExcel(name="反馈")
    	private String opinionContent;
    	/** 当勾选了其他之后输入的内容 */
    	@MyExcel(name="其他")
    	private String otherContent;
    
    	public List<Map> getScoreParams() {
    		return scoreParams;
    	}
    
    	public void setScoreParams(List<Map> scoreParams) {
    		this.scoreParams = scoreParams;
    	}
    
    	public void setId(Long id)
    	{
    		this.id = id;
    	}
    
    	public Long getId()
    	{
    		return id;
    	}
    	public void setUserExtendId(Long userExtendId)
    	{
    		this.userExtendId = userExtendId;
    	}
    
    	public Long getUserExtendId()
    	{
    		return userExtendId;
    	}
    	public void setUserId(Long userId)
    	{
    		this.userId = userId;
    	}
    
    	public Long getUserId()
    	{
    		return userId;
    	}
    	public void setLearningDuration(Long learningDuration)
    	{
    		this.learningDuration = learningDuration;
    	}
    
    	public Long getLearningDuration()
    	{
    		return learningDuration;
    	}
    	public void setTotalDuration(Long totalDuration)
    	{
    		this.totalDuration = totalDuration;
    	}
    
    	public Long getTotalDuration()
    	{
    		return totalDuration;
    	}
    	public void setListeningDuration(Long listeningDuration)
    	{
    		this.listeningDuration = listeningDuration;
    	}
    
    	public Long getListeningDuration()
    	{
    		return listeningDuration;
    	}
    	public void setListeningNumber(Integer listeningNumber)
    	{
    		this.listeningNumber = listeningNumber;
    	}
    
    	public Integer getListeningNumber()
    	{
    		return listeningNumber;
    	}
    	public void setPaperNumber(Integer paperNumber)
    	{
    		this.paperNumber = paperNumber;
    	}
    
    	public Integer getPaperNumber()
    	{
    		return paperNumber;
    	}
    	public void setQuestionNumber(Integer questionNumber)
    	{
    		this.questionNumber = questionNumber;
    	}
    
    	public Integer getQuestionNumber()
    	{
    		return questionNumber;
    	}
    	public void setShowTeachers(String showTeachers)
    	{
    		this.showTeachers = showTeachers;
    	}
    
    	public String getShowTeachers()
    	{
    		return showTeachers;
    	}
    	public void setAllKnowledge(Integer allKnowledge)
    	{
    		this.allKnowledge = allKnowledge;
    	}
    
    	public Integer getAllKnowledge()
    	{
    		return allKnowledge;
    	}
    	public void setMasterKnowledge(Integer masterKnowledge)
    	{
    		this.masterKnowledge = masterKnowledge;
    	}
    
    	public Integer getMasterKnowledge()
    	{
    		return masterKnowledge;
    	}
    	public void setIsValid(Integer isValid)
    	{
    		this.isValid = isValid;
    	}
    
    	public Integer getIsValid()
    	{
    		return isValid;
    	}
    
    	public String getUserName() {
    		return userName;
    	}
    
    	public void setUserName(String userName) {
    		this.userName = userName;
    	}
    
    	public Integer getYear() {
    		return year;
    	}
    
    	public void setYear(Integer year) {
    		this.year = year;
    	}
    
    	public Date getActivateDate() {
    		return activateDate;
    	}
    
    	public void setActivateDate(Date activateDate) {
    		this.activateDate = activateDate;
    	}
    
    	public Integer getGoodsLevel() {
    		return goodsLevel;
    	}
    
    	public void setGoodsLevel(Integer goodsLevel) {
    		this.goodsLevel = goodsLevel;
    	}
    
    	public BigDecimal getLearningRate() {
    		return learningRate;
    	}
    
    	public void setLearningRate(BigDecimal learningRate) {
    		this.learningRate = learningRate;
    	}
    
    	public String getOpinionContent() {
    		return opinionContent;
    	}
    
    	public void setOpinionContent(String opinionContent) {
    		this.opinionContent = opinionContent;
    	}
    
    	public String getOtherContent() {
    		return otherContent;
    	}
    
    	public void setOtherContent(String otherContent) {
    		this.otherContent = otherContent;
    	}
    }

## 注解MyExcel.java ##

package com.dongao.project.annotation;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * 自定义导出Excel数据注解
     * 
     * @author ruoyi
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    public @interface MyExcel
    {
        /**
         * 导出到Excel中的名字.
         */
        public String name() default "";
        /**
         * 导出到Excel中的名字.
         */
        public String parentName() default "";
    
        /**
         * 日期格式, 如: yyyy-MM-dd
         */
        public String dateFormat() default "";
    
        /**
         * 读取内容转表达式 (如: 0=男,1=女,2=未知)
         */
        public String readConverterExp() default "";
    
        /**
         * 导出类型（0数字 1字符串）
         */
        public ColumnType cellType() default ColumnType.STRING;
    
        /**
         * 导出时在excel中每个列的高度 单位为字符
         */
        public double height() default 14;
    
        /**
         * 导出时在excel中每个列的宽 单位为字符
         */
        public double width() default 16;
    
        /**
         * 文字后缀,如% 90 变成90%
         */
        public String suffix() default "";
    
        /**
         * 当值为空时,字段的默认值
         */
        public String defaultValue() default "";
    
        /**
         * 提示信息
         */
        public String prompt() default "";
    
        /**
         * 设置只能选择不能输入的列内容.
         */
        public String[] combo() default {};
    
        /**
         * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
         */
        public boolean isExport() default true;
    
        /**
         * 另一个类中的属性名称,支持多级获取,以小数点隔开
         */
        public String targetAttr() default "";
    
        /**
         * 字段类型（0：导出导入；1：仅导出；2：仅导入）
         */
        Type type() default Type.ALL;
    
        public enum Type
        {
            ALL(0), EXPORT(1), IMPORT(2);
            private final int value;
    
            Type(int value)
            {
                this.value = value;
            }
    
            public int value()
            {
                return this.value;
            }
        }
    
        public enum ColumnType
        {
            NUMERIC(0), STRING(1);
            private final int value;
    
            ColumnType(int value)
            {
                this.value = value;
            }
    
            public int value()
            {
                return this.value;
            }
        }
    }

## 注解 MyExcels ##

## 导出工具类MyExcelUtil.java ##

package com.dongao.project.utils.poi;
    
    import com.dongao.project.annotation.MyExcel;
    import com.dongao.project.annotation.MyExcels;
    import com.dongao.project.utils.StringUtil;
    import com.ruoyi.common.exception.BusinessException;
    import com.ruoyi.common.utils.DateUtils;
    import com.ruoyi.common.utils.StringUtils;
    import com.ruoyi.framework.config.RuoYiConfig;
    import com.ruoyi.framework.web.domain.AjaxResult;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.text.DecimalFormat;
    import java.util.*;
    
    /**
     * Excel相关处理
     * 
     * @author ruoyi
     */
    public class MyExcelUtil<T>
    {
        private static final Logger log = LoggerFactory.getLogger(com.ruoyi.common.utils.poi.ExcelUtil.class);
    
        /**
         * Excel sheet最大行数，默认65536
         */
        public static final int sheetSize = 65536;
    
        /**
         * 工作表名称
         */
        private String sheetName;
    
        /**
         * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
         */
        private MyExcel.Type type;
    
        /**
         * 工作薄对象
         */
        private Workbook wb;
    
        /**
         * 工作表对象
         */
        private Sheet sheet;
    
        /**
         * 样式列表
         */
        private Map<String, CellStyle> styles;
    
        /**
         * 导入导出数据列表
         */
        private List<T> list;
    
        /**
         * 注解列表
         */
        private List<Object[]> fields;
        /**
         * 注解列表
         */
        private List<Object[]> parentFields;
    
        /**
         * 实体对象
         */
        public Class<T> clazz;
    
        public MyExcelUtil(Class<T> clazz)
        {
            this.clazz = clazz;
        }
    
        public void init(List<T> list, String sheetName, MyExcel.Type type,boolean flag)
        {
            if (list == null)
            {
                list = new ArrayList<T>();
            }
            this.list = list;
            this.sheetName = sheetName;
            this.type = type;
            if (flag) {
                createParentExcelField();
            }
            createExcelField();
            createWorkbook();
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * 
         * @param list 导出数据集合
         * @param sheetName 工作表的名称
         * @return 结果
         */
        public AjaxResult exportExcel(List<T> list, String sheetName,boolean flag)
        {
            this.init(list, sheetName, MyExcel.Type.EXPORT,flag);
            return exportExcel(flag);
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * 
         * @return 结果
         */
        public AjaxResult exportExcel(boolean flag)
        {
            OutputStream out = null;
            try
            {
                // 取出一共有多少个sheet.
                double sheetNo = Math.ceil(list.size() / sheetSize);
                for (int index = 0; index <= sheetNo; index++)
                {
                    createSheet(sheetNo, index);
    
                    Row row1 = null;
                    if (flag) {
                        //头标题
                        int size = parentFields.size();
                        int start = 0;
                        Set set = new HashSet();
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellRangeAddress = null;
                            CellRangeAddress cellRangeAddress1 = null;
                            Object[] os = parentFields.get(i);
                            MyExcel excel = (MyExcel) os[1];
                            String input = excel.parentName();
                            if (StringUtil.isNotEmptyString(input)) {
                                if (set.contains(input)) {
                                }else {
                                    //开始合并科目一的
                                    if (start > 0) {
                                        cellRangeAddress = new CellRangeAddress(0, 0, start, i-1);
                                    }
                                    start = i;
                                    set.add(input);
                                }
                            }else {
                                if (set.isEmpty()) {
                                    // 起始行号，结束行号，起始列号，结束列号
                                    cellRangeAddress = new CellRangeAddress(0, 1, i, i);
                                }else {
                                    //开始合并科目二的
                                    cellRangeAddress = new CellRangeAddress(0, 0, start, i-1);
                                    start = 0;
                                    set.clear();
                                    //科目二的合并完了同时记得合并当前列的
                                    cellRangeAddress1 = new CellRangeAddress(0, 1, i, i);
                                }
                            }
    
                            if (cellRangeAddress != null) {
                                sheet.addMergedRegion(cellRangeAddress);
                            }
                            if (cellRangeAddress1 != null) {
                                sheet.addMergedRegion(cellRangeAddress1);
                            }
                        }
    
                        row1 = sheet.createRow(1);
                    }
                    // 产生一行
                    Row row = sheet.createRow(0);
    
                    int column = 0;
                    // 写入各个字段的列头名称
                    for (Object[] os : fields)
                    {
                        MyExcel excel = (MyExcel) os[1];
                        this.createCell(excel, row, column);
                        if (StringUtil.isNotEmptyString(excel.parentName())) {
                            this.createCell1(excel, row1, column);
                        }
                        column++;
                    }
                    if (MyExcel.Type.EXPORT.equals(type))
                    {
                        fillExcelData(index, row);
                    }
                }
                String filename = encodingFilename(sheetName);
                out = new FileOutputStream(getAbsoluteFile(filename));
                wb.write(out);
                return AjaxResult.success(filename);
            }
            catch (Exception e)
            {
                e.printStackTrace();
                log.error("导出Excel异常{}", e.getMessage());
                throw new BusinessException("导出Excel失败，请联系网站管理员！");
            }
            finally
            {
                if (wb != null)
                {
                    try
                    {
                        wb.close();
                    }
                    catch (IOException e1)
                    {
                        e1.printStackTrace();
                    }
                }
                if (out != null)
                {
                    try
                    {
                        out.close();
                    }
                    catch (IOException e1)
                    {
                        e1.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 填充excel数据
         * 
         * @param index 序号
         * @param row 单元格行
         */
        public void fillExcelData(int index, Row row)
        {
            int startNo = index * sheetSize;
            int endNo = Math.min(startNo + sheetSize, list.size());
            for (int i = startNo; i < endNo; i++)
            {
                row = sheet.createRow(i + 2 - startNo);
                // 得到导出对象.
                T vo = (T) list.get(i);
                int column = 0;
                for (Object[] os : fields)
                {
                    Field field = (Field) os[0];
                    MyExcel excel = (MyExcel) os[1];
                    // 设置实体类私有属性可访问
                    field.setAccessible(true);
                    this.addCell(excel, row, vo, field, column++);
                }
            }
        }
    
        /**
         * 创建表格样式
         * 
         * @param wb 工作薄对象
         * @return 样式列表
         */
        private Map<String, CellStyle> createStyles(Workbook wb)
        {
            // 写入各条记录,每条记录对应excel表中的一行
            Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font dataFont = wb.createFont();
            dataFont.setFontName("Arial");
            dataFont.setFontHeightInPoints((short) 10);
            style.setFont(dataFont);
            styles.put("data", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font headerFont = wb.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(headerFont);
            styles.put("header", style);
    
            return styles;
        }
    
        /**
         * 创建单元格
         */
        public Cell createCell(MyExcel attr, Row row, int column)
        {
            // 创建列
            Cell cell = row.createCell(column);
            // 写入列信息
            if (StringUtil.isNotEmptyString(attr.parentName())) {
                cell.setCellValue(attr.parentName());
            }else {
                cell.setCellValue(attr.name());
            }
            setDataValidation(attr, row, column);
            cell.setCellStyle(styles.get("header"));
            return cell;
        }
        /**
         * 创建单元格
         */
        public Cell createCell1(MyExcel attr, Row row, int column)
        {
            // 创建列
            Cell cell = row.createCell(column);
            cell.setCellValue(attr.name());
            setDataValidation(attr, row, column);
            cell.setCellStyle(styles.get("header"));
            return cell;
    
        }
    
        /**
         * 设置单元格信息
         * 
         * @param value 单元格值
         * @param attr 注解相关
         * @param cell 单元格信息
         */
        public void setCellVo(Object value, MyExcel attr, Cell cell)
        {
            if (MyExcel.ColumnType.STRING == attr.cellType())
            {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
            }
            else if (MyExcel.ColumnType.NUMERIC == attr.cellType())
            {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Integer.parseInt(value + ""));
            }
        }
    
        /**
         * 创建表格样式
         */
        public void setDataValidation(MyExcel attr, Row row, int column)
        {
            if (attr.name().indexOf("注：") >= 0)
            {
                sheet.setColumnWidth(column, 6000);
            }
            else
            {
                // 设置列宽
                sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
                row.setHeight((short) (attr.height() * 20));
            }
            // 如果设置了提示信息则鼠标放上去提示.
            if (StringUtils.isNotEmpty(attr.prompt()))
            {
                // 这里默认设了2-101列提示.
                setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
            }
            // 如果设置了combo属性则本列只能选择不能输入
            if (attr.combo().length > 0)
            {
                // 这里默认设了2-101列只能选择不能输入.
                setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
            }
        }
    
        /**
         * 添加单元格
         */
        public Cell addCell(MyExcel attr, Row row, T vo, Field field, int column)
        {
            Cell cell = null;
            try
            {
                // 设置行高
                row.setHeight((short) (attr.height() * 20));
                // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                if (attr.isExport())
                {
                    // 创建cell
                    cell = row.createCell(column);
                    cell.setCellStyle(styles.get("data"));
    
                    // 用于读取对象中的属性
                    Object value = getTargetValue(vo, field, attr);
                    String dateFormat = attr.dateFormat();
                    String readConverterExp = attr.readConverterExp();
                    if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
                    {
                        cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                    }
                    else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
                    {
                        cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                    }
                    else
                    {
                        // 设置列类型
                        setCellVo(value, attr, cell);
                    }
                }
            }
            catch (Exception e)
            {
                log.error("导出Excel失败{}", e);
            }
            return cell;
        }
    
        /**
         * 设置 POI XSSFSheet 单元格提示
         * 
         * @param sheet 表单
         * @param promptTitle 提示标题
         * @param promptContent 提示内容
         * @param firstRow 开始行
         * @param endRow 结束行
         * @param firstCol 开始列
         * @param endCol 结束列
         */
        public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                int firstCol, int endCol)
        {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            dataValidation.createPromptBox(promptTitle, promptContent);
            dataValidation.setShowPromptBox(true);
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 设置某些列的值只能输入预制的数据,显示下拉框.
         * 
         * @param sheet 要设置的sheet.
         * @param textlist 下拉框显示的内容
         * @param firstRow 开始行
         * @param endRow 结束行
         * @param firstCol 开始列
         * @param endCol 结束列
         * @return 设置好的sheet.
         */
        public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
        {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 加载下拉列表内容
            DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            // 数据有效性对象
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            // 处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation)
            {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            }
            else
            {
                dataValidation.setSuppressDropDownArrow(false);
            }
    
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 解析导出值 0=男,1=女,2=未知
         * 
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @return 解析后值
         * @throws Exception
         */
        public static String convertByExp(String propertyValue, String converterExp) throws Exception
        {
            try
            {
                String[] convertSource = converterExp.split(",");
                for (String item : convertSource)
                {
                    String[] itemArray = item.split("=");
                    if (itemArray[0].equals(propertyValue))
                    {
                        return itemArray[1];
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return propertyValue;
        }
    
        /**
         * 反向解析值 男=0,女=1,未知=2
         * 
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @return 解析后值
         * @throws Exception
         */
        public static String reverseByExp(String propertyValue, String converterExp) throws Exception
        {
            try
            {
                String[] convertSource = converterExp.split(",");
                for (String item : convertSource)
                {
                    String[] itemArray = item.split("=");
                    if (itemArray[1].equals(propertyValue))
                    {
                        return itemArray[0];
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return propertyValue;
        }
    
        /**
         * 编码文件名
         */
        public String encodingFilename(String filename)
        {
            filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
            return filename;
        }
    
        /**
         * 获取下载路径
         * 
         * @param filename 文件名称
         */
        public String getAbsoluteFile(String filename)
        {
            String downloadPath = RuoYiConfig.getDownloadPath() + filename;
            File desc = new File(downloadPath);
            if (!desc.getParentFile().exists())
            {
                desc.getParentFile().mkdirs();
            }
            return downloadPath;
        }
    
        /**
         * 获取bean中的属性值
         * 
         * @param vo 实体对象
         * @param field 字段
         * @param excel 注解
         * @return 最终的属性值
         * @throws Exception
         */
        private Object getTargetValue(T vo, Field field, MyExcel excel) throws Exception
        {
            Object o = field.get(vo);
    
            if (StringUtils.isNotEmpty(excel.targetAttr()))
            {
                String target = excel.targetAttr();
                if (target.indexOf(".") > -1)
                {
                    String[] targets = target.split("[.]");
                    if (o instanceof List) {
                        Map map = (Map) ((List) o).get(Integer.parseInt(targets[0]));
                        o = map.get(targets[1]);
                    }else {
                        for (String name : targets)
                        {
                            o = getValue(o, name);
                        }
                    }
    
                }
                else
                {
                    o = getValue(o, target);
                }
            }
            return o;
        }
    
        /**
         * 以类的属性的get方法方法形式获取值
         * 
         * @param o
         * @param name
         * @return value
         * @throws Exception
         */
        private Object getValue(Object o, String name) throws Exception
        {
            if (StringUtils.isNotEmpty(name))
            {
                Class<?> clazz = o.getClass();
                String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
                Method method = clazz.getMethod(methodName);
                o = method.invoke(o);
            }
            return o;
        }
    
        /**
         * 得到所有定义字段
         */
        private void createExcelField()
        {
            this.fields = new ArrayList<Object[]>();
            List<Field> tempFields = new ArrayList<>();
            tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
            tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
            for (Field field : tempFields)
            {
                // 单注解
                if (field.isAnnotationPresent(MyExcel.class))
                {
                    putToField(field, field.getAnnotation(MyExcel.class));
                }
    
                // 多注解
                if (field.isAnnotationPresent(MyExcels.class))
                {
                    MyExcels attrs = field.getAnnotation(MyExcels.class);
                    MyExcel[] excels = attrs.value();
                    for (MyExcel excel : excels)
                    {
                        putToField(field, excel);
                    }
                }
            }
        }
    
        /**
         * 得到所有定义字段
         */
        private void createParentExcelField()
        {
            this.parentFields = new ArrayList<Object[]>();
            List<Field> tempFields = new ArrayList<>();
            tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
            tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
            for (Field field : tempFields)
            {
                // 单注解
                if (field.isAnnotationPresent(MyExcel.class))
                {
                    putToParentField(field, field.getAnnotation(MyExcel.class));
                }
    
                // 多注解
                if (field.isAnnotationPresent(MyExcels.class))
                {
                    MyExcels attrs = field.getAnnotation(MyExcels.class);
                    MyExcel[] excels = attrs.value();
                    for (MyExcel excel : excels)
                    {
                        putToParentField(field, excel);
                    }
                }
            }
        }
    
        /**
         * 放到字段集合中
         */
        private void putToField(Field field, MyExcel attr)
        {
            if (attr != null && (attr.type() == MyExcel.Type.ALL || attr.type() == type))
            {
                this.fields.add(new Object[] { field, attr });
            }
        }
        /**
         * 放到字段集合中
         */
        private void putToParentField(Field field, MyExcel attr)
        {
            if (attr != null && (attr.type() == MyExcel.Type.ALL || attr.type() == type))
            {
                this.parentFields.add(new Object[] { field, attr });
            }
        }
    
        /**
         * 创建一个工作簿
         */
        public void createWorkbook()
        {
            this.wb = new SXSSFWorkbook(500);
        }
    
        /**
         * 创建工作表
         * 
         * @param sheetNo sheet数量
         * @param index 序号
         */
        public void createSheet(double sheetNo, int index)
        {
            this.sheet = wb.createSheet();
            this.styles = createStyles(wb);
            // 设置工作表的名称.
            if (sheetNo == 0)
            {
                wb.setSheetName(index, sheetName);
            }
            else
            {
                wb.setSheetName(index, sheetName + index);
            }
        }
    
        /**
         * 获取单元格值
         * 
         * @param row 获取的行
         * @param column 获取单元格列号
         * @return 单元格值
         */
        public Object getCellValue(Row row, int column)
        {
            if (row == null)
            {
                return row;
            }
            Object val = "";
            try
            {
                Cell cell = row.getCell(column);
                if (StringUtils.isNotNull(cell))
                {
                    if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
                    {
                        val = cell.getNumericCellValue();
                        if (HSSFDateUtil.isCellDateFormatted(cell))
                        {
                            val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                        }
                        else
                        {
                            if ((Double) val % 1 > 0)
                            {
                                val = new DecimalFormat("0.00").format(val);
                            }
                            else
                            {
                                val = new DecimalFormat("0").format(val);
                            }
                        }
                    }
                    else if (cell.getCellTypeEnum() == CellType.STRING)
                    {
                        val = cell.getStringCellValue();
                    }
                    else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
                    {
                        val = cell.getBooleanCellValue();
                    }
                    else if (cell.getCellTypeEnum() == CellType.ERROR)
                    {
                        val = cell.getErrorCellValue();
                    }
    
                }
            }
            catch (Exception e)
            {
                return val;
            }
            return val;
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NzZG41NjU5NzM4NTA_size_16_color_FFFFFF_t_70]: /images/20221020/ce90b786026f406c885083607d4a19ea.png