springboot项目导出excel 合并单元格表格
导出效果业务controller业务数据业务实体类注解MyExcel.java注解 MyExcels导出工具类MyExcelUtil.java导出效果
业务controller
/*** 导出学员学习数据统计列表*/@RequiresPermissions("project:userLearningData:export")@PostMapping("/export")@ResponseBodypublic 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 学员学习数据统计集合*/@Overridepublic 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 mons.lang3.builder.ToStringBuilder;import mons.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 -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
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*/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface MyExcels{MyExcel[] value();}
导出工具类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 mon.exception.BusinessException;import mon.utils.DateUtils;import mon.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(mon.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 (bo().length > 0){// 这里默认设了2-101列只能选择不能输入.setXSSFValidation(sheet, bo(), 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()){// 创建cellcell = 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;}}
