1.Controller
/**
* 导入Excel
* @param cd
* @param req
* @param resp
*/
@PostMapping("/putCarRecords")
@RequiresPermissions("carRecord:putCarRecords")
@ResponseBody
Result<String> putCarRecords(MultipartFile upfile){
String fileName=upfile.getOriginalFilename();
long size=upfile.getSize();
if(StringUtils.isEmpty(fileName)||size==0){
return Result.build(Result.CODE_FAIL, "上传失败");
}
return carRecordService.putCarRecords(fileName, upfile);
}
2.service
@Override
public Result<String> putCarRecords(String name, MultipartFile file) {
Map<String,Long> reasonMap=new HashMap<String,Long>();
Map<String,Long> carModelMap=new HashMap<String,Long>();
List<ReasonDO> reasonList = reasonService.selectList(null);
List<CarModelDO> carList = carModelService.selectList(null);
for(ReasonDO reason:reasonList){
reasonMap.put(reason.getReasonName(), reason.getReasonId());
}
for(CarModelDO carModelDO:carList){
carModelMap.put(carModelDO.getCarModelName(), carModelDO.getCarModelId());
}
//解析excel，获取客户信息集合。
try {
List<CarRecordDO> carRecordDOs = ExcelUtil.getExcelInfo(name, file, reasonMap, carModelMap);
if(carRecordDOs != null){
if(carRecordDOs.size()!=0){
insertBatch(carRecordDOs);
return Result.ok();
}
return Result.build(Result.CODE_FAIL, "请至少上传一条数据!!");
}else{
return Result.build(Result.CODE_FAIL, "插入失败!!");
}
} catch (Exception e) {
e.printStackTrace();
return Result.build(Result.CODE_FAIL, e.getMessage());
}
}
3.ExcelUtil
/**
* 读EXCEL文件，获取客户信息集合
* @param
* @return
*/
public static List<CarRecordDO> getExcelInfo(String fileName, MultipartFile Mfile,Map<String,Long> resonMap,Map<String,Long> carModelMap){
//初始化客户信息的集合
List<CarRecordDO> customerList=new ArrayList<CarRecordDO>();
//初始化输入流
InputStream is = null;
try{
//验证文件名是否合格
if(!validateExcel(fileName)){
return null;
}
//根据文件名判断文件是2003版本还是2007版本
boolean isExcel2003 = true;
if(WDWUtil.isExcel2007(fileName)){
isExcel2003 = false;
}
//根据新建的文件实例化输入流
is = Mfile.getInputStream();
//根据excel里面的内容读取客户信息
customerList = getExcelInfo(is, isExcel2003, resonMap, carModelMap);
is.close();
}catch(Exception e){
throw new RuntimeException(e.getMessage());
} finally{
if(is !=null)
{
try{
is.close();
}catch(IOException e){
is = null;
e.printStackTrace();
}
}
}
return customerList;
}
/**
* 根据excel里面的内容读取客户信息
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
private static List<CarRecordDO> getExcelInfo(InputStream is,boolean isExcel2003,Map<String,Long> resonMap,Map<String,Long> carModelMap){
List<CarRecordDO> customerList=null;
try{
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
//当excel是2003时
if(isExcel2003){
wb = new HSSFWorkbook(is);
}
else{//当excel是2007时
wb = new XSSFWorkbook(is);
}
//读取Excel里面客户的信息
customerList=readExcelValue(wb, resonMap,carModelMap);
}
catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
return customerList;
}
/**
* 读取Excel里面客户的信息
* @param wb
* @return
*/
private static List<CarRecordDO> readExcelValue(Workbook wb,Map<String,Long> resonMap,Map<String,Long> carModelMap){
//得到第一个shell
Sheet sheet=wb.getSheetAt(0);
SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss");
//得到Excel的行数 物理行数
//int totalRows=sheet.getPhysicalNumberOfRows();
int totalRows=sheet.getLastRowNum()+1;
//得到列数
int totalCells=0;
//得到Excel的列数(前提是有行数)
if(totalRows>=1 && sheet.getRow(0) != null){
totalCells=sheet.getRow(6).getPhysicalNumberOfCells();
}
List<CarRecordDO> cardRecords=new ArrayList<CarRecordDO>();
CarRecordDO cardRecordDO;
//循环Excel行数,从第8行开始。标题不入库
for(int r=7;r<totalRows;r++){
Row row = sheet.getRow(r);
if (row == null) continue;
cardRecordDO=new CarRecordDO();
//循环Excel的列
for(int c = 0; c <=totalCells; c++){
Cell cell = row.getCell(c);
switch(c){
case 0:
if(cell==null){
throw new RuntimeException("请填写车主姓名");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String carOwnerName=cell.getStringCellValue();
cardRecordDO.setCarOwnerName(carOwnerName.replace(" ", ""));
break;
case 1:
if(cell==null){
throw new RuntimeException("请填写车主电话");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue = cell.getStringCellValue();
cardRecordDO.setCarOwnerTel(stringCellValue.replace(" ", ""));
break;
case 2:
String stringCellValue2 = cell.getStringCellValue();
if(!StringUtils.isEmpty(stringCellValue2)){
cardRecordDO.setCarNumber(stringCellValue2.replace(" ", ""));
}
break;
case 3:
if(cell==null){
throw new RuntimeException("请填写车型");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue3 = cell.getStringCellValue();
if(carModelMap.containsKey(stringCellValue3)){
cardRecordDO.setCarModelId(carModelMap.get(stringCellValue3.replace(" ", "")));
}else{
throw new RuntimeException("请选择指定车型");
}
break;
case 4:
if(cell==null){
continue;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue4 = cell.getStringCellValue();
if(!StringUtils.isEmpty(stringCellValue4)){
if(resonMap.containsKey(stringCellValue4)){
cardRecordDO.setReasonId(resonMap.get(stringCellValue4.replace(" ", "")));
}else{
throw new RuntimeException("请填写正确进场原因");
}
}
break;
case 5:
if(cell==null){
throw new RuntimeException("请输入进场值班员");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue5 = cell.getStringCellValue();
cardRecordDO.setInUserName(stringCellValue5.replace(" ", ""));
break;
case 6:
if(cell==null){
throw new RuntimeException("请输入进场时间");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue6 = cell.getStringCellValue();
try {
cardRecordDO.setInTime(sdf.parse(stringCellValue6.replace(" ", "")));
} catch (ParseException e) {
throw new RuntimeException("时间格式错误");
}
break;
case 7:
if(cell==null){
continue;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue7 = cell.getStringCellValue();
if(!StringUtils.isEmpty(stringCellValue7)){
try {
cardRecordDO.setOutTime(sdf.parse(stringCellValue7.replace(" ", "")));
} catch (ParseException e) {
throw new RuntimeException("时间格式错误");
}
}
break;
case 8:
if(cell==null){
continue;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue8 = cell.getStringCellValue();
if(!StringUtils.isEmpty(stringCellValue8)){
cardRecordDO.setOutUserName(stringCellValue8.replace(" ", ""));
}
break;
case 9:
if(cell==null){
throw new RuntimeException("请输入受理单位");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String stringCellValue9 = cell.getStringCellValue();
cardRecordDO.setAcceptanceUnit(stringCellValue9);
break;
case 10:
if(cell==null){
throw new RuntimeException("请输入发送机型号");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String engineNumber = cell.getStringCellValue();
cardRecordDO.setEngineNumber(engineNumber);
break;
case 11:
if(cell==null){
throw new RuntimeException("请输入车架号");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String vinNo = cell.getStringCellValue();
cardRecordDO.setVinNo(vinNo);
break;
case 12:
if(cell==null){
throw new RuntimeException("请输入事发地点");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String accidentAddress = cell.getStringCellValue();
cardRecordDO.setAccidentAddress(accidentAddress);
break;
case 13:
if(cell==null){
throw new RuntimeException("请输入停放区域");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String parkedArea = cell.getStringCellValue();
cardRecordDO.setParkedArea(parkedArea);
break;
case 14:
if(cell==null){
throw new RuntimeException("请输入经办人");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String agent = cell.getStringCellValue();
cardRecordDO.setAgent(agent);
break;
case 15:
if(cell==null){
throw new RuntimeException("请选择状态");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String statusStr = cell.getStringCellValue();
if(statusStr.replace(" ", "").equals("进场")){
cardRecordDO.setStatus(0);
}else if(statusStr.replace(" ", "").equals("出场")){
cardRecordDO.setStatus(1);
}else{
throw new RuntimeException("请选择正确状态");
}
break;
}
//添加客户
}
cardRecords.add(cardRecordDO);
}
return cardRecords;
}
4.前端
$(".filebox").on("change","#file",function(){
//得到文件路径
var filePath = $('#file').val();
if(filePath!=""){
//对文件格式进行验证(简单验证)
var d1=/\.[^\.]+$/.exec(filePath);
if(d1==".xls"){
var formData=new FormData();
formData.append("upfile",$("#file")[0].files[0]);
$.ajax({
url:prefix+'/putCarRecords',
type:'POST',
data:formData,
/*
改成contentType为false才会加上正确的ContentType
*/
contentType: false,
/**
* 必须false才会避开jQuery对 formdata 的默认处理
* XMLHttpRequest会对 formdata 进行正确的处理
*/
processData: false,
success: function (data) {
if(data.code==0){
window.location.reload();
}else{
layer.msg(data.msg);
$("#file").val("");
}
},
error:function(){
layer.msg("网络错误!");
$("#file").val("");
}
});
}else{
layer.msg("请选择.xls文件");
}
}else{
layer.msg("请选择.xls文件!");
}
});
