打开 导入Excel文件 （异步）

引用 类库：

using NPOI.SS.UserModel;
using System.Threading;

 

 

object lockObject = new object();
int CurrentSheetIndex = 0;//当前选择的Sheet;
DateTime dateStart = DateTime.Now;//开始导入的时间
ISheet Currentsheet = null;//当前的Sheet;
int Step = 3000;//每个线程处理的行数
int MaxThreadQty = 6;//取大子线程数
int currentThreadQty = 0;//当前的子线程数
int ShowMessageCount = 50;//多少行才提示
int CurrentValue = 0;
DataTable dtPreview = null;//预览的数据
string resultByExcelToDB = "";//导入batch检查长度
Dictionary<string, int> Columns = new Dictionary<string, int>();//记录字段在当前表格中的顺序
Thread ttMain;
Thread tt;

 

 

 

 

        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOpenFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog open = new OpenFileDialog();

            open.Filter = "Excel File|*.xls;*.xlsx";
            if (open.ShowDialog() == DialogResult.OK)
            {
                this.txtPath.Text = open.FileName;
                string s = txtPath.Text;
                //this.txtTableName.Text = s.Substring(s.LastIndexOf('\\') + 1).Replace(".xls", "");

                try
                {
                    //尝试新的读方法
                    IWorkbook hssfworkbook;
                    using (FileStream file = new FileStream(open.FileName, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = WorkbookFactory.Create(file);
                        file.Dispose();
                    }
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    comSheet.Items.Clear();
                    for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                    {
                        comSheet.Items.Add(hssfworkbook.GetSheetName(i));
                    }


                    comSheet.SelectedIndex = 0;

                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }

            }


        }
        /// <summary>
        /// Sheet 选择切换事件，加载预览数据，这里只加载1千行，防止太多，加载太慢
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            CurrentSheetIndex = comSheet.SelectedIndex;
            backgroundWorker1.RunWorkerAsync();
            labMsg.Text = "加载数据中，请耐心等待";
        }
        /// <summary>
        /// 读取excel 到DataTable 中
        /// 
        /// </summary>
        /// <param name="strFilePath">excel文档路径 这里是绝对路径，如果不是绝对路径</param>
        /// <param name="StartRowIndex">开始的行下标(包括标题行)，起始值是0</param>
        /// <param name="SheetIndex">Shoee的下标，默认是0起始值是0</param>
        /// <param name="returnRowCount">返回指定行数的数据，主要是为了获取预览数据，0表示返回所有数据，否则就是指定的行数</param>
        /// <param name="FrisRowToColumn">第一行是否当作转换成数据表的列</param>
        /// <returns></returns>
        public DataTable Import(string strFilePath, int StartRowIndex, int SheetIndex = 0, int returnRowCount = 0, bool FrisRowToColumn = true)
        {
            Columns = new Dictionary<string, int>();//初始化
            DataTable dt = new DataTable();

            IWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = WorkbookFactory.Create(file);
            }
            Currentsheet = hssfworkbook.GetSheetAt(SheetIndex);
            //progressBar.Maximum = Currntsheet.LastRowNum;
            //labMsg.Text = Currntsheet.LastRowNum.ToString();
            if (Currentsheet.LastRowNum < Step)
            {
                ShowMessageCount = 50;
            }
            else if (Currentsheet.LastRowNum > Step && Currentsheet.LastRowNum < 10000)
            {
                ShowMessageCount = 100;
            }
            else
            {
                ShowMessageCount = 1000;
            }
            System.Collections.IEnumerator rows = Currentsheet.GetRowEnumerator();
            IRow headerRow = Currentsheet.GetRow(StartRowIndex);
            if (headerRow == null) return dt;//这里为什么读不到数据，还要再看一下
            int cellCount = headerRow.LastCellNum;
            if (FrisRowToColumn)//第一行转换成字段
            {
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null)
                    {
                        Columns[cell.ToString().Trim()] = j;
                        dt.Columns.Add(cell.ToString());
                    }
                    else
                    {
                        dt.Columns.Add("F" + j);
                    }
                }
                #region 验证表头是否正确
                try
                {
                    int ii = Columns["Material"];
                    // ii = Columns["Material Description"];
                    ii = Columns["Material Description"];
                    ii = Columns["Batch"];
                    ii = Columns["Storage Location"];
                    ii = Columns["Movement type"];
                    ii = Columns["Order"];
                    ii = Columns["Posting Date"];
                    ii = Columns["Purchase order"];
                    ii = Columns["Quantity"];
                    ii = Columns["Material Doc.Item"];
                    ii = Columns["Material Document"];
                }
                catch
                {
                    MessageBox.Show("请将表头设置成正确的值");
                }
                #endregion
                StartRowIndex++;
            }
            else
            {
                for (int j = 0; j < cellCount; j++)
                {
                    dt.Columns.Add("F" + j);
                }
            }
            int rowCount = 0;
            for (int i = StartRowIndex; i <= Currentsheet.LastRowNum; i++)
            {
                IRow row = Currentsheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
                //如果已经到达要求的行数，返回
                rowCount++;
                if (returnRowCount > 0 && rowCount >= returnRowCount)
                {
                    break;
                }
            }
            return dt;
        }

        /// <summary>
        /// 导入按钮 执行删除导入到数据库里
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btndeleteImport_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtPath.Text.Trim()))
            {
                
                try
                {
                    Step = Convert.ToInt32(txt_Step.Text.Trim());
                    MaxThreadQty = Convert.ToInt32(txt_MaxThredQty.Text.Trim());
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                    return;
                }
                //初始化各数据
                progressBar.Value = 0;
                labMsg.Text = "";
                dateStart = DateTime.Now;
                CurrentValue = 0;
                currentThreadQty = 0;
                btndeleteImport.Enabled = false;
                btnCompute.Enabled = false;
                btnOpenFile.Enabled = false;
                btndel.Enabled = false;
                comSheet.Enabled = false;
                 ttMain = new Thread(() =>
                {
                    int taskCount;
                    try
                    {
                        taskCount = Currentsheet.LastRowNum / Step;//整除运算
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("请耐心等待，数据加载完成再导入！");
                        return;
                    }
                    

                    for (int i = 0; i < taskCount + 1; i++)
                    {
                        int index = i;
                         tt = new Thread(() =>
                        {
                            DoAsyncImport(index * Step, index * Step + Step);

                        });

                        tt.IsBackground = true;
                        tt.Start();
                        currentThreadQty++;
                        Thread.Sleep(100);//防止线程扎堆
                        while (currentThreadQty >= MaxThreadQty)
                        {
                            Thread.Sleep(200);
                        }

                    }
                });

                ttMain.Start();
            }
            else
            {
                MessageBox.Show("请选择Excel表格！");
            }
        }



        /// <summary>
        /// 执行异步导入动作
        /// </summary>
        private void DoAsyncImport(int startIndex, int endIndex)
        {

            if (endIndex > Currentsheet.LastRowNum) endIndex = Currentsheet.LastRowNum + 1;
           

            for (int i = startIndex; i < endIndex; i++)
            {
                IRow row = Currentsheet.GetRow(i);
                if (i == 0 || row == null)//第一行是表头，不处理 
                {
                    continue;
                
                }
                DoInsertDB("WMTL_ITEM_BATCH", row);
                //lock (lockObject)
                //{
                    CurrentValue++;
                //}
                //减少显示信息的次数
                if (CurrentValue % ShowMessageCount == 0 || CurrentValue == Currentsheet.LastRowNum)
                {
                    if (this.progressBar.InvokeRequired)
                    {
                        this.progressBar.BeginInvoke(new SetMessageCallback(SetMesage), new object[] { CurrentValue, startIndex + "/" + endIndex });
                    }
                    else
                    {
                        SetMesage(CurrentValue, startIndex + "/" + endIndex);
                    }
                }
            }
            currentThreadQty--;


        }
        /// <summary>
        /// 处理单行数据，将数据插入到数据库中
        /// </summary>
        /// <param name="Table"></param>
        /// <param name="row"></param>
        private void DoInsertDB(string Table, IRow row)
        {
            string PostingDate = "";
            if (row.GetCell(Columns["Posting Date"]) != null)
            {
                try
                {
                    if (row.GetCell(Columns["Posting Date"]).CellType == CellType.STRING)
                {
                    PostingDate = DateTime.ParseExact(row.GetCell(Columns["Posting Date"]).StringCellValue, "dd.MM.yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    //  PostingDate = Convert.ToDateTime(row.GetCell(Columns["Posting Date"]).StringCellValue).ToString("yyyy-MM-dd");
                }
                else
                {
                        string strDate = DateTime.FromOADate(row.GetCell(Columns["Posting Date"]).NumericCellValue).ToString("d");
                        PostingDate = Convert.ToDateTime(strDate).ToString("yyyy-MM-dd");
                }
                }
                catch (Exception)
                {
                    MessageBox.Show(row.GetCell(Columns["Posting Date"]).NumericCellValue + "日期格式不合法");

                }
            }
            string MATERIAL = Convert.ToString(row.GetCell(Columns["Material"]));
            string MATERIAL_DESC = Convert.ToString(row.GetCell(Columns["Material Description"]));
            string BATCH = Convert.ToString(row.GetCell(Columns["Batch"]));
            //对batch  判断   如果开头为“00”，则为不合法数据 王万香2019年7月8日12:33:02
            string val = BATCH.Substring(0, 2);
            if (val == "00")
            {
                resultByExcelToDB += "第" + row.RowNum + "行数据"+ BATCH + "常度不合法!\n";
                return;
            }
            string STORAGE_LOCATION = Convert.ToString(row.GetCell(Columns["Storage Location"]));
            string MOVEMENT_TYPE = Convert.ToString(row.GetCell(Columns["Movement type"]));
            string ORDER_PO = Convert.ToString(row.GetCell(Columns["Order"]));
            string PURCHASE_ORDER = Convert.ToString(row.GetCell(Columns["Purchase order"]));
            string MATERIAL_DOC = Convert.ToString(row.GetCell(Columns["Material Document"]));
            string MATERIAL_DOC_ITEM = Convert.ToString(row.GetCell(Columns["Material Doc.Item"]));
            int QUANTITY = Convert.ToInt32( row.GetCell(Columns["Quantity"]).NumericCellValue);
            string POSTING_DATE = PostingDate;
            string USER_ID = FrmLogin.user_ID;
            string IMPORT_TIME = DateTime.Now.ToString("yyyy-MM-dd:HH:mm:ss");
            string sqlstr = " INSERT INTO " + Table + " SELECT '" + MATERIAL + "','" + MATERIAL_DESC + "','" + BATCH + "','" + STORAGE_LOCATION +
                            "','" + MOVEMENT_TYPE + "','" + ORDER_PO + "','" + PURCHASE_ORDER + "','" + MATERIAL_DOC + 
                             "','" + MATERIAL_DOC_ITEM + "'," + QUANTITY+",'"+ POSTING_DATE + "','" + USER_ID + "','" + IMPORT_TIME + "'";
            SqlServerHelper.ExeSql(sqlstr);
            //sqlstr += " WHERE NOT EXISTS(SELECT 'X' FROM " + Table + " T WHERE T.BATCH='" + BATCH + "')";
            //if (!SqlServerHelper.ExeSql(sqlstr))
            //{
            //    sqlstr = " UPDATE " + Table + "  SET MATERIAL='" + MATERIAL + "',MATERIAL_DESC='" + MATERIAL_DESC + "',BATCH='" + BATCH + "',MOVEMENT_TYPE='" + MOVEMENT_TYPE + "',ORDER_PO='" + ORDER_PO +
            //        "',PURCHASE_ORDER='" + PURCHASE_ORDER + "',MATERIAL_DOC='" + MATERIAL_DOC + "',POSTING_DATE='" + POSTING_DATE + "',QUANTITY=" + QUANTITY + ",USER_ID='" + USER_ID + "',IMPORT_TIME='" + IMPORT_TIME +
            //        "',STORAGE_LOCATION='"+ STORAGE_LOCATION + "',MATERIAL_DOC_ITEM='"+ MATERIAL_DOC_ITEM + "' WHERE BATCH='" + BATCH + "'";
            //}
        }

        /// <summary>
        /// 执行插入数据库的的动作,这个方法不使用了
        /// </summary>
        //   private void DoInsertDB(IRow row)
        //   {


        //       string sqlstr = @"INSERT INTO WMTL_ITEM_BATCH(
        //           MATERIAL,MATERIAL_DESC,BATCH,MOVEMENT_TYPE,ORDER_PO,PURCHASE_ORDER,
        //           COST_CENTER,MATERIAL_DOC,POSTING_DATE,DOCUMENT_DATE) 
        //VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
        //       //for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
        //       //{

        //       //}
        //       int ii = Columns["Material"];
        //       ii = Columns["Material Description"];
        //       ii = Columns["Batch"];
        //       ii = Columns["Movement type"];
        //       ii = Columns["Order"];
        //       ii = Columns["Purchase order"];
        //       //ii = Columns["Cost Center"];
        //       ii = Columns["Material Document"];

        //       //将两个时间的格式转换
        //       string PostingDate = "";
        //       if (row.GetCell(Columns["Posting Date"]) != null)
        //       {
        //           if (row.GetCell(Columns["Posting Date"]).CellType == CellType.STRING)
        //           {
        //               PostingDate = DateTime.ParseExact(row.GetCell(Columns["Posting Date"]).StringCellValue, "dd.MM.yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
        //               //  PostingDate = Convert.ToDateTime(row.GetCell(Columns["Posting Date"]).StringCellValue).ToString("yyyy-MM-dd");
        //           }
        //           else
        //           {
        //               string strDate = DateTime.FromOADate(row.GetCell(Columns["Posting Date"]).NumericCellValue).ToString("d");
        //               PostingDate = Convert.ToDateTime(strDate).ToString("yyyy-MM-dd");
        //           }
        //       }
        //       string DocumentDate = "";
        //       //if (row.GetCell(Columns["Document Date"]) != null)
        //       //{
        //       //    string strDate = DateTime.FromOADate(row.GetCell(Columns["Document Date"]).NumericCellValue).ToString("d");
        //       //    DocumentDate = Convert.ToDateTime(strDate).ToString("yyyy-MM-dd");
        //       //}


        //       sqlstr = string.Format(sqlstr, row.GetCell(Columns["Material"]), row.GetCell(Columns["Material Description"]), row.GetCell(Columns["Batch"]),
        //           row.GetCell(Columns["Movement type"]), row.GetCell(Columns["Order"]),
        //           row.GetCell(Columns["Purchase order"]), "",
        //           row.GetCell(Columns["Material Document"]), PostingDate, DocumentDate);
        //       SqlServerHelper.ExeSql(sqlstr);
        //   }
        delegate void SetMessageCallback(int value, string message);

        private void SetMesage(int value, string message)
        {

            this.progressBar.Value = CurrentValue;
            TimeSpan ts = DateTime.Now.Subtract(dateStart);
            this.labMsg.Text = CurrentValue + "/" + Currentsheet.LastRowNum + " 用时：" + ts.Minutes + "分" + ts.Seconds + "秒";
            /// textBox1.Text = this.labMsg.Text + "\r\n" + textBox1.Text;
            if (CurrentValue == Currentsheet.LastRowNum)
            {
                btndeleteImport.Enabled = true;
                btnCompute.Enabled = true;
                btnOpenFile.Enabled = true;
                btndel.Enabled = true;
                comSheet.Enabled = true;

                MessageBox.Show("导入完成");
                if (resultByExcelToDB!=null)
                {
                    MessageBox.Show(resultByExcelToDB,"提示");
                }
               
            }

        }