将Excel表中的数据导入到数据库
网上查到的有参考价值的就一家，自己调试发现可行。感谢原创文章：将Excel中数据导入数据库（一）
1 1 using System; 2 2 using System.Collections.Generic; 3 3 using System.Linq; 4 4 using System.Web; 5 5 //using System.Web.UI; 6 6 //using System.Web.UI.WebControls; 7 7 using System.Data; 8 8 using System.Data.OleDb; 9 9 using System.Configuration; 10 10 using System.Data.SqlClient; 11 11 namespace ConsoleApplication1 12 12 { 13 13 class FileSvr 14 14 { 15 15 /// <summary> 16 16 /// 应用程序的主入口点。 17 17 /// </summary> 18 18 [STAThread] 19 19 static void Main(string[] args) 20 20 { 21 21 FileSvr fileSvr = new FileSvr(); 22 22 System.Data.DataTable dt = fileSvr.GetExcelDatatable("F:\\ExcelToDB1.xls", "mapTable"); 23 23 int count = fileSvr.InsetData(dt); 24 24 Console.WriteLine(count); 25 25 } 26 26 27 27 /// <summary> 28 28 /// Excel数据导入Datable 29 29 /// </summary> 30 30 /// <param name="fileUrl"></param> 31 31 /// <param name="table"></param> 32 32 /// <returns></returns> 33 33 public System.Data.DataTable GetExcelDatatable(string fileUrl, string table) 34 34 { 35 35 //office2007之前 仅支持.xls 36 36 const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';"; 37 37 ////支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据； 38 38 //const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; 39 39 System.Data.DataTable dt = null; 40 40 //建立连接 41 41 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl)); 42 42 try 43 43 { 44 44 //打开连接 45 45 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed) 46 46 { 47 47 conn.Open(); 48 48 } 49 49 50 50 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 51 51 //获取Excel的第一个Sheet名称 52 52 string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); 53 53 //查询sheet中的数据 54 54 string strSql = "select * from [" + sheetName + "]"; 55 55 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); 56 56 DataSet ds = new DataSet(); 57 57 da.Fill(ds, table); 58 58 dt = ds.Tables[0]; 59 59 return dt; 60 60 } 61 61 catch (Exception exc) 62 62 { 63 63 throw exc; 64 64 } 65 65 finally 66 66 { 67 67 conn.Close(); 68 68 conn.Dispose(); 69 69 } 70 70 } 71 71 /// <summary> 72 72 /// 从System.Data.DataTable导入数据到数据库 73 73 /// </summary> 74 74 /// <param name="dt"></param> 75 75 /// <returns></returns> 76 76 public int InsetData(System.Data.DataTable dt) 77 77 { 78 78 int i = 0; 79 79 string words = ""; 80 80 string wordKind = ""; 81 81 string fellingkind = ""; 82 82 string power = ""; 83 83 string polar = ""; 84 84 string assistfellingkind = ""; 85 85 string assistpower = ""; 86 86 string assistpolar = ""; 87 87 foreach (DataRow dr in dt.Rows) 88 88 { 89 89 words = dr["Words"].ToString().Trim(); 90 90 wordKind = dr["wordKind"].ToString().Trim(); 91 91 fellingkind = dr["fellingkind"].ToString().Trim(); 92 92 power = dr["power"].ToString().Trim(); 93 93 polar = dr["polar"].ToString().Trim(); 94 94 assistfellingkind = dr["assistfellingkind"].ToString().Trim(); 95 95 assistpower = dr["assistpower"].ToString().Trim(); 96 96 assistpolar = dr["assistpolar"].ToString().Trim(); 97 97 //sw = string.IsNullOrEmpty(sw) ? "null" : sw; 98 98 //kr = string.IsNullOrEmpty(kr) ? "null" : kr; 99 99 string strSql = string.Format("Insert into tb_MyFellingWords (Words,wordKind,fellingkind,power,polar,assistfellingkind,assistpower,assistpolar) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", words, wordKind, fellingkind, power, polar, assistfellingkind, assistpower, assistpolar); 100 100 101 101 102 102 //连接帐套数据库, 要跟据帐套参数定义创建连接字符串 103 103 string sConn = "Server={0};Database={1};User ID={2};Password={3};Connection TimeOut=180;"; 104 104 sConn = String.Format(sConn, 105 105 "20120906-1046", 106 106 "CSFramework3.Test", 107 107 "sa", 108 108 "sa"); 109 109 SqlConnection sqlConnection = new SqlConnection(sConn); 110 110 try 111 111 { 112 112 // SqlConnection sqlConnection = new SqlConnection(strConnection); 113 113 sqlConnection.Open(); 114 114 SqlCommand sqlCmd = new SqlCommand(); 115 115 sqlCmd.CommandText = strSql; 116 116 sqlCmd.Connection = sqlConnection; 117 117 SqlDataReader sqlDataReader = sqlCmd.ExecuteReader(); 118 118 i++; 119 119 Console.WriteLine(i); 120 120 sqlDataReader.Close(); 121 121 } 122 122 catch (Exception ex) 123 123 { 124 124 throw ex; 125 125 } 126 126 finally 127 127 { 128 128 sqlConnection.Close(); 129 129 } 130 130 //if (opdb.ExcSQL(strSql)) 131 131 // i++; 132 132 } 133 133 return i; 134 134 } 135 135 } 136 136 } 137 137
