以下代码是在maven工程中编写,maven地址
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
代码示例 可读取合并单元格的信息
java">public class PoiUtils {
private static NumberFormat numberFormat = NumberFormat.getInstance();
static {
//关闭分组显示
numberFormat.setGroupingUsed(false);
}
/**
* @Comments :读取Excel文件
* @param file 待读取的文件(通过前端上传的文件信息)
* @param heads 表头对应的值,用于返回map的key
* @param startRows 起始行
* @param startCols 起始列
* @return
* @Author :乐享生活522
* @Date :2019年10月15日 下午7:46:23
*/
public static List<Map<String, Object>> readExcelFile(MultipartFile file, String[] heads, int startRows, int startCols) {
InputStream fis = null;
Workbook book = null;
String fileName = file.getOriginalFilename();
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
Map<String, Object> mergeRowsMap = new HashMap<>();
List<Map<String, Object>> rowList = new ArrayList<>();
try {
fis = file.getInputStream();
if (fileType.equals("xls")) {
book = new HSSFWorkbook(fis);
} else if (fileType.equals("xlsx")) {
book = new XSSFWorkbook(fis);
} else {
return null;
}
Sheet sheet = book.getSheetAt(0);
int totalRows = sheet.getLastRowNum() + 1;//获取最后一行的序号(总行数)
for (int i = startRows; i < totalRows; i++) {
Row row = sheet.getRow(i);
if (row != null) {
Map<String, Object> rowMap = new HashMap<>();
for (int j = startCols; j < heads.length; j++) {
Cell cell = row.getCell(j);
String cellValue = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = DateUtils.format(DateUtil.getJavaDate(cell.getNumericCellValue()), Pattern.YMD);
} else {
cellValue = numberFormat.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() ? "是" : "否";
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "";
break;
}
}
Map<String, Object> mergeCellMap = isMergedRegion(sheet, i, j);
if (MapUtils.isNotEmpty(mergeCellMap)) {
int firstRow = MapUtils.getIntValue(mergeCellMap, "firstRow");
int firstColumn = MapUtils.getIntValue(mergeCellMap, "firstColumn");
int lastRow = MapUtils.getIntValue(mergeCellMap, "lastRow");
if (StringUtils.isEmpty(cellValue) && firstRow < i && lastRow >= i && firstColumn == j) {
cellValue = MapUtils.getString(mergeRowsMap, heads[j], "");
} else {
mergeRowsMap.put(heads[j], cellValue);
}
}
/*if (mergeList.contains(heads[j])) {//存在合并列中
if (StringUtils.isEmpty(cellValue)) {
cellValue = MapUtils.getString(mergeRowsMap, heads[j], "");
} else {
mergeRowsMap.put(heads[j], cellValue);
}
}*/
rowMap.put(heads[j], cellValue);
}
rowList.add(rowMap);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (book != null) {
try {
book.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return rowList;
}
/**
* @Comments :判断当前单元格是否合并
* @param sheet
* @param row 当前行
* @param column 当前列
* @Author :乐享生活522
* @Date :2019年10月15日 下午7:15:13
*/
private static Map<String, Object> isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Map<String, Object> resultMap = new HashMap<>();
resultMap.put("firstRow", firstRow);
resultMap.put("lastRow", lastRow);
resultMap.put("firstColumn", firstColumn);
return resultMap;
}
}
}
return null;
}
}