spring boot+layui的导入excel模板数据保存到数据库中

最新推荐文章于 2024-08-23 18:00:00 发布

CCyyll_168

最新推荐文章于 2024-08-23 18:00:00 发布

阅读量5.7k

点赞数

本文链接：https://blog.youkuaiyun.com/CCyyll_168/article/details/83619174

版权

html代码

<button type="button" class="layui-btn" id="importFile" shiro:hasAnyRoles="root,resourceManager"><i class="layui-icon"></i>导入考勤模板</button>

js代码

layui.use(['upload'],function(){

var $ = layui.jquery,

upload = layui.upload;

//指定允许上传的文件类型
       upload.render({
       elem: '#importFile'
       ,url: port.getAttendanceListURL("import_attendance")
       ,accept: 'file' //普通文件
       ,exts: 'xlsx' //只允许上传压缩文件
       ,done: function(res){
           if(res.code == 0){ //上传成功
           layer.msg(res.msg);

           }else{
               layer.msg('上传失败');
           }
           tabInst.reload();
           }
           ,error: function(index, upload){
           layer.msg('上传失败');
           }
       });

});

Controller层代码

/**
   * 导入考勤模板
   *
   * @param response
   * @return
   */
   @RequestMapping("/get/import_attendance")
   public String importTemplate(@RequestParam("file") MultipartFile file) {
       String str = "";
       Map<String, String> resObj = new HashMap<>();
       if (!file.isEmpty()) {
           try {

               String filename = file.getOriginalFilename();
               str = omsAttendanceService.importService(filename, file);
           } catch (IOException e) {
               logger.error("考勤查看的导入数据保存出错", e);
               resObj.put("msg", "error");
               resObj.put("code", "1");
               return JSONObject.toJSONString(resObj);
           }
           resObj.put("msg", str);
           resObj.put("code", "0");
           return JSONObject.toJSONString(resObj);
       } else {
           return null;
       }
   }

接口代码：

   @SuppressWarnings("resource")
   @Transactional(rollbackFor=Exception.class)
   @Override
   public String importService(String filename, MultipartFile file) throws IOException {
       boolean isExcel2003 = true;
       String str = "";
       if (filename.matches("^.+\\.(?i)(xlsx)$")) {
           isExcel2003 = false;
       }
       InputStream is = file.getInputStream();
       Workbook wb = null;
       if (isExcel2003) {
           wb = new HSSFWorkbook(is);
       } else {
           wb = new XSSFWorkbook(is);
       }
       Sheet sheet = wb.getSheetAt(0);
       if (sheet != null) {
           str = "文件上传成功";
       } else {
           str = "上传失败sheet为空";
           return str;
       }
       for (int r = 1; r <= sheet.getLastRowNum(); r++) {
           Row row = sheet.getRow(r);
           if (row == null) {
               continue;
           }
           row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
           row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
           row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
           row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);

//从excel第二行开始获取每个单元格的数据

String vcName = row.getCell(0).getStringCellValue();

           String vcDate = row.getCell(1).getStringCellValue();
           String vcSignin = row.getCell(2).getStringCellValue();
           String vcLeave = row.getCell(3).getStringCellValue();

ObjectName va = new ObjectName ();

           va.setVcName(vcName);
           va.setVcDate(DateUtils.getExcelToDate(Integer.parseInt(vcDate)));
           va.setVcSignin(vcSignin.trim().length()>0 ? DateUtils.getExcelToTime(Double.parseDouble(vcSignin)):"");

           va.setVcLeave(vcLeave.trim().length()>0 ? DateUtils.getExcelToTime(Double.parseDouble(vcLeave)):"");
           //保存
           ObjectName Mapper.save(va);
       }
       return str;
   }

注意：

DateUtils.getExcelToTime

这个方法写的excel日期数字转换的工具类2018-10-20

DateUtils.getExcelToTime

这方法写的excel时间数字转换的工具类08:23