class Program
    {
        static string newExcelPath = string.Empty;
        static string fileName = string.Empty;

        static void Main(string[] args)
        {
            CopyReportTemplate();
            ExportDataToExcel();
            SendEmail();
        }

        #region 复制 Excel 模板 -- void CopyReportTemplate()
        /// <summary>
        /// 复制 Excel 模板
        /// 
        /// ++++++++++++++++++++++++++++++++++++++++++++
        /// liam 2021.03.9
        /// 获取应用程序的当前工作目录。 
        /// System.IO.Directory.GetCurrentDirectory() 
        /// 获取启动了应用程序的可执行文件的路径。
        /// System.Windows.Forms.Application.StartupPath 
        /// +++++++++++++++++++++++++++++++++++++++++++++
        /// 
        /// </summary>
        private static void CopyReportTemplate()
        {
            string excelTemplatePath = System.Windows.Forms.Application.StartupPath + "\\出行申报报表.xlsx";
            //Directory.GetCurrentDirectory() + "\\出行报表.xlsx";
            string str = DateTime.Now.ToString("yyyyMMdd");
            fileName = "出行申报报表-" + str;

            newExcelPath =System.Windows.Forms.Application.StartupPath + "\\Report\\" + fileName + ".xlsx";
            //Directory.GetCurrentDirectory() + "\\Report\\" + fileName + ".xlsx";
            File.Delete(newExcelPath);
            File.Copy(excelTemplatePath, newExcelPath);
        }
        #endregion

        #region 获取报表数据 -- DataSet GetReportData()
        /// <summary>
        /// 获取报表数据
        /// </summary>
        /// <returns></returns>
        private static DataSet GetReportData()
        {
            DataSet ds = new DataSet();
            string sql0 = "pc_Select_CheckHolidayInformationData_No";
            string date = DateTime.Now.ToString("yyyy-MM-dd");
            SqlParameter[] sp = {
                                new SqlParameter("@date",date)
            };
            DataTable table0 = GetDataTable(CommandType.StoredProcedure, sql0, sp);
            table0.TableName = "未申报记录";
            string sql1 = "pc_Select_CheckHolidayInformationData_Yes";
            DataTable table1 = GetDataTable(CommandType.StoredProcedure, sql1);
            table1.TableName = "已申报记录";
            ds.Tables.Add(table0);
            ds.Tables.Add(table1);

            return ds;
        }
        #endregion

        #region 导出数据至 Excel -- static void ExportDataToExcel()
        /// <summary>
        /// 导出数据至 Excel
        /// </summary>
        private static void ExportDataToExcel()
        {
            XSSFWorkbook workbook = null;
            using (FileStream file = new FileStream(newExcelPath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
                file.Close();
            }
            ICellStyle style = CellStyle(workbook);
            ICellStyle styleRed = CellStyle(workbook);
            styleRed.FillPattern = FillPattern.SolidForeground;
            styleRed.FillForegroundColor = HSSFColor.Coral.Index;
            DataSet ds = GetReportData();
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable table = ds.Tables[i];
                ISheet sheet = workbook.GetSheet(table.TableName);
                for (int j = 0; j < table.Rows.Count; j++)
                {
                    IRow row = sheet.CreateRow(1 + j);
                    row.Height = 18 * 20;

                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        string colName = table.Columns[k].ColumnName;
                        string str = CheckFieldIsNullOrEmpty(table, j, colName);
                        ICell cell = row.CreateCell(k);
                        cell.SetCellValue(str);
                    }
                }
            }
            using (FileStream fs = new FileStream(newExcelPath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                workbook.Write(fs);
                fs.Close();
            }
        }
        #endregion

        #region 创建 Excel 单元格样式 -- ICellStyle CellStyle(XSSFWorkbook workbook)
        /// <summary>
        /// 创建 Excel 单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static ICellStyle CellStyle(XSSFWorkbook workbook)
        {
            IFont fontAll = workbook.CreateFont();
            fontAll.FontName = "Microsoft YaHei";
            fontAll.FontHeight = 10 * 20;
            ICellStyle ItemCellStyle = workbook.CreateCellStyle();
            ItemCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            ItemCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            ItemCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            ItemCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            ItemCellStyle.BottomBorderColor = HSSFColor.Grey25Percent.Index;
            ItemCellStyle.LeftBorderColor = HSSFColor.Grey25Percent.Index;
            ItemCellStyle.RightBorderColor = HSSFColor.Grey25Percent.Index;
            ItemCellStyle.TopBorderColor = HSSFColor.Grey25Percent.Index;
            ItemCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
            ItemCellStyle.VerticalAlignment = VerticalAlignment.Center;
            ItemCellStyle.SetFont(fontAll);
            return ItemCellStyle;
        }
        #endregion

        #region 检查 Table CELL 数据 -- string CheckFieldIsNullOrEmpty(DataTable table, string key)
        /// <summary>
        /// 检查 Table CELL 数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string CheckFieldIsNullOrEmpty(DataTable table, string key)
        {
            string value = string.Empty;
            if (table != null && table.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(table.Rows[0][key].ToString()))
                {
                    value = table.Rows[0][key].ToString();
                }
            }
            return value;
        }

        /// <summary>
        /// 检查 Table CELL 数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="rowIndex"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string CheckFieldIsNullOrEmpty(DataTable table, int rowIndex, string key)
        {
            string value = string.Empty;
            if (table != null && table.Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(table.Rows[rowIndex][key].ToString()))
                {
                    value = table.Rows[rowIndex][key].ToString();
                }
            }
            return value;
        }
        #endregion

        #region 发送邮件 -- static void SendEmail()
        /// <summary>
        /// 发送邮件
        /// </summary>
        private static void SendEmail()
        {
            //string sql = "pc_SendEmail_CheckUserReport";
            //SqlParameter[] sp = {
            //                    new SqlParameter("@path",newExcelPath),
            //                    new SqlParameter("@fileName",fileName)
            //};
            //int i = NonQuery(CommandType.StoredProcedure, sql, sp);

            //设置邮件的服务器
            string host = "IP地址";
            //替换成你的hotmail账户
            string mailAddress = "发邮件地址";
            //目标邮件地址。可添加多个地址，逗号分隔
            string[] ToAddress = { "目标邮件地址" };
            string[] CcAddress = { "抄送邮件地址" };

           
            SmtpClient smtp = new SmtpClient(host);
            smtp.EnableSsl = false; //开启安全连接。
            smtp.DeliveryMethod = SmtpDeliveryMethod.Network; //使用网络传送
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("<span style='font-family:Arial; font-size: 13px; '>Dear All:</span><br/><br/>");
            sb.AppendLine("<div style='font-family:Microsoft YaHei UI;font-size: 13px; padding-left:100px; width:300px;'>          出行申报情况，请查阅附件，谢谢。</div>");
            sb.AppendLine("<br/><span style='font-family:Arial;font-size: 13px; '> Best regards, CMOA System;</ span ><br/> ");
            sb.AppendLine("<span style='font-family:Arial;font-size: 13px;'>This is auto-generated email, please do not reply. Thank you for your attention !<br/></span><br/><br/>");
            MailMessage message = new MailMessage(); //创建邮件
            message.From = new MailAddress(mailAddress);
            message.Subject = fileName;
            message.IsBodyHtml = true;
            for (int i = 0; i < ToAddress.Length; i++)
            {
                message.To.Add(ToAddress[i]);
            }
            for (int i = 0; i < CcAddress.Length; i++)
            {
                message.CC.Add(CcAddress[i]);
            }
            message.Body = sb.ToString();

            //string newExcelPath = @"D:\MID PDF.zip";

            string MIME = MimeMapping.GetMimeMapping(newExcelPath);//文件的MediaType MIME 
                                                                   //message.AlternateViews.Add(new AlternateView(fileAddress,MIME)); //发送附加内容（附加的内容为文件中的内容）

            //发送附加件
            message.Attachments.Add(new Attachment(newExcelPath, MIME));
            smtp.Send(message); //发送邮件
        }
        #endregion

        #region DataTable -- static DataTable GetDataTable(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        /// <summary>
        /// DataTable
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(CommandType cmdType, string cmdText, params SqlParameter[] cmdParams)
        {
            DateTime starttime = DateTime.Now; //起始时间
            using (SqlConnection Connection = new SqlConnection("Server=IP地址;DataBase=库名;Uid=账号;Pwd=密码"))
            {
                DataTable dt = new DataTable();
                Connection.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmdText, Connection);
                try
                {
                    da.SelectCommand.CommandTimeout = 300;
                    da.SelectCommand.CommandType = cmdType;          //设置命令类型
                    da.SelectCommand.Parameters.AddRange(cmdParams);
                    da.Fill(dt);

                    return dt;

                }
                catch (System.Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    da.Dispose();
                    Connection.Close();
                    return null;
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
        }
        #endregion

        #region 执行数据的增、删、改方法 -- static int NonQuery(CommandType type, string CommandText, string sysName, params SqlParameter[] pars)
        /// <summary>
        /// 执行数据的增、删、改方法
        /// </summary>
        /// <param name="type">要执行的数据库命令类型</param>
        /// <param name="CommandText">要执行的SQL语句或存储过程</param>
        /// <param name="pars">所需的参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int NonQuery(CommandType type, string CommandText, params SqlParameter[] pars)
        {
            DateTime starttime = DateTime.Now; //起始时间
            int i = 0;
            using (SqlConnection Connection = new SqlConnection("Server=IP地址;DataBase=库名;Uid=账号;Pwd=密码"))
            {
                Connection.Open();
                //实例化数据库命令对象
                SqlCommand cmd = new SqlCommand(CommandText, Connection);
                try
                {
                    cmd.CommandTimeout = 0;
                    //设置命令执行类型
                    cmd.CommandType = type;
                    //设置SQL语句或存储过程参数
                    SqlParameter[] sp = new SqlParameter[pars.Length];
                    sp = pars;
                    cmd.Parameters.AddRange(sp);
                    //调用方法得到SqlDataReader对象
                    i = cmd.ExecuteNonQuery();

                    return i;
                }
                catch (Exception err)
                {
                    cmd.Dispose();
                    Connection.Close();
                    throw new Exception(err.Message);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
        }
        #endregion
    }