Winform应用程序使用NPOI实现Excel导入数据库

        /// <summary>
        /// 导入基础数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImport_Click(object sender, EventArgs e)
        {
            //打开一个文件选择框
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
            ofd.Filter = "Excel文件| *.xlsx;*.xls";
            ofd.ValidateNames = true;     //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
            ofd.CheckFileExists = true;  //验证路径有效性
            ofd.CheckPathExists = true; //验证文件有效性
            string strName = string.Empty;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                strName = ofd.FileName;
            }
            if (strName == "")
            {
                MessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            DataTable dataTable = GetExcel(strName);
            //循环读取dataTable，插入数据
            foreach (DataRow dr in dataTable.Rows)
            {
                //查询数据，获取字段lid值
                string cwoc = dr["工作中心"].ToString().Trim();
                string description = dr["线段名称"].ToString().Trim();
                DataTable dt = disService.GetLineSectionNew(cwoc, description);
                string lid = "";
                if (dt.Rows.Count > 0)
                {
                    lid = dt.Rows[0]["LID"].ToString();
                }
                string partcode1 = dr["物料编码"].ToString().Trim();
                string limitQuan1 = dr["定容量"].ToString().Trim();
                string safeQuan1 = dr["安全库存"].ToString().Trim();
                string hsafeQuan1 = dr["最高库存"].ToString().Trim();
                string isdelivery = dr["是否送货拉动"].ToString().Trim();
                string isdispatch = dr["是否配送拉动"].ToString().Trim();
                int Lid1 = 0;
                decimal LimitQuan1 = 0;
                decimal SafeQuan1 = 0;
                decimal HSafeQuan1 = 0;
                try
                {
                    Lid1 = int.Parse(lid);
                    LimitQuan1 = decimal.Parse(limitQuan1);
                    SafeQuan1 = decimal.Parse(safeQuan1);
                    HSafeQuan1 = decimal.Parse(hsafeQuan1);
                }
                catch (Exception)
                {
                    MessageBox.Show("编号或数量格式不正确!");
                    return;
                }
                int ret = disService.InsertLineSecPartNew(Lid1, WinFrmDispatching.Framework.UserState.Instance.LoginName, LimitQuan1, SafeQuan1, partcode1, HSafeQuan1, isdelivery, isdispatch);
                if (ret > 0)
                {
                    GetData();
                    MessageBox.Show("导入成功");
                }
                else
                {
                    MessageBox.Show("导入失败");
                }
            }
        }
        /// <summary>
        /// 获取Excel到Datatable中
        /// </summary>
        /// <param name="filePath">Excel路径</param>
        /// <returns>返回DataTable数据</returns>
        public static DataTable GetExcel(string filePath)
        {
            IWorkbook iwkX;
            using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                iwkX = WorkbookFactory.Create(fs);
                fs.Close();
            }
            //sheet
            DataTable dt = new DataTable();
            for (int h = 0; h < iwkX.NumberOfSheets; h++)
            {
                ISheet sheet = iwkX.GetSheetAt(h);
                var rows = sheet.GetRowEnumerator();
                bool isMove = rows.MoveNext();
                //循环sheet
                if (isMove)
                {
                    var Cols = (IRow)rows.Current;
                    dt.TableName = sheet.SheetName;
                    for (int i = 0; i < Cols.LastCellNum; i++)
                    {
                        string str = Cols.GetCell(i).ToString();
                        dt.Columns.Add(Cols.GetCell(i).ToString());
                    }
                    while (rows.MoveNext())
                    {
                        var row = (IRow)rows.Current;
                        var dr = dt.NewRow();
                        for (int i = 0; i < row.LastCellNum; i++)
                        {
                            var cell = row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = "";
                            }
                            else
                            {
                                string strdr = cell.ToString();
                                dr[i] = cell.ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }