引用 类库：

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;

 

 

 

 

/// 
        /// 选择文件        /// 
        ///         ///         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);                }            }        }        /// 
        /// Sheet 选择切换事件，加载预览数据，这里只加载1千行，防止太多，加载太慢        /// 
        ///         ///         private void comSheet_SelectedIndexChanged(object sender, EventArgs e)        {            CurrentSheetIndex = comSheet.SelectedIndex;            backgroundWorker1.RunWorkerAsync();            labMsg.Text = "加载数据中，请耐心等待";        }        /// 
        /// 读取excel 到DataTable 中        ///         /// 
        /// excel文档路径 这里是绝对路径，如果不是绝对路径        /// 开始的行下标(包括标题行)，起始值是0        /// Shoee的下标，默认是0起始值是0        /// 返回指定行数的数据，主要是为了获取预览数据，0表示返回所有数据，否则就是指定的行数        /// 第一行是否当作转换成数据表的列        /// 
             public DataTable Import(string strFilePath, int StartRowIndex, int SheetIndex = 0, int returnRowCount = 0, bool FrisRowToColumn = true)        {            Columns = new Dictionary
     
      ();//初始化            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;        }        /// 
      
        /// 导入按钮 执行删除导入到数据库里        /// 
        /// 
              /// 
              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表格！");            }        }        /// 
      
        /// 执行异步导入动作        /// 
        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--;        }        /// 
      
        /// 处理单行数据，将数据插入到数据库中        /// 
        /// 
              /// 
              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 + "'";            //}        }        /// 
      
        /// 执行插入数据库的的动作,这个方法不使用了        /// 
        //   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,"提示");                }                           }        }