| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> oracle >> c#中高效的excel导入oracle的方法 c# zhong gao xiao de excel dao ru oracle de fang fa
 

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

2008-07-26 07:42:36 编辑來源:互联网 繁體版 评论
 
 
本文为【c#中高效的excel导入oracle的方法】的汉字拼音对照版显示拼音
  ruhegaoxiaodejiangexceldaorudaooracle?heqianliangtiandeSqlBulkCopy daorudaosqlserverduiying,oracleyeyouzishendefangfa,zhishishaoweifuduoxie.

  najiushi使shiyongoracledesql*loadergongnengersqlldrzhizhichileisicsvgeshideshujusuoyiyaozijibaexcelzhuanhuanyixia

  shixianbuzhou

  yongcomzujianduquexcel-baocunweicsvgeshi-chulizuihouyigeziduanweinulldeqingkuanghebiaotou-genjuexceljiegoujianbiao-shengchengsqlldrdekongzhiwenjian-yongsqlldrminglingdaorushuju

  zhegexingnengsuiranmeiyousqldebcpkuaidanhuanshixiangdangkeguandezaiwojiqishang1wanduoshujubudao4miaoerqiedaoruguochengdaimabijiaojiandanyetongyangmeiyouxunhuanpinjiesqlcharunamenanyiweihu

  zheliyetigewenti:chulicsvwenjiandebiaotouhezuihouyigeziduanweinulldeqingkuangshifoukeyiyouhuachulewodaimazhongdeliziwoshizaixiangbuchuqitabanfa

  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;

  //yinyong-com-microsoft excel objects 11.0

  namespace WindowsApplication5

  {

   public partial class Form1 : Form

   {

   public Form1()

   {

   InitializeComponent();

   }

  

   /// <SUMMARY>

   /// exceldaorudaooracle

   /// </SUMMARY>

   /// <PARAM name="excelFile">wenjianming</PARAM>

   /// <PARAM name="sheetName">sheetming</PARAM>

   /// <PARAM name="sqlplusString">oracleminglingsqlpluslianjiechuan</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 + "\\" + strFileName + ".Ctl";

   string strSqlPath = strTempDir + "\\" + strFileName + ".Sql";

   if (System.IO.File.Exists(strCsvPath))

   System.IO.File.Delete(strCsvPath);

  

  

   //huoquexcelduixiang

   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));

  

  

   //baocunweicsvlinshiwenjian

   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();

  

   //duqucsvwenjianxuyaojiangbiaotouqudiaobingqiejiangzuihouyilieweinulldeziduanchuliweixianshidenullfouzeoraclebuhuishibiezhegebuzhouyoumeiyouhaodetihuanfangfa?

   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();

  

  

   //huoquexcelbiaojiegou

   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 });

  

  

   //shengchengsqlldryongdaodekongzhiwenjian,wenjianjiegoushenkaosql*loadergongnengbenshiliyidouhaofengecsvshujudaidouhaodeyongyinhaokuoqilai

   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();

  

   //shengchengchushihuaoraclebiaojiegoudewenjian

   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();

  

  

   //yunxingsqlpluschushihuabiao

   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();

  

   //yunxingsqlldrdaorushuju

   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;//jiequshuchuliu

   string line = r.ReadLine();//meiciduquyixing

   textBox3.Text += line + "\r\n";

   while (!r.EndOfStream)

   {

   line = r.ReadLine();

   textBox3.Text += line + "\r\n";

   textBox3.Update();

   }

   p.WaitForExit();

  

   //keyizixingjiejuediaolinshiwenjiancsvctlhesqldaimaluequ

   }

  

   private void button1_Click(object sender, EventArgs e)

   {

   TransferData(@"D:\test.xls", "Sheet1", "username/password@servicename");

   }

  

   }

  }原文
 
 
 
 
上一篇《在Oracle中选取有父子或树状关系的数据记录》
下一篇《详细讲解Oracle数据库9i穿过防火墙问题》
 
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
如何高效的将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的情况是否可以优化?除了我代码中的例子,我实在想不出其他办法。 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导入到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 +"[url=file://\\]\\"+strFileName[/url] + ".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 &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表结构的文件 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", "[url=mailto:username/password@servicename]username/password@servicename[/url]"); } } }
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
越来越漂亮的楚楚(7)
越来越漂亮的楚楚(6)
越来越漂亮的楚楚(5)
越来越漂亮的楚楚(4)
共长一色
水中莲
峰林晚照
远离喧嚣——山村小景
 
>>返回首页<<
 为你推荐
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
©2005- 王朝网络 版权所有