读取的问价格式如下：
直接上代码：
controller层
@ApiOperation(value = "全自动导入资源和编目")
@PostMapping("/autoExcelToSql")
public Response readExcelToList(@RequestPart("file") MultipartFile file) throws IOException, BizException {
String filename = file.getOriginalFilename();
Assert.isNull(filename, "无法获取文件信息");
Assert.isTrue(!filename.contains(".xls"), "上传文件类型错误");
// 获取文件输入流
InputStream inputStream = file.getInputStream();
// 获取excel工作簿对象
List<AutoExcelToSqlDTO> tableInfos = new ArrayList<>();
String substring = filename.substring(filename.lastIndexOf("."), filename.length());
if (".xls".equals(substring)) {
HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
tableInfos = rdResourceDirService.excelToList(workbook);
} else if (".xlsx".equals(substring)) {
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
//tableInfos = excelxToList(workbook);
}
// 关闭输入流
log.info("tableInfos=" + tableInfos);
inputStream.close();
return Response.success(tableInfos);
}
service层
/**
* 文件是.xls
*
* @param workbook
* @return
*/
@Override
public List<AutoExcelToSqlDTO> excelToList(HSSFWorkbook workbook) throws BizException {
// 得到excel工作表对象
HSSFSheet sheetAt = workbook.getSheetAt(0);
List<AutoExcelToSqlDTO> autoExcelToSqlDTOS = new ArrayList<>();
// 负载行数
for (int i = 0; i < sheetAt.getLastRowNum() + 1; i++) {
// 获取行数
Row row = sheetAt.getRow(i);
//首行（即表头）不读取
if (row != null) {
if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2) {
continue;
}
int ii = i + 1;
//读取当前行中单元格数据，索引从0开始
// 序号
double number = 0;
if (row.getCell(0) != null) {
row.getCell(0).setCellType(NUMERIC);
number = row.getCell(0).getNumericCellValue();
}
// 资源名称
String resName = null;
if (row.getCell(1) != null) {
row.getCell(1).setCellType(STRING);
resName = row.getCell(1).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(resName), "序号为" + ii + "行的数据缺失资源名称参数!");
// 归集状态
String collectionSituation = null;
int collectionSituationInt;
if (row.getCell(2) != null) {
row.getCell(2).setCellType(STRING);
collectionSituation = row.getCell(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;
if (row.getCell(3) != null) {
row.getCell(3).setCellType(STRING);
updateFrequency = row.getCell(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;
break;
case "每年":
updateFrequencyInt = 7;
break;
case "不更新":
updateFrequencyInt = 8;
break;
case "不定时":
updateFrequencyInt = 9;
break;
}
}
// 是否发布(0-未发布 1-已发布)
String isRelease = null;
int isReleaseInt = 0;
if (row.getCell(4) != null) {
row.getCell(4).setCellType(STRING);
isRelease = row.getCell(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;
if (row.getCell(5) != null) {
row.getCell(5).setCellType(STRING);
resPower = row.getCell(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;
if (row.getCell(6) != null) {
row.getCell(6).setCellType(STRING);
resGetSource = row.getCell(6).getStringCellValue();
}
// 资源应用范围
String resLevel = null;
Integer resLevelInt = null;
if (row.getCell(7) != null) {
row.getCell(7).setCellType(STRING);
resLevel = row.getCell(7).getStringCellValue();
}
if (StringUtil.isNotBlank(resLevel)) {
switch (resLevel) {
case "省级":
resLevelInt = 1;
break;
case "市级":
resLevelInt = 2;
break;
case "区级":
resLevelInt = 3;
break;
}
}
// 提供部门（id）
double resSourceDeptId = 0;
if (row.getCell(9) != null) {
row.getCell(9).setCellType(NUMERIC);
resSourceDeptId = row.getCell(9).getNumericCellValue();
}
Assert.isTrue(resSourceDeptId == 0, "序号为" + ii + "行的数据缺失部门id参数!");
// 资源说明
String rdResourceDesc = null;
if (row.getCell(10) != null) {
row.getCell(10).setCellType(STRING);
rdResourceDesc = row.getCell(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, row.getRowNum(), 0);
i = i + mergerCellRegionRow - 1;
// 读取编目信息
List<AutoResDataItemDTO> autoResDataItemDTOList = new ArrayList<>();
for (int j = row.getRowNum(); j <= i; j++) {
Row rowItem = sheetAt.getRow(j);
String colName = null;
if (rowItem.getCell(11) != null) {
colName = rowItem.getCell(11).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(colName), "第"+ j + "行字段中文名不可为空!");
String colDesc = null;
if (rowItem.getCell(12) != null) {
colDesc = rowItem.getCell(12).getStringCellValue();
}
Assert.isTrue(StringUtil.isBlank(colDesc), "第"+ j + "行字段英文名不可为空!");
Assert.isNull(rowItem.getCell(13), "字段类型不能为空");
String colType = null;
String substring = null;
if (rowItem.getCell(13) != null) {
colType = rowItem.getCell(13).getStringCellValue();
substring = colType.contains("(") ? colType.substring(0, colType.lastIndexOf("(")) : colType;
}
double colLength = 0.0;
if (rowItem.getCell(14) != null) {
colLength = rowItem.getCell(14).getNumericCellValue();
}
double colPrecesion = 0.0;
if (rowItem.getCell(15) != null) {
colPrecesion = rowItem.getCell(15).getNumericCellValue();
}
String ifKey = null;
int ifKeyInt;
if (rowItem.getCell(16) != null) {
ifKey = rowItem.getCell(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 = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
if (cellCol >= firstCol && cellCol <= lastCol) {
retVal = lastRow - firstRow + 1; // 得到合并的行数
break;
}
}
}
return retVal;
}
******优化字段获取写法
/**
* 文件是.xlsx
*
* @param workbook
* @return
*/
@Override
public List<AutoExcelToSqlDTO> excelxToList(XSSFWorkbook workbook) throws BizException {
// 得到excel工作表对象
XSSFSheet sheetAt = workbook.getSheetAt(0);
List<AutoExcelToSqlDTO> autoExcelToSqlDTOS = new ArrayList<>();
// 负载行数
for (int i = 0; i < sheetAt.getLastRowNum() + 1; i++) {
// 获取行数
Row row = sheetAt.getRow(i);
//首行（即表头）不读取
if (row != null) {
if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2) {
continue;
}
int ii = i + 1;
//读取当前行中单元格数据，索引从0开始
// 序号
// double number = getDoubleCellValue(row.getCell(0));
// if (row.getCell(0) != null) {
// row.getCell(0).setCellType(NUMERIC);
// number = row.getCell(0).getNumericCellValue();
// }
// 资源名称
String resName = getStringCellValue(row.getCell(1));
Assert.isBlank(resName, "序号为" + ii + "行的数据缺失资源名称参数!");
// 归集状态
String collectionSituation = getStringCellValue(row.getCell(2));
int collectionSituationInt;
Assert.isBlank(collectionSituation, "序号为" + ii + "行的数据缺失归集状态参数!");
Assert.isTrue(!collectionSituation.equals("未归集") && !collectionSituation.equals("已归集"), "序号为" + ii + "行的数据归集状态参数格式错误!");
if (collectionSituation.equals("已归集")) {
collectionSituationInt = 1;
} else {
collectionSituationInt = 2;
}
// 更新周期
String updateFrequency = getStringCellValue(row.getCell(3));
Integer updateFrequencyInt = null;
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;
break;
case "每年":
updateFrequencyInt = 7;
break;
case "不更新":
updateFrequencyInt = 8;
break;
case "不定时":
updateFrequencyInt = 9;
break;
default:
updateFrequencyInt = null;
}
}
// 是否发布(0-未发布 1-已发布)
String isRelease = getStringCellValue(row.getCell(4));
int isReleaseInt = 0;
Assert.isBlank(isRelease, "序号为" + ii + "行的数据缺失资源状态参数!");
Assert.isTrue(!isRelease.equals("未发布") && !isRelease.equals("已发布"), "序号为" + ii + "行的数据发布状态参数格式错误!");
if (isRelease.equals("已发布")) {
isReleaseInt = 1;
}
// 共享方式
String resPower = getStringCellValue(row.getCell(5));
int resPowerInt;
Assert.isBlank(resPower, "序号为" + ii + "行的数据缺失共享方式参数!");
Assert.isTrue(!resPower.equals("公开") && !resPower.equals("受限"), "序号为" + ii + "行的数据共享方式参数格式错误!");
if (resPower.equals("公开")) {
resPowerInt = 1;
} else {
resPowerInt = 2;
}
// 资源来源(部门名称)
String resGetSource = getStringCellValue(row.getCell(6));
// 资源应用范围
String resLevel = getStringCellValue(row.getCell(7));
Integer resLevelInt = null;
if (StringUtil.isNotBlank(resLevel)) {
switch (resLevel) {
case "省级":
resLevelInt = 1;
break;
case "市级":
resLevelInt = 2;
break;
case "区级":
resLevelInt = 3;
break;
default:
resLevelInt = null;
}
}
// 提供部门（id）
double resSourceDeptId = getDoubleCellValue(row.getCell(9));
Assert.isTrue((int) resSourceDeptId == 0, "序号为" + ii + "行的数据缺失部门id参数!");
// 资源说明
String rdResourceDesc = getStringCellValue(row.getCell(10));
// 处理基本信息
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 = getMergerCellRegionRowx(sheetAt, row.getRowNum(), 0);
// 如果是合并行
if (mergerCellRegionRow > 0) {
i = i + mergerCellRegionRow - 1;
}
// 读取编目信息
List<AutoResDataItemDTO> autoResDataItemDTOList = new ArrayList<>();
for (int j = row.getRowNum(); j <= i; j++) {
Row rowItem = sheetAt.getRow(j);
// 英文名称
String colDesc = getStringCellValue(rowItem.getCell(12));
// 如果英文字段不是空，则说明这行有编目信息需要导入
if (StringUtil.isNotBlank(colDesc)) {
String colName = getStringCellValue(rowItem.getCell(11));
// 字段类型
String colType = getStringCellValue(rowItem.getCell(13));
Assert.isBlank(colType, "字段类型不能为空，行" + (j + 1));
String substring = colType.contains("(") ? colType.substring(0, colType.lastIndexOf("(")) : colType;
// 字段长度
double colLength = getDoubleCellValue(rowItem.getCell(14));
// 字段精度
double colPrecesion = getDoubleCellValue(rowItem.getCell(15));
String ifKey = getStringCellValue(rowItem.getCell(16));
int ifKeyInt;
if ("yes".equals(ifKey)) {
ifKeyInt = 1;
} else {
ifKeyInt = 2;
}
AutoResDataItemDTO autoResDataItemDTO = new AutoResDataItemDTO()
.setChineseName(colName)
.setEnglishName(colDesc)
.setFieldType(substring)
.setFieldLength(colLength + "")
.setFieldAccuracy(colPrecesion + "")
.setKeywords(ifKeyInt);
autoResDataItemDTOList.add(autoResDataItemDTO);
// 整合参数
autoExcelToSqlDTO.setAutoResDataItemDTOList(autoResDataItemDTOList);
}
}
autoExcelToSqlDTOS.add(autoExcelToSqlDTO);
}
}
return autoExcelToSqlDTOS;
}
扫一扫
561
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
