提供把Excel里的數據導入到SQL Server 數據庫，前提是Excel里的字段在Sql Server表里都有，不然會出現錯誤。注釋很詳細哦！要引用的命名空間是：
using System.Data.OleDb;
using System.Data.SqlClient;

Code
public?class?ExcelToSQL
{
????//string?SqlConnectionString?=?"Server=(local);Initial?Catalog=Test;Integrated?Security=True";
????public?SqlConnection?sqlcon;????????//創建SQL連接
????public?SqlCommand?sqlcom;??????????//創建SQL命令對象


????public?ExcelToSQL()
????{
????????DataOperation?dataOperation?=?new?DataOperation();?//用到平臺的函數，就是初始化SqlConnection對象
????????DBUnit?dbUnit?=?dataOperation.GetDbUnit();
????????sqlcon?=?(SqlConnection)dbUnit.cnt;
????????if?(sqlcon.State.ToString()?==?"Open")
????????????sqlcon.Close();
????}
????public?int?ImportSql(string?excelPath,?string?tableName)??//導入的Excel的路徑，數據庫里的表名
????{
????????if?(!TableExist(tableName))?//表名是否存在
????????????return?(int)ImportState.tableNameError;

????????DataTable?dt?=?ExcelToDataTable(excelPath);
???????if (dt == null)
??????? {
??????????? return (int)ImportState.excelFormatError;
??????? }
????????ArrayList?tableField?=?GetTableField(tableName);???//表格的列名稱

????????string?columnName?=?"ID,";?//Excel里的列名，增加一個ID列
????????for?(int?i?=?0;?i?<?dt.Columns.Count;?i++)
????????{
????????????columnName?+=?dt.Columns[i].ColumnName?+?",";
????????????string?currentColumn?=?dt.Columns[i].ToString().ToUpper();?//當前列名
????????????for?(int?j?=?0;?j?<?tableField.Count;?j++)
????????????{
????????????????if?(tableField[j].ToString().ToUpper()?==?dt.Columns[i].ToString().ToUpper())
????????????????????break;???//跳出本層和上一層循環，continue是跳出本層循環，如果用continue，會繼續執行j++
????????????????//Excel里的字段必須在Sql中都有
????????????????if?((tableField[j].ToString().ToUpper()?!=?dt.Columns[i].ToString().ToUpper())?&&?j?==?tableField.Count?-?1)
????????????????????return?(int)ImportState.fieldMatchError;
????????????}
????????}
????????int?m?=?columnName.LastIndexOf(',');
????????columnName?=?columnName.Remove(m);??//移除最后一個逗號

????????sqlcom?=?new?SqlCommand();
????????sqlcom.Connection?=?sqlcon;
????????sqlcon.Open();
????????sqlcom.CommandType?=?CommandType.Text;

????????for?(int?h?=?0;?h?<?dt.Rows.Count;?h++)
????????{
????????????string?value?=?"'"?+?System.Guid.NewGuid().ToString()?+?"'"?+?",";
????????????for?(int?k?=?0;?k?<?dt.Columns.Count;?k++)?//根據列名得到值
????????????{
????????????????value?+=?"'"?+?dt.Rows[h][k].ToString()?+?"'"?+?",";
????????????}
????????????value?=?value.Remove(0,?1);
????????????int?n?=?value.LastIndexOf(',');
????????????value?=?value.Remove(n);????//移除最后一個逗號
????????????n?=?value.LastIndexOf("'");
????????????value?=?value.Remove(n);

????????????try
????????????{
????????????????string?sql?=?"insert?into?"?+?tableName?+?"("?+?columnName?+?")?values('"?+?value?+?"')";
????????????????sqlcom.CommandText?=?sql;
????????????????string?sss?=?sqlcom.ExecuteNonQuery().ToString();
????????????}
????????????catch?(Exception?err)
????????????{
????????????????string?erroe?=?err.Message;
????????????????return?(int)ImportState.dataTypeError;
????????????}
????????}
????????sqlcon.Close();
????????sqlcom.Dispose();

????????return?(int)ImportState.right;
????}
????public?DataTable?ExcelToDataTable(string?excelPath)??//把Excel里的數據轉換為DataTable，并返回DataTable
????{
?????????? string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
??????? System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
??????? string strCom = "SELECT * FROM [Sheet1$]";
??????? DataTable dt;
??????? try
??????? {
??????????? Conn.Open();
??????????? System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
??????????? DataSet ds = new DataSet();
??????????? myCommand.Fill(ds, "[Sheet1$]");
??????????? Conn.Close();
??????????? dt = ds.Tables[0];
??????? }
??????? catch(Exception err)
??????? {
??????????? return null;
??????? }
??????? return dt;
????}
????public?bool?TableExist(string?tableName)?//查看數據庫里是否有此表名
????{
????????sqlcom?=?new?SqlCommand();
????????sqlcom.Connection?=?sqlcon;
????????sqlcom.CommandType?=?CommandType.Text;
????????try
????????{
????????????sqlcon.Open();
????????????string?sql?=?"select?name?from?sysobjects?where?type='u'";
????????????sqlcom.CommandText?=?sql;
????????????SqlDataReader?sqldr?=?sqlcom.ExecuteReader();
????????????while?(sqldr.Read())
????????????{
????????????????if?(sqldr.GetString(0).ToUpper()?==?tableName.ToUpper())
????????????????????return?true;
????????????}
????????}
????????catch?{?return?false;?}
????????finally
????????{
????????????sqlcon.Close();
????????}
????????return?false;
????}
????public?ArrayList?GetTableField(string?tableName)??//得到數據庫某一個表中的所有字段
????{
????????ArrayList?al?=?new?ArrayList();
????????sqlcom?=?new?SqlCommand();
????????sqlcom.Connection?=?sqlcon;
????????sqlcom.CommandType?=?CommandType.Text;
????????try
????????{
????????????sqlcon.Open();
????????????string?sql?=?"SELECT?b.name?FROM?sysobjects?a?INNER?JOIN?syscolumns?b?ON?a.id?=?b.id?WHERE?(a.name?=?'"?+?tableName?+?"')";
????????????sqlcom.CommandText?=?sql;
????????????SqlDataReader?sqldr?=?sqlcom.ExecuteReader();
????????????while?(sqldr.Read())
????????????{
????????????????al.Add(sqldr.GetString(0));
????????????}
????????}
????????finally
????????{
????????????sqlcon.Close();
????????}
????????return?al;?//返回的是表中的字段
????}
????public?enum?ImportState
????{
????????right?=?1,?//成功
????????tableNameError?=?2,//表名不存在
????????fieldMatchError?=?3,//excel里的字段和數據庫表里的字段不匹配
????????dataTypeError?=?4,?//轉換數據類型時發生錯誤
??????? excelFormatError=5,//Excel格式不能讀取
????}
????public?void?Alert(string?str)
????{
????????HttpContext.Current.Response.Write("<script?language='javascript'>alert('"?+?str?+?"');</script>");
????}
}

?

頁面調用代碼：

Code
?protected?void?btnExport_Click(object?sender,?EventArgs?e)
????{
????????string?filepath?=?this.fileUpload.PostedFile.FileName;
????????if?(filepath?!=?"")
????????{
????????????if?(this.txtTableName.Text?!=?"")
????????????{
????????????????if?(filepath.Contains("xls"))
????????????????{
????????????????????int?result?=?ets.ImportSql(filepath,?this.txtTableName.Text);

????????????????????if?(result?==?(int)ExcelToSQL.ImportState.tableNameError)
????????????????????????ets.Alert("此表名在數據中不存在！");
???????????????????else if(result==(int)ExcelToSQL.ImportState.excelFormatError)
??????????????????????? ets.Alert("Excel格式不能正確讀取！");
????????????????????else?if?(result?==?(int)ExcelToSQL.ImportState.fieldMatchError)
????????????????????????ets.Alert("Excel里的字段和Sql?Server里的字段不匹配！");
????????????????????else?if(result==(int)ExcelToSQL.ImportState.dataTypeError)
????????????????????????ets.Alert("轉換數據類型時發生錯誤!");
????????????????????else?if?(result?==?(int)ExcelToSQL.ImportState.right)
????????????????????{
????????????????????????ets.Alert("導入成功");
????????????????????}
????????????????}
????????????????else?ets.Alert("上傳的文件類型必須為excel文件！");
????????????}
????????????else?ets.Alert("表名不能為空！");
????????}
????????else?ets.Alert("沒有選擇要上傳的文件！");
????}

?

前臺代碼：

Code
?<form?id="form1"?runat="server">
????<div>
????????<asp:Label?ID="Label1"?runat="server"?Text="路徑"></asp:Label>
????????&nbsp;<asp:FileUpload?ID="fileUpload"?runat="server"?Width="443px"?/><br?/>
????????<asp:Label?ID="Label2"?runat="server"?Text="數據庫表名稱"></asp:Label>&nbsp;
????????<asp:TextBox?ID="txtTableName"?runat="server"></asp:TextBox><br?/>
????????<asp:Button?ID="btnExport"?runat="server"?Text="導入到SQL"?OnClick="btnExport_Click"?/>
????</div>
????</form>

轉載于:https://www.cnblogs.com/lhking/archive/2009/06/08/1499002.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的ASP.NET Excel导入到SQL Server数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。