C#将Excel表格中的数据导入SQL数据库
private static string _filePath=string.Empty ;
#region Excel导入SQL数据库
/// <summary>
/// 获取Excel数据表列表
/// </summary>
/// <returns></returns>
public static ArrayList GetExcelTables()
{
//将Excel架构存入数据里
System.Data.DataTable dt = new System.Data.DataTable();
ArrayList TablesList=new ArrayList();
if (File.Exists(FilePath))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet."+
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath))
{
try
{
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
}
catch (Exception exp)
{
Log.StrFileName ="查询Excel表单名";
Log.StrDepartment ="Excel架构存入数据";
Log.StrDescription =exp.ToString ();
Log.WriteLog ();
}
//获取数据表个数
int tablecount=dt.Rows.Count;
for (int i=0;i<tablecount;i=i+2)
{
string tablename=dt.Rows[i][2].ToString().Trim().TrimEnd('$');
if(TablesList.IndexOf(tablename)<0)
{
TablesList.Add(tablename);
}
}
}
}
return TablesList;
}
/// <summary>
/// 导入Excel数据表至DataTable(第一行作为表头)
/// </summary>
/// <returns></returns>
public static System.Data.DataSet FillDataSet()
{
if (!File.Exists(FilePath))
{
throw new Exception("Excel文件不存在!");
}
ArrayList TableList=new ArrayList();
TableList = GetExcelTables();
if(TableList.Count <=0)
{
return null;
}
System.Data.DataTable table ;
System.Data .DataSet ds=new DataSet ();
OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
for(int i=0;i<TableList.Count ;i++)
{
string dtname=TableList[i].ToString ();
try
{
OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
table=new DataTable (dtname);
adapter.Fill(table);
ds.Tables .Add (table);
}
catch (Exception exp)
{
Log.StrFileName =dtname;
Log.StrDepartment ="将Excel表格导入DataSet";
Log.StrDescription =exp.ToString ();
Log.WriteLog ();
}
}
}
finally
{
if (dbcon.State == ConnectionState.Open)
{
dbcon.Close();
}
}
return ds;
}
/// <summary>
/// Excel导入数据库
/// </summary>
/// <returns></returns>
public static DataSet ImportFromExcel()
{
return FillDataSet();
}
#endregion