java 按列名获取数据apache poi excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34291110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
getting data by column name apache poi excel
提问by Pawan Pandey
I am using apache POI for excel import and parsing . I have to get the data by passing column name .
我正在使用 apache POI 进行 excel 导入和解析。我必须通过传递列名来获取数据。
this is my code
这是我的代码
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;
There is a provision for column index but how could I do it by column name.
有一个列索引的规定，但我怎么能通过列名来做到这一点。
Please help me.
请帮我。
采纳答案by Pawan Pandey
Please find below the code an another workaround for this .Please see the comments in code to be more clear what I have done.
请在代码下方找到另一种解决方法。请参阅代码中的注释以更清楚我所做的工作。
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 */
Map<String, Integer> colMapByName = new HashMap<String, Integer>();
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<String, String> 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;
回答by dlopatin
You have to convert column name to index:
您必须将列名转换为索引：
int colIdx = CellReference.convertColStringToIndex(letter);
CellUtil.getCell(row, colIdx)
or if you need convert column index to string:
或者如果您需要将列索引转换为字符串：
String colName = CellReference.convertNumToColString(colIdx)
