java poi excel合并单元格,如何从Java中使用Apache POI的Excel合并单元格看？

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 rows = new ArrayList();

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 rowIterator = sheet.iterator();

while (rowIterator.hasNext()) {

Row row = rowIterator.next();

//For each row, iterate through all the columns

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

}

}