如果使用的maven项目，导入excel文件内容的时候需要引入以下依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
2. 读取excel文件代码
public static voidmain(String[] args)throwsException {File file =newFile("D:\\work2\\import.xlsx");FileInputStream fis =newFileInputStream(file);//根据文件名来创建Excel工作薄Workbook work =getWorkbook(in, fileName);if(null== work) {throw newException("创建Excel工作薄为空！");}Sheet sheet =null;Row row =null;Cell cell =null;//返回数据List<Map<String,Object>> resultList=newArrayList<Map<String,Object>>();List<APPRAISE_DATA> appraiseList=newArrayList<APPRAISE_DATA>();//循环多个工作表for(inti = 0; i < work.getNumberOfSheets(); i++) {Map<String,Object> result=newHashMap<String,Object>();sheet = work.getSheetAt(i);if(sheet ==null)continue;//获取有合并单元格的区域List<CellRangeAddress> combineCellList=getCombineCellList(sheet);APPRAISE_DATA appraise_data=newAPPRAISE_DATA();intfirstC=0;intlastC=0;//由于了解只有一行合并单元格，所以得出合并单元格的列数从n1.....n+for(CellRangeAddress ca:combineCellList){firstC=(Integer) ca.getFirstColumn();lastC=(Integer) ca.getLastColumn();}for(intj = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {// Map<String,Object> isCombined=isCombineCell(combineCellList,row.getCell(0),sheet);row = sheet.getRow(j);intindex=1;intindex2=1;APPRAISE_DATA appraise_data_n=newAPPRAISE_DATA();//判断如果是Excel的前两行则走以下逻辑,否则走elseif(j<=sheet.getFirstRowNum()+1){for(inty = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {cell = row.getCell(y);//判断是前两行中的第一行或者第二行if(j==row.getFirstCellNum()){if(y==firstC){Object v =getCellValue(cell);appraise_data.setFund_name(v.toString());}else if(y==lastC+index){Object v =getCellValue(cell);//appraise_data.setCompetitor_name_1(v.toString());for(Field declaredField : APPRAISE_DATA.class.getDeclaredFields()) {String competitor_name="competitor_name_"+index;if(declaredField.getName().equals(competitor_name)) {declaredField.setAccessible(true);declaredField.set(appraise_data,v);}}index++;}}else if(j==row.getFirstCellNum()+1){Object v =getCellValue(cell);if(y==firstC){appraise_data.setTuoguan(v.toString());}else if(y>firstC && y<lastC){//appraise_data.setCompetitor_name_1(v.toString());for(Field declaredField : APPRAISE_DATA.class.getDeclaredFields()) {String channel_name="channel_name_"+index;if(declaredField.getName().equals(channel_name)) {declaredField.setAccessible(true);declaredField.set(appraise_data,v);}}index++;}}}}else{BeanUtils.copyProperties(appraise_data,appraise_data_n);for(inty = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {cell = row.getCell(y);Object v =getCellValue(cell);if( y == row.getFirstCellNum()){appraise_data_n.setChannel(v.toString());}else if( y == row.getFirstCellNum()+1){appraise_data_n.setChannel_manager(v.toString());}else if(y==firstC){appraise_data_n.setTuoguan_turnover(v.toString());}else if(y>firstC && y<lastC){for(Field declaredField : APPRAISE_DATA.class.getDeclaredFields()) {String channel_turnover="channel_turnover_"+index;if(declaredField.getName().equals(channel_turnover)) {declaredField.setAccessible(true);declaredField.set(appraise_data_n,v);}}index++;}else if(y==lastC){appraise_data_n.setTotal_turnover(v.toString());}else if(y==lastC+index2){for(Field declaredField : APPRAISE_DATA.class.getDeclaredFields()) {String competitor_turnover="competitor_turnover_"+index2;if(declaredField.getName().equals(competitor_turnover)) {declaredField.setAccessible(true);declaredField.set(appraise_data_n,v);}}index2++;}}}if(j>sheet.getFirstRowNum()+1) {appraiseList.add(appraise_data_n);}}}}
*@return*/public staticObject getCellValue(Cell cell) {Object value =null;DecimalFormat df =newDecimalFormat("0");//格式化number String字符SimpleDateFormat sdf =newSimpleDateFormat("yyy-MM-dd");//日期格式化DecimalFormat df2 =newDecimalFormat("0");//格式化数字if(cell!=null){switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:value = cell.getRichStringCellValue().getString();break;caseCell.CELL_TYPE_NUMERIC:if("General".equals(cell.getCellStyle().getDataFormatString())) {value = df.format(cell.getNumericCellValue());}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {value = sdf.format(cell.getDateCellValue());}else{value = df2.format(cell.getNumericCellValue());}break;caseCell.CELL_TYPE_BOOLEAN:value = cell.getBooleanCellValue();break;caseCell.CELL_TYPE_BLANK:value ="";break;caseCell.CELL_TYPE_ERROR:value="";break;default:break;}}else{value="";}returnvalue;}
//获取合并单元格集合public staticList<CellRangeAddress> getCombineCellList(Sheet sheet){List<CellRangeAddress> list =newArrayList<CellRangeAddress>();//获得一个 sheet 中合并单元格的数量intsheetmergerCount = sheet.getNumMergedRegions();//遍历所有的合并单元格for(inti = 0; i<sheetmergerCount;i++){//获得合并单元格保存进list中CellRangeAddress ca = sheet.getMergedRegion(i);list.add(ca);}returnlist;}
3.实体类
private String appraise_data_id;private String appraise_info_id;private String company;private String channel;private String channel_manager;private String fund_name;private String tuoguan;private String tuoguan_turnover;private String channel_name_1;private String channel_turnover_1;private String channel_name_2;private String channel_turnover_2;private String channel_name_3;private String channel_turnover_3;private String channel_name_4;private String channel_turnover_4;private String channel_name_5;private String channel_turnover_5;private String channel_name_6;private String channel_turnover_6;private String channel_name_7;private String channel_turnover_7;private String channel_name_8;private String channel_turnover_8;private String channel_name_9;private String channel_turnover_9;private String channel_name_10;private String channel_turnover_10;private String total_turnover;private String competitor_name_1;private String competitor_turnover_1;private String competitor_name_2;private String competitor_turnover_2;private String competitor_name_3;private String competitor_turnover_3;private String competitor_name_4;private String competitor_turnover_4;private String competitor_name_5;private String competitor_turnover_5;private String competitor_name_6;private String competitor_turnover_6;private String competitor_name_7;private String competitor_turnover_7;private String competitor_name_8;private String competitor_turnover_8;private String competitor_name_9;private String competitor_turnover_9;private String competitor_name_10;private String competitor_turnover_10;private String need_split;
4.表格
