Excel 导入数据到SQL Server 数据库

方法一：Excel导入SQL Server数据库,要求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.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// ExcelToSQL 的摘要说明
/// </summary>
public class ExcelToSQL
{
    string str=ConfigurationManager.AppSettings["ConnectionStr"].ToString();
    public SqlConnection sqlcon = new SqlConnection("Data Source=(local);Initial Catalog=test;User 

Id=sa;Password=123;"); //创建SQL连接
    public SqlCommand sqlcmd; //创建SQL命令对象

    public ExcelToSQL()
    {
        if (sqlcon.State == ConnectionState.Open)
        {
            sqlcon.Close();
        }
    }

    /// <summary>
    /// excel导入sqlserver数据库
    /// </summary>
    /// <param name="excelPath">excel 路径,绝对路径</param>
    /// <param name="tableName">数据库表名</param>
    /// <returns></returns>
    public int ImportSql(string excelPath, string tableName)  //导入的Excel的路径，数据库里的表名      
    {
        if (!TableExist(tableName)) //表名是否存在             
            return (int)ImportState.tableNameError;
        DataTable dt = ExcelToDataTable(excelPath);//把Excel里的数据转换为DataTable，并返回DataTable
        if (dt == null)
        {
            return (int)ImportState.excelFormatError;//Excel格式不能读取
        }
        ArrayList tableField = GetTableField(tableName);   //表格的列名称         
        string columnName = "";// "ID,"; //Excel里的列名，增加一个ID列,如果ID自动递增则不需要增加ID列，只需

要columnName=“”就可以了。    
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            columnName += "["+dt.Columns[i].ColumnName + "],";
            string currentColumn = dt.Columns[i].ToString().ToUpper(); //当前列名      
            for (int j = 0; j < tableField.Count; j++)
            {
                if (tableField[j].ToString().ToUpper() == dt.Columns[i].ToString().ToUpper())
                    break;   //跳出本层和上一层循环，continue是跳出本层循环，如果用continue，会继续执行j++  

                
                //Excel里的字段必须在Sql中都有             
                if ((tableField[j].ToString().ToUpper() != dt.Columns[i].ToString().ToUpper()) && j == 

tableField.Count - 1)
                    return (int)ImportState.fieldMatchError;//excel里的字段和数据库表里的字段不匹配 
            }
        }
        int m = columnName.LastIndexOf(',');
        columnName = columnName.Remove(m);  //移除最后一个逗号      
        sqlcmd = new SqlCommand();
        sqlcmd.Connection = sqlcon;
        sqlcon.Open();
        sqlcmd.CommandType = CommandType.Text;
        for (int h = 0; h < dt.Rows.Count; h++)
        {
            string value = "";// "'" + System.Guid.NewGuid().ToString() + "'" + ","; //如果ID自动递增ID列不

需要增加了，那么value的初始值只需要value=“”就可以了。       
            for (int k = 0; k < dt.Columns.Count; k++) //根据列名得到值         
            {
                value += "'" + dt.Rows[h][k].ToString() + "'" + ",";
            } 
            int n = value.LastIndexOf(',');
            value = value.Remove(n);    //移除最后一个逗号
            try
            {
                string sql = "insert into " + tableName + "(" + columnName + ") values(" + value + ")";
                sqlcmd.CommandText = sql;
                string sss = sqlcmd.ExecuteNonQuery().ToString();
            }
            catch (Exception err)
            {
                string erroe = err.Message;
                return (int)ImportState.dataTypeError;
            }
        }
        sqlcon.Close();
        sqlcmd.Dispose();
        return (int)ImportState.right;
    }

    /// <summary>
    /// 把Excel里的数据转换为DataTable，并返回DataTable
    /// </summary>
    /// <param name="excelPath"></param>
    /// <returns></returns>
    public DataTable ExcelToDataTable(string excelPath)   
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended 

Properties='Excel 8.0;IMEX=1'";
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
        string strCom = "SELECT * FROM [Sheet1$]";
        DataTable dt;
        try
        {
            Conn.Open();
            System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, 

Conn); 
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[Sheet1$]");
            Conn.Close();
            dt = ds.Tables[0];
        }
        catch (Exception err)
        {
            return null;
        }
        return dt;
    }

    /// <summary>
    /// 查看数据库里是否有此表名
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public bool TableExist(string tableName)
    {
        sqlcmd = new SqlCommand();
        sqlcmd.Connection = sqlcon;
        sqlcmd.CommandType = CommandType.Text;
        try
        {
            sqlcon.Open();
            string sql = "select name from sysobjects where type='u'";
            sqlcmd.CommandText = sql;
            SqlDataReader sqldr = sqlcmd.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr.GetString(0).ToUpper() == tableName.ToUpper())
                    return true;
            }
        }
        catch
        {
            return false;
        }
        finally
        {
            sqlcon.Close();
        }
        return false;
    }

    /// <summary>
    /// 得到数据库某一个表中的所有字段    
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public ArrayList GetTableField(string tableName)  
    {
        ArrayList al = new ArrayList();
        sqlcmd = new SqlCommand();
        sqlcmd.Connection = sqlcon;
        sqlcmd.CommandType = CommandType.Text;
        try
        {
            sqlcon.Open();
            string sql = "SELECT b.name FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id WHERE 

(a.name = '" + tableName + "')";
            sqlcmd.CommandText = sql;
            SqlDataReader sqldr = sqlcmd.ExecuteReader();
            while (sqldr.Read())
            {
                al.Add(sqldr.GetString(0));
            }
        }
        finally
        {
            sqlcon.Close();
        }
        return al; //返回的是表中的字段     
    }

    /// <summary>
    /// 枚举，导入状态
    /// </summary>
    public enum ImportState
    {
        right = 1, //成功        
        tableNameError = 2,//表名不存在        
        fieldMatchError = 3,//excel里的字段和数据库表里的字段不匹配    
        dataTypeError = 4, //转换数据类型时发生错误      
        excelFormatError = 5,//Excel格式不能读取     
    }

    public void Alert(string str)
    {
        HttpContext.Current.Response.Write("<script language='javascript'>alert('" + str + "');</script>");
    }
}

方法二:

/// <summary>
        /// 把Excel里的数据转换为DataSet，并返回DataSet
        /// </summary>
        /// <param name="filenameurl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public DataSet ExecleToDataSet(string filenameurl, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + 

";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            odda.Fill(ds, table);
            return ds;
        }

protected void ImportInfoSQL()
        {
            string a1,a2;
            decimal b1,b2;

            if (FileUpload1.HasFile == false)
            {
                Response.Write("<script>alert('请选择Excel文件')</script> ");
                return;
            }
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
            if (IsXls != ".xls")
            {
                Response.Write("<script>alert('文件扩展名不对')</script>");
                return; 
            }
            string strpath = Server.MapPath(@"\Dir\") + exname;  //获取Execle文件路径 

            //将文件保存到服务器目录下
            FileUpload1.PostedFile.SaveAs(strpath);

            string filename = FileUpload1.FileName;                       //获取Execle文件名   
            DataSet ds = ExecleToDataSet(strpath, filename);
            DataRow[] dr = ds.Tables[0].Select();                        //定义一个DataRow数组    
            int rowsnum = ds.Tables[0].Rows.Count;
            if (rowsnum == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用

户进行提示 
            }
            else
            {
                //对表中数据进行错误检查
                for (int i = 0; i < dr.Length; i++)
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("INSERT into table1(col1,col2,col3,col4) values(");
                    //逐行导入数据
                    a1=dr[i][0].ToString();
                    a2_ind=dr[i][1].ToString();

                    if (dr[i][2].ToString() == "" || dr[i][2] == null)
                    {
                        b1 = 0;
                    }
                    else
                    {
                        b2 = decimal.Parse(dr[i][2].ToString());
                    }


                    strSql.Append("'"+a1 + "','" + a2 + "'," + b1 + "," + b2 + ")");
                    //执行
                     if (DbHelperSQL.ExecuteSql(strSql.ToString()) > 0)
                        {
                            this.Label1.Text += "导入成功!\br";
                        }
                        else
                        {
                            this.Label1.Text += "导入失败!\br";
                        }
                   
                }
            }
        }