打开、修改、保存Excel文件和发送邮件

private void ImportQAISSUEReport()
        {
            //初始化 SQL 查询语句,注意空格
            string sqlUnion = "";
            string a = "sum(t.QtyBalance * sku.stdcube) / 1000000 Vol ";
            string b = "where t.StorerKey = sku.storerkey and t.Sku = sku.sku ";
            string c = "and trunc(t.CutOffDate) > sysdate - 61 and t.StorerKey = 'CSX' ";
            string d = "and mod(trunc(t.CutOffDate) - to_date('2000-01-03','YYYY-MM-DD') , 7) = 0 ";
            string e = "group by trunc(t.CutOffDate), t.WHSEID, t.StorerKey)";
            sqlUnion += "select * from (select trunc(t.CutOffDate) InvDate,'CN61', t.StorerKey," + a + "FROM WH7.tempinvrpt t, WH7.sku sku " + b + "and trunc(t.CutOffDate) > sysdate - 61 and t.StorerKey = 'CSC' " + d + e + "UNION ";
            sqlUnion += "(select trunc(t.CutOffDate) InvDate,'CN64', t.StorerKey," + a + "FROM WH2.tempinvrpt t, WH2.sku  sku " + b + c + d + e + "UNION ";
            sqlUnion += "(select trunc(t.CutOffDate) InvDate,'CN66', t.StorerKey," + a + "FROM WH21.tempinvrpt t, WH21.sku  sku " + b + c + d + e + "UNION ";
            sqlUnion += "(select trunc(t.CutOffDate) InvDate,'CN67', t.StorerKey," + a + "FROM WH15.tempinvrpt t, WH15.sku  sku " + b + c + d + e;

            //填充对应的DATASET
            conn.Open();
            DataTable dt = new DataTable();
            DataSet dsOrderUnion = new DataSet();
            dsOrderUnion = conn.GetDataSet(sqlUnion);
            dt = dsOrderUnion.Tables[0];

            // 模板文件位置
            string strFileName = Environment.CurrentDirectory + @"\QA_ISSUE_Tmp.xlsx";
            //备份位置
            string backFilePath = Environment.CurrentDirectory + @"\excel\issue" + "\\" + DateTime.Now.ToString("yyyy") + "\\" + DateTime.Now.ToString("yyyyMMdd");
            //检查备份是否存在
            if (!Directory.Exists(backFilePath))
            {
                Directory.CreateDirectory(backFilePath);
            }
            //保存的文件名
            string strSaveFileName = backFilePath + @"\CSX CSCINV(库存报表)" + @".xlsx";

            //压缩
            //string fileName = "QA_RECEIPT_" + System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "") + @".rar";

            //初始化EXCEL DLL 控件
            Excel.Application ThisApplication = new Excel.ApplicationClass();
            Excel.Workbook ThisWorkBook;
            object missing = System.Reflection.Missing.Value;
            try
            {    //加载Excel模板文件     
                ThisWorkBook = ThisApplication.Workbooks.Open(strFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
                ThisApplication.Visible = false;
                int m = 0;
                //将查询出来的数据填入模板
                for (int i = 0; i < dt.Rows.Count; i = i + 4)
                {
                    DataRow dr = dt.Rows[i];
                    DateTime dateTime = DateTime.Parse(dr[0].ToString());
                    ThisSheet.Cells[m + 7, 2] = dateTime.ToString("yyyy-MM-dd");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        dr = dt.Rows[i + j];
                        ThisSheet.Cells[m + 7, j + 3] = dr[3].ToString();
                    }
                    m++;
                }
                //更新数据后另存为新文件     
                ThisSheet.SaveAs(strSaveFileName, missing, missing, missing, missing, missing, missing, missing, missing);

                //关闭文件，清理EXCEL
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)ThisSheet);
                ThisSheet = null;
                ThisWorkBook.Close(true, strSaveFileName, null);

                //指定接收人清单初始化
                string strSendList = "Jie.OuYang@tollgroup.com";
                //string backRarFileDir = "";
                //string sendRARfile = backRarFileDir + "\\" + fileName;

                //发送给指定接收人
                SendEmailBySTA(strSendList, strSaveFileName, DateTime.Today.ToString("yyyy-MM-dd") + " QA 报告", "附件为QA 报告，请查收。谢谢！");
            }
            catch (Exception ex)
            {
                //出错发送给IT
                SendEmailBySTA("471478656@qq.com;", "", "发送QA -ISSUE 报告_出错，程序名称QAREPORT", ex.Message);
            }
            finally
            {
                ThisWorkBook = null;
                ThisApplication.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)ThisApplication);
                ThisApplication = null;
                dt = null;
            }



        }

ImportQAISSUEReport()

        public DataSet GetDataSet(string strSql)
        {
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand(strSql, connection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            dataAdapter.SelectCommand = cmd;
            dataAdapter.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 引用DLL后，可直接调用此函数
        /// </summary>
        /// <param name="sendToList">收件人</param>
        /// <param name="attachment">附件</param>
        /// <param name="subject">标题</param>
        /// <param name="mailBody">内容</param>

        public static void SendEmailBySTA(string sendToList, string attachment, string subject, string mailBody)
        {
            SendMailDLL.CSendMail csendMail = new SendMailDLL.CSendMail();
            if (string.IsNullOrEmpty(attachment.Trim()))
            {
                csendMail.SendMail(sendToList, subject, mailBody);
            }
            else
            {
                csendMail.SendMail(sendToList, attachment, subject, mailBody);
            }
        }

        private static Conn conn = new Conn();
        public Form1()
        {
            InitializeComponent();
            ImportQAISSUEReport();
        }