如何高效的將Excel導(dǎo)入到Oracle?和前兩天的SqlBulkCopy 導(dǎo)入到sqlserver對(duì)應(yīng),oracle也有自身的方法,只是稍微復(fù)雜些.
那就是使用oracle的sql*loader功能，而sqlldr只支持類似csv格式的數(shù)據(jù)，所以要自己把excel轉(zhuǎn)換一下。
實(shí)現(xiàn)步驟：
用com組件讀取excel-保存為csv格式-處理最后一個(gè)字段為null的情況和表頭-根據(jù)excel結(jié)構(gòu)建表-生成sqlldr的控制文件-用sqlldr命令導(dǎo)入數(shù)據(jù)
這個(gè)性能雖然沒有sql的bcp快，但還是相當(dāng)可觀的，在我機(jī)器上1萬多數(shù)據(jù)不到4秒，而且導(dǎo)入過程代碼比較簡(jiǎn)單，也同樣沒有循環(huán)拼接sql插入那么難以維護(hù)。

這里也提個(gè)問題:處理csv文件的表頭和最后一個(gè)字段為null的情況是否可以優(yōu)化？除了我代碼中的例子，我實(shí)在想不出其他辦法。

view plaincopy to clipboardPRint?
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();  
        }  
   
        /// <SUMMARY>  
        /// excel導(dǎo)入到oracle  
        /// </SUMMARY>  
        /// <PARAM name="excelFile">文件名</PARAM>  
        /// <PARAM name="sheetName">sheet名</PARAM>  
        /// <PARAM name="sqlplusString">oracle命令sqlplus連接串</PARAM>  
        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 + "http://" + strFileName + ".Ctl";  
            string strSqlPath = strTempDir + "http://" + strFileName + ".Sql";  
            if (System.IO.File.Exists(strCsvPath))  
                System.IO.File.Delete(strCsvPath);  
 
 
            //獲取excel對(duì)象  
            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臨時(shí)文件  
            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不會(huì)識(shí)別，這個(gè)步驟有沒有好的替換方法?  
            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表結(jié)構(gòu)  
            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用到的控制文件,文件結(jié)構(gòu)參考sql*loader功能，本示例已逗號(hào)分隔csv，數(shù)據(jù)帶逗號(hào)的用引號(hào)括起來。     
            string strControl =  "load data/r/ninfile &apos;{0}&apos; /r/nappend into table {1}/r/n"+　　　   
　　                "FIELDS TERMINATED BY &apos;,&apos; OPTIONALLY ENCLOSED BY &apos;/"&apos;/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表結(jié)構(gòu)的文件  
            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();  
 
 
            //運(yùn)行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();  
 
            //運(yùn)行sqlldr，導(dǎo)入數(shù)據(jù)  
            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();  
 
            //可以自行解決掉臨時(shí)文件csv，ctl和sql，代碼略去  
        }  
 
        private void button1_Click(object sender, EventArgs e)  
        {  
            TransferData(@"D:/test.xls", "Sheet1", "username/passWord@servicename");  
        }  
          
    }  
}