自定义基于注解形式的Excel解析器框架

Myth丶恋晨 2022-06-06 09:18 203阅读 0赞

基于poi 3.16版本

<dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi</artifactId>
 <version>3.10.1</version>
 </dependency>

使用方式基本不变，参照另外一篇博客[这基于自定义的Excel解析框架的使用范例][Excel]

下面介绍下框架中有哪些类，每个类的职责:

![Excell解析框架类目][Excell]

Excel : 对poi的封装，提供一些简单方便的获取poi数据的方法；

ExcelColumn : 自定义注解，标识Model的属性或者getter方法上，建议Excel Column到Filed的映射；

ExcelInstance : 存储了每行解析生成的对象实例及行序号；

ExcelMapping : 自定义注解，标识在Model Class上，指定数据起始行，标题行，及数据Sheet码；

ExcelMappingProcessor : Excel解析器，最重要的类，负责解析注解建立映射关系及解析Excel数据；

ExcelPromptAuthor : 批注的标识

PropertyInitializer : Model初始化器，Model实现此接口，在解析Excel每行数据前通过此接口获取Model实例。

ExcelException : 自定义的异常，在解析Excel时遇到非正常错误时抛出

ExcelMappingException : 自定义异常，在解析单元格遇到类型不匹配及行唯一性冲突时抛出，由自定义的异常处理器决定如何处理这个异常，同时是否继续解析；

**MappingExceptionResolver** : 映射异常处理器，负责处理解析过程中抛出的可处理异常，比如单元格数据类型与属性类型不匹配，行与行之间的唯一性字段重复。处理异常后可决定是否再继续解析Excel数据 。此接口时本次改进的关键，灵活的处理解析过程中的异常，可自由决定是否中途停止解析等，可以自定义自己的异常处理器，增加框架可扩展性。

ExcelEditorExceptionResolver : 实现了MappingExceptionResolver 接口，自定义了异常处理，将类型不匹配异常以批注的形式添加到相应的单元格上，对于行唯一性冲突异常，对冲突行的后者，在最后列+1上标识上冲突信息。

CombiningExceptionResolver : 实现了MappingExceptionResolver 接口，自定义了异常处理，将每行的类型不匹配信息合并起来，再加上行冲突信息，最前面加行号组合成文字信息，供前端展示。当错误行大于10时，中止解析过程。

测试方法代码:

@Test
 public void testParsingWithEditor() throws Exception {
 File original = new File("C:\\Users\\dell-7359\\Desktop\\Excel原始数据.xlsx");
 Excel excel = new Excel(original);
 processor = new ExcelMappingProcessor<ExcelModelExtends>(excel, ExcelModelExtends.class, new ExcelEditorExceptionResolver());
 boolean success = processor.process();
 Collection<ExcelInstance<ExcelModelExtends>> results = processor.getCorrectResult();
 System.out.println(results.size());
 File errorFile = new File("C:\\Users\\dell-7359\\Desktop\\Excel原始数据1.xlsx");
 errorFile.createNewFile();
 excel.write(errorFile);
 }
 
 @Test
 public void testParsingWithCombining() throws Exception {
 File original = new File("C:\\Users\\dell-7359\\Desktop\\Excel原始数据.xlsx");
 Excel excel = new Excel(original);
 CombiningExceptionResolver exceptionResolver = new CombiningExceptionResolver();
 processor = new ExcelMappingProcessor<ExcelModelExtends>(excel, ExcelModelExtends.class,exceptionResolver);
 boolean success = processor.process();
 if(!success){
 System.out.println(exceptionResolver.getCombinedMsg());
 }
 }

下面就将每个类的相应代码附上:

package com.bob.config.mvc.excelmapping.exception;
    
    /** * 映射异常处理器 * * @author dell-7359 * @create 2017-10-22 10:31 */
    public interface MappingExceptionResolver { 
    
        /** * 是否在Excel上直接编辑错误信息 * * @return */
        default boolean excelEditorMode() {
            return false;
        }
    
        /** * 处理单元格数据类型与属性类型不匹配异常 * * @param ex * @return 是否继续解析 * @throws Exception */
        boolean handleTypeMismatch(ExcelMappingException ex) throws Exception;
    
        /** * 处理行唯一性冲突异常 * * @param ex * @return 是否继续解析 * @throws Exception */
        boolean handleUniqueConflict(ExcelMappingException ex) throws Exception;
    
    
    }

package com.bob.config.mvc.excelmapping.exception;
    
    import java.io.Serializable;
    
    import com.bob.config.mvc.excelmapping.ExcelMappingProcessor;
    
    /** * Excel映射异常 * * @author * @since 2017年3月21日 下午6:56:39 */
    public final class ExcelMappingException extends RuntimeException implements Serializable { 
    
        private static final long serialVersionUID = -1582949558953456846L;
    
        private int rowIndex;
        private int columnIndex;
        private Exception original;
        private ExcelMappingProcessor excelMappingProcessor;
    
        public ExcelMappingException(String msg,int rowIndex, int colIndex, ExcelMappingProcessor excelMappingProcessor) {
            super(msg);
            this.rowIndex = rowIndex;
            this.columnIndex = colIndex;
            this.original = original;
            this.excelMappingProcessor = excelMappingProcessor;
        }
    
        public int getRowIndex() {
            return rowIndex;
        }
    
        public void setRowIndex(int rowIndex) {
            this.rowIndex = rowIndex;
        }
    
        public int getColumnIndex() {
            return columnIndex;
        }
    
        public void setColumnIndex(int columnIndex) {
            this.columnIndex = columnIndex;
        }
    
        public Exception getOriginal() {
            return original;
        }
    
        public void setOriginal(Exception original) {
            this.original = original;
        }
    
        public ExcelMappingProcessor getExcelMappingProcessor() {
            return excelMappingProcessor;
        }
    
        public void setExcelMappingProcessor(ExcelMappingProcessor excelMappingProcessor) {
            this.excelMappingProcessor = excelMappingProcessor;
        }
    }

package com.bob.config.mvc.excelmapping.exception;
    
    /** * 对Excel内容编辑的异常处理器 * * @author dell-7359 * @create 2017-10-22 11:26 */
    public class ExcelEditorExceptionResolver implements MappingExceptionResolver { 
    
        @Override
        public boolean excelEditorMode() {
            return true;
        }
    
        @Override
        public boolean handleTypeMismatch(ExcelMappingException ex) throws Exception {
            ex.getExcelMappingProcessor().markErrorPrompt(ex.getRowIndex(),ex.getColumnIndex(),ex.getMessage());
            return true;
        }
    
        @Override
        public boolean handleUniqueConflict(ExcelMappingException ex) throws Exception {
            ex.getExcelMappingProcessor().markErrorMsg(ex.getRowIndex(),ex.getMessage());
            return true;
        }
    }

package com.bob.config.mvc.excelmapping.exception;
 
 import java.util.LinkedHashMap;
 import java.util.Map;
 
 /** * 异常信息合并处理器 * * @author dell-7359 * @create 2017-10-22 19:11 */
 public class CombiningExceptionResolver implements MappingExceptionResolver { 
 
 private static final LinkedHashMap<Integer, String> ROW_ERROR_MAPPINGS = new LinkedHashMap<Integer, String>(16);
 
 @Override
 public boolean handleTypeMismatch(ExcelMappingException ex) throws Exception {
 return combineErrorMsg(ex.getRowIndex(),
 String.format("单元格[%s]异常,%s", (char)(ex.getColumnIndex() + 65) + "" + (ex.getRowIndex() + 1), ex.getMessage()));
 }
 
 @Override
 public boolean handleUniqueConflict(ExcelMappingException ex) throws Exception {
 return combineErrorMsg(ex.getRowIndex(), ex.getMessage());
 }
 
 /** * 获取组合好的错误信息以供前端展示 * * @return */
 public String getCombinedMsg() {
 StringBuilder sb = new StringBuilder();
 for (Map.Entry<Integer, String> entry : ROW_ERROR_MAPPINGS.entrySet()) {
 sb.append(String.format("第%d行:%s\n",entry.getKey()+1,entry.getValue()));
 }
 return sb.toString();
 }
 
 private boolean combineErrorMsg(int rowIndex, String msg) {
 if (!ROW_ERROR_MAPPINGS.containsKey(rowIndex)) {
 ROW_ERROR_MAPPINGS.put(rowIndex, msg);
 } else {
 ROW_ERROR_MAPPINGS.put(rowIndex, ROW_ERROR_MAPPINGS.get(rowIndex) + ";" + msg);
 }
 return ROW_ERROR_MAPPINGS.size() < 10;
 }
 
 public LinkedHashMap<Integer, String> getRowErrorMappings() {
 return ROW_ERROR_MAPPINGS;
 }
 }

package com.bob.config.mvc.excelmapping.exception;
    
    /** * Excel异常 * * @author * @create 2017-09-19 10:18 */
    public class ExcelException extends RuntimeException { 
    
        public ExcelException() {
            super();
        }
    
        public ExcelException(String message) {
            super(message);
        }
    
        public ExcelException(String message, Throwable cause) {
            super(message, cause);
        }
    
        public ExcelException(Throwable cause) {
            super(cause);
        }
    }

package com.bob.config.mvc.excelmapping;
 
 import java.lang.annotation.ElementType;
 import java.lang.annotation.Retention;
 import java.lang.annotation.RetentionPolicy;
 import java.lang.annotation.Target;
 
 /** * @since 2016年5月19日 下午4:54:29 * @author * */
 @Retention(RetentionPolicy.RUNTIME)
 @Target({ ElementType.FIELD, ElementType.METHOD })
 public @interface ExcelColumn { 
 
 /** * 配置列的名称,对应A,B,C,D.... */
 Column value() default Column.DEFAULT_NONE;
 
 /** * 是否可作为唯一性的标识 * 默认 notNull() = true * * @return */
 boolean key() default false;
 
 /** * 非空校验 * null or '' or ' ' ? true: false * * @return */
 boolean notNull() default false;
 
 /** * 是否为最后一列 * * @return */
 boolean last() default false;
 
 /** * 列信息 */
 public enum Column {
 DEFAULT_NONE("NONE", -1), // 默认无
 
 A("A", 0), B("B", 1), C("C", 2), D("D", 3), E("E", 4), F("F", 5), G("G", 6),
 
 H("H", 7), I("I", 8), J("J", 9), K("K", 10), L("L", 11), M("M", 12), N("N", 13),
 
 O("O", 14), P("P", 15), Q("Q", 16), R("R", 17), S("S", 18), T("T", 19), U("M", 20),
 
 V("V", 21), W("W", 22), X("X", 23), Y("Y", 24), Z("Z", 25), AA("AA", 26), AB("AB", 27),
 
 AC("AC", 28), AD("AD", 29), AE("AE", 30), AF("AF", 31), AG("AG", 32), AH("AH", 33), AI("AI", 34),
 
 AJ("AJ", 35), AK("AK", 36), AL("AL", 37), AM("AM", 38), AN("AN", 39), AO("AO", 40);
 
 public String name;
 
 public Integer value;
 
 private Column(String name, Integer value) {
 this.name = name;
 this.value = value;
 }
 }
 }

package com.bob.config.mvc.excelmapping;
 
 import java.io.Serializable;
 
 /** * Excel成功解析返回对象,按行存储 * * @since 2017年3月21日 下午6:56:39 * @author JiangJibo * */
 public class ExcelInstance<T> implements Serializable { 
 private static final long serialVersionUID = 4183046707691570188L;
 
 private int rowIndex;
 private T instance;
 
 public ExcelInstance(int rowIndex, T instance) {
 super();
 this.rowIndex = rowIndex;
 this.instance = instance;
 }
 
 /** * 行号 * * @return the rowIndex */
 public int getRowIndex() {
 return rowIndex;
 }
 
 /** * @param rowIndex * the rowIndex to set */
 public void setRowIndex(int rowIndex) {
 this.rowIndex = rowIndex;
 }
 
 /** * 解析成功对象信息 * * @return the instance */
 public T getInstance() {
 return instance;
 }
 
 /** * @param instance * the instance to set */
 public void setInstance(T instance) {
 this.instance = instance;
 }
 }

package com.bob.config.mvc.excelmapping;
    
    import java.lang.annotation.Documented;
    import java.lang.annotation.Retention;
    import java.lang.annotation.Target;
    
    import static java.lang.annotation.ElementType.TYPE;
    import static java.lang.annotation.RetentionPolicy.RUNTIME;
    
    /** * 标识在Model类上的注解，指明标题栏，起始栏及所在页码 * * @author wb-jjb318191 */
    @Documented
    @Retention(RUNTIME)
    @Target(TYPE)
    public @interface ExcelMapping { 
    
        /** * 标识数据在第几页 * * @return */
        int sheetAt() default 0;
    
        /** * 数据行第一行位置 * * @return */
        int dataRow();
    
        /** * 标题行所在位置,字段说明所在行 * * @return */
        int titleRow();
    }

package com.bob.config.mvc.excelmapping;
 
 /** * @since 2016年5月25日 上午11:15:46 * @author * */
 public interface PropertyInitializer<T> {
 
 /** * 初始化解析的Excel对象 * * @return */
 T initProperties();
 }

package com.bob.config.mvc.excelmapping;
 
 import java.lang.reflect.Field;
 import java.lang.reflect.Method;
 import java.lang.reflect.Modifier;
 import java.math.BigDecimal;
 import java.text.SimpleDateFormat;
 import java.util.Calendar;
 import java.util.Collection;
 import java.util.Date;
 import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Map.Entry;
 import java.util.concurrent.ConcurrentHashMap;
 
 import com.bob.config.mvc.excelmapping.exception.ExcelMappingException;
 import com.bob.config.mvc.excelmapping.exception.MappingExceptionResolver;
 import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
 import org.apache.poi.hssf.util.HSSFColor.RED;
 import org.apache.poi.hssf.util.HSSFColor.WHITE;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
 import org.apache.poi.ss.usermodel.ClientAnchor;
 import org.apache.poi.ss.usermodel.Comment;
 import org.apache.poi.ss.usermodel.Drawing;
 import org.apache.poi.ss.usermodel.Font;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.beans.BeanUtils;
 import org.springframework.util.Assert;
 import org.springframework.util.ReflectionUtils;
 import org.springframework.util.StringUtils;
 import org.springframework.validation.BindingResult;
 import org.springframework.validation.FieldError;
 
 import static org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND;
 import static org.apache.poi.ss.usermodel.ClientAnchor.MOVE_AND_RESIZE;
 import static org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD;
 
 /** * 基于注解的Excel解析工具类 * * @author JiangJibo * @since 2016年5月19日 下午5:07:00 */
 public final class ExcelMappingProcessor<T extends PropertyInitializer<T>> { 
 
 private static final Logger LOGGER = LoggerFactory.getLogger(ExcelMappingProcessor.class);
 
 private boolean hasError = false;
 private ExcelColumn lastExcelColumn;
 private final static String ERROR_SPLIT_BR = "\n";
 
 //private final Excel excel;
 private final Excel excel;
 private final Class<T> clazz;
 private final String version;
 private final Integer sheetAt;
 private final Integer dataRow;
 private final Integer titleRow;
 private final String promptAuthor;
 private final CellStyle errorCellStyle;
 private final Drawing drawingPatriarch;
 private final ClientAnchor clientAnchor;
 private static final Map<Class<?>, LinkedHashMap<Field, ExcelColumn>> EXCEL_MAPPINGS = new ConcurrentHashMap<Class<?>, LinkedHashMap<Field, ExcelColumn>>();
 private final LinkedHashMap<String, ExcelColumn> fieldColumns;
 private final LinkedHashMap<String, ExcelColumn> keyFieldColumns;
 private final LinkedHashMap<String, ExcelInstance<T>> correctResult;
 
 private final MappingExceptionResolver exceptionResolver;
 
 private static final String EXCELCOLUMN_ANN_NAME = ExcelColumn.class.getSimpleName();
 
 /** * 构建Excel解析器 * Default promptAuthor is { @linkplain ExcelPromptAuthor} * * @param excel * @param clazz */
 public ExcelMappingProcessor(Excel excel, Class<T> clazz, MappingExceptionResolver exceptionResolver) {
 this(excel, clazz, exceptionResolver, ExcelPromptAuthor.WB_JJB);
 }
 
 /** * 构建Excel解析器 * * @param excel * @param clazz * @param promptAuthor */
 public ExcelMappingProcessor(Excel excel, Class<T> clazz, MappingExceptionResolver exceptionResolver, ExcelPromptAuthor promptAuthor) {
 // 1.excel相关属性
 this.excel = excel;
 this.clazz = clazz;
 this.exceptionResolver = exceptionResolver;
 ExcelMapping excelMapping = clazz.getAnnotation(ExcelMapping.class);
 Assert.notNull(excelMapping, "解析Excel对象{" + clazz.getSimpleName() + "}未标识ExcelMapping注解，请联系系统维护人员！");
 this.sheetAt = excelMapping.sheetAt();
 this.dataRow = excelMapping.dataRow();
 this.titleRow = excelMapping.titleRow();
 // 2.初始化参数
 this.promptAuthor = promptAuthor.getAuthor();
 this.version = new SimpleDateFormat("HHmmss").format(new Date());
 this.fieldColumns = new LinkedHashMap<String, ExcelColumn>();
 this.keyFieldColumns = new LinkedHashMap<String, ExcelColumn>();
 this.correctResult = new LinkedHashMap<String, ExcelInstance<T>>();
 // 3. 创建批注框
 if (excel.isXLSX()) {
 this.clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, (short)3, (short)3, (short)5, (short)6);
 } else {
 this.clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, (short)3, (short)3, (short)5, (short)6);
 }
 this.clientAnchor.setAnchorType(MOVE_AND_RESIZE);
 this.drawingPatriarch = excel.getSheet().createDrawingPatriarch();
 // 4. 创建错误栏样式
 this.errorCellStyle = excel.createCellStyle();
 Font font = excel.createFont();
 font.setFontName("宋体");
 font.setFontHeightInPoints((short)10);
 font.setColor(RED.index);
 font.setBoldweight(BOLDWEIGHT_BOLD);
 this.errorCellStyle.setFont(font);
 this.errorCellStyle.setWrapText(true);
 this.errorCellStyle.setFillPattern(SOLID_FOREGROUND);
 }
 
 private ClientAnchor generateClientAnchor() {
 if (excel.isXLSX()) {
 return new XSSFClientAnchor(0, 0, 0, 0, (short)3, (short)3, (short)5, (short)6);
 } else {
 return new HSSFClientAnchor(0, 0, 0, 0, (short)3, (short)3, (short)5, (short)6);
 }
 }
 
 /** * 获取解析正确的结果集 * * @return */
 public Collection<ExcelInstance<T>> getCorrectResult() {
 return correctResult.values();
 }
 
 /** * 获取属性列对应信息 * * @return the fieldColumns */
 public LinkedHashMap<String, ExcelColumn> getFieldColumns() {
 return fieldColumns;
 }
 
 /** * 创建Excel到Model的映射 */
 private void buildExcelMapping() {
 LinkedHashMap<Field, ExcelColumn> fieldColumns = new LinkedHashMap<Field, ExcelColumn>();
 //解析标识了@ExcelColumn注解的属性
 ReflectionUtils.doWithLocalFields(clazz, (field) -> {
 ExcelColumn column = field.getAnnotation(ExcelColumn.class);
 if (null == column) {
 return;
 } else if (Modifier.isStatic(field.getModifiers())) {
 LOGGER.warn("[{}]注解不适用于静态属性[{}]", EXCELCOLUMN_ANN_NAME, field.getName());
 return;
 }
 fieldColumns.put(field, column);
 });
 //解析标识了@ExcelColumn注解的getter()方法
 ReflectionUtils.doWithLocalMethods(clazz, (method) -> {
 ExcelColumn column = method.getAnnotation(ExcelColumn.class);
 if (null == column) {
 return;
 } else if (Modifier.isStatic(method.getModifiers())) {
 LOGGER.warn("[{}]注解不适用于静态方法[{}]", EXCELCOLUMN_ANN_NAME, method.getName());
 return;
 }
 if (!isGetter(method)) {
 LOGGER.warn("[{}]注解不适用于非getter方法[{}]", EXCELCOLUMN_ANN_NAME, method.getName());
 return;
 }
 Field field = getFieldFromGetter(method);
 if (fieldColumns.containsKey(field)) {
 LOGGER.warn("[{}]属性被重复解析，略过", field.getName());
 return;
 }
 fieldColumns.put(field, column);
 });
 EXCEL_MAPPINGS.put(clazz, fieldColumns);
 }
 
 /** * 判断一个函数是否是getter()方法 * * @param method * @return */
 private boolean isGetter(Method method) {
 String methodName = method.getName();
 boolean getter = methodName.startsWith("get") && Character.isUpperCase(methodName.charAt(3));
 boolean isser = methodName.startsWith("is") && Character.isUpperCase(methodName.charAt(2));
 return (getter || isser) && method.getParameterCount() == 0;
 }
 
 /** * @param method * @return */
 private Field getFieldFromGetter(Method method) {
 String methodName = method.getName();
 String filedName = Character.toLowerCase(methodName.charAt(3)) + methodName.substring(4, methodName.length());
 return ReflectionUtils.findField(clazz, filedName);
 }
 
 /** * 获取当前类的映射集合 * * @return */
 private LinkedHashMap<Field, ExcelColumn> getExcelMapping() {
 if (EXCEL_MAPPINGS.get(clazz) == null) {
 synchronized (clazz) {
 if (EXCEL_MAPPINGS.get(clazz) == null) {
 buildExcelMapping();
 }
 }
 }
 return EXCEL_MAPPINGS.get(clazz);
 }
 
 /** * Excel解析 * * @return hasError ? true : false; */
 public boolean process() throws Exception {
 // 循环每一行 循环每一列，获取每一个单元格的数值，通过注解设置到指定属性中
 final int physRow = excel.getSheetAt(sheetAt).getPhysicalNumberOfRows();
 Assert.isTrue(physRow > dataRow, String.format("解析Excel错误，Excel实际可读取的物理行数%d小于指定的数据行数%d", physRow, dataRow));
 LinkedHashMap<Field, ExcelColumn> fieldColumns = this.getExcelMapping();
 for (int i = dataRow; i < physRow; i++) {
 final int rowIndex = i;
 if (exceptionResolver.excelEditorMode()) {
 this.removeErrorMsg(rowIndex);
 }
 final T newInstance = BeanUtils.instantiate(clazz).initProperties();
 final StringBuilder keyBuilder = new StringBuilder();
 boolean hasRowError = false;
 //遍历Key Column
 for (Entry<Field, ExcelColumn> entry : fieldColumns.entrySet()) {
 Field field = entry.getKey();
 ExcelColumn excelColumn = entry.getValue();
 if (excelColumn.last() && null == lastExcelColumn) {
 lastExcelColumn = excelColumn;
 }
 ExcelColumn.Column column = excelColumn.value();
 Cell cell = excel.getCell(rowIndex, column.value);
 Assert.notNull(cell, String.format("获取Excel单元格%d行%s列为空", rowIndex + 1, column.name));
 Object value = null;
 try {
 value = getCellValue(cell, field, excelColumn);
 } catch (Exception e) {
 hasRowError = true;
 if (!exceptionResolver.handleTypeMismatch(new ExcelMappingException(e.getMessage(), rowIndex, column.value, this))) {
 LOGGER.warn("因类型不匹配中止解析解析Excel，当前解析到第[{}]行第[{}]列", rowIndex, column.value);
 return false;
 }
 continue;
 }
 boolean isKey = excelColumn.key();
 if (rowIndex == dataRow && isKey) {
 keyFieldColumns.put(field.getName(), excelColumn);
 }
 if (isKey) {
 keyBuilder.append("[").append(field.getName()).append(":").append(value).append("]");
 }
 field.setAccessible(true);
 ReflectionUtils.setField(field, newInstance, value);
 // 1.4 parse current remove old cell prompt
 if (exceptionResolver.excelEditorMode()) {
 removeErrorPrompt(cell);
 }
 }
 
 if (hasRowError) {
 this.setError();
 continue;
 }
 
 if (keyFieldColumns.isEmpty()) {
 correctResult.put(String.valueOf(rowIndex), new ExcelInstance<T>(rowIndex, newInstance));
 continue;
 }
 
 // 2.唯一性校验
 String key = keyBuilder.toString();
 if (correctResult.containsKey(key)) {
 this.setError();
 int dupRowIndex = correctResult.get(key).getRowIndex() + 1;
 String errorMsg = "此行与第" + dupRowIndex + "行的数据存在重复情况";
 if (!exceptionResolver.handleUniqueConflict(new ExcelMappingException(errorMsg, rowIndex, 0, this))) {
 LOGGER.warn("因行唯一性冲突中止解析解析Excel，当前解析到第[{}]行", rowIndex);
 return false;
 }
 continue;
 }
 correctResult.put(keyBuilder.toString(), new ExcelInstance<T>(rowIndex, newInstance));
 }
 // 在标题行中标记唯一键
 this.markKeyColumnsPrompt();
 //
 return !hasError;
 }
 
 /** * 统一在当前行最后一列增加错误信息,默认添加一条错误信息 * * @param rowIndex * @param errorMsg */
 public void markErrorMsg(int rowIndex, String errorMsg) {
 // 1.set style
 int lastColumnIndex = this.getExcelMapping().size();
 Cell cell = excel.getCell(rowIndex, lastColumnIndex);
 cell.setCellStyle(errorCellStyle);
 // 2.set message
 StringBuilder errorMsgBuilder = new StringBuilder();
 String existErrorMsg = cell.getStringCellValue();
 if (StringUtils.hasText(existErrorMsg)) {
 errorMsgBuilder.append(existErrorMsg).append(ERROR_SPLIT_BR);
 }
 errorMsgBuilder.append(errorMsg);
 cell.setCellValue(errorMsgBuilder.toString());
 }
 
 /** * 统一在当前行最后一列增加错误信息,添加一系列的错误信息 * * @param rowIndex * @param errorMsgs */
 public void markErrorMsg(int rowIndex, List<String> errorMsgs) {
 StringBuilder builder = new StringBuilder();
 for (String errorMsg : errorMsgs) {
 builder.append(errorMsg).append(ERROR_SPLIT_BR);
 }
 this.markErrorMsg(rowIndex, builder.substring(0, builder.length() - 1).toString());
 }
 
 /** * 删除当前行最后一列的错误信息 * * @param rowIndex */
 private void removeErrorMsg(int rowIndex) {
 int lastColumnIndex = this.getExcelMapping().size();
 Cell cell = excel.getCell(rowIndex, lastColumnIndex);
 cell.setCellValue("");
 }
 
 /** * 根据FiledName在对应的列上添加错误批注 * * @param rowIndex * @param bindingResult 对象属性校验异常 */
 public void markErrorPrompt(int rowIndex, BindingResult bindingResult) {
 boolean hasError = bindingResult.hasErrors();
 if (!hasError) {
 return;
 }
 for (FieldError fieldError : bindingResult.getFieldErrors()) {
 String errorMsg = fieldError.getDefaultMessage();
 ExcelColumn excelColumn = fieldColumns.get(fieldError.getField());
 if (null == excelColumn) {
 this.markErrorMsg(rowIndex, errorMsg);
 continue;
 }
 this.markErrorPrompt(excel.getCell(rowIndex, excelColumn.value().value), errorMsg);
 }
 }
 
 /** * 在对应的列上添加错误批注 * * @param rowIndex * @param colIndex * @param errorPrompt */
 public void markErrorPrompt(int rowIndex, int colIndex, String errorPrompt) {
 this.markErrorPrompt(excel.getCell(rowIndex, colIndex), errorPrompt);
 }
 
 /** * 在对应的列上添加错误批注 * * @param cell * @param prompt 批注信息 */
 public void markErrorPrompt(Cell cell, String prompt) {
 // 1.check delete
 StringBuilder promptBuilder = new StringBuilder();
 if (isMarkByPromptAuthor(cell)) {
 promptBuilder.append(getErrorPrompt(cell)).append(ERROR_SPLIT_BR);
 } else {
 this.removeErrorPrompt(cell);
 }
 // 2.add
 promptBuilder.append(prompt);
 String promptText = promptBuilder.toString();
 cell.setCellComment(this.createPromptComment(promptText));
 // 3.set cell background color:Red
 this.setBackgroundColor(cell, RED.index);
 }
 
 /** * 删除对应列上的错误提示 * * @param cell */
 private void removeErrorPrompt(Cell cell) {
 if (!isMarkErrorPrompt(cell)) {
 return;
 }
 cell.removeCellComment();
 this.setBackgroundColor(cell, WHITE.index);
 }
 
 /** * 判断当前列是否添加批注 * * @param cell * @return */
 private boolean isMarkErrorPrompt(Cell cell) {
 return null != cell.getCellComment();
 }
 
 /** * 校验当前列批注是否为当前作者 * * @param cell * @return */
 private boolean isMarkByPromptAuthor(Cell cell) {
 if (!isMarkErrorPrompt(cell)) {
 return false;
 }
 String cellPromptAuthor = cell.getCellComment().getAuthor();
 return cellPromptAuthor.equals(promptAuthor);
 }
 
 /** * 获取当前列批注信息 * * @param cell * @return */
 private String getErrorPrompt(Cell cell) {
 return cell.getCellComment().getString().getString();
 }
 
 /** * 设置列底色 * * @param cell * @param color */
 private void setBackgroundColor(Cell cell, short color) {
 CellStyle oldStyle = cell.getCellStyle();
 oldStyle.setFillBackgroundColor(color);
 }
 
 /** * 标记标题行所有唯一键列批注提示 * * @return */
 private void markKeyColumnsPrompt() {
 if (keyFieldColumns.isEmpty()) {
 return;
 }
 for (ExcelColumn excelColumn : keyFieldColumns.values()) {
 Cell cell = excel.getCell(titleRow, excelColumn.value().value);
 if (isMarkByPromptAuthor(cell)) {
 break;
 }
 // 2.add
 String keyPromptText = "此列为Excel数据的唯一键，各行此列数据不可重复";
 Comment promptComment = this.createPromptComment(keyPromptText);
 cell.setCellComment(promptComment);
 }
 }
 
 /** * 创建批注 * * @param comment * @return */
 private Comment createPromptComment(String comment) {
 Comment promptComment = drawingPatriarch.createCellComment(generateClientAnchor());
 promptComment.setAuthor(promptAuthor + version);
 promptComment.setString(excel.createRichTextString(comment));
 return promptComment;
 }
 
 /** * 设置cell值 * * @param cell * @param value */
 public void setCellValue(Cell cell, Object value) {
 this.setCellValue(cell.getRowIndex(), cell.getColumnIndex(), value);
 }
 
 /** * 设置cell值 * * @param rowIndex * @param colIndex * @param value */
 public void setCellValue(int rowIndex, int colIndex, Object value) {
 if (null == value) {
 return;
 }
 if (value instanceof String) {
 excel.setCell(rowIndex, colIndex, (String)value);
 } else if (value instanceof Number) {
 excel.setCell(rowIndex, colIndex, (Number)value);
 } else if (value instanceof BigDecimal) {
 excel.setCell(rowIndex, colIndex, (BigDecimal)value);
 } else if (value instanceof Double) {
 excel.setCell(rowIndex, colIndex, (Double)value);
 } else if (value instanceof Long) {
 excel.setCell(rowIndex, colIndex, (long)value);
 } else if (value instanceof Integer) {
 excel.setCell(rowIndex, colIndex, (int)value);
 } else if (value instanceof Short) {
 excel.setCell(rowIndex, colIndex, (short)value);
 } else if (value instanceof Byte) {
 excel.setCell(rowIndex, colIndex, (byte)value);
 } else if (value instanceof Boolean) {
 excel.setCell(rowIndex, colIndex, (Boolean)value);
 } else if (value instanceof Date) {
 excel.setCell(rowIndex, colIndex, (Date)value);
 } else if (value instanceof Calendar) {
 excel.setCell(rowIndex, colIndex, (Calendar)value);
 } else {
 throw new IllegalArgumentException(String.format("设值%s错误，暂不支持[%s]类型", value.toString(), value.getClass().getSimpleName()));
 }
 }
 
 /** * 将数据填充到Excel文件中,使用每行数据生成的对象类型,需要指定数据起始行。 * * @param excel * @param objs * @return */
 public Excel fillInObjects(Excel excel, List<?> objs, Integer dataRow) {
 Assert.notEmpty(objs, "查询明细数据不存在");
 Class<?> clazz = objs.get(0).getClass();
 int j = 0;
 for (int i = dataRow; i < objs.size() + dataRow; i++) {
 j++;
 excel.getCell(i, 0).setCellValue(j);
 for (Entry<Field, ExcelColumn> entry : this.getExcelMapping().entrySet()) {
 Object value = ReflectionUtils.getField(entry.getKey(), objs.get(i));
 if (null != value) {
 int column = entry.getValue().value().value;
 excel.getCell(i, column).setCellValue(value.toString());
 }
 
 }
 }
 return excel;
 }
 
 /** * 删除行 * * @param rowIndexs */
 public void removeRow(List<Integer> rowIndexs) {
 Sheet sheet = excel.getSheetAt(sheetAt);
 for (Integer rowIndex : rowIndexs) {
 Row row = sheet.getRow(rowIndex);
 if (null == row) {
 return;
 }
 sheet.removeRow(row);
 }
 // 2.上移空行
 Integer lastRowIndex = sheet.getLastRowNum();
 for (; lastRowIndex > 0; lastRowIndex--) {
 Row row = sheet.getRow(lastRowIndex);
 if (null != row) {
 continue;
 }
 sheet.shiftRows(lastRowIndex + 1, lastRowIndex, -1);
 }
 }
 
 /** * 删除行 * * @param rowIndex */
 public void removeRow(Integer rowIndex) {
 Sheet sheet = excel.getSheet();
 Row row = sheet.getRow(rowIndex);
 if (null == row) {
 return;
 }
 sheet.removeRow(row);
 // 2.上移空行
 sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
 }
 
 /** * 设置解析出错标记 */
 private void setError() {
 if (!hasError) {
 hasError = true;
 }
 }
 
 /** * 根据对象属性类型解析ExcelColumn值 * * @param cell * @param field * @param excelColumn * @return */
 private Object getCellValue(Cell cell, Field field, ExcelColumn excelColumn) {
 // 1.
 Class<?> fieldType = field.getType();
 Object value = excel.getCellValue(cell);
 Object strValue = excel.getCellString(cell);
 if (null == value || null == strValue) {
 Assert.state(excelColumn.key() || excelColumn.notNull(), "解析{" + excelColumn.value().name + "}列错误，值为空");
 return value;
 }
 // 2.
 int rowIndex = cell.getRowIndex();
 int columnIndex = cell.getColumnIndex();
 if (fieldType.isAssignableFrom(String.class)) {
 value = strValue;
 } else if (fieldType.isAssignableFrom(Integer.class)) {
 value = excel.getCellInteger(cell, null);
 Assert.notNull(value, "解析{" + strValue + "}错误，值应为[整型]类型");
 } else if (fieldType.isAssignableFrom(Long.class)) {
 value = excel.getCellLong(cell, null);
 Assert.notNull(value, "解析{" + strValue + "}错误，值应为[长整型]类型");
 } else if (fieldType.isAssignableFrom(Boolean.class)) {
 value = excel.getCellBoolean(cell);
 Assert.notNull(value, "解析{" + strValue + "}错误，值应为[布尔]类型");
 } else if (fieldType.isAssignableFrom(Date.class)) {
 value = excel.getCellDate(cell);
 Assert.notNull(value, "解析{" + strValue + "}错误，值应为[日期]类型");
 } else if (fieldType.isAssignableFrom(BigDecimal.class)) {
 value = excel.getCellDecimal(cell);
 Assert.notNull(value, "解析{" + strValue + "}错误，值应为[数值]类型");
 } else {
 throw new IllegalArgumentException("解析{" + strValue + "}错误，暂不支持[" + field.getType().getName() + "]类型");
 }
 return value;
 }
 
 }

package com.bob.config.mvc.excelmapping;
 
 import java.io.ByteArrayOutputStream;
 import java.io.File;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.io.InputStream;
 import java.io.OutputStream;
 import java.math.BigDecimal;
 import java.math.BigInteger;
 import java.net.URL;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.Calendar;
 import java.util.Date;
 import java.util.Iterator;
 
 import com.bob.config.mvc.excelmapping.exception.ExcelException;
 import org.apache.poi.hpsf.ClassID;
 import org.apache.poi.hssf.usermodel.HSSFPalette;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.hssf.util.HSSFColor;
 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
 import org.apache.poi.ss.usermodel.BorderStyle;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
 import org.apache.poi.ss.usermodel.ClientAnchor;
 import org.apache.poi.ss.usermodel.CreationHelper;
 import org.apache.poi.ss.usermodel.DataFormat;
 import org.apache.poi.ss.usermodel.DataFormatter;
 import org.apache.poi.ss.usermodel.DateUtil;
 import org.apache.poi.ss.usermodel.FillPatternType;
 import org.apache.poi.ss.usermodel.Font;
 import org.apache.poi.ss.usermodel.FormulaEvaluator;
 import org.apache.poi.ss.usermodel.HorizontalAlignment;
 import org.apache.poi.ss.usermodel.Hyperlink;
 import org.apache.poi.ss.usermodel.IndexedColors;
 import org.apache.poi.ss.usermodel.RichTextString;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
 import org.apache.poi.ss.usermodel.VerticalAlignment;
 import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.ss.usermodel.WorkbookFactory;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
 import org.apache.poi.xssf.usermodel.XSSFColor;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 import static java.sql.Types.NUMERIC;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC;
 import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING;
 import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_CENTER;
 import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_GENERAL;
 import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_JUSTIFY;
 import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_LEFT;
 import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_RIGHT;
 import static org.apache.poi.ss.usermodel.CellStyle.BORDER_THIN;
 import static org.apache.poi.ss.usermodel.CellStyle.SOLID_FOREGROUND;
 import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_BOTTOM;
 import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_CENTER;
 import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_TOP;
 
 /** * @since * @author wb_jjb318191 */
 public class Excel { 
 final static Logger logger = LoggerFactory.getLogger(Excel.class);
 private static final ClassID INVALID_CLASS_ID = new ClassID();
 private final boolean xlsx;
 private final Workbook workbook;
 private CreationHelper creationHelper;
 private FormulaEvaluator evaluator;
 private DataFormatter formatter;
 private Sheet sheet; // active sheet
 private Row lastRow; // row object of last search
 private Cell lastCell; // cell object of last search
 private int lastRowIndex; // row index of last search
 private int lastCellIndex; // cell index of last search
 private short nowColorIndex = 0x3F; // custom color added from ox40 to ox08!
 @SuppressWarnings("unused")
 private final boolean autoWidth = false;
 
 
 /** * */
 public Excel() {
 this(false);
 }
 
 /** * HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. * * XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file * format. * * @param xlsx * the Excel 2007 OOXML (.xlsx) file */
 public Excel(boolean xlsx) {
 this(xlsx ? new XSSFWorkbook() : new HSSFWorkbook());
 }
 
 /** * @param workbook */
 public Excel(Workbook workbook) {
 if (null == workbook) {
 throw new IllegalArgumentException("workbook");
 }
 this.xlsx = (workbook instanceof XSSFWorkbook);
 this.workbook = workbook;
 /*if (workbook instanceof HSSFWorkbook) { DirectoryNode root = ((HSSFWorkbook) workbook).getRootDirectory(); if (null != root) { if (INVALID_CLASS_ID.equals(root.getStorageClsid())) { root.setStorageClsid(ClassID.EXCEL97); } } else { @SuppressWarnings("resource") POIFSFileSystem fs = new POIFSFileSystem(); root = fs.getRoot(); root.setStorageClsid(ClassID.EXCEL97); BeanWrapperImpl beanWrapper = new BeanWrapperImpl(workbook); beanWrapper.setPropertyValue("directory", root); beanWrapper.setPropertyValue("preserveNodes", true); } }*/
 setActiveSheet();
 }
 
 /** * * @param url * @throws IOException */
 public Excel(URL url) throws IOException {
 this(url.openStream());
 }
 
 /** * @param istream * @throws IOException */
 public Excel(InputStream istream) throws IOException {
 this(create(istream));
 }
 
 /** * @param file * @throws IOException */
 public Excel(File file) throws IOException {
 this(create(file));
 }
 
 /** * @param fileName * @throws IOException */
 public Excel(String fileName) throws IOException {
 this(create(new File(fileName)));
 }
 
 /** * @param istream * @throws IOException */
 public static Workbook create(InputStream istream) throws IOException {
 try {
 return WorkbookFactory.create(istream);
 } catch (InvalidFormatException e) {
 throw new ExcelException("试图读取无效的Excel格式(XLS/XLSX)数据！", e);
 }
 }
 
 /** * @param file * @throws IOException */
 public static Workbook create(File file) throws IOException {
 try {
 return WorkbookFactory.create(file);
 } catch (InvalidFormatException e) {
 throw new ExcelException("试图打开无效的Excel格式(XLS/XLSX)文件！", e);
 }
 }
 
 /** * @param ostream * NOT Automatic Close * @param close * Close Excel Object * @throws IOException */
 public void write(OutputStream ostream, boolean close) throws IOException {
 workbook.write(ostream);
 }
 
 /** * @param ostream * NOT Automatic Close * @throws IOException */
 public void write(OutputStream ostream) throws IOException {
 write(ostream, true);
 }
 
 /** * @param file * @param close * Close Excel Object * @throws IOException */
 public void write(File file, boolean close) throws IOException {
 FileOutputStream ostream = new FileOutputStream(file);
 boolean success = false;
 try {
 write(ostream);
 success = true;
 } finally {
 try {
 ostream.close();
 } catch (IOException e) {
 if (success) {
 throw e;
 }
 }
 }
 }
 
 /** * @param file * @throws IOException */
 public void write(File file) throws IOException {
 write(file, true);
 }
 
 /** * @return the xlsx */
 public boolean isXLSX() {
 return xlsx;
 }
 
 /** * @return the workbook */
 protected Workbook getWorkbook() {
 return workbook;
 }
 
 /** * @return the creationHelper */
 protected final CreationHelper getCreationHelper() {
 CreationHelper result = creationHelper;
 if (null == result) {
 result = workbook.getCreationHelper();
 creationHelper = result;
 }
 return result;
 }
 
 /** * @param text * @return */
 public RichTextString createRichTextString(String text) {
 return getCreationHelper().createRichTextString(text);
 }
 
 /** * @return */
 public DataFormat createDataFormat() {
 return getCreationHelper().createDataFormat();
 }
 
 /** * @return */
 public ClientAnchor createClientAnchor() {
 return getCreationHelper().createClientAnchor();
 }
 
 /** * @param type * @return */
 public Hyperlink createHyperlink(int type) {
 return getCreationHelper().createHyperlink(type);
 }
 
 /** * @return the formulaEvaluator */
 protected final FormulaEvaluator getFormulaEvaluator() {
 FormulaEvaluator result = evaluator;
 if (null == result) {
 result = getCreationHelper().createFormulaEvaluator();
 evaluator = result;
 }
 return result;
 }
 
 /** * @return the dataFormatter */
 protected final DataFormatter getDataFormatter() {
 DataFormatter result = formatter;
 if (null == result) {
 result = new DataFormatter();
 formatter = result;
 }
 return result;
 }
 
 /** * @return the active sheet */
 public Sheet getSheet() {
 return sheet;
 }
 
 /** * * @return */
 public int getSheetCount() {
 return workbook.getNumberOfSheets();
 }
 
 /** * @param index * of the sheet number (0-based physical & logical) * @return */
 public Sheet getSheetAt(int index) {
 return workbook.getSheetAt(index);
 }
 
 /** * * @param sheet */
 protected void setSheet(Sheet sheet) {
 this.sheet = sheet;
 lastRow = null;
 lastCell = null;
 lastRowIndex = -1;
 lastCellIndex = -1;
 }
 
 /** * @param sheetName */
 public void setSheetName(String sheetName) {
 int index = workbook.getActiveSheetIndex();
 if (index >= 0) {
 workbook.setSheetName(index, sheetName);
 }
 }
 
 /** * @param index * index of the sheet (0-based) * @param sheetName */
 public void setSheetName(int index, String sheetName) {
 workbook.setSheetName(index, sheetName);
 }
 
 /** * @return */
 public int getActiveSheetIndex() {
 return workbook.getActiveSheetIndex();
 }
 
 /** * */
 protected void setActiveSheet() {
 Sheet activeSheet;
 int count = workbook.getNumberOfSheets();
 if (count > 0) {
 int index = workbook.getActiveSheetIndex();
 if (index < 0 || index >= count) {
 index = 0;
 workbook.setActiveSheet(0);
 }
 activeSheet = workbook.getSheetAt(index);
 } else {
 activeSheet = workbook.createSheet();
 }
 setSheet(activeSheet);
 }
 
 /** * @param index * index of the active sheet (0-based) */
 public void setActiveSheet(int index) {
 workbook.setActiveSheet(index);
 setActiveSheet();
 }
 
 /** * @return */
 public Sheet createSheet() {
 Sheet newSheet = workbook.createSheet();
 int index = workbook.getSheetIndex(newSheet);
 workbook.setActiveSheet(index);
 setSheet(newSheet);
 return newSheet;
 }
 
 /** * @param sheetName * @return */
 public Sheet createSheet(String sheetName) {
 Sheet newSheet = workbook.createSheet(sheetName);
 int index = workbook.getSheetIndex(newSheet);
 workbook.setActiveSheet(index);
 setSheet(newSheet);
 return newSheet;
 }
 
 
 /** * Returns the number of phsyically defined rows (NOT the number of rows in the sheet) * * @return */
 public int getPhysicalNumberOfRows() {
 return sheet.getPhysicalNumberOfRows();
 }
 
 /** * Gets the first row on the sheet * * @return the number of the first logical row on the sheet, zero based */
 public int getFirstRowNum() {
 return sheet.getFirstRowNum();
 }
 
 /** * Gets the number last row on the sheet. Owing to idiosyncrasies in the excel file format, if * the result of calling this method is zero, you can't tell if that means there are zero rows * on the sheet, or one at position zero. For that case, additionally call * {@link #getPhysicalNumberOfRows()} to tell if there is a row at position zero or not. * * @return the number of the last row contained in this sheet, zero based. */
 public int getLastRowNum() {
 return sheet.getLastRowNum();
 }
 
 /** * * @return (lastRow - firstRow + 1) * @see #getPhysicalNumberOfRows() */
 public int getRowCount() {
 int firstRow = getFirstRowNum();
 if (firstRow >= 0) {
 int lastRow = getLastRowNum();
 if (lastRow >= 0) {
 return (lastRow - firstRow + 1);
 }
 }
 return (0);
 }
 
 /** * Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you * get a null. This is to say row 4 represents the fifth row on a sheet. * * @param rowIndex * 0 based row number * @return Row representing the rownumber or create new if its not defined on the sheet, never * to return null */
 public Row getRow(int rowIndex) {
 Row row;
 if (rowIndex != lastRowIndex) {
 row = sheet.getRow(rowIndex);
 if (null == row) {
 row = sheet.createRow(rowIndex);
 }
 lastRow = row;
 lastRowIndex = rowIndex;
 } else {
 row = lastRow;
 }
 return row;
 }
 
 /** * @return an iterator of the PHYSICAL rows. Meaning the 3rd element may not be the third row if * say for instance the second row is undefined. Call getRowNum() on each row if you * care which one it is. */
 public Iterator<Row> iterator() {
 return sheet.iterator();
 }
 
 /** * get the number of the first cell contained in this row. * * @param rowIndex * 0 based row number * @return short representing the first logical cell in the row, or -1 if the row does not * contain any cells. */
 public short getFirstCellNum(int rowIndex) {
 return getRow(rowIndex).getFirstCellNum();
 }
 
 /** * Gets the index of the last cell contained in this row PLUS ONE. The result also * happens to be the 1-based column number of the last cell. This value can be used as a * standard upper bound when iterating over cells: * * <pre> * short minColIx = row.getFirstCellNum(); * short maxColIx = row.getLastCellNum(); * for (short colIx = minColIx; colIx < maxColIx; colIx++) { * Cell cell = row.getCell(colIx); * if (cell == null) { * continue; * } * // ... do something with cell * } * </pre> * * @param rowIndex * 0 based row number * @return short representing the last logical cell in the row PLUS ONE, or -1 if the row * does not contain any cells. */
 public short getLastCellNum(int rowIndex) {
 return getRow(rowIndex).getLastCellNum();
 }
 
 /** * gets the number of defined cells (NOT number of cells in the actual row!). That is to say if * only columns 0,4,5 have values then there would be 3. * * @param rowIndex * 0 based row number * @return int representing the number of defined cells in the row. */
 public int getPhysicalNumberOfCells(int rowIndex) {
 return getRow(rowIndex).getPhysicalNumberOfCells();
 }
 
 /** * 不同于 getFirstCellNum(int)面向单行查找并返回，getFirstColumnNum()是面向所有getFirstRowNum() * 与getLastRowNum()之间的行查找并返回最小的起始有效列。 * * @return * @see #getFirstCellNum(int) * @see #getFirstRowNum() * @see #getLastRowNum() */
 public short getFirstColumnNum() {
 int firstRow = getFirstRowNum();
 if (firstRow >= 0) {
 int lastRow = getLastRowNum();
 short minColIx = Short.MAX_VALUE;
 short firstCell;
 Row row;
 for (int i = firstRow; i <= lastRow; ++i) {
 row = sheet.getRow(i);
 if (null != row) {
 firstCell = row.getFirstCellNum();
 if (firstCell >= 0 && firstCell < minColIx) {
 minColIx = firstCell;
 }
 }
 }
 if (minColIx < Short.MAX_VALUE) {
 return minColIx;
 }
 }
 return (-1);
 }
 
 /** * 不同于 getLastCellNum(int)面向单行查找并返回，getLastColumnNum()是面向所有getFirstRowNum() * 与getLastRowNum()之间的行查找并返回最大的最后有效列。 * * 与 getLastCellNum(int) 方法一样，返回包含内容的最后有效列加1(PLUS ONE)。 * * @return * @see #getLastCellNum(int) * @see #getFirstRowNum() * @see #getLastRowNum() */
 public short getLastColumnNum() {
 int firstRow = getFirstRowNum();
 if (firstRow >= 0) {
 int lastRow = getLastRowNum();
 short maxColIx = Short.MIN_VALUE;
 short lastCell;
 Row row;
 for (int i = firstRow; i <= lastRow; ++i) {
 row = sheet.getRow(i);
 if (null != row) {
 lastCell = row.getLastCellNum();
 if (lastCell >= 0 && lastCell > maxColIx) {
 maxColIx = lastCell;
 }
 }
 }
 if (maxColIx >= 0) {
 return maxColIx;
 }
 }
 return (-1);
 }
 
 /** * 不同于 getPhysicalNumberOfCells(int)面向单行查找并返回，getColumnCount() 是面向所有getFirstRowNum() * 与getLastRowNum()之间的行查找并返回最小的起始有效列与最大的最后有效列之间的列数，包括两者。 * * @return (lastCol - firstCol) * @see #getPhysicalNumberOfCells(int) * @see #getFirstColumnNum() * @see #getLastColumnNum() */
 public int getColumnCount() {
 short firstCol = getFirstColumnNum();
 if (firstCol >= 0) {
 short lastCol = getLastColumnNum();
 if (lastCol > 0) { // NOTE: PLUS ONE
 return (lastCol - firstCol);
 }
 }
 return (0);
 }
 
 /** * /** Get the cell representing a given column (logical cell) 0-based. If you ask for a cell * that is not defined then you get a null, unless you have set a different on the base * workbook. * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return Cell representing that column or create new if undefined. never to return null. */
 public Cell getCell(int rowIndex, int cellIndex) {
 Cell cell;
 if (cellIndex != lastCellIndex || rowIndex != lastRowIndex) {
 Row row = getRow(rowIndex);
 cell = row.getCell(cellIndex);
 if (null == cell) {
 cell = row.createCell(cellIndex);
 }
 lastCell = cell;
 lastCellIndex = cellIndex;
 } else {
 cell = lastCell;
 }
 return cell;
 }
 
 /** * Get the cell representing a given column (logical cell) 0-based. If you ask for a cell that * is not defined then you get a null, unless you have set a different on the base workbook. * * @param row * @param cellIndex * 0 based column number * @return Cell representing that column or create new if undefined. never to return null. */
 public Cell getCell(Row row, int cellIndex) {
 Cell cell = row.getCell(cellIndex);
 if (null == cell) {
 cell = row.createCell(cellIndex);
 }
 return cell;
 }
 
 /** * * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If * the Excel format pattern cannot be parsed then the cell value will be formatted using a * default format. * * * When passed a null or blank cell, this method will return an empty String (""). Formula cells * will be evaluated. The caller is responsible for setting the currentRow on the evaluator * * * @param cell * @return a string value of the cell */
 public String formatCellValue(Cell cell) {
 return getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
 }
 
 /** * * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If * the Excel format pattern cannot be parsed then the cell value will be formatted using a * default format. * * * When passed a null or blank cell, this method will return an empty String (""). Formula cells * will be evaluated. The caller is responsible for setting the currentRow on the evaluator * * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return a string value of the cell */
 public String formatCellValue(int rowIndex, int cellIndex) {
 return formatCellValue(getCell(rowIndex, cellIndex));
 }
 
 /** * * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If * the Excel format pattern cannot be parsed then the cell value will be formatted using a * default format. * * * When passed a null or blank cell, this method will return an empty String (""). Formula cells * will be evaluated. The caller is responsible for setting the currentRow on the evaluator * * * @param row * @param cellIndex * 0 based column number * @return a string value of the cell */
 public String formatCellValue(Row row, int cellIndex) {
 return formatCellValue(getCell(row, cellIndex));
 }
 
 /** * * @param cell * @return */
 public Object getRichCellValue(Cell cell) {
 switch (cell.getCellType()) {
 case CELL_TYPE_NUMERIC:
 if (DateUtil.isCellDateFormatted(cell)) {
 return cell.getDateCellValue();
 } else {
 return Double.valueOf(cell.getNumericCellValue());
 }
 case CELL_TYPE_STRING:
 return cell.getRichStringCellValue();
 case CELL_TYPE_BOOLEAN:
 return Boolean.valueOf(cell.getBooleanCellValue());
 case CELL_TYPE_FORMULA:
 switch (cell.getCachedFormulaResultType()) {
 case CELL_TYPE_NUMERIC:
 if (DateUtil.isCellDateFormatted(cell)) {
 return cell.getDateCellValue();
 } else {
 return Double.valueOf(cell.getNumericCellValue());
 }
 case CELL_TYPE_STRING:
 return cell.getRichStringCellValue();
 case CELL_TYPE_BOOLEAN:
 return Boolean.valueOf(cell.getBooleanCellValue());
 }
 case CELL_TYPE_ERROR:
 case CELL_TYPE_BLANK:
 default:
 return null;
 }
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Object getRichCellValue(int rowIndex, int cellIndex) {
 return getRichCellValue(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Object getRichCellValue(Row row, int cellIndex) {
 return getRichCellValue(getCell(row, cellIndex));
 }
 
 /** * * @param cell * @return */
 public Object getCellValue(Cell cell) {
 RichTextString richText;
 switch (cell.getCellType()) {
 case CELL_TYPE_NUMERIC:
 if (DateUtil.isCellDateFormatted(cell)) {
 return cell.getDateCellValue();
 } else {
 return Double.valueOf(cell.getNumericCellValue());
 }
 case CELL_TYPE_STRING:
 richText = cell.getRichStringCellValue();
 if (null != richText) {
 return richText.getString();
 } else {
 return null;
 }
 case CELL_TYPE_BOOLEAN:
 return Boolean.valueOf(cell.getBooleanCellValue());
 case CELL_TYPE_FORMULA:
 switch (cell.getCachedFormulaResultType()) {
 case CELL_TYPE_NUMERIC:
 if (DateUtil.isCellDateFormatted(cell)) {
 return cell.getDateCellValue();
 } else {
 return Double.valueOf(cell.getNumericCellValue());
 }
 case CELL_TYPE_STRING:
 richText = cell.getRichStringCellValue();
 if (null != richText) {
 return richText.getString();
 } else {
 return null;
 }
 case CELL_TYPE_BOOLEAN:
 return Boolean.valueOf(cell.getBooleanCellValue());
 }
 case CELL_TYPE_ERROR:
 case CELL_TYPE_BLANK:
 default:
 return null;
 }
 }
 
 
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Object getCellValue(int rowIndex, int cellIndex) {
 return getCellValue(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Object getCellValue(Row row, int cellIndex) {
 return getCellValue(getCell(row, cellIndex));
 }
 
 /** * * @param cell * @return */
 public String getCellString(Cell cell) {
 return getCellString(cell,null);
 }
 
 /** * @param cell * @param defaultValue * @return */
 public String getCellString(Cell cell, String defaultValue) {
 Object value = getCellValue(cell);
 if(value == null){
 return null;
 }else if(value instanceof Date){
 try {
 return new SimpleDateFormat("yyyy-MM-dd").format((Date)value);
 } catch (Exception e) {
 throw new IllegalArgumentException("坐标为{"+cell.getRowIndex()+"},{"+cell.getColumnIndex()+"}的Cell不符合日期格式",e);
 }
 }else if(value instanceof Number){
 return ((Number)value).toString();
 }else if(value instanceof Boolean){
 return ((Boolean)value).toString();
 }else if(value instanceof String){
 return (String)value;
 }else{
 return defaultValue;
 }
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public String getCellString(int rowIndex, int cellIndex) {
 return getCellString(getCell(rowIndex, cellIndex));
 }
 
 /** * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @param defaultValue * @return */
 public String getCellString(int rowIndex, int cellIndex, String defaultValue) {
 return getCellString(getCell(rowIndex, cellIndex), defaultValue);
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public String getCellString(Row row, int cellIndex) {
 return getCellString(getCell(row, cellIndex));
 }
 
 /** * * @param cell * @return */
 public int getCellInt(Cell cell) {
 return intValue(getCellValue(cell),0);
 }
 
 /** * * @param value * @param defaultValue * @return 如果 value 为空值(null)、"null"、空串("")或空白串(" ")或存在格式异常则返回 defaultValue */
 private int intValue(Object value, int defaultValue) {
 if (null == value) {
 return defaultValue;
 } else if (value instanceof Integer) {
 return ((Integer) value).intValue();
 } else if (value instanceof Number) {
 if (isInfiniteOrNaN(value)) {
 return defaultValue;
 }
 return ((Number) value).intValue();
 } else if(value instanceof String) {
 return Integer.valueOf((String)value).intValue();
 }else{
 return defaultValue;
 }
 }
 
 /** * * @param value * @return */
 private boolean isInfiniteOrNaN(Object value) {
 if (value instanceof Double) {
 Double d = (Double) value;
 return (d.isInfinite() || d.isNaN());
 } else if (value instanceof Float) {
 Float f = (Float) value;
 return (f.isInfinite() || f.isNaN());
 } else {
 return false;
 }
 }
 
 /** * @param cell * @param defaultValue * @return */
 public int getCellInt(Cell cell, int defaultValue) {
 return intValue(getCellValue(cell),defaultValue);
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public int getCellInt(int rowIndex, int cellIndex) {
 return getCellInt(getCell(rowIndex, cellIndex));
 }
 
 /** * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @param defaultValue * @return */
 public int getCellInt(int rowIndex, int cellIndex, int defaultValue) {
 return getCellInt(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public int getCellInt(Row row, int cellIndex) {
 return getCellInt(getCell(row, cellIndex));
 }
 
 /** * 无损数字解析，可能返回类型：Integer, Long, BigInteger, BigDecimal * * @param value * @param defaultValue * @return 如果 s 为空值(null)、"null"、空串("")或空白串(" ")，则返回null，存在格式异常则返回 defaultValue */
 private Number numberOf(final Object value, final Number defaultValue) {
 if (null != value) {
 if (value instanceof Number) {
 if (value instanceof Integer || value instanceof Long) {
 return (Integer) value;
 } else if (value instanceof BigDecimal || value instanceof BigInteger) {
 return (BigDecimal) value;
 } else if (value instanceof Double) {
 return (Double)value;
 } else if (value instanceof Float) {
 return (Float)value;
 }
 }
 }
 return null;
 }
 
 /** * * @param value * @param defaultValue * @return 如果 value 为空值(null)、空串("")或"null"，则返回null，存在格式异常则返回 defaultValue */
 private Integer integerOf(Object value, Integer defaultValue) {
 Number n = numberOf(value, defaultValue);
 if (null != n) {
 if (n instanceof Integer) {
 return (Integer) n;
 } else {
 return Integer.valueOf(n.intValue());
 }
 }
 return null;
 }
 
 /** * * @param cell * @return */
 public Integer getCellInteger(Cell cell) {
 return getCellInteger(cell,null);
 }
 
 /** * * @param cell * @param defaultValue * @return */
 public Integer getCellInteger(Cell cell, Integer defaultValue) {
 return integerOf(getCellValue(cell), defaultValue);
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Integer getCellInteger(int rowIndex, int cellIndex) {
 return getCellInteger(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @param defaultValue * @return */
 public Integer getCellInteger(int rowIndex, int cellIndex, Integer defaultValue) {
 return getCellInteger(getCell(rowIndex, cellIndex), defaultValue);
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Integer getCellInteger(Row row, int cellIndex) {
 return getCellInteger(getCell(row, cellIndex));
 }
 
 /** * * @param value * @param defaultValue * @return 如果 value 为空值(null)、空串("")或"null"，则返回null，存在格式异常则返回 defaultValue */
 private Long longOf(Object value, Long defaultValue) {
 Number n = numberOf(value, defaultValue);
 if (null != n) {
 if (n instanceof Long) {
 return (Long) n;
 } else {
 return Long.valueOf(n.longValue());
 }
 }
 return null;
 }
 
 /** * * @param cell * @return */
 public Long getCellLong(Cell cell) {
 return longOf(getCellValue(cell),null);
 }
 
 /** * * @param cell * @param defaultValue * @return */
 public Long getCellLong(Cell cell, Long defaultValue) {
 return longOf(getCellValue(cell), defaultValue);
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Long getCellLong(int rowIndex, int cellIndex) {
 return getCellLong(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @param defaultValue * @return */
 public Long getCellLong(int rowIndex, int cellIndex, Long defaultValue) {
 return longOf(getCell(rowIndex, cellIndex), defaultValue);
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Long getCellLong(Row row, int cellIndex) {
 return getCellLong(getCell(row, cellIndex));
 }
 
 
 
 /** * * @param cell * @return */
 public Date getCellDate(Cell cell) {
 Object value = getCellValue(cell);
 if (null == value) {
 return null;
 } else if (value instanceof Date) {
 return (Date) value;
 } else if (value instanceof String) {
 return parseDateText((String)value);
 } else {
 return null;
 }
 }
 
 /** * @param text * @return */
 private Date parseDateText(String text){
 try {
 return new SimpleDateFormat(text.length() < 10 ? "yyyy-MM-dd":"yyyy-MM-dd hh:mm:ss").parse(text);
 } catch (ParseException e) {
 logger.error("解析日志字符串{[]}出现异常",text,e);
 return null;
 }
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Date getCellDate(int rowIndex, int cellIndex) {
 return getCellDate(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Date getCellDate(Row row, int cellIndex) {
 return getCellDate(getCell(row, cellIndex));
 }
 
 /** * * @param cell * @return */
 public Boolean getCellBoolean(Cell cell) {
 Object value = getCellValue(cell);
 if (null == value) {
 return null;
 } else if (value instanceof Boolean) {
 return (Boolean)value;
 } else if (value instanceof String) {
 return valueOf((String)value, null);
 } else if (value instanceof Integer) {
 return (Integer)value == 1;
 } else if(value instanceof Double) {
 return ((Double)value).intValue() == 1;
 }else{
 return null;
 }
 }
 
 /** * * @param text * @param defaultValue * @return */
 private Boolean valueOf(String text, Boolean defaultValue) {
 if (null == text || text.isEmpty() || text.trim().isEmpty()) {
 return defaultValue;
 }
 if (text.equalsIgnoreCase("true")) {
 return Boolean.TRUE;
 }
 if (text.equalsIgnoreCase("false")) {
 return Boolean.FALSE;
 }
 return defaultValue;
 }
 
 /** * * @param rowIndex * 0 based row number * @param cellIndex * 0 based column number * @return */
 public Boolean getCellBoolean(int rowIndex, int cellIndex) {
 return getCellBoolean(getCell(rowIndex, cellIndex));
 }
 
 /** * * @param row * @param cellIndex * 0 based column number * @return */
 public Boolean getCellBoolean(Row row, int cellIndex) {
 return getCellBoolean(getCell(row, cellIndex));
 }
 
 /** * @param cell * @return */
 public BigDecimal getCellDecimal(Cell cell){
 return toBigDecimal(numberOf(getCellValue(cell),null),BigDecimal.ZERO);
 }
 
 /** * @param n * @param defaultValue * @return */
 private BigDecimal toBigDecimal(Number n, BigDecimal defaultValue) {
 if (null == n) {
 return null;
 } else if (n instanceof BigDecimal) {
 return (BigDecimal) n;
 } else if (n instanceof BigInteger) {
 return new BigDecimal((BigInteger) n);
 } else if (n instanceof Double || n instanceof Float) {
 if (isInfiniteOrNaN(n)) {
 return defaultValue;
 }
 /** * 对于不是 double/float NaN 和 ±Infinity 的值，BigDecimal(String)构造方法与 * Double/Float.toString(double/float) 返回的值兼容。这通常是将 double/float 转换为 BigDecimal * 的首选方法，因为它不会遇到 BigDecimal(double) 构造方法的不可预知问题。 */
 return new BigDecimal(n.toString());
 } else {
 return BigDecimal.valueOf(n.longValue());
 }
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @return */
 public Cell setCellBlank(int rowIndex, int colIndex) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellType(CELL_TYPE_BLANK);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, String value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(createRichTextString(value));
 CellStyle cellStyle = cell.getCellStyle();
 if (!cellStyle.getShrinkToFit()) {
 cellStyle.setShrinkToFit(true);
 cell.setCellStyle(cellStyle);
 }
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, RichTextString value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, byte value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, short value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, int value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, long value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, double value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, Double value) {
 Cell cell = getCell(rowIndex, colIndex);
 if (null == value) {
 cell.setCellType(CELL_TYPE_BLANK);
 } else {
 cell.setCellValue(value);
 }
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, BigDecimal value) {
 Cell cell = getCell(rowIndex, colIndex);
 if (null == value) {
 cell.setCellType(CELL_TYPE_BLANK);
 } else {
 cell.setCellValue(value.doubleValue());
 }
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, Number value) {
 Cell cell = getCell(rowIndex, colIndex);
 if (null == value) {
 cell.setCellType(CELL_TYPE_BLANK);
 } else {
 cell.setCellValue(value.doubleValue());
 }
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, boolean value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, Boolean value) {
 Cell cell = getCell(rowIndex, colIndex);
 if (null == value) {
 cell.setCellType(CELL_TYPE_BLANK);
 } else {
 cell.setCellValue(value);
 }
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, Date value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param value * @return */
 public Cell setCell(int rowIndex, int colIndex, Calendar value) {
 Cell cell = getCell(rowIndex, colIndex);
 cell.setCellValue(value);
 return cell;
 }
 
 /** * 合并单元格 起始行列+跨度 >>> 起止行列 * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param rowSpan * @param colSpan * @param border */
 public void mergedCells(int rowIndex, int colIndex, int rowSpan, int colSpan, int border) {
 sheet.addMergedRegion(new CellRangeAddress(rowIndex, (rowIndex + rowSpan - 1), colIndex, (colIndex + colSpan - 1)));
 if ((border & 15) != 0) {
 for (int r = 0; r < rowSpan; r++) {
 for (int c = 0; c < colSpan; c++) {
 Cell cell = getCell(rowIndex + r, colIndex + c);
 CellStyle cellStyle = workbook.createCellStyle(); // 在工作薄的基础上建立一个样式
 if ((border & 1) == 1) {
 cellStyle.setBorderLeft(BORDER_THIN);
 }
 if ((border & 2) == 2) {
 cellStyle.setBorderTop(BORDER_THIN);
 }
 if ((border & 4) == 4) {
 cellStyle.setBorderRight(BORDER_THIN);
 }
 if ((border & 8) == 8) {
 cellStyle.setBorderBottom(BORDER_THIN);
 }
 cell.setCellStyle(cellStyle);
 }
 }
 }
 }
 
 /** * 设置行高InPoints：Height的单位是twips (1/20 of a point)(height:3point=4像素) * * @param rowIndex * 0 based row number * @param height * @return */
 public Row setHeight(int rowIndex, float height) {
 Row row = sheet.getRow(rowIndex);
 row.setHeightInPoints(height);
 return row;
 }
 
 /** * 设置列宽：单位是1/256个字符宽度 (256/14*2=36.57) //!=35.7 * * @param colIndex * 0 based column number * @param width */
 public void setWidth(int colIndex, int width) {
 sheet.setColumnWidth(colIndex, (int) Math.abs(width * 36.57));
 if (width <= 0) {
 setHidden(colIndex);
 }
 }
 
 /** * 设置列隐藏Hidden * * @param colIndex * 0 based column number */
 public void setHidden(int colIndex) {
 sheet.setColumnHidden(colIndex, true);
 }
 
 /** * 设置单元格格式：统一 style: 0大标题/1首尾项/2列标头/>=3数据列 * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param style * @return */
 public Cell setCellStyle(int rowIndex, int colIndex, int style) {
 Cell cell = getCell(rowIndex, colIndex);
 CellStyle cellStyle = getCellStyle(cell, style);
 cell.setCellStyle(cellStyle);
 return cell;
 }
 
 /** * 设置单元格格式：统一 style: 0大标题/1首尾项/2列标头/>=3数据列 * * @param cell * @param style * @return */
 public Cell setCellStyle(Cell cell, int style) {
 CellStyle cellStyle = getCellStyle(cell, style);
 cell.setCellStyle(cellStyle);
 return cell;
 }
 
 /** * 生成单元格格式：独立 * * @param style * @return */
 public CellStyle getCellStyle(int style) {
 CellStyle cellStyle = workbook.createCellStyle(); // 在工作薄的基础上建立一个样式
 cellStyle = getCellStyle(cellStyle, style);
 return cellStyle;
 }
 
 /** * 生成单元格格式：叠加 * * @param cell * @param style * @return */
 public CellStyle getCellStyle(Cell cell, int style) {
 CellStyle cellStyle = cell.getCellStyle();
 cellStyle = getCellStyle(cellStyle, style);
 return cellStyle;
 }
 
 /** * 生成单元格格式 style: 0大标题/1首尾项/2列标头/3~n数据列 * * @param cellStyle * @param style * @return */
 public CellStyle getCellStyle(CellStyle cellStyle, int style) {
 if (null == cellStyle) {
 cellStyle = workbook.createCellStyle();
 }
 // 统一：
 // 1.边框
 if (style >= 2) {
 cellStyle.setBorderLeft(BORDER_THIN);
 cellStyle.setBorderTop(BORDER_THIN);
 cellStyle.setBorderRight(BORDER_THIN);
 cellStyle.setBorderBottom(BORDER_THIN);
 }
 // 2.对齐：
 if (style <= 0 || style == 2) {
 cellStyle.setAlignment(ALIGN_CENTER);
 }
 cellStyle.setVerticalAlignment(VERTICAL_CENTER); // VERTICAL_TOP
 // 3.字体：
 Font font = workbook.createFont();
 if (style == 0) {
 font.setFontName("华文新魏");
 font.setFontHeightInPoints((short) 18);
 } else {
 font.setFontName("宋体");
 font.setFontHeightInPoints((short) 9);
 }
 cellStyle.setFont(font);
 // 4.换行：
 cellStyle.setWrapText(true);
 // 5.背景：
 /*if (style == 2) { // TODO cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex()); cellStyle.setFillBackgroundColor(IndexedColors.AUTOMATIC.getIndex()); }*/
 // 9.个性：
 switch (style) {
 case 0: //
 break;
 case 1: //
 break;
 case 2: //
 break;
 default: //
 }
 //
 return cellStyle;
 }
 
 /** * 生成单元格格式：叠加(个性化) border, align, font, other * * @param cellStyle * @param border * @param align * @param fonts * @param other * @param color * @return */
 public CellStyle getCellStyle(CellStyle cellStyle, int border, int align, int fonts, int other, int color) {
 CellStyle newStyle = workbook.createCellStyle();
 newStyle.cloneStyleFrom(cellStyle);
 // 1.Border: 1=Left/2=Top/4=Right/8=Bottom/?=粗细
 if ((border & 1) == 1) {
 newStyle.setBorderLeft(BORDER_THIN);
 }
 if ((border & 2) == 2) {
 newStyle.setBorderTop(BORDER_THIN);
 }
 if ((border & 4) == 4) {
 newStyle.setBorderRight(BORDER_THIN);
 }
 if ((border & 8) == 8) {
 newStyle.setBorderBottom(BORDER_THIN);
 }
 // 2.Align: cellStyle.ALIGN_GENERAL, cellStyle.ALIGN_LEFT,
 // cellStyle.ALIGN_CENTER, cellStyle.ALIGN_RIGHT,
 // cellStyle.ALIGN_JUSTIFY, cellStyle.ALIGN_FILL,
 // cellStyle.ALIGN_CENTER_SELECTION
 if ((align & 15) == 0) { // 0=GENERAL/1=LEFT/2=CENTER/4=RIGHT/8=JUSTIFY/!=FILL
 newStyle.setAlignment(ALIGN_GENERAL);
 } else if ((align & 15) == 1) {
 newStyle.setAlignment(ALIGN_LEFT);
 } else if ((align & 15) == 2) {
 newStyle.setAlignment(ALIGN_CENTER);
 } else if ((align & 15) == 4) {
 newStyle.setAlignment(ALIGN_RIGHT);
 } else if ((align & 15) == 8) {
 newStyle.setAlignment(ALIGN_JUSTIFY);
 } else {
 // newStyle.setAlignment(CellStyle.ALIGN_FILL); //none
 }
 // VerticalAlignment: cellStyle.VERTICAL_TOP, cellStyle.VERTICAL_CENTER,
 // cellStyle.VERTICAL_BOTTOM,
 // cellStyle.VERTICAL_JUSTIFY
 if ((align & 112) == 16) { // 16=TOP/32=CENTER/64=BOTTOM/!=JUSTIFY
 newStyle.setVerticalAlignment(VERTICAL_TOP);
 } else if ((align & 112) == 32) {
 newStyle.setVerticalAlignment(VERTICAL_CENTER);
 } else if ((align & 112) == 64) {
 newStyle.setVerticalAlignment(VERTICAL_BOTTOM);
 } else {
 // newStyle.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);
 }
 // 3.fonts: (fonts % 100) = font.size && (fonts / 100) = font.name &&.
 int iFontSize = (fonts % 100);
 int iFontName = ((fonts % 1000) / 100);
 int iFontType = ((fonts % 10000) / 1000);
 int iFontClrs = ((fonts % 100000) / 10000);
 if ((iFontSize + iFontName + iFontType + iFontClrs) > 0) {
 Font font = workbook.createFont(); // 应该先从当前Style中提取Font，再在其上增改。
 // 字号
 if (iFontSize <= 0) {
 iFontSize = 9;
 }
 font.setFontHeightInPoints((short) iFontSize);
 // 字体
 String sFontName = "宋体";
 switch (iFontName) {
 case 9: //
 sFontName = "Arial";
 break;
 case 8: //
 sFontName = "Times New Roman";
 break;
 case 7: //
 sFontName = "Wingdings";
 break;
 case 6: //
 sFontName = "华文新魏";
 break;
 case 5: //
 sFontName = "华文彩云";
 break;
 case 4: //
 sFontName = "幼圆";
 break;
 case 3: //
 sFontName = "黑体";
 break;
 case 2: //
 sFontName = "隶书";
 break;
 }
 font.setFontName(sFontName);
 if (iFontType > 0) {
 if ((iFontType & 1) == 1) {
 //font.setBold(true);
 }
 if ((iFontType & 2) == 2) {
 font.setItalic(true);
 }
 if ((iFontType & 4) == 4) {
 font.setUnderline((byte) 1);
 }
 if ((iFontType & 8) == 8) {
 font.setStrikeout(true);
 }
 // if ((iFontType & 15) == ?) { ... }
 }
 if (iFontClrs > 0) {
 short colorIdx = 0;
 switch (iFontClrs) {
 case 1: //
 colorIdx = IndexedColors.RED.getIndex();
 break;
 case 2: //
 colorIdx = IndexedColors.GREEN.getIndex();
 break;
 case 3: //
 colorIdx = IndexedColors.BLUE.getIndex();
 break;
 case 4: //
 colorIdx = IndexedColors.LIME.getIndex();
 break;
 case 5: //
 colorIdx = IndexedColors.MAROON.getIndex();
 break;
 case 6: //
 colorIdx = IndexedColors.ROSE.getIndex();
 break;
 case 7: //
 colorIdx = IndexedColors.GOLD.getIndex();
 break;
 case 8: //
 colorIdx = IndexedColors.OLIVE_GREEN.getIndex();
 break;
 case 9: //
 colorIdx = IndexedColors.GREY_25_PERCENT.getIndex();
 break;
 default: //
 }
 if (colorIdx > 0) {
 font.setColor(colorIdx);
 }
 }
 newStyle.setFont(font);
 }
 // 4.other：
 // ...
 // 5.背景：// 底色: setFillBackgroundColor/填充: setFillForegroundColor >>
 // #dce9f1=>(241,233,220)=>14477809
 if (setCellStyleForegroundColor(newStyle, color)) {
 newStyle.setFillPattern(SOLID_FOREGROUND);
 newStyle.setFillBackgroundColor(IndexedColors.AUTOMATIC.getIndex());
 }
 return newStyle;
 }
 
 /** * 设置int型color对应的颜色 * * @param cellStyle * @param color * 颜色值使用Windows平台存储顺序：0x00bbggrr，与Java默认的0x00rrggbb有所不同 * @param foreground * @return */
 protected boolean setCellStyleColor(CellStyle cellStyle, int color, boolean foreground) {
 color &= 0x00ffffff;
 if (color == 0) {
 // 无需处理，否则，某些单元将出现黑色背景。
 return false;
 }
 
 byte r = (byte) (color & 0x00ff);
 byte g = (byte) ((color >> 8) & 0x00ff);
 byte b = (byte) ((color >> 16) & 0x00ff);
 if (xlsx) {
 XSSFColor xssfColor = new XSSFColor(new byte[] { r, g, b });
 if (foreground) {
 ((XSSFCellStyle) cellStyle).setFillForegroundColor(xssfColor);
 } else {
 ((XSSFCellStyle) cellStyle).setFillBackgroundColor(xssfColor);
 }
 } else {
 HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();
 HSSFColor hssfColor = palette.findColor(r, g, b);
 if (null == hssfColor) {
 try {
 hssfColor = palette.addColor(r, g, b);
 } catch (RuntimeException ignore) {
 palette.setColorAtIndex(nowColorIndex, r, g, b);
 hssfColor = palette.findColor(r, g, b);
 if (null != hssfColor) {
 --nowColorIndex;
 } else {
 return false;
 }
 }
 }
 if (foreground) {
 cellStyle.setFillForegroundColor(hssfColor.getIndex());
 } else {
 cellStyle.setFillBackgroundColor(hssfColor.getIndex());
 }
 }
 return true;
 }
 
 /** * 设置int型color对应的前景颜色 * * @param cellStyle * @param color * 颜色值使用Windows平台存储顺序：0x00bbggrr，与Java默认的0x00rrggbb有所不同 * @return */
 public boolean setCellStyleForegroundColor(CellStyle cellStyle, int color) {
 return setCellStyleColor(cellStyle, color, true);
 }
 
 /** * 设置int型color对应的背景颜色 * * @param cellStyle * @param color * 颜色值使用Windows平台存储顺序：0x00bbggrr，与Java默认的0x00rrggbb有所不同 * @return */
 public boolean setCellStyleBackgroundColor(CellStyle cellStyle, int color) {
 return setCellStyleColor(cellStyle, color, false);
 }
 
 /** * 设置单元格格式 * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param border * @param align * @param font * @param other * @param color * @return */
 public Cell setCellStyle(int rowIndex, int colIndex, int border, int align, int font, int other, int color) {
 Cell cell = getCell(rowIndex, colIndex);
 CellStyle cellStyle = cell.getCellStyle();
 cellStyle = getCellStyle(cellStyle, border, align, font, other, color);
 cell.setCellStyle(cellStyle);
 return cell;
 }
 
 /** * Invoking {@link HSSFWorkbook#getBytes()} does not return all of the data necessary to re- * construct a complete Excel file. * * use {@link ByteArrayOutputStream} because it is good style to include anyway in case its * later changed to a different kind of stream * * @return the byte array with this {@link #workbook} * @throws IOException * if anything can't be written * @author CaiBo */
 public byte[] getBytes() throws IOException {
 // we're best off getting the size because it takes too much time
 ByteArrayOutputStream bos = new ByteArrayOutputStream();
 try {
 workbook.write(bos);
 } finally {
 bos.close();
 }
 return bos.toByteArray();
 }
 
 /** * 设置某单元格为指定样式 * * * @param rowIndex * 0 based row number * @param colIndex * 0 based column number * @param style * 该单元格的样式，可通过本类中的 {@link #createCellStyle} 方法创建 * @author CaiBo */
 public void setCellStyle(int rowIndex, int colIndex, CellStyle style) {
 getCell(rowIndex, colIndex).setCellStyle(style);
 }
 
 /** * 创建一个自定义样式 * * &nbsp&nbsp设置字体、字号等: 请使用 {@link #createFont} 方法 * * @return 自定义样式 * @author CaiBo */
 public CellStyle createCellStyle() {
 return workbook.createCellStyle();
 }
 
 /** * 创建自定义字体 * * @return */
 public Font createFont() {
 return workbook.createFont();
 }
 
 }

[Excel]: http://blog.csdn.net/longdayu4544/article/details/78290019
[Excell]: /images/20220606/40049b1c4cb44f2ca9e481cc4efb846a.png