java后台管理系统做Excel导入

1.前端html页面代码

<a type="button" class="btn btn-danger" data-toggle="modal" data-target="#importExcel"> <i class="fa fa-pencil-square-o"></i>Excel导入</a><!--导入excel--><div class="col-md-2"><div class="modal fade" id="importExcel" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true"><div class="modal-dialog"><div class="modal-content"><div class="modal-header"><button type="button" class="close" data-dismiss="modal" ><span aria-hidden="true">&times;</span><span class="sr-only">Close</span></button><!-- 修改myModalLabel 变为 myModalLabel1--><h4 class="modal-title" id="myModalLabel1">批量商家信息</h4></div><div class="modal-body" style="background-color: AliceBlue;"><!-- 提交路径在此处 --><form id="form_add_by_teacher" method="post" class="form-horizontal" enctype="multipart/form-data"><div class="form-group"><label class="col-lg-3 col-lg-offset-1 control-label">文件&nbsp;<span style="color: red;">*</span></label><span class="btn btn-default btn-file"><span class="fileinput-new">请选择Excel文件</span><input type="file" name="file" style="position: inherit;font-size: 5px;opacity: 1;"></span></div><div class="form-group"><div class="col-lg-8 col-lg-offset-4"><button class="btn btn-primary" @click="uploadExcel">上传</button><!-- 下载excel模板路径在此--><a id="downLoadId" href="../templ/orgGroupInfo.xlsx">导入excel模板下载</a></div></div></form></div><div class="modal-footer"></div></div></div></div></div><!--导入excel，end-->

2.JS代码

//Excel导入uploadExcel:function () {var formData = new FormData($("#form_add_by_teacher")[0]);$.ajax({type: "post",url: "../importExcel/BusinessBaseInfoImport_importUpload.do",async: false,contentType: false,processData: false,dataType: 'json',data: formData,success: function(resp) {if(resp.success){alert("上传完成"+"\n"+resp.data);vm.reload();}else{alert(resp.data);}},});}

3.后端执行方法

public class BusinessBaseInfoImportAction extends BaseAction {private static final long serialVersionUID = 1L;private static Logger logger = (Logger) LogManager.getLogger("action");@ResourceBusinessBaseInfoService businessBaseInfoService;/*** 文件说明 生成get、set方法*/private File file;//为上传临时文件.tmp 结尾与html中的必须同名private String fileContentType;//为上传的类型private String fileFileName;//为上传的名字.扩展名//导入excelpublic void importUpload(){try {//session方式 没用tokenSessionUserInfo sessionUserInfo = WebMainUtil.getSessionUserInfo();//上传文件大小判断long fileSize = FileUtil.getKbSize(file);if (fileSize > 10000) {this.writeJson("文件超过大小", false);return;}if (!fileFileName.endsWith("xlsx") && !fileFileName.endsWith("xls")) {this.writeJson("上传失败，文件格式错误，必须为xlsx或者xls文件", false);return;}//判断是否为空文件，空文件会扔出错误if (file.getCanonicalPath() == null) {this.writeJson("上传失败，请选择文件", false);return;}//获取存储路径String path = ServletActionContext.getServletContext().getRealPath("/templ/upload");//建立文件夹File dest = new File(path);//不存在则建立文件夹if (!dest.exists()) dest.mkdirs();//System.out.println(dest.getPath());//文件以 时间+原文件名的方式存储File file1 = new File(dest.getPath() + "/" + DateFormatUtil.formatToStringTimeDetail(new Date()) + fileFileName);//把临时文件拷贝到新文件FileUtils.copyFile(this.file, file1);InputStream inputStream = new FileInputStream(file1);String msg = businessBaseInfoService.excelBusinessList(inputStream, WebMainUtil.getSessionUserInfo().getUserId());this.writeJson(msg, true);} catch (Exception e) {e.printStackTrace();LOG.error(e.toString(), e);this.writeJson("上传失败:" + e.getMessage(), false);}}public File getFile() {return file;}public void setFile(File file) {this.file = file;}public String getFileFileName() {return fileFileName;}public void setFileFileName(String fileFileName) {this.fileFileName = fileFileName;}public String getFileContentType() {return fileContentType;}public void setFileContentType(String fileContentType) {this.fileContentType = fileContentType;}}

4.导入过程中的excelBusinessList方法封装

public String excelBusinessList(InputStream inputStream, Long lastOperator) {StringBuilder msg = new StringBuilder();//线程不安全，效率高String enter = System.getProperty("line.separator");try {Workbook workbook = WorkbookFactory.create(inputStream); //excel对象inputStream.close();Sheet sheet = workbook.getSheetAt(0); //sheet 表对象，默认获取sheet1int rowLength = sheet.getLastRowNum(); // 总行数Row row = sheet.getRow(0); //行对象int celLength = row.getLastCellNum(); //总列数Cell cell = row.getCell(0); // 列对象// 第一行是标题，从第二行开始读取int guide = 1; // 1:添加到数据库 0:跳过for (int rowNum = 1; rowNum <= rowLength; rowNum++) {guide = 1;BusinessBaseInfo businessBaseInfo = new BusinessBaseInfo();//获取行row = sheet.getRow(rowNum);if (isEmptyRow(row)){continue;}//获取此行数据for (int cellNum = 0; cellNum < celLength; cellNum++) {cell = row.getCell(cellNum);if (cell == null) { //空值的时候，下面解析会报nullPoint，所以要创建一个单元格cell = row.createCell(cellNum);}cell.setCellType(Cell.CELL_TYPE_STRING);String stringCellValue = cell.getStringCellValue();// 封装数据到实体类String resultErrorMsg = this.setBusinessParameter(cellNum + 1, businessBaseInfo, stringCellValue);//关键数据为空，直接跳过这一行if (resultErrorMsg != null) {guide = 0;msg.append("第" + rowNum + "行" + "导入失败，原因：" + "第" + (cellNum + 1) + "列--->" + resultErrorMsg + enter);logger.info("第" + rowNum + "行数据导入失败:" + resultErrorMsg);break;}}// 读取完一行，添加一次数据if (guide == 1) { //添加String addResult = addExcelBusiness(businessBaseInfo, lastOperator);if (addResult.equals("success_Update")) { //添加一行成功msg.append("第" + rowNum + "行" + "导入成功: " + "数据更新" + enter);logger.info("操作ID:" + lastOperator + "导入excel " + "第" + rowNum + "行数据导入成功:" + "更新了数据");} else if (addResult.equals("success_Insert")) {msg.append("第" + rowNum + "行" + "导入成功: " + "数据添加" + enter);logger.info("操作ID:" + lastOperator + "导入excel " + "第" + rowNum + "行数据导入成功:" + "添加了数据");} else {msg.append("第" + rowNum + "行" + "导入成功：" + addResult + enter);logger.info("操作ID:" + lastOperator + "导入excel " + "第" + rowNum + "行数据导入失败:" + addResult);}} else { //跳过continue;}}} catch (Exception e) {logger.error("parse excel file error :", e);throw new RuntimeException("parse excel file error :", e);}return msg.toString();}// 单元格数据封装到实体类// 返回：错误原因public String setBusinessParameter(int index, BusinessBaseInfo businessBaseInfo, String data) throws ParseException {String result = null;//有多少列就判断多少次if (index == 1) { //关联的用户IdbusinessBaseInfo.setIsDeleted("n");if (StringUtil.isEmpty(data)) {businessBaseInfo.setUserId(null);} else {businessBaseInfo.setUserId(Long.valueOf(data));}} else if (index == 2) {//商家全称if (StringUtil.isEmpty(data)) {//必须要有的字段 否则就添加失败result = "商家全称不能为空";return result;}businessBaseInfo.setBusinessName(data);} return null;}// 添加封装数据到数据库public String addExcelBusiness (BusinessBaseInfo businessBaseInfo, Long id){//通过Id查询操作人的信息BaseCustomerInfo baseCustomerInfo = baseCustomerInfoService.selectById(id);//通过数据库中不能重复的字段 查询是否已经存在这个对象BusinessBaseInfo businessBaseInfo1 = businessBaseInfoMapper.selectByBusinessName(businessBaseInfo.getBusinessName());if (businessBaseInfo1!=null){//对象已存在 进行更新操作Date date=new Date();businessBaseInfo.setLastModifyTime(date);//特殊字段 设定修改时间businessBaseInfo1.setLastOperator(baseCustomerInfo.getAccounts());//设定最后修改人int i = businessBaseInfoMapper.updateByPrimaryKeySelective(businessBaseInfo);if (i==1){return "success_Update";}else {return "update error for excelImport";}}else {//不存在 进行新增操作/* if (businessBaseInfo.getUserId()!=null){UserBaseInfoDubbo user = userBaseInfoDubboService.selectRedisUserByUid(businessBaseInfo.getUserId());businessBaseInfo.setOrgId(user.getOrgId());businessBaseInfo.setViceNumber(user.getViceNumber());}*///特定字段 设定 新增时间 和 操作人Date date=new Date();businessBaseInfo.setCreateTime(date);businessBaseInfo.setLastModifyTime(date);businessBaseInfo.setCreateOperator(baseCustomerInfo.getAccounts());businessBaseInfo.setLastOperator(baseCustomerInfo.getAccounts());int i = businessBaseInfoMapper.insertSelective(businessBaseInfo);if (i==1){return "success_Insert";}else {return "insert error for excelImport";}}}// 判断整行数据是否为空的方法 public static boolean isEmptyRow(Row row) {if (row == null || row.toString().isEmpty()) {return true;} else {Iterator<Cell> it = row.iterator();boolean isEmpty = true;while (it.hasNext()) {Cell cell = it.next();if (cell.getCellType() != CELL_TYPE_BLANK) {isEmpty = false;break;}}return isEmpty;}}