html页面
<span class="export cur" οnclick="testSendTosFile.click()"><img src="../res/web/img//index/tj.png" alt=""> 导入</span>
<input type="file" id="testSendTosFile" name="testSendTosFile" οnchange="importTaxFile()" style="visibility: hidden; position: absolute;" />
js
function importFile(){
loading(function () {
//判断文件上传类型
//文件类型
var filetypes = [".xls",".xlsx"];
var filepath = document.getElementById("testSendTosFile").value;
if(BaseUtil.isBlank(filepath))
return false;
var isnext = false;
var fileend = filepath.substring(filepath.lastIndexOf("."));
if (filetypes && filetypes.length > 0) {
for (var i = 0; i < filetypes.length; i++) {
if (filetypes[i] == fileend) {
isnext = true;
break;
}
}
}
if (!isnext) {
BaseUtil.showMsg('不接受此文件类型,请上传xls/xlsx格式的文件!');
document.getElementById('testSendTosFile').value = null;
return false;
}
//判断上传文件的大小
// var files = document.getElementById("testSendTosFile").files[0].size;
// var size = files / 1024;
// var filemaxsize = 1024 * 1;//1M
// if (size > filemaxsize) {
// BaseUtil.showMsg('附件大小不能大于' + filemaxsize / 1024 +'M!\r\n');
// document.getElementById('testSendTosFile').value = null;
// return false;
// }
BaseUtil.upload("./testImport","testSendTosFile",function (response) {
document.getElementById('testSendTosFile').value = null;
var result = response.body.data.result;
var flag = response.body.data.flag;//错误行记录数据
var bufferMon = response.body.data.bufferMon;错误行金额记录数据
if(BaseUtil.isNotBlank(result)){
BaseUtil.showMsg(result+(BaseUtil.isNotBlank(flag)?flag+"数据不存在!":"")+(BaseUtil.isNotBlank(bufferMon)?"\n其中!第"+bufferMon+"行金额存在非法类型":""));
}
page.initPage();
});
});
}
后台代码
@RequestMapping(value = "/testImport", method = RequestMethod.POST)
@ResponseBody
public Resp importTempExecInfo(@RequestParam MultipartFile file) throws IOException {
if (file == null) {
return Resp.succResp(request, RespMap.getInstance("result", "上传文件不能为空").build());
}
//修改数据
List<FcTestVo> fcTestVos = new ArrayList<>();
StringBuffer buffer = new StringBuffer();//返回错误数据用
StringBuffer bufferMon = new StringBuffer();//返回错误数据用
StringBuffer bufferExit = new StringBuffer();//返回错误数据用
InputStream is = null;
try {
boolean isExcel2003 = true;
if (file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet.getLastRowNum() < 1) {
return Resp.succResp(request, RespMap.getInstance("result", "文件内容不符,请下载模板文件").add("flag", "").build());
}
//行循环
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
//当前这行全部为空或者空格就忽视当前这行
if (isAllRowEmpty(row, sheet.getRow(1))) {
continue;
}
//取出excel每个单元格的值
String cell0 = getCellVal(row.getCell(0));
String cell1 = getCellVal(row.getCell(1));
String cell2 = getCellVal(row.getCell(2));
String cell3 = getCellVal(row.getCell(3));
String cell4 = getCellVal(row.getCell(4));
String cell5 = getCellVal(row.getCell(5));
String cell6 = getCellVal(row.getCell(6));
String cell7 = getCellVal(row.getCell(7));
String cell8 = getCellVal(row.getCell(8));
String cell9 = getCellVal(row.getCell(9));
String cell10 = getCellVal(row.getCell(10));
FcTestVo fcTestVo = new FcTestVo();
if(){
//这里加单元格校验 数据是否存在、日期时间格式是否正确、金额是否正确、保留小数是否合格。。。
}
AtomicBoolean sign = new AtomicBoolean(true);
//查表中数据加判断是否重复
List<FcTestVo> fcTestVos1 = teatService.getListByCodeAndYearAndMon(cell4,cell0,cell7);
if(null != fcTestVos1 && fcTestVos1.size() != 0){
bufferExit.append("第").append(r+1).append("行,");
continue;
}
//处理相同数据
if(!fcTestVos.isEmpty()){
for (FcTestVo key : fcTestVos){
if(StringUtils.equals(key.getYearAndMon(),cell0)&&StringUtils.equals(key.getCode(),cell4)){
sign.set(false);
break;
} } }
if(StringUtils.equals(sign.toString(),"false")){
continue;
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//把excel表中的数据分别保存数据表中
fcTestVo.setA(cell0);
fcTestVo.setB(cell3);
fcTestVo.setC(cell4);
fcTestVo.setD(cell5);
fcTestVo.setE(cell6);
fcTestVo.setFcell7);
fcTestVo.setG(cell8));
fcTestVo.setPayMoney(turnMoney(cell9));
fcTestVo.setRemark(cell10);
fcTestVos.add(fcTestVo);
}
testService.insertAll(fcTestVos);
if (fcTestVos.isEmpty()) {
return Resp.succResp(request, RespMap.getInstance("result", "当前模板中无符合上传的数据!").add("flag", buffer).add("bufferMon",bufferMon).add("bufferExit",bufferExit).build());
}
return Resp.succResp(request, RespMap.getInstance("result", (StringUtils.isBlank(buffer) ? "上传成功!" : "部分数据上传成功!")).add("flag", buffer).add("bufferMon",bufferMon).add("bufferExit",bufferExit).build());
} catch (IOException e) {
log.error(e.getMessage(), e);
} catch (ParseException e) {
log.info("付款日期异常");
throw new RuntimeException(e);
} finally {
log.info("finally");
if (is != null) {
is.close();
}
}
return Resp.succResp(request, RespMap.getInstance("result", "上传失败").add("flag", buffer).build());
}