(C#)excel數(shù)據(jù)導(dǎo)入SqlServer中
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>無標(biāo)題頁</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FuloadExcelFile" runat="server" Height="25px" Width="473px" />
<asp:Button ID="btnReadDataTo" runat="server" Text="導(dǎo)入" On
</form>
</body>
</html>
protected void btnReadDataTo_Click(object sender, EventArgs e) {
if (FuloadExcelFile.FileName == "")
return; string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName); if (fileExt != ".xls")//必須是EXCEL文件 return;
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路徑 DataTable dt = new DataTable(); dt = CallExcel(filepath);//返回EXCEL文件的數(shù)據(jù) if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//導(dǎo)入數(shù)據(jù)庫 {} } protected DataTable CallExcel(string filepath) { OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0"); con.Open(); string sql = "select * from [Sheet1$]";//選擇第一個數(shù)據(jù)SHEET //OleDbCommand command = new OleDbCommand(sql, con); //OleDbDataReader reader = command.ExecuteReader(); //if (reader.Read()) //{ // reader[0].ToString();//直接讀出數(shù)據(jù) //} OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con); DataTable dt = new DataTable(); adapter.Fill(dt); //reader.Close(); //command.Dispose(); con.Close(); con.Dispose(); return dt; } protected bool InsertSQLServer(DataTable dt,string dataname) { string strCon = @"User ID=SA;Initial Catalog=CarManage;Data Source=(local);Password=SA";//無數(shù)據(jù)庫名連接 string strTest="testTable"; try { SqlConnection con = new SqlConnection(strCon);//創(chuàng)建數(shù)據(jù)庫 con.Open(); string strSQL = "IF NOT EXISTS(SELECT name FROM sysobjects WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]"; SqlCommand command = new SqlCommand(strSQL, con); command.ExecuteNonQuery(); //創(chuàng)建數(shù)據(jù)庫表 strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sysobjects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " ("; string strColumn=string.Empty; for (int i = 0; i < dt.Columns.Count; i++) { strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),"; strColumn+=dt.Columns[i].ColumnName+","; } strSQL += " )"; SqlCommand newcom = new SqlCommand(strSQL, con); newcom.ExecuteNonQuery(); //插入數(shù)據(jù) strColumn=strColumn.Substring(0,strColumn.Length-1); for(int i=0;i<dt.Rows.Count;i++) { strSQL = "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values ("; for (int k = 0; k < dt.Columns.Count; k++) { strSQL += "'"+dt.Rows[i][k].ToString()+"',"; } strSQL = strSQL.Substring(0, strSQL.Length - 1); strSQL += ")"; SqlCommand insertCom = new SqlCommand(strSQL, con); insertCom.ExecuteNonQuery(); } return true; } catch { return false; } }
從Excel表格中讀取數(shù)據(jù)導(dǎo)入到Access數(shù)據(jù)庫中
//一、把DataTable插入數(shù)據(jù)庫
public static void DataTableToDB()
{
string _strExcelFileName = @"D:\example.xls";
DataTable dtExcel = ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
InsertDataToAccess(dtExcel.Rows[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].ToString()));
}
}
//二、把Excel數(shù)據(jù)讀入DataTable
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}
//三、向Access數(shù)據(jù)庫表插入數(shù)據(jù)
public static void InsertDataToAccess(string _strPara, float _fPara)
{
OleDbConnection oleDbConn = new OleDbConnection();
oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;";
oleDbConn.Open();
string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)";
OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
oComm.Parameters.Add("@strCollumn1", OleDbType.Char, 50);
oComm.Parameters["@strCollumn1"].Value = _strPara;
oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
oComm.Parameters["@fCollumn2"].Value = _fPara;
ocomm.ExecuteNonQuery();
oleDbConn.Close();
}
