我正在使用apache POI进行excel导入和解析.我必须通过传递列名来获取数据.
这是我的代码
JSONObject jo = new JSONObject(); JSONArray dataCollection = new JSONArray(); JSONObject data = null; try { String tempCampaignFilesPath = getSessionData("userPath") + System.getProperty("file.separator") + "tempCampaignFiles"; File someFile = new File(tempCampaignFilesPath, fileName); /* read from this file */ FileInputStream fileInputStream = new FileInputStream(someFile); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheet(sheetName); int rowNum = sheet.getLastRowNum() + 1; int colNum = sheet.getRow(0).getLastCellNum(); Row row = null; Cell cell = null; for (int i = 1; i < rowNum; i++) { row = sheet.getRow(i); data = new JSONObject(); for (int j = 0; j < colNum; j++) { cell = row.getCell(j); data.put(columnList.get(j), cellToString(cell)); } dataCollection.put(data); } fileInputStream.close(); // someFile.delete(); jo.put("tableData", dataCollection); } catch (Exception e) { e.printStackTrace(); } return jo;
列索引有一个规定,但我怎么能按列名做.
请帮我.
您必须将列名转换为索引:
int colIdx = CellReference.convertColStringToIndex(letter); CellUtil.getCell(row, colIdx)
或者如果您需要将列索引转换为字符串:
String colName = CellReference.convertNumToColString(colIdx)
请在下面找到代码的另一种解决方法.请参阅代码中的注释,以便更清楚我所做的事情.
JSONObject jo = new JSONObject(); JSONArray dataCollection = new JSONArray(); JSONObject data = null; try { String tempCampaignFilesPath = getSessionData("userPath") + System.getProperty("file.separator") + "tempCampaignFiles"; File someFile = new File(tempCampaignFilesPath, fileName); /* read from this file */ FileInputStream fileInputStream = new FileInputStream(someFile); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheet(sheetName); int rowNum = sheet.getLastRowNum() + 1; int colNum = sheet.getRow(0).getLastCellNum(); Row row = null; Cell cell = null; /* first row data for column names and index */ MapcolMapByName = new HashMap (); if (sheet.getRow(0).cellIterator().hasNext()) { for (int j = 0; j < colNum; j++) { colMapByName.put(cellToString(sheet.getRow(0).getCell(j)), j); } } System.out.println(colMapByName);//shows the indexes of columns populated by traversing first row /* first row data */ for (int i = 1; i < rowNum; i++) { row = sheet.getRow(i); data = new JSONObject(); //colMap consists the columnnames and alias name for it for (Entry colData : colMap.entrySet()) { cell = row.getCell(colMapByName.get(colData.getValue()));//gives the index of column from colMapByName Map by passing column name data.put(colData.getKey(), cellToString(cell));//now the data passed to the alias for the column tobe used in application } dataCollection.put(data); } fileInputStream.close(); someFile.delete(); jo.put("tableData", dataCollection); } catch (Exception e) { e.printStackTrace(); } return jo;