如何使用Apache POI从Java中的Excel合并单元格中读取? [英] How to read from merged cells of Excel in Java using Apache POI?

查看:66
本文介绍了如何使用Apache POI从Java中的Excel合并单元格中读取?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

I have a Excel file in .xlsx format. I have stored data by merging cells to form various columns. I am reading the Excel file via a Java web application and saving its data to a database (MySQL). But when I read from merged cells I get null values along with what are stored in the columns as well as the headers. I am using Apache POI. My code is:

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

    }

    }

}

我在网上搜索了答案,但没有找到任何相关内容.

I searched for answers in the web but did not find anything relevant.

推荐答案

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

Following code of snippet might help.

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

这篇关于如何使用Apache POI从Java中的Excel合并单元格中读取?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆