求一个Excel导入数据库的代码，网上找的都不给力啊~~~

[b0172716]

如题，要详细的，包括命名空间，前后台代码，最好有注释，呵呵，省的再百度了，网上找了好多，都有这样那样的问题，都不详细，大家谁有做过这个的，发下吧~~数据库为SQL2005

[dalmeeme]

通过C#使Excel数据导入数据库

[szjarvis]

正在用的，参考一下：
protected void btnImport_Click(object sender, EventArgs e)
{
OleDbConnection xlsConn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + Server.MapPath("xx.xls") + "; Extended Properties = 'Excel 8.0; HDR=YES; IMEX=1'");
xlsConn.Open();

string xlsSQL = "select * from [sheet1$]";
OleDbCommand xlsCmd = new OleDbCommand(xlsSQL, xlsConn);
OleDbDataReader dr = xlsCmd.ExecuteReader();

string strSQL = "", strErr = "";
ArrayList arySQL = new ArrayList();
while (dr.Read())
{
#region 初始化数据
string v1 = (dr[1].ToString().Trim() == "") ? "" : dr[1].ToString();
string v2 = (dr[2].ToString().Trim() == "") ? "" : dr[2].ToString();
string v3 = (dr[3].ToString().Trim() == "") ? "" : dr[3].ToString();
string v4 = (dr[4].ToString().Trim() == "") ? "" : dr[4].ToString();
string v5 = (dr[5].ToString().Trim() == "") ? "" : dr[5].ToString();
string v6 = (dr[6].ToString().Trim() == "") ? "" : dr[6].ToString();
string v7 = (dr[7].ToString().Trim() == "") ? "" : dr[7].ToString();
string v8 = (dr[8].ToString().Trim() == "") ? "" : dr[8].ToString();
string v9 = (dr[9].ToString().Trim() == "") ? "" : dr[9].ToString();
string v10 = (dr[10].ToString().Trim() == "") ? "" : dr[10].ToString();
string v11 = (dr[11].ToString().Trim() == "") ? "" : dr[11].ToString();
string v12 = (dr[12].ToString().Trim() == "") ? "" : dr[12].ToString();
float v13 = (dr[13].ToString().Trim() == "") ? 0 : Math.Abs(Convert.ToSingle(dr[13].ToString()));
string v14 = (dr[14].ToString().Trim() == "") ? "" : dr[14].ToString();
string v15 = (dr[15].ToString().Trim() == "") ? "" : dr[15].ToString();
string v16 = (dr[16].ToString().Trim() == "") ? "" : dr[16].ToString();
string v17 = (dr[17].ToString().Trim() == "") ? "" : dr[17].ToString();
string v18 = (dr[18].ToString().Trim() == "") ? "" : dr[18].ToString();
#endregion

strSQL = "insert into asset_asset(asset_typeid,asset_sclassid,asset_no,asset_name,asset_model,asset_sn,asset_collocate,asset_pinpai,asset_providerid,asset_buyer,asset_buydate,asset_buyprice,asset_memo,asset_keeper,asset_keepdate,asset_statusid,asset_empno,asset_adddate)";
strSQL += " values('" + v1 + "','" + v2 + "','" + v3 + "','" + v4 + "','" + v5 + "','" + v6 + "','" + v7 + "','" + v8 + "','" + v9 + "','" + v10 + "','" + v11 + "','" + v12 + "'," + v13 + ",'" + v14 + "','" + v15 + "','" + v16 + "','" + v17 + "')";
arySQL.Add(strSQL);
}

dr.Close();
dr.Dispose();
xlsConn.Close();
xlsConn.Dispose();
}

DbConnSql.ExcuSS(arySQL); // 此处的DbConnSql是一个类，你可以自己写一个执行sql的代码。

[GHOSTSEA]

学习中········

[net5354]

]1.上传EXCEL文件到服务器 2.连接EXCEL 3.逐条插入SQL
if (FileUpload1.HasFile)//上传控件FileUpload1 {
string filenameid = System.DateTime.Now.ToString("yyyyMMddhhmmssff") + ".xls";//定义文件名为年:2008.xsl的格式
string serverpath = Server.MapPath("~/xls/") + filenameid;//保存路径
FileUpload1.PostedFile.SaveAs(serverpath);//保存上传的Excel文件

//连接Excel文件，读入myDstaSet
String strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", serverpath);//excel2003的
OleDbConnection Excel_conn = new OleDbConnection(strConnectionString);
Excel_conn.Open();
DataTable dtExcelSchema = Excel_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//建立连接Excel的数据表
string SheetName = "";
SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();//取出第一个工作表我名称
Excel_conn.Close();
//Label1.Text = SheetName;

string query = "SELECT * FROM " + "[" + SheetName + "]";//查询Excel字符串
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, Excel_conn);
DataSet myDataSet = new DataSet();
Excel_conn.Open();
oleAdapter.Fill(myDataSet, "Excel_Sheet1");
Excel_conn.Close();

GridView1.DataSource = myDataSet.Tables["Excel_Sheet1"];
GridView1.DataBind();

sqlcon = new SqlConnection(strCon);//srtCom是连接数据库字符串，自己写
for (int i = 0; i <= myDataSet.Tables["Excel_Sheet1"].Rows.Count - 1; i++)
{
string intostr = "insert into chengji" +//....插入语句自己写

sqlcon.Open();
sqlcom = new SqlCommand(intostr, sqlcon);
sqlcom.ExecuteNonQuery();//添加插入
sqlcon.Close();

//....
}

}

[酷儿]

楼主 度娘上面 可以教会你很多 如果你自己不用心去思考 那以后你永远不懂得如果去把别人的代码转换成你自己需要的 那么以后你有问题不可能每次都有人给你贴代码. 自己好好想想吧 思想才是最重要的 代码只是实现的一种方式而已

[蓬莱仙客]

楼主 度娘上面 可以教会你很多 如果你自己不用心去思考 那以后你永远不懂得如果去把别人的代码转换成你自己需要的 那么以后你有问题不可能每次都有人给你贴代码. 自己好好想想吧 思想才是最重要的 代码只是实现的一种方式而已

[魂千]

想问问LZ怎么才算是给力？

[弦弦]

请安装office驱动。
        public static DataSet ExcelToDS(string Pathm, string TableName, string type)
        {
            DataSet ds = new DataSet();
            string strConn = "";
            if (type == "excel2003")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Pathm + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            }
            else if (type == "excel2007")
            {
                strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + Pathm + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            }
            try
            {
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]", TableName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, TableName);
            }
            catch (Exception)
            {
                return ds;
            }
            return ds;
        }

[CalvinR]

6楼的代码就挺好的
楼主参考一下吧

[风2013]

public void DataSet()
{   
         string type2 = FileUpload1.FileName;
            var type3 = type2.Substring(type2.LastIndexOf('.'));
            if(type3.Equals(".xls") || type3.Equals(".xlsx"))
            {
                string newName = Server.MapPath("..//App_Data//Uploads//") + DateTime.Now.ToString("hhmmss") + ".xls";

                FileUpload1.SaveAs(newName);
                string connStr = string.Empty;

                if (type3.Equals(".xlsx"))
                {
                    connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + newName +
                              ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
                }

                else if (type3.Equals(".xls"))
                {
                    connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + newName +
                              ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
                }
                OleDbConnection conn = new OleDbConnection(connStr);
                if (conn.State.ToString() == "Closed")
                {
                    conn.Open();
                }
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var tableName = dt.Rows[0][2].ToString().Trim();
                var str1 = string.Format("Select * from [{0}]", tableName+"A:E");
                OleDbDataAdapter oda = new OleDbDataAdapter(str1, conn);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                conn.Close();
                File.Delete( newName );
                return ds
}

[风2013]

这是我目前用的方法 你不妨试一试

[风2013]

var str1 = string.Format("Select * from [{0}]", tableName+"A:E");
这一句 你自己看着办改 A:E 只读取五列 如果不需要限制 +"A:E" 就去掉这一段

[格桑花]

LZ试下这个，里面有现成的代码，有注释，

[永动bug制造机]

关注中。。。

[昵称不能为空噢噢噢噢]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace _8
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 带事务的批量操作数据。
        /// </summary>
        /// <param name="sqlInsert">SQL</param>
        /// <returns>操作是否成功</returns>
        public static bool OperateDatasWithTransaction(List<string> sqlList)
        {
            SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;
            // Start a local transaction
            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                foreach (string item in sqlList)
                {
                    myCommand.CommandText = item;
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                return true;
            }
            catch (Exception)
            {
                myTrans.Rollback();
            }
            finally
            {
                myConnection.Close();
            }
            return false;
        }
        private System.Data.DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds = new DataSet();
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }

            if (ds == null)
            {
                return null;
            }

            if (ds.Tables.Count < 1)
            {
                return null;
            }

            return ds.Tables[0];
        }

[昵称不能为空噢噢噢噢]

 protected void AddExcel(DataSet ds, ref bool s)
        {
            try
            {
                 System.Data.DataTable dt = ds.Tables[0];

              // string fileName ="C:\\数据处理文件夹\\"+ Guid.NewGuid() + ".xls";
                 //string str = DateTime.Now.ToShortDateString();
               // string[] strar=str.Split(' ');
               // string str1 =strar[0]+strar[1]; 
                 SaveFileDialog sf = new SaveFileDialog();
                 sf.InitialDirectory = "C:\\";
                 sf.Filter = "excel文件（*.xls）|*.xls";
                 
               if (sf.ShowDialog()==DialogResult.OK)
              {
                  string fileName = sf.FileName; //"C:\\数据处理文件夹\\" +str+ ".xls";
                Excel.Application excel = new Excel.ApplicationClass();

                int rowIndex = 1;
                int colIndex = 0;

                excel.Application.Workbooks.Add(true);

                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                foreach (DataRow row in dt.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                    {
                        excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
                    }
                }

                excel.Visible = false;
               // excel.ActiveWorkbook.s
                excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                //excel.Save(fileName); 

                excel.Quit();
                excel = null;

                GC.Collect();//垃圾回收
            }
            }
            catch
            {
                s = false;
            }
       // }

        }


        private void button1_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.InitialDirectory = "C:\\";
            this.openFileDialog1.Filter = "excel文件（*.xls）|*.xls";
            this.openFileDialog1.FilterIndex = 2;
            this.openFileDialog1.RestoreDirectory = true;
            this.openFileDialog1.Title = "打开excel文件";
            if (openFileDialog1.ShowDialog()==DialogResult.OK)
            
            {
                string sss = openFileDialog1.FileName;
                textBox1.Text = sss;
            System.Data.DataTable dt;
            //OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
            string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
           //string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
           // string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString; 
            SqlConnection conn = new SqlConnection(constr);
            bool success = true;
            dt = GetExcelTable(sss);
            success = input(dt, conn, success,"Budget");//总额
           // success = input(dt, conn, success, "Budget_Detail");//明细

            }
        }

        private bool input(System.Data.DataTable dt, SqlConnection conn, bool success,string flag)
        {
            if (dt != null)
            {
                try
                {

                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    List<string> L = new List<string>();


                    if (flag == "Budget")
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i].ItemArray[0].ToString() != "")
                            {
                                string s = "insert into [Budget] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "')";
                                //  OleDbCommand cmd = new OleDbCommand( s,conn);
                                L.Add(s);
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i].ItemArray[0].ToString() != "")
                            {
                                string s = "insert into [Budget_Detail] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "','" + dt.Rows[i].ItemArray[4] + "','" + dt.Rows[i].ItemArray[5] + "','" + dt.Rows[i].ItemArray[6] + "','" + dt.Rows[i].ItemArray[9] + "')";
                                //  OleDbCommand cmd = new OleDbCommand( s,conn);
                                L.Add(s);
                            }
                        }
                    }

                    if (!OperateDatasWithTransaction(L))
                    {
                        success = false;
                    }

                }
                catch (Exception eer)
                {
                    MessageBox.Show(eer.ToString());
                    success = false;
                    conn.Close();
                }
                if (success)
                {
                    label1.Text = "导入成功";
                    if (flag == "Budget")
                        MessageBox.Show("预算资金总账导入成功！", "信息提示");
                    else
                        MessageBox.Show("预算资金明细导入成功!", "信息提示");

                }
                else
                {
                    label1.Text = "导入失败";
                    if (flag == "Budget")
                        MessageBox.Show("预算资金总账导入失败！", "信息提示");
                    else
                        MessageBox.Show("预算资金明细导入失败!", "信息提示");
                }
            }
            return success;
        }

[昵称不能为空噢噢噢噢]

 private void button3_Click(object sender, EventArgs e)
        {
            cleartb();

        }

        private void cleartb()
        {
            string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
            bool w = true;
            SqlConnection conn = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = "truncate table [Budget]  truncate table [Budget_Detail]";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                w = false;
            }
            if (w)

            { 
                label1.Text = "表已清空";
                MessageBox.Show("表已清空","信息提示");
            }

            else
            { label1.Text = "清空失败"; MessageBox.Show("表清空失败", "信息提示"); }
        }
        private void cleartbing()
        {
            string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
            bool w = true;
            SqlConnection conn = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = "truncate table [Order]";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                w = false;
            }
            if (!w)
            label1.Text = "处理数据失败";
        }

        private void button2_Click(object sender, EventArgs e)
        {
           this.openFileDialog2.InitialDirectory = "C:\\";
            this.openFileDialog2.Filter = "excel文件（*.xls）|*.xls";
            this.openFileDialog2.FilterIndex = 2;
            this.openFileDialog2.RestoreDirectory = true;
            this.openFileDialog2.Title = "打开excel文件";
            if (openFileDialog2.ShowDialog() == DialogResult.OK)
            {
                string sss = openFileDialog2.FileName;
                textBox2.Text = sss;
                System.Data.DataTable dt;
                //OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
                string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
                //string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
                // string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString; 
                SqlConnection conn = new SqlConnection(constr);
                bool success = true;
                dt = GetExcelTable(sss);
                //success = input(dt, conn, success, "Budget");//总额
                 success = input(dt, conn, success, "Budget_Detail");//明细
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }
    }
}

哈哈

[PitTroll]

excel导入的话，是不是还要对excel中的内容做处理、做验证、做提示。如果直接导入那很简单的。但是如果要做特殊处理，那就要自己写业务逻辑判处理了，例如：有的不能为空的列是否输入了空值，有些需要验证的列是否格式不对，数据行是否出现重复，数据行是否与现有数据库中的内容出现重复等等。这些都是需要考虑的。金蝶的一些产品这些方面做的很不友好。

[少多慢快]

[Quote=引用 2 楼 net5354 的回复:]

]1.上传EXCEL文件到服务器 2.连接EXCEL 3.逐条插入SQL
if (FileUpload1.HasFile)//上传控件FileUpload1 {
string filenameid = System.DateTime.Now.ToString("yyyyMMddhhmmssff") + ".xls";//定义文件名为年:2008.xsl的格式
……
[/Quote]

可以用