C# 将excel文件导入到SqlServer数据库

川长思鸟来 2022-12-23 13:27 154阅读 0赞

项目结构（很标准的三层架构）：  
![在这里插入图片描述][2020112515312734.png_pic_center]

## 一、做一些准备 ##

1.  这里上传文件用到的控件是webuploader，下载地址：[http://fex.baidu.com/webuploader/][http_fex.baidu.com_webuploader]  
    webuploader的使用方法：[https://www.jianshu.com/p/005341448bd0][https_www.jianshu.com_p_005341448bd0]
2.  如果需要使用到easyUI，下载地址：[https://www.jeasyui.cn/ ][https_www.jeasyui.cn_]，使用方法也在这里
3.  导入ExcelDataReader和ExcelDataReader.DatasSet![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 1]
4.  导入模板，记得与数据库的内容对应  
    （1）我的excel模板，注意：保留一张sheet  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 2]  
    （2）数据库（其实这里是不允许为空的，因为没有对excel表中为空的数据进行处理，因为注重的不是这个，所以没有做处理。自己添加约束即可。）  
    ![在这里插入图片描述][20201125153909386.png_pic_center]

## 二、正式开始 ##

**注：为了贴图方便，顺序可能和实际编写代码的顺序不一致**

1.  下面是前端页面的代码。标明了导入必须的代码以及非必须的代码，可以根据需要查看。

<!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title></title>
        <link href="jquery-easyui-1.8.6/themes/default/easyui.css" rel="stylesheet" />
        <link href="jquery-easyui-1.8.6/themes/icon.css" rel="stylesheet" />
        <script src="jquery-easyui-1.8.6/jquery.min.js"></script>
        <script src="jquery-easyui-1.8.6/jquery.easyui.min.js"></script>
        <link href="webuploader-0.1.5/webuploader.css" rel="stylesheet" />
        <script src="webuploader-0.1.5/webuploader.js"></script>
        <style> /*修改webuploader的样式*/ #picker { display: inline-block; line-height: 1.428571429; vertical-align: middle; margin: 0 1px 0 0; width: 90px; } #picker .webuploader-pick { padding: 0; display: block; } </style>
    </head>
    <body>
        <a id="picker" href="#" plain="true" class="easyui-linkbutton l-btn l-btn-plain">导入学员</a>
        <table id="dg"></table>
    </body>
    </html>
    <script> $(function () { ImportExcelData(); //下面内容只要导入非必须。只调用上面的函数就可。 $("#dg").datagrid({ nowrap: false, autoRowHeight: true, striped: true, collapsible: false, fit: true, fitColumns: false, singleSelect: true, remoteSort: false, columns: [[ { field: 'name', title: '姓名', width: 100 }, { field: 'gender', title: '姓别', width: 100, formatter: function (value, row, index) { if (value == 0) { return value = "男"; } else { return value = "女"; } } }, { field: 'idcardnum', title: '身份证号', width: 300 }, { field: 'address', title: '地址', width: 300 }, { field: 'phone', title: '电话号码', width: 300 }, ]], pagination: false, rownumbers: false, }); BindData(); }) //该方法只要导入非必要，是用来绑定数据到datagrid的 function BindData() { $.post("InAndOut.ashx", { type: 'GetDataList' }, function (data) { $("#dg").datagrid("loadData", data.list); }, 'json'); } //导入 function ImportExcelData() { //初始化webuploader控件 var uploader = WebUploader.create({ auto: true,// 选完文件后，是否自动上传。 swf: "webuploader-0.1.5/Uploader.swf",// swf文件路径 server: 'InAndOut.ashx?type=UploadImportExcel',// 文件接收服务端。 //dnd: '.upload-container', pick: '#picker',// 内部根据当前运行是创建，可能是input元素，也可能是flash. 这里是div的id multiple: false, // 选择多个 chunked: true,// 开起分片上传。 method: 'POST', // 文件上传方式，POST或者GET。 fileSizeLimit: 1024 * 1024 * 100 * 10, //验证文件总大小是否超出限制, 超出则不允许加入队列。 fileSingleSizeLimit: 1024 * 1024 * 100, //验证单个文件大小是否超出限制, 超出则不允许加入队列。 //fileVal: 'epub', // [默认值：'file'] 设置文件上传域的name。 }); //上传成功后的操作 uploader.on('uploadSuccess', function (file, json) { if (json.code == 1) { BindData(); } else { alert(json.msg); return false; } }); } </script>

**再次提醒！！这里和实际编写代码顺序不一样，这里相当于程序实际运行起来的调用顺序，所以会有一些小问题，继续看下去就好啦**

![在这里插入图片描述][20201125160408370.png_pic_center]

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace Model
    { 
        //和数据库的字段一一对应
        public class InAndOutModel
        { 
            public int id {  get; set; }
            public string name {  get; set; }
            public int gender {  get; set; }
            public string idcardnum {  get; set; }
            public string address {  get; set; }
            public string phone {  get; set; }
        }
    }

1.  ![在这里插入图片描述][20201125154719889.png_pic_center]

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Reflection;
    using System.Web;
    using Model;
    using BLL;
    using System.IO;
    using System.Data;
    using ExcelDataReader;
    
    namespace 导入导出
    { 
        /// <summary>
        /// InAndOut 的摘要说明
        /// </summary>
        public class InAndOut : IHttpHandler
        { 
    
            public void ProcessRequest(HttpContext context)
            { 
                context.Response.ContentType = "text/plain";
                //这里是用委托调用对应方法
                var type = context.Request["type"];
                if(!string.IsNullOrEmpty(type))
                { 
                    MethodInfo method = this.GetType().GetMethod(type);
                    if(method != null)
                    { 
                        method.Invoke(this, null);
                    }
                }
            }
    
            /// <summary>
            /// 获取列表，非必须
            /// </summary>
            public void GetDataList()
            { 
                InAndOutBll bll = new InAndOutBll();
                List<InAndOutModel> list = new List<InAndOutModel>();
                list = bll.GetList("");
                if(list != null && list.Count > 0)
                { 
                    var item = new
                    { 
                        code = 1,
                        list = list
                    };
                    //这里还需要引入一个人Newtonsoft包
                    HttpContext.Current.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(item));
                }
                else
                { 
                    var item = new
                    { 
                        code = 0,
                        msg = "当前没有数据可以绑定"
                    };
                    HttpContext.Current.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(item));
                }
            }
    
            /// <summary>
            /// 重要步骤
            /// </summary>
            public void UploadImportExcel()
            { 
                //1.获取上传的文件
                HttpFileCollection files = HttpContext.Current.Request.Files;
                //2.获取要保存文件的路径
                string path = HttpContext.Current.Server.MapPath("~/upload/");
                //3.判断该路径文件夹是否存在
                if (!Directory.Exists(path))
                { 
                    //3.1不存在就创建
                    Directory.CreateDirectory(path);
                }
                //4.获取当前时间戳，用来命名确保文件名字的唯一性
                string time = DateTime.Now.Ticks.ToString();
                //5.获取上传文件的文件名（主要是为了获取后缀名），如果介意新的命名太长可以只获取后缀名
                string filename = files[0].FileName;
                //6.得到完整的命名
                string name = path + time + filename;
                //7.保存
                files[0].SaveAs(name);
                //8.将excel取出转化为datatable
                var tables = GetExcelSheets(name);
                var datatable = GetExcelSheet(name, tables[0]);
                //9.判断datatable中是否有数据
                if(datatable.Rows.Count < 2)
                { 
                    var item = new
                    { 
                        code = 0,
                        msg = "表中没有数据，请检查"
                    };
                    HttpContext.Current.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(item));
                }
                //9.2 有数据进行的操作
                else
                { 
                    InAndOutBll bll = new InAndOutBll();
                    //因为涉及到多条数据的插入，所以需要用到事务。
                    bool res = bll.Trans_Insert(datatable);
                    if(res)
                    { 
                        var item = new
                        { 
                            code = 1,
                            msg = "插入成功"
                        };
                        HttpContext.Current.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(item));
                    }
                    else
                    { 
                        var item = new
                        { 
                            code = 0,
                            msg = "插入失败"
                        };
                        HttpContext.Current.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(item));
                    }
                }
            }
    
            #region 获取工作簿
            /// <summary>
            /// 获取所有的sheet名称
            /// </summary>
            /// <param name="path"></param>
            /// <returns></returns>
            public static List<string> GetExcelSheets(string path)
            { 
                List<string> TableNames = new List<string>();
                DataSet ds = GetDataForExcel(path);
                for (int i = ds.Tables.Count - 1; i >= 0; i--)
                { 
                    TableNames.Add(ds.Tables[i].TableName);
                }
                return TableNames;
            }
            /// <summary>
            /// sheetName 获取单个sheet数据 
            /// </summary>
            /// <param name="path"></param>
            /// <param name="sheetname"></param>
            /// <returns></returns>
            public static System.Data.DataTable GetExcelSheet(string path, string sheetname)
            { 
                System.Data.DataTable dt = new System.Data.DataTable();
                DataSet ds = GetDataForExcel(path);
                for (int i = 0; i < ds.Tables.Count; i++)
                { 
                    if (ds.Tables[i].TableName == sheetname)
                    { 
                        dt = ds.Tables[i];
                        break;
                    }
                }
                return dt;
            }
    
            /// <summary>
            /// 获取整个excel 数据
            /// </summary>
            /// <param name="filePath"></param>
            /// <returns></returns>
            public static DataSet GetDataForExcel(string filePath)
            { 
                using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                { 
                    using (var reader = ExcelReaderFactory.CreateReader(stream))
                    { 
                        DataSet result = reader.AsDataSet();
                        return result;
                    }
                }
            }
            #endregion
    
            public bool IsReusable
            { 
                get
                { 
                    return false;
                }
            }
        }
    }

![在这里插入图片描述][20201125155619794.png_pic_center]

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Model;
    using DAL;
    using System.Data;
    using DBUtility;
    
    namespace BLL
    { 
        public class InAndOutBll
        { 
            private readonly InAndOutDal dal = new InAndOutDal();
            /// <summary>
            /// 获取列表
            /// </summary>
            /// <param name="condition">查询条件</param>
            /// <returns></returns>
            public List<InAndOutModel> GetList(string condition)
            { 
                return dal.GetList(condition);
            }
            /// <summary>
            /// 多条数据插入
            /// </summary>
            /// <param name="dt">需要插入的datatable</param>
            /// <returns></returns>
            public bool Trans_Insert(DataTable dt)
            { 
                bool istrue = true;
                //这里的SQLServerHelper是数据库帮助类，网上有很多可以用
                SQLServerHelper.Transaction transaction = new SQLServerHelper.Transaction();
                //遍历这个表的每一行
                foreach(DataRow dr in dt.Rows)
                { 
                    if(dr["Column0"].ToString() == "姓名")
                    { 
                        continue;
                    }
                    if(istrue)
                    { 
                        istrue = dal.Trans_Insert(transaction, dr) > 0;
                    }
                    else
                    { 
                        istrue = false;
                        break;
                    }
                }
                //所有行都成功插入，提交事务
                if(istrue)
                { 
                    transaction.Commit();
                }
                //否则回滚
                else
                { 
                    transaction.RollBack();
                }
                return istrue;
            }
        }
    }

调试的时候可以看到datatable的结构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 3]

![在这里插入图片描述][20201125155709449.png_pic_center]

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Model;
    using DBUtility;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace DAL
    { 
        public class InAndOutDal
        { 
            /// <summary>
            /// 获取列表，导入非必须
            /// </summary>
            /// <param name="condition">查询条件</param>
            /// <returns></returns>
            public List<InAndOutModel> GetList(string condition)
            { 
                List<InAndOutModel> list = new List<InAndOutModel>();
                string sql = "select * from InAndOut where 1 = 1 " + condition;
                DataSet ds = SQLServerHelper.Query(sql);
                if(ds != null && ds.Tables.Count > 0)
                { 
                    DataTable dt = ds.Tables[0];
                    if(dt.Rows.Count > 0)
                    { 
                        foreach (DataRow dr in dt.Rows)
                        { 
                            list.Add(this.GetModel(dr));
                        }
                    }
                }
                return list;
            }
            /// <summary>
            /// 多条数据插入
            /// </summary>
            /// <param name="transaction">事务</param>
            /// <param name="dr">要插入的行</param>
            /// <returns></returns>
            public int Trans_Insert(SQLServerHelper.Transaction transaction, DataRow dr)
            { 
                InAndOutModel model = new InAndOutModel();
                model = this.ExcelDrGetModel(dr);
                string sql = "insert into InAndOut(name, gender, idcardnum, address, phone) values (@name, @gender, @idcardnum, @address, @phone);select @@IDENTITY";
                SqlParameter[] parameters = { 
                    new SqlParameter("@name", SqlDbType.NVarChar,10),
                    new SqlParameter("@gender", SqlDbType.Int, 4),
                    new SqlParameter("@idcardnum", SqlDbType.NVarChar, 18),
                    new SqlParameter("@address", SqlDbType.NVarChar, 20),
                    new SqlParameter("@phone", SqlDbType.NVarChar, 12),
                };
                parameters[0].Value = model.name;
                parameters[1].Value = model.gender;
                parameters[2].Value = model.idcardnum;
                parameters[3].Value = model.address;
                parameters[4].Value = model.phone;
                object obj = SQLServerHelper.GetTrObject(transaction, sql, parameters);
                if(obj == null)
                { 
                    return 0;
                }
                else
                { 
                    return Convert.ToInt32(obj);
                }
            }
    
            public InAndOutModel GetModel(DataRow dr)
            { 
                InAndOutModel model = new InAndOutModel();
                if (dr != null)
                { 
                    if(dr["name"] != null)
                    { 
                        model.name = dr["name"].ToString();
                    }
                    if(dr["gender"] != null)
                    { 
                        model.gender = Convert.ToInt32(dr["gender"]);
                    }
                    if(dr["idcardnum"] != null)
                    { 
                        model.idcardnum = dr["idcardnum"].ToString();
                    }
                    if(dr["address"] != null)
                    { 
                        model.address = dr["address"].ToString();
                    }
                    if(dr["phone"] != null)
                    { 
                        model.phone = dr["phone"].ToString();
                    }
                }
                return model;
            }
    
            public InAndOutModel ExcelDrGetModel(DataRow dr)
            { 
                InAndOutModel model = new InAndOutModel();
                if (dr != null)
                { 
    //这里用dr[0]是根据dr结构的，调试可以看到的哦
                    if (dr[0] != null)
                    { 
                        model.name = dr[0].ToString();
                    }
                    if (dr[1] != null)
                    { 
                        if(dr[1].ToString() == "男")
                        { 
                            dr[1] = 0;
                        }
                        else
                        { 
                            dr[1] = 1;
                        }
                        model.gender = Convert.ToInt32(dr[1]);
                    }
                    if (dr[2] != null)
                    { 
                        model.idcardnum = dr[2].ToString();
                    }
                    if (dr[3] != null)
                    { 
                        model.address = dr[3].ToString();
                    }
                    if (dr[4] != null)
                    { 
                        model.phone = dr[4].ToString();
                    }
                }
                return model;
            }
        }
    }

**如果你使用了easyUI进行数据绑定，那么运行会有这样的效果**

*  网页  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 4]
 *  数据库  
    ![在这里插入图片描述][20201125160753991.png_pic_center]

**因为这里主要将excel文件导入数据库，所以很多细节没有处理，比如判空、判重等等等，需要自己添加限制条件哦。**

[2020112515312734.png_pic_center]: /images/20221120/e72ab69ef4fa4cf5a920c6ebba8e4523.png
[http_fex.baidu.com_webuploader]: http://fex.baidu.com/webuploader/
[https_www.jianshu.com_p_005341448bd0]: https://www.jianshu.com/p/005341448bd0
[https_www.jeasyui.cn_]: https://www.jeasyui.cn/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/865d5efe208f49088037a8c1729b72a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201125153727294.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201125153846419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky,size_16,color_FFFFFF,t_70#pic_center
[20201125153909386.png_pic_center]: https://img-blog.csdnimg.cn/20201125153909386.png#pic_center
[20201125160408370.png_pic_center]: https://img-blog.csdnimg.cn/20201125160408370.png#pic_center
[20201125154719889.png_pic_center]: https://img-blog.csdnimg.cn/20201125154719889.png#pic_center
[20201125155619794.png_pic_center]: https://img-blog.csdnimg.cn/20201125155619794.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201125160549966.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky,size_16,color_FFFFFF,t_70#pic_center
[20201125155709449.png_pic_center]: https://img-blog.csdnimg.cn/20201125155709449.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201125175024192.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNDIyNjky,size_16,color_FFFFFF,t_70#pic_center
[20201125160753991.png_pic_center]: https://img-blog.csdnimg.cn/20201125160753991.png#pic_center