java 如何使用jxl逐页读取excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12372136/
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
How to read an excel file, sheet by sheet using jxl
提问by moCap
I'm working on a utility to convert excel file from one format to another. I was suggested to use jxl library to meet the requirements which are as follows.
read the file sheet by sheet and do following
我正在开发一种将excel文件从一种格式转换为另一种格式的实用程序。我被建议使用 jxl 库来满足以下要求。
逐页阅读文件并执行以下操作
get the sheet name, make it key of a map and then get its column headers and make them value.
获取工作表名称，使其成为地图的键，然后获取其列标题并使它们成为值。
it will result in following
这将导致以下
Map<String, List<String>> result = result<key=sheetName, value=list of column headers>
Do this for all sheets in the given file
对给定文件中的所有工作表执行此操作
I did this in following way
我按照以下方式做到了这一点
public Map> function(String filePath ) throws IOException, BiffException{
public Map> function(String filePath) 抛出 IOException, BiffException{
Map<String, List<String>> map = new HashMap<String, List<String>>();
Workbook workBook=Workbook.getWorkbook(new File (filePath));
String [] sheetNames = workBook.getSheetNames();
Sheet sheet=null;
List<String > fields = new ArrayList<String>();
for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){
sheet=workBook.getSheet(sheetNames[sheetNumber]);
for (int columns=0;columns < sheet.getColumns();columns++){
fields.add(sheet.getCell(columns, 0).getContents());
}
map.put(sheetNames[sheetNumber],fields);
}
return map;
}
I did this with a hope of getting desired result but what it does is that it stores column headers of all sheets against each key as value. i.e. if there are two sheets in file named as
我这样做是希望获得所需的结果，但它所做的是将所有工作表的列标题针对每个键存储为值。即如果文件中有两张名为
- sheet1
- sheet2
- 表 1
- 表2
and following are their column headers
以下是他们的列标题
Sheet1 -> id, name
sheet2 -> category, price
Sheet1 -> id，名称
sheet2 -> 类别，价格
then map will be like
然后地图会像
result<sheet1,<id, name, caegory, price>>
result<sheet2,<id, name, caegory, price>>
Can't figure out what I'm doing wrong? Help please, as my project has a lot of backend calculations and I don't want to spend a lot of time in this thing.
无法弄清楚我做错了什么？请帮助，因为我的项目有很多后端计算，我不想在这件事上花很多时间。
Any help will be highly appreciated
任何帮助将不胜感激
回答by moCap
I solved the issue after posting it here, but forgot to give solution here.
The problem in above code is location of list declaration.
It should be declared inside the for, so that it gets refreshed (emptied) after each iteration of loop and stores data of only one sheet at a time.
The correct code is below.
我在这里发布后解决了这个问题，但忘记在这里给出解决方案。
上面代码中的问题是列表声明的位置。
它应该在 中声明for，以便在每次循环迭代后刷新（清空）并一次仅存储一张表的数据。
正确的代码如下。
Map<String, List<String>> map = new HashMap<String, List<String>>();
Workbook workBook=Workbook.getWorkbook(new File (filePath));
String [] sheetNames = workBook.getSheetNames();
Sheet sheet=null;
for (int sheetNumber =0; sheetNumber < sheetNames.length; sheetNumber++){
List<String > fields = new ArrayList<String>();
sheet=workBook.getSheet(sheetNames[sheetNumber]);
for (int columns=0;columns < sheet.getColumns();columns++){
fields.add(sheet.getCell(columns, 0).getContents());
}
map.put(sheetNames[sheetNumber],fields);
}
return map;
}
Hope It'll help someone
希望它会帮助某人
回答by naisenTMI
public String ExcelValueGetFn(String FilePath, int column, int row) throws BiffException, IOException {
Workbook workbook = null;
final String EXCEL_FILE_LOCATION = FilePath;
workbook = Workbook.getWorkbook(new File(EXCEL_FILE_LOCATION));
Sheet sheet = workbook.getSheet(0);
Cell cell1 = sheet.getCell(column, row);
System.out.print(cell1.getContents() + ":");
String textToSendFromExcel = cell1.getContents();
return textToSendFromExcel;
}
