1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Diagnostics;
  6 using System.Linq;
  7 using System.ServiceProcess;
  8 using System.Text;
  9 using System.Threading.Tasks;
 10 using System.IO;
 11 using MmxChina.StartExhibition.userInfos;
 12 using System.Threading;
 13 using Microsoft.Office.Interop.Excel;
 14 using System.Data.SqlClient;
 15 using System.Net.Mail;
 16 using System.Net.Mime;
 17 using System.Collections;
 18 
 19 
 20 
 21 namespace Mmxchina.StarExhibition.Service
 22 {
 23 
 24     public partial class Service1 : ServiceBase
 25     {
 26 
 27         public Service1()
 28         {
 29             InitializeComponent();
 30         }
 31 
 32         //启动服务
 33         protected override void OnStart(string[] args)
 34         {
 35             Thread thread = new Thread(new ThreadStart(print));
 36             thread.Start();
 37         }
 38 
 39         //停止服务
 40         protected override void OnStop()
 41         {
 42 
 43         }
 44 
 45         //服务业务开始
 46         private void print()
 47         {
 48             while (true)
 49             {
 50                 Thread.Sleep(1000);
 51                 if (DateTime.Now.ToLongTimeString() == "0:00:01")
 52                 {
 53                     ProcessExcels(1);
 54                 }
 55             }
 56         }
 57 
 58         /// <summary>
 59         /// 准备数据
 60         /// </summary>
 61         public void ProcessExcels(int doNums)
 62         {
 63             string logInfoPath = "c:\\" + DateTime.Now.GetDateTimeFormats(‘D‘)[0].ToString() + "星展日志信息.txt";
 64             //开始准备数据
 65             File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：开始准备数据\r\n", Encoding.UTF8);
 66             SqlConnection conn = new SqlConnection("Data Source=112.124.56.152;Initial Catalog=DB_StartExhibition;User ID=wangman;pwd=wangman");
 67             conn.Open();
 68             SqlDataAdapter adapter = new SqlDataAdapter("select row_number() over (order by id desc) as 序号, userName 姓名, userTelPhone 电话, userCity 城市,case isConsumer when ‘1‘ then ‘是‘ when ‘0‘ then ‘否‘ end 是否是星展客户,userEmail 您的邮箱,createTime 上行时间 from userInfos  where createTime between  CONVERT(varchar (100),(getdate()-1),23) and CONVERT(varchar (100),getdate(),23)", conn);
 69             DataSet dataSet = new System.Data.DataSet();
 70             adapter.Fill(dataSet);
 71             conn.Close();
 72             if (dataSet.Tables[0].Rows.Count <= 0)
 73             {
 74                 CreateMessageWithAttachment(null, "星展昨天无用户数据", "星展昨天无用户信息", "891142430@qq.com", "王曼", "");
 75                 return;
 76             }
 77 
 78             Microsoft.Office.Interop.Excel.Application _excelApplicatin = null;
 79             Workbook _workBook = null;
 80             Worksheet _workSheet = null;
 81             Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//引用Excel对象 
 82             _excelApplicatin = new Microsoft.Office.Interop.Excel.Application();
 83             _excelApplicatin.Visible = false;
 84             _excelApplicatin.DisplayAlerts = false;
 85             _workBook = _excelApplicatin.Workbooks.Add(true);
 86             _workSheet = (Worksheet)_workBook.ActiveSheet;
 87             /*标题信息*/
 88             _workSheet.Name = "星展用户信息";
 89             _workSheet.get_Range("A1", "G1").Merge(_workSheet.get_Range("A1", "F1").MergeCells);
 90             _workSheet.get_Range("A1", "H1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
 91             _workSheet.get_Range("A1", "H1").Font.Size = 18;
 92             _workSheet.get_Range("A1", "H1").RowHeight = 30;
 93             _workSheet.get_Range("A1", "H1").Font.Bold = true;
 94             _workSheet.Cells[1, 1] = DateTime.Now.AddDays(-1).GetDateTimeFormats(‘D‘)[0].ToString() + "星展用户信息";
 95             /*标题信息*/
 96             /*表头信息*/
 97             int colIndex = 0;
 98             int rowIndex = 2;
 99             foreach (DataColumn col in dataSet.Tables[0].Columns)
100             {
101                 colIndex++;
102                 _workSheet.Cells[rowIndex, colIndex] = col.ColumnName;
103             }
104             _workSheet.get_Range("A2", "G2").RowHeight = 20;
105             _workSheet.get_Range("A2", "G2").Font.Bold = true;
106             _workSheet.get_Range("A1", "I" + dataSet.Tables[0].Rows.Count + 3).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
107             _workSheet.get_Range("A1", "I" + dataSet.Tables[0].Rows.Count + 3).NumberFormatLocal = "@";
108             /*表头信息*/
109 
110             foreach (DataRow row in dataSet.Tables[0].Rows)
111             {
112                 rowIndex++;
113                 colIndex = 0;
114                 foreach (DataColumn col in dataSet.Tables[0].Columns)
115                 {
116                     colIndex++;
117                     if (colIndex == 7)
118                         _workSheet.Cells[rowIndex, colIndex] = DateTime.Parse(row[col.ColumnName].ToString()).GetDateTimeFormats(‘f‘)[0].ToString();
119                     else
120                         _workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName];
121                 }
122 
123             }
124 
125             _workSheet.get_Range("A2", "H1").EntireColumn.AutoFit();
126             _workBook.SaveAs("c:\\" + DateTime.Now.AddDays(-1).GetDateTimeFormats(‘D‘)[0].ToString() + "星展用户信息");
127             _workBook.Close();
128             _excelApplicatin.Quit();
129             File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：数据准备完毕\r\n", Encoding.UTF8);
130             File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：开始发送数据\r\n", Encoding.UTF8);
131             try
132             {
133                 string[,] mailBoxs = { { "王曼", "wangman@mmxchina.com.cn" }, { "陈高峰", "gaofeng@mmxchina.com.cn" } };
134                 for (int i = 0; i < mailBoxs.GetLength(0) - 1; i++)
135                 {
136                     File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：正在给" + mailBoxs[i, 0] + "发送邮件\r\n", Encoding.UTF8);
137                     CreateMessageWithAttachment(new string[] { "c:\\" + DateTime.Now.AddDays(-1).ToLongDateString().ToString() + "星展用户信息.xlsx" }, "星展用户信息", "您好：昨天有" + dataSet.Tables[0].Rows.Count + "人登记信息，请打开附件查看详细系信息。   以上，谢谢！", mailBoxs[i, 1], "星展客户", "");
138                 }
139 
140                 File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：数据发送成功\r\n", Encoding.UTF8);
141             }
142             catch (Exception err)
143             {
144                 File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：数据发送失败.   失败原因：" + err.Message + "\r\n", Encoding.UTF8);
145                 Thread.Sleep(5000);
146                 if (doNums <= 3)
147                     ProcessExcels(++doNums);
148                 if (doNums == 4)
149                 {
150                     File.AppendAllText(logInfoPath, DateTime.Now.ToString("yyyyMMddHHmmss") + "：已尝试发送三次邮件,仍无法送达对方服务器...\r\n", Encoding.UTF8);
151                     CreateMessageWithAttachment(null, "星展用户信息未送达服务器", null, "gaofeng@mmxchina.com.cn", "陈高峰", "");
152                 }
153             }
154 
155         }
156 
157         /// <summary>
158         /// 发送邮件
159         /// </summary>
160         /// <param name="filePaths">文件路径数组可附件多个文件</param>
161         /// <param name="title">标题</param>
162         /// <param name="content">邮件躯体【当指定邮件内容为html格式时此值可为null】</param>
163         /// <param name="toUser">接收邮件地址</param>
164         /// <param name="toUserName">接收人姓名</param>
165         /// <param name="htmlPath">html格式的邮件</param>
166         /// 参考地址
167         /// http://www.cnblogs.com/cykevin/archive/2011/04/12/2013542.html
168         /// http://www.cnblogs.com/panthervic/archive/2012/08/08/2629068.html
169         public static void CreateMessageWithAttachment(string[] filePaths, string title, string content, string toUser, string toUserName, string htmlPath = "")
170         {
171             // 1， Create a message and set up the recipients.创建一个邮件，设置发送人，收信人以及主题，正文 
172             //电子邮件信息类
173             System.Net.Mail.MailAddress fromAddress = new System.Net.Mail.MailAddress("man_it@163.com", "北京无线互联广告有限公司");//发件人Email,在邮箱是这样显示的,[发件人:小明<panthervic@163.com>;]
174             System.Net.Mail.MailAddress toAddress = new System.Net.Mail.MailAddress(toUser, toUserName);//收件人Email,在邮箱是这样显示的, [收件人:小红<43327681@163.com>;]
175             System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage(fromAddress, toAddress);//创建一个电子邮件类
176             mailMessage.Subject = title;
177 
178             if (htmlPath != "")
179             {
180                 string contentFilePath = (htmlPath);//邮件的内容可以是一个html文本.
181                 System.IO.StreamReader read = new System.IO.StreamReader(contentFilePath, System.Text.Encoding.GetEncoding("GB2312"));
182                 string mailBody = read.ReadToEnd();
183                 read.Close();
184                 mailMessage.Body = mailBody;//可为html格式文本
185                 mailMessage.IsBodyHtml = true;//邮件内容是否为html格式
186             }
187             else
188             {
189                 mailMessage.Body = content;//可为html格式文本
190             }
191 
192             //mailMessage.Body = "邮件的内容";//可为html格式文本
193             mailMessage.SubjectEncoding = System.Text.Encoding.UTF8;//邮件主题编码
194             mailMessage.BodyEncoding = System.Text.Encoding.GetEncoding("GB2312");//邮件内容编码
195             mailMessage.Priority = System.Net.Mail.MailPriority.High;//邮件的优先级,有三个值:高(在邮件主题前有一个红色感叹号,表示紧急),低(在邮件主题前有一个蓝色向下箭头,表示缓慢),正常(无显示).
196 
197             // 2，Create  the file attachment for this e-mail message.创建邮件附件 
198             if (filePaths != null)
199                 foreach (string filePath in filePaths)
200                 {
201                     Attachment data = new Attachment(filePath, MediaTypeNames.Application.Octet);
202                     mailMessage.Attachments.Add(data);// 添加附件 
203                 }
204 
205             // 5，创建发送端，配置发送端Send the message. 
206             SmtpClient client = new SmtpClient("smtp.163.com");
207             client.UseDefaultCredentials = false;
208             // Add credentials if the SMTP server requires them. 
209             client.Credentials = new System.Net.NetworkCredential("man_it@163.com", "aaaa");
210             // 6，发送
211             try
212             {
213                 client.Send(mailMessage);// 发送 
214             }
215             catch
216             {
217                 mailMessage.Dispose();
218                 client.Dispose();
219                 throw new Exception("邮件服务器异常.信息未发出!");
220             }
221 
222         }
223 
224 
225     }
226 }