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
    {
        /// <summary>
        /// excel数据导入datable
        /// </summary>
        /// <param name="fileurl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        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();
            }

        }

        /// <summary>
        /// 从system.data.datatable导入数据到数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        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;
        }
    }
}

运行结果：