用C#怎么将Excel中的数据导入到Sql Server中 C#页面上有一个按钮,点击后就可以将Excel上的数据导入到相应的表中,请问该怎么实现? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 http://blog.csdn.net/hy_huyang/archive/2006/03/01/613405.aspx看看这个对你有没有帮助很久以前写的 可以用StreamReader先把Excel的内容读出来。然后再插到数据库中,还有方法是:using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Web; namespace WindowsApplication1 { /// <summary> /// Form1 的摘要说明。 /// </summary> public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.DataGrid dataGrid1; /// <summary> /// 必需的设计器变量。 /// </summary> private System.ComponentModel.Container components = null; public Form1() { // // Windows 窗体设计器支持所必需的 // InitializeComponent(); // // TODO: 在 InitializeComponent 调用后添加任何构造函数代码 // } /// <summary> /// 清理所有正在使用的资源。 /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows 窗体设计器生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 /// </summary> private void InitializeComponent() { this.dataGrid1 = new System.Windows.Forms.DataGrid(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // dataGrid1 // this.dataGrid1.DataMember = " "; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(16, 32); this.dataGrid1.Name = "dataGrid1 "; this.dataGrid1.Size = new System.Drawing.Size(664, 464); this.dataGrid1.TabIndex = 0; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(6, 14); this.ClientSize = new System.Drawing.Size(688, 494); this.Controls.Add(this.dataGrid1); this.Name = "Form1 "; this.Text = "Form1 "; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// <summary> /// 应用程序的主入口点。 /// </summary> [STAThread] static void Main() { Application.Run(new Form1()); } private void wirteSql() { DataTable tb; tb =OLEEXCEL().Tables[0]; string strCreateTable = " create table table1 ( "; //strCreateTable = tb.Columns[0].ColumnName for(int col=0;col <tb.Columns.Count;col++) { if(col == tb.Columns.Count - 1) { strCreateTable = strCreateTable + " [ " +tb.Columns[col].ColumnName + "] "+ " varchar(50)) "; } else { strCreateTable = strCreateTable + " [ " +tb.Columns[col].ColumnName + "] "+ " varchar(50), "; } } MessageBox.Show(strCreateTable); string sqlInsert= " "; for(int i=0;i <tb.Rows.Count;i++) { string colvalue= " "; for(int j =0;j <tb.Columns.Count;j++) { if(tb.Rows[i][j] == null) colvalue = "null "; if(j == tb.Columns.Count -1) { colvalue =colvalue+ " ' "+tb.Rows[i][j].ToString()+ " ' " ; } else { colvalue = colvalue+ " ' "+tb.Rows[i][j].ToString()+ " ' " + ", "; } } sqlInsert = sqlInsert+ "insert into table1 values( "+colvalue+ ") "; } MessageBox.Show(sqlInsert); SqlConnection conn = new SqlConnection ( "server=(local);database=pubs;uid=sa;pwd = sa; "); conn.Open(); try { SqlCommand cmd = new SqlCommand (); cmd.Connection = conn; cmd.CommandText = strCreateTable+sqlInsert; cmd.ExecuteNonQuery(); } catch(Exception e) { MessageBox.Show(e.Message); } conn.Close(); } private DataSet OLEEXCEL() { OleDbConnection oleDbConnXls = new OleDbConnection (); string str; str= "Provider=Microsoft.Jet.OLEDB.4.0; "; str+= "Data Source=E:\\001.xls; "; str+= "Extended Properties=Excel 8.0; "; //oleDbConnXls已申明; oleDbConnXls.ConnectionString= str; try { oleDbConnXls.Open(); } catch { MessageBox.Show( "打开数据库失败!\n请检查数据库服务器后重新运行! "); } OleDbDataAdapter oleda = new OleDbDataAdapter ( "select * from [aaa$] ",oleDbConnXls); DataSet ds = new DataSet (); oleda.Fill(ds); if(oleDbConnXls!=null && oleDbConnXls.State == ConnectionState.Open) { oleDbConnXls.Close(); } dataGrid1.DataSource = ds.Tables[0]; return ds; } private void Form1_Load(object sender, System.EventArgs e) { OLEEXCEL(); wirteSql(); } } } Provider=Microsoft.Jet.OLEDB.4.0; "; str+= "Data Source=E:\\001.xls; "; str+= "Extended Properties=Excel 8.0; "; 这种方法好像有好多格式识别不了, USING ...;USING EXCEL; string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\test.xls;Extended Properties=Excel 8.0";//建立EXCEL连接 再建立一个SQL SERVER 连接; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = "INSERT INTO TABLENAME VALUES( SELECT * FROM [Sheet1$A1:G10] ) "; 其中SELECT * FROM [Sheet1$A1:G10] 是选择A1到G10之间的所有数据,可根据你的需要做修改 myConn.Open(); //执行插入操作 myConn.Close();这些都放到一个BUTTON_CLICK事件里就成.一点建议.希望能够抛砖引玉.谢谢 要看你Excel里面的数据格式两种方式1、如果数据规整,那么你完全可以把Excel当成数据源,至于代码,可以参照楼上的2、如果数据格式不规整,则需要饮用office com组件方式,循环cell实例代码网上很多的 以前回的很详细的例子供参考http://topic.csdn.net/u/20071103/15/d42b9c9a-fad8-4ced-8d2d-a025ac58f1a1.html DataSet Socket StreamReader無法讀取數據 sql费解。。。 求救!!!怎么样防止文本输入参数攻击、SQL攻击?? DataGridView如何固化某行色彩和加入行号 C# 反射获取DLL窗口中控件的事件 怎么样获得PDA的机器码? 关于C#多线程绘制问题 上海的一家猎头公司!(几个职位进来看看) 名称“Session”在类或命名空间“s.aa”中不存在,我要加上那一个类? 怎么制作.net的web程序安装包,我的数据库是SqlServer 2005! [100分在线急切等答案]无法修改Excel 组织结构图node的内容
很久以前写的
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web; namespace WindowsApplication1
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null; public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent(); //
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
} /// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
} #region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = " ";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(16, 32);
this.dataGrid1.Name = "dataGrid1 ";
this.dataGrid1.Size = new System.Drawing.Size(664, 464);
this.dataGrid1.TabIndex = 0;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(688, 494);
this.Controls.Add(this.dataGrid1);
this.Name = "Form1 ";
this.Text = "Form1 ";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false); }
#endregion /// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
} private void wirteSql()
{
DataTable tb;
tb =OLEEXCEL().Tables[0];
string strCreateTable = " create table table1 ( ";
//strCreateTable = tb.Columns[0].ColumnName
for(int col=0;col <tb.Columns.Count;col++)
{ if(col == tb.Columns.Count - 1)
{
strCreateTable = strCreateTable + " [ " +tb.Columns[col].ColumnName + "] "+ " varchar(50)) ";
}
else
{
strCreateTable = strCreateTable + " [ " +tb.Columns[col].ColumnName + "] "+ " varchar(50), ";
}
}
MessageBox.Show(strCreateTable);
string sqlInsert= " ";
for(int i=0;i <tb.Rows.Count;i++)
{
string colvalue= " ";
for(int j =0;j <tb.Columns.Count;j++)
{
if(tb.Rows[i][j] == null)
colvalue = "null ";
if(j == tb.Columns.Count -1)
{
colvalue =colvalue+ " ' "+tb.Rows[i][j].ToString()+ " ' " ;
}
else
{
colvalue = colvalue+ " ' "+tb.Rows[i][j].ToString()+ " ' " + ", ";
}
}
sqlInsert = sqlInsert+ "insert into table1 values( "+colvalue+ ") ";
}
MessageBox.Show(sqlInsert);
SqlConnection conn = new SqlConnection ( "server=(local);database=pubs;uid=sa;pwd = sa; ");
conn.Open();
try
{
SqlCommand cmd = new SqlCommand ();
cmd.Connection = conn;
cmd.CommandText = strCreateTable+sqlInsert;
cmd.ExecuteNonQuery();
}
catch(Exception e)
{ MessageBox.Show(e.Message);
}
conn.Close();
} private DataSet OLEEXCEL()
{
OleDbConnection oleDbConnXls = new OleDbConnection ();
string str;
str= "Provider=Microsoft.Jet.OLEDB.4.0; ";
str+= "Data Source=E:\\001.xls; ";
str+= "Extended Properties=Excel 8.0; ";
//oleDbConnXls已申明;
oleDbConnXls.ConnectionString= str;
try
{
oleDbConnXls.Open();
}
catch
{
MessageBox.Show( "打开数据库失败!\n请检查数据库服务器后重新运行! ");
}
OleDbDataAdapter oleda = new OleDbDataAdapter ( "select * from [aaa$] ",oleDbConnXls);
DataSet ds = new DataSet ();
oleda.Fill(ds);
if(oleDbConnXls!=null && oleDbConnXls.State == ConnectionState.Open)
{
oleDbConnXls.Close();
} dataGrid1.DataSource = ds.Tables[0];
return ds;
} private void Form1_Load(object sender, System.EventArgs e)
{
OLEEXCEL();
wirteSql();
}
}
}
str+= "Data Source=E:\\001.xls; ";
str+= "Extended Properties=Excel 8.0; ";
这种方法好像有好多格式识别不了,
USING EXCEL;
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\test.xls;Extended Properties=Excel 8.0";//建立EXCEL连接
再建立一个SQL SERVER 连接;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "INSERT INTO TABLENAME VALUES( SELECT * FROM [Sheet1$A1:G10] ) ";
其中SELECT * FROM [Sheet1$A1:G10] 是选择A1到G10之间的所有数据,可根据你的需要做修改
myConn.Open();
//执行插入操作
myConn.Close();
这些都放到一个BUTTON_CLICK事件里就成.一点建议.希望能够抛砖引玉.谢谢
1、如果数据规整,那么你完全可以把Excel当成数据源,至于代码,可以参照楼上的2、如果数据格式不规整,则需要饮用office com组件方式,循环cell实例代码网上很多的
http://topic.csdn.net/u/20071103/15/d42b9c9a-fad8-4ced-8d2d-a025ac58f1a1.html