c#中高效的excel导入oracle的方法

c#中高效的excel导入oracle的方法

2009年04月21日 星期二 下午 07:57

如何高效的将excel导入到oracle?和前两天的SqlBulkCopy 导入到sqlserver对应,oracle也有自身的方法,只是稍微复杂些. 那就是使用oracle的sql*loader功能，而sqlldr只支持类似csv格式的数据，所以要自己把excel转换一下。 实现步骤： 用com组件读取excel-保存为csv格式-处理最后一个字段为null的情况和表头-根据excel结构建表-生成sqlldr的控制文件-用sqlldr命令导入数据 这个性能虽然没有sql的bcp快，但还是相当可观的，在我机器上1万多数据不到4秒，而且导入过程代码比较简单，也同样没有循环拼接sql插入那么难以维护。 这里也提个问题:处理csv文件的表头和最后一个字段为null的情况是否可以优化？除了我代码中的例子，我实在想不出其他办法。 using System; using System.Data; using System.Text; using System.Windows.Forms; using Microsoft.Office.Interop.Excel; using System.Data.OleDb; //引用-com-microsoft excel objects 11.0 namespace WindowsApplication5 {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();         }           ///         /// excel导入到oracle         ///         /// 文件名         /// sheet名         /// oracle命令sqlplus连接串         public void TransferData(string excelFile, string sheetName, string sqlplusString)         {             string strTempDir = System.IO.Path.GetDirectoryName(excelFile);             string strFileName = System.IO.Path.GetFileNameWithoutExtension(excelFile);             string strCsvPath = strTempDir +"\\"+strFileName + ".csv";             string strCtlPath = strTempDir + "\\" + strFileName + ".Ctl";             string strSqlPath = strTempDir + "\\" + strFileName + ".Sql";             if (System.IO.File.Exists(strCsvPath))                 System.IO.File.Delete(strCsvPath);               //获取excel对象             Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();               Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;               Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet = null;               ObjWorkBook = ObjExcel.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);               foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in ObjWorkBook.Sheets)             {                 if (sheet.Name.ToLower() == sheetName.ToLower())                 {                     ObjWorkSheet = sheet;                     break;                 }             }             if (ObjWorkSheet == null) throw new Exception(string.Format("{0} not found!!", sheetName));               //保存为csv临时文件             ObjWorkSheet.SaveAs(strCsvPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, false, false, false, Type.Missing, Type.Missing, false);             ObjWorkBook.Close(false, Type.Missing, Type.Missing);             ObjExcel.Quit();               //读取csv文件，需要将表头去掉，并且将最后一列为null的字段处理为显示的null，否则oracle不会识别，这个步骤有没有好的替换方法?             System.IO.StreamReader reader = new System.IO.StreamReader(strCsvPath,Encoding.GetEncoding("gb2312"));             string strAll = reader.ReadToEnd();             reader.Close();             string strData = strAll.Substring(strAll.IndexOf("\r\n") + 2).Replace(",\r\n",",Null");               byte[] bytes = System.Text.Encoding.Default.GetBytes(strData);             System.IO.Stream ms = System.IO.File.Create(strCsvPath);             ms.Write(bytes, 0, bytes.Length);             ms.Close();               //获取excel表结构             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";             OleDbConnection conn = new OleDbConnection(strConn);             conn.Open();             System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,                 new object[] { null, null, sheetName+"$", null });               //生成sqlldr用到的控制文件,文件结构参考sql*loader功能，本示例已逗号分隔csv，数据带逗号的用引号括起来。               string strControl = "load data\r\ninfile '{0}' \r\nappend into table {1}\r\n"+　　　 　　                "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\r\n(";　　             strControl = string.Format(strControl, strCsvPath,sheetName);             foreach (System.Data.DataRow drowColumns in table.Select("1=1", "Ordinal_Position"))             {                 strControl += drowColumns["Column_Name"].ToString() + ",";             }               strControl = strControl.Substring(0, strControl.Length - 1) + ")";             bytes=System.Text.Encoding.Default.GetBytes(strControl);             ms= System.IO.File.Create(strCtlPath);               ms.Write(bytes, 0, bytes.Length);             ms.Close();               //生成初始化oracle表结构的文件            string strSql = @"drop table {0};　　　　　　　　　　  　　                create table {0}　 　　                (";             strSql = string.Format(strSql, sheetName);             foreach (System.Data.DataRow drowColumns in table.Select("1=1", "Ordinal_Position"))             {                 strSql += drowColumns["Column_Name"].ToString() + " varchar2(255),";             }             strSql = strSql.Substring(0, strSql.Length - 1) + ");\r\nexit;";             bytes = System.Text.Encoding.Default.GetBytes(strSql);             ms = System.IO.File.Create(strSqlPath);               ms.Write(bytes, 0, bytes.Length);             ms.Close();               //运行sqlplus，初始化表             System.Diagnostics.Process p = new System.Diagnostics.Process();             p.StartInfo = new System.Diagnostics.ProcessStartInfo();             p.StartInfo.FileName = "sqlplus";             p.StartInfo.Arguments = string.Format("{0} @{1}", sqlplusString, strSqlPath);             p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;             p.StartInfo.UseShellExecute = false;             p.StartInfo.CreateNoWindow = true;             p.Start();             p.WaitForExit();               //运行sqlldr，导入数据             p = new System.Diagnostics.Process();             p.StartInfo = new System.Diagnostics.ProcessStartInfo();             p.StartInfo.FileName = "sqlldr";             p.StartInfo.Arguments = string.Format("{0} {1}", sqlplusString, strCtlPath);             p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;             p.StartInfo.RedirectStandardOutput = true;             p.StartInfo.UseShellExecute = false;             p.StartInfo.CreateNoWindow = true;             p.Start();             System.IO.StreamReader r = p.StandardOutput;//截取输出流             string line = r.ReadLine();//每次读取一行             textBox3.Text += line + "\r\n";             while (!r.EndOfStream)             {                 line = r.ReadLine();                 textBox3.Text += line + "\r\n";                 textBox3.Update();             }             p.WaitForExit();               //可以自行解决掉临时文件csv，ctl和sql，代码略去         }           private void button1_Click(object sender, EventArgs e)         {             TransferData(@"D:\test.xls", "Sheet1", "username/password@servicename");         }             } }

 

转载于:https://www.cnblogs.com/hanhaodong/archive/2010/06/30/1768167.html