c# 导入excel 到 oracle 数据库,c#中高效的excel导入oracle的方法(2)

//获取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();