文件上传到后台并在后台读取解析
文件上传的前端比较简单，
html:
<div class="modal-body">
<!--multiple="multiple" 表示可以选多个文件-->
<input type="file" id="file" >
</div>
js:
function uploadFile() { var file=$("#file");
console.log("file",file)
var tmp=file[0].files[0];
if(tmp){
var formdata = new FormData();
formdata.append("file",tmp);
console.log("33",formdata);
$.ajax({
url: "station/org/files",
type: "post",
data:formdata,
dataType: "json",
processData: false, // 告诉jQuery不要去处理发送的数据
contentType: false, // 告诉jQuery不要去设置Content-Type请求头
success: function (res) {
console.log("上传文件返回信息",res);
if("0"==res.status){
layer.alert(res.message||"文件上传成功");
$("#uploadFile").modal("hide");
//清空文件框的显示内容
$("#file").val("");
$("#file")[0].files[0]="";
}else{
layer.alert(res.message||"文件上传失败"
} }
});
}
}
java：
controller:
@PostMapping("files")
@ApiOperation(value="文件上传请求")
public ResultData file(@RequestParam("file") MultipartFile file)throws IOException {
return peOrgService.uploadFile(file);
}
service:
public ResultData uploadFile(MultipartFile file) throws IOException{
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<String[]> list = new ArrayList<String[]>();
StringBuffer sb=new StringBuffer();
try {
log.info("上传文件名",file);
/*检查文件是否传输过来*/
if(null==file||"".equals(file)){
return ResultData.failed(-1,"上传文件为空");
}
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf('.'));
System.out.print("suffix:"+suffix);
if(!(suffix.equals(excel2003L)||suffix.equals(excel2007U))){
return ResultData.failed(-1,"上传文件格式不对，请上传excell文件");
}
/*中文文件名乱码，为文件换新名字，读取后删除该文件，故可以不用理会文件乱码问题*/
String newFileName = new Date().getTime() + suffix;
String path = "F:/test/";
File newFile = new File(path + newFileName);
/*如果父文件夹不存在，则创建*/
File fileParent = newFile.getParentFile();
if (!fileParent.exists()) {
fileParent.mkdirs();
}
/*开始读取excell并插入数据库*/
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){//当前行为空
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
System.out.print("excell的对象:"+list);
/*删除保存在本地的excell*/
newFile.delete();
//list 为整个excell的对象数组
for(int i=0;i<list.size();i++){
String[] str=list.get(i);
if(str.length<8){
int num=i+1;
String st="第"+num+"行信息不正确";
sb.append(st);
continue;
}
AddUserParam param=new AddUserParam();
param.setUserId(str[0]);//工号
param.setRealName(str[1]);//姓名
param.setIdNumber(str[2]);//身份证号
/*通过机构名获取机构号*/
String ogNo= peOrgMapper.getOrgNoByName(str[3]);
param.setOrgId(ogNo);//机构号
param.setPhone(str[4]);//手机号
param.setContinueTime(str[5]);//有效期
param.setUserType(new Integer(str[6]));//用户类型
param.setEmployeeFlag(str[7]);//是否行员
param.setOnduty(str[8]);//在岗状态
// if(checkParam(param)){
// int num1=i+1;
// String str1="表格第"+num1+"行"+error.get();
// sb.append(str1);
// continue;
//// throw new Exception(str1);
// }
log.info("用户批量导入注册:{}",param);
UserInfoRawDTO dto = peUserMapper.getUserInfoRaw(param.getUserId());
if(dto != null && StringUtils.isNotBlank(dto.getUserId())){
UpdateUserParam tempParam = CastUtil.cast(new UpdateUserParam(),param);
this.updateUser(tempParam);
continue;
}
String operator = CurrentUserUtil.getCurrentUserDTO().getUserId();
PeUserLogin record = CastUtil.cast(new PeUserLogin(),param);
record.setPassword(OauthUtil.pass(record.getPassword()));
record.setPassword2(OauthUtil.pass(record.getPassword2()));
record.setPasswordstate(String.valueOf(PeUserLoginEnum.PASSWORDSTATE_INIT.code));
record.setStatus(StatusEnum.NORMAL.code);
record.setEnable(StatusEnum.YES.code);
record.setCreator(operator);
record.setCreateTime(new Date());
int result = peUserMapper.add(record);
if(result > 0) continue;
}
} catch (Exception e) {
e.printStackTrace();
e.getMessage();
return ResultData.failed(-1,"aaaaa");
}finally {
return ResultData.ok(RetCode.OK,sb.toString());
}
}
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.getMessage();
}
return workbook;
}
@CheckParams
@Transactional(rollbackFor = Exception.class)
public ResultData updateUser(UpdateUserParam param){
log.info("修改用户信息:{}",param);
String operator = CurrentUserUtil.getCurrentUserDTO().getUserId();
PeUserLogin record = CastUtil.cast(new PeUserLogin(),param);
record.setPasswordstate(String.valueOf(PeUserLoginEnum.PASSWORDSTATE_INIT.code));
record.setLastUpdUser(operator);
record.setLastUpdDatetime(new Date());
int result = peUserMapper.update(record);
if(result > 0) return ResultData.ok();
return ResultData.failed(RetCode.FAILED);
}
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把数字当成String来读，避免出现1读成1.0的情况
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
