需求：根据excel模板导入数据
sheet1：1-6行为固定格式，且需要取值({xxx});7行开始为数据集合(list)
sheet2：都为固定格式，取值地方：{xxx}
1、数据格式（两个Sheet）
2、代码
public static EngineerTunnelMonitorExcelVo importExcel(MultipartFile file){
if(file.isEmpty()){
return null;
}
EngineerTunnelMonitorExcelVo result=new EngineerTunnelMonitorExcelVo();
//获取excel对象
Workbook workbook= null;
try {
//获取excel
workbook = new XSSFWorkbook(file.getInputStream());
//获取工作簿
/************************************主表**************************************/
Sheet sheetAt = workbook.getSheetAt(0);
//标题
String titl = getMergedRegionValue(sheetAt, 1, 1);
if(null!=titl && !"".equals(titl) && titl.contains("周边")){
result.setTestPart(2);
}else{
result.setTestPart(1);
}
//项目名称
result.setPointName(getMergedRegionValue(sheetAt,2 , 3));
//施工单位名称
result.setConstructionName(getMergedRegionValue(sheetAt, 2, 7));
//围岩级别
result.setWallRockLevel(getMergedRegionValue(sheetAt, 2, 10));
//断面里程
result.setFractureSurfaceMileage(getMergedRegionValue(sheetAt, 2, 13));
//建立日期
result.setBuildTime(getDataForExcel(getMergedRegionValue(sheetAt, 3,3)));
//施工方法
result.setConstructMethod(getMergedRegionValue(sheetAt, 3, 7));
//观测方法
result.setObservationMethod(getMergedRegionValue(sheetAt, 3, 10));
//测点编号
result.setTestCode(getMergedRegionValue(sheetAt, 3, 13));
/************************************记录表子表**************************************/
Integer rows=6;
Integer index=1;
ArrayList<EngineerTunnelMonitorRecordForRimExcelVo> list=new ArrayList<>();
while (true){
if(null==sheetAt.getRow(rows)){
break;
}
EngineerTunnelMonitorRecordForRimExcelVo engineerTunnelMonitorRecordForRimExcelVo=new EngineerTunnelMonitorRecordForRimExcelVo();
engineerTunnelMonitorRecordForRimExcelVo.setTestTime(getDataForExcel(getCellData(sheetAt.getRow(rows).getCell(index))))
.setTestTemp(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setSpaceTime(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setSpaceFirstTime(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setMeasureOne(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setMeasureTwo(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setMeasureThree(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setAvgValue(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setLastDown(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setFirstDown(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setDownRate(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setSpaceFace(getCellData(sheetAt.getRow(rows).getCell(++index)))
.setManageLevel(getCellData(sheetAt.getRow(rows).getCell(++index)));
list.add(engineerTunnelMonitorRecordForRimExcelVo);
index=1;
rows++;
}
/************************************分析表子表**************************************/
Sheet sheetTwo = workbook.getSheetAt(1);
EngineerTunnelMonitorAnalyseExcelVo engineerTunnelMonitorAnalyse = new EngineerTunnelMonitorAnalyseExcelVo();
engineerTunnelMonitorAnalyse.setName(getMergedRegionValue(sheetTwo, 2, 3))
.setConstructionName(getMergedRegionValue(sheetTwo, 2, 7))
.setFractureSurfaceMileage(getMergedRegionValue(sheetTwo, 2, 10))
.setTestPart(getMergedRegionValue(sheetTwo, 3,3))
.setWallRockLevel(getMergedRegionValue(sheetTwo, 3, 7))
.setTestCode(getMergedRegionValue(sheetTwo, 3, 10))
.setFirstTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 3)))
.setLastTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 7)))
.setDataTaskTime(getDataForExcel(getMergedRegionValue(sheetTwo, 4, 10)))
.setRegressionEquation(getMergedRegionValue(sheetTwo, 5, 3))
.setCoefficientAssociation(getMergedRegionValue(sheetTwo, 5, 9))
.setDisplacementLevel(getMergedRegionValue(sheetTwo, 6, 3)+";"+getMergedRegionValue(sheetTwo, 8, 3))
.setRate(getMergedRegionValue(sheetTwo, 10, 3))
.setConclusion(getMergedRegionValue(sheetTwo, 13, 3));
result.setEngineerTunnelMonitorRecordList(list);
result.setEngineerTunnelMonitorAnalyse(engineerTunnelMonitorAnalyse);
} catch (IOException e) {
throw new RuntimeException(e);
}
return result;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @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 getCellData(fCell);
}
}
}
return null ;
}
//获取单元格内容
public static String getCellData(Cell cell){
String value="";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
case FORMULA:value = "" + cell.getCellFormula();
break;
case NUMERIC:value = "" + cell.getNumericCellValue();
break;
case STRING:value = cell.getStringCellValue();
break;
default:
break;
}
return value;
}
//获取日期
public static Date getDataForExcel(String val){
try {
if(StringUtils.isEmpty(val)){
return null;
}
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val);
} catch (Exception e) {
double excelDate = Double.valueOf(val); // 示例 Excel 日期和时间数值
long baseMillis = -2209017600000L; // 1900 年 1 月 1 日的毫秒数（注意 Excel 的 1900 年闰年错误，减去两天）
// 计算日期和时间的毫秒数
long millis = baseMillis + (long) (excelDate * 24 * 60 * 60 * 1000) - 172800000;
// 创建 Date 对象
return new Date(millis);
}
}
