同一个worksheet根据数据特征导入到两个表

        private void button1_Click(object sender, EventArgs e)
        {
            string excelFileName = @"C:\xxx.xlsx";
            Excel.Application app = new Excel.Application();
            app.Visible = true; app.DisplayAlerts = false;
            Excel.Workbook wb = app.Workbooks.Open(Filename: excelFileName, ReadOnly: true);
            Excel.Worksheet sht = wb.Worksheets[1];

            DataTable dtSource = ToDataTable(sht.UsedRange.Value2);
            dtSource.TableName = "sht";
            //MyCommon.WriteToFile(MyCommon.ConvertDataTableToXml(dt), "sht",0, "d:\\log");
            app.Quit();

            
            string path = @"C:\xxx";
            //DataTable dtSource = MyCommon.ConvertXmlToDataTable(MyCommon.ReadXmlStringFromFile("sht.xml", "", "d:\\log"));


            
            string tableName = "ZDMX";
            int importedCount = ImportData(dtSource, tableName, path);
            MessageBox.Show(
                    string.Format("表{0}导入{1}条记录。", tableName, importedCount)
                  , "导入结果");

            tableName = "TCMX";
            importedCount = ImportData(dtSource, tableName, path);
            MessageBox.Show(
                    string.Format("表{0}导入{1}条记录。", tableName, importedCount)
                  , "导入结果");

        }

        /// <summary>
        /// 将提纯过的内存表转换成excel的数组导入到数据库表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="configPath"></param>
        /// <returns></returns>
        public static int ImportData(DataTable dtSource, string tableName, string configPath, int offsetColumn = 1)
        {
            CpmVars myVar = new CpmVars(tableName, configPath);
            MyCommon.ExecuteSql(string.Format("delete from {0} where 1=1",tableName),myVar.Sqc.ConnectString);
            
            DataTable dt = dtSource.Copy();

            if (myVar.BdCfg.OnlyColumn > 0 && !string.IsNullOrEmpty(myVar.BdCfg.OnlyColumnReg))
            {
                Regex reg = new Regex(myVar.BdCfg.OnlyColumnReg, RegexOptions.IgnoreCase);
                int columnNo = myVar.BdCfg.OnlyColumn - offsetColumn;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    if (!reg.IsMatch(dt.Rows[i][columnNo].ToString()))
                        dt.Rows.RemoveAt(i);
                }
            }


            myVar.BdCfg.StartLine = 0; myVar.BdCfg.KeyColumn = 0; myVar.BdCfg.OnlyColumn = 0;

            BigData bd = new BigData(); bd.ConfigByVar(myVar); bd.InitDataTable();
            bd.ArrData = ToArray(dt);

            BigDataToSqlServer b2s = new BigDataToSqlServer();
            b2s.ConnectionString = myVar.Sqc.ConnectString;
            b2s.TableName = tableName;
            int importedCount = bd.WriteDataTableFromArray();
            b2s.DtData = bd.DtData; b2s.CopyToServer();

            return importedCount;
        }



        /// <summary>
        /// EXCEL二维数组转DataTable
        /// </summary>
        /// <param name="arr"></param>
        /// <returns></returns>
        public static DataTable ToDataTable(object[,] arr)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < arr.GetLength(1); i++)
                dt.Columns.Add(i.ToString(), typeof(object));

            for (int i = 0; i < arr.GetLength(0); i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < arr.GetLength(1); j++)
                {
                    dr[j.ToString()] = arr[i + 1, j + 1];
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// DataTable转换成二维数组（excel从1开始）
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static object[,] ToArray(DataTable dt)
        {
            Array arr = Array.CreateInstance(typeof(object), new Int32[] { dt.Rows.Count, dt.Columns.Count }, new Int32[] { 1, 1 });
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                    arr.SetValue(dt.Rows[i][j], i + 1, j + 1);
            }
            return (object[,])arr;
        }