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;

 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;




    /**//// <summary>
    /// Summary description for OracleDataBase
    /// </summary>
    public class OracleDataBase
    {

        public   static string connectionString = "Data Source=yourSour;User ID=yourID;Password=yourPassword;Unicode=True";
        public OracleDataBase()
        {
            //
            // TODO: Add constructor logic here
            //
        }

                      
        /**//// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
}

 

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

  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;

                ObjExcel.Workbooks.Close();
                return colCount;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }



        }
        /**//// <summary>
        /// 获得行数
        /// </summary>
        /// <param name="sPathName"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int GetRowsCount(string sPathName, string sheetName)
        {
            try
            {
                string strTempDir = System.IO.Path.GetDirectoryName(sPathName);
                int rowCount = 0;

                //获取excel对象
                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
                Workbook ObjWorkBook;
                Worksheet ObjWorkSheet = null;
                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);
                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));

                rowCount = ObjWorkSheet.Rows.Count;
                ObjExcel.Workbooks.Close();

                return rowCount;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }



        }
        /**//// <summary>
        /// 获得所有的工作表名
        /// </summary>
        /// <param name="sPathName"></param>
        /// <returns></returns>
        public ArrayList GetAllSheetNames(string sPathName)
        {
            try
            {

                ArrayList arra = new ArrayList();
                string strTempDir = System.IO.Path.GetDirectoryName(sPathName);
                string fName = System.IO.Path.GetFileName(sPathName);


                //获取excel对象
                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
                Workbook ObjWorkBook;

                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);
                foreach (Worksheet sheet in ObjWorkBook.Sheets)
                {
                    arra.Add(sheet.Name);


                }
                ObjExcel.Workbooks.Close();
                return arra;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            
        }

        /**//// <summary>
        /// 从ListView中获得字段名称、字段类型、是否主键
        /// </summary>
        /// <param name="myListView"></param>
        /// <returns></returns>
        public List<ArrayList> getValuesFromListView(myEditListView myListView)
        {
            try
            {

                List<ArrayList> tempContainer = new List<ArrayList>();

                int colCount = myListView.Columns.Count;
                foreach (ListViewItem lvitem in myListView.Items)
                {
                    ArrayList tempArray = new ArrayList();
                    for (int i = 0; i < colCount; i++)

                        tempArray.Add(lvitem.SubItems[i].Text);
                    tempContainer.Add(tempArray);
                }
                return tempContainer;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /**//// <summary>
        /// excel导入oracle
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="tableName"></param>
        /// <param name="arraList"></param>
        
        public void excelToBase(string sPathName,string sheetName, string tableName, List<ArrayList> arraList)
        {
            try
            {
                string sqlStr0 = "select count(*) from all_tables where table_name='" + tableName.ToUpper() + "'";

                //查询是否已存在名称为tableName的表
                object obj = OracleDataBase.GetSingle(sqlStr0);
                if (obj.ToString() == "1")
                {
                    throw new Exception(string.Format("名为{0}的表已存在,请换用其它表名!", tableName));
                }
                string sqlStr1 = "create table " + tableName + "(";
                Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
                Workbook ObjWorkBook;
                Worksheet ObjWorkSheet = null;
                int rowcount = 0, colcount = 0;
                colcount = arraList.Count;
                ArrayList tranStr = new ArrayList();

                //构造用于创建表的sql语句
                for (int i = 0; i < colcount; i++)
                {
                    ArrayList arra = new ArrayList();
                    arra = arraList[i];
                    string name = arra[0].ToString();
                    string ntype = arra[1].ToString();
                    string nprimkey = arra[2].ToString();
                    if (i != colcount - 1)
                    {
                        if (nprimkey == "否")
                            sqlStr1 += name + " " + ntype + ",";
                        else if (nprimkey == "是")
                            sqlStr1 += name + " " + ntype + "primary key,";
                    }
                    else
                    {
                        if (nprimkey == "否")
                            sqlStr1 += name + " " + ntype + ")";
                        else if (nprimkey == "是")
                            sqlStr1 += name + " " + ntype + "primary key)";

                    }

                }
                //把sqlStr1添加到事务列表中
                tranStr.Add(sqlStr1);


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

                rowcount = ObjWorkSheet.UsedRange.Rows.Count;
                //设置插入记录的sql语句
                for (int i = 2; i <= rowcount; i++)
                {
                    string sqlStr = "insert into " + tableName + " values(";
                    for (int j = 1; j <= colcount; j++)
                    {
                        Range cell;
                        string ntype = arraList[j - 1][1].ToString();
                        if (j != colcount)
                        {
                            cell = (Range)ObjWorkSheet.Cells[i, j];
                            if (ntype.ToLower().Contains("char"))


                                sqlStr += "'" + cell.Value2.ToString() + "',";

                            else
                                if (ntype.ToLower() == "date")
                                    sqlStr += "to_date('" + cell.Value2.ToString() + "','yyyy-mm-dd')" + ",";
                                else
                                    sqlStr += cell.Value2.ToString() + ",";


                        }
                        else
                        {
                            cell = (Range)ObjWorkSheet.Cells[i, j];
                            if (ntype.ToLower().Contains("char"))
                                sqlStr += "'" + cell.Value2.ToString() + "'";
                            else
                                if (ntype.ToLower() == "date")
                                    sqlStr += "to_date('" + cell.Value2.ToString() + "','yyyy-mm-dd')";
                                else
                                    sqlStr += cell.Value2.ToString();
                        }

                    }
                    sqlStr += ")";
                    //将插入记录的语句添加到事务列表中
                    tranStr.Add(sqlStr);

                }
                OracleDataBase.ExecuteSqlTran(tranStr);
                ObjExcel.Workbooks.Close();
                
                return;

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
                
            }
           
                
           
           
 
        }



    }

        
        
        
}