生成EXCEL文件

        /// <summary>
        /// 生成附件
        /// </summary>
        public void GenerateAttachment(string yf)
        {
            string dir = AppDomain.CurrentDomain.BaseDirectory + ".\\files";
            string wddmStr = GetWddmString();
            wddmStr = (wddmStr.Length > 12) ? wddmStr.Substring(0, 12) : wddmStr;
            string fileName = string.Format(@"{0}\{1}_{2}.xlsx", dir, OrgName, yf);

            string sqlPart = @"SELECT HH,WDDM,YF,Z 
                                ,CASE WHEN yf='{0}' THEN 'bq' ELSE 'tq' END qj 
	                            FROM dbo.LRB 
	                            WHERE  {1}
		                            AND WDDM IN ({2})
                               ";
            //月份（当月、同期）??
            string sql = string.Format(sqlPart
                , yf
                , new YF(yf).GenerateQjCondition("YF")
                , GetWddmCond());

            //MyCommon.WriteLog(sql);

            DataTable dt = MyCommon.GetDt(sql
                , new SqlConnectConfig("10.29.33.63\\sqlexpress", "TEST", "sa", "szsf").ConnectString);
            if (dt.Rows.Count == 0)
                return;

            Excel.Application excelApp = new Excel.Application();
            excelApp.DisplayAlerts = false;
            excelApp.Visible = true;

            //copy file
            string sourceFile = dir + "\\..\\" + "管理报表-分部利润表-发送模板.xlsx";
            System.IO.File.Copy(sourceFile, fileName);


            Excel.Workbook wb = excelApp.Workbooks.Open(fileName);
            Excel.Worksheet sht;

            sht = wb.Worksheets["模板 管理报表_分部利润表 （本期）"];
            for (int i = 0; i < this.Wddms.Count; i++)
            {
                sht.Cells[6, 5 + i].Value = this.Wddms[i].Pq;
                sht.Cells[7, 5 + i].Value = this.Wddms[i].Wddm;
                sht.Cells[8, 5 + i].Value = this.Wddms[i].Wdmc;
            }
            Excel.Range rng;

            //隐藏列
            rng = sht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
            int lastColumn = rng.Column;
            for (int i = lastColumn; i >= 5; i--)
            {
                rng = sht.Cells[7, i];
                string content = rng.Text;
                if (content.Trim().Equals("分部代码"))
                    rng.EntireColumn.Hidden = true;
            }
            //期间
            rng = sht.Range["B2"];
            rng.Value = string.Format("期间：{0}年{1}月 币种：人民币 单位：元"
                , new YF(yf).Year.ToString()
                , new YF(yf).Month.ToString("00"));


            sht = wb.Worksheets["模板 管理报表_分部利润表 （同期）"];
            for (int i = 0; i < this.Wddms.Count; i++)
            {
                sht.Cells[6, 5 + i].Value = this.Wddms[i].Pq;
                sht.Cells[7, 5 + i].Value = this.Wddms[i].Wddm;
                sht.Cells[8, 5 + i].Value = this.Wddms[i].Wdmc;
            }

            rng = sht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);

            lastColumn = rng.Column;
            for (int i = lastColumn; i >= 5; i--)
            {
                rng = sht.Cells[7, i];
                string content = rng.Text;
                if (content.Trim().Equals("分部代码"))
                    rng.EntireColumn.Hidden = true;
            }

            rng = sht.Range["B2"];
            rng.Value = string.Format("期间：{0}年{1}月 币种：人民币 单位：元"
                , new YF(yf).GetSTLY().Substring(0, 4)
                , new YF(yf).GetSTLY().Substring(4, 2));


            sht = wb.Worksheets["Sheet1"];//.Add();
            //string shtName = yf;
            //wb.Save();
            rng = sht.Rows["2:" + sht.UsedRange.Rows.Count.ToString()];
            rng.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);


            MyCommon.FillDtToWorksheet(sht, dt);

            wb.SaveAs(Filename: fileName);
            wb.Close(SaveChanges: true);
            excelApp.Quit();

            this.Files.Add(fileName);
        }