客户端excel如何存入数据库?

需求

先看看我们手里有什么==>一个List集合放了一堆Map Map里面放着K=V

技术支撑

少不了myBaits servlet
前端用的elementUI
插件poi==>需要用workBook

ok就这些 奥下面是poi的maven版本 需要的自己下一下

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

流程

❤️ 实体类和表怎么建?

public class Student {
    private int id;
    private String name;
    private int age;
    @JSONField(format = "yyyy-MM-dd")
    private Date birth;
    private int schid;
    private Course course;
    private School school;
    private int picId;
    private boolean del;
    }

当然了setter getter方法要自己写~
表👇

属性和字段名一一对应 当然我项目里面有多张表 所以添了几个外键 不需要schid和picid的可以不写

❤️ 遍历list

List<Map<String,String>> list =getMapArr(is);//上一篇的方法 直接拿来用了
ArrayList<Student> result =new ArrayList<>();
                    for(Map<String,String> m:list){
                        Student stu =new Student();
                        stu.setName(m.get("姓名"));
                        stu.setAge(Integer.parseInt(m.get("年龄").substring(0,m.get("年龄").lastIndexOf("."))));//小数点后面的不要
                        stu.setBirth(ExcelDoubleToDate(m.get("生日").substring(0,m.get("生日").lastIndexOf("."))));//小数点后面的也不要
                        stu.setSchid(Integer.parseInt(m.get("学校id").substring(0,m.get("学校id").lastIndexOf("."))));
                        result.add(stu);
                    }

这里有点小插曲 这个年龄这里 excel表格里面的数据是这样的

所以 ??这是个啥 我们需要先给他办了 我们需要java.util里面的Date类型刚开始我以为害两三行就行呗,…嗯 后来写了…反正还挺多…给大家看看吧👇

public static Date ExcelDoubleToDate(String strDate) {
        if(strDate.length() == 5){
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return tDate
            }catch (Exception e){
                e.printStackTrace();
                return null
            }
        }
    }
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));
        return tDate;
    }
    

没什么难度就是字符串转double 再转date 不过也可能写麻烦了 害就这样吧 总之到这里我们就把实体类组装好了 拿到了一个list集合 剩下就好办了 添就完事了 我直接快进
❤️ service

   public void addLot(ArrayList<Student> result) {
        SqlSession session =SF.getConnection();
        try{
            StudentMapper dao =session.getMapper(StudentMapper.class);
            dao.addLot(result);
            session.commit();
        }catch (Exception e){
            e.printStackTrace();
            if(session!=null) session.rollback();
        }finally {
            if(session!=null) session.close();
        }
    }

❤️ Mapper

void addLot(@Param("result") ArrayList<Student> result);

❤️ .xml

 <insert id="addLot">
        insert into student(name,age,birth,schid) values
        <foreach collection="result" item="r" separator=",">
            (#{r.name},#{r.age},#{r.birth},#{r.schid})
        </foreach>
    </insert>

不解释 咱直接给它办烂,再回头看接到值得servlet接下来干点啥

//刚才的组装数据
for(Map<String,String> m:list){
                        Student stu =new Student();
                        stu.setName(m.get("姓名"));
                        stu.setAge(Integer.parseInt(m.get("年龄").substring(0,m.get("年龄").lastIndexOf("."))));
                        stu.setBirth(StringtoDate.getDate(ExcelDoubleToDate(m.get("生日").substring(0,m.get("生日").lastIndexOf(".")))));
                        stu.setSchid(Integer.parseInt(m.get("学校id").substring(0,m.get("学校id").lastIndexOf("."))));
                        result.add(stu);
                    }
                    //新操作 就是夸自己几句
                    StudentService ss =new StudentService();
                    ss.addLot(result);
                    resp res =new resp();
                    res.setMsg("添加一堆成功");
                    res.setCode("200");
                    writeRes(res,response);

行了 后台就办了

---

按理说…哈哈不卖关子了 不!还不够 我要那种前端发给我一个表格 我再写到服务器端再读再存数据库这样似儿的 行!咱直接办它,满足你的小调皮!你是不是要这样的效果👇

截至到这里 其实后台就剩怎么接request了 让servlet自己动(狗头)别以为我不说了 最后会说怎么接
嗯难受得是前台还没东西大白板 咱看看业务逻辑

业务逻辑

把大象装进冰箱需要几步(滑稽)
分三(三声)步
-第一步上传个破表格 elementui el-upload办烂它
-第二步后台接这个文件 poi能办这个事
-第三步转Map转实体类再存数据库 两篇文章都在讲这个事 C+V就直接办烂(滑稽)

el-upload

当然了 我还弄了个模态框 一点蹦出来 然后里面放了个破上传,简单的很 去组件库翻翻就有了 我不说了 看el-upload
…算了还是说说吧 我也怕自己忘 当时候回头也能直接用(滑稽)
template👇

	<el-button type="success" @click="addLot">批量添加</el-button>
   <el-dialog title="提示" :visible.sync="addAlotOrNot" width="30%">
      <el-upload
        class="upload-demo"
        action="http://localhost:8080/student/addlot" 
        multiple
        :limit="1"
        :on-exceed="handleExceed"
        :on-success="addAlotSuccess"
        ref="addLotUpload"
      >
        <el-button size="small" type="primary">点击上传</el-button>
      </el-upload>
    </el-dialog>

data()

data(){
	return{
		 addAlotOrNot: false//先别显示这个破框
	}
}

methods:

  addAlotSuccess(response) {
      this.$refs.addLotUpload.clearFiles();//清空上传列表
      this.$message({
        message: "添加成功",
        type: "success",
      });
      this.queryData();
      this.addAlotOrNot = !this.addAlotOrNot;
    },
    handleExceed() {//超过限制骂人
      this.$message({
        message: "超过限制",
        type: "error",
      });
    },
    //批量添加
    addLot() {
      this.addAlotOrNot = !this.addAlotOrNot;//让它显示
    }

后台怎么接呢?

public void doPost(HttpServletRequest request, HttpServletResponse response) {
try {
            request.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        FileItemFactory f =new DiskFileItemFactory();
        ServletFileUpload su =new ServletFileUpload(f);
        try{
            List<FileItem> items =su.parseRequest(request);
            
            }catch(Exception e){
				e.printStackTrace()
			}
}

哎 得这么接 我前面介绍过怎么接二进制的请求 没看过的立刻给我去看看完记得点赞(破音) 如何接收并解析二进制的request
看完了吧 good 那我快进 嘻嘻 接着刚才的往下写👇

public void doPost(HttpServletRequest request, HttpServletResponse response) {
try {
            request.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        FileItemFactory f =new DiskFileItemFactory();
        ServletFileUpload su =new ServletFileUpload(f);
        try{
            List<FileItem> items =su.parseRequest(request);
             if(items.size()>0){
                FileItem item =items.get(0);
                if(!item.isFormField()){
                    //文件名字
                    String name =item.getName();
                    //文件后缀
                    String fExt =name.substring(name.lastIndexOf("."));
                    //为了避免重复 改名
                    String newName = UUID.randomUUID().toString().replaceAll("-","")+fExt;

                    ServletContext application =this.getServletConfig().getServletContext();
                    String path = application.getRealPath("/exces/"+newName);
                    //把图片保存路径放到数据库以备下次使用
                    item.write(new File(path));
            }catch(Exception e){
				e.printStackTrace()
			}
}

哎你会发现 ??到io流了 哇和最开始的inputStream接上头了 没错 之后就是xffworkbook的事了 嗯 全讲完了 哎 肯定能通 不通给我留言 我直接倒立洗头
最后贴一下这篇源码吧 想要前端的全部功能的话 我明后天的会把整个工程push一下 你们直接clone就可

package com.etoak.student.controller;

import com.alibaba.fastjson.JSONObject;
import com.etoak.student.entity.School;
import com.etoak.student.entity.Student;
import com.etoak.student.response.resp;
import com.etoak.student.service.StudentService;
import com.etoak.student.utils.StringtoDate;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletContext;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

@WebServlet("/student/addlot")
public class AddLotServlet extends HttpServlet {
    private static void writeRes(resp res, HttpServletResponse servletResponse){
        try{
            servletResponse.setContentType("text/json;charset=UTF-8");
            PrintWriter pw =servletResponse.getWriter();
            String json = JSONObject.toJSONString(res);
            pw.print(json);
            pw.flush();
            pw.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response) {
        try {
            request.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        FileItemFactory f =new DiskFileItemFactory();
        ServletFileUpload su =new ServletFileUpload(f);
        try{
            List<FileItem> items =su.parseRequest(request);
            if(items.size()>0){
                FileItem item =items.get(0);
                if(!item.isFormField()){
                    //文件名字
                    String name =item.getName();
                    //文件后缀
                    String fExt =name.substring(name.lastIndexOf("."));
                    //为了避免重复 改名
                    String newName = UUID.randomUUID().toString().replaceAll("-","")+fExt;

                    ServletContext application =this.getServletConfig().getServletContext();
                    String path = application.getRealPath("/exces/"+newName);
                    //把图片保存路径放到数据库以备下次使用
                    item.write(new File(path));
                    //添加一堆 读表格
                    InputStream is =new FileInputStream(new File(path));
                    List<Map<String,String>> list =getMapArr(is);
                    ArrayList<Student> result =new ArrayList<>();
                    for(Map<String,String> m:list){
                        Student stu =new Student();
                        stu.setName(m.get("姓名"));
                        stu.setAge(Integer.parseInt(m.get("年龄").substring(0,m.get("年龄").lastIndexOf("."))));
                        stu.setBirth(StringtoDate.getDate(ExcelDoubleToDate(m.get("生日").substring(0,m.get("生日").lastIndexOf(".")))));
                        stu.setSchid(Integer.parseInt(m.get("学校id").substring(0,m.get("学校id").lastIndexOf("."))));
                        result.add(stu);
                    }
                    StudentService ss =new StudentService();
                    ss.addLot(result);
                    resp res =new resp();
                    res.setMsg("添加一堆成功");
                    res.setCode("200");
                    writeRes(res,response);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response){
        this.doPost(request,response);
    }
        public static String getString(XSSFCell xssfCell) {
            if (xssfCell == null) {
                return "";
            }
            if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
                return String.valueOf(xssfCell.getNumericCellValue());
            } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
                return String.valueOf(xssfCell.getBooleanCellValue());
            } else {
                return xssfCell.getStringCellValue();
            }
        }


        public static List<Map<String,String>> getMapArr(InputStream is){
            XSSFWorkbook xssfWorkbook = null;
            try {
                xssfWorkbook = new XSSFWorkbook(is);
            } catch (Exception e) {
                e.printStackTrace();
            }
            //定义工作表
            XSSFSheet xssfSheet;
            xssfSheet = xssfWorkbook.getSheetAt(0);
            List<Map<String, String>> list = new ArrayList<Map<String, String>>();

            //定义行
            //默认第一行为标题行，index = 0
            XSSFRow titleRow = xssfSheet.getRow(0);

            //循环取每行的数据
            for (int rowIndex = 1; rowIndex < xssfSheet.getPhysicalNumberOfRows(); rowIndex++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
                if (xssfRow == null) {
                    continue;
                }

                Map<String, String> map = new LinkedHashMap<String, String>();
                //循环取每个单元格(cell)的数据
                for (int cellIndex = 0; cellIndex < xssfRow.getPhysicalNumberOfCells(); cellIndex++) {
                    XSSFCell titleCell = titleRow.getCell(cellIndex);
                    XSSFCell xssfCell = xssfRow.getCell(cellIndex);
                    map.put(getString(titleCell),getString(xssfCell));
                }
                list.add(map);
            }
            return list;
        }
    public static String ExcelDoubleToDate(String strDate) {
        if(strDate.length() == 5){
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return sdf.format(tDate);
            }catch (Exception e){
                e.printStackTrace();
                return strDate;
            }
        }
        return strDate;
    }
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));

        return tDate;
    }
}

good 行了说归说闹归闹还是希望大家能有所收获,看完喜欢给个赞吧很喜欢也可以给个关注 嘻嘻 纯手动码 当然了百度也帮忙了(再次滑稽) 会持续更新的,让我看到你们手里舞动的秋裤 好吗!!溜了