工作中遇到的常用助手类，这里只有自己用到的一些东西。
EmaileHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Net.Mail; using System.Net; using System.Net.Mime; using OpenPop.Pop3; using OpenPop.Mime; using System.IO; using System.Collections; namespace Common { public static class EmailHelper { #region private static Dictionary<string, Message> EmaiMessagelList = new Dictionary<string, Message>(); public static int EmailCount = 0; public static bool isDownLoad = false; #endregion #region ServerInfo private static string SendServer = System.Configuration.ConfigurationManager.AppSettings["ESendserver"].ToString(); private static string From = System.Configuration.ConfigurationManager.AppSettings["ESendAdd"].ToString(); private static string Pwd = System.Configuration.ConfigurationManager.AppSettings["ESendpwd"].ToString(); private static string ReceiveServer = System.Configuration.ConfigurationManager.AppSettings["EReceiveserver"].ToString(); private static string ReceiveAdd = System.Configuration.ConfigurationManager.AppSettings["EReceiveAdd"].ToString(); private static string ReceivePwd = System.Configuration.ConfigurationManager.AppSettings["EReceivepwd"].ToString(); #endregion #region SendEmail /// <summary> /// 发送邮件(统一的发件人) /// </summary> /// <param name="toemailadd">收件人地址</param> /// <param name="cclist">抄送人地址</param> /// <param name="subject">主题</param> /// <param name="content">内容</param> /// <param name="attachmentlist">附件</param> /// <param name="isproxy">是否使用代理</param> /// <param name="msg">返回提示</param> /// <returns>返回ture or false 表示是否发送成功</returns> public static bool SendEmail(string toemailaddresslist, string ccaddresslist, string subject, string content, string attachmentlist, bool isproxy) { try { MailMessage Mail = new MailMessage(); Mail.From = new MailAddress(From); string[] ToAddressList = toemailaddresslist.Split(';'); for (int i = 0; i < ToAddressList.Length; i++) { Mail.To.Add(new MailAddress(ToAddressList[i].ToString())); } Mail.BodyEncoding = System.Text.Encoding.UTF8; Mail.IsBodyHtml = true; //添加抄送人 string[] List = ccaddresslist.Split(';'); for (int i = 0; i < List.Length; i++) { Mail.CC.Add(new MailAddress(List[i].ToString())); } Mail.Subject = subject; Mail.Body = content; //添加附件 string[] AttachmentList = attachmentlist.Split(';'); for (int i = 0; i < AttachmentList.Length; i++) { Attachment att = new Attachment(AttachmentList[i].ToString()); ContentDisposition disposition = att.ContentDisposition; disposition.CreationDate = System.IO.File.GetCreationTime(AttachmentList[i].ToString()); disposition.ModificationDate = System.IO.File.GetLastWriteTime(AttachmentList[i].ToString()); disposition.ReadDate = System.IO.File.GetLastAccessTime(AttachmentList[i].ToString()); disposition.DispositionType = DispositionTypeNames.Attachment; Mail.Attachments.Add(att); } if (isproxy) { //还需要重写 } SmtpClient client = new SmtpClient(SendServer); client.UseDefaultCredentials = false; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.Credentials = new System.Net.NetworkCredential(From, Pwd); client.Send(Mail); return true; } catch (SmtpException ex) { throw new Exception("邮件发送失败\r\n" + ex.InnerException.Message); } catch (Exception ex) { throw new Exception("邮件发送失败\r\n" + ex.InnerException.Message); } } /// <summary> /// 发送邮件（自定义发件人） /// </summary> /// <param name="fromemailadd">发送箱地址</param> /// <param name="fromemailpwd">发件箱密码</param> /// <param name="toemailadd">收件人地址</param> /// <param name="cclist">抄送人地址</param> /// <param name="subject">主题</param> /// <param name="content">内容</param> /// <param name="attachmentlist">附件</param> /// <param name="isproxy">是否使用代理</param> /// <param name="msg">返回提示</param> /// <returns>返回ture or false 表示是否发送成功</returns> public static bool SendEmail(string fromemailadd, string fromemailpwd, string toemailaddresslist, string ccaddresslist, string subject, string content, string attachmentlist, bool isproxy) { try { MailMessage Mail = new MailMessage(); Mail.From = new MailAddress(fromemailadd); string[] ToAddressList = toemailaddresslist.Split(';'); for (int i = 0; i < ToAddressList.Length; i++) { Mail.To.Add(new MailAddress(ToAddressList[i].ToString())); } Mail.BodyEncoding = System.Text.Encoding.UTF8; Mail.IsBodyHtml = true; //添加抄送人 string[] List = ccaddresslist.Split(';'); for (int i = 0; i < List.Length; i++) { Mail.Bcc.Add(new MailAddress(List[i].ToString())); } Mail.Subject = subject; Mail.Body = content; //添加附件 string[] AttachmentList = attachmentlist.Split(';'); for (int i = 0; i < AttachmentList.Length; i++) { Attachment Attachment = new Attachment(AttachmentList[i].ToString()); ContentDisposition disposition = Attachment.ContentDisposition; disposition.CreationDate = System.IO.File.GetCreationTime(AttachmentList[i].ToString()); disposition.ModificationDate = System.IO.File.GetLastWriteTime(AttachmentList[i].ToString()); disposition.ReadDate = System.IO.File.GetLastAccessTime(AttachmentList[i].ToString()); disposition.DispositionType = DispositionTypeNames.Attachment; Mail.Attachments.Add(Attachment); } if (isproxy) { //还需要重写 } SmtpClient client = new SmtpClient(SendServer); client.UseDefaultCredentials = false; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.Credentials = new System.Net.NetworkCredential(fromemailadd, fromemailpwd); client.Send(Mail); return true; } catch (SmtpException ex) { throw new Exception("邮件发送失败\r\n" + ex.InnerException.Message); } catch (Exception ex) { throw new Exception("邮件发送失败\r\n" + ex.InnerException.Message); } } #endregion #region ReceiveEmail /// <summary> /// 下载服务器上的邮件到本地 /// 返回Dictionary<int, OpenPop.Mime.Message> EmaiMessagelList字典 /// </summary> public static Dictionary<string, Message> DownLoadEmail() { try { Pop3Client client = new Pop3Client(); if (client.Connected) { client.Disconnect(); } client.Connect(ReceiveServer, 110, false); client.Authenticate(ReceiveAdd, ReceivePwd); EmailCount = client.GetMessageCount(); EmaiMessagelList.Clear(); for (int i = 1; i <= EmailCount; i++) { Message message = client.GetMessage(i); EmaiMessagelList.Add(client.GetMessageUid(i), message); } isDownLoad = true; return EmaiMessagelList; } catch (Exception ex) { EmaiMessagelList.Clear(); throw new Exception("下载邮件失败\r\n" + ex.InnerException.Message); } } /// <summary> /// 获取邮件主题树结构 /// </summary> /// <param name="EmailMessageList">下载的邮件</param> /// <returns></returns> public static System.Windows.Forms.TreeView GetEmailSubjectTree(Dictionary<string, Message> EmailMessageList) { System.Windows.Forms.TreeView SubjectTreeView = new System.Windows.Forms.TreeView(); if (!isDownLoad) { throw new Exception("程序：邮件未下载"); } { foreach (var dic in EmailMessageList) { System.Windows.Forms.TreeNode Node = new System.Windows.Forms.TreeNode(dic.Value.Headers.Subject); Node.Name = dic.Key.ToString(); SubjectTreeView.Nodes.Add(Node); } return SubjectTreeView; } } /// <summary> /// 获取下载邮件的主题列表 /// </summary> /// <param name="EmailMessageList">下载的邮件</param> /// <returns></returns> public static string[] GetEmailSubjectList(Dictionary<string, Message> EmailMessageList) { string[] EmailSubjectList = new string[EmailMessageList.Count]; if (!isDownLoad) { throw new Exception("程序：邮件未下载"); } { foreach (var dic in EmailMessageList) { int i = 0; EmailSubjectList[i] = dic.Value.Headers.Subject; i++; } return EmailSubjectList; } } /// <summary> /// 获取一个包含邮件信息的Message /// </summary> /// <param name="Number">邮件编号</param> /// <returns></returns> public static Message GetEmailMessage(string uid) { if (!isDownLoad) { throw new Exception("程序：邮件未下载"); } { return EmaiMessagelList[uid]; } } public static Email GetEmailInfo(Message message) { Email email = new Email(); email.Subject = message.Headers.Subject; email.Fromaddress = message.Headers.Sender.ToString(); string Toaddress = ""; for (int i = 0; i < message.Headers.To.Count; i++) { Toaddress += message.Headers.To[i].Address + ";"; } email.Toaddress = Toaddress.Substring(0, Toaddress.Length - 1); string Ccaddress = ""; for (int i = 0; i < message.Headers.Cc.Count; i++) { Ccaddress += message.Headers.Cc[i].Address + ";"; } email.Ccadress = Ccaddress.Substring(0, Ccaddress.Length - 1); string Bccaddress = ""; for (int i = 0; i < message.Headers.Bcc.Count; i++) { Bccaddress += message.Headers.Bcc[i].Address + ";"; } email.Bccadress = Bccaddress.Substring(0, Bccaddress.Length - 1); email.Senddate = message.Headers.DateSent.ToLocalTime().ToString("yyyy-MM-dd hh:mm:ss"); if (!message.MessagePart.IsMultiPart) { email.Content = Encoding.Default.GetString(message.MessagePart.Body); } else { for (int i = 0; i < message.MessagePart.MessageParts.Count; i++) { email.Content += Encoding.Default.GetString(message.MessagePart.MessageParts[i].Body); } } if (!message.MessagePart.IsAttachment) { email.Attachments = null; } else { } return email; } /// <summary> /// 获取邮件数量 /// </summary> /// <returns></returns> public static int GetMessagesCount() { Pop3Client client = new Pop3Client(); if (client.Connected) { client.Disconnect(); } client.Connect(ReceiveServer, 110, false); client.Authenticate(ReceiveAdd, ReceivePwd); return client.GetMessageCount(); } #endregion } public class Email { private string subject; private string fromaddress; private string toaddress; private string ccadress; private string bccadress; private string senddate; private string content; public string Subject { get { return subject; } set { subject = value; } } public string Fromaddress { get { return fromaddress; } set { fromaddress = value; } } public string Toaddress { get { return toaddress; } set { toaddress = value; } } public string Ccadress { get { return ccadress; } set { ccadress = value; } } public string Bccadress { get { return bccadress; } set { bccadress = value; } } public string Senddate { get { return senddate; } set { senddate = value; } } public string Content { get { return content; } set { content = value; } } public Dictionary<string, string> Attachments = new Dictionary<string, string>(); } class BuilderTrees { public BuilderTrees() { } public static System.Windows.Forms.TreeNode BuildTree(Message message, int i) { System.Windows.Forms.TreeNode child = new System.Windows.Forms.TreeNode(); System.Windows.Forms.TreeNode TopNode = new System.Windows.Forms.TreeNode(message.Headers.Subject, new[] { child }); TopNode.Tag = i; return TopNode; } private System.Windows.Forms.TreeNode BuildChild(MessagePart messagepart) { System.Windows.Forms.TreeNode[] child = new System.Windows.Forms.TreeNode[0]; if (messagepart.IsMultiPart) { child = new System.Windows.Forms.TreeNode[messagepart.MessageParts.Count]; for (int i = 0; i < messagepart.MessageParts.Count; i++) { child[i] = BuildChild(messagepart.MessageParts[i]); } } System.Windows.Forms.TreeNode currentNode = new System.Windows.Forms.TreeNode(messagepart.ContentType.MediaType, child); currentNode.Tag = messagepart; return currentNode; } } }
ExcelHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System.Data; using System.Windows.Forms; using System.IO; using System.Data.OleDb; using System.Collections; using NPOI.HSSF.Util; using NPOI.XSSF.UserModel; /// ///使用该类 需要添加 NOPI.dll 的引用 ///版本为 2.0.0.0 namespace Common { public static class ExcelHelper { #region 导出 /// <summary> /// 导出一个Datatable的数据到Excel文件 /// </summary> /// <param name="dt">需要导出的DataTable</param> public static void ExportDataTableToExcel(DataTable dt) { ExportDataTableToExcel(dt, ""); } /// <summary> /// 导出一个Datatable的数据到Excel文件 /// </summary> /// <param name="dt">需要导出的DataTable</param> /// <param name="sheetname">需要保存的Sheet名称</param> public static void ExportDataTableToExcel(DataTable dt, string sheetname) { try { HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = null; if (string.IsNullOrEmpty(sheetname)) { sheet = wk.CreateSheet("Sheet1"); } else { sheet = wk.CreateSheet(sheetname); } int RowCount = dt.Rows.Count; int ColumnCount = dt.Columns.Count; IRow row = sheet.CreateRow(0); for (int j = 0; j < ColumnCount; j++) //列标题 { sheet.SetColumnWidth(j, 20 * 256); ICell cell = row.CreateCell(j); cell.SetCellType(CellType.String); cell.SetCellValue(dt.Columns[j].ColumnName); } for (int i = 0; i < RowCount; i++) { row = sheet.CreateRow(i + 1); for (int j = 0; j < ColumnCount; j++) { sheet.SetColumnWidth(j, 20 * 256); ICell cell = row.CreateCell(j); cell.SetCellType(CellType.String); cell.SetCellValue(dt.Rows[i][j].ToString()); } } SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存为Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！ { wk.Write(fs); //向打开的这个xls文件中写入Sheet1表并保存。 MessageBox.Show("导出成功！"); } } } catch (Exception ex) { throw new Exception("导出失败\r\n" + ex.InnerException.Message); } } /// <summary> /// 导出一个DataGridView的数据到Excel文件 /// </summary> /// <param name="Gridview">绑定了数据源的DataGridView控件</param> public static void ExportDataGridViewToExcel(DataGridView Gridview) { ExportDataGridViewToExcel(Gridview, ""); } /// <summary> /// 导出一个DataGridView的数据到Excel文件 /// </summary> /// <param name="Gridview">绑定了数据源的DataGridView控件</param> /// <param name="sheetname">需要保存的Sheet名称</param> public static void ExportDataGridViewToExcel(DataGridView Gridview, string sheetname) { try { HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = null; if (string.IsNullOrEmpty(sheetname)) { sheet = wk.CreateSheet("Sheet1"); } else { sheet = wk.CreateSheet(sheetname); } int RowCount = Gridview.RowCount; int ColumnCount = Gridview.ColumnCount; IRow row = sheet.CreateRow(0); for (int j = 0; j < ColumnCount; j++) //列标题 { sheet.SetColumnWidth(j, 20 * 256); ICell cell = row.CreateCell(j); cell.SetCellType(CellType.String); cell.SetCellValue(Gridview.Columns[j].HeaderText); cell.CellStyle = GetCellStyle(wk, Styles.ColumnHead); } for (int i = 0; i < RowCount; i++) { row = sheet.CreateRow(i + 1); for (int j = 0; j < ColumnCount; j++) { sheet.SetColumnWidth(j, 20 * 256); ICell cell = row.CreateCell(j); cell.SetCellType(CellType.String); cell.SetCellValue(Gridview.Rows[i].Cells[j].Value.ToString()); } } SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存为Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！ { wk.Write(fs); //向打开的这个xls文件中写入Sheet1表并保存。 MessageBox.Show("导出成功！"); } } } catch (Exception ex) { throw new Exception("导出失败\r\n" + ex.InnerException.Message); } } #endregion #region 读取 /// <summary> /// 读取一个Excel文件（只读取该文件的第一个Sheet的数据,不指定读取的列范围） /// </summary> /// <returns>返回一个包含Excel文件数据的DataTable</returns> public static DataTable ReadExcelFirstSheetToDataTable() { return ReadExcelFirstSheetToDataTable("", ""); } /// <summary> /// 读取一个Excel文件（只读取该文件的第一个Sheet的数据，指定从startcolumnname列开始，endcolumnname列结束的范围内的数据） /// </summary> /// <param name="startcolumnname">开始列名称</param> /// <param name="endcolumnname">结束列名称</param> /// <returns>返回一个包含Excel文件数据的DataTable</returns> public static DataTable ReadExcelFirstSheetToDataTable(string startcolumnname, string endcolumnname) { DataTable dt = new DataTable(); OpenFileDialog OpenDialog = new OpenFileDialog(); OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx"; OpenDialog.FilterIndex = 0; OpenDialog.RestoreDirectory = true; OpenDialog.Title = "读取Excel文件"; OleDbConnection con; OleDbDataAdapter da; DataSet ds = new DataSet(); if (OpenDialog.ShowDialog() == DialogResult.OK) { if (string.IsNullOrEmpty(OpenDialog.FileName)) { MessageBox.Show("请选择Excel文件！"); return dt; } else { try { string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.')); string connStr = ""; switch (filename) { case ".xls": { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } case ".xlsx": { connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; } default: { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } } string sql = ""; if (!string.IsNullOrEmpty(startcolumnname) && !string.IsNullOrEmpty(endcolumnname)) { sql = "SELECT * FROM [" + GetExcelFirstTableName(OpenDialog.FileName, connStr)[0].ToString() + startcolumnname + ":" + endcolumnname + "] "; } else { sql = "SELECT * FROM [" + GetExcelFirstTableName(OpenDialog.FileName, connStr)[0].ToString() + "] "; } con = new OleDbConnection(connStr); con.Open(); da = new OleDbDataAdapter(sql, con); da.Fill(ds); if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("选择的文件没数据！"); return dt; } else { dt = ds.Tables[0]; } return dt; } catch (Exception ex) { throw new Exception("读取Excel文件失败\r\n" + ex.InnerException.Message); } } } return dt; } /// <summary> /// 读取一个Excel文件（读取该文件的所有Sheet的数据，但是该文件的所有Sheet数据格式必须一致,不限制列范围） /// </summary> /// <returns>返回一个包含Excel文件数据的DataTable</returns> public static DataTable ReadMoreSameExcelToDataTable() { return ReadMoreSameExcelToDataTable("", ""); } /// <summary> /// 读取一个Excel文件（读取该文件的所有Sheet的数据，但是该文件的所有Sheet数据格式必须一致，限制列范围从startcolumnname到endcolumnname） /// </summary> /// <param name="startcolumnname">开始列名称</param> /// <param name="endcolumnname">结束列名称</param> /// <returns>返回一个包含Excel文件数据的DataTable</returns> public static DataTable ReadMoreSameExcelToDataTable(string startcolumnname, string endcolumnname) { DataTable dt = new DataTable(); OpenFileDialog OpenDialog = new OpenFileDialog(); OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx"; OpenDialog.FilterIndex = 0; OpenDialog.RestoreDirectory = true; OpenDialog.Title = "读取Excel文件"; OleDbConnection con; OleDbDataAdapter da; DataSet ds = new DataSet(); if (OpenDialog.ShowDialog() == DialogResult.OK) { if (string.IsNullOrEmpty(OpenDialog.FileName)) { MessageBox.Show("请选择Excel文件！"); return dt; } else { try { string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.')); string connStr = ""; switch (filename) { case ".xls": { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } case ".xlsx": { connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; } default: { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } } ArrayList sqllist = new ArrayList(); ArrayList tablelist = new ArrayList(); tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr); if (!string.IsNullOrEmpty(startcolumnname) && !string.IsNullOrEmpty(endcolumnname)) { for (int i = 0; i < tablelist.Count; i++) { sqllist.Add("SELECT * FROM [" + tablelist[i] + startcolumnname + ":" + endcolumnname + "]"); } } else { for (int i = 0; i < tablelist.Count; i++) { sqllist.Add("SELECT * FROM [" + tablelist[i] + "]"); } } con = new OleDbConnection(connStr); con.Open(); for (int i = 0; i < sqllist.Count; i++) { da = new OleDbDataAdapter(sqllist[i].ToString(), con); da.Fill(ds); } if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("选择的文件没数据！"); return dt; } else { dt = ds.Tables[0]; } return dt; } catch (Exception ex) { throw new Exception("读取Excel文件失败\r\n" + ex.InnerException.Message); } } } return dt; } /// <summary> /// 读取一个Excel文件（读取该文件的所有Sheet的数据，该文件的Sheet数据格式可以一致） /// </summary> /// <returns>返回一个包含Excel文件数据的DataSet</returns> public static DataSet ReadMoreNotSameExcelToDataTable() { DataTable dt = new DataTable(); OpenFileDialog OpenDialog = new OpenFileDialog(); OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx"; OpenDialog.FilterIndex = 0; OpenDialog.RestoreDirectory = true; OpenDialog.Title = "读取Excel文件"; OleDbConnection con; OleDbDataAdapter da; DataSet ds = new DataSet(); if (OpenDialog.ShowDialog() == DialogResult.OK) { if (string.IsNullOrEmpty(OpenDialog.FileName)) { MessageBox.Show("请选择Excel文件！"); return ds; } else { try { string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.')); string connStr = ""; switch (filename) { case ".xls": { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } case ".xlsx": { connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; break; } default: { connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; break; } } ArrayList sqllist = new ArrayList(); ArrayList tablelist = new ArrayList(); tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr); for (int i = 0; i < tablelist.Count; i++) { sqllist.Add("SELECT * FROM [" + tablelist[i] + "]"); } con = new OleDbConnection(connStr); con.Open(); for (int i = 0; i < sqllist.Count; i++) { da = new OleDbDataAdapter(sqllist[i].ToString(), con); da.Fill(ds, "Table" + i); } bool isHave = false; for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count != 0) { isHave = true; } } if (!isHave) { MessageBox.Show("选择的文件没数据！"); return ds; } return ds; } catch (Exception ex) { throw new Exception("读取Excel文件失败\r\n" + ex.InnerException.Message); } } } return ds; } /// <summary> /// 获取第一个Sheet的名称 /// </summary> /// <param name="excelFileName">Excel文件名称</param> /// <param name="strExtension">读取Excel文件数据的数据连接</param> /// <returns></returns> private static ArrayList GetExcelFirstTableName(string excelFileName, string strExtension) { ArrayList tablenamelist = new ArrayList(); try { if (File.Exists(excelFileName)) { using (OleDbConnection conn = new OleDbConnection(strExtension)) { conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); tablenamelist.Add(dt.Rows[0][2].ToString().Trim()); return tablenamelist; } } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } return tablenamelist; } /// <summary> /// 获取所有Sheet的名称 /// </summary> /// <param name="excelFileName">Excel文件名称</param> /// <param name="strExtension">读取Excel文件数据的数据连接</param> /// <returns></returns> private static ArrayList GetExcelAllTableName(string excelFileName, string strExtension) { ArrayList tablenamelist = new ArrayList(); try { if (File.Exists(excelFileName)) { using (OleDbConnection conn = new OleDbConnection(strExtension)) { conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); for (int i = 0; i < dt.Rows.Count; i++) { tablenamelist.Add(dt.Rows[i][2].ToString().Trim()); } return tablenamelist; } } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } return tablenamelist; } #endregion #region //其他 private enum Styles { /// <summary> /// 列头 /// </summary> ColumnHead, /// <summary> /// 行头 /// </summary> RowHead, /// <summary> /// 货币 /// </summary> Money, /// <summary> /// 时间 /// </summary> DateTime } private static ICellStyle GetCellStyle(HSSFWorkbook wk, Styles style) { ICellStyle CellStyle = wk.CreateCellStyle(); CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中显示 switch (style) { case Styles.ColumnHead: CellStyle.FillPattern = FillPattern.Squares; CellStyle.FillForegroundColor = CellStyle.FillBackgroundColor = HSSFColor.Yellow.Index; break; case Styles.RowHead: CellStyle.FillPattern = FillPattern.Squares; CellStyle.FillForegroundColor = CellStyle.FillBackgroundColor = HSSFColor.Yellow.Index; break; case Styles.DateTime: IDataFormat DateFormat = wk.CreateDataFormat(); CellStyle.DataFormat = DateFormat.GetFormat("yyyy-MM-dd"); ; break; case Styles.Money: IDataFormat MoneyFormat = wk.CreateDataFormat(); CellStyle.DataFormat = MoneyFormat.GetFormat("￥#,##0"); break; } return CellStyle; } #endregion #region Write Excel Template /// <summary> /// 按照Excel模板格式导出数据 /// </summary> /// <param name="dtsources">要导出的数据DataTable</param> /// <param name="templatepath">模板路径</param> /// <param name="destpath">保存Excel文件的路径</param> /// <param name="startrowindex">开始写入的行索引 默认为1</param> /// <param name="startcolumnindex">开始写入的列索引 默认为1</param> public static void WriteExcelTemplate(DataTable dtsources, string templatepath, string destpath, int startrowindex, int startcolumnindex) { if (string.IsNullOrEmpty(templatepath)) throw new Exception("模板路径不正确"); if (string.IsNullOrEmpty(destpath)) throw new Exception("存储路径不能为空"); XSSFWorkbook wb = null; FileStream fileStream = new FileStream(templatepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); FileStream fileStream2 = null; try { wb = new XSSFWorkbook(fileStream); ISheet sheet = wb.GetSheetAt(0); IRow row1 = sheet.GetRow(1); ICell cell1 = row1.GetCell(1); string s = cell1.StringCellValue; for (int i = 0; i < dtsources.Rows.Count; i++) { if (i < 2) { IRow row = sheet.GetRow(startrowindex + i); if (row == null) { row = sheet.CreateRow(startrowindex + i); } for (int j = 0; j < dtsources.Columns.Count; j++) { ICell cell = row.GetCell(startcolumnindex + j); // if (cell == null) { cell = row.CreateCell(startcolumnindex + j); } cell.SetCellType(CellType.String); cell.SetCellValue(dtsources.Rows[i][j].ToString()); } } } fileStream2 = new FileStream(destpath, FileMode.Create, FileAccess.ReadWrite); wb.Write(fileStream2); } catch (Exception ex) { throw new Exception("\r\n" + ex.InnerException.Message); } finally { fileStream.Close(); fileStream2.Close(); } } #endregion } }
SocketHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Net; using System.Net.Sockets; using System.IO; using System.Threading; namespace Common { public class SocketHelper { public delegate void ShowMessageDelagate(string ip, string msg); /// <summary> /// ShowMessageDelagate类型的event 将委托参数传送出去 /// </summary> public event ShowMessageDelagate OnShowMessage; private int iPort = 8080; private int oPort = 8081; private string oIP = ""; private string rIP = ""; private string iMessage = ""; /// <summary> /// 通过需要发送到的IP构造SocketHelper对象 将使用默认接收端口为8080 发送端口为8081 /// </summary> /// <param name="oip">需要发送到的IP</param> public SocketHelper(string oip) { this.oIP = oip; } /// <summary> /// 通过接收端口,发送端口和需要发送到的IP构造SocketHelper对象 /// </summary> /// <param name="iport">接收端口</param> /// <param name="oport">发送端口</param> /// <param name="oip">需要发送到的IP</param> public SocketHelper(int iport, int oport, string oip) { this.iPort = iport; this.oPort = oport; this.oIP = oip; } #region 接受消息 /// <summary> /// 开始监听本机 默认监听8080端口 /// </summary> public void StartListener() { Thread ListenThread = new Thread(new ThreadStart(ReceiveMessage)); ListenThread.Name = "ListenThread"; ListenThread.IsBackground = true; ListenThread.Start(); } private void ReceiveMessage() { // IPAddress LocalIP = Dns.GetHostAddresses(Dns.GetHostName()).Where(ip => ip.AddressFamily == AddressFamily.InterNetwork).First(); TcpListener tcpListener = new TcpListener(IPAddress.Any, iPort); // tcpListener.Start(); while (true) { TcpClient ListenerClient = tcpListener.AcceptTcpClient(); NetworkStream stream = ListenerClient.GetStream(); StreamReader reader = new StreamReader(stream); iMessage = reader.ReadToEnd(); rIP = ListenerClient.Client.RemoteEndPoint.ToString(); stream.Close(); reader.Close(); ListenerClient.Close(); if (OnShowMessage != null) { OnShowMessage(rIP, iMessage); } } } #endregion #region 发送消息 /// <summary> /// 发送消息 /// </summary> /// <param name="msg">要发送的消息 默认为UTF8编码</param> /// <returns></returns> public bool SendMessage(string msg) { return SendMessage(msg, null); } /// <summary> /// 发送消息 /// </summary> /// <param name="msg">要发送的消息 </param> /// <param name="encoding">要使用的编码</param> /// <returns></returns> public bool SendMessage(string msg, Encoding encoding) { if (string.IsNullOrEmpty(oIP)) { return false; } else { TcpClient Client = null; NetworkStream stream = null; try { Client = new TcpClient(); Client.Connect(oIP, oPort); stream = Client.GetStream(); if (encoding == null) { byte[] bytes = Encoding.UTF8.GetBytes(msg); int i = 0; while (i < bytes.Length) { stream.WriteByte(bytes[i]); i++; } } else { byte[] bytes = encoding.GetBytes(msg); int i = 0; while (i < bytes.Length) { stream.WriteByte(bytes[i]); i++; } } return true; } catch (SocketException ex) { throw new Exception("发送失败\r\nSocketException:" + ex.Message); } catch (Exception ex) { throw new Exception("发送失败\r\n" + ex.Message); } finally { if (stream != null) { stream.Close(); } if (Client != null) { Client.Close(); } } } } #endregion } }
SqlHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace Common { public static class SqlHelpers { /// <summary> /// 数据库连接串 /// </summary> private static SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString()); /// <summary> /// 打开或者关闭数据库连接 /// </summary> private static void ConnectionStateChange() { if (connection.State == ConnectionState.Closed) { connection.Open(); } else { connection.Close(); } } #region SQL语句 /// <summary> /// 查询是否包含某一满足条件的记录 /// </summary> /// <param name="Querystr">SQL语句字符串</param> /// <returns>包含返回;true 不包含则返回:false</returns> public static bool IsHaveRecord(string Querystr) { return isHaveRecord(Querystr, null); } /// <summary> /// 查询是否包含某一满足条件的记录(带参数) /// </summary> /// <param name="QueryStr">SQL语句字符串</param> /// <param name="ParameterList">参数数组</param> /// <returns>包含返回;true 不包含则返回:false</returns> public static bool isHaveRecord(string QueryStr, params SqlParameter[] ParameterList) { try { SqlCommand cmd = new SqlCommand(QueryStr); ConnectionStateChange(); if (ParameterList != null) { cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; cmd.Parameters.AddRange(ParameterList); } else { cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; } return cmd.ExecuteReader().Read() == true ? true : false; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } /// <summary> /// 获取查询结果集 /// </summary> /// <param name="QueryStr">SQL语句字符串</param> /// <returns>返回一个DataTable</returns> public static DataTable ExecuteDataTable(string QueryStr) { return ExecuteDataTable(QueryStr, null); } /// <summary> /// 获取查询结果集 /// </summary> /// <param name="QueryStr">SQL语句字符串</param> /// <param name="ParameterList">参数数组</param> /// <returns>返回一个DataTable</returns> public static DataTable ExecuteDataTable(string QueryStr, params SqlParameter[] ParameterList) { try { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(); ConnectionStateChange(); if (ParameterList != null) { cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; cmd.Parameters.AddRange(ParameterList); } else { cmd.Connection = connection; cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; } SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } /// <summary> /// 执行SQL语句 /// </summary> /// <param name="QueryStr">SQL语句</param> /// <returns>受影响行数大于0返回true 否则返回 false</returns> public static bool ExecuteNonQuery(string QueryStr) { return ExecuteNonQuery(QueryStr, null); } /// <summary> /// 执行带参数的SQL语句 /// </summary> /// <param name="QueryStr">SQL语句</param> /// <param name="ParameterList">SqlParameter参数数组</param> /// <returns>受影响行数大于0返回true 否则返回 false</returns> public static bool ExecuteNonQuery(string QueryStr, params SqlParameter[] ParameterList) { try { SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; ConnectionStateChange(); if (ParameterList != null) { cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; cmd.Parameters.AddRange(ParameterList); } else { cmd.CommandType = CommandType.Text; cmd.CommandText = QueryStr; } return cmd.ExecuteNonQuery() > 0 ? true : false; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } /// <summary> /// 执行多条SQL语句 /// </summary> /// <param name="QueryStrList">SQL语句字符串数组</param> /// <returns>返回所有SQL语句执行所影响的行数综合</returns> public static bool ExecuteNonQueryNotSingle(string[] QueryStrList) { try { SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; ConnectionStateChange(); cmd.CommandType = CommandType.Text; cmd.CommandText = PrepareNotSingleSQL(QueryStrList); return cmd.ExecuteNonQuery() > 0 ? true : false; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } private static string PrepareNotSingleSQL(string[] QueryStrList) { string sql = ""; foreach (string str in QueryStrList) { sql += str + ";"; } return sql; } #endregion #region 存储过程 /// <summary> /// 查询是否包含某一满足条件的记录 /// </summary> /// <param name="Querystr">存储过程</param> /// <returns>包含返回;true 不包含则返回:false</returns> public static bool isProHaveRecord(string ProName) { return isProHaveRecord(ProName, null); } /// <summary> /// 查询是否包含某一满足条件的记录(带参数) /// </summary> /// <param name="QueryStr">存储过程</param> /// <param name="ParameterList">参数数组</param> /// <returns>包含返回;true 不包含则返回:false</returns> public static bool isProHaveRecord(string ProName, params SqlParameter[] ParameterList) { try { SqlCommand cmd = new SqlCommand(ProName); ConnectionStateChange(); if (ParameterList != null) { cmd.Connection = connection; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; cmd.Parameters.AddRange(ParameterList); } else { cmd.Connection = connection; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; } return cmd.ExecuteReader().Read() == true ? true : false; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } /// <summary> /// 获取查询结果集 /// </summary> /// <param name="QueryStr">存储过程</param> /// <returns>返回一个DataTable</returns> public static DataTable ExecuteProDataTable(string ProName) { return ExecuteDataTable(ProName, null); } /// <summary> /// 获取查询结果集 /// </summary> /// <param name="QueryStr">存储过程</param> /// <param name="ParameterList">SqlParameter参数数组</param> /// <returns>返回一个DataTable</returns> public static DataTable ExecuteProDataTable(string ProName, params SqlParameter[] ParameterList) { try { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(); ConnectionStateChange(); if (ParameterList != null) { cmd.Connection = connection; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; cmd.Parameters.AddRange(ParameterList); } else { cmd.Connection = connection; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; } SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="QueryStr">存储过程名称</param> /// <returns>受影响行数大于0返回true 否则返回 false</returns> public static bool ExecuteProNonQuery(string ProName) { return ExecuteProNonQuery(ProName, null); } /// <summary> /// 执行带参数的存储过程 /// </summary> /// <param name="QueryStr">存储过程</param> /// <param name="ParameterList">SqlParameter参数数组</param> /// <returns>受影响行数大于0返回true 否则返回 false</returns> public static bool ExecuteProNonQuery(string ProName, params SqlParameter[] ParameterList) { try { SqlCommand cmd = new SqlCommand(); cmd.Connection = connection; ConnectionStateChange(); if (ParameterList != null) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; cmd.Parameters.AddRange(ParameterList); } else { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProName; } return cmd.ExecuteNonQuery() > 0 ? true : false; } catch (Exception ex) { throw ex; } finally { ConnectionStateChange(); } } #endregion #region Insert DataTable To DB /// <summary> /// 把DataTable的数据写入数据库（DataTable的数据列的顺序必须与数据库表的列顺序相同） /// </summary> /// <param name="dt">需要写入的DataTable</param> /// <param name="DestinationTableName">被写入的数据库中的表名称</param> public static void InsertDatableToDB(DataTable dt, string DestinationTableName) { using (SqlBulkCopy sqlBC = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["conn"].ToString(), SqlBulkCopyOptions.UseInternalTransaction)) { sqlBC.BatchSize = 100; sqlBC.NotifyAfter = 1; sqlBC.DestinationTableName = DestinationTableName; for (int i = 0; i < dt.Columns.Count; i++) { sqlBC.ColumnMappings.Add(i, i);//(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } sqlBC.WriteToServer(dt); } } #endregion } }
以上代码中用到的DLL在这里下载：下载
1919
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
