importtobase.cs

excel和文本文件上传的oracle数据库,采用exl11com接口

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using Microsoft.Office.Interop.Excel;

namespace datamanager2
{

    public partial class ImportToBase : Form
    {

        private datamanager2.myColumnHeader header1 = new datamanager2.myColumnHeader(datamanager2.myListViewColumnStyle.ReadOnly);
        private datamanager2.myColumnHeader header2 = new datamanager2.myColumnHeader(datamanager2.myListViewColumnStyle.EditBox);
        private datamanager2.myColumnHeader header3 = new datamanager2.myColumnHeader(datamanager2.myListViewColumnStyle.ComboBox);
        private string sPathName;
        
        public ImportToBase()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
            this.Dispose();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "Excel工作表(*.xls)|*.xls|文本文件 (*txt)|*.txt|所有文件 (*.*)|*.*";
            openFileDialog1.FilterIndex = 1;
            
            
            
            if (openFileDialog1.ShowDialog() == DialogResult.OK)//需判断
            {
                sPathName = openFileDialog1.FileName.ToString();
                txtPathName.Text = sPathName;
                txtFileName.Text = System.IO.Path.GetFileName(sPathName);
                ImpFile2Dase ie = new ImpFile2Dase();
                ArrayList arra = new ArrayList();
                arra = ie.GetAllSheetNames(sPathName);
                foreach (string str in arra)
                {
                    cbWorksheetNames.Items.Add(str);
                }
                if (arra.Count == 0)
                    MessageBox.Show("Excel文件无工作表", "提示");
                labelWS.Visible = true;
                cbWorksheetNames.Visible = true;

                txtConnStr.Text = "Data Source=crop;User ID=rs;Password=rscrop;Unicode=True";
                txtConnStr.ReadOnly = true;
                txtTableName.Text = System.IO.Path.GetFileNameWithoutExtension(sPathName);
                btSet.Enabled = true;
            }

            
           
            
            

        }

       
        
       
       
        /// <summary>
        /// 初始加载项
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ImportToBase_Load(object sender, EventArgs e)
        {
            
            btImport.Enabled = false;
            btSet.Enabled = false;
            labelWS.Visible = false;
            cbWorksheetNames.Visible = false;
            this.SuspendLayout();
            // 
            // header1
            // 
            this.header1 .ColumnStyle  = datamanager2.myListViewColumnStyle.ReadOnly;
            this.header1.Text = "字段名称";
            this.header1.Width = 100;
            // 
            // header2
            // 
            this.header2.ColumnStyle = datamanager2.myListViewColumnStyle.EditBox ;
            this.header2.Text = "字段类型";
            this.header2.Width = 100;
            // 
            // header3
            // 
            this.header3.ColumnStyle = datamanager2.myListViewColumnStyle.ComboBox ;
            this.header3.Text = "是否主键";
            this.header3.Width = 110;

            this.editListView.Columns.AddRange(new datamanager2.myColumnHeader [] {
            this.header1,
            this.header2,
            this.header3});
            
            this.editListView .BoundListToColumn(2, new string[] { "是", "否"});
            this.editListView.ComboBoxFont = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
            this.editListView.EditFont = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
            this.editListView.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
            this.editListView.FullRowSelect = true;//必须
            this.editListView.View = View.Details ;
            this.ResumeLayout(false);

            
         }       
            
         

         
                                 
       /// <summary>
       /// 提取并设置字段
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
        private void btSet_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult mydr = new DialogResult();
                if (cbWorksheetNames.Text == "")
                    mydr = MessageBox.Show("请先选择工作表名称", "提示信息", MessageBoxButtons.OK);
                if (mydr == DialogResult.OK)
                {
                    return;

                }
                string sPathName;
                string sWorksheetName;
                List<string> colNames;
                ImpFile2Dase ie = new ImpFile2Dase();
                sPathName = txtPathName.Text.ToString();
                sWorksheetName = cbWorksheetNames.Text.ToString();
              

                int colCount = ie.GetColCount(sPathName, sWorksheetName);
                colNames = ie.GetColumnNames(sPathName, sWorksheetName);
                foreach (string str in colNames)
                {
                    ListViewItem lvitem = new ListViewItem();
                    lvitem.SubItems.Clear();
                    lvitem.SubItems[0].Text = str;
                    lvitem.SubItems.Add("此处添加类型");
                    lvitem.SubItems.Add("否");
                    this.editListView.Items.Add(lvitem);
                }
                btImport.Enabled = true;

                return;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "请检查Excel文件是否规范！");
            }
           
            
          
        }
        /// <summary>
        /// 执行excel导入数据库操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                string sPathName = txtPathName.Text.ToString();
                string tabName = txtTableName.Text.ToString();
                string sheetName = cbWorksheetNames.Text.ToString();
                ImpFile2Dase ie = new ImpFile2Dase();

                List<ArrayList> arraList = new List<ArrayList>();
                arraList = ie.getValuesFromListView(this.editListView);

                ie.excelToBase(sPathName, sheetName, tabName, arraList);
                DialogResult myresult = new DialogResult();
                myresult = MessageBox.Show("导入成功", "提示信息", MessageBoxButtons.OK);
                if (myresult == DialogResult.OK)
                {
                    this.Close();
                    this.Dispose();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString()+"请核对您的Excel文件是否规范!");

            }
            finally
            {
                
            }

          
           

        }
       
        


    }
}