前端代码
< 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 ( ) {
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>
后端代码
依赖
< 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) {
List< WcmsBaseInfo> wcmsMaterialInfoList = ExcelReader. readExcelToGetWcmsMaterialInfo ( file) ;
List< ChemicalComposition> chemicalCompositionList = ExcelReader. readExcelToGetChemicalComposition ( file) ;
. . . . . .
}
return RestResponse. success ( ) . put ( "message" , "上传成功" ) ;
} else {
return RestResponse. error ( "文件上传失败" ) ;
}
}
}
ExcelReader
public class ExcelReader {
private final static Logger logger = LoggerFactory. getLogger ( ExcelReader. class ) ;
private static final String XLS = "xls" ;
private static final String XLSX = "xlsx" ;
public static List< WcmsMaterialInfo> readExcelToGetWcmsMaterialInfo ( MultipartFile file) {
List< WcmsMaterialInfo> wcmsMaterialInfoList;
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file. getOriginalFilename ( ) ;
try {
String fileType = fileName. substring ( fileName. lastIndexOf ( "." ) + 1 ) ;
File excelFile = transferToFile ( file) ;
if ( ! excelFile. exists ( ) ) {
logger. info ( "指定的Excel文件不存在!" ) ;
return null;
}
inputStream = new FileInputStream ( excelFile) ;
workbook = getWorkbook ( inputStream, fileType) ;
List< Integer> mergeRowNumList = ExcelReader. getMergeRowNumList ( file) ;
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;
}
}
}
public static List< ChemicalComposition> readExcelToGetChemicalComposition ( MultipartFile file) {
List< ChemicalComposition> chemicalCompositionList;
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file. getOriginalFilename ( ) ;
try {
String fileType = fileName. substring ( fileName. lastIndexOf ( "." ) + 1 ) ;
File excelFile = transferToFile ( file) ;
if ( ! excelFile. exists ( ) ) {
logger. info ( "指定的Excel文件不存在!" ) ;
return null;
}
inputStream = new FileInputStream ( excelFile) ;
workbook = getWorkbook ( inputStream, fileType) ;
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;
}
}
}
private static WcmsMaterialInfo convertRowDataToMaterialInfo ( Row row, int startCellNum) {
WcmsMaterialInfo wcmsMaterialInfo = new WcmsMaterialInfo ( ) ;
Cell cell;
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;
}
private static ChemicalComposition convertRowDataToChemicalComposition ( Row row, int startCellNum) {
ChemicalComposition chemicalComposition = new ChemicalComposition ( ) ;
Cell cell;
int cellNum = startCellNum;
cell = row. getCell ( cellNum++ ) ;
String c = convertCellValueToString ( cell) ;
chemicalComposition. setC ( c) ;
cell = row. getCell ( cellNum++ ) ;
String n = convertCellValueToString ( cell) ;
chemicalComposition. setN ( n) ;
cell = row. getCell ( cellNum++ ) ;
String o = convertCellValueToString ( cell) ;
chemicalComposition. setO ( o) ;
return chemicalComposition;
}
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;
}
private static File transferToFile ( MultipartFile multipartFile) {
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;
}
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;
}
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;
}
public static List< Integer> getMergeRowNumList ( MultipartFile file) {
List< Integer> result = new ArrayList < > ( ) ;
Workbook workbook = null;
FileInputStream inputStream = null;
String fileName = file. getOriginalFilename ( ) ;
try {
String fileType = fileName. substring ( fileName. lastIndexOf ( "." ) + 1 ) ;
File excelFile = transferToFile ( file) ;
if ( ! excelFile. exists ( ) ) {
logger. info ( "指定的Excel文件不存在!" ) ;
return null;
}
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;
}
}
}
}