如何将值从Excel存储到Java中的某些集合

Question

我有一个如下的Excel文件，

**Method Name**            **Status Code**     **user**    **password**
getLoggedinUserDetails        400              anto          test
createRequisition             400              mayank       hexgen
excelMDM                      400              xxxxx        hexgen
createOrder                   400              yyyyy        hexgen
confirmOrder                  400              zzzzz        hexgen

我想将上述详细信息保存一些，collection以便我可以访问details by providing username and get the details like Method Name, Status code,and password.

我尝试过这样的事情，只能存储 method name and status code

package com.hexgen.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class TestMethodsDetails {
    public Map<String, Integer> getKnownGoodMap(String filePath) {
        String key = "";
        int value = 0;
        //filePath="TestMethodDetails.xls";
        Map<String, Integer> knownGoodMap = new LinkedHashMap<String, Integer>();
        try {

            FileInputStream file = new FileInputStream(new File(filePath));

            // Get the workbook instance for XLS file
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            // Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);

            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        value = (int) cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_STRING:
                        key = cell.getStringCellValue();
                        break;
                    }

                    if (key != null && value != Integer.MIN_VALUE) {
                        knownGoodMap.put(key, value);
                        key = null;
                        value = Integer.MIN_VALUE;
                    }
                }
            }
            file.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return knownGoodMap;
    }
    public static void main(String[] args) {
        TestMethodsDetails details = new TestMethodsDetails();
        System.out.println("Method Details : "+details.getKnownGoodMap("TestMethodDetails.xls"));
    }
}

上面的代码显示以下内容：

Method Details : {Method Name=0, getLoggedinUserDetails=400, createRequisition=400, excelMDM=400, createOrder=400, confirmOrder=400}

to be frank i really don't know what mechanism to use to store these details so that i enables easy access for me to process details, the user name and password will be different, i have given here some user and password for sample only

请帮助我做到这一点。

Answer 1

您应该使用面向对象的方法来创建实体。创建一个代表您的excel数据行的类，假设它称为Records，然后将该类的对象放在以用户名作为键的Hashmap中。这是示例类：

public class Record {

    private String methodName;
    private String statusCode;
    private String user;
    private String password;

    public String getMethodName() {
        return methodName;
    }

    public void setMethodName(String methodName) {
        this.methodName = methodName;
    }

    public String getStatusCode() {
        return statusCode;
    }

    public void setStatusCode(String statusCode) {
        this.statusCode = statusCode;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

定义一个集合以将记录存储为

Map<String, Record> recordsMap = new <String, Record>HashMap();

读取excel文件，为每行创建一条记录，然后保存在此集合中。从地图中检索记录应该容易，快捷。