前端代碼
<template>
<div>
<el-upload
class="upload"
ref="doUpload"
:action="uploadUrl"
:on-preview="beforeUploadHandle"
:on-success="successHandle"
:limit="1"
accept=".xls, .xlsx"
:file-list="fileList">
<el-button slot="trigger" size="small" type="primary" @click="submitUpload">導入</el-button>
<div slot="tip" class="el-upload__tip">隻能上傳excel檔案</div>
</el-upload>
</div>
</template>
<script>
export default {
data () {
return {
uploadUrl: '',
fileList: []
}
},
methods: {
// 上傳之前
beforeUploadHandle (file) {
console.log('fileType', file.type)
},
// 上傳成功
successHandle (response, file, fileList) {
this.fileList = fileList
},
// 上傳檔案
submitUpload () {
// storeType [1, 4, 5, 6, 7] [标準庫, 實驗庫, 動态庫, 競品庫, 仿真庫]
let storeType = '1'
this.uploadUrl = this.$http.BASE_URL + `/doImport/importAll?token=${this.$cookie.get('token')}&storeType=${storeType}`
this.$nextTick(() => {
this.$refs.doUpload.submit()
})
}
}
}
</script>
後端代碼
依賴
<!-- Apache POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
DoImportController
@RestController
@RequestMapping("doImport")
public class DoImportController extends AbstractController {
@Autowired
private WcmsBaseInfoService wcmsBaseInfoService;
@Autowired
private WcmsChemicalCompositionService wcmsChemicalCompositionService;
@RequestMapping("/importAll")
@Transactional
public RestResponse importAll(@RequestParam(value = "file", required = false) MultipartFile file, @RequestParam(value = "storeType", required = false) String storeType) {
if (file != null) {
// 讀取Excel檔案中主表内容
List<WcmsBaseInfo> wcmsMaterialInfoList = ExcelReader.readExcelToGetWcmsMaterialInfo(file);
// 讀取Excel檔案中所有化學元素内容
List<ChemicalComposition> chemicalCompositionList = ExcelReader.readExcelToGetChemicalComposition(file);
// 擷取到資料後執行相應插入資料庫操作
......
}
return RestResponse.success().put("message", "上傳成功");
} else {
return RestResponse.error("檔案上傳失敗");
}
}
}
ExcelReader
/**
* @ClassName ExcelReader
* @Description 讀取Excel檔案中的各個實體資訊
* @Author F
* @Version 1.0
*/
public class ExcelReader {
private final static Logger logger = LoggerFactory.getLogger(ExcelReader.class);
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* @param file 檔案
* @return 讀取結果清單，讀取失敗時傳回null
* @description 讀取Excel檔案中主表資訊
*/
public static List<WcmsMaterialInfo> readExcelToGetWcmsMaterialInfo(MultipartFile file){
List<WcmsMaterialInfo> wcmsMaterialInfoList;
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file.getOriginalFilename();
try {
// 擷取Excel字尾名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
// 擷取Excel檔案
File excelFile = transferToFile(file);
if (!excelFile.exists()) {
logger.info("指定的Excel檔案不存在！");
return null;
}
// 擷取Excel工作簿
inputStream = new FileInputStream(excelFile);
workbook = getWorkbook(inputStream, fileType);
// 讀取Excel第五行之後的合并行數
List<Integer> mergeRowNumList = ExcelReader.getMergeRowNumList(file);
// 讀取excel中主表的資料
wcmsMaterialInfoList = parseExcelToGetMaterialInfo(workbook, 1, mergeRowNumList);
return wcmsMaterialInfoList;
} catch (Exception e) {
logger.info("解析Excel失敗，檔案名：" + fileName + " 錯誤資訊：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
} catch (Exception e) {
logger.info("關閉資料流出錯！錯誤資訊：" + e.getMessage());
return null;
}
}
}
/**
* @param file 檔案
* @return 讀取結果清單，讀取失敗時傳回null
* @description 讀取Excel檔案中化學成分資訊
*/
public static List<ChemicalComposition> readExcelToGetChemicalComposition(MultipartFile file) {
List<ChemicalComposition> chemicalCompositionList;
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file.getOriginalFilename();
try {
// 擷取Excel字尾名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
// 擷取Excel檔案
File excelFile = transferToFile(file);
if (!excelFile.exists()) {
logger.info("指定的Excel檔案不存在！");
return null;
}
// 擷取Excel工作簿
inputStream = new FileInputStream(excelFile);
workbook = getWorkbook(inputStream, fileType);
// 讀取excel中化學成分資料
chemicalCompositionList = parseExcelToGetChemicalComposition(workbook, 50);
return chemicalCompositionList;
} catch (Exception e) {
logger.info("解析Excel失敗，檔案名：" + fileName + " 錯誤資訊：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
} catch (Exception e) {
logger.info("關閉資料流出錯！錯誤資訊：" + e.getMessage());
return null;
}
}
}
/**
* @param row 行資料
* @return 解析後的行資料對象，行資料錯誤時傳回null
* @description 提取每一行中主表的資料，構造成為一個結果資料對象
* @note 當該行中有單元格的資料為空或不合法時，忽略該行的資料
* @note 主表資料1-3列
**/
private static WcmsMaterialInfo convertRowDataToMaterialInfo(Row row, int startCellNum) {
WcmsMaterialInfo wcmsMaterialInfo = new WcmsMaterialInfo();
Cell cell;
// 從1開始 去掉excel開始的序号
int cellNum = startCellNum;
// 材料名稱(中文)
cell = row.getCell(cellNum++);
String materialCategoryC = convertCellValueToString(cell);
wcmsMaterialInfo.setMaterialCategoryC(materialCategoryC);
// 材料名稱(英文)
cell = row.getCell(cellNum++);
String mMaterialCategoryE = convertCellValueToString(cell);
wcmsMaterialInfo.setMaterialCategoryE(mMaterialCategoryE);
// 材料牌号
cell = row.getCell(cellNum++);
String mMaterialNum = convertCellValueToString(cell);
wcmsMaterialInfo.setMaterialNum(mMaterialNum);
return wcmsMaterialInfo;
}
/**
* @param row 行資料
* @return 解析後的行資料對象，行資料錯誤時傳回null
* @Description 提取每一行中化學成分資料，構造成為一個結果資料對象
* @note 化學成分資料偶從4-7列
*/
private static ChemicalComposition convertRowDataToChemicalComposition(Row row, int startCellNum) {
ChemicalComposition chemicalComposition = new ChemicalComposition();
Cell cell;
// 從 4開始 模闆設定4以後是化學成分資料
int cellNum = startCellNum;
// C 碳
cell = row.getCell(cellNum++);
String c = convertCellValueToString(cell);
chemicalComposition.setC(c);
// N 氮
cell = row.getCell(cellNum++);
String n = convertCellValueToString(cell);
chemicalComposition.setN(n);
// O 氧
cell = row.getCell(cellNum++);
String o = convertCellValueToString(cell);
chemicalComposition.setO(o);
return chemicalComposition;
}
/**
* @param cell 單元格
* @return 内容
* @description 将單元格内容轉換為字元串
*/
private static String convertCellValueToString(Cell cell) {
if (cell == null) {
return null;
}
String returnValue = null;
switch (cell.getCellType()) {
// 數字
case NUMERIC:
Double doubleValue = cell.getNumericCellValue();
returnValue = doubleValue.toString();
break;
// 字元串
case STRING:
returnValue = cell.getStringCellValue();
break;
// 布爾
case BOOLEAN:
Boolean booleanValue = cell.getBooleanCellValue();
returnValue = booleanValue.toString();
break;
// 空值
case BLANK:
break;
// 公式
case FORMULA:
returnValue = cell.getCellFormula();
break;
// 故障
case ERROR:
break;
default:
break;
}
return returnValue;
}
/**
* @param multipartFile 原本檔案類型
* @return File
* @description 轉換檔案類型
*/
private static File transferToFile(MultipartFile multipartFile) {
// 選擇用緩沖區來實作這個轉換即使用java 建立的臨時檔案 使用 MultipartFile.transferto()方法 。
File resultFile = null;
try {
String originalFileName = multipartFile.getOriginalFilename();
String[] fileName = originalFileName.split("\\.");
if (fileName.length > 1) {
resultFile = File.createTempFile(fileName[0], fileName[1]);
multipartFile.transferTo(resultFile);
resultFile.deleteOnExit();
}
} catch (IOException e) {
e.printStackTrace();
}
return resultFile;
}
/**
* @param inputStream 讀取檔案的輸入流
* @param fileType 檔案字尾名類型（xls或xlsx）
* @return 包含檔案資料的工作簿對象
* @throws IOException
* @description 根據檔案字尾名類型擷取對應的工作簿對象
*/
private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
/**
* @param cell 目前單元格
* @param sheet 工作sheet
* @return 合并的行數
* @decription 擷取合并行數
*/
public static int getMergeRowNum(Cell cell, Sheet sheet) {
int mergeSize = 1;
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
for (CellRangeAddress cellRangeAddress : mergedRegions) {
if (cellRangeAddress.isInRange(cell)) {
//擷取合并的行數
mergeSize = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1;
break;
}
}
return mergeSize;
}
/**
* @param file 檔案
* @return 傳回合并行數清單
* @decription 擷取合并行數清單
*/
public static List<Integer> getMergeRowNumList(MultipartFile file) {
// 解析sheet workbook.getNumberOfSheets()擷取sheet頁數 隻有一個sheet頁
List<Integer> result = new ArrayList<>();
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file.getOriginalFilename();
try {
// 擷取Excel字尾名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
// 擷取Excel檔案
File excelFile = transferToFile(file);
if (!excelFile.exists()) {
logger.info("指定的Excel檔案不存在！");
return null;
}
// 擷取Excel工作簿
inputStream = new FileInputStream(excelFile);
workbook = getWorkbook(inputStream, fileType);
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return result;
}
// 模闆設定從第五行資料
int rowStart = sheet.getFirstRowNum() + 5;
int rowEnd = sheet.getPhysicalNumberOfRows();
for (int rowNum = rowStart; rowNum < rowEnd; ) {
Row row = sheet.getRow(rowNum);
int physicalNumberOfRows = getMergeRowNum(row.getCell(0), sheet);
// 合并行數
result.add(physicalNumberOfRows);
rowNum += physicalNumberOfRows;
}
return result;
} catch (Exception e) {
logger.info("解析Excel失敗，檔案名：" + fileName + " 錯誤資訊：" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != inputStream) {
inputStream.close();
}
} catch (Exception e) {
logger.info("關閉資料流出錯！錯誤資訊：" + e.getMessage());
return null;
}
}
}
}
![Android的前後端互動架構搭建（okhhtp+springboot+mysql完整版）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)