读取excel, 让用户选出的几个列，将数据导入到sql server,如何实现！

q3256

　　excel 是列的多少不固定。
sql server 表列是固定的
就是想实现将用户现有的数据导入到新开发的系统的数据库
，让用户自己来实现。现在excel 列名已经取出来了
导入的sql server 语句还不知道怎么写，因为列不是固定的，根据用户自己选择的
动态生成的sql 语句。
　　// cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

namespace upload
{
/// <summary></summary>
/// WebForm3 的摘要说明。
///
public class WebForm3 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.ListBox ListBox1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Button Button2;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.Label Label5;
protected System.Web.UI.WebControls.Label Label6;
protected System.Web.UI.WebControls.ListBox ListBox2;
protected System.Web.UI.WebControls.Button Button3;
protected System.Web.UI.WebControls.DataGrid DataGrid1;


private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{

bind();
}

}
private void bind()
{
// 在此处放置用户代码以初始化页面
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("/Files/Book1.xls") + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data from the worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");

// Bind data to DataGrid control.
//DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
//DataGrid1.DataBind();

// Clean up objects.
objConn.Close();



// DataRow newRow = objDataset1.Tables[0].NewRow();
//
// newRow[0] = "内容1";
// newRow[1] = "内容2";
// objDataset1.Tables[0].Rows.InsertAt(newRow, 0); //a为要插入的行号(int型)

System.Data.DataColumn cl=objDataset1.Tables[0].Columns[0];

DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();
}

#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary></summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
///
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Button2.Click += new System.EventHandler(this.Button2_Click);
this.Button3.Click += new System.EventHandler(this.Button3_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void Button1_Click(object sender, System.EventArgs e)
{

//取列名
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("/Files/Book1.xls") + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
this.ListBox1.DataSource=objDataset1.Tables[0];


if (System.Convert.ToInt32(Session["i"])==objDataset1.Tables[0].Columns.Count)
{
Session["i"]=0;
}
this.ListBox1.DataTextField=objDataset1.Tables[0].Columns[System.Convert.ToInt32(Session["i"])].ToString();

this.ListBox1.DataBind();

ArrayList al = new ArrayList();
al.Add(objDataset1.Tables[0].Columns[System.Convert.ToInt32(Session["i"])].ToString());
for (int i=0;i {
Response.Write(al.ToString());
this.Label1.Text=al.ToString();
}

Session["i"]=System.Convert.ToInt32(Session["i"])+1;
}

private void Button2_Click(object sender, System.EventArgs e)
{
//插入列名
this.ListBox2.Items.Insert(0,this.Label1.Text);

}

private void Button3_Click(object sender, System.EventArgs e)
{
//列出列名
for (int i=0;i< this.ListBox2.Items.Count;i++)
{
Response.Write(this.ListBox2.Items.Text+"
");
}
//这里写插入sql server 的代码，如果sql server 的有一列没有数据插入，则这一列为空

}
}
}
　　//aspx
<!--Page language="c#" Codebehind="WebForm3.aspx.cs" AutoEventWireup="false" Inherits="upload.WebForm3"-->




<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">




<datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 216px; POSITION: absolute; TOP: 208px"></datagrid>runat="server" Width="529px" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" BackColor="White"
CellPadding="3" GridLines="Horizontal">
<selecteditemstyle font-bold="True" forecolor="#F7F7F7" backcolor="#738A9C"></selecteditemstyle>
<alternatingitemstyle backcolor="#F7F7F7"></alternatingitemstyle>
<itemstyle forecolor="#4A3C8C" backcolor="#E7E7FF"></itemstyle>
<headerstyle font-bold="True" forecolor="#F7F7F7" backcolor="#4A3C8C"></headerstyle>
<footerstyle forecolor="#4A3C8C" backcolor="#B5C7DE"></footerstyle>
<pagerstyle forecolor="#4A3C8C" backcolor="#E7E7FF" horizontalalign="Right" mode="NumericPages"></pagerstyle>

<listbox id="ListBox1" style="Z-INDEX: 102; LEFT: 480px; POSITION: absolute; TOP: 24px" runat="server"></listbox>Width="96px" Height="168px">
Text="Button">
Text="Button">
Width="56px">
Width="80px">Label
Label
Label
Label
Label
<listbox id="ListBox2" style="Z-INDEX: 111; LEFT: 576px; POSITION: absolute; TOP: 368px"></listbox>runat="server" Width="72px" Height="168px">
Text="Button">

其他文章 上一篇：一个一直没有解决的问题  下一篇：window.print() 分页问题 相关文章 更多文章
·一个一直没有解决的问题
·window.print() 分页问题
·在线等：如何解决insert问题？
·Forms验证怎样在webconfig中指定两个错误
·为什么< 被自动转换为&lt;?　应该
·执行过程出现这样的问题！
<script src="/ad/right_250.js" type="text/javascript"></script><script type="text/javascript"><!--google_ad_client = "pub-0290692204058008";google_ad_width = 250;google_ad_height = 250;google_ad_format = "250x250_as";google_ad_type = "text_image";//2007-03-17: donew_250google_ad_channel = "0177700582";google_color_border = "FFFFFF";google_color_bg = "f0f8ff";google_color_link = "004a9c";google_color_text = "004a9c";google_color_url = "CCCCCC";//--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script><iframe name="google_ads_frame" marginwidth="0" marginheight="0" src="http://pagead2.googlesyndication.com/pagead/ads?client=ca-pub-0290692204058008&amp;dt=1178594714687&amp;lmt=1175441401&amp;prev_fmts=728x15_0ads_al_s%2C300x250_as&amp;format=250x250_as&amp;output=html&amp;channel=0177700582&amp;url=http%3A%2F%2Fbiancheng.139aa.com%2Fasp%2F4152.html&amp;color_bg=f0f8ff&amp;color_text=004a9c&amp;color_link=004a9c&amp;color_url=CCCCCC&amp;color_border=FFFFFF&amp;ad_type=text_image&amp;ref=http%3A%2F%2Fwww.baidu.com%2Fs%3Flm%3D0%26si%3D%26rn%3D10%26ie%3Dgb2312%26ct%3D0%26wd%3D%25CA%25FD%25BE%25DD%25B5%25BC%25C8%25EB%2B%25C1%25D0%26pn%3D30%26cl%3D3&amp;cc=100&amp;flash=0&amp;u_h=1024&amp;u_w=1280&amp;u_ah=994&amp;u_aw=1280&amp;u_cd=32&amp;u_tz=480&amp;u_java=true" frameborder="0" width="250" scrolling="no" height="250" allowtransparency="allowtransparency"></iframe>