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;

}

}

}