将Excel导入到Oracle

绮梦6634

private void btnInsert_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
ds = ImportExcel(openFileDialog1.FileName);
else
return;
Thread thread = new Thread(new ThreadStart(InsertDBMeth));
thread.Start();
}
DataSet ds;
/// <summary>
/// 插入数据库方法
/// </summary>
private void InsertDBMeth()
{
int odr = 0;
OracleConnection conn = new OracleConnection(DBHelper.strConnection);//获得conn连接   
try
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into TestInfo values(:CID,:PathSid,:CName,:Sid)";
int dsLength = ds.Tables[0].Rows.Count;//获得Excel中数据长度   
for (int i = 1; i < dsLength; i++)
{
cmd.Parameters.Add("CID", OracleType.Number).Value = ds.Tables[0].Rows[0];
cmd.Parameters.Add("PathSid", OracleType.VarChar).Value = ds.Tables[0].Rows[1];
cmd.Parameters.Add("CName", OracleType.VarChar).Value = ds.Tables[0].Rows[2];
cmd.Parameters.Add("Sid", OracleType.Number).Value = ds.Tables[0].Rows[3];
odr = cmd.ExecuteNonQuery();//提交
}
//如果查到了数据，才使控制分页按钮生效   
if (odr > 0)
{
MessageBox.Show("插入成功");
}
conn.Close();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 将Excel保存到DataSet
/// </summary>
/// <param name="file">Excel表名</param>
/// <returns></returns>
private static DataSet ImportExcel(string file)
{
DataSet dsExcel = new DataSet();
FileInfo fileinto = new FileInfo(file);
if (!fileinto.Exists)
return null;
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file +
";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
string strSql = "select * from [社区表$]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
da.Fill(dsExcel);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn.Close();
}
return dsExcel;
}
　　获取Excel文件的第一个表名

    /// <summary>
/// C#中获取Excel文件的第一个表名
/// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字. 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么. 以下代码就是实现这个功能的:
/// </summary>
/// <param name="excelFileName"></param>
/// <returns></returns>
public static string GetExcelFirstTableName(string excelFileName)
{
string tableName = null;
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tableName = dt.Rows[0][2].ToString().Trim();
}
}
return tableName;
}