【解析excel】利用easyexcel解析excel

EasyExcel

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

POM

        <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel-core</artifactId><version>3.2.1</version><exclusions><exclusion><groupId>org.ehcache</groupId><artifactId>ehcache</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId></exclusion></exclusions></dependency>

监听类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Cell;
import com.alibaba.excel.metadata.data.ReadCellData;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;public class MyExcelListener extends AnalysisEventListener<Map<Integer, String>> {private Map<Integer, String> headMap = new HashMap<>();private List<Map<Integer, String>> valList = new ArrayList<>();private Map<String, String> headValAndTypeMap = new HashMap<>();private List<Map<String, String>> contentValAndTypeList = new ArrayList<>();private boolean nameAndTypeFlag;private Integer startColumnNum;private Integer startRowNum;private Integer endColumnNum;private Integer endRowNum;// sheet中最大的列号,默认为0,不可为nullprivate int maxColumnNum;// sheet中行数，默认为1，因为从第二行开始读取private int maxRowNum = 1;@Overridepublic void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {if (integerStringMap == null || integerStringMap.size()==0){return;}// 获取当前行数analysisContext.readRowHolder().getRowIndex() 从1开始(0行进的head方法)Integer currentRowNum = analysisContext.readRowHolder().getRowIndex();if(currentRowNum<startRowNum-1){return;}// 扫描excel全部内容获取此excel最大列数maxColumnNum = Math.max(maxColumnNum,integerStringMap.size());// 最大行数maxRowNum++;// 起始列，Map中小于startColumnNum-1都不需要integerStringMap.entrySet().removeIf(entry -> entry.getKey() < startColumnNum-1);// 移除值为null的数据integerStringMap.entrySet().removeIf(entry -> entry.getValue() == null);// 格式化单元格中的数据formatExcelValByCellType(integerStringMap,analysisContext.readRowHolder().getCellMap());// 本方法从excel第二行开始读,为防止起始不是excel第一行，多读入一行数据if (nameAndTypeFlag && (contentValAndTypeList.size() == 0 || contentValAndTypeList.size() == 1)){// 如果获取名称和类型,只获取一行数据contentValAndTypeList.add(getCellType(integerStringMap,analysisContext.readRowHolder().getCellMap()));} else if (endRowNum == null){// 未设置结束单元格无结束行数，则读取全部valList.add(integerStringMap);} else if (valList.size() < endRowNum-1){valList.add(integerStringMap);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {Set<Map.Entry<Integer, ReadCellData<?>>> entrieSet = headMap.entrySet();for (Map.Entry<Integer, ReadCellData<?>> entry : entrieSet) {String val = entry.getValue().getType()== CellDataTypeEnum.EMPTY?"":entry.getValue().getStringValue();this.headMap.put(entry.getKey(),val);}if (startRowNum==1 && nameAndTypeFlag && headValAndTypeMap.size() == 0){// 如果获取名称和类型,只获取一行数据headValAndTypeMap = getCellType(this.headMap,context.readRowHolder().getCellMap());}}private void formatExcelValByCellType(Map<Integer, String> integerStringMap, Map<Integer, Cell> cellMap){for (Integer key : integerStringMap.keySet()) {ReadCellData cell = (ReadCellData) cellMap.get(key);String newVal = MyExcelUtils.getOtherDateFormat(cell,cell.getDataFormatData().getFormat());if (newVal!=null && !"".equals(newVal)){integerStringMap.put(key,newVal);}}}/*** eg: 0:张三  0_type:String* @param integerStringMap* @param cellMap* @return*/private Map<String, String> getCellType(Map<Integer, String> integerStringMap, Map<Integer, Cell> cellMap){Map<String, String> nameAndTypeMap = new HashMap<>();// key取值是 0 1 2 3....for (Integer key : integerStringMap.keySet()) {nameAndTypeMap.put(String.valueOf(key),integerStringMap.get(key));ReadCellData cell = (ReadCellData) cellMap.get(key);String cellType = MyExcelUtils.getCellType(cell,integerStringMap.get(key));if (cellType!=null && !"".equals(cellType)){nameAndTypeMap.put(key+"_type",cellType);}}return nameAndTypeMap;}public Map<Integer, String> getHeadMap() {return headMap;}public void setHeadMap(Map<Integer, String> headMap) {this.headMap = headMap;}public List<Map<Integer, String>> getValList() {return valList;}public void setValList(List<Map<Integer, String>> valList) {this.valList = valList;}public Integer getStartColumnNum() {return startColumnNum;}public void setStartColumnNum(Integer startColumnNum) {this.startColumnNum = startColumnNum;}public Integer getStartRowNum() {return startRowNum;}public void setStartRowNum(Integer startRowNum) {this.startRowNum = startRowNum;}public Integer getEndColumnNum() {return endColumnNum;}public void setEndColumnNum(Integer endColumnNum) {this.endColumnNum = endColumnNum;}public Integer getEndRowNum() {return endRowNum;}public void setEndRowNum(Integer endRowNum) {this.endRowNum = endRowNum;}public int getMaxColumnNum() {return maxColumnNum;}public void setMaxColumnNum(int maxColumnNum) {this.maxColumnNum = maxColumnNum;}public int getMaxRowNum() {return maxRowNum;}public void setMaxRowNum(int maxRowNum) {this.maxRowNum = maxRowNum;}public boolean isNameAndTypeFlag() {return nameAndTypeFlag;}public void setNameAndTypeFlag(boolean nameAndTypeFlag) {this.nameAndTypeFlag = nameAndTypeFlag;}public Map<String, String> getHeadValAndTypeMap() {return headValAndTypeMap;}public void setHeadValAndTypeMap(Map<String, String> headValAndTypeMap) {this.headValAndTypeMap = headValAndTypeMap;}public List<Map<String, String>> getContentValAndTypeList() {return contentValAndTypeList;}public void setContentValAndTypeList(List<Map<String, String>> contentValAndTypeList) {this.contentValAndTypeList = contentValAndTypeList;}
}

工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.DateUtils;
import org.apache.commons.lang.time.DateFormatUtils;import java.io.DataInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;import static com.nature.ysl.zl.excel.MyExcelUtils.SPECTYPESIGN;public class MyExcelUtils {public static final String SPECTYPESIGN = "#@#";// 字母private static final String MATCH_LETTERS = "[a-zA-Z]+";// 匹配字母开头private static final String LETTER_START_REGEX = "^[a-zA-Z]*$";// 提取单元格的行数private static final String ROW_REGEX = "[\\d]+";// 匹配中文private static final String CHINESETYPE_REGEX = ".*[\\u4e00-\\u9fa5].*";// 单元格数值类型private static final String NUMBERTYPE_REGEX = "[0|.]+";// 匹配除了,.数字之前外的所有符号,0_ 代表整数private static final String EXCLUDE_SPECIFIC_REGEX = ".*[^,.#\\d].*";private static final String CELL_REGEX = "[A-Z]+[1-9][\\d]*";private static final Pattern date_ptrn1 = Pattern.compile("^\\[\\$\\-.*?\\]");private static final Pattern date_ptrn2 = Pattern.compile("^\\[[a-zA-Z]+\\]");private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+|[mM]+|[sS]+)\\]");private static final Pattern date_ptrn5 = Pattern.compile("^\\[DBNum(1|2|3)\\]");/*** 获取excel文件所有的sheet页名称** @param ins* @return*/public static List<String> getSheetList(InputStream ins) {List<String> resList = new ArrayList<>();ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(ins);ExcelReader excelReader = excelReaderBuilder.build();List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();sheetList.forEach(e -> resList.add(e.getSheetName()));return resList;}/*** 获取sheet种的内容，前rowNum行，null则查询全部* @param ins* @param sheetName* @param rowNum* @return*/public static List<List<String>> getSheetData(InputStream ins, String sheetName, Integer rowNum) {if (rowNum != null && rowNum == 0){return null;}InputStream is = null;is = new DataInputStream(ins);MyExcelListener listen = new MyExcelListener();// 设置读取的行数listen.setEndRowNum(rowNum);ExcelReaderBuilder read = EasyExcel.read(is, listen);read.sheet(sheetName).doRead();// 读取sheet中最大的列数int maxColumnNum = listen.getMaxColumnNum();// 第一行数据(也就是表头数据)，下标从0开始Map<Integer, String> headMap = listen.getHeadMap();// 其余数据，下标从0开始List<Map<Integer, String>> valList = listen.getValList();// 为还原excel原始样式，以最大列数为约束，遍历headMap、valList，获取不到的数据以空填充// 如果rowNum不为空，开始填充数据rowNum = rowNum == null?listen.getMaxRowNum():rowNum;List<List<String>> resList = new ArrayList();for (int i = 0; i < rowNum; i++) {List<String> list = new ArrayList<>();for (int j = 0; j < maxColumnNum; j++) {if (i == 0){// 如果不存在默认返回空list.add(headMap.getOrDefault(j,""));}else {list.add(valList.get(i-1).getOrDefault(j,""));}}resList.add(list);}return resList;}/*** 部分格式数据处理* @param cell* @param formatVal* @return*/public static String getOtherDateFormat(ReadCellData cell, String formatVal){String newFormatStr = MyExcelTypeEnum.getFormatType(formatVal);if (cell.getDataFormatData().getIndex() == 22) {// excel显示格式为：2012/1/20 23:00return DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), "yyyy/M/d H:mm");} else if (cell.getDataFormatData().getIndex() == 30) {return DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), "M/d/yy");}if (StringUtils.isEmpty(newFormatStr)){return null;}String dateStr = DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()),newFormatStr);if (StringUtils.contains(dateStr,SPECTYPESIGN)){// 二〇〇五年一月十五日return convertNumberToChineseDate(dateStr,SPECTYPESIGN);} else if (StringUtils.contains(newFormatStr," aa")) {// 2011/1/3 6:00 AMreturn DateFormatUtils.format(doubleToDate(cell.getNumberValue().doubleValue()), newFormatStr, Locale.ENGLISH);} else if (StringUtils.contains(newFormatStr,"MMM")) {// J 、J-23return getEnglishDate(cell.getNumberValue().doubleValue(),newFormatStr);}return dateStr;}/*** 将yyyy年-MM月-dd日 格式日期转换成中文格式* 例：2000-1-1 --> 二〇〇〇年一月一日*/public static String convertNumberToChineseDate(String date,String splitStr) {if (date == null || "".equals(date)) {return date;}try {String[] dates = date.split(splitStr);StringBuilder chineseDateSbu = new StringBuilder();for (int i = 0; i < dates.length; i++) {chineseDateSbu.append(formatDigit(dates[i]));}return chineseDateSbu.toString();} catch (Exception e) {return null;}}/*** 截取后的年月日转换为中文* 例1：2000 --> 二〇〇〇* 例1：10 --> 十*/public static String formatDigit(String sign) {if (sign == null || "".equals(sign)) {return null;}char[] signChars = sign.toCharArray();StringBuilder chinese = new StringBuilder();if (signChars.length > 4 || signChars.length < 3) {for (char c : signChars) {chinese.append(MyChineseNumberEnum.getValue(String.valueOf(c)));}} else {if (sign.startsWith("0")){for (char c : signChars) {chinese.append(MyChineseNumberEnum.getValue(String.valueOf(c)));}}else {if (sign.equals(MyChineseNumberEnum.getValue(sign))){String subStr = sign.substring(0,sign.length()-1);String unitStr = sign.substring(sign.length()-1);chinese.append(MyChineseNumberEnum.getValue(subStr)+unitStr);}else {chinese.append(MyChineseNumberEnum.getValue(sign));}}}return chinese.toString();}public static String getCellType(ReadCellData cell, String cellVal){if (isStringTypeFromCell(cell,cellVal)){return "STRING";}switch (cell.getType()) {case NUMBER:String formatVal = cell.getDataFormatData().getFormat();// 将excel的类型处理成java可以识别的类型String newFormat = MyExcelTypeEnum.getFormatType(formatVal);if (newFormat == null || "".equals(newFormat)){newFormat = formatJavaType(formatVal);}if (DateUtils.isADateFormat(cell.getDataFormatData().getIndex(),formatVal)) {// 将数据格式化String originVal = getOtherDateFormat(cell,formatVal);if (!StringUtils.isEmpty(formatVal)&& (isChineseType(newFormat) || isChineseType(originVal) )) {return "STRING";}// 暂时简单处理，如果是包含h:mm:ss 默认按照时间格式处理，剩下默认按照时间戳处理if (StringUtils.contains(formatVal, "mm:ss")) {return "TIMESTAMP";} else {return "DATE";}} else if (StringUtils.equalsIgnoreCase("General",newFormat)) {return "STRING";} else {// 自定义的事件类型String otherDateType = getOtherDateType(cell.getDataFormatData().getIndex());if (!StringUtils.isEmpty(otherDateType)){return otherDateType;}if (!StringUtils.isEmpty(newFormat) && newFormat.matches(NUMBERTYPE_REGEX)) {if (StringUtils.equals("0", newFormat) || !newFormat.contains(".")) {return "NUMBER";} else {return "DOUBLE";}} else if (!StringUtils.isEmpty(newFormat) && newFormat.matches(EXCLUDE_SPECIFIC_REGEX)) {// 解决货币之类的数据类型判断如 ￥14return "STRING";} else {return "DOUBLE";}}case STRING:// 首行如果是表头汉字，全部都是stringString format = cell.getDataFormatData().getFormat();if (StringUtils.equalsIgnoreCase("General", format)) {return "STRING";} else if (!StringUtils.isEmpty(format) && format.matches(NUMBERTYPE_REGEX)) {if (StringUtils.equals("0", format)) {return "NUMBER";} else {return "DOUBLE";}} else if (isChineseType(MyExcelTypeEnum.getFormatType((format)))) {return "STRING";} else if (StringUtils.containsIgnoreCase(format, "mm:ss")) {return "TIMESTAMP";} else if (StringUtils.containsIgnoreCase(format, "yyyy")|| StringUtils.containsIgnoreCase(format, "mm")|| StringUtils.containsIgnoreCase(format, "dd")) {return "DATE";} else {return "STRING";}case BOOLEAN:return "BOOLEAN";default:return "STRING";}}/*** 用于解析excel表头及类型* @param ins* @param sheetName* @param startCell 起始单元格 默认A1* @param endCell 结束单元格* @return*/public static List<Map<String,String>> getSheetColumnNameAndType(InputStream ins, String sheetName, String startCell, String endCell){List<Map<String,String>> resList = new ArrayList<>();// 提起起始行数列数，结束行数列数Integer startColumnNum = excelCellToColumnNum(startCell);Integer startRowNum = excelCellToRowNum(startCell);Integer endColumnNum = excelCellToColumnNum(endCell);Integer endRowNum = excelCellToColumnNum(endCell);InputStream is = null;is = new DataInputStream(ins);MyExcelListener listen = new MyExcelListener();// 获取字段名称和类型listen.setNameAndTypeFlag(true);// 设置读取的列片区listen.setStartColumnNum(startColumnNum);listen.setEndColumnNum(endColumnNum);// 设置读取的行片区listen.setStartRowNum(startRowNum);listen.setEndRowNum(endRowNum);ExcelReaderBuilder read = EasyExcel.read(is, listen);read.sheet(sheetName).doRead();// 如果endColumnNum==null则无结束单元格，maxColumn就是excel最大的列，反之则取endColumnNumInteger maxColumn = endColumnNum != null ? endColumnNum : listen.getMaxColumnNum();// 组装首行内容 （受到其实单元格及结束单元格约束） headMap从0开始// 第一行数据(也就是表头数据)，下标从0开始，startRowNum默认1（A1）Map<String, String> headMap = listen.getHeadValAndTypeMap();// 第二行数据，下标从0开始。包含内容及类型List<Map<String, String>>  valList = listen.getContentValAndTypeList();Map<String, String> resHeadMap = null;String type = "STRING";for (int i = startColumnNum-1; i < maxColumn; i++) {resHeadMap = new HashMap<>();if (startRowNum==1){// 起始行数是1 head就会有数据，且是需要的数据if (valList.size()>0 && !StringUtils.isEmpty(valList.get(0).get(i+"_type"))){type = valList.get(0).get(i+"_type");} else if (!StringUtils.isEmpty(headMap.get(i+"_type"))) {type = headMap.get(i+"_type");}resHeadMap.put("columnName",headMap.getOrDefault(String.valueOf(i),""));resHeadMap.put("columnType",type);resList.add(resHeadMap);}else if(startRowNum>1 && valList.size()>0){// 起始行数是1 head中的数据并不是需要的数据，则从valList取 0 和 1 分别做 头和内容if (valList.size()>1 && valList.get(1)!=null && !StringUtils.isEmpty(valList.get(1).get(i+"_type"))){type = valList.get(1).get(i+"_type");} else if (!StringUtils.isEmpty(valList.get(0).get(i+"_type"))) {type = valList.get(0).get(i+"_type");}resHeadMap.put("columnName",valList.get(0).getOrDefault(String.valueOf(i),""));resHeadMap.put("columnType",type);resList.add(resHeadMap);}}return resList;}public static String checkExcelCellString(String startCell, String endCell) {// 起始单元格校验if (!StringUtils.isEmpty(startCell)) {if (!checkExcelCellSpecs(startCell)) {// 起始单元格不符合规范return "起始单元格不符合规范";}} else {// 起始单元格不得为空return "起始单元格不得为空";}// 结束单元格但如果不为空，则需要合法校验if (!StringUtils.isEmpty(endCell)) {if (!checkExcelCellSpecs(endCell)) {// 结束单元格不符合规范return "结束单元格不符合规范";}}// 单元格全部合法后，进行逻辑约束校验，起始单元格后的数字要小于等于结束单元格后缀// 列数校验 结束列数 >= 起始列数Integer startColumnNum = excelCellToColumnNum(startCell);Integer endColumnNum = excelCellToColumnNum(endCell);// 行数校验 结束行数 >= 起始行数Integer startRowNum = excelCellToRowNum(startCell);Integer endRowNum = excelCellToRowNum(endCell);if (endColumnNum != null) {if (startColumnNum > endColumnNum){return "起始单元格列必须小于等于结束起始单元格列";}else if (startRowNum > endRowNum){return "起始单元格行必须小于等于结束起始单元格行";}}return "";}/*** 检验单元格合法性* @param excelCell* @return*/private static boolean checkExcelCellSpecs(String excelCell) {return !StringUtils.isEmpty(excelCell) && excelCell.matches(CELL_REGEX);}/** 提取单元格字母对应列,如果colStr==null，则返回null* A1 -> 1、B1 -> 2、C1 -> 3*/public static Integer excelCellToColumnNum(String colStr) {Integer result = null;if (!StringUtils.isEmpty(colStr)) {result = 0;int length = colStr.length();int j = 0;int num = 0;for (int i = 0; i < length; i++) {char ch = colStr.charAt(length - i - 1);if (String.valueOf(ch).matches(LETTER_START_REGEX)) {num = ch - 'A' + 1;num *= Math.pow(26, j);j++;result += num;}}}return result;}/*** 提取单元格对应行返回值：Integer* 提取单元格字母对应行,如果colStr==null，则返回null* A1 -> 1、B2 -> 2、C3 -> 3*/public static Integer excelCellToRowNum(String colStr) {String numStr = excelCellToRowString(colStr);if (StringUtils.isEmpty(numStr)) {return null;}return Integer.parseInt(numStr);}/*** 提取单元格对应行返回值：string* @param colStr* @return*/public static String excelCellToRowString(String colStr) {String res = null;if (!StringUtils.isEmpty(colStr)) {Matcher matcher = Pattern.compile(ROW_REGEX).matcher(colStr);if (matcher.find()) {res = matcher.group();}}return res;}/*** 英文缩写的日期处理* @param numericCellValue* @param formatVal* @return*/private static String getEnglishDate(double numericCellValue, String formatVal){String dateStr = DateFormatUtils.format(doubleToDate(numericCellValue), formatVal, Locale.ENGLISH);if (StringUtils.equals(formatVal,"MMMMM")){// excel显示格式为：Freturn StringUtils.substring(dateStr,0,1);} else if (StringUtils.equals(formatVal,"MMMMM-yy")) {// excel显示格式为：F-23String letters = null;Matcher matcher = Pattern.compile(MATCH_LETTERS).matcher(dateStr);if (matcher.find()) {letters = matcher.group();}if (!StringUtils.isEmpty(letters) && !StringUtils.isEmpty(dateStr)){return StringUtils.replace(dateStr,letters,StringUtils.substring(dateStr,0,1));}return dateStr;}return dateStr;}private static boolean isStringTypeFromCell(ReadCellData cell, String cellValue){// 如果数据中含有中文，则直接返回string格式if (!StringUtils.isEmpty(cellValue) && cellValue.matches(CHINESETYPE_REGEX)){return true;}String newFormatStr = MyExcelTypeEnum.getFormatKey(cell.getDataFormatData().getFormat());if (StringUtils.containsIgnoreCase(newFormatStr,"EN") || StringUtils.containsIgnoreCase(cellValue,"AM")|| StringUtils.containsIgnoreCase(cellValue,"PM")){return true;}short shortNum = cell.getDataFormatData().getIndex();switch (shortNum){case 46:// 1184426:00:00return true;}return false;}private static boolean isChineseType(String param){if (!StringUtils.isEmpty(param)){if(param.matches(CHINESETYPE_REGEX)|| param.contains("E")|| param.contains("MMMM")){return true;}}return false;}private static String getOtherDateType(short shortNum){switch (shortNum) {case 14:case 30:return "DATE";case 31:case 57:return "TIMESTAMP";}return null;}private static String formatJavaType(String formatVal){String fs = formatVal;int length = formatVal.length();StringBuilder sb = new StringBuilder(length);int separatorIndex;for(separatorIndex = 0; separatorIndex < length; ++separatorIndex) {char c = fs.charAt(separatorIndex);if (separatorIndex < length - 1) {char nc = fs.charAt(separatorIndex + 1);if (c == '\\') {switch (nc) {case ' ':case ',':case '-':case '.':case '\\':continue;}} else if (c == ';' && nc == '@') {++separatorIndex;continue;}}sb.append(c);}fs = sb.toString();// excel设置单元格格式 使用数值fs = StringUtils.replace(fs,"0_ ","0");fs = StringUtils.replace(fs,"0_)","0");if (date_ptrn4.matcher(fs).matches()) {return fs;} else {fs = date_ptrn5.matcher(fs).replaceAll("");fs = date_ptrn1.matcher(fs).replaceAll("");fs = date_ptrn2.matcher(fs).replaceAll("");separatorIndex = fs.indexOf(59);if (0 < separatorIndex && separatorIndex < fs.length() - 1) {fs = fs.substring(0, separatorIndex);}return fs;}}private static Date doubleToDate(Double date){Calendar base = Calendar.getInstance();base.set(1899, 11, 30, 0, 0, 0);base.add(Calendar.DATE, date.intValue());base.add(Calendar.MILLISECOND,(int)((date % 1) * 24 * 60 * 60 * 1000));return base.getTime();}}
enum MyChineseNumberEnum {/*** 0*/ZERO("0", "〇"),ZERO_DOUBLE("00", "〇〇"),/*** 1*/ONE("1", "一"),/*** 2*/TWO("2", "二"),/*** 3*/THREE("3", "三"),/*** 4*/FOUR("4", "四"),/*** 5*/FIVE("5", "五"),/*** 6*/SIX("6", "六"),/*** 7*/SEVEN("7", "七"),/*** 8*/EIGHT("8", "八"),/*** 9*/NINE("9", "九"),/*** 10*/TEN("10", "十"),/****/ELEVEN("11", "十一"),/****/TWELVE("12", "十二"),/****/THIRTEEN("13", "十三"),/****/FOURTEEN("14", "十四"),/****/FIFTEEN("15", "十五"),/****/SIXTEEN("16", "十六"),/****/SEVENTEEN("17", "十七"),/****/EIGHTEEN("18", "十八"),/****/NINETEEN("19", "十九"),/****/TWENTY("20", "二十"),/****/TWENTY_ONE("21", "二十一"),/****/TWENTY_TWO("22", "二十二"),/****/TWENTY_THREE("23", "二十三"),/****/TWENTY_FOUR("24", "二十四"),/****/TWENTY_FIVE("25", "二十五"),/****/TWENTY_SIX("26", "二十六"),/****/TWENTY_SEVEN("27", "二十七"),/****/TWENTY_EIGHT("28", "二十八"),/****/TWENTY_NINE("29", "二十九"),/****/THIRTY("30", "三十"),/****/THIRTY_ONE("31", "三十一"),;private final String numberCode;private final String chineseCode;MyChineseNumberEnum(String numberCode, String chineseCode) {this.numberCode = numberCode;this.chineseCode = chineseCode;}public String getNumberCode() {return numberCode;}public String getChineseCode() {return chineseCode;}static final Map<String, String> SEND_METHOD_MAP = new HashMap<>();static {for (MyChineseNumberEnum code : MyChineseNumberEnum.values()) {SEND_METHOD_MAP.put(code.getNumberCode(), code.getChineseCode());}}public static String getValue(String key) {return SEND_METHOD_MAP.getOrDefault(key,key);}
}
enum MyExcelTypeEnum {// &$# 为二〇〇五年一月十五日格式数据的再次格式化标识DATE_CAP_1("[DBNum1][$-804]yyyy\"年\"m\"月\"d\"日\"","yyyy年"+SPECTYPESIGN+"M月"+SPECTYPESIGN+"dd日"),DATE_CAP_2("[DBNum1][$-804]yyyy\"年\"m\"月\"","yyyy年"+SPECTYPESIGN+"M月"),DATE_CAP_3("[DBNum1][$-804]m\"月\"d\"日\"","M月"+SPECTYPESIGN+"dd日"),DATE_CAP_4("[DBNum1]h\"时\"mm\"分\"","H时"+SPECTYPESIGN+"mm分"),DATE_CAP_5("[DBNum1]上午/下午h\"时\"mm\"分\"","aa"+SPECTYPESIGN+"h时"+SPECTYPESIGN+"mm分"),DATE_EN_SHORTHAND_1("d-mmm","d-MMM"),DATE_EN_SHORTHAND_2("d-mmm-yy","d-MMM-yy"),DATE_EN_SHORTHAND_3("dd\\-mmm\\-yy","d-MMM-yy"),DATE_EN_SHORTHAND_4("mmm-yy","MMM-yy"),DATE_EN_SHORTHAND_5("mmmm\\-yy","MMMM-yy"),DATE_EN_SHORTHAND_6("mmmm-yy","MMMM-yy"),DATE_EN_SHORTHAND_7("mmmmm","MMMMM"),DATE_EN_SHORTHAND_8("mmmmm\\-yy","MMMMM-yy"),DATE_TIME_1("h\"时\"mm\"分\"","H时mm分"),DATE_TIME_2("h\"时\"mm\"分\"ss\"秒\"","H时mm分ss秒"),DATE_TIME_3("上午/下午h\"时\"mm\"分\"","aah时mm分"),DATE_TIME_4("上午/下午h\"时\"mm\"分\"ss\"秒\"","aah时mm分ss秒"),DATE_EN_1("yyyy/m/d\\ h:mm\\ AM/PM","yyyy/M/d h:mm aa"),DATE_EN_2("h:mm\\ AM/PM","h:mm aa"),DATE_EN_3("h:mm:ss\\ AM/PM","h:mm:ss aa");String excelType;String formatType;MyExcelTypeEnum(String excelType, String formatType) {this.excelType = excelType;this.formatType = formatType;}public static String getFormatType(String excelType) {for (MyExcelTypeEnum excelTypeEnum : MyExcelTypeEnum.values()) {if (excelTypeEnum.excelType.equals(excelType)) {return excelTypeEnum.formatType;}}return null;}public static String getFormatKey(String excelType) {for (MyExcelTypeEnum excelTypeEnum : MyExcelTypeEnum.values()) {if (excelTypeEnum.excelType.equals(excelType)) {return excelTypeEnum.name();}}return excelType;}
}class StringUtils{public static boolean isEmpty(String str){return str==null || "".equals(str);};public static String replace(String str, String oldStr){if (isEmpty(str)) return str;return str.replace(oldStr,"");};public static String replace(String str, String oldStr, String newStr){if (isEmpty(str)) return str;return str.replace(oldStr,newStr);};public static boolean containsIgnoreCase(String str, String containsStr){if (isEmpty(str)) return false;return str.toLowerCase().contains(containsStr.toLowerCase());};public static boolean equals(String str, String anObject){if (isEmpty(str)) return false;return str.equals(anObject);};public static boolean equalsIgnoreCase(String str, String anObject){if (isEmpty(str)) return false;return str.equalsIgnoreCase(anObject);};public static boolean contains(String str, String anObject){if (isEmpty(str)) return false;return str.contains(anObject);};public static String substring(String str, int beginIndex, int endIndex){if (isEmpty(str)) return str;return str.substring(beginIndex, endIndex);};
}

测试类

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import java.util.Map;public class Mytest {public static void main(String[] args) throws Exception {String filep = "C:\\test.xlsx";String sheet = "Sheet1";String startCell = "A1";String end = null;//"C3";File file = new File(filep);InputStream ins2 = new FileInputStream(file);String checkMsg = MyExcelUtils.checkExcelCellString(startCell,end);System.out.println(checkMsg);List<Map<String,String>> ll =MyExcelUtils.getSheetColumnNameAndType(ins2,sheet,startCell,end);ll.forEach(e-> System.out.println("columnName---"+e.get("columnName")+"---"+e.get("columnType")));}
}

部分测试结果

备注

excel类型比较多，这里适配的也只是一部分，逐步完善

其他

Chat2DB：https://easyexcel.opensource.alibaba.com/docs/current/
官方网站：https://easyexcel.opensource.alibaba.com/
github地址：https://github.com/alibaba/easyexcel
gitee地址：https://gitee.com/easyexcel/easyexcel