layui Excel模板下载、批量导入

功能:点击批量导入–弹出页面(下载模板、点击上传、批量导入按钮)
在这里插入图片描述

一、下载模板

1、前端页面

<button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="mulAdd">批量导入</button>
 <!--  这里是弹出层表单(批量导入) -->
 	<div class="layui-row" id="popmulAddTest" style="display:none;">
	    <div class="layui-col-md11">
	        <form class="layui-form layui-from-pane" action="" style="margin-top:20px;" method="">
	            <div>
	               <h3 style="text-align: center">
	               <a href="/item/filedown" 
	                  class="layui-btn layui-btn-normal layui-btn-xs">下载导入模板</a>
	               </h3>
	            </div>
	            
	            <div class="layui-form-item" style="text-align: center;margin-top:20px">
	                <div class="layui-upload-drag" id="test8" name="file">
	                    <i class="layui-icon"></i>
	                    <p>点击上传,或将文件拖拽到此处</p>
	                </div>
	            </div>	
	            <div class="layui-form-item" style="margin-top:20px">
	                <div class="" style="text-align: center"> 	                   	                                    
	                    <button type="button" class="layui-upload layui-btn" id="test19">批量导入</button>	                   
	                </div>
	            </div>
	        </form>
	    </div>
	</div> 
//监听工具条事件
		table.on("toolbar(itemTable)",function(obj){
			 switch(obj.event){
			    //批量导入
			    case 'mulAdd':
                    layer.msg('添加');
                    layer.open({
                        //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
                        type: 1,
                        title: "批量导入信息",
                        area: ['410px', '340px'],
                        content: $("#popmulAddTest")  //引用的弹出层的页面层的方式加载修改界面表单
                    });
                 break; 	
			  };
		});

2、前端控制器

/**
	 * 文件下载
	 * @param request
	 * @param response
	 */
	@RequestMapping("filedown")
	public void down(HttpServletRequest request , HttpServletResponse response){
		System.out.println("文件开始下载");
		//声明
		String fName = "未完事项下载模板.xls";
		//第一种方法,读取电脑硬盘里面的
	        //String fileName = "D:\\tempFile\\tmpFilesMilti\\"+fName;
		//第二种方法,读取项目里面的
		String fileName = "\\TempFile\\"+fName;
		System.out.println(fileName);
		BufferedOutputStream out = null;
		InputStream bis = null;
		try {
			//第一种方法
			//bis = new BufferedInputStream(new FileInputStream(new File(fileName)));
			//第二种方法:读取项目resourse目录下的文件
			ClassLoader loade = ServletContextResource.class.getClassLoader();
			bis = loade.getResourceAsStream(fileName);
			//中文名下载
			String downName = fName;
			downName = URLEncoder.encode(downName,"UTF-8"); 
			response.addHeader("Content-Disposition", "attachment;filename=" + downName);
			 //1.设置文件ContentType类型,这样设置,会自动判断下载文件类型    
        	response.setContentType("multipart/form-data"); 
        	out = new BufferedOutputStream(response.getOutputStream());
        	int len = 0;
        	while((len = bis.read()) != -1){
        		out.write(len);
        		out.flush();
        	}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(bis != null){
				try {
					bis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(out != null){
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		System.out.println("文件下载结束");
	}

3、模板存放位置
在这里插入图片描述

二、批量导入

1、依赖包

		<!-- 批量导入所需包 -->
		<dependency>
		   <groupId>org.apache.poi</groupId>
		   <artifactId>poi</artifactId>
		   <version>3.14</version>
		</dependency>
		<dependency>
		   <groupId>org.apache.poi</groupId>
		   <artifactId>poi-ooxml</artifactId>
		   <version>3.14</version>
		</dependency>
		<dependency>
		   <groupId>org.apache.poi</groupId>
		   <artifactId>poi-ooxml-schemas</artifactId>
		   <version>3.14</version>
		</dependency>

2、映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sxt.bus.mapper.ItemMapper">
    <!-- Excel批量导入 -->
   <insert id="insertImportItem">
    insert into bus_item
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="itemid != null and itemid != ''" >
        itemid,
      </if>
      <if test="matter != null and matter != ''" >
        matter,
      </if>
      <if test="belongsto != null and belongsto != ''" >
        belongsto,
      </if>
      <if test="detail != null and detail != ''" >
        detail,
      </if>
      <if test="starttime != null and starttime != ''" >
        starttime,
      </if>
      <if test="endtime != null and endtime != ''" >
        endtime,
      </if>
      <if test="principal != null and principal != ''" >
        principal,
      </if>
      <if test="remark != null and remark != ''" >
        remark,
      </if>
      <if test="status != null and status != ''" >
        status,
      </if>
    </trim>
    
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="itemid != null and itemid != ''" >
        #{itemid},
      </if>
      <if test="matter != null and matter != ''" >
        #{matter},
      </if>
      <if test="belongsto != null and belongsto != ''" >
        #{belongsto},
      </if>
      <if test="detail != null and detail != ''" >
        #{detail},
      </if>
      <if test="starttime != null and starttime != ''" >
        #{starttime},
      </if>
      <if test="endtime != null and endtime != ''" >
        #{endtime},
      </if>
      <if test="principal != null and principal != ''" >
        #{principal},
      </if>
      <if test="remark != null and remark != ''" >
        #{remark},
      </if>
      <if test="status != null and status != ''" >
        #{status},
      </if>
    </trim>
  </insert>
</mapper>

3、mapper接口

package com.sxt.bus.mapper;
import com.sxt.bus.domain.Item;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
/**
 * 映射接口
 * @author Administrator
 *
 */
public interface ItemMapper extends BaseMapper<Item> {
	//批量导入
	void insertImportItem(Item vo);
}

4、service

package com.sxt.bus.service;
import com.sxt.bus.domain.Item;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;
import com.baomidou.mybatisplus.extension.service.IService;
/**
 * 服务接口
 * @author Administrator
 *
 */
public interface ItemService extends IService<Item> {
	/**
	 * 批量导入
	 * @param file
	 * @param request
	 * @param response
	 */
	void addAjaxUploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response);
}

5、service实现类

package com.sxt.bus.service.impl;
import com.sxt.bus.domain.Item;
import com.sxt.bus.mapper.ItemMapper;
import com.sxt.bus.service.ItemService;
import com.sxt.bus.utils.ExcelUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
/**
 * 服务接口实现类
 * @author Administrator
 *
 */
@Service
public class ItemServiceImpl extends ServiceImpl<ItemMapper, Item> implements ItemService {
	@Autowired
	private ItemMapper itemMapper;

	/**
	 * 批量导入
	 */
	@Override
	public void addAjaxUploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
       /*MultipartFile file = multipartRequest.getFile("file");*/
        System.out.println("ItemServiceImpl得到数据文件");
        if(file.isEmpty()){
            try {
                throw new Exception("文件不存在!");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        InputStream in =null;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
      //  System.out.println("加载流");
        List<List<Object>> listob = null;
        try {
        //    System.out.println("加载流");
            listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
        }

        SimpleDateFormat sdf = new SimpleDateFormat();
        sdf = new SimpleDateFormat( "yyyy-MM-dd");
        String s = "2011-07-09 ";
        try {
            Date date = sdf.parse(s);
            System.out.println(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //该处可调用service相应方法进行数据保存到数据库中,现只对数据输出
        for (int i = 0; i < listob.size(); i++) {
            List<Object> lo = listob.get(i);
            System.out.println("遍历" + listob.get(i));
            Item vo = new Item();
                 
            if(lo.size() == 8){  //无ID这一列     
            	vo.setMatter(String.valueOf(lo.get(0)));
            	vo.setBelongsto(String.valueOf(lo.get(1)));
            	vo.setDetail(String.valueOf(lo.get(2)));
            	vo.setStarttime(String.valueOf(lo.get(3)));
            	vo.setEndtime(String.valueOf(lo.get(4)));
            	vo.setPrincipal(String.valueOf(lo.get(5)));
            	vo.setRemark(String.valueOf(lo.get(6)));
            	vo.setStatus(Integer.valueOf(String.valueOf(lo.get(7))));
         
            }else if(lo.size() == 9){   //有ID这一列
            	vo.setItemid(Integer.valueOf(String.valueOf(lo.get(0))));
            	vo.setMatter(String.valueOf(lo.get(1)));
            	vo.setBelongsto(String.valueOf(lo.get(2)));
            	vo.setDetail(String.valueOf(lo.get(3)));
            	vo.setStarttime(String.valueOf(lo.get(4)));
            	vo.setEndtime(String.valueOf(lo.get(5)));
            	vo.setPrincipal(String.valueOf(lo.get(6)));
            	vo.setRemark(String.valueOf(lo.get(7)));
            	vo.setStatus(Integer.valueOf(String.valueOf(lo.get(8))));          	
            }          
            itemMapper.insertImportItem(vo);
        }	
	}
}

6、前端控制器

	/**
	 * Excel批量导入
	 * @param file
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
    @RequestMapping("ajaxUpload")
    public Map<String, String> ajaxUpload(@RequestParam("file")MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        System.out.println("itemService进来了!!");
        Map<String, String> map = new HashMap<>();
        itemService.addAjaxUploadExcel(file, request, response);
        System.out.println(file+","+request+","+response);
        map.put("msg", "ok");
        return map;
    }

7、前端页面

<button class="layui-btn layui-btn-sm layui-btn-normal" lay-event="mulAdd">批量导入</button>

<!--  这里是弹出层表单(批量导入) -->
 	<div class="layui-row" id="popmulAddTest" style="display:none;">
	    <div class="layui-col-md11">
	        <form class="layui-form layui-from-pane" action="" style="margin-top:20px;" method="">
	            <div>
	               <h3 style="text-align: center">
	               <a href="/item/filedown" 
	                  class="layui-btn layui-btn-normal layui-btn-xs">下载导入模板</a>
	               </h3>
	            </div>
	            
	            <div class="layui-form-item" style="text-align: center;margin-top:20px">
	                <div class="layui-upload-drag" id="test8" name="file">
	                    <i class="layui-icon"></i>
	                    <p>点击上传,或将文件拖拽到此处</p>
	                </div>
	            </div>	
	            <div class="layui-form-item" style="margin-top:20px">
	                <div class="" style="text-align: center"> 	                   	                                    
	                    <button type="button" class="layui-upload layui-btn" id="test19">批量导入</button>	                   
	                </div>
	            </div>
	        </form>
	    </div>
	</div> 

在这里插入图片描述

 //选完文件后不自动上传
	       upload.render({	    	   
	        	elem: '#test8' //
	            ,url: '/item/ajaxUpload'
	            ,auto: false
	            ,accept: 'file' //普通文件
	            //,multiple: true
	            ,bindAction: '#test19'
	            ,done: function(res){
	            	if(res.code > 0){	            		                              
	            		layer.msg('导入失败!', {	            		                                  
	            		}, function(){	            		                                    
	            		location.reload();	            		                                  
	            		});            		                            
	            		}else{	
	            		location.reload();                                
	         
	            		layer.msg('导入成功!');
	            		                            
	            		}		            	
	            }	            
	        });

三、所遇到的bug

在这里插入图片描述
原因:
1、上面已经写了@RestController,下面又写@ResponseBody,已经重复
2、应该是8,而不是7
在这里插入图片描述

发布了37 篇原创文章 · 获赞 4 · 访问量 1025

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览