insert into TB1(comluA) SELECT comluA FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="d:\test.xls";Extended properties=Excel 10.0')...[Sheet1$]
在sqlcommand中执行如上的sql语句
呵呵 我很久没写这个了 随便写了一个test using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.OleDb;namespace PutExcelToDB { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } private void button1_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ds = FindInfoFromExcel(this.tbxPath.Text,this.tbxTableName.Text, this.tbxColumns.Text, "Id","","","",3); dgvExcelInfo.DataSource = null; dgvExcelInfo.DataSource = ds.Tables[0];
SELECT comluA
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\test.xls";Extended properties=Excel 10.0')...[Sheet1$]
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;namespace PutExcelToDB
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{ } private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = FindInfoFromExcel(this.tbxPath.Text,this.tbxTableName.Text, this.tbxColumns.Text, "Id","","","",3);
dgvExcelInfo.DataSource = null;
dgvExcelInfo.DataSource = ds.Tables[0];
} private void btnfindbycount_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = FindInfoFromExcel(this.tbxPath.Text, this.tbxTableName.Text, this.tbxColumns.Text, "Id", "", this.tbxRows.Text, this.tbxbeginCount.Text, 2);
//ds = FindInfoFromExcel("d:\\zzz.xls", "dd", "name,symbol,Id", "Id", "321312");
dgvExcelInfo.DataSource = null;
dgvExcelInfo.DataSource = ds.Tables[0];
} private void button2_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = FindInfoFromExcel(this.tbxPath.Text, this.tbxTableName.Text, this.tbxColumns.Text, "Id", "", this.tbxRows.Text,"",1);
//ds = FindInfoFromExcel("d:\\zzz.xls", "dd", "name,symbol,Id", "Id", "321312");
dgvExcelInfo.DataSource = null;
dgvExcelInfo.DataSource = ds.Tables[0];
} private DataSet FindInfoFromExcel(string stFilePath,string tableName,string stFiles,string stPrimaryKey,string stCondition,string stRows,string stBeginRowcount,int flag)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;"
+ "data source=" + stFilePath + ";"
+ "Extended Properties=Excel 8.0;";
String strSql = string.Empty;
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
if (stRows != "" && flag==1)
{
//strSql = "Select top(1) " + stFiles + " From [" + tableName + "] order by " + stPrimaryKey + "";
strSql = "Select top "+stRows+" * From [" + tableName + "$"+stFiles+"]";
}
else if(flag==2 && stBeginRowcount!="")
{
strSql = "select top " + stRows + " * from [" + tableName + "$" + stFiles + "] where idx not in (select top " + stBeginRowcount + " idx from [" + tableName + "$]) ";
}
else if(flag ==3)
{
//strSql = "Select " + stFiles + " From [" + tableName + "] order by " + stPrimaryKey + "";
//strSql = "Select " + stFiles + " From [" + tableName + "$A:B]";
strSql = "Select * From [" + tableName + "$" + stFiles + "]";
}
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
objCmd.Connection = objConn;
OleDbDataAdapter adapter = new OleDbDataAdapter(objCmd);
DataSet ds = new DataSet(); try
{
adapter.Fill(ds);
return ds;
}
catch (Exception exc)
{
MessageBox.Show(exc.ToString());
return null;
}
}
}
}
{
partial class Form1
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.IContainer components = null; /// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
} #region Windows 窗体设计器生成的代码 /// <summary>
/// 设计器支持所需的方法 - 不要
/// 使用代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.tbxPath = new System.Windows.Forms.TextBox();
this.tbxTableName = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.tbxColumns = new System.Windows.Forms.TextBox();
this.label4 = new System.Windows.Forms.Label();
this.tbxRows = new System.Windows.Forms.TextBox();
this.dgvExcelInfo = new System.Windows.Forms.DataGridView();
this.btnfindbycount = new System.Windows.Forms.Button();
this.label5 = new System.Windows.Forms.Label();
this.tbxbeginCount = new System.Windows.Forms.TextBox();
((System.ComponentModel.ISupportInitialize)(this.dgvExcelInfo)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(26, 104);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 0;
this.button1.Text = "btnAll";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(143, 104);
this.button2.Name = "button2";
this.button2.Size = new System.Drawing.Size(75, 23);
this.button2.TabIndex = 1;
this.button2.Text = "btnFirst";
this.button2.UseVisualStyleBackColor = true;
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// button3
//
this.button3.Location = new System.Drawing.Point(385, 103);
this.button3.Name = "button3";
this.button3.Size = new System.Drawing.Size(75, 23);
this.button3.TabIndex = 2;
this.button3.Text = "btnImport";
this.button3.UseVisualStyleBackColor = true;
//
// tbxPath
//
this.tbxPath.Location = new System.Drawing.Point(87, 24);
this.tbxPath.Name = "tbxPath";
this.tbxPath.Size = new System.Drawing.Size(131, 21);
this.tbxPath.TabIndex = 3;
this.tbxPath.Text = "d:\\zzz.xls";
//
// tbxTableName
//
this.tbxTableName.Location = new System.Drawing.Point(347, 24);
this.tbxTableName.Name = "tbxTableName";
this.tbxTableName.Size = new System.Drawing.Size(170, 21);
this.tbxTableName.TabIndex = 4;
this.tbxTableName.Text = "dd";
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(12, 27);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(59, 12);
this.label1.TabIndex = 5;
this.label1.Text = "Excel路径";
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(253, 27);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(77, 12);
this.label2.TabIndex = 6;
this.label2.Text = "要导入的表名";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(238, 78);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(131, 12);
this.label3.TabIndex = 7;
this.label3.Text = "选择excel里要导出的列";
//
// tbxColumns
//
this.tbxColumns.Location = new System.Drawing.Point(385, 69);
this.tbxColumns.Name = "tbxColumns";
this.tbxColumns.Size = new System.Drawing.Size(170, 21);
this.tbxColumns.TabIndex = 8;
this.tbxColumns.Text = "A:B";
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(6, 78);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(119, 12);
this.label4.TabIndex = 9;
this.label4.Text = "导入excel的前多少行";
//
// tbxRows
//
this.tbxRows.Location = new System.Drawing.Point(131, 69);
this.tbxRows.Name = "tbxRows";
this.tbxRows.Size = new System.Drawing.Size(86, 21);
this.tbxRows.TabIndex = 10;
this.tbxRows.Text = "3";
//
// dgvExcelInfo
//
this.dgvExcelInfo.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dgvExcelInfo.Location = new System.Drawing.Point(26, 133);
this.dgvExcelInfo.Name = "dgvExcelInfo";
this.dgvExcelInfo.RowTemplate.Height = 23;
this.dgvExcelInfo.Size = new System.Drawing.Size(529, 304);
this.dgvExcelInfo.TabIndex = 11;
//
// btnfindbycount
//
this.btnfindbycount.Location = new System.Drawing.Point(240, 103);
this.btnfindbycount.Name = "btnfindbycount";
this.btnfindbycount.Size = new System.Drawing.Size(111, 23);
this.btnfindbycount.TabIndex = 12;
this.btnfindbycount.Text = "btnfindbycount";
this.btnfindbycount.UseVisualStyleBackColor = true;
this.btnfindbycount.Click += new System.EventHandler(this.btnfindbycount_Click);
//
// label5
//
this.label5.AutoSize = true;
this.label5.Location = new System.Drawing.Point(12, 54);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(101, 12);
this.label5.TabIndex = 13;
this.label5.Text = "从第几行开始导出";
//
// tbxbeginCount
//
this.tbxbeginCount.Location = new System.Drawing.Point(132, 45);
this.tbxbeginCount.Name = "tbxbeginCount";
this.tbxbeginCount.Size = new System.Drawing.Size(86, 21);
this.tbxbeginCount.TabIndex = 14;
this.tbxbeginCount.Text = "1";
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(653, 460);
this.Controls.Add(this.tbxbeginCount);
this.Controls.Add(this.label5);
this.Controls.Add(this.btnfindbycount);
this.Controls.Add(this.dgvExcelInfo);
this.Controls.Add(this.tbxRows);
this.Controls.Add(this.label4);
this.Controls.Add(this.tbxColumns);
this.Controls.Add(this.label3);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.tbxTableName);
this.Controls.Add(this.tbxPath);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dgvExcelInfo)).EndInit();
this.ResumeLayout(false);
this.PerformLayout(); } #endregion private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.TextBox tbxPath;
private System.Windows.Forms.TextBox tbxTableName;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox tbxColumns;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox tbxRows;
private System.Windows.Forms.DataGridView dgvExcelInfo;
private System.Windows.Forms.Button btnfindbycount;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.TextBox tbxbeginCount;
}
}
不是在WEB形式
A B c
uname uid lasttime
aaa 001 2008-02-01 10:00:00
bbb 002 2008-02-01 10:00:00
ccc 003 2008-02-01 10:00:00
希望导入uname uid 两列至sqlserver表TESTTABLE中 //假设TESTTABLE还未创建 string strSQL = "SELECT uname，uid INTO TESTTABLE FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=d:\\test.xls;Extended Properties=Excel 8.0')...[Sheet1$]";
OleDbConnection scon = new OleDbConnection("Provider=SQLOLEDB;Data Source=(local);Initial Catalog=SCSIM;User ID=sa;Password=");
scon.Open();
OleDbCommand scmd = new OleDbCommand(strSQL, scon);
scmd.ExecuteNonQuery();
scon.Close();
string strSQL = "SELECT uname,uid INTO TESTTABLE FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=d:\\test.xls;Extended Properties=Excel 8.0')...[Sheet1$]";
OleDbConnection scon = new OleDbConnection("Provider=SQLOLEDB;Data Source=(local);Initial Catalog=SCSIM;User ID=sa;Password=");
scon.Open();
OleDbCommand scmd = new OleDbCommand(strSQL, scon);
scmd.ExecuteNonQuery();
scon.Close();
sqlserver数据库为本地，数据库名称为SCSIM，登录名为sa 密码为空
贴进去就能用
就是因为考虑到EXCEL表还有表头之类的存在
所以那句怎么写~我是想从第几行的数开始导
你写给我的已经写了什么列了~现在我想的是从第几行开始导
怎么设定从第几行呢?