多表工作簿导入数据库表（按日期存储表）

        private void ImportHcData()
        {
            string xllPath = @"E:\CH\GENERALTOOLS";
            string configTableName = "HCYSJ";
            CpmVars myVar = new CpmVars(configTableName, xllPath);


            string sqlPart = @"IF OBJECT_ID(N'{0}',N'U') IS NOT  NULL 
	                                drop TABLE {0}
                                create table {0} (OBJECT_ID NVARCHAR(38) NOT NULL,YDH NVARCHAR(20) NOT NULL,BBH DECIMAL(10,0) NOT NULL,HCLX NVARCHAR(30),QD NVARCHAR(30),FKFS NVARCHAR(10),FKWD NVARCHAR(10),SJRQ NVARCHAR(30),PJRQ NVARCHAR(30),YJDWDDM NVARCHAR(10),MDDWDDM NVARCHAR(10),HCSJ NVARCHAR(30) NOT NULL,HCRYGH NVARCHAR(20),YJZH NVARCHAR(20),YYJD NVARCHAR(10),XYJD NVARCHAR(10),YMDD NVARCHAR(10),XMDD NVARCHAR(10),YCPDM NVARCHAR(10),XCPDM NVARCHAR(10),YSXLX NVARCHAR(10),XSXLX NVARCHAR(10),YQYLX NVARCHAR(10),XQYLX NVARCHAR(10),YYWLX NVARCHAR(10),XYWLX NVARCHAR(10),YJFZL NVARCHAR(40),XJFZL NVARCHAR(40),YFKFS NVARCHAR(10),XFKFS NVARCHAR(10),YYF NVARCHAR(40),XYF NVARCHAR(40),CE NVARCHAR(40),YYJZH NVARCHAR(20),XYJZH NVARCHAR(20),YBZF NVARCHAR(10),XBZF NVARCHAR(10),YBJ NVARCHAR(10),XBJ NVARCHAR(10),YWTJ NVARCHAR(10),XWTJ NVARCHAR(10),YDLPYJ NVARCHAR(10),XDLPYJ NVARCHAR(10),YTSBJ NVARCHAR(10),XTSBJ NVARCHAR(10),YTSPS NVARCHAR(10),XTSPS NVARCHAR(10),YDSHKFW NVARCHAR(10),XDSHKFW NVARCHAR(10),YSXFW NVARCHAR(10),XSXFW NVARCHAR(10),YCCCZ NVARCHAR(10),XCCCZ NVARCHAR(10),YTZBZ NVARCHAR(10),XTZBZ NVARCHAR(10),YJJRFWF NVARCHAR(10),XJJRFWF NVARCHAR(10),YBDPS NVARCHAR(10),XBDPS NVARCHAR(10),YZZFJF NVARCHAR(10),XZZFJF NVARCHAR(10),YDSHK NVARCHAR(10),XDSHK NVARCHAR(10),YZJTH NVARCHAR(10),XZJTH NVARCHAR(10),YXGPYJ NVARCHAR(10),XXGPYJ NVARCHAR(10),YPYFJF NVARCHAR(10),XPYFJF NVARCHAR(10),YGGFKFSFJF NVARCHAR(10),XGGFKFSFJF NVARCHAR(10),YPJDZBGFW NVARCHAR(10),XPJDZBGFW NVARCHAR(10),YHWBGFW NVARCHAR(10),XHWBGFW NVARCHAR(10),YSHSLFW NVARCHAR(10),XSHSLFW NVARCHAR(10), primary key(YDH, BBH, HCSJ))

                                ";

            string path = @"E:\CH\HC\data";
            string[] filenames = EUtil.RetrieveFilesFromDir(path);

            string pattern = @"(.*)(\d{4}-\d{2}-\d{2})(.*)";
            string replacement = "$2";
            List<string> strs = new List<string>(); //for msg
            MyCommon.ExecuteSql("TRUNCATE TABLE dbo.DRJL", sqc.ConnectString); //testing

            Excel.Application app = new Excel.Application(); app.Visible = true;
            Excel.Workbook wb = null;

            foreach (string filename in filenames)
            {
                // date
                string rqGang = Regex.Replace(filename, pattern, replacement);
                string rq = rqGang.Replace("-", "");
                strs.Add(rq);

                string tableName = string.Format("HCYSJ{0}", rq);


                int count = 0;

                string sql = string.Format(sqlPart, tableName);
                MyCommon.ExecuteSql(sql, sqc.ConnectString);

                wb = app.Workbooks.Open(Filename: path + "\\" + filename, ReadOnly: true);
                foreach (Excel.Worksheet sht in wb.Worksheets)
                {
                    count += EUtil.ImportData(sht, myVar, tableName);
                }
                wb.Close();


                sql = string.Format("INSERT INTO dbo.DRJL ( OBJECT_ID, RQ, HS, DRSJ, BM, CZR ) values ('{{{0}}}','{1}',{2},'{3}','{4}',{5}) "
                    , Guid.NewGuid().ToString(), rqGang, count, DateTime.Now.ToString(), tableName, "null");
                MyCommon.ExecuteSql(sql, sqc.ConnectString);

            }


            app.Quit();

            string msg = string.Join(Environment.NewLine, strs.ToArray());
            MessageBox.Show(msg);

        }