import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 读取Excel并解析合并单元格,将结果转为Map集合
*/
public class ReadMergeCellExcelUtil {
/**
* 读取Excel数据
*
* @param file 上传的Excel文件
* @return 解析后的数据列表
*/
public static List<Map<String, String>> readExcelToObj(MultipartFile file) {
List<Map<String, String>> result = new ArrayList<>();
try (InputStream inputStream = file.getInputStream()) {
Workbook workbook = XSSFWorkbookFactory.create(inputStream);
result = readExcel(workbook, 0, 0, 0);
} catch (IOException e) {
e.printStackTrace();
}
return result;
}
/**
* 读取Excel文件
*
* @param workbook 工作簿
* @param sheetIndex sheet页下标:从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 去除最后读取的行
* @return 解析后的数据列表
*/
private static List<Map<String, String>> readExcel(Workbook workbook, int sheetIndex, int startReadLine, int tailLine) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
List<Map<String, String>> result = new ArrayList<>();
for (int i = startReadLine; i <= sheet.getLastRowNum() - tailLine; i++) {
Row row = sheet.getRow(i);
Map<String, String> rowData = parseRow(sheet, row, formulaEvaluator);
result.add(rowData);
}
return result;
}
/**
* 解析一行数据
*
* @param sheet 工作表
* @param row 行对象
* @param formulaEvaluator 公式评估器
* @return 解析后的行数据
*/
private static Map<String, String> parseRow(Sheet sheet, Row row, FormulaEvaluator formulaEvaluator) {
Map<String, String> rowData = new HashMap<>();
if (row != null) {
int cellIndex = 0;
for (Cell cell : row) {
String cellValue = getCellValue(sheet, cell, formulaEvaluator);
rowData.put("field" + cellIndex, cellValue);
cellIndex++;
}
}
return rowData;
}
/**
* 获取单元格的值
*
* @param sheet 工作表
* @param cell 单元格
* @param formulaEvaluator 公式评估器
* @return 单元格值
*/
private static String getCellValue(Sheet sheet, Cell cell, FormulaEvaluator formulaEvaluator) {
if (cell == null) return "";
return isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex())
? getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex(), formulaEvaluator)
: getCellStringValue(cell, formulaEvaluator);
}
/**
* 获取合并单元格的值
*
* @param sheet 工作表
* @param row 行号
* @param column 列号
* @param formulaEvaluator 公式评估器
* @return 合并单元格值
*/
private static String getMergedRegionValue(Sheet sheet, int row, int column, FormulaEvaluator formulaEvaluator) {
for (CellRangeAddress range : sheet.getMergedRegions()) {
if (range.isInRange(row, column)) {
Row firstRow = sheet.getRow(range.getFirstRow());
Cell firstCell = firstRow.getCell(range.getFirstColumn());
return getCellStringValue(firstCell, formulaEvaluator);
}
}
return "";
}
/**
* 判断单元格是否是合并单元格
*
* @param sheet 工作表
* @param row 行下标
* @param column 列下标
* @return 是否是合并单元格
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
for (CellRangeAddress range : sheet.getMergedRegions()) {
if (range.isInRange(row, column)) {
return true;
}
}
return false;
}
/**
* 获取单元格的字符串值
*
* @param cell 单元格
* @param formulaEvaluator 公式评估器
* @return 单元格字符串值
*/
private static String getCellStringValue(Cell cell, FormulaEvaluator formulaEvaluator) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
return formulaEvaluator.evaluate(cell).formatAsString();
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
default:
return "";
}
}
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.