Maven项目管理+SSM:实现文件上传+读取Excel数据批量导入数据库

于 2019-08-22 15:56:50 发布
阅读量2.1k 收藏 10
点赞数
文章标签： Maven SSM AJAX
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/FtLnnL/article/details/100017091
版权

Maven项目管理+SSM:实现文件上传+读取Excel数据批量导入数据库

  • 项目目录

此为本项目的完整目录

  1. 前端页面代码

UploadText.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
    <!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
    <link rel="stylesheet"
          href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css"
          integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u"
          crossorigin="anonymous">
    <script type="text/javascript" src="${pageContext.request.contextPath}/js/jquery-2.1.1.min.js"></script>
    <!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
    <script
            src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"
            integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa"
            crossorigin="anonymous">
    </script>
</head>
<body>
<form id="uploadForm">
    <!-- 由于使用了ssm框架，后台接收是用name绑定的，注意name和后台一致 -->
     <input type="file" id="file" name="file" />
     <input type="button" onclick="UpladFile()" value="上传" />
</form>

<div class="progress" style="width: 40%">
    <div id="progressactive"
         class="progress-bar progress-bar-striped active"
         role="progressbar" aria-valuenow="45"
         aria-valuemin="0" aria-valuemax="100"
         style="width: 0%">
    </div>
</div>
<span id="sr-only"></span>
</body>
<script type="text/javascript">
    function UpladFile() {
        var fileObj = document.getElementById("file").files[0]; // js 获取文件对象
        //var fileObj  = document.getElementsByName("file")[0].files[0];//js 获取文件对象
        if(fileObj>5120){
            alert("上传大于5M")
        }
        var FileController = "${pageContext.request.contextPath}/upload"; // 接收上传文件的后台地址
        // FormData 对象
        var form = new FormData($("#uploadForm")[0]);//创建一个form对象
        // XMLHttpRequest 对象
        var xhr = new XMLHttpRequest();//创建XMLHttpRequest对象
        xhr.open("post", FileController, true);//打开连接，(访问类型，地址，是否异步)
        xhr.onload = function() {//请求完成后执行  里面可用
            if ((xhr.status >= 200 && xhr.status < 300) || xhr.status == 304) {

            } else {
                alert("Request was unsuccessful: " + xhr.status);//状态
                //0	UNSENT (未打开),1	OPENED  (未发送),2 HEADERS_RECEIVED (已获取响应头),3	LOADING (正在下载响应体),4	DONE (请求完成)
            }
        };
        xhr.upload.addEventListener("progress", progressFunction, false);//添加上传监听器方法
        xhr.send(form);//发送请求
    }
    function progressFunction(evt) {
        var progressBar = document.getElementById("progressactive");//获取进度条对象
        var percentageDiv = document.getElementById("sr-only");//获取展示进度数据对象
        if (evt.lengthComputable) {
            var max = evt.total;//文件总大小
            var loaded = evt.loaded;//已上传文件大小
            console.log(max);
            progressBar.style.width = Math.round(loaded / max * 100) + "%";
            percentageDiv.innerHTML = Math.round(loaded / max * 100) + "%";
            if (evt.loaded == evt.total) {
                window.location.href = "${pageContext.request.contextPath}/";
                percentageDiv.innerHTML = "上传完成";

            }
        }
    }
</script>
</html>

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>批量导入数据信息</title>
    <script src="${pageContext.request.contextPath}/js/jquery/2.0.0/jquery.min.js"></script>
    <link href="${pageContext.request.contextPath}/CSS/bootstrap/3.3.6/bootstrap.min.css" rel="stylesheet">
    <script src="${pageContext.request.contextPath}/js/bootstrap/3.3.6/bootstrap.min.js"></script>
</head>
<body>
<div class="container" backgroundcolor="blue">
    <div class="row clearfix">
        <div class="col-md-12 column">
            <div class="carousel slide" id="carousel-346245">
                <ol class="carousel-indicators">
                    <li class="active" data-slide-to="0" data-target="#carousel-346245">
                    </li>
                    <li data-slide-to="1" data-target="#carousel-346245">
                    </li>
                    <li data-slide-to="2" data-target="#carousel-346245">
                    </li>
                </ol>
                <div class="carousel-inner">
                    <div class="item active">
                        <img alt="" src="./image/3.jpg" />
                        <div class="carousel-caption">
                            <h4>
                                First Thumbnail label
                            </h4>
                            <p>
                                Cras justo odio, dapibus ac facilisis in, egestas eget quam. Donec id elit non mi porta gravida at eget metus. Nullam id dolor id nibh ultricies vehicula ut id elit.
                            </p>
                        </div>
                    </div>
                    <div class="item">
                        <img alt="" src="./image/1.jpg" />
                        <div class="carousel-caption">
                            <h4>
                                Second Thumbnail label
                            </h4>
                            <p>
                                Cras justo odio, dapibus ac facilisis in, egestas eget quam. Donec id elit non mi porta gravida at eget metus. Nullam id dolor id nibh ultricies vehicula ut id elit.
                            </p>
                        </div>
                    </div>
                    <div class="item">
                        <img alt="" src="./image/2.jpg" />
                        <div class="carousel-caption">
                            <h4>
                                Third Thumbnail label
                            </h4>
                            <p>
                                Cras justo odio, dapibus ac facilisis in, egestas eget quam. Donec id elit non mi porta gravida at eget metus. Nullam id dolor id nibh ultricies vehicula ut id elit.
                            </p>
                        </div>
                    </div>
                </div> <a class="left carousel-control" href="#carousel-346245" data-slide="prev"><span class="glyphicon glyphicon-chevron-left"></span></a> <a class="right carousel-control" href="#carousel-346245" data-slide="next"><span class="glyphicon glyphicon-chevron-right"></span></a>
            </div>
            <nav class="navbar navbar-default" role="navigation">
                <div class="navbar-header">
                    <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1"> <span class="sr-only">Toggle navigation</span><span class="icon-bar"></span><span class="icon-bar"></span><span class="icon-bar"></span></button> <a class="navbar-brand" href="#">Brand</a>
                </div>

                <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
                    <ul class="nav navbar-nav">
                        <li class="active">
                            <a href="#">Link</a>
                        </li>
                        <li>
                            <a href="#">Link</a>
                        </li>
                        <li class="dropdown">
                            <a href="#" class="dropdown-toggle" data-toggle="dropdown">Dropdown<strong class="caret"></strong></a>
                            <ul class="dropdown-menu">
                                <li>
                                    <a href="#">Action</a>
                                </li>
                                <li>
                                    <a href="#">Another action</a>
                                </li>
                                <li>
                                    <a href="#">Something else here</a>
                                </li>
                                <li class="divider">
                                </li>
                                <li>
                                    <a href="#">Separated link</a>
                                </li>
                                <li class="divider">
                                </li>
                                <li>
                                    <a href="#">One more separated link</a>
                                </li>
                            </ul>
                        </li>
                    </ul>
                    <form class="navbar-form navbar-left" role="search">
                        <div class="form-group">
                            <input type="text" class="form-control" />
                        </div> <button type="submit" class="btn btn-default">Submit</button>
                    </form>
                    <ul class="nav navbar-nav navbar-right">
                        <li>
                            <a href="#">Link</a>
                        </li>
                        <li class="dropdown">
                            <a href="#" class="dropdown-toggle" data-toggle="dropdown">Dropdown<strong class="caret"></strong></a>
                            <ul class="dropdown-menu">
                                <li>
                                    <a href="#">Action</a>
                                </li>
                                <li>
                                    <a href="#">Another action</a>
                                </li>
                                <li>
                                    <a href="#">Something else here</a>
                                </li>
                                <li class="divider">
                                </li>
                                <li>
                                    <a href="#">Separated link</a>
                                </li>
                            </ul>
                        </li>
                    </ul>
                </div>

            </nav>
        </div>
    </div>
    <div class="row clearfix">
        <div class="col-md-4 column">
            <h2>
                批量(少量)导入excel数据至数据库
            </h2>
            <p>
                Donec id elit non mi porta gravida at eget metus. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Etiam porta sem malesuada magna mollis euismod. Donec sed odio dui.
            </p>
            <p>
                <a class="btn" href="${pageContext.request.contextPath}/Inserttable">开始导入 »</a>
            </p>
        </div>
        
    </div>
</div>
</body>
</html>
  1. 控制层
    HandleController
package com.cenhua.dataimport.controller;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
 * 读取Excel文件数据批量插入数据库
 */
@Controller
public class HandleControler {
    public static String sqlcreat = " ";//建表语句
    public static String sqlcreatestart ="create table xyz("; //建表语句开头
    public static String sqlload = " varchar(20)";  //字段属性
    public static String sqlcreateend = " )"; //建表语句结尾
    public static String sqlinsert = " ";//插数语句
    public static  String[] sqlinsertstart = new String[1000000];//动态初始化
    //定义数组不能指定数组长度  String sqlinsertstart[] 数组定义另外一种方式;
    public static String sqlinsertend =" )";  //插数语句结尾
    public static String sqlchar = "  ";
    public static int[] id = new int[1000000] ;//存储整型数据编号
    public static int y = 0;//确定插入语句
    public static int j1 = 0;
    public static int r =0;
    public static int r1 =0;
    @RequestMapping(value = "/handle")
    public ModelAndView handle(HttpServletResponse response){
        int i = 0;
        int j = 0;
        String[] node = new String[10000];
        ModelAndView mav = new ModelAndView();
        ArrayList<String> listFileName = new ArrayList<String>();
        HandleControler ha = new HandleControler();
        String st ="D:\\SSM框架\\DataImport\\target\\dataImport-1.0-SNAPSHOT\\upload\\";
        //获取文件名
        this.getAllFileName(st,listFileName);
        //获取文件夹下文件名
        for(String name:listFileName){
                String s1 = name;
                List<List<String>>  lists = ha.readExcel(s1);
                //展示读取数据作用
                for (List<String> list : lists) {
                    for (String strs : list) {
                        //显示表格列字段名
                        if(i<1){
                            if(strs.contains("工龄")){
                                System.out.println("公龄位于"+r+"列");
                                id[r] = r+1;
                            }else if(strs.contains("行龄")){
                                System.out.println("行龄位于"+r+"列");
                                id[r] = r+1;
                            }else if(strs.contains("年龄")){
                                System.out.println("年龄位于"+r+"列");
                                id[r] = r+1;
                            }
                            r++;//获取列数
                        } else{
                            /*显示全部列字段数值  插入数据代码块  判断插入数据类型
                            r:总列数 利用读取
                            r1:总列数（中间值） 每列循环结束 利用r总列数重新赋值
                            y:插入数据数组序号
                            j1:判断是每列第几个元素  可以结合 id[]数组进行判断 但是循环是个问题
                            j:数组序号*/
                            node[j] = strs;
                                //判断为特殊类型
                                      if(j1==0) {//第一行
                                          if(id[j1]!=j1+1){
                                               sqlinsertstart[y] = "insert into xyz values( ";
                                               sqlinsertstart[y] = sqlinsertstart[y] +"'" +node[j] +"'" + ",";
                                               j++;
                                          }else if(id[j1]==j1+1){
                                              sqlinsertstart[y] = "insert into xyz values( ";
                                              sqlinsertstart[y] = sqlinsertstart[y] +node[j] + ",";
                                              j++;
                                          }
                                      }else if(j1<r1-1){//其余行
                                             if(id[j1]!=j1+1){
                                             sqlinsertstart[y] = sqlinsertstart[y] +"'" + node[j] +"'" +",";
                                             j++;
                                             }else if(id[j1]==j1+1){
                                                 sqlinsertstart[y] = sqlinsertstart[y] + node[j] +",";
                                                 j++;
                                             }
                                      } else if(j1==r1-1){
                                             if(id[j1]!=j1+1) {
                                                 sqlinsertstart[y] = sqlinsertstart[y] + "'" + node[j] + "'" + ")";
                                                 j++;
                                             }else if(id[j1]==j1+1){
                                                 sqlinsertstart[y] = sqlinsertstart[y]  + node[j]  + ")";
                                                 j++;

                                             }
                                      }
                            j1++;
                        }
                    }
                    r1=r;//重新赋值列数 重新循环
                    j1=0;//判断是否是第一个
                    y++;//换插入语句
                    i++;//换列
                }
            //链接数据库  批量插入数据
           try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection c = DriverManager.getConnection( "jdbc:mysql://127.0.0.1/自己的数据库名?serverTimezone=UTC&useSSL=false",//在插入数据时要规定数据格式
                        "账号", "密码");
                Statement ps = null;
                c.setAutoCommit(false);
                ps = c.createStatement();
                for(int l = 1;l<y-1;l++){
                    System.out.println(sqlinsertstart[l]);
                    ps.addBatch(sqlinsertstart[l]);
                }
                ps.executeBatch();
                c.commit();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        mav.setViewName("Success");
        return mav;
    }
    //读取EXCEL文件 利用二维数组进行读取Excel文件
    public static List<List<String>> readExcel(String path) {
        String fileType = path.substring(path.lastIndexOf(".") + 1);//截取文件类型
        // return a list contains many list
        //System.out.println(fileType+"12"+"filetype");
        List<List<String>> lists = new ArrayList<List<String>>();
        //读取excel文件
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }
            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList<String> list = new ArrayList<String>();
                for (Cell cell : row) {
                    //根据不同类型转化成字符串
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    list.add(cell.getStringCellValue());
                }
                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }
    //获取文件路径
    public static void getAllFileName(String path, ArrayList<String> listFileName){
        File file = new File(path);
        String [] names = file.list();
        if(names != null){
            String [] completNames = new String[names.length];
            for(int i=0;i<names.length;i++){
                completNames[i]=path+names[i];
            }
            listFileName.addAll(Arrays.asList(completNames));
        }
    }
    //创建EXCEL文件
    public static Workbook creatExcel(List<List<String>> lists, String[] titles, String name) throws IOException {
        System.out.println(lists);
        //创建新的工作薄
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(name);
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<titles.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }
        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for(int i=0;i<titles.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cs);
        }
        if(lists == null || lists.size() == 0){
            return wb;
        }
        //设置每行每列的值
        for (int i1 = 1; i1 <= lists.size(); i1++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i1);
            for(int j1=0;j1<titles.length;j1++){
                // 在row行上创建一个方格
                Cell cell = row1.createCell(j1);
                cell.setCellValue(lists.get(i1-1).get(j1));
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
}

InsertController

package com.cenhua.dataimport.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class InsertController {
    @RequestMapping(value="/Inserttable")

    public ModelAndView InsertTable(){
        ModelAndView mav = new ModelAndView();
        mav.setViewName("UploadText");
        return mav;

    }
}

本项目通过对Excel表格的读取，根据字段来判断数据类型，之后拼接SQL语句，通过批量运行SQL语句，从而将数据插入至数据库

FtLnnL
关注
  • 0
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值