读取excel表格的内容,并且读取excel合并单元格的内容,从合并的单元格中找到有值的单元格内容返回
读取excel表格的excel格式可以是
读取的代码:
/**
* @Description: 判断excel表格合并单元格哪个单元格有值,返回有值的那个表格
* @Author: fuwei
* @Date: 2020/1/2 9:55
*/
public static String getMergeValue(Result result,Sheet sheet) {
//为空判断
if (result == null) {
log.info("需要获取值的单元格为null");
return null;
}
//判断是否是合并单元格
if (result.isMergeCell()) {
int firstColumn = result.getFirstColumn();
int lastColumn = result.getLastColumn();
int firstRow = result.getFirstRow();
int lastRow = result.getLastRow();
//判断是上下合并,列相同,行不同
if (firstColumn == lastColumn && firstRow!=lastRow) {
for (int i= firstRow-1; i<lastRow ; i++){
Row row = sheet.getRow(i);
Cell cell = row.getCell(lastColumn-1);
String cellStringValue = getCellStringValue(cell);
if (!cellStringValue.isEmpty()){
return cellStringValue;
}
}
//判断是不是左右合并
} else if (firstRow == lastRow && firstColumn!=lastColumn) {
//获取当前行
Row row = sheet.getRow(firstRow-1);
for (int i = firstColumn-1; i < lastColumn; i++) {
Cell cell = row.getCell(i);
String cellStringValue = getCellStringValue(cell);
if (!cellStringValue.isEmpty()) {
return cellStringValue;
}
}
//其他合并方式
} else {
//当进行多行,多列合并的时候
for (int i = firstRow-1; i <=lastRow ; i++) {
Row row = sheet.getRow(i);
for (int j = firstColumn-1; j<= lastColumn ;j++){
Cell cell = row.getCell(j);
String cellStringValue = getCellStringValue(cell);
if (!cellStringValue.isEmpty()) {
return cellStringValue;
}
}
}
}
} else {
Row row = sheet.getRow(result.getFirstRow());
Cell cell = row.getCell(result.getFirstColumn());
String cellStringValue = getCellStringValue(cell);
return cellStringValue;
}
return "";
}
获取值的方法:
/**
*根据数据格式进行数据的获取,并转换
* @param cell
* @return
*
*/
public static String getCellStringValue(Cell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case STRING://字符串类型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue.isEmpty();
break;
case NUMERIC: //数值类型
//判断数值类型中是不是日期格式
if (HSSFDateUtil.isCellDateFormatted(cell))
{
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
}else {
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
case FORMULA: //公式
cell.setCellType(CellType.FORMULA);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case BLANK:
cellValue.isEmpty();
break;
case BOOLEAN:
break;
case ERROR:
break;
default:
break;
}
return cellValue;
}
判断是否是合并单元格方法在我上一个博客中
如有异议,欢迎提问