private void SendEmail(string emailAddress, string companyName,string proxy, string officer, DataTable dt)
{
ChangeOfOwnerReport report = new ChangeOfOwnerReport();
MemoryStream stream = report.ExportToExcel(companyName, proxy, officer, dt);
string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format("{0}.xls", "Nomination Notice " + Guid.NewGuid().ToString()));
FileStream ss = new FileStream(fileName, FileMode.OpenOrCreate);
byte[] data = stream.GetBuffer();
ss.Write(data, 0, data.Length);
ss.Flush();
ss.Close();
EmailTransaction email = new EmailTransaction();
//create zip file
string zipFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format("{0}.zip", "Nomination Notice " + Guid.NewGuid().ToString()));
List<string> srcList = new List<string>();
srcList.Add(fileName);
ZipUtility.Zip(zipFileName, srcList, String.Empty);
FileInfo zipFile = new FileInfo(zipFileName);
FileStream fs = zipFile.OpenRead();
byte[] zipByte = new byte[(int)zipFile.Length];
fs.Read(zipByte, 0, (int)zipFile.Length);
email.EmTrAttachment = zipByte;
email.EmTrAttachmentName = "Nomination Notice Response.zip";
fs.Close();
fs.Dispose();
System.IO.File.Delete(zipFileName);
System.IO.File.Delete(fileName);
email.EmTrSubject = "Nomination Notice Response";
email.EmTrTo = emailAddress;
string content = "Greetings\r\nPlease find attached CofCT adjudication of change of offender, request submitted by you.\r\nRegards";
content += "\r\nCity of Cape Town Traffic management";
email.EmTrContent = content;
EmailManager emailManager = new EmailManager();
emailManager.SendMail(email);
}
NPOI 导出：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using SIL.AARTO.BLL.EntLib;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
namespace SIL.AARTO.BLL.Report
{
public class ChangeOfOwnerReport
{
private HSSFWorkbook wb;
private ISheet sheet;
public ChangeOfOwnerReport()
{
wb = new HSSFWorkbook();
}
public MemoryStream ExportToExcel(string companyName,string proxy,string officer, DataTable dt)
{
try
{
MemoryStream stream = new MemoryStream();
sheet = wb.CreateSheet();
wb.SetSheetName(0, "Proxy Nomination Notice");
this.createHeading(companyName, proxy, officer);
this.createTitle();
this.createDataRows(dt);
for (int i = 0; i < 5; i++)
{
sheet.AutoSizeColumn((short)i);
}
wb.Write(stream);
return stream;
}
catch (Exception e)
{
EntLibLogger.WriteErrorLog(e, LogCategory.Exception, "COO");
throw e;
}
}
private void createHeading(string companyName, string proxy, string officer)
{
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
font.FontHeightInPoints = (short)20;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.CENTER;
sheet.CreateRow(0);
ICell cell = sheet.GetRow(0).CreateCell(0);
cell.CellStyle = style;
HSSFRichTextString rtf = new HSSFRichTextString("Submitted by " + companyName + " Proxy " + proxy);
rtf.ApplyFont(font);
cell.SetCellValue(rtf);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region);
IFont font1 = wb.CreateFont();
font1.Boldweight = (short)FontBoldWeight.BOLD;
sheet.CreateRow(1);
ICell cell1 = sheet.GetRow(1).CreateCell(0);
cell1.CellStyle = style;
HSSFRichTextString rtf1 = new HSSFRichTextString("Processed by Officer:" + officer);
rtf1.ApplyFont(font1);
cell1.SetCellValue(rtf1);
CellRangeAddress region1 = new CellRangeAddress(1, 1, 0, 3);
sheet.AddMergedRegion(region1);
ICell cell2 = sheet.GetRow(1).CreateCell(4);
cell2.CellStyle = style;
HSSFRichTextString rtf2 = new HSSFRichTextString(DateTime.Now.ToString("yyyy-MM-dd"));
rtf2.ApplyFont(font1);
cell2.SetCellValue(rtf2);
}
private void createTitle()
{
sheet.CreateRow(2);
IFont fontHeading = wb.CreateFont();
fontHeading.Boldweight = (short)FontBoldWeight.BOLD;
ICellStyle styleHeading = wb.CreateCellStyle();
styleHeading.Alignment = HorizontalAlignment.CENTER;
styleHeading.VerticalAlignment = VerticalAlignment.CENTER;
ICell cell1 = sheet.GetRow(2).CreateCell(0);
cell1.CellStyle = styleHeading;
HSSFRichTextString rtf1 = new HSSFRichTextString("Notice No");
rtf1.ApplyFont(fontHeading);
cell1.SetCellValue(rtf1);
ICell cell2 = sheet.GetRow(2).CreateCell(1);
cell2.CellStyle = styleHeading;
HSSFRichTextString rtf2 = new HSSFRichTextString("Registration");
rtf2.ApplyFont(fontHeading);
cell2.SetCellValue(rtf2);
ICell cell3 = sheet.GetRow(2).CreateCell(2);
cell3.CellStyle = styleHeading;
HSSFRichTextString rtf3 = new HSSFRichTextString("Offence Date");
rtf3.ApplyFont(fontHeading);
cell3.SetCellValue(rtf3);
ICell cell4 = sheet.GetRow(2).CreateCell(3);
cell4.CellStyle = styleHeading;
HSSFRichTextString rtf4 = new HSSFRichTextString("Offender");
rtf4.ApplyFont(fontHeading);
cell4.SetCellValue(rtf4);
ICell cell5 = sheet.GetRow(2).CreateCell(4);
cell5.CellStyle = styleHeading;
HSSFRichTextString rtf5 = new HSSFRichTextString("Status");
rtf5.ApplyFont(fontHeading);
cell5.SetCellValue(rtf5);
}
private void createDataRows(DataTable dt)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.CreateRow(3 + i);
ICell cell1 = sheet.GetRow(3 + i).CreateCell(0);
cell1.SetCellValue(dt.Rows[i]["Notice No"].ToString());
ICell cell2 = sheet.GetRow(3 + i).CreateCell(1);
cell2.SetCellValue(dt.Rows[i]["Registration"].ToString());
ICell cell3 = sheet.GetRow(3 + i).CreateCell(2);
cell3.SetCellValue(dt.Rows[i]["Offence Date"].ToString());
ICell cell4 = sheet.GetRow(3 + i).CreateCell(3);
cell4.SetCellValue(dt.Rows[i]["Offender"].ToString());
ICell cell5 = sheet.GetRow(3 + i).CreateCell(4);
cell5.SetCellValue(dt.Rows[i]["Status"].ToString());
}
}
}
}
发邮件：EmailManager.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;
using SIL.ServiceQueueLibrary.DAL.Entities;
using SIL.ServiceQueueLibrary.DAL.Services;
using System.ComponentModel;
using System.Net;
using System.IO;
using System.Net.Mime;
//using SIL.ServiceLibrary.Email;
namespace SIL.ServiceLibrary
{
public class EmailManager
{
private string _from = string.Empty;
private string _userName = string.Empty;
private string _smtp = string.Empty;
private string _password = string.Empty;
private bool _isAuthenticate = false;
private int _port = 0;
static object lockEmail = new object();
//readonly Email.EmailServerInformation emailServer = new Email.EmailServerInformation();
//readonly string _connStr = string.Empty;
public EmailManager()
{ }
public EmailManager(string from, string smtp, string userName, string password, bool isAuthenticate, int? port)
{
//this._connStr = connStr;
//_from = System.Configuration.ConfigurationManager.AppSettings["AdminEmailAddress"];
//_smtp = System.Configuration.ConfigurationManager.AppSettings["SmtpServer"];
//_userName = System.Configuration.ConfigurationManager.AppSettings["SmtpUserName"];
//_password = System.Configuration.ConfigurationManager.AppSettings["SmtpPassword"];
//_isAuthenticate = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["IsAuthenticate"]);
//_port = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SmtpPort"]);
this._from = from;
this._smtp = smtp;
if (isAuthenticate)
{
this._userName = userName;
this._password = password;
this._isAuthenticate = isAuthenticate;
}
if (port != null)
{
this._port = port.Value;
}
}
/// <summary>
/// Update sentToQueueDateTime field by FK(emtrID)
/// </summary>
/// <param name="emTrID"></param>
/// <param name="sentToQueueDate"></param>
/// <returns></returns>
//public List<EmailTransaction> GetEmailTransaction(int pageSize)
//{
// SqlParameter[] para = new SqlParameter[]{
// new SqlParameter("@PageSize",SqlDbType.Int)
// };
// para[0].Value = pageSize;
// EmailTransaction email = null;
// List<EmailTransaction> returnList = new List<EmailTransaction>();
// SqlConnection con = new SqlConnection(this._connStr);
// SqlCommand cmd = con.CreateCommand();
// cmd.CommandText = "SILCustom_EmailTransaction_GetEmailTransaction";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddRange(para);
// try
// {
// if (con.State == ConnectionState.Closed)
// con.Open();
// using (SqlDataReader reader = cmd.ExecuteReader())
// {
// while (reader.Read())
// {
// email = new EmailTransaction();
// email.EmTrId = (decimal)reader["EmTrId"];
// email.EmTrTo = reader["EmTrTo"].ToString();
// email.EmTrCc = reader["EmTrCc"].ToString();
// email.EmTrBcc = reader["EmTrBcc"].ToString();
// //email.EmTrFrom = reader["EmTrFrom"].ToString();
// email.EmTrSubject = reader["EmTrSubject"].ToString();
// email.EmTrContent = reader["EmTrContent"].ToString();
// email.EmTrSendDate = reader["EmTrSendDate"] is DBNull ? null : (DateTime?)reader["EmTrSendDate"];
// email.EmTrRetryCount = reader["EmTrRetryCount"] is DBNull ? null : (byte?)reader["EmTrRetryCount"];
// email.EmTrSendSuccess = reader["EmTrSendSuccess"] is DBNull ? null : (bool?)reader["EmTrSendSuccess"];
// email.LastUser = reader["LastUser"].ToString();
// //email.EmTrLoadedDate = Convert.ToDateTime(reader["EmTrLoadedDate"]);
// // email.SentToQueueDateTime = reader["SentToQueueDateTime"] is DBNull ? null : (DateTime?)reader["SentToQueueDateTime"];
// //email.IsHtmlEmail = Convert.ToBoolean(reader["EmTrIsHtml"]);
// returnList.Add(email);
// }
// }
// return returnList;
// }
// catch
// {
// return null;
// }
// finally
// {
// if (con.State == ConnectionState.Open)
// con.Close();
// }
//}
public void GetEmailToSend()
{
//<add key="SmtpServer" value="smtp.163.com"/>
//<add key="UserName" value="test@163.com"/>
//<add key="Password" value="000000"/>
//System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
string sql = " EmTrSendDate IS NULL AND (EmTrSendSuccess IS NULL OR EmTrSendSuccess= 0) AND (EmTrRetryCount IS NULL OR EmTrRetryCount < 3 )";
int totalCount = 0;
TList<EmailTransaction> emails = new EmailTransactionService().GetPaged(sql, "", 0, 0, out totalCount);
MailMessage message = null;
foreach (EmailTransaction email in emails)
{
message = new MailMessage();
message.From = new MailAddress(_from.Trim());
if (!String.IsNullOrEmpty(email.EmTrTo))
{
foreach (string addr in email.EmTrTo.Trim().Split(';'))
{
if (!String.IsNullOrEmpty(addr))
message.To.Add(new MailAddress(addr));
}
}
if (!String.IsNullOrEmpty(email.EmTrCc))
{
foreach (string addr in email.EmTrCc.Trim().Split(';'))
{
if (!String.IsNullOrEmpty(addr))
message.CC.Add(new MailAddress(addr));
}
}
message.Subject = email.EmTrSubject;
message.Body = email.EmTrContent;
message.Priority = MailPriority.High;
//msg.Subject = message.Subject;
message.SubjectEncoding = System.Text.Encoding.UTF8;
message.BodyEncoding = System.Text.Encoding.UTF8;
message.IsBodyHtml = false;
//add attachment
if (!String.IsNullOrEmpty(email.EmTrAttachmentName)
&& email.EmTrAttachment != null
&& email.EmTrAttachment.Count() > 0)
{
message.Attachments.Add(AddAttachment(email.EmTrAttachment, email.EmTrAttachmentName));
}
SmtpClient client = new SmtpClient(_smtp);
//client.SendCompleted += new SendCompletedEventHandler(SendCompletedCallback);
//client.UseDefaultCredentials = true;
if (_isAuthenticate)
{
client.Credentials = new System.Net.NetworkCredential(_userName, _password);
client.DeliveryMethod = SmtpDeliveryMethod.Network;
}
else
{
client.UseDefaultCredentials = true;
//client.DeliveryMethod = SmtpDeliveryMethod.PickupDirectoryFromIis;
//client.Credentials = CredentialCache.DefaultNetworkCredentials;
}
//client.EnableSsl = true;
if (_port > 0) client.Port = _port;
object userState = email.EmTrId;
try
{
lock (lockEmail)
{
client.Send(message);
//client.SendAsync(message, userState);
//EmailInterface e = new EmailInterface();
//e.MhtUnLockCode = System.Configuration.ConfigurationManager.AppSettings["MhtUnLockCode"]; ;
//e.EmailUnLockCode = System.Configuration.ConfigurationManager.AppSettings["MailUnLockCode"];
//e.SmtpServer = emailServer.SmtpServer;
//e.SendMail(email,emailServer);
//client.Send(message);
EmailTransaction emailTran = new EmailTransactionService().GetByEmTrId(email.EmTrId);
if (emailTran != null)
{
emailTran.EmTrSendDate = DateTime.Now;
emailTran.EmTrSendSuccess = true;
new EmailTransactionService().Save(emailTran);
}
}
//while (mailSent == false)
//{
//}
}
catch (System.Net.Mail.SmtpException ex)
{
throw ex;
}
}
}
/// <summary>
/// Test function
/// </summary>
/// <param name="from"></param>
/// <param name="mailTo"></param>
/// <param name="subject"></param>
/// <param name="body"></param>
/// <returns></returns>
public bool SendEmail(string from, string mailTo, string subject, string body)
{
bool r = false;
try
{
System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
msg.To.Add(mailTo);
msg.From = new MailAddress(from, from, System.Text.Encoding.UTF8);
msg.Subject = subject;
msg.SubjectEncoding = System.Text.Encoding.UTF8;
msg.Body = body;
msg.BodyEncoding = System.Text.Encoding.UTF8;
msg.IsBodyHtml = true;
msg.Priority = System.Net.Mail.MailPriority.High;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential("jakezyz@163.com", "test");
client.Port = 25;
client.Host = "smtp.163.com";
client.EnableSsl = true;
object userState = msg;
client.Send(msg);
r = true;
}
catch (System.Net.Mail.SmtpException ex)
{
throw ex;
}
return r;
}
public void SendMail(EmailTransaction emailTran)
{
try
{
if (emailTran != null)
{
emailTran.EmTrRetryCount = 0;
emailTran.EmTrCreatedDate = DateTime.Now;
emailTran.EmTrSendDate = null;
emailTran.EmTrSendSuccess = false;
new EmailTransactionService().Save(emailTran);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void SendCompletedCallback(object sender, AsyncCompletedEventArgs e)
{
// Get the unique identifier for this asynchronous operation.
decimal token = (decimal)e.UserState;
if (e.Cancelled || e.Error != null)
{ }
else
{
EmailTransaction emailTran = new EmailTransactionService().GetByEmTrId(token);
if (emailTran != null)
{
emailTran.EmTrSendDate = DateTime.Now;
emailTran.EmTrSendSuccess = true;
new EmailTransactionService().Save(emailTran);
}
}
}
public Attachment AddAttachment(byte[] attachment, string fileName)
{
try
{
MemoryStream ms = new MemoryStream(attachment);
string extension = Path.GetExtension(fileName);
ContentType contentType = null;
if (!String.IsNullOrEmpty(extension))
{
switch (extension.ToLower())
{
case ".zip":
contentType = new ContentType("application/x-zip-compressed"); break;
case ".pdf":
contentType = new ContentType("application/pdf"); break;
case ".doc":
contentType = new ContentType("application/msword"); break;
case ".docx":
contentType = new ContentType("application/msword"); break;
case ".xls":
contentType = new ContentType("application/x-excel"); break;
case ".xlsx":
contentType = new ContentType("application/x-excel"); break;
case ".txt":
contentType = new ContentType("text/plain"); break;
case ".html":
contentType = new ContentType("text/html"); break;
case ".htm":
contentType = new ContentType("text/html"); break;
default:
contentType = new ContentType("application/x-zip-compressed"); break;
}
}
else
{
contentType = new ContentType("application/x-zip-compressed");
}
Attachment atth = new Attachment(ms, contentType);
atth.Name = fileName;
atth.NameEncoding = Encoding.UTF8;
//atth.TransferEncoding = TransferEncoding.SevenBit;
return atth;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
zip 压缩：要添加ICSharpCode.SharpZipLib.dll
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Checksums;
using System.IO;
namespace MvcModelApp.Common
{
public class ZipUtility
{
public static int Zip(string zipFileName, List<string> srcFiles, string password)
{
ZipOutputStream zipStream = null;
FileStream streamWriter = null;
string fileName;
int count = 0;
try
{
if (srcFiles == null) return count;
//Use Crc32
Crc32 crc32 = new Crc32();
//Create Zip File
zipStream = new ZipOutputStream(File.Create(zipFileName));
//Specify Level
zipStream.SetLevel(Convert.ToInt32(9));
//Specify Password
if (password != null && password.Trim().Length > 0)
{
zipStream.Password = password;
}
//Foreach File
foreach (string file in srcFiles)
{
//Read the file to stream
streamWriter = File.OpenRead(file);
byte[] buffer = new byte[streamWriter.Length];
streamWriter.Read(buffer, 0, buffer.Length);
streamWriter.Close();
//Specify ZipEntry
crc32.Reset();
crc32.Update(buffer);
fileName = file.Substring(file.LastIndexOf('\\') + 1);
ZipEntry zipEntry = new ZipEntry(fileName);
zipEntry.DateTime = DateTime.Now;
zipEntry.Size = buffer.Length;
zipEntry.Crc = crc32.Value;
//Put file info into zip stream
zipStream.PutNextEntry(zipEntry);
//Put file data into zip stream
zipStream.Write(buffer, 0, buffer.Length);
count++;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//Clear Resource
if (streamWriter != null)
{
streamWriter.Close();
}
if (zipStream != null)
{
zipStream.Finish();
zipStream.Close();
}
}
return count;
}
public static List<string> Unzip(string destFolder, string srcZipFile, string password)
{
List<string> fileList = new List<string>();
ZipInputStream zipStream = null;
ZipEntry zipEntry = null;
FileStream streamWriter = null;
int count = 0;
int bufferSize = 2048;
try
{
zipStream = new ZipInputStream(File.OpenRead(srcZipFile));
zipStream.Password = password;
while ((zipEntry = zipStream.GetNextEntry()) != null)
{
string zipFileDirectory = Path.GetDirectoryName(zipEntry.Name);
string destFileDirectory = Path.Combine(destFolder, zipFileDirectory);
if (!Directory.Exists(destFileDirectory))
{
Directory.CreateDirectory(destFileDirectory);
}
string fileName = Path.GetFileName(zipEntry.Name);
if (fileName.Length > 0)
{
string destFilePath = Path.Combine(destFileDirectory, fileName);
streamWriter = File.Create(destFilePath);
int size = bufferSize;
byte[] data = new byte[bufferSize];
long extractCount = 0;
while (true)
{
size = zipStream.Read(data, 0, data.Length);
if (size > 0)
{
streamWriter.Write(data, 0, size);
}
else
{
break;
}
extractCount += size;
}
streamWriter.Flush();
streamWriter.Close();
fileList.Add(fileName);
count++;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (zipStream != null)
{
zipStream.Close();
}
if (streamWriter != null)
{
streamWriter.Close();
}
}
return fileList;
}
}
}
