Excel 导入数据库

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// ExcellToDataBase 的摘要说明
/// </summary>
public class ExcellToDataBase
{
/// <summary>
/// 数据连接配置
/// </summary>

/// <param name="cmdText">Execll连接版本</param>
public ExcellToDataBase( string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'")
{

CmdTest = cmdText;
//
// TODO: 在此处添加构造函数逻辑
//
}
public string CmdTest { get; set; }

/// <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代表第一行是标题，不是数据；
string cmdText = CmdTest ;//可修改//

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 use_id = "";
string mee_room_id = "";
string use_row = "";
string use_column = "";

foreach (DataRow dr in dt.Rows)
{
use_id = dr["nid"].ToString().Trim();//可修改//
mee_room_id = dr["ntitle"].ToString().Trim();//可修改//
use_row = dr["content"].ToString().Trim();//可修改//
use_column = dr["create"].ToString().Trim();//可修改//

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

string strSql = string.Format(@"Insert into News (ntitle,content,createTime
) Values ('{0}','{1}','{2}')", mee_room_id, use_row, use_column);//可修改//

try
{
// SqlConnection sqlConnection = new SqlConnection(strConnection);
//sqlConnection.Open();
//SqlCommand sqlCmd = new SqlCommand();
//sqlCmd.CommandText = strSql;
//sqlCmd.Connection = sqlConnection;
//SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
DBHelper.ExecuteNonQuery(strSql);
i++;
//sqlDataReader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{

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