Java 使用 POI Apache 从 Excel 读取数据时将数据添加到 ArrayList
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38522931/
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
Adding data to ArrayList while reading data from Excel using POI Apache
提问by Ebad Ali
I am trying to read data from Excel sheets using POI Apache. The problem I am having is that I want to read the data of all cell of a row at the same time and store it in ArrayList of Type Class but the output is only cell by cell.
我正在尝试使用 POI Apache 从 Excel 工作表中读取数据。我遇到的问题是我想同时读取一行的所有单元格的数据并将其存储在类型类的 ArrayList 中，但输出只是一个单元格。
Here is the class that open the excel sheet and read the data cell by cell.
这是打开excel表格并逐个单元格读取数据的类。
package testing;
import javax.swing.JFileChooser;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelDemo
{
ArrayList<Data> list = new ArrayList<>();
String path;
public ReadExcelDemo(String path)
{
this.path = path;
try
{
FileInputStream file = new FileInputStream(new File(path));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
System.out.println("");
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
Data Class
数据类
package testing;
public class Data {
int ID;
String F_Name,L_Name;
public Data(int ID, String F_Name, String L_Name) {
this.ID = ID;
this.F_Name = F_Name;
this.L_Name = L_Name;
}
public int getID() {
return ID;
}
public String getF_Name() {
return F_Name;
}
public String getL_Name() {
return L_Name;
}
I want to add the cell data in Arraylist like this at a single time
我想像这样一次性在 Arraylist 中添加单元格数据
List.add(new Data(1,"Amit","shukla"));
but the data the iterator return is one by one like first it outputs 1then amitand then shuklawhich is really difficult to add to arraylist
但是迭代器返回的数据是一一的，比如先输出1，然后是amit，然后是shukla，这真的很难添加到 arraylist
I tried so much to add data to ArrayList at a single line but I couldn't. It would be really helpful if you guy help me solve this problem.
我尝试在一行中将数据添加到 ArrayList 中，但我做不到。如果你能帮我解决这个问题，那将非常有帮助。
采纳答案by user2025528
this.path = path;
this.path = 路径；
try
{
FileInputStream file = new FileInputStreaHashMap<K, V>ile(path));
HashMap<Integer, Data> mp= new HashMap<Integer, Data>();
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
System.out.println("");
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
int i=0;
int j=0;
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
i=Integer.parseInt(cell.getNumericCellValue());
Data d= new Data();
d.setId(cell.getNumericCellvalue());
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
if( j==0){
Data data= mp.get(i);
data.setName(cell.getStringCellValue());
mp.put(i, data);
j=j+1;
}
else
{
Data data= mp.get(i);
data.setLastName(cell.getStringCellValue());
mp.put(i, data);
j=0;
}
break;
}
}
System.out.println("");
}
List<Data> dataList= new ArrayList<Data>();
for (Data d : mp.values()) {
dataList.add(d);
}
file.close();
}
catch (Exception e)
{
e.printStackTrace();
}
回答by Rishi12
You can add a single row of an excel sheet by a single iteration using this,
您可以使用此方法通过一次迭代添加单行 Excel 工作表，
public void ReadExcel(String filePath,String fileName,String sheetName) throws InterruptedException, IOException{
File file = new File(filePath+"\"+fileName);
//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);
Workbook AddCatalog = null;
//Find the file extension by splitting file name in substring and getting only extension name
String fileExtensionName = fileName.substring(fileName.indexOf("."));
//Check condition if the file is a .xls file or .xlsx file
if(fileExtensionName.equals(".xls")){
//If it is .xls file then create object of HSSFWorkbook class
AddCatalog = new HSSFWorkbook(inputStream);
}
else if(fileExtensionName.equals(".xlsx")){
//If it is .xlsx file then create object of XSSFWorkbook class
AddCatalog = new XSSFWorkbook(inputStream);
}
//Read sheet inside the workbook by its name
Sheet AddCatalogSheet = AddCatalog.getSheet(sheetName);
//Find number of rows in excel file
int rowcount = AddCatalogSheet.getLastRowNum()- AddCatalogSheet.getFirstRowNum();
System.out.println("Total row number: "+rowcount);
for(int i=1; i<rowcount+1; i++){
//Create a loop to get the cell values of a row for one iteration
Row row = AddCatalogSheet.getRow(i);
List<String> arrName = new ArrayList<String>();
for(int j=0; j<row.getLastCellNum(); j++){
// Create an object reference of 'Cell' class
Cell cell = row.getCell(j);
// Add all the cell values of a particular row
arrName.add(cell.getStringCellValue());
}
System.out.println(arrName);
System.out.println("Size of the arrayList: "+arrName.size());
// Create an iterator to iterate through the arrayList- 'arrName'
Iterator<String> itr = arrName.iterator();
while(itr.hasNext()){
System.out.println("arrayList values: "+itr.next());
}
}
}
回答by Abderrahmen
You have a problem in Switch and Casepart change to this should work :
您在Switch 和 Case部分更改时遇到问题，应该可以：
switch (cell.getCellType()) {
case NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
