C# 从Excel 批量导入数据库
最近遇到了关于 C# MVC 批量添加数据的问题，解决后就自己写了一个未完成的小Demo
不管什么编程语言都会提供操作Excel文件的方式，C#操作Excel主要有以下几种方式：
1.Excel
说明：利用Office 的Excel组件来操作excel文件
优点：能够完全操作Excel文件，生成丰富文件内容
缺点：需要电脑安装Excel,会启动Excel进程这在web上很不方便
2.OpenXML
说明：一个操作字处理文档的组件包括Excel
优点：能够操作操作Excel2007版本文件
缺点：只能够操作Excel2007文件
3.NPOI
说明：一个开源的Excel读写库
优点：不需要安装Excel
缺点：只能够操作Excel2003文档，对文档内容控制不完全
4.OleDb
说明：使用Microsoft Jet 提供程序用于连接到 Excel 工作簿,将Excel文件作为数据源来读写
优点：简单快速,能够操作高版本Excel
缺点：只能够进行有限的操作(读、写)
今天学习使用OleDb操作Excel文件
连接字符串：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'
provider：表示提供程序名称
Data Source：这里填写Excel文件的路径
Extended Properties：设置Excel的特殊属性
Extended Properties 取值：
Excel 8.0 针对Excel2000及以上版本，Excel5.0 针对Excel97。
HDR=Yes 表示第一行包含列名,在计算行数时就不包含第一行
IMEX 0:导入模式,1:导出模式:2混合模式
1.读取Excel
if (openFileDialog1.ShowDialog() == DialogResult.OK) { String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+openFileDialog1.FileName+";"+ "Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { //类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表] ole_cmd.CommandText = "select * from [Sheet1$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd); DataSet ds = new DataSet(); adapter.Fill(ds, "Sheet1"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MessageBox.Show(ds.Tables[0].Rows[i]["商家名称"].ToString()); } } } }
2.获取工作簿中所有的工作表
if (openFileDialog1.ShowDialog() == DialogResult.OK) { String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+openFileDialog1.FileName+";"+ "Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { DataTable tb = ole_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in tb.Rows) { MessageBox.Show(row["TABLE_NAME"].ToString()); } } } }
3.写入数据到Excel表
if (openFileDialog1.ShowDialog() == DialogResult.OK) { String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+openFileDialog1.FileName+";"+ "Extended Properties=Excel 8.0;"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { ole_cmd.CommandText = "insert into [Sheet1$](商户ID,商家名称)values('DJ001','点击科技')"; ole_cmd.ExecuteNonQuery(); MessageBox.Show("数据插入成功......"); } } }
4.创建Excel文件并写入数据
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=d:\\excel1.xls;" + "Extended Properties=Excel 8.0;"; //实例化一个Oledbconnection类(实现了IDisposable,要using) using (OleDbConnection ole_conn = new OleDbConnection(sConnectionString)) { ole_conn.Open(); using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { ole_cmd.CommandText = "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)"; ole_cmd.ExecuteNonQuery(); ole_cmd.CommandText = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','点击科技')"; ole_cmd.ExecuteNonQuery(); MessageBox.Show("生成Excel文件成功并写入一条数据......"); } }
1 首先 我们 在前台进行文件的上传
<div id="bulkDlg" > <div class="col-xs-12"> <form class="form-horizontal" id="bulkForm" enctype="multipart/form-data"> <div class="form-group"> </div> <div class="form-group"> <label class="col-sm-3 control-label no-padding-right" for=""> 上传文件 </label> <div class="col-sm-9"> <input type="file" id="BulkXls" name="BulkXls" /> </div> <div> <input type="button" id="sub" value="提交" /> </div> </div> </form> </div> </div>
<script> $("#sub").click(function () { var formData = new FormData(); formData.append("BulkXls", document.getElementById("BulkXls").files[0]); $.ajax({ url: "/Home/UploadXls", type: "POST", data: formData, /** *必须false才会自动加上正确的Content-Type */ contentType: false, /** * 必须false才会避开jQuery对 formdata 的默认处理 * XMLHttpRequest会对 formdata 进行正确的处理 */ processData: false, success: function (data) { //执行成功回调函数 }, error: function () { } }); }) </script>
注意：在我们用ajax提交的时候，这里一定得用$.ajax, 不能用$.post（本人试过，post提交为成功！）contentType 和 processData 一定要有 并且必须为false 还有就是 FormData支持高版本浏览器 低版本浏览器（IE8以上）
2 后台代码
定义一个 方法来接受 前台传过来的文件，以及解析Excel文件中的内容，并转换为Dataset
[HttpPost]
public string UploadXls() { try { HttpPostedFileBase file = HttpContext.Request.Files["BulkXls"]; if (file == null) { return "请选择文件"; } var ext = Path.GetExtension(file.FileName); var exts = ",.xls,.xlsx,"; if (!exts.Contains("," + ext + ",")) { return "请上传.xls,.xlsx文件"; } //上传文件 var path = HttpContext.Request.MapPath("~/"); var dir = "/Upload_Files/xls/"; if (!Directory.Exists(path + dir)) { Directory.CreateDirectory(path + dir); } //保存文件 var fullPath = path + dir + "hotels" + Path.GetExtension(file.FileName); file.SaveAs(fullPath); var filePath = Server.MapPath("~/Upload_Files/xls/" + "hotels" + Path.GetExtension(file.FileName)); // 读取Excel文件到DataSet中 string connStr = ""; string fileType = System.IO.Path.GetExtension(filePath); if (!string.IsNullOrEmpty(fileType)) {
//支持文件格式不一样，Excel有 97-2003及2007格式 分别为 .xls和.xlsx if (fileType == ".xls") { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; } else { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; } string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null; OleDbDataAdapter da = null; DataSet ds = new DataSet(); try { // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); // 初始化适配器 da = new OleDbDataAdapter(); da.SelectCommand = new OleDbCommand(String.Format(sql_F, "Sheet1$"), conn); DataSet dsItem = new DataSet(); da.Fill(dsItem, "t"); ds.Tables.Add(dsItem.Tables[0].Copy()); //这里的for是循环DataSet中的值并向数据库中插入 for (int i = 0; i < dsItem.Tables[0].Rows.Count; i++) {
var hotelName = dsItem.Tables[0];
//比如我们导入的 Excel表中有 四列 if (!_HotelRepository.IsExist(u => u.HotelName == hotelName)) {
//实例化一个User对象并赋值
User model= new User();
//这里是Excel表格中对应的列 model.UserName= dsItem.Tables[0].Rows[i]["用户名"].ToString();// model.Account= dsItem.Tables[0].Rows[i]["账号"].ToString(); model.Password= dsItem.Tables[0].Rows[i]["密码"].ToString();
//执行插入的方法 _UserRepository.Add(model); } } } catch (Exception ex) { } finally { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); da.Dispose(); conn.Dispose(); } } } } catch (Exception ex) { } return ""; }
具体的像数据库插入数据，这里就不多说了，方法有很多
注意：在后台读取解析Excel文件的时候如果遇到 “未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序。” 遇到这个问题的时候，请自行百度下载一个 名为 AccessDatabaseEngine.exe的插件 百度直接搜索 “未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序。” 也行
