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;

        }

    }

}