c#怎么将Excel数据导入到数据库

本篇内容介绍了“c#怎么将Excel数据导入到数据库”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

假如Excel中的数据如下：

数据库建表如下：

其中Id为自增字段：

代码：

复制代码 代码如下:

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.OleDb;using System.Configuration;using System.Data.SqlClient;

namespace InExcelOutExcel{    public partial class ExcelToDB : System.Web.UI.Page    {        protected void Page_Load(object sender, EventArgs e)        {            FileSvr fileSvr = new FileSvr();            System.Data.DataTable dt = fileSvr.GetExcelDatatable("C:\\Users\\NewSpring\\Desktop\\Demo\\InExcelOutExcel\\InExcelOutExcel\\excel\\ExcelToDB.xlsx", "mapTable");            fileSvr.InsetData(dt);        }    }    class FileSvr    {        ///         /// Excel数据导入Datable        ///         ///         ///         ///         public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)        {            //office2007之前 仅支持.xls            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

            System.Data.DataTable dt = null;            //建立连接            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));            try            {                //打开连接                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)                {                    conn.Open();                }

                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取Excel的第一个Sheet名称                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                //查询sheet中的数据                string strSql = "select * from [" + sheetName + "]";                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);                DataSet ds = new DataSet();                da.Fill(ds, table);                dt = ds.Tables[0];

                return dt;            }            catch (Exception exc)            {                throw exc;            }            finally            {                conn.Close();                conn.Dispose();            }

        }

        ///         /// 从System.Data.DataTable导入数据到数据库        ///         ///         ///         public int InsetData(System.Data.DataTable dt)        {            int i = 0;            string lng = "";            string lat = "";            string offsetLNG = "";            string offsetLAT = "";

            foreach (DataRow dr in dt.Rows)            {                lng = dr["LNG"].ToString().Trim();                lat = dr["LAT"].ToString().Trim();                offsetLNG = dr["OFFSET_LNG"].ToString().Trim();                offsetLAT = dr["OFFSET_LAT"].ToString().Trim();

                //sw = string.IsNullOrEmpty(sw) ? "null" : sw;                //kr = string.IsNullOrEmpty(kr) ? "null" : kr;

                string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values ('{0}','{1}',{2},{3})", lng, lat, offsetLNG, offsetLAT);

                string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString();                SqlConnection sqlConnection = new SqlConnection(strConnection);                try                {                    // SqlConnection sqlConnection = new SqlConnection(strConnection);                    sqlConnection.Open();                    SqlCommand sqlCmd = new SqlCommand();                    sqlCmd.CommandText = strSql;                    sqlCmd.Connection = sqlConnection;                    SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();                    i++;                    sqlDataReader.Close();                }                catch (Exception ex)                {                    throw ex;                }                finally                {                    sqlConnection.Close();

                }                //if (opdb.ExcSQL(strSql))                //    i++;            }            return i;        }    }}

运行结果：

“c#怎么将Excel数据导入到数据库”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：[email protected]进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。