Vs.Net方向将Excel数据导入到数据库

开发环境:

1,用的数据库是Sql Server 2005 你可以自己建立一个数据库我用的表是yinAddress(最下面有表结构的生成语句)

2,开发环境是VS.Net2008+C#

3,使用到第三方的一个组件名称:C1.C1Excel.2.dll (可以到网上找)也可以在这里下载已经压缩

-----------------------------------------------------------

下面是代码

using C1.C1Excel;//必须添加这个引用哦
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        InportExcel();
    }
    //Excel数据导入到数据库
    private void InportDataBase()
    {
        C1XLBook b = new C1XLBook();//第三方的东东(可以拿来学习下)
        string fileName = Server.MapPath(Request.ApplicationPath + "/Book1.xls");//获取Excel文件存放的路径
        b.Load(fileName);//将Book1.xls的数据装载到C1XLBook对象里面,

然后数据就通过它((b))一条条读取,在进入数据库之前你可以做任何编辑, 然后再保存; 或者你可以一条条的保存到DataTable里面或者添加到DataSet里面我这里则是在获取每条数据后直接保存到数据库里了
        if (b.Sheets[0] != null &&b.Sheets[0].Rows.Count > 0)
        {
            for (int i = 0; i < b.Sheets[0].Rows.Count; i++)
            {
                string sql = " insert into yinAddress(AddressLine1,AddressLine2,City,StateProvinceID,PostalCode,rowguid,ModifiedDate) " +
                              " values('{0}','{1}','{2}','{3}','{4}','{5}','{6}') ";       //Sql语句

string v1 = Convert.ToString(b.Sheets[0][i,1].Value); //读取每条数据
string v2 = Convert.ToString(b.Sheets[0][i,2].Value);

string v3 = Convert.ToString(b.Sheets[0][i,3].Value);
string v4 = Convert.ToString(b.Sheets[0][i,4].Value);

string v5 = Convert.ToString(b.Sheets[0][i,5].Value);

string v6 = Convert.ToString(b.Sheets[0][i,6].Value);
string v7 = Convert.ToString(b.Sheets[0][i,7].Value);

                sql = String.Format(sql,v1,v2,v3,v4,v5,v6,v7);   //拼装SQL语句
                try
                {
                    int result = DataAccess.EditData(sql);//是自己写的数据库组件层的方法

                    Response.Write("<script>alert('good');</script>");//测试是否成功将数据导入到数据库
                }
                catch (Exception ee)
                {
                    string error = ee.Message;
                }
            }
        }
    }
}

-------------------------------------------------------------------------

下面是yinAddress表的生成语句

USE [AdventureWorks] //这个是Sql Server2005自带的一个数据库,我在里面建了一个表表名就叫yinAddress
GO
/****** 对象: Table [dbo].[yinAddress] 脚本日期: 08/27/2008 17:57:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[yinAddress](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressLine2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_yinAddress_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_yinAddress_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_yinAddress_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

----------------------------------------------------------------

上面的代码已经测试,针对Excel2003和Excel2007都可以