EXCEL 导入ORACLE

HCM系统HCMBOMImport模块示例

1、创建一个导入界面

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ComponentFactory.Krypton.Toolkit;
using Mes.Core.Service;

namespace HCMBOMImport
{
    public partial class BOMImpt : Mes.Core.BaseForm
    {
        public BOMImpt()
        {
            InitializeComponent();
        }
        private bool blchecksucessflag;


        private void btn_import_Click(object sender, EventArgs e)
        {
            if (!blchecksucessflag)
                blchecksucessflag = true;
            OpenFileDialog of = new OpenFileDialog();
            of.Multiselect = false;
            of.Filter = "Excel 2003|*.xls|Excel 2007|*.xlsx";
            if (of.ShowDialog(this).Equals(DialogResult.OK))
            {
                BOMImport.ExtName = System.IO.Path.GetExtension(of.FileName).Remove(0, 1);
                if (!BOMImport.ExtName.Contains("XLS"))
                {
                    MessageBox.Show("请选择EXCEL文件");
                }
                else
                {
                    BOMImport.FilePath = of.FileName;
                    if (!DgvControl.FillDataComm(dataGridViewEx1, BOMImport.GetDT()))
                    {
                        System.Windows.Forms.MessageBox.Show("当前导入的EXCEL中存在空行或格式不对，请先清空内容与格式！");
                    }
                }
            }
        }

        private void dataGridViewEx1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void btn_check_Click(object sender, EventArgs e)
        {
            if (!DgvControl.ImpToDB(dataGridViewEx1))
            {
                MessageBox.Show("校验未通过不能导入！");
            }
               
        }
    }
}

 

2、创建一个得到EXCEL数据的类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using Mes.Core.Service;

namespace HCMBOMImport
{
    public class BOMImport
    {
        private static string strextname = string.Empty;    //文件扩展名
        private static string strfilepath = string.Empty;   //文件路径
        private static string strconnect = string.Empty;    //连接字符串
        //扩展名
        public static string ExtName
        {
            get { return strextname; }
            set { strextname = value.ToUpper();}
        }
        public static string FilePath
        {
            get { return strfilepath; }
            set { strfilepath = value; }
        }
        public static DataTable GetDT()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            //确保文件未被占用
            System.IO.FileStream fs = new System.IO.FileStream(strfilepath, System.IO.FileMode.Open);
            fs.Close();

            SelectConnStr();
            OleDbConnection craboDbConnection = new OleDbConnection(strconnect);
            craboDbConnection.Open();
            DataTable execlTables = craboDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            craboDbConnection.Close();
            OleDbDataAdapter oleda = new OleDbDataAdapter("SELECT * FROM [" + execlTables.Rows[0]["TABLE_NAME"].ToString() + "]", craboDbConnection);
            oleda.Fill(ds);
            dt = ds.Tables[0];
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {
            //        try
            //        {
            //            dt.Rows[i][j] = Convert.ToString(dt.Rows[i][j]).Trim();
            //        }
            //        catch (Exception)
            //        {
            //            dt.Rows[i][j] = dt.Rows[i][j];
            //        }
            //    }
            //}
            return dt;
        }

        public static void SelectConnStr()
        {
            if (!string.IsNullOrEmpty(strfilepath))
            {
                switch (strextname)
                {
                    case "XLS":
                        strconnect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strfilepath + ";Extended Properties='Excel 8.0;IMEX=1'";
                        break;
                    case "XLSX":
                        strconnect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strfilepath + ";Extended Properties='Excel 12.0;IMEX=1'";
                        break;
                    default:
                        break;
                }
            }
        }

        public static string GetBatchID()
        {
            return (string)DatabaseAccessService.execute("select hcm_demand_temp_s.nextval from dual", ReturnType.STRING);
        }
        /// <summary>
        /// 取出EXCEL第一页的数据,增加了Trim()
        /// </summary>
        /// <returns></returns>
     
    }
}

 

3、创建一个类，将得到的数据填充在dataGridView中，检验并导入数据库

using System;
using System.Collections.Generic;
using System.Text;
using Mes.Core.Service;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Drawing;


namespace HCMBOMImport
{
    class DgvControl
    {

        private static int ck_i;
        public static bool FillDataComm(ComponentFactory.Krypton.Toolkit.KryptonDataGridView dgv_excelata, DataTable dt)
        {

            if (dgv_excelata.Rows.Count > 0)
                dgv_excelata.Rows.Clear();

            int rownum = dt.Rows.Count;
            ck_i = 0;
            try
            {
                #region 具体处理
                DataGridViewRow row;
                for (int i = 1; i < rownum; i++)
                {
                    row = new DataGridViewRow();
                    row.CreateCells(dgv_excelata);
                    for (int j = 0; j < 3; j++)
                    {
                        row.Cells[j].Value = dt.Rows[i][j].ToString().Trim();
                    }
                    //验证物料和库存组织是否存在
                   
                    if (CheckRowVal(row))
                    {
                        ck_i = 1;
                        row.Cells[1].Style.BackColor = Color.Red;
                    }
                   
                    dgv_excelata.Rows.Add(row);
                   
                }
                #endregion
            }
            catch (Exception WhenProcess)
            {
                //不处理异常，将异常交给外部代码处理
                throw WhenProcess;
            }


            return CheckNullRow(dgv_excelata, rownum - 3, 3);
        }
        private static bool CheckRowVal(DataGridViewRow row)
        {
            string Org = "";
            string ItemCode = "";
            Org = row.Cells[0].Value.ToString();
            ItemCode = row.Cells[1].Value.ToString();
 
            string sql = "{?=call cux_hcm_pro_mtl_convert_pkg.item_no_check('" + Org + "','" + ItemCode + "')}";
            string ret = (string)Mes.Core.Service.DatabaseAccessService.execute(sql, Mes.Core.Service.ReturnType.STRING);

            if (ret == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
            
        }

        public static bool ImpToDB(ComponentFactory.Krypton.Toolkit.KryptonDataGridView dgv_excelata)
        {
            if (ck_i == 1)
            {
                return false;
            }
            else
            {
                string sql = "{?=call cux_hcm_pro_mtl_convert_pkg.r_index}";
                string  ind_i = (string)Mes.Core.Service.DatabaseAccessService.execute(sql, Mes.Core.Service.ReturnType.STRING);

                int rownum = dgv_excelata.RowCount;
            
                for (int i = 0; i < rownum; i++)
                {
                  
                    string itemno = dgv_excelata.Rows[i].Cells[1].Value.ToString();
                    string org = dgv_excelata.Rows[i].Cells[0].Value.ToString();
                    string qty = dgv_excelata.Rows[i].Cells[2].Value.ToString();
             
                    string sql1 = "{?=call cux_hcm_pro_mtl_convert_pkg.imp_for_ui(" + ind_i + ",'" + org + "','" + itemno + "'," + qty + " )}";
                    string r_msg = (string)Mes.Core.Service.DatabaseAccessService.execute(sql1, Mes.Core.Service.ReturnType.STRING);
                    if (r_msg.Length > 1)
                    {
                        MessageBox.Show("第" + i.ToString() + "有误:" + r_msg);
                        break;
                    }
                }

               
                
                MessageBox.Show("批次号为:" + ind_i.ToString() + ",请注意记录");
                return true;
            }
        }


        private static bool CheckNullRow(ComponentFactory.Krypton.Toolkit.KryptonDataGridView dgv_excel, int rownum, int colnum)
        {
            int n = 0;
            int dgvrownum = dgv_excel.Rows.Count - 1;
            if (rownum < 0)
                return true;
            for (int i = 0; i < colnum; i++)
            {
                if (dgv_excel.Rows[dgvrownum].Cells[i].Value == null || string.IsNullOrEmpty(dgv_excel.Rows[dgvrownum].Cells[i].Value.ToString()))
                    n++;
            }
            if (n == colnum)
            {
                return false;
            }
            else
            {
                return true;
            }

        }
    }
}