如何从中读取数据。xlsx并将数据转换为地图

Question

我需要从Excel表中读取数据，并需要将数据转换为键值对。

我已经写了下面的代码。

这是我的代码：

import java.io.File;  
import java.io.FileInputStream;  
import java.util.Iterator;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  

public class XLSXReaderExample  {  
    public static void main(String[] args) {  
       try {  
          File file = new File("C:\\demo\\employee.xlsx");   //creating a new file instance  
          FileInputStream fis = new FileInputStream(file);   //obtaining bytes from the file  
          //creating Workbook instance that refers to .xlsx file  
          XSSFWorkbook wb = new XSSFWorkbook(fis);   
          XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object  
          Iterator<Row> itr = sheet.iterator();    //iterating over excel file  
          while (itr.hasNext()){  
             Row row = itr.next();  
             Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column  
             while (cellIterator.hasNext()){  
                Cell cell = cellIterator.next();  
                switch (cell.getCellType()){  
                   case Cell.CELL_TYPE_STRING:    //field that represents string cell type  
                      System.out.print(cell.getStringCellValue() + "\t\t\t");  
                      break;  
                   case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type  
                      System.out.print(cell.getNumericCellValue() + "\t\t\t");  
                      break;  
                   case Cell.CELL_TYPE_Date:    //field that represents Date cell type
                      System.out.print(cell.getDateCellValue() + "\t\t\t");  
                      break; 
                   default:  
                }  
             }  
             System.out.println("");  
          }  
       }catch(Exception e){  
          e.printStackTrace();  
       }  
    }  
 } 

我得到的结果如下：

Employee ID   Employee Name    Salary     Designation          Department   
1223.0         Harsh                      Marketing Manager    Marketing
3213.0         Vivek           15000.0    Financial Advisor    Finance  

然而，我需要的输出，所有的头列应该来在映射键和相应的数据应该作为值。

我需要将我的数据设置为如下格式，请帮助<代码>地图

Answer 1

正如在comment中所写的，因为您无法知道单元格是否包含标题值或数据值，所以您不可能想做什么。

乍一看，你有两种方法来实现你的目标：

• 尝试只包含一个表的文件，并且标题行必须始终位于同一excel行（请参见此处）
• 向标题内容添加自定义标记：如

编辑：

您添加了一条注释，确认标题始终位于第一行，并且文件中只有一个表，所以下一个代码应该可以工作。

public static void main(String[] args) {
    try {
        File file = new File("C:\\demo\\employee.xlsx");   //creating a new file instance
        FileInputStream fis = new FileInputStream(file);   //obtaining bytes from the file
        //creating Workbook instance that refers to .xlsx file
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
        Iterator<Row> itr = sheet.iterator();    //iterating over excel file

        // CAREFUL HERE! use LinkedHashMap to guarantee the insertion order!
        Map<String, List<String>> myMap = new LinkedHashMap<>();

        // populate map with headers and empty list
        if (itr.hasNext()) {
            Row row = itr.next();
            Iterator<Cell> headerIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                myMap.put(getCellValue(cell), new ArrayList<>());
            }
        }

        Iterator<List<String>> columnsIterator;
        // populate lists
        while (itr.hasNext()) {

            // get the list iterator every row to start from first list
            columnsIterator = myMap.values().iterator();
            Row row = itr.next();
            Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                // here don't check hasNext() because if the file not contains problems
                // the # of columns is same as # of headers
                columnsIterator.next().add(getCellValue(cell));
            }
        }

        // here your map should be filled with data as expected

    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static String getCellValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:    //field that represents string cell type
            return cell.getStringCellValue() + "\t\t\t";
        case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type
            return cell.getNumericCellValue() + "\t\t\t";
        case Cell.CELL_TYPE_Date:    //field that represents Date cell type
            return cell.getDateCellValue() + "\t\t\t";
        default:
            return "";
    }
}