//分析文件,结果为[[第一行的数据],[第二行的数据],.....] public static List<List<String>> analysisSheet(String filePath, intcurrentSheet) { Workbook wb = null; Sheet sheet = null; Row row = null; List<List<String>> list = null; String cellData = null; wb =readExcel(filePath); if (wb != null) { //用来存放表中数据 list = new ArrayList<List<String>>(); //获取第一个sheet sheet =wb.getSheetAt(currentSheet); //获取最大行数 int rownum =sheet.getPhysicalNumberOfRows(); //获取第一行 row = sheet.getRow(0); //获取最大列数 int colnum = 0; /*获取最大列数 */ for(int i = 1; i < rownum; i++){ row =sheet.getRow(i); if(row.getPhysicalNumberOfCells()>colnum){ colnum =row.getPhysicalNumberOfCells(); } } for (int i = 1; i < rownum; i++) { Map<String, String> map = new LinkedHashMap<String, String>(); row =sheet.getRow(i); if (row != null) { List<String> rowData = new ArrayList<>(); for (int j = 0; j < colnum; j++) { cellData =(String) getCellFormatValue(row.getCell(j)); rowData.add(cellData); } list.add(rowData); } else{ break; } } } returnlist; } //读取excel文件 public staticWorkbook readExcel(String filePath) { Workbook wb = null; if (filePath == null) { return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is = null; try{ is = newFileInputStream(filePath); if (".xls".equals(extString)) { return wb = newHSSFWorkbook(is); } else if (".xlsx".equals(extString)) { return wb = newXSSFWorkbook(is); } else{ return wb = null; } } catch(FileNotFoundException e) { e.printStackTrace(); } catch(IOException e) { e.printStackTrace(); } returnwb; } //根据excel文件内容数据的不同类型格式化 public staticObject getCellFormatValue(Cell cell) { Object cellValue = null; if (cell != null) { //判断cell类型 switch(cell.getCellType()) { caseCell.CELL_TYPE_NUMERIC: { cellValue =String.valueOf(cell.getNumericCellValue()); break; } caseCell.CELL_TYPE_FORMULA: { //判断cell是否为日期格式 if(DateUtil.isCellDateFormatted(cell)) { //转换为日期格式YYYY-mm-dd cellValue =cell.getDateCellValue(); } else{ //数字 cellValue =String.valueOf(cell.getNumericCellValue()); } break; } caseCell.CELL_TYPE_STRING: { cellValue =cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } } else{ cellValue = ""; } returncellValue; } //写入文件,toExcelMap内容格式为{"第五列":"xxxx","第六列:"xxxx"....} public static void writeExcel(ConcurrentHashMap<Integer, LinkedHashMap<String,Object>> toExcelMap, intcurrentSheet, String filePath) { OutputStream out = null; try{ //读取Excel文档 Workbook workBook =readExcel(filePath); //sheet 对应一个工作页 Sheet sheet =workBook.getSheetAt(currentSheet); /*** 往Excel中写新数据 */ for (int i = 1; i <= sheet.getLastRowNum(); i++) { try{ log.info("写入第{}条", i); Row row =sheet.getRow(i); if(toExcelMap.containsKey(i)) { LinkedHashMap<String, Object> info =toExcelMap.get(i); row.createCell(START_COLUMN).setCellValue(info.get("location") != null ? info.get("location").toString() : null); row.createCell(START_COLUMN + 1).setCellValue(info.get("status") != null ? info.get("status").toString() : null); row.createCell(START_COLUMN + 2).setCellValue(info.get("count") != null ? info.get("count").toString() : null); } }catch(Exception e) { errorWriteList.add(e); } } System.out.println(errorWriteList); //创建文件输出流,准备输出电子表格:这个必须有,否则你在sheet上做的任何操作都不会有效 out = newFileOutputStream(filePath); workBook.write(out); System.out.println("------- 数据导出成功(filePath"+filePath+") -------"); } catch(Exception e) { e.printStackTrace(); System.out.println("------- 数据导出失败 -------"); } finally{ try{ if (out != null) { out.flush(); out.close(); } } catch(IOException e) { e.printStackTrace(); } } }
java读取excel文件
免责声明:文章转载自《java读取excel文件》仅用于学习参考。如对内容有疑问,请及时联系本站处理。
上篇HBase的Write Ahead Log (WAL) —— 整体架构、线程模型C Primer+Plus(一)概览下篇
宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=