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

2011-09-27 09:04:13 267 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

C#导入excel到数据库这几天又碰到了要导excel数据到数据库中，之前在网上也是找，东拼西凑也是把功能弄出来了，今天写个博客记录下。首先页面的代码 @using (Html.BeginForm("Browse", "OutGygx", FormMethod.Post, new { enctype = "multipart/form-data", id = "form_Upload" })) { ... 阅读详情

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

...全文

Windows 11 上 WSL2 安装 Ubuntu 22.04 保姆级教程（含桌面环境配置）本文提供在Windows 11上通过WSL2安装Ubuntu 22.04并配置桌面环境的详细教程。涵盖WSL2核心安装、性能调优、Xfce桌面环境部署以及开发环境集成，帮助开发者构建高效、无缝的Windows-Linux混合开发工作站。阅读详情

b0172716

.NET社区 62,272 成员 668,968 内容

+ 关注 + 加入社区

切换为时间正序

少多慢快 2011-09-28

打赏
举报

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

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

可以用

PitTroll 2011-09-28

打赏
举报

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

昵称不能为空噢噢噢噢 2011-09-28

打赏
举报



 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)

        {



        }

    }

}

哈哈

昵称不能为空噢噢噢噢 2011-09-28

打赏
举报



 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;

        }

昵称不能为空噢噢噢噢 2011-09-28

打赏
举报



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];

        }

永动bug制造机 2011-09-28

打赏
举报

关注中。。。

格桑花 2011-09-28

打赏
举报

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

风2013 2011-09-28

打赏
举报

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

风2013 2011-09-28

打赏
举报

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

风2013 2011-09-28

打赏
举报



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

}

CalvinR 2011-09-28

打赏
举报

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

弦弦 2011-09-28

打赏
举报



请安装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;

        }

魂千 2011-09-28

打赏
举报

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

蓬莱仙客 2011-09-28

打赏
举报

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

酷儿 2011-09-28

打赏
举报

net5354 2011-09-28

打赏
举报

]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();

//....
}

}

GHOSTSEA 2011-09-28

打赏
举报

学习中········

szjarvis 2011-09-28

打赏
举报

正在用的，参考一下：
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的代码。

dalmeeme 2011-09-27

打赏
举报

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

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

相关推荐