將Excle數據導入到數據庫

假如Excel中的數據如下：

　　數據庫建表如下：

　　其中Id為自增字段：

?

代碼：

  1 using System;2 using System.Collections.Generic;3 using System.Linq;4 using System.Web;5 using System.Web.UI;6 using System.Web.UI.WebControls;7 using System.Data;8 using System.Data.OleDb;9 using System.Configuration;10 using System.Data.SqlClient;11 12 namespace InExcelOutExcel13 {14     public partial class ExcelToDB : System.Web.UI.Page15     {16         protected void Page_Load(object sender, EventArgs e)17         {18             FileSvr fileSvr = new FileSvr();19             System.Data.DataTable dt = fileSvr.GetExcelDatatable("C:\\Users\\NewSpring\\Desktop\\Demo\\InExcelOutExcel\\InExcelOutExcel\\excel\\ExcelToDB.xlsx", "mapTable");20             fileSvr.InsetData(dt);21         }22     }23     class FileSvr24     {25         /// <summary>26         /// Excel數據導入Datable27         /// </summary>28         /// <param name="fileUrl"></param>29         /// <param name="table"></param>30         /// <returns></returns>31         public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)32         {33             //office2007之前 僅支持.xls34             //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";35             //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是標題，不是數據；36             const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";37 38             System.Data.DataTable dt = null;39             //建立連接40             OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));41             try42             {43                 //打開連接44                 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)45                 {46                     conn.Open();47                 }48 49 50                 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);51 52                 //獲取Excel的第一個Sheet名稱53                 string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();54 55                 //查詢sheet中的數據56                 string strSql = "select * from [" + sheetName + "]";57                 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);58                 DataSet ds = new DataSet();59                 da.Fill(ds, table);60                 dt = ds.Tables[0];61 62                 return dt;63             }64             catch (Exception exc)65             {66                 throw exc;67             }68             finally69             {70                 conn.Close();71                 conn.Dispose();72             }73 74         }75 76         /// <summary>77         /// 從System.Data.DataTable導入數據到數據庫78         /// </summary>79         /// <param name="dt"></param>80         /// <returns></returns>81         public int InsetData(System.Data.DataTable dt)82         {83             int i = 0;84             string lng = "";85             string lat = "";86             string offsetLNG = "";87             string offsetLAT = "";88 89             foreach (DataRow dr in dt.Rows)90             {91                 lng = dr["LNG"].ToString().Trim();92                 lat = dr["LAT"].ToString().Trim();93                 offsetLNG = dr["OFFSET_LNG"].ToString().Trim();94                 offsetLAT = dr["OFFSET_LAT"].ToString().Trim();95 96                 //sw = string.IsNullOrEmpty(sw) ? "null" : sw;97                 //kr = string.IsNullOrEmpty(kr) ? "null" : kr;98 99                 string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values ('{0}','{1}',{2},{3})", lng, lat, offsetLNG, offsetLAT);
100 
101                 string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString();
102                 SqlConnection sqlConnection = new SqlConnection(strConnection);
103                 try
104                 {
105                     // SqlConnection sqlConnection = new SqlConnection(strConnection);
106                     sqlConnection.Open();
107                     SqlCommand sqlCmd = new SqlCommand();
108                     sqlCmd.CommandText = strSql;
109                     sqlCmd.Connection = sqlConnection;
110                     SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
111                     i++;
112                     sqlDataReader.Close();
113                 }
114                 catch (Exception ex)
115                 {
116                     throw ex;
117                 }
118                 finally
119                 {
120                     sqlConnection.Close();
121 
122                 }
123                 //if (opdb.ExcSQL(strSql))
124                 //    i++;
125             }
126             return i;
127         }
128     }
129 }

運行結果：