我有一个.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();
}
}
}
}
}
我在网上搜索了答案，但没有找到任何相关内容。
最佳答案
遵循以下代码段可能会有所帮助。
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());
}
}