- 如果使用的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 void main(String[] args) throws Exception { File file = new File("D:\\work2\\import.xlsx"); FileInputStream fis = new FileInputStream(file); // 根据文件名来创建Excel工作薄 Workbook work = getWorkbook(in, fileName); if (null == work) { throw new Exception("创建Excel工作薄为空!"); } Sheet sheet = null; Row row = null; Cell cell = null; // 返回数据 List<Map<String,Object>> resultList=new ArrayList<Map<String,Object>>(); List<APPRAISE_DATA> appraiseList=new ArrayList<APPRAISE_DATA>(); //循环多个工作表 for (int i = 0; i < work.getNumberOfSheets(); i++) { Map<String,Object> result=new HashMap<String,Object>(); sheet = work.getSheetAt(i); if (sheet == null) continue; //获取有合并单元格的区域 List<CellRangeAddress> combineCellList=getCombineCellList(sheet); APPRAISE_DATA appraise_data=new APPRAISE_DATA(); int firstC=0; int lastC=0; //由于了解只有一行合并单元格,所以得出合并单元格的列数从n1.....n+ for(CellRangeAddress ca:combineCellList){ firstC=(Integer) ca.getFirstColumn(); lastC=(Integer) ca.getLastColumn(); } for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) { // Map<String,Object> isCombined=isCombineCell(combineCellList,row.getCell(0),sheet); row = sheet.getRow(j); int index=1; int index2=1; APPRAISE_DATA appraise_data_n=new APPRAISE_DATA(); //判断如果是Excel的前两行则走以下逻辑,否则走else if(j<=sheet.getFirstRowNum()+1){ for (int y = 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 (int y = 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 static Object getCellValue(Cell cell) { Object value = null; DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化 DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字 if(cell!=null){ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.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; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; case Cell.CELL_TYPE_ERROR: value=""; break; default: break; } }else{ value=""; } return value; }
//获取合并单元格集合 public static List<CellRangeAddress> getCombineCellList(Sheet sheet) { List<CellRangeAddress> list = new ArrayList<CellRangeAddress>(); //获得一个 sheet 中合并单元格的数量 int sheetmergerCount = sheet.getNumMergedRegions(); //遍历所有的合并单元格 for(int i = 0; i<sheetmergerCount;i++) { //获得合并单元格保存进list中 CellRangeAddress ca = sheet.getMergedRegion(i); list.add(ca); } return list; }
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.表格
fg qdjl name zjp(动态增加) jp(动态增加) jp(动态增加).... tgh fx(动态增加列).... 合计销量 累计销量(万) 累计销量(万) 累计销量(万).... 北京 XX、XX、XX 10 20 30 1 2 3....