如何使用Apache POI从Java中Excel的合并单元格中读取？

java apache import-from-excel apache-poi cell

2022-09-03 07:16:44

我有一个.xlsx格式的Excel文件。我通过合并单元格以形成各种列来存储数据。我正在通过Java Web应用程序读取Excel文件并将其数据保存到数据库（MySQL）。但是，当我从合并的单元格中读取时，我会获得空值以及列中存储的内容以及标题。我正在使用Apache POI。我的代码是：

public static void excelToDBLogIN() {

    FileInputStream file = null;
    Boolean flag = true;
    ArrayList<String> rows = new ArrayList<String>();
    try {


        // here uploadFolder contains the path to the Login 3.xlsx file

        file = new FileInputStream(new File(uploadFolder + "Login 3.xlsx"));

        //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);

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

            String tuple = "";
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                //Check the cell type and format accordingly
                switch (cell.getCellType()) {

                        case Cell.CELL_TYPE_NUMERIC:                            

                        //int value = new BigDecimal(cell.getNumericCellValue()).setScale(0, RoundingMode.HALF_UP).intValue();
                        //tuple = tuple + String.valueOf(value) + "+";

                        DataFormatter objDefaultFormat = new DataFormatter();    

                        String str = objDefaultFormat.formatCellValue(cell);

                        tuple = tuple + str + "+";

                        break;

                    case Cell.CELL_TYPE_STRING:

                        tuple = tuple + cell.getStringCellValue() + "+";

                        break;

                    case Cell.CELL_TYPE_BLANK:                                                        

                        tuple = tuple + "" + "+";

                        break;


                }

            }

            rows.add(tuple);
            flag = true;

        }

    }    


    } catch (Exception e) {

        e.printStackTrace();

    } finally {

        if (file != null) {

            try {
                file.close();
                file = null;
            } catch (Exception e) {

                System.out.println("File closing operation failed");
                e.printStackTrace();
            }
        }                                 

    }

    }

}

我在网上搜索答案，但没有找到任何相关的东西。

答案 1

以下代码片段可能会有所帮助。

while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        //For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        outer:
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            //will iterate over the Merged cells
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress region = sheet.getMergedRegion(i); //Region of merged cells

                int colIndex = region.getFirstColumn(); //number of columns merged
                int rowNum = region.getFirstRow();      //number of rows merged
                //check first cell of the region
                if (rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) {
                    System.out.println(sheet.getRow(rowNum).getCell(colIndex).getStringCellValue());
                    continue outer;
                }
            }
            //the data in merge cells is always present on the first cell. All other cells(in merged region) are considered blank
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK || cell == null) {
                continue;
            }
            System.out.println(cell.getStringCellValue());
        }
    }

答案 2

此方法可以读取特定单元格（包括合并单元格）：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public static void readCell(String excelFilePath, int rowIndex, int columnIndex) throws FileNotFoundException, IOException {
    try (InputStream inp = new FileInputStream(excelFilePath)) {
        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFCell cell = wb.getSheetAt(0).getRow(rowIndex).getCell(columnIndex);

        switch (cell.getCellType()) {

        case STRING:
            System.out.println(cell.getRichStringCellValue().getString());
            break;

        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.println(cell.getDateCellValue());
            } else {
                System.out.println(cell.getNumericCellValue());
            }
            break;

        case BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;

        case FORMULA:
            System.out.println(cell.getCellFormula());
            break;

        case BLANK:
            System.out.println();
            break;

        default:
            System.out.println();
        }

        wb.close();
    }
}

依赖关系：POI 5.0.0、JDK 1.8.0