layui 上传excel，数据插入到数据库

前端代码:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="views/lib/layui-v2.5.5/css/layui.css" media="all">
    <link rel="stylesheet" href="views/css/layuimini.css?v=2.0.4.2" media="all">
    <link rel="stylesheet" href="views/css/themes/default.css" media="all">
    <link rel="stylesheet" href="views/lib/font-awesome-4.7.0/css/font-awesome.min.css" media="all">
    <script src="views/lib/layui-v2.5.5/layui.js" charset="utf-8"></script>
    <script src="views/js/lay-config.js?v=2.0.0" charset="utf-8"></script>
    <script src="views/js/common.js" charset="utf-8"></script>
    <!--[if lt IE 9]>
    <script src="views/lib/export-word/excanvas-IE8.js"></script>
    <script src="views/lib/export-word/jqueryWordExport-IE8.js"></script>
    <script src="views/lib/other/html5.min.js"></script>
    <script src="views/lib/other/respond.min.js"></script>
    <![endif]-->
</head>
<body class="layui-layout-body">
<div class="layui-upload" align="center">
    <fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;" align="center">
        <legend>请选择您要上传的Excel文件</legend>
    </fieldset>
    <div style="margin-top: 30px" align="center">
        <button id="importData" name="file" class="layui-btn layui-btn-normal">导入</button>
    </div>
</div>
</div>

<script>
    layui.use(["element", "layer", "upload"], function () {
        var layer = layui.layer;
        layui.upload.render({
            elem: "#importData",
            url: '/upload',
            size: '800',
            accept: "file",
            exts: 'xls|xlsx',
            before: function () {
                layer.load();
            },
            done: function (res) {
                if (res.code==0) {
                    let index=layer.alert('上传成功！',function () {
                        layer.close(index);
                    });
                } else layer.alert('失败',{icon:5});
                layer.closeAll('loading');
            }
        });
    });

</script>
</body>
</html>
</html>

实体类:

package com.pojo;

/**
 * 试题表
 * */
public class Topic {
    private Integer id;
    private String title;
    private String answerA;
    private String answerB;
    private String answerC;
    private String answerD;
    private int isMore;
    private String answer;
    private int sid;
    private int grade;

    public Topic() {
    }

    public Topic(String title, String answerA, String answerB, String answerC, String answerD, int isMore, String answer, int sid, int grade) {
        this.title = title;
        this.answerA = answerA;
        this.answerB = answerB;
        this.answerC = answerC;
        this.answerD = answerD;
        this.isMore = isMore;
        this.answer = answer;
        this.sid = sid;
        this.grade = grade;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAnswerA() {
        return answerA;
    }

    public void setAnswerA(String answerA) {
        this.answerA = answerA;
    }

    public String getAnswerB() {
        return answerB;
    }

    public void setAnswerB(String answerB) {
        this.answerB = answerB;
    }

    public String getAnswerC() {
        return answerC;
    }

    public void setAnswerC(String answerC) {
        this.answerC = answerC;
    }

    public String getAnswerD() {
        return answerD;
    }

    public void setAnswerD(String answerD) {
        this.answerD = answerD;
    }

    public int getIsMore() {
        return isMore;
    }

    public void setIsMore(int isMore) {
        this.isMore = isMore;
    }

    public String getAnswer() {
        return answer;
    }

    public void setAnswer(String answer) {
        this.answer = answer;
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }
}

mapper 接口

package com.mapper;

import com.pojo.Topic;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TopicMapper {
    @Select("select * from topic")
    public List<Topic>getAll();
    @Insert("<script>" +
            "insert into topic(title,answerA,answerB,answerC,answerD,isMore,answer,sid,grade) values" +
            "<foreach collection='list'  item='item' index='index' separator=','> "+
            "(#{item.title},#{item.answerA},#{item.answerB},#{item.answerC},#{item.answerD},#{item.isMore}," +
            "#{item.answer},#{item.sid},#{item.grade}) " +
            "</foreach>" +
            "</script>")
    public boolean AddTopic(@Param("list") List<Topic> list);
}

service 接口

package com.service;

import com.pojo.Topic;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

public interface TopicService {

    public List<Topic> getAll();
    public boolean AddTopic(List<Topic> list);
    String UploadExcel(HttpServletRequest request, HttpServletResponse response);
}

PowerServiceImpl 实现类

@Service
public class TopicServiceImpl implements TopicService {
    @Autowired
    private TopicMapper mapper;
    @Override
    public List<Topic> getAll() {
        return mapper.getAll();
    }

    @Override
    public boolean AddTopic(List<Topic> list) {
        return mapper.AddTopic(list);
    }

    @Override
    public String UploadExcel(HttpServletRequest request, HttpServletResponse response) {

        CommonsMultipartResolver cmr = new CommonsMultipartResolver(request.getServletContext());
        cmr.setDefaultEncoding("utf-8");
        cmr.setMaxInMemorySize(40960);
        cmr.setMaxUploadSize(10485760000L);
        MultipartFile file=null;
        if (cmr.isMultipart(request)) {
            MultipartHttpServletRequest multipartRequest = cmr.resolveMultipart(request);
            file = multipartRequest.getFile("file");// 与页面input的name相同
        }
        if(file.isEmpty()){
            try {
                throw new Exception("文件不存在！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        InputStream in =null;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        List<List<Object>> listob = null;
        try {
            listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Topic>list=new ArrayList<>();
        for (int i = 0; i < listob.size(); i++) {
            List<Object> lo = listob.get(i);
            Topic vo = new Topic();

            vo.setTitle(String.valueOf(String.valueOf(lo.get(0))));
            vo.setAnswerA(String.valueOf(lo.get(1)));
            vo.setAnswerB(String.valueOf(lo.get(2)));
            vo.setAnswerC(String.valueOf(lo.get(3)));
            vo.setAnswerD(String.valueOf(lo.get(4)));
            vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(5))));
            vo.setAnswer(String.valueOf(lo.get(6)));
            vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(7))));
            vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(8))));
            list.add(vo);
        }
        mapper.AddTopic(list);

        return "ok";
    }

}

Controller 层

@Controller
public class TopicController {
    @Autowired
    private TopicService service;
    @GetMapping("/topicAll")
    public String TopicAddAll(){
        return "views/topicAll.htm";
    }

    @RequestMapping(value = "/upload",method = {RequestMethod.GET,RequestMethod.POST})
    @ResponseBody
    public Object upload(HttpServletRequest request, HttpServletResponse response) throws InvocationTargetException, IntrospectionException, IllegalAccessException, IOException, InvalidFormatException {
        String s=service.ajaxUploadExcel(request,response);
        if(s.equals("ok"))
            return JsonRes.res();
        else
            return JsonRes.error("导入excel失败！");
        
    }
}

工具类：

package com.util;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException
     */
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = this.getWorkbook(in,fileName);
        if(null == work){
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){continue;}

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(this.getValue(cell));
                }
                list.add(li);
            }
        }

        return list;

    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(excel2003L.equals(fileType)){
            wb = new HSSFWorkbook(inStr);  //2003-
        }else if(excel2007U.equals(fileType)){
            wb = new XSSFWorkbook(inStr);  //2007+
        }else{
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     */
    //解决excel类型问题，获得数值
    public  String getValue(Cell cell) {
        String value = "";
        if(null==cell){
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    value = format.format(date);;
                }else {// 纯数字
                    BigDecimal big=new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if(null!=value&&!"".equals(value.trim())){
                        String[] item = value.split("[.]");
                        if(1<item.length&&"0".equals(item[1])){
                            value=item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue().toString();
                break;
            // 公式类型
            case Cell.CELL_TYPE_FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue().toString();
                }
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                value = " "+ cell.getBooleanCellValue();
                break;
            default:
                value = cell.getStringCellValue().toString();
        }
        if("null".endsWith(value.trim())){
            value="";
        }
        return value;
    }
}

pom.xml

<!--poi 上传需要的依赖-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.8-beta3</version>
      <type>jar</type>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
      <type>jar</type>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.4</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.6</version>
    </dependency>

数据：

title	answerA	answerB	answerC	answerD	isMore	answer	sid	grade
以下选项中符合Python语言变量命名规则的是（）	x y z	5_five	_3a	&	C	2	2
下列表达式的值为True的是（）	4+3>2+5	3>4>2	’a’<’b’	3<1 and 5>2	C	2	2
下列选项中运行后输出 3+5 的是（）	Print(3+5)	print(‘3’+’5’)	print(3’+’5)	print(‘3’+’+’+’5’)	D	2	2

layui 上传excel，数据插入到数据库

继续阅读

qtp-Doexcel函数写入失败或提示缺少对象，经验分享

SQL表中数据按条件批量导出多个Excel文件

GlobalMapper20如何导入文本类（txt，csv，excel）的坐标数据序：一、如何导入离散的坐标点二、离散的坐标点如何生产相互连接的线三、离散的坐标点如何生成范围面

如何增值你的文档资产?_

在线转换pdf和虚拟打印机生成pdf文件操作攻略

如何在Word中输入英语音标

VBA实用代码拾零

vba excel编程三日谈(1)

EXCEL VBA笔记

使用ffmpeg进行视频文件转换成FLV整理

文件资源管理器右键开文件夹_如何在Windows的文件资源管理器中设置自定义启动文件夹...

利用javascript和jxl实现自定义报表的输出

Excel多行转多列

Excel如何将小写转大写金额

个项目的开发过程及成员组成

layui多任务上传添加进度条