NPOI,导出Execl,压缩文件zip,发送Email
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; } } }
