直接上代码：
controller层
@ApiOperation(value ="全⾃动导⼊资源和编⽬")
@PostMapping("/autoExcelToSql")
public Response readExcelToList(@RequestPart("file") MultipartFile file) throws IOException, BizException { String filename = OriginalFilename();
Assert.isNull(filename, "⽆法获取⽂件信息");
Assert.isTrue(!ains(".xls"), "上传⽂件类型错误");
// 获取⽂件输⼊流
InputStream inputStream = InputStream();
// 获取excel⼯作簿对象
List<AutoExcelToSqlDTO> tableInfos = new ArrayList<>();
String substring = filename.substring(filename.lastIndexOf("."), filename.length());
}
int ii = i + 1;
//读取当前⾏中单元格数据，索引从0开始
// 序号
double number =0;
Cell(0)!= null){
number = Cell(0).getNumericCellValue();
}
// 资源名称
String resName = null;
Cell(1)!= null){
resName = Cell(1).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(resName), "序号为" + ii + "⾏的数据缺失资源名称参数!");
// 归集状态
String collectionSituation = null;
int collectionSituationInt;
Cell(2)!= null){
collectionSituation = Cell(2).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(collectionSituation), "序号为" + ii + "⾏的数据缺失归集状态参数!");
Assert.isTrue(!collectionSituation.equals("未归集")&&!collectionSituation.equals("已归集"), "序号为" + ii + "⾏的数据归集状态参数格式错误!");
if(collectionSituation.equals("未归集")){
collectionSituationInt =1;
}else{
collectionSituationInt =2;
}
// 更新周期
String updateFrequency = null;
Integer updateFrequencyInt = null;
Cell(3)!= null){
updateFrequency = Cell(3).getStringCellValue();
}
if(StringUtil.isNotBlank(updateFrequency)){
switch (updateFrequency){
case"实时":
updateFrequencyInt =1;
break;
case"每⼩时":
updateFrequencyInt =2;
break;
case"每天":
updateFrequencyInt =3;
break;
case"每⽉":
updateFrequencyInt =4;
break;
case"每季度":
updateFrequencyInt =5;
break;
case"每半年":
updateFrequencyInt =6;
java switch case stringbreak;
case"每年":
updateFrequencyInt =7;
break;
case"不更新":
updateFrequencyInt =8;
break;
case"不定时":
updateFrequencyInt =9;
break;
}
}
// 是否发布(0-未发布1-已发布)
String isRelease = null;
int isReleaseInt =0;
Cell(4)!= null){
isRelease = Cell(4).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(isRelease), "序号为" + ii + "⾏的数据缺失发布状态参数!");
Assert.isTrue(!isRelease.equals("未发布")&&!isRelease.equals("已发布"), "序号为" + ii + "⾏的数据发布状态参数格式错误!");
if(isRelease.equals("已发布")){
isReleaseInt =1;
}
// 共享⽅式
String resPower = null;
int resPowerInt;
Cell(5)!= null){
resPower = Cell(5).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(resPower), "序号为" + ii + "⾏的数据缺失共享⽅式参数!");
Assert.isTrue(!resPower.equals("公开")&&!resPower.equals("受限"), "序号为" + ii + "⾏的数据共享⽅式参数格式错误!");
if(resPower.equals("公开")){
resPowerInt =1;
}else{
resPowerInt =2;
}
// 资源来源(部门名称)
String resGetSource = null;
Cell(6)!= null){
resGetSource = Cell(6).getStringCellValue();
}
// 资源应⽤范围
String resLevel = null;
Integer resLevelInt = null;
Cell(7)!= null){
resLevel = Cell(7).getStringCellValue();
}
if(StringUtil.isNotBlank(resLevel)){
switch (resLevel){
case"省级":
resLevelInt =1;
break;
case"市级":
resLevelInt =2;
break;
case"区级":
resLevelInt =3;
break;
}
}
/
/ 提供部门（id）
double resSourceDeptId =0;
Cell(9)!= null){
resSourceDeptId = Cell(9).getNumericCellValue();
}
Assert.isTrue(resSourceDeptId ==0, "序号为" + ii + "⾏的数据缺失部门id参数!");
// 资源说明
String rdResourceDesc = null;
Cell(10)!= null){
rdResourceDesc = Cell(10).getStringCellValue();
}
// 处理基本信息
AutoExcelToSqlDTO autoExcelToSqlDTO = new AutoExcelToSqlDTO()
.setResName(resName)
.setCollectionSituation(collectionSituationInt)
.setUpdateFrequency(updateFrequencyInt)
.setIsRelease(isReleaseInt)
.setResPower(resPowerInt)
.setResGetSource(resGetSource)
.setResLevel(resLevelInt)
.
setResSourceDeptId((long) resSourceDeptId)
.setRdResourceDesc(rdResourceDesc);
//判断是不是合并⾏
int mergerCellRegionRow = getMergerCellRegionRow(sheetAt, RowNum(),0);
i = i + mergerCellRegionRow -1;
//读取编⽬信息
List<AutoResDataItemDTO> autoResDataItemDTOList = new ArrayList<>();
for (int j = RowNum(); j <= i; j++) {
Row rowItem = Row(j);
String colName = null;
if (Cell(11)!= null) {
colName = Cell(11).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(colName), "第"+ j + "⾏字段中⽂名不可为空!");
String colDesc = null;
if (Cell(12)!= null) {
colDesc = Cell(12).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(colDesc), "第"+ j + "⾏字段英⽂名不可为空!");
Assert.Cell(13), "字段类型不能为空");
String colType = null;
String substring = null;
if (Cell(13)!= null) {
colType = Cell(13).getStringCellValue();
substring = ains("(")? colType.substring(0, colType.lastIndexOf("(")): colType;
}
double colLength =0.0;
Cell(14)!= null){
colLength = Cell(14).getNumericCellValue();
}
double colPrecesion =0.0;
Cell(15)!= null){
colPrecesion = Cell(15).getNumericCellValue();
}
String ifKey = null;
int ifKeyInt;
Cell(16)!= null){
ifKey = Cell(16).getStringCellValue();
}
if("yes".equals(ifKey)){
ifKeyInt =1;
}else{
ifKeyInt =2;
}
AutoResDataItemDTO autoResDataItemDTO = new AutoResDataItemDTO()
.setChineseName(colDesc)
.setEnglishName(colName)
.setFieldType(substring)
.setFieldLength(colLength + "")
.setFieldAccuracy(colPrecesion + "")
.setKeywords(ifKeyInt);
autoResDataItemDTOList.add(autoResDataItemDTO);
}
// 整合参数
autoExcelToSqlDTO.setAutoResDataItemDTOList(autoResDataItemDTOList);
autoExcelToSqlDTOS.add(autoExcelToSqlDTO);
}
}
}
return autoExcelToSqlDTOS;
}
/**
* 判断单元格是否是合并的单格，如果是，获取其合并的⾏数。
*
* @param sheet 表单
* @param cellRow 被判断的单元格的⾏号
* @param cellCol 被判断的单元格的列号
* @return
* @throws IOException
*/
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol){
int retVal =0;
int sheetMergerCount = NumMergedRegions();
for(int i =0; i < sheetMergerCount; i++){
CellRangeAddress cra =(CellRangeAddress) MergedRegion(i);
int firstRow = FirstRow(); // 合并单元格CELL起始⾏
int firstCol = FirstColumn(); // 合并单元格CELL起始列
int lastRow = LastRow(); // 合并单元格CELL结束⾏
int lastCol = LastColumn(); // 合并单元格CELL结束列
if(cellRow >= firstRow && cellRow <= lastRow){ // 判断该单元格是否是在合并单元格中
if(cellCol >= firstCol && cellCol <= lastCol){
retVal = lastRow - firstRow + 1; // 得到合并的⾏数
break;
}
}
}
return retVal;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论