Excel 表数据如何导入数据库Server 2000

//////下面的代码不合格，注意不允许Baidu搜索糊弄我。要专业人士解决。
首先这个文件必须上传到服务器，然后读取里面的内容放在ds里面，然后在导入sql
具体做法：
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;
using System.Configuration; namespace ETable
{
/// <summary>
/// ETable 的摘要说明。
/// </summary>
public class ETable : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlInputFile File1;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public Random rd; private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
} #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.Load += new System.EventHandler(this.Page_Load); }
#endregion private void Button1_Click(object sender, System.EventArgs e)
{
if (File1.PostedFile!=null)
{ rd=new Random(1);
string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.ToLongTimeString().Replace(":","")+rd.Next(9999).ToString()+".xls";
File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename);
//Response.Write(File1.PostedFile.FileName.ToString());
//Response.Write("上传成功");
Label1.Text="文件名为"+filename;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties=Excel 8.0" ;
OleDbConnection thisconnection=new OleDbConnection(conn);
thisconnection.Open();
string Sql="select * from [Sheet1$]";
OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection);
DataSet ds=new DataSet();
mycommand.Fill(ds,"[Sheet1$]");
thisconnection.Close();
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1=new OleDbConnection(conn1);
thisconnection1.Open();
int count=ds.Tables["[Sheet1$]"].Rows.Count; for (int i=0;i<count;i++)
{
string id,id_1,id_2,id_3;
id=ds.Tables["[Sheet1$]"].Rows[i]["id"].ToString();
id_1=ds.Tables["[Sheet1$]"].Rows[i]["id_1"].ToString();
id_2=ds.Tables["[Sheet1$]"].Rows[i]["id_2"].ToString();
id_3=ds.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();
string excelsql="insert into excel(id,id_1,id_2,id_3) values ('"+id+"','"+id_1+"','"+id_2+"','"+id_3+"') ";
OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
//FileStream fileStream = new FileStream(@Server.MapPath("file/")+filename);
System.IO.File.Delete(@Server.MapPath("file/")+filename); }
}
}
}
C# 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;
using System.Configuration; namespace ETable
{
/// <summary>
/// ETable 的摘要说明。
/// </summary>
public class ETable : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlInputFile File1;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public Random rd; private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
} #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.Load += new System.EventHandler(this.Page_Load); }
#endregion private void Button1_Click(object sender, System.EventArgs e)
{
if (File1.PostedFile!=null)
{ rd=new Random(1);
string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.ToLongTimeString().Replace(":","")+rd.Next(9999).ToString()+".xls";
File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename);
//Response.Write(File1.PostedFile.FileName.ToString());
//Response.Write("上传成功");
Label1.Text="文件名为"+filename;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties=Excel 8.0" ;
OleDbConnection thisconnection=new OleDbConnection(conn);
thisconnection.Open();
string Sql="select * from [Sheet1$]";
OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection);
DataSet ds=new DataSet();
mycommand.Fill(ds,"[Sheet1$]");
thisconnection.Close();
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1=new OleDbConnection(conn1);
thisconnection1.Open();
int count=ds.Tables["[Sheet1$]"].Rows.Count; for (int i=0;i<count;i++)
{
string id,id_1,id_2,id_3;
id=ds.Tables["[Sheet1$]"].Rows[i]["id"].ToString();
id_1=ds.Tables["[Sheet1$]"].Rows[i]["id_1"].ToString();
id_2=ds.Tables["[Sheet1$]"].Rows[i]["id_2"].ToString();
id_3=ds.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();
string excelsql="insert into excel(id,id_1,id_2,id_3) values ('"+id+"','"+id_1+"','"+id_2+"','"+id_3+"') ";
OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close(); }
}
}
}

我看看哦，还没有听说过MyXls的..尴尬。。

SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]先上传文件，再打开excel
if(File.Exists(strPathName))
{
book=(Excel._Workbook)(app.Workbooks.Open(strPathName,Missing.Value, Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value, Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value));
}
else
{
Response.Write(" <script language='javascript'>alert('请上传需要到入的数据文件') </script>");
return ;
}
using(SqlConnection conn=new SqlConnection(ConnectionString))
{
SqlDataAdapter da=new SqlDataAdapter(Sql,conn);
System.Data.SqlClient.SqlCommandBuilder scb=new SqlCommandBuilder(da);
sheet=(Excel._Worksheet)book.Worksheets[1];
for(int i=1;i <=sheet.Rows.Count;i++)
{
dr=ds.Tables[0].NewRow();
//循环导入数据
ds.Tables[0].Rows.Add(dr);
}
da.Update(ds);
}

直接用SQL Server的所有任务的到如何导出就好了

Update:2007.4.3
Author:Hougate
sql导出到Excel
从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)直接用SQL

或者用SQL自带的导入导出工具DTS,

/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'  /*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions  /*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/  SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions  /********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

直接复制过去,改一下文件名,路径OK

不是啊，我是一个程序中的，是上传文件，然后把上传的excel的数据存到数据库中的,怪我没有说清楚..该打该打

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions  把这个复制吗??改路径是把c:\test.xls 该成我文件的路径吗??

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions这两个要更改的,那个路径改为你的,后面的那个改为 sheet1$这种格式

.....好复杂哦，你们能帮我个忙不？？我数据库中已经有一个表了，就是把Excel的数据插入到表里面，我看了参考了，感觉这个比较适合我的水平，不过在插入数据的时候出了点问题，因为没看懂》》》》谁可以具体写一下代码谢谢了，不建立数据库，和表，直接插入数据。。比如表里面2个字段，一个username,一个pwd,谢谢了。
protected bool InsertSQLServer(DataTable dt,string dataname)
{
}

呵呵,你要程序的呀,不懂,SQL的简单

就是Insert语句不会写,不知道怎么写....好难过啊,6楼的参考,的最后一个方法写一下了谢谢哦，SQL77，结贴的时候会给你分的，呵呵。。不过不是全分，不过肯定10+

INSERT TB SELECT * FROM TB1
??
这样类似,呵呵insert 库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

你错了,是这样写的,所有代码大揭晓:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {    }
    protected void Button1_Click(object sender, EventArgs e)
    {        if (FileUpload1.FileName == "")
            return;
        string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
        if (fileExt != ".xls")//必须是EXCEL文件
            return;
        string filepath = FileUpload1.PostedFile.FileName;//文件路径
        DataTable dt = new DataTable();
        dt = CallExcel(filepath);//返回EXCEL文件的数据
        if (InsertSQLServer(dt, FileUpload1.FileName.Split('.')[0]))//导入数据库
        {}
    }    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$]";//选择第一个数据SHEET
        //OleDbCommand command = new OleDbCommand(sql, con);
        //OleDbDataReader reader = command.ExecuteReader();
        //if (reader.Read())
        //{
        //    reader[0].ToString();//直接读出数据
        //}
        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 = @"server=xxx.xxx.xxx.xxx;database=TableName;uid=sa;pwd=";
        try
        {
            SqlConnection con = new SqlConnection(strCon);//连接数据库
              con.Open();//打开

            //插入数据
              for(int i=0;i<dt.Rows.Count;i++)
            {
                string strSQL = " Insert into one 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表中的字段要和数据库中建立的表的字段一样,要一样.然后就OK了..
给分数了,所有回答的人都有分..

调试易

Excel 表数据如何导入数据库Server 2000

解决方案 »