Java的excel表上传

最新推荐文章于 2022-12-26 20:32:03 发布

B1ackid

最新推荐文章于 2022-12-26 20:32:03 发布

阅读量138

点赞数

分类专栏：学习文章标签： Java Excel 文件上传 layui

本文链接：https://blog.csdn.net/qq_43118311/article/details/90108764

版权

学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 整体架构采用的是Layui与spring boot+mybatis

2. 要使用文件上传pom文件中引入

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.11</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.11</version>
</dependency>

3. 前端页面

3.1 前端使用的layui的上传模块，详情:https://www.layui.com/doc/modules/upload.html

<html>
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<title>修改密码</title>
	<link rel="stylesheet" href="layui/css/layui.css">
</head>
<body>
	<div class="layui-col-md12">
		<div class="layui-card">
			<div class="layui-card-header">上传学生成绩</div>
			<div class="layui-card-body" pad15="">
				<div class="layui-upload">
				  <button type="button" class="layui-btn layui-btn-normal" id="selectFile">选择文件</button>
				  <button type="button" class="layui-btn" id="StartUpload">开始上传</button>
				</div>
			</div>
		</div>
	</div>
</body>
<script src="layui/layui.js"></script>
<script>
layui.use(['upload'] ,function(){
  var $ = layui.jquery
  ,upload = layui.upload
 
  
  upload.render({
	    elem: '#selectFile'
	    ,url: '/uploadScore'
	    ,auto: false
	    ,accept : 'file'
	    //,multiple: true
	    ,bindAction: '#StartUpload'
	    ,done: function(res){
	      alert(res.info);
	    }
	  });
 
   
});
</script>
</html>

4.上传的后端java代码

/**
   * excel数据录入
   */
   @RequestMapping("/uploadScore")
   public JSONObject uploadMarkExcel(MultipartFile file) {
	  JSONObject result = new JSONObject();
	  result.put("info", "上传成功!");
    //判断文件是否为空
     if (file==null) {
       result.put("info", "文件不能为空");
     }
       try {
         InputStream is = file.getInputStream();
         //得到excel表的这个对象
         HSSFWorkbook book = new HSSFWorkbook(is);
         //获取第一个表格，正常的excel文件下表有多个表
         HSSFSheet sheet = book.getSheetAt(0);
         if (sheet==null) {
         	result.put("info", "请使用Sheet1");
         }
         //校验表格 (可忽略，依据业务需求)
         for (int i = 1; i <=sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row==null) {
        	    result.put("info", "数据之间不能有空行");
            }
            //获取学科信息
            String subjectName = row.getCell(1).toString();
            String sql = String.format("select * from Subject WHERE name = '%s';", subjectName);
            List<LinkedHashMap<String, Object>> subject_mark_list = dataService.getData(sql);
            //分数限制
            Object object = subject_mark_list.get(0).get("total");
            int normalMark = 100;
            if(object!=null) {
            	normalMark = Integer.parseInt(subject_mark_list.get(0).get("total")+"") ;
            } else {
            	result.put("info", "没有此科目！");
            }
            //录入分数
            double stu_mark = (Double.parseDouble(row.getCell(2).toString()));
            if (stu_mark<0) {
            	result.put("info", "第"+(i+1)+"分数为负数！");
             // return JsonUtils.resultOfJsonObject(500, "分数只能为正数:第"+(i+1)+"行!");
            }
            if (stu_mark>normalMark) {
            	result.put("info", "第"+(i+1)+"分数大于规定分数！");
            }
         }
         //校验成功
         //录入分数
         String sql = "";
         //将得到的学科名与人名转换成对应id
         Map<String, String> subjectMap = getSubject_();
         Map<String, String> stuMap = getStu();
         //表格第一行是表头，所以从1开始
         for (int i = 1; i <=sheet.getLastRowNum(); i++) {
           //获取行对象
           HSSFRow row = sheet.getRow(i);
           sql= ",('"+stuMap.get(row.getCell(0).toString())+"','"+subjectMap.get(row.getCell(1).toString())+"',"+row.getCell(2).toString()+")"+sql;
         }
         sql = "INSERT INTO score VALUES"+sql.substring(1, sql.length());
         boolean batchInsertFlag = dataService.insertData(sql);
         if (batchInsertFlag==false) {
        	 result.put("info", "批量插入失败!");
         }
       } catch (IOException e) {
         e.printStackTrace();
       }
      
     return result;
   }

B1ackid

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java的excel表上传

1. 整体架构采用的是Layui与spring boot+mybatis2. 要使用文件上传pom文件中引入<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version&...
复制链接

扫一扫