excel 解析 java_java解析EXCEL

1 packagemain.java.commUtils;2

3 import org.apache.poi.ss.usermodel.*;4

5 importjava.io.File;6 importjava.io.FileInputStream;7 importjava.io.IOException;8 importjava.util.ArrayList;9 importjava.util.HashMap;10 importjava.util.List;11 importjava.util.Map;12

13

14 public classExcelReader {15 privateString filePath;16 privateString sheetName;17 privateWorkbook workBook;18 privateSheet sheet;19 private ListcolumnHeaderList;20 private List>listData;21 private List>mapData;22 private booleanflag;23

24 publicExcelReader(String filePath, String sheetName) {25 this.filePath =filePath;26 this.sheetName =sheetName;27 this.flag = false;28 this.load();29 }30

31 private voidload() {32 FileInputStream inStream = null;33 try{34 inStream = new FileInputStream(newFile(filePath));35 workBook =WorkbookFactory.create(inStream);36 sheet =workBook.getSheet(sheetName);37 } catch(Exception e) {38 e.printStackTrace();39 } finally{40 try{41 if (inStream != null) {42 inStream.close();43 }44 } catch(IOException e) {45 e.printStackTrace();46 }47 }48 }49

50 privateString getCellValue(Cell cell) {51 String cellValue = "";52 DataFormatter formatter = newDataFormatter();53 if (cell != null) {54 switch(cell.getCellType()) {55 caseCell.CELL_TYPE_NUMERIC:56 if(DateUtil.isCellDateFormatted(cell)) {57 cellValue =formatter.formatCellValue(cell);58 } else{59 double value =cell.getNumericCellValue();60 int intValue = (int) value;61 cellValue = value - intValue == 0 ?String.valueOf(intValue) : String.valueOf(value);62 }63 break;64 caseCell.CELL_TYPE_STRING:65 cellValue =cell.getStringCellValue();66 break;67 caseCell.CELL_TYPE_BOOLEAN:68 cellValue =String.valueOf(cell.getBooleanCellValue());69 break;70 caseCell.CELL_TYPE_FORMULA:71 cellValue =String.valueOf(cell.getCellFormula());72 break;73 caseCell.CELL_TYPE_BLANK:74 cellValue = "";75 break;76 caseCell.CELL_TYPE_ERROR:77 cellValue = "";78 break;79 default:80 cellValue =cell.toString().trim();81 break;82 }83 }84 returncellValue.trim();85 }86

87 private voidgetSheetData() {88 listData = new ArrayList>();89 mapData = new ArrayList>();90 columnHeaderList = new ArrayList();91 int numOfRows = sheet.getLastRowNum() + 1;92 for (int i = 0; i < numOfRows; i++) {93 Row row =sheet.getRow(i);94 Map map = new HashMap();95 List list = new ArrayList();96 if (row != null) {97 for (int j = 0; j < row.getLastCellNum(); j++) {98 Cell cell =row.getCell(j);99 if (i == 0) {100 columnHeaderList.add(getCellValue(cell));101 } else{102 map.put(columnHeaderList.get(j), this.getCellValue(cell));103 }104 list.add(this.getCellValue(cell));105 }106 }107 if (i > 0) {108 mapData.add(map);109 }110 listData.add(list);111

112 }113 flag = true;114 }115

116 public String getCellData(int row, intcol) {117 if (row <= 0 || col <= 0) {118 return null;119 }120 if (!flag) {121 this.getSheetData();122 }123 if (listData.size() >= row && listData.get(row - 1).size() >=col) {124 return listData.get(row - 1).get(col - 1);125 } else{126 return null;127 }128 }129

130 public String getCellData(introw, String headerName) {131 if (row <= 0) {132 return null;133 }134 if (!flag) {135 this.getSheetData();136 }137 if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {138 return mapData.get(row - 1).get(headerName);139 } else{140 return null;141 }142 }143

144

145 public static voidmain(String[] args) {146 ExcelReader eh = new ExcelReader("C:\\Users\\ty\\Documents\\工作簿1.xlsx", "Sheet2");147 System.out.println(eh.getCellData(1, 1));148 //System.out.println(eh.getCellData(1, "test1"));

149 System.out.println(eh.getCellData(1, 1));150 eh.getSheetData();151

152 }153 }