excel读取
看了下之前上传的资源,发现excel通用读取的代码下载量还可以,所以趁着有些时间,对excel通用读取的功能进行了一次升级优化,升级后的代码结构上耦合度更低,扩展性更好,同时还增加了些功能,进一步简化了使用。具体设计及代码如下:
-
单元格的读取
// 1.针对各个类型的单元格进行读取 private Object getCellOriginalValue(Cell cell) { switch (cell.getCellType()) { case STRING: // 字符串 return getStringCellValue(cell); case NUMERIC: // 数字 if (DateUtil.isCellDateFormatted(cell)) { return getNumericCellDateValue(cell); } else { return getNumericCellValue(cell); } case BOOLEAN: // 布尔值 return Boolean.toString(cell.getBooleanCellValue()); case FORMULA: // 公式 try { return getNumericCellValue(cell); } catch (Exception e1) { try { return getFormuaCellStringValue(cell); } catch (Exception e2) { throw new RuntimeException("获取公式类型的单元格失败", e2); } } default: return null; } } // 2.对于各单元格的值进行转换 public Object getCellValue(Cell cell) { Object cellValue = getCellOriginalValue(cell); if (getConfig() == null) { return cellValue; } List<DataConvertor> dataConvertorList = getConfig().getDataConvertorList(); for (DataConvertor dataConvertor : dataConvertorList) { cellValue = dataConvertor.convertData(cellValue); } return cellValue; }
提供的值转换器
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kNeuI2Tw-1622466643735)(pic/通用组件/image-20210531204828584.png)]
-
行的读取
// 配置与读取器分离,方便后续扩展。 private List<Object> getRowValueToList(Row row){ List<ExcelCellReader> excelCellReaderList = getConfig().getExcelCellReaderList(); int cellCount = getConfig().getEndCellIndex() - getConfig().getStartCellIndex(); if (cellCount > excelCellReaderList.size()) { for (int j = excelCellReaderList.size(); j <= cellCount; j++) { excelCellReaderList.add(getConfig().getDefaultCellReader()); } } List<Object> rowDataList = new ArrayList<>(); for (int i = getConfig().getStartCellIndex(); i <= getConfig().getEndCellIndex(); i++) { Cell cell = row.getCell(i); rowDataList.add(excelCellReaderList.get(i).getCellValue(cell)); } return rowDataList; }
-
sheet的读取
// 同样做了配置与读取器的分离 public List<T> getSheetValue(Sheet sheet) { int startRowIndex = getConfig().getStartRowIndex(); int endRowIndex = getConfig().getEndRowIndex(); Iterator<Row> rowsIt = sheet.rowIterator(); List<T> sheetDataList = new ArrayList<>(); int currentIndex = -1; while (rowsIt.hasNext()) { Row row = rowsIt.next(); currentIndex++; if (currentIndex < startRowIndex) { continue; } if (endRowIndex > -1 && endRowIndex > startRowIndex && currentIndex > endRowIndex) { break; } Object rowValue = config.getExcelRowReader().getRowValue(row); if (isInvalidRowValue(rowValue)) { continue; } if (isEndRowValue(rowValue)) { break; } sheetDataList.add(convertRowValueToObject(rowValue)); } return sheetDataList; }
-
excel的读取
public List getExcelData() { List excelDataList = new ArrayList(); try { for (Map<String, Object> sheetConfigMap : getExcelReaderConfig().getSheetConfigList()) { Sheet sheet = getSheet(sheetConfigMap); if (sheet == null) { continue; } excelDataList.add(((ExcelSheetReader) sheetConfigMap.get(ExcelReaderConfig.ECXCEL_READER_CONFIG_SHEET_READER_KEY)).getSheetValue(sheet)); } } finally { close(); } return excelDataList; }
-
构造器,为了简化使用而生
// 只展示出冰山一角 private DataConvertor buildDataConvertor(String dataConvertorStr) { if(StringUtils.isBlank(dataConvertorStr)){ return null; } String[] dataConvertorInfoArray = dataConvertorStr.split(KEY_SPLIT); String dataConvertorName = dataConvertorInfoArray[0]; DataConvertor dataConvertor = DataConvertFact.getInstance().getDataConvertor(dataConvertorName); if(dataConvertor == null){ return null; } if(dataConvertorInfoArray.length < 2){ return dataConvertor; } if((dataConvertor instanceof ConvertToString)|| (dataConvertor instanceof ConvertToSqlTimestamp) || (dataConvertor instanceof ConvertToSqlDate) || (dataConvertor instanceof ConvertToSqlTime) || (dataConvertor instanceof ConvertToDate) ){ dataConvertor.setConvertConfig(new FormmaterConfig(dataConvertorInfoArray[1])); }else if(dataConvertor instanceof StringReplaceConvert && dataConvertorInfoArray.length>2){ // 参数:原字符串,新字符串,替换标识(1:替换开头,0:替换结尾,不设置或设置其他的:全部替换) StringReplaceConfig convertConfig = new StringReplaceConfig(); convertConfig.setSrcStr(dataConvertorInfoArray[1]); convertConfig.setNewStr(dataConvertorInfoArray[2]); if(dataConvertorInfoArray.length>3){ String replaceOpStr = dataConvertorInfoArray[2]; if(replaceOpStr.equals("1")){ convertConfig.setReplacePrefix(true); convertConfig.setReplaceSuffix(false); convertConfig.setReplaceAll(false); }else if(replaceOpStr.equals("0")){ convertConfig.setReplacePrefix(true); convertConfig.setReplaceSuffix(true); convertConfig.setReplaceAll(false); } } dataConvertor.setConvertConfig(convertConfig); } return dataConvertor; }
-
使用,最重要的使用来了,针对于普通的类型的excel,返回json集合的,简直不要太简单了
String fielName = "D:\\test.xlsx"; try { DefaultExcelReaderBuilder builder = new DefaultExcelReaderBuilder(); //1.指定读取文件的文件名称 builder.setExcelFileName(fielName); //2.指定需要读取的sheet位置(0表示第一个sheet)、各列对应的返回数据中的key名称, builder.addSheetReader(0,"seq,name,no,age,birthday,workaddr,tel") // 3.特殊需求,日期格式化、字符替换(没有则不需要此步骤) .setCellConvert(0,"birthday", String.class.getName()+ DefaultExcelReaderBuilder.KEY_SPLIT+DateUtils.DEFAULT_DATE_FORMATTER ,"StringReplaceConvert"+DefaultExcelReaderBuilder.KEY_SPLIT+"200"+DefaultExcelReaderBuilder.KEY_SPLIT+"199"+DefaultExcelReaderBuilder.KEY_SPLIT+"1") .setSheetDataStartRowIndexAndEndCellIndex(0,1,6); // 4.指定读取的起始行和结束列. List excelData = builder.build().getExcelData(); // 5.返回 excel中的数据。 System.out.println(new JSONArray(excelData).toString()); } catch (Exception e) { e.printStackTrace(); }
全部源码下载地址,请点击