有关web上传execel写入数据库的方法.

在一位网友的帮助搞定了.很是高兴.在这里再次谢谢他.

这是一段代码

/********************************************************************
 filename:  UpLoadExcelToSql.cs
 created: 2009/04/16
 author:  liaoyun
 purpose: 根据上传的Excel文件将数据导入数据库
*********************************************************************/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// UpLoadExcelToSql 的摘要说明
/// </summary>
public class UpLoadExcelToSql
{
    private string conStringExcel;//excel连接字符串
    private string excelFilePath;//Excel文件路径

    //连接字符串应该从配置文件获得
    private string connectionString = "Data Source=WWW-DD8092BA15B;Initial Catalog=sltx;Integrated Security=True";

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="excelFileName">Excel文件名</param>
    public UpLoadExcelToSql(string excelFilePath)
    {
        this.excelFilePath = excelFilePath;
        this.conStringExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" +
            excelFilePath + "';Extended Properties=Excel 8.0";
    }

    /// <summary>
    /// 获得Excel表中的表名
    /// 值是类似这样的：Sheet1$表1$表2$表3$
    /// </summary>
    /// <returns>Excel文件所有工作表名(工作簿)</returns>
    private List<string> ExcelSheetName()
    {
        List<string> sheetNames = new List<string>();
        string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            excelFilePath + ";Extended Properties=Excel 8.0;"; //连接字符串
        using (OleDbConnection con = new OleDbConnection(conString))
        {
            con.Open();
            DataTable sheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
            con.Close();
            foreach (DataRow var in sheetName.Rows)
            {
                sheetNames.Add(var[2].ToString());
            }
        }
        return sheetNames;
    }

    /// <summary>
    /// 执行插入操作
    /// </summary>
    /// <param name="sqls">SQL语句集</param>
    /// <returns>成功true，失败false</returns>
    private bool InsertExcelDataToSql(List<string> sqls)
    {
        bool flag = false;
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con, tran);
            try
            {
                foreach (string var in sqls)
                {
                    cmd.CommandText = var;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                flag = true;
            }
            catch (Exception)
            {
                tran.Rollback();
            }
        }
        return flag;
    }

    /// <summary>
    /// 拼接SQL语句
    /// </summary>
    /// <param name="ds">Excel文件数据集</param>
    /// <returns>SQL语句集</returns>
    /******此方法可以根据实际需要修改,只需要修改DataSet填充的汉字******/
    private List<string> CreateSqlFromExcel(DataSet ds)
    {
        List<string> sqls = new List<string>();
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            StringBuilder sql = new StringBuilder();
            string isbn = ds.Tables[0].Rows[i]["ISBN"].ToString();  //ISBN号
            string name = ds.Tables[0].Rows[i]["书名"].ToString();  //书名
            string cong_name = ds.Tables[0].Rows[i]["从书名"].ToString();  //丛书名
            string author = ds.Tables[0].Rows[i]["著者"].ToString();  //著者
            string pubAddress = ds.Tables[0].Rows[i]["出版地"].ToString();  //出版地
            string publish = ds.Tables[0].Rows[i]["出版社"].ToString();  //出版社
            string price = ds.Tables[0].Rows[i]["单价"].ToString();  //单价
            string reader = ds.Tables[0].Rows[i]["读者对象"].ToString();  //读者对象
            string format = ds.Tables[0].Rows[i]["开本"].ToString();  //开本
            string pageSize = ds.Tables[0].Rows[i]["页数"].ToString();  //页数
            string zhuangZhen = ds.Tables[0].Rows[i]["装帧"].ToString();  //装帧
            string content = ds.Tables[0].Rows[i]["内容简介"].ToString();  //内容简介
            string pubTime = ds.Tables[0].Rows[i]["出版时间"].ToString();  //出版时间
            string zhongType = ds.Tables[0].Rows[i]["中图法分类"].ToString();  //中图法分类
            string zhengDing = ds.Tables[0].Rows[i]["征订号"].ToString();  //征订号
            sql.Append("insert into Book values (");
            sql.AppendFormat("'{0}','{1}','{2}',", isbn, name, cong_name);
            sql.AppendFormat("'{0}','{1}','{2}',", author, pubAddress, publish);
            sql.AppendFormat("'{0}','{1}','{2}',", price, reader, format);
            sql.AppendFormat("'{0}','{1}','{2}',", pageSize, zhuangZhen, content);
            sql.AppendFormat("'{0}','{1}','{2}')", pubTime, zhongType, zhengDing);
            sqls.Add(sql.ToString());
        }
        return sqls;
    }

    /// <summary>
    /// 把Excel文件的数据导入到数据库
    /// </summary>
    /// <param name="fails">导入失败的表</param>
    /// <returns>导入成功的表</returns>
    public List<string> InsertExcelDataToSql(out List<string> fails)
    {
        List<string> sheets = new List<string>();
        List<string> sheetNames = ExcelSheetName();
        fails = new List<string>();
        OleDbConnection cnnxls = new OleDbConnection(conStringExcel);
        for (int i = 0; i < sheetNames.Count; i++)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetNames[i] + "]", cnnxls);
            myDa.Fill(ds);
            //再调用一个方法拼接SQL
            List<string> sqls = CreateSqlFromExcel(ds);
            //再调用一个方法插入数据库
            if (InsertExcelDataToSql(sqls))
            {
                sheets.Add(sheetNames[i]);
            }
            else
            {
                fails.Add(sheetNames[i]);
            }
        }
        return sheets;
    }

    /// <summary>
    /// 已知Excel文件工作表名直接插入，但只能是一张表
    /// </summary>
    /// <param name="sheetName">工作表名</param>
    /// <returns>成功true失败false</returns>

    public bool InsertExcelDataToSql(string sheetName)
    {
        bool flag = false;
        DataSet ds = new DataSet();
        OleDbConnection con = new OleDbConnection(conStringExcel);
        OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetName + "$]", con);
        myDa.Fill(ds);
        con.Close();
        List<string> sqls = CreateSqlFromExcel(ds);
        return flag=InsertExcelDataToSql(sqls);
    }
}