.net excel利用NPOI匯入oracle

標籤：

1.連結資料庫

  引用System.Data.OracleClient；

  //資料庫連結字串   Data Source如：192.168.5.153:1521/orcl

  string linkStr = "User ID=" + name + "; Password=" + password + "; Data Source=" + oraLink;

  OracleConnection oraCon = new OracleConnection(linkStr);

  oraCon.Open();

2.利用NPOI讀取excel資料  

public class ExcelHelper:IDisposable    {        private string fileName = null; //檔案名稱        private IWorkbook workbook = null;        private FileStream fs = null;        private bool disposed;        public ExcelHelper(string fileName)        {            this.fileName = fileName;            disposed = false;        }        /// <summary>        /// 將Excel匯入資料庫        /// </summary>        /// <param name="sheetName"></param>        /// <param name="isFirstRowColumn"></param>        /// <returns></returns>        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)        {            ISheet sheet = null;            DataTable data = new DataTable();            int startRow = 0;            try            {                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);                if (fileName.IndexOf(".xlsx") > 0) // 2007版本                    workbook = new HSSFWorkbook(fs);                else if (fileName.IndexOf(".xls") > 0) // 2003版本                    workbook = new HSSFWorkbook(fs);                if (sheetName != null)                {                    sheet = workbook.GetSheet(sheetName);                    if (sheet == null) //如果沒有找到指定的sheetName對應的sheet，則嘗試擷取第一個sheet                    {                        sheet = workbook.GetSheetAt(0);                    }                }                else                {                    sheet = workbook.GetSheetAt(0);                }                if (sheet != null)                {                    IRow firstRow = sheet.GetRow(0);                    int cellCount = firstRow.LastCellNum; //一行最後一個cell的編號 即總的列數                    if (isFirstRowColumn)                    {                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                        {                            ICell cell = firstRow.GetCell(i);                            if (cell != null)                            {                                string cellValue = cell.StringCellValue;                                if (cellValue != null)                                {                                    DataColumn column = new DataColumn(cellValue);                                    data.Columns.Add(column);                                }                            }                        }                        startRow = sheet.FirstRowNum + 1;                    }                    else                    {                        startRow = sheet.FirstRowNum;                    }                    //最後一列的標號                    int rowCount = sheet.LastRowNum;                    for (int i = startRow; i <= rowCount; ++i)                    {                        IRow row = sheet.GetRow(i);                        if (row == null) continue; //沒有資料的行預設是null　　　　　　　                        DataRow dataRow = data.NewRow();                        for (int j = row.FirstCellNum; j < cellCount; ++j)                        {                            if (row.GetCell(j) != null) //同理，沒有資料的儲存格都預設是null                                dataRow[j] = row.GetCell(j).ToString();                        }                        data.Rows.Add(dataRow);                    }                }                return data;            }            catch (Exception ex)            {                Console.WriteLine("Exception: " + ex.Message);                return null;            }        }        public void Dispose()        {            Dispose(true);            GC.SuppressFinalize(this);        }        protected virtual void Dispose(bool disposing)        {            if (!this.disposed)            {                if (disposing)                {                    if (fs != null)                        fs.Close();                }                fs = null;                disposed = true;            }        }

excelHelper類

3.插入資料庫

 --利用openFileDialog讀取檔案名稱

 --代碼如下：

  openFileDialog1.Filter = "(*.xls)|*.xls";
  openFileDialog1.ShowDialog();
  this.textBox1.Text = openFileDialog1.FileName;

 --將資料插入oracle資料庫

  using(ExcelHelper excel = new ExcelHelper(this.textBox1.Text))

 {

      DataTable dbdata = excel.ExcelToDataTable(null, true); //調用excelHelper中的函數

      for (int i = 0; i < dbdata.Rows.Count; i++)  //解析資料   

     {

         string XMMC = dbdata.Rows[i][0].ToString();

         string sql ="insert into XXX(xmmc) values (‘"+XMMC +"‘)";       

         OracleCommand cmd = new OracleCommand(sql, oraCon);
         OracleDataReader reader = cmd.ExecuteNonQuery();//增刪改

         /*  判斷資料是否存在

         OracleCommand cmd = new OracleCommand(sql1, oraCon);
         OracleDataReader reader = cmd.ExecuteReader();

         if(reader.Read()){}

       */

     }

 }

.net excel利用NPOI匯入oracle