使用 POI Apache 从 Excel 读取数据时向 ArrayList 添加数据

Question

我正在尝试使用 POI Apache 从 Excel 工作表中读取数据。我遇到的问题是我想同时读取一行所有单元格的数据并将其存储在类型类的 ArrayList 中，但输出只是逐个单元格。

这是打开 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();
        }
    }
}

数据类

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;
    }

我想像这样一次性将单元格数据添加到Arraylist中

List.add(new Data(1,"Amit","shukla"));

但是迭代器返回的数据是一个一个的，就像它首先输出 1 然后是 amit 然后是 shukla ，这真的很难添加到 arraylist

我尝试了很多在一行中将数据添加到 ArrayList 但我做不到。如果你们帮我解决这个问题，那将非常有帮助。

原文由 Ebad Ali 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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();
    }

原文由 user2025528 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

最简单的方法如下

1. 创建一个包含所有 excel 列名称的数组列表。
2. 创建一个列表以将每一行保存为一个对象
3. 创建一个 HashMap 用于将列名和单元格值保存为键值对
4. 遍历每一行并遍历该行的每个单元格。并将单元格值拉入我们创建的 HashMap
5. 读取当前行的所有单元格后，使用 Jackson 对象映射器将此 HashMap 映射到自定义 pojo 类。并将此对象添加到我们在步骤 2 中创建的列表中。
6. 现在重复步骤 4 和 5。

你可以参考下面的代码

                //declare list of column headers in the excel file
            List<String> header = new ArrayList<>(Arrays.asList("ID","FirstName","LastName"));
            //list for holding each row of data as list of custom objects
            List<CustomObject> rawDataList = new ArrayList<>();
            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();
                //map for holding cell values of current row as key value pair
                Map<String, String> rowDataMap = new HashMap<>();
                Cell cell;
                for (int k = 0; k < row.getLastCellNum(); k++) {

                    if (null != (cell = row.getCell(k))) {
                        switch (cell.getCellType()) {
                        case NUMERIC:
                            //Get the column name from header array and push the cell value into hashmap
                            rowDataMap.put(header.get(k),NumberToTextConverter.toText(cell.getNumericCellValue()));
                            break;
                        case STRING:
                            //Get the column name from header array and push the cell value into hashmap
                            rowDataMap.put(header.get(k), cell.getStringCellValue());
                            break;

                        }
                    }
                }
                //create object of jackson object mnapper (Better configure as a bean)
                ObjectMapper mapper = new ObjectMapper()
                          .configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
                mapper.configure(DeserializationFeature.ACCEPT_SINGLE_VALUE_AS_ARRAY, true);
                mapper.configure(DeserializationFeature.ACCEPT_EMPTY_ARRAY_AS_NULL_OBJECT, true);

                // after reading all the cells of current row map the hashmap into
                //custom pojo class using object mapper
                CustomObject rawData = mapper.convertValue(rowDataMap, CustomObject.class);
                //add the cusom pojo object into the list
                rawDataList.add(rawData);

            }

原文由 5huraif 发布，翻译遵循 CC BY-SA 4.0 许可协议