c#开发的从excel到oracle的导入过程

最近需要实现excel表导入oracle,网上浏览了几种方法,但是各有千秋,把我的代码拿给大家交流一下,好的,let's go!

首先,添加名为microsoft excel 11.o object liberary的com引用,这样您就可以

using Microsoft.Office.Interop.Excel;其次,您系统需要安装有oracleclient.

访问excel利用com接口,而访问oracle利用oracleclient来进行:

1.基于oraclelient的一个数据库访问类OracleDataBase;

2.访问excel并导入oracle的一个类import;

3.重设字段类型需采用的可编辑Listview类;

4.应用程序示例

1.基于oraclelient的一个数据库访问类OracleDataBase;

Code
 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4using System.Data;
 5using System.Collections;
 6using System.Collections.Specialized;
 7using System.Configuration;
 8using System.Data.OracleClient;
 9using System.IO;
10
11
12
13
14    /**//// <summary>
15    /// Summary description for OracleDataBase
16    /// </summary>
17    public class OracleDataBase
18    {
19
20        public   static string connectionString = "Data Source=yourSour;User ID=yourID;Password=yourPassword;Unicode=True";
21        public OracleDataBase()
22        {
23            //
24            // TODO: Add constructor logic here
25            //
26        }
27
28                      
29        /**//// <summary>
30        /// 执行多条SQL语句，实现数据库事务。
31        /// </summary>
32        /// <param name="SQLStringList">多条SQL语句</param>        
33        public static void ExecuteSqlTran(ArrayList SQLStringList)
34        {
35            using (OracleConnection conn = new OracleConnection(connectionString))
36            {
37                conn.Open();
38                OracleCommand cmd = new OracleCommand();
39                cmd.Connection = conn;
40                OracleTransaction tx = conn.BeginTransaction();
41                cmd.Transaction = tx;
42                try
43                {
44                    for (int n = 0; n < SQLStringList.Count; n++)
45                    {
46                        string strsql = SQLStringList[n].ToString();
47                        if (strsql.Trim().Length > 1)
48                        {
49                            cmd.CommandText = strsql;
50                            cmd.ExecuteNonQuery();
51                        }
52                    }
53                    tx.Commit();
54                }
55                catch (System.Data.OracleClient.OracleException E)
56                {
57                    tx.Rollback();
58                    throw new Exception(E.Message);
59                }
60            }
61        }
62}

 

2访问excel并导入oracle的一个类import

Code
  1using System;
  2using System.Data ;
  3using System.Collections.Generic;
  4using System.Text;
  5using Microsoft.Office.Interop.Excel;
  6using System .Collections;
  7using System.Windows .Forms ;
  8//在将excel文件导入数据库之前，注意设置规范的表名，列名，列名必须在第一行，日期类型输入格式为'yyyy-mm-dd'，有空格会抛出异常
  9
 10namespace datamanager2
 11{
 12   /**//// <summary>
 13   /// 导入导出类
 14   /// </summary>
 15    class Import
 16    {
 17        
 18        
 19        /**//// <summary>
 20        /// 从工作表获得字段名称集合
 21        /// </summary>
 22        /// <param name="pWorksheet"></param>
 23        /// <returns></returns>
 24        public List<string> GetColumnNames(string sPathName,string sheetName)
 25        {
 26            try
 27            {
 28                List<string> str = new List<string>();
 29                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
 30                Workbook ObjWorkBook;
 31                Worksheet ObjWorkSheet = null;
 32                ObjWorkBook = ObjExcel.Workbooks.Open(sPathName, 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);
 33                foreach (Worksheet sheet in ObjWorkBook.Sheets)
 34                {
 35                    if (sheet.Name.ToLower() == sheetName.ToLower())
 36                    {
 37                        ObjWorkSheet = sheet;
 38                        break;
 39                    }
 40                }
 41                int colCount = ObjWorkSheet.UsedRange.Columns.Count;
 42                if (colCount == 0)
 43                    throw new Exception(string.Format("{0} is empty!!", "worksheet"));
 44                else
 45                {
 46                    for (int i = 1; i <= colCount; i++)
 47                    {
 48                        Range cell = (Range)ObjWorkSheet.Cells[1, i];
 49                        str.Add(cell.Value2.ToString());
 50                    }
 51
 52
 53                }
 54                ObjExcel.Workbooks.Close();
 55                
 56               
 57                return str;
 58            }
 59            catch (Exception e)
 60            {
 61                throw new Exception(e.Message);
 62            }
 63
 64               
 65 
 66        }
 67       
 68       
 69
 70       
 71        /**//// <summary>
 72        /// 获得列数
 73        /// </summary>
 74        /// <param name="sPathName"></param>
 75        /// <param name="sheetName"></param>
 76        /// <returns></returns>
 77        public int GetColCount(string sPathName, string sheetName)
 78        {
 79            try
 80            {
 81                string strTempDir = System.IO.Path.GetDirectoryName(sPathName);
 82                int colCount = 0;
 83
 84                //获取excel对象
 85                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
 86                Workbook ObjWorkBook;
 87                Worksheet ObjWorkSheet = null;
 88                ObjWorkBook = ObjExcel.Workbooks.Open(sPathName, 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);
 89                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in ObjWorkBook.Sheets)
 90                {
 91                    if (sheet.Name.ToLower() == sheetName.ToLower())
 92                    {
 93                        ObjWorkSheet = sheet;
 94                        break;
 95                    }
 96                }
 97                if (ObjWorkSheet == null)
 98                    throw new Exception(string.Format("{0} not found!!", sheetName));
 99                colCount = ObjWorkSheet.Columns.Count;
100
101                ObjExcel.Workbooks.Close();
102                return colCount;
103            }
104            catch (Exception e)
105            {
106                throw new Exception(e.Message);
107            }
108
109
110
111        }
112        /**//// <summary>
113        /// 获得行数
114        /// </summary>
115        /// <param name="sPathName"></param>
116        /// <param name="sheetName"></param>
117        /// <returns></returns>
118        public int GetRowsCount(string sPathName, string sheetName)
119        {
120            try
121            {
122                string strTempDir = System.IO.Path.GetDirectoryName(sPathName);
123                int rowCount = 0;
124
125                //获取excel对象
126                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
127                Workbook ObjWorkBook;
128                Worksheet ObjWorkSheet = null;
129                ObjWorkBook = ObjExcel.Workbooks.Open(sPathName, 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);
130                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in ObjWorkBook.Sheets)
131                {
132                    if (sheet.Name.ToLower() == sheetName.ToLower())
133                    {
134                        ObjWorkSheet = sheet;
135                        break;
136                    }
137                }
138                if (ObjWorkSheet == null)
139                    throw new Exception(string.Format("{0} not found!!", sheetName));
140
141                rowCount = ObjWorkSheet.Rows.Count;
142                ObjExcel.Workbooks.Close();
143
144                return rowCount;
145            }
146            catch (Exception e)
147            {
148                throw new Exception(e.Message);
149            }
150
151
152
153        }
154        /**//// <summary>
155        /// 获得所有的工作表名
156        /// </summary>
157        /// <param name="sPathName"></param>
158        /// <returns></returns>
159        public ArrayList GetAllSheetNames(string sPathName)
160        {
161            try
162            {
163
164                ArrayList arra = new ArrayList();
165                string strTempDir = System.IO.Path.GetDirectoryName(sPathName);
166                string fName = System.IO.Path.GetFileName(sPathName);
167
168
169                //获取excel对象
170                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
171                Workbook ObjWorkBook;
172
173                ObjWorkBook = ObjExcel.Workbooks.Open(sPathName, 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);
174                foreach (Worksheet sheet in ObjWorkBook.Sheets)
175                {
176                    arra.Add(sheet.Name);
177
178
179                }
180                ObjExcel.Workbooks.Close();
181                return arra;
182            }
183            catch (Exception e)
184            {
185                throw new Exception(e.Message);
186            }
187            
188        }
189
190        /**//// <summary>
191        /// 从ListView中获得字段名称、字段类型、是否主键
192        /// </summary>
193        /// <param name="myListView"></param>
194        /// <returns></returns>
195        public List<ArrayList> getValuesFromListView(myEditListView myListView)
196        {
197            try
198            {
199
200                List<ArrayList> tempContainer = new List<ArrayList>();
201
202                int colCount = myListView.Columns.Count;
203                foreach (ListViewItem lvitem in myListView.Items)
204                {
205                    ArrayList tempArray = new ArrayList();
206                    for (int i = 0; i < colCount; i++)
207
208                        tempArray.Add(lvitem.SubItems[i].Text);
209                    tempContainer.Add(tempArray);
210                }
211                return tempContainer;
212            }
213            catch (Exception e)
214            {
215                throw new Exception(e.Message);
216            }
217        }
218
219        /**//// <summary>
220        /// excel导入oracle
221        /// </summary>
222        /// <param name="ws"></param>
223        /// <param name="tableName"></param>
224        /// <param name="arraList"></param>
225        
226        public void excelToBase(string sPathName,string sheetName, string tableName, List<ArrayList> arraList)
227        {
228            try
229            {
230                string sqlStr0 = "select count(*) from all_tables where table_name='" + tableName.ToUpper() + "'";
231
232                //查询是否已存在名称为tableName的表
233                object obj = OracleDataBase.GetSingle(sqlStr0);
234                if (obj.ToString() == "1")
235                {
236                    throw new Exception(string.Format("名为{0}的表已存在,请换用其它表名!", tableName));
237                }
238                string sqlStr1 = "create table " + tableName + "(";
239                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
240                Workbook ObjWorkBook;
241                Worksheet ObjWorkSheet = null;
242                int rowcount = 0, colcount = 0;
243                colcount = arraList.Count;
244                ArrayList tranStr = new ArrayList();
245
246                //构造用于创建表的sql语句
247                for (int i = 0; i < colcount; i++)
248                {
249                    ArrayList arra = new ArrayList();
250                    arra = arraList[i];
251                    string name = arra[0].ToString();
252                    string ntype = arra[1].ToString();
253                    string nprimkey = arra[2].ToString();
254                    if (i != colcount - 1)
255                    {
256                        if (nprimkey == "否")
257                            sqlStr1 += name + " " + ntype + ",";
258                        else if (nprimkey == "是")
259                            sqlStr1 += name + " " + ntype + "primary key,";
260                    }
261                    else
262                    {
263                        if (nprimkey == "否")
264                            sqlStr1 += name + " " + ntype + ")";
265                        else if (nprimkey == "是")
266                            sqlStr1 += name + " " + ntype + "primary key)";
267
268                    }
269
270                }
271                //把sqlStr1添加到事务列表中
272                tranStr.Add(sqlStr1);
273
274
275                ObjWorkBook = ObjExcel.Workbooks.Open(sPathName, 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);
276                foreach (Worksheet sheet in ObjWorkBook.Sheets)
277                {
278                    if (sheet.Name.ToLower() == sheetName.ToLower())
279                    {
280                        ObjWorkSheet = sheet;
281                        break;
282                    }
283                }
284                if (ObjWorkSheet == null)
285                    throw new Exception(string.Format("{0} not found!!", sheetName));
286
287                rowcount = ObjWorkSheet.UsedRange.Rows.Count;
288                //设置插入记录的sql语句
289                for (int i = 2; i <= rowcount; i++)
290                {
291                    string sqlStr = "insert into " + tableName + " values(";
292                    for (int j = 1; j <= colcount; j++)
293                    {
294                        Range cell;
295                        string ntype = arraList[j - 1][1].ToString();
296                        if (j != colcount)
297                        {
298                            cell = (Range)ObjWorkSheet.Cells[i, j];
299                            if (ntype.ToLower().Contains("char"))
300
301
302                                sqlStr += "'" + cell.Value2.ToString() + "',";
303
304                            else
305                                if (ntype.ToLower() == "date")
306                                    sqlStr += "to_date('" + cell.Value2.ToString() + "','yyyy-mm-dd')" + ",";
307                                else
308                                    sqlStr += cell.Value2.ToString() + ",";
309
310
311                        }
312                        else
313                        {
314                            cell = (Range)ObjWorkSheet.Cells[i, j];
315                            if (ntype.ToLower().Contains("char"))
316                                sqlStr += "'" + cell.Value2.ToString() + "'";
317                            else
318                                if (ntype.ToLower() == "date")
319                                    sqlStr += "to_date('" + cell.Value2.ToString() + "','yyyy-mm-dd')";
320                                else
321                                    sqlStr += cell.Value2.ToString();
322                        }
323
324                    }
325                    sqlStr += ")";
326                    //将插入记录的语句添加到事务列表中
327                    tranStr.Add(sqlStr);
328
329                }
330                OracleDataBase.ExecuteSqlTran(tranStr);
331                ObjExcel.Workbooks.Close();
332                
333                return;
334
335            }
336            catch (Exception e)
337            {
338                throw new Exception(e.Message);
339                
340            }
341           
342                
343           
344           
345 
346        }
347
348
349
350    }
351
352        
353        
354        
355}

 

 

 

转载于:https://www.cnblogs.com/groundhog/archive/2008/10/06/1304453.html