前言
最近接到一个需求,需要把一份37万的excel数据解析并导入数据库,
分析
表格格式相对复杂包含很多合并单元格, 不符合通过navicat直接导入的要求, 数据量比较大, 建议一次保存25条,否则会导致内存泄漏;
代码
1.引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
2.具体操作代码
2.1.判断指定的单元格是否为合并单元格
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
//获取该sheet所有合并的单元格
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) {
return true;
}
}
}
return false;
}
2.2.获取合并单元格的值
/**
* 获取合并单元格的值
*
* @param sheet sheet索引 从0开始
* @param row 行索引 从0开始
* @param column 列索引 从0开始
* @return
*/
public static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
2.3.获取单元格的值(不是合并单元格)
/**
* 获取单元格的值 先确定单元格的类型,然后根据类型 取值
*
* @param cell 单元格
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null) return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
2.4.解析完一行数据后,判断对象的属性是否都为空
/**
* 判断一个对象的属性是否都为空,
*
* @param obj 对象
* @return false : 至少有一个属性不为空, true: 该对象的属性全为空
*/
public boolean allFieldIsNULL(Object obj) {
Boolean flag = true;//都为空
if (null == obj || "".equals(obj)) return flag;
try {
Field[] declaredFields = obj.getClass().getDeclaredFields();
for (Field field : declaredFields) { // 循环该类,取出类中的每个属性
field.setAccessible(true);// 把该类中的所有属性设置成 public
Object object = field.get(obj); // object 是对象中的属性
if (object instanceof CharSequence) { // 判断对象中的属性的类型,是否都是CharSequence的子类
if (!Objects.isNull(object)) { // 如果是他的子类,那么就可以用ObjectUtils.isEmpty进行比较
flag = false;//不为空
}
} else { //如果不是那就直接用null判断
if (!(null == object || "".equals(object))) {
flag = false;//不为空
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return flag;//false:不都为空
}
2.5.解析excel的工具类
/**
* 读取excel文件
*
* @param
* @param sheetIndex sheet页下标:从0开始
* @param startReadLine 开始读取的行:从0开始
* @param tailLine 结束行
*/
public void readExcel(String path, int sheetIndex, int startReadLine, int tailLine) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(new File(path));
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
//读取excel表中的sheet, 参数为sheet的索引值(从0开始)
Sheet sheet = wb.getSheetAt(sheetIndex);
Row row = null;
Boolean save = false;
//获取该sheet的最后一行数据的索引
int lastRowNum = sheet.getLastRowNum();
ArrayList<CmsIndexCategory> list = new ArrayList<>();
//外循环是循环行,内循环是循环每行的单元格
for (int i = startReadLine; i <= lastRowNum; i++) {
CmsIndexCategory indexCategory = new CmsIndexCategory();
//根据行索引获取行对象(单元格集合)
row = sheet.getRow(i);
//遍历行的单元格,并解析
for (Cell c : row) {
String returnStr = "";
String trim = "";
//设置该单元格的数据的类型为String
c.setCellType(Cell.CELL_TYPE_STRING);
boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
// 判断是否具有合并单元格
if (isMerge) {
//如果是合并单元格,就获取合并单元格的值
returnStr = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()).toString();
} else {
//不是合并单元格直接获取单元格的值
returnStr = getCellValue(c).toString();
}
if (Objects.nonNull(returnStr) && StringUtils.isNotEmpty(returnStr)) {
trim = returnStr.trim();
//封装结果集,一行数据封装为一个对象
if (c.getColumnIndex() == 0) {
indexCategory.setAgencyBrand(trim);
} else if (c.getColumnIndex() == 1) {
indexCategory.setCompanyName(trim);
} else if (c.getColumnIndex() == 2) {
indexCategory.setIndustryField(trim);
} else if (c.getColumnIndex() == 3) {
indexCategory.setDetectionField(trim);
} else if (c.getColumnIndex() == 4) {
indexCategory.setDetectionObj(trim);
} else if (c.getColumnIndex() == 5) {
indexCategory.setSampleCategory(trim);
} else if (c.getColumnIndex() == 6) {
indexCategory.setDetectionRange(trim);
} else if (c.getColumnIndex() == 7) {
indexCategory.setDetectionItem(trim);
} else if (c.getColumnIndex() == 8) {
indexCategory.setDetectionStandard(trim);
}
}
}
//判断一个对象的属性是否都为空, true:都为空 , false: 不都为空
if (!allFieldIsNULL(indexCategory)) {
//该对象不都为空的情况下,添加到集合中
list.add(indexCategory);
}
//一次保存25条数据,最后一次数据不够25条也进行保存
if (list.size() == 25 || i == lastRowNum) {
save = this.iCmsIndexCategoryService.saveBatch(list);
System.out.println("==================================================第------" + (i + 1) + "---------行保存结果为======================================== " + save);
list.clear();
}
}
}
3.具体实践
@GetMapping("/excel")
public String excel2() {
this.readExcel("C:\\Users\\yangmin\\Desktop\\我要测资料\\特色检测项目\\7.xlsx", 0, 0, 0);
return "完成";
}