EmailHelperusing System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Net; using System.Net.Mail; using System.Text; namespace Helper { /// /// Author : yenange /// Date : 2014-02-26 /// Description : 邮件发送辅助类 /// public class EmailHelper { #region [ 属性(发送Email相关) ] private string _SmtpHost = string.Empty; private int _SmtpPort = -1; private string _FromEmailAddress = string.Empty; private string _FormEmailPassword = string.Empty; /// /// smtp 服务器 /// public string SmtpHost { get { if (string.IsNullOrEmpty(_SmtpHost)) { _SmtpHost = ConfigurationManager.AppSettings["SmtpHost"]; } return _SmtpHost; } } /// /// smtp 服务器端口 默认为25 /// public int SmtpPort { get { if (_SmtpPort == -1) { if (!int.TryParse(ConfigurationManager.AppSettings["SmtpPort"], out _SmtpPort)) { _SmtpPort = 25; } } return _SmtpPort; } } /// /// 发送者 Eamil 地址 /// public string FromEmailAddress { get { if (string.IsNullOrEmpty(_FromEmailAddress)) { _FromEmailAddress = ConfigurationManager.AppSettings["FromEmailAddress"]; } return _FromEmailAddress; } } /// /// 发送者 Eamil 密码 /// public string FormEmailPassword { get { if (string.IsNullOrEmpty(_FormEmailPassword)) { _FormEmailPassword = ConfigurationManager.AppSettings["FormEmailPassword"]; } return _FormEmailPassword; } } #endregion #region [ 属性(邮件相关) ] /// /// 收件人 Email 列表，多个邮件地址之间用 半角逗号 分开 /// public string ToList { get; set; } /// /// 邮件的抄送者，支持群发，多个邮件地址之间用 半角逗号 分开 /// public string CCList { get; set; } /// /// 邮件的密送者，支持群发，多个邮件地址之间用 半角逗号 分开 /// public string BccList { get; set; } /// /// 邮件标题 /// public string Subject { get; set; } /// /// 邮件正文 /// public string Body { get; set; } private bool _IsBodyHtml = true; /// /// 邮件正文是否为Html格式 /// public bool IsBodyHtml { get { return _IsBodyHtml; } set { _IsBodyHtml = value; } } /// /// 附件列表 /// public List<Attachment> AttachmentList { get; set; } #endregion #region [ 构造函数 ] /// /// 构造函数 (body默认为html格式) /// /// 收件人列表 /// 邮件标题 /// 邮件正文 public EmailHelper(string toList, string subject, string body) { this.ToList = toList; this.Subject = subject; this.Body = body; } /// /// 构造函数 /// /// 收件人列表 /// 邮件标题 /// 邮件正文是否为Html格式 /// 邮件正文 public EmailHelper(string toList, string subject, bool isBodyHtml, string body) { this.ToList = toList; this.Subject = subject; this.IsBodyHtml = isBodyHtml; this.Body = body; } /// /// 构造函数 /// /// 收件人列表 /// 抄送人列表 /// 密送人列表 /// 邮件标题 /// 邮件正文是否为Html格式 /// 邮件正文 public EmailHelper(string toList, string ccList, string bccList, string subject, bool isBodyHtml, string body) { this.ToList = toList; this.CCList = ccList; this.BccList = bccList; this.Subject = subject; this.IsBodyHtml = isBodyHtml; this.Body = body; } /// /// 构造函数 /// /// 收件人列表 /// 抄送人列表 /// 密送人列表 /// 邮件标题 /// 邮件正文是否为Html格式 /// 邮件正文 /// 附件列表 public EmailHelper(string toList, string ccList, string bccList, string subject, bool isBodyHtml, string body, List<Attachment> attachmentList) { this.ToList = toList; this.CCList = ccList; this.BccList = bccList; this.Subject = subject; this.IsBodyHtml = isBodyHtml; this.Body = body; this.AttachmentList = attachmentList; } #endregion #region [ 发送邮件 ] /// /// 发送邮件 /// /// <returns></returns> public void Send() { SmtpClient smtp = new SmtpClient(); //实例化一个SmtpClient smtp.DeliveryMethod = SmtpDeliveryMethod.Network; //将smtp的出站方式设为 Network smtp.EnableSsl = false; //smtp服务器是否启用SSL加密 smtp.Host = this.SmtpHost; //指定 smtp 服务器地址 smtp.Port = this.SmtpPort; //指定 smtp 服务器的端口，默认是25，如果采用默认端口，可省去 smtp.UseDefaultCredentials = true; //如果你的SMTP服务器不需要身份认证，则使用下面的方式，不过，目前基本没有不需要认证的了 smtp.Credentials = new NetworkCredential(this.FromEmailAddress, this.FormEmailPassword); //如果需要认证，则用下面的方式 MailMessage mm = new MailMessage(); //实例化一个邮件类 mm.Priority = MailPriority.Normal; //邮件的优先级，分为 Low, Normal, High，通常用 Normal即可 mm.From = new MailAddress(this.FromEmailAddress, "管理员", Encoding.GetEncoding(936)); //收件人 if (!string.IsNullOrEmpty(this.ToList)) mm.To.Add(this.ToList); //抄送人 if (!string.IsNullOrEmpty(this.CCList)) mm.CC.Add(this.CCList); //密送人 if (!string.IsNullOrEmpty(this.BccList)) mm.Bcc.Add(this.BccList); mm.Subject = this.Subject; //邮件标题 mm.SubjectEncoding = Encoding.GetEncoding(936); //这里非常重要，如果你的邮件标题包含中文，这里一定要指定，否则对方收到的极有可能是乱码。 mm.IsBodyHtml = this.IsBodyHtml; //邮件正文是否是HTML格式 mm.BodyEncoding = Encoding.GetEncoding(936); //邮件正文的编码， 设置不正确， 接收者会收到乱码 mm.Body = this.Body; //邮件正文 //邮件附件 if (this.AttachmentList != null && this.AttachmentList.Count > 0) { foreach (Attachment attachment in this.AttachmentList) { mm.Attachments.Add(attachment); } } //发送邮件，如果不返回异常， 则大功告成了。 smtp.Send(mm); } #endregion } }
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Net; using System.Text; namespace Helper { public class FTPHelper { /// /// FTP请求对象 /// FtpWebRequest request = null; /// /// FTP响应对象 /// FtpWebResponse response = null; /// /// FTP服务器地址 /// public string ftpURI { get; private set; } /// /// FTP服务器IP /// public string ftpServerIP { get; private set; } /// /// FTP服务器默认目录 /// public string ftpRemotePath { get; private set; } /// /// FTP服务器登录用户名 /// public string ftpUserID { get; private set; } /// /// FTP服务器登录密码 /// public string ftpPassword { get; private set; } /// /// 初始化 /// /// FTP连接地址 /// 指定FTP连接成功后的当前目录, 如果不指定即默认为根目录 /// 用户名 /// 密码 public FTPHelper(string ftpServerIP, string ftpRemotePath, string ftpUserID, string ftpPassword) { this.ftpServerIP = ftpServerIP; this.ftpRemotePath = ftpRemotePath; this.ftpUserID = ftpUserID; this.ftpPassword = ftpPassword; this.ftpURI = "ftp://" + ftpServerIP + "/" + ftpRemotePath + "/"; } ~FTPHelper() { if (response != null) { response.Close(); response = null; } if (request != null) { request.Abort(); request = null; } } /// /// 建立FTP链接,返回响应对象 /// /// FTP地址 /// 操作命令 /// <returns></returns> private FtpWebResponse Open(Uri uri, string ftpMethod) { request = (FtpWebRequest)FtpWebRequest.Create(uri); request.Method = ftpMethod; request.UseBinary = true; request.KeepAlive = false; request.Credentials = new NetworkCredential(this.ftpUserID, this.ftpPassword); return (FtpWebResponse)request.GetResponse(); } /// /// 建立FTP链接,返回请求对象 /// /// FTP地址 /// 操作命令 private FtpWebRequest OpenRequest(Uri uri, string ftpMethod) { request = (FtpWebRequest)WebRequest.Create(uri); request.Method = ftpMethod; request.UseBinary = true; request.KeepAlive = false; request.Credentials = new NetworkCredential(this.ftpUserID, this.ftpPassword); return request; } /// /// 验证登录用户是否合法 /// /// ftp路径 /// ftp用户名 /// ftp密码 /// <returns></returns> public bool CheckFtp() { bool res = false; try { request = (FtpWebRequest)FtpWebRequest.Create(this.ftpURI); request.Method = WebRequestMethods.Ftp.ListDirectory;//发送一个请求命令 request.UseBinary = true; request.KeepAlive = false; request.Credentials = new NetworkCredential(this.ftpUserID, this.ftpPassword); FtpWebResponse ftpResponse = (FtpWebResponse)request.GetResponse();//响应一个请求 ftpResponse.Close(); res = true; } catch { res = false; } return res; } /// /// 验证登录用户是否合法 /// /// ftp路径 /// ftp用户名 /// ftp密码 /// <returns></returns> public static bool CheckFtp(string ftpAddress, string ftpUserName, string ftpUserPwd) { bool res = false; try { FtpWebRequest ftpWebRequest = (FtpWebRequest)WebRequest.Create("ftp://" + ftpAddress);//创建FtpWebRequest对象 ftpWebRequest.Credentials = new NetworkCredential(ftpUserName, ftpUserPwd);//设置FTP登陆信息 ftpWebRequest.Method = WebRequestMethods.Ftp.ListDirectory;//发送一个请求命令 FtpWebResponse ftpResponse = (FtpWebResponse)ftpWebRequest.GetResponse();//响应一个请求 ftpResponse.Close();//关闭请求 res = true; } catch { res = false; } return res; } /// /// 创建目录 /// /// 目录名 public void CreateDirectory(string remoteDirectoryName) { response = Open(new Uri(ftpURI + remoteDirectoryName), WebRequestMethods.Ftp.MakeDirectory); } /// /// 更改目录或文件名 /// /// 当前名称 /// 修改后新名称 public void ReName(string currentName, string newName) { request = OpenRequest(new Uri(ftpURI + currentName), WebRequestMethods.Ftp.Rename); request.RenameTo = newName; response = (FtpWebResponse)request.GetResponse(); } /// /// 切换当前目录 /// /// true:绝对路径 false:相对路径 public void GotoDirectory(string DirectoryName, bool IsRoot) { if (IsRoot) ftpRemotePath = DirectoryName; else ftpRemotePath += "/" + DirectoryName; ftpURI = "ftp://" + ftpServerIP + "/" + ftpRemotePath + "/"; } /// /// 删除目录(包括下面所有子目录和子文件) /// /// 要删除的带路径目录名：如web/test /* * 例：删除test目录 FTPHelper helper = new FTPHelper("x.x.x.x", "web", "user", "password"); helper.RemoveDirectory("web/test"); */ public void RemoveDirectory(string remoteDirectoryName) { GotoDirectory(remoteDirectoryName, true); var listAll = ListFilesAndDirectories(); foreach (var m in listAll) { if (m.IsDirectory) RemoveDirectory(m.Path); else { GotoDirectory(remoteDirectoryName, true); DeleteFile(m.Name); } } GotoDirectory(remoteDirectoryName, true); response = Open(new Uri(ftpURI), WebRequestMethods.Ftp.RemoveDirectory); } /// /// 删除文件 /// /// public void RemoveFile(string remoteFilePatch) { string directoryName = remoteFilePatch.Substring(0, remoteFilePatch.LastIndexOf('/')); string fileName = remoteFilePatch.Replace(directoryName, "").Substring(1); GotoDirectory(directoryName, true); var listAll = ListFilesAndDirectories(); foreach (var m in listAll) { if (m.Name == fileName) DeleteFile(m.Name); } } /// /// 删除所有文件 /// /// public void RemoveAllFile(string remoteFilePatch) { try { string directoryName = remoteFilePatch.Substring(0, remoteFilePatch.LastIndexOf('/')) + '/'; if (System.IO.Directory.Exists(directoryName))//判断文件夹是否存在 { string[] strFiles = System.IO.Directory.GetFiles(remoteFilePatch);//获取文件数组 foreach (var item in strFiles) { System.IO.File.Delete(item); } } } catch (Exception e) { System.Diagnostics.Debug.Write(e.Message.ToString()); } } /// /// 文件上传 /// /// 本地文件路径 public void Upload(string localFilePath) { FileInfo fileInf = new FileInfo(localFilePath); request = OpenRequest(new Uri(ftpURI + fileInf.Name), WebRequestMethods.Ftp.UploadFile); request.ContentLength = fileInf.Length; int buffLength = 2048; byte[] buff = new byte[buffLength]; int contentLen; using (var fs = fileInf.OpenRead()) { using (var strm = request.GetRequestStream()) { contentLen = fs.Read(buff, 0, buffLength); while (contentLen != 0) { strm.Write(buff, 0, contentLen); contentLen = fs.Read(buff, 0, buffLength); } } } } /// /// 上传图片并压缩 /// /// /// public void UploadImg(System.Drawing.Image img, string fileFullName) { MemoryStream stream = new MemoryStream(); System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(img); stream = ImageHelper.CompressImages(bmp, 30); request = OpenRequest(new Uri(ftpURI + fileFullName), WebRequestMethods.Ftp.UploadFile); request.ContentLength = stream.Length; int buffLength = 2048; byte[] buff = new byte[buffLength]; int contentLen; stream.Position = 0; using (var strm = request.GetRequestStream()) { contentLen = stream.Read(buff, 0, buffLength); while (contentLen != 0) { strm.Write(buff, 0, contentLen); contentLen = stream.Read(buff, 0, buffLength); } } } /// /// 删除文件 /// /// 要删除的文件名 public void DeleteFile(string remoteFileName) { response = Open(new Uri(ftpURI + remoteFileName), WebRequestMethods.Ftp.DeleteFile); } /// /// 获取当前目录的文件和一级子目录信息 /// /// <returns></returns> public List<FTPFileStruct> ListFilesAndDirectories() { var fileList = new List<FTPFileStruct>(); response = Open(new Uri(ftpURI), WebRequestMethods.Ftp.ListDirectoryDetails); using (var stream = response.GetResponseStream()) { using (var sr = new StreamReader(stream)) { string line = null; while ((line = sr.ReadLine()) != null) { //line的格式如下： //08-18-13 11:05PM aspnet_client //09-22-13 11:39PM 2946 Default.aspx DateTime dtDate = DateTime.ParseExact(line.Substring(0, 8), "MM-dd-yy", null); DateTime dtDateTime = DateTime.Parse(dtDate.ToString("yyyy-MM-dd") + line.Substring(8, 9)); string[] arrs = line.Split(' '); var model = new FTPFileStruct() { IsDirectory = line.IndexOf(" ") > 0 ? true : false, CreateTime = dtDateTime, Name = arrs[arrs.Length - 1], Path = ftpRemotePath + "/" + arrs[arrs.Length - 1] }; fileList.Add(model); } } } return fileList; } /// /// 列出当前目录的所有文件 /// public List<FTPFileStruct> ListFiles() { var listAll = ListFilesAndDirectories(); var listFile = listAll.Where(m => m.IsDirectory == false).ToList(); return listFile; } /// /// 列出当前目录的所有一级子目录 /// public List<FTPFileStruct> ListDirectories() { var listAll = ListFilesAndDirectories(); var listFile = listAll.Where(m => m.IsDirectory == true).ToList(); return listFile; } /// /// 判断当前目录下指定的子目录或文件是否存在 /// /// 指定的目录或文件名 public bool IsExist(string remoteName) { var list = ListFilesAndDirectories(); if (list.Count(m => m.Name == remoteName) > 0) return true; return false; } /// /// 判断当前目录下指定的一级子目录是否存在 /// /// 指定的目录名 public bool IsDirectoryExist(string remoteDirectoryName) { var listDir = ListDirectories(); if (listDir.Count(m => m.Name == remoteDirectoryName) > 0) return true; return false; } /// /// 判断当前目录下指定的子文件是否存在 /// /// 远程文件名 public bool IsFileExist(string remoteFileName) { var listFile = ListFiles(); if (listFile.Count(m => m.Name == remoteFileName) > 0) return true; return false; } /// /// 下载 /// /// 下载后的保存路径 /// 要下载的文件名 public void Download(string saveFilePath, string downloadFileName) { using (FileStream outputStream = new FileStream(saveFilePath + "\\" + downloadFileName, FileMode.Create)) { response = Open(new Uri(ftpURI + downloadFileName), WebRequestMethods.Ftp.DownloadFile); using (Stream ftpStream = response.GetResponseStream()) { long cl = response.ContentLength; int bufferSize = 2048; int readCount; byte[] buffer = new byte[bufferSize]; readCount = ftpStream.Read(buffer, 0, bufferSize); while (readCount > 0) { outputStream.Write(buffer, 0, readCount); readCount = ftpStream.Read(buffer, 0, bufferSize); } } } } /// /// 下载文件流 /// /// 要下载的文件名 /// <returns></returns> public Stream LoadStream(string downloadFileName) { response = Open(new Uri(ftpURI + downloadFileName), WebRequestMethods.Ftp.DownloadFile); using (Stream ftpStream = response.GetResponseStream()) { return ftpStream; } } /// /// 载入远程图片 /// /// 要下载的文件名 /// <returns></returns> public System.Drawing.Image LoadImg(string downloadFileName) { response = Open(new Uri(ftpURI + downloadFileName), WebRequestMethods.Ftp.DownloadFile); using (Stream ftpStream = response.GetResponseStream()) { return System.Drawing.Image.FromStream(ftpStream); } } } public class FTPFileStruct { /// /// 是否为目录 /// public bool IsDirectory { get; set; } /// /// 创建时间 /// public DateTime CreateTime { get; set; } /// /// 文件或目录名称 /// public string Name { get; set; } /// /// 路径 /// public string Path { get; set; } } }
packageInstall-Package System.Data.SQLite.EF6Config<connectionStrings> <add name="DB_HES" connectionString="Data Source=|DataDirectory|App_Data\DB_HES.db;Version=3;UseUnicode=true;characterEncoding=UTF-8;" providerName="System.Data.SQLite.EF6" /> </connectionStrings>SqliteHelperusing System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SQLite; using System.IO; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; using System.Xml; namespace HES.AddAdmin { /// /// 对SQLite操作的类 /// 引用：System.Data.SQLite.dll【版本：3.6.23.1（原始文件名：SQlite3.DLL）】 /// public class SQLiteHelper { /// /// 所有成员函数都是静态的，构造函数定义为私有 /// private SQLiteHelper() { } /// /// 连接字符串 /// public static string ConnectionString {//"Data Source=Test.db3;Pooling=true;FailIfMissing=false"; get { ////(AppSettings节点下的"SQLiteConnectionString") string text = ConfigurationManager.ConnectionStrings["SQLiteConnectionString"].ConnectionString; //string str2 = ConfigurationManager.AppSettings["IsEncrypt"]; //if (str2 == "true") //{ // text = DesEncrypt.Decrypt(text); //} return text; } } private static SQLiteConnection _Conn = null; /// /// 连接对象 /// public static SQLiteConnection Conn { get { if (_Conn == null) _Conn = new SQLiteConnection(ConnectionString); return SQLiteHelper._Conn; } set { SQLiteHelper._Conn = value; } } #region CreateCommand(commandText,SQLiteParameter[]) /// /// 创建命令 /// /// 连接 /// 语句 /// 语句参数. /// <returns>SQLite Command</returns> public static SQLiteCommand CreateCommand(string commandText, params SQLiteParameter[] commandParameters) { SQLiteCommand cmd = new SQLiteCommand(commandText, Conn); if (commandParameters.Length > 0) { foreach (SQLiteParameter parm in commandParameters) cmd.Parameters.Add(parm); } return cmd; } #endregion #region CreateParameter(parameterName,parameterType,parameterValue) /// /// 创建参数 /// /// 参数名 /// 参数类型 /// 参数值 /// <returns>返回创建的参数</returns> public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue) { SQLiteParameter parameter = new SQLiteParameter(); parameter.DbType = parameterType; parameter.ParameterName = parameterName; parameter.Value = parameterValue; return parameter; } #endregion #region ExecuteDataSet(commandText,paramList[]) /// /// 查询数据集 /// /// 连接. /// 查询语句. /// object参数列表. /// <returns></returns> public static DataSet ExecuteDataSet(string commandText, params object[] paramList) { SQLiteCommand cmd = Conn.CreateCommand(); cmd.CommandText = commandText; if (paramList != null) { AttachParameters(cmd, commandText, paramList); } DataSet ds = new DataSet(); if (Conn.State == ConnectionState.Closed) Conn.Open(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Dispose(); Conn.Close(); return ds; } #endregion #region ExecuteDataSet(SQLiteCommand) /// /// 查询数据集 /// /// SQLiteCommand对象 /// <returns>返回数据集</returns> public static DataSet ExecuteDataSet(SQLiteCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); DataSet ds = new DataSet(); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); da.Fill(ds); da.Dispose(); cmd.Connection.Close(); cmd.Dispose(); return ds; } #endregion #region ExecuteDataSet(SQLiteTransaction,commandText,params SQLiteParameter[]) /// /// 查询数据集 /// /// SQLiteTransaction对象. /// 查询语句. /// 命令的参数列表. /// <returns>DataSet</returns> /// <remarks>必须手动执行关闭连接transaction.connection.Close</remarks> public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction"); IDbCommand cmd = transaction.Connection.CreateCommand(); cmd.CommandText = commandText; foreach (SQLiteParameter parm in commandParameters) { cmd.Parameters.Add(parm); } if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataSet((SQLiteCommand)cmd); return ds; } #endregion #region ExecuteDataSet(SQLiteTransaction,commandText,object[] commandParameters) /// /// 查询数据集 /// /// SQLiteTransaction对象 /// 查询语句. /// 命令参数列表 /// <returns>返回数据集</returns> /// <remarks>必须手动执行关闭连接transaction.connection.Close</remarks> public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, object[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction"); IDbCommand cmd = transaction.Connection.CreateCommand(); cmd.CommandText = commandText; AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters); if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); DataSet ds = ExecuteDataSet((SQLiteCommand)cmd); return ds; } #endregion #region UpdateDataset(insertCommand,deleteCommand,updateCommand,dataSet,tableName) /// /// 更新数据集中数据到数据库 /// /// insert语句 /// delete语句 /// update语句 /// 要更新的DataSet /// 数据集中要更新的table名 public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName) { if (insertCommand == null) throw new ArgumentNullException("insertCommand"); if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); if (updateCommand == null) throw new ArgumentNullException("updateCommand"); if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); // Create a SQLiteDataAdapter, and dispose of it after we are done using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter()) { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update(dataSet, tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } } #endregion #region ExecuteReader(SQLiteCommand,commandText, object[] paramList) /// /// ExecuteReader方法 /// /// 查询命令 /// 含有类似@colume参数的sql语句 /// 语句参数列表 /// <returns>IDataReader</returns> public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList) { if (cmd.Connection == null) throw new ArgumentException("没有为命令指定活动的连接.", "cmd"); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return rdr; } #endregion #region ExecuteNonQuery(commandText,paramList) /// /// 执行ExecuteNonQuery方法 /// /// 连接 /// 语句 /// 参数 /// <returns></returns> public static int ExecuteNonQuery(string commandText, params object[] paramList) { SQLiteCommand cmd = Conn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (Conn.State == ConnectionState.Closed) Conn.Open(); int result = cmd.ExecuteNonQuery(); cmd.Dispose(); Conn.Close(); return result; } #endregion #region ExecuteNonQuery(SQLiteTransaction,commandText,paramList) /// /// 执行ExecuteNonQuery方法,带事务 /// /// 之前创建好的SQLiteTransaction对象 /// 语句. /// 参数. /// <returns>返回影响的行数</returns> /// <remarks> /// 定义事务 DbTransaction trans = conn.BeginTransaction(); /// 或者：SQLiteTransaction trans = Conn.BeginTransaction(); /// 操作代码示例： /// try ///{ /// // 连续操作记录 /// for (int i = 0; i < 1000; i++) /// { /// ExecuteNonQuery(trans,commandText,[] paramList); /// } /// trans.Commit(); ///} ///catch ///{ /// trans.Rollback(); /// throw; ///} ///trans.Connection.Close();//关闭事务连接 ///transaction.Dispose();//释放事务对象 /// </remarks> public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList) { if (transaction == null) throw new ArgumentNullException("transaction is null"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,please provide an open transaction.", "transaction"); using (IDbCommand cmd = transaction.Connection.CreateCommand()) { cmd.CommandText = commandText; AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList); if (transaction.Connection.State == ConnectionState.Closed) transaction.Connection.Open(); int result = cmd.ExecuteNonQuery(); return result; } } #endregion #region ExecuteNonQuery(IDbCommand) /// /// 执行ExecuteNonQuery方法 /// /// 创建好的命令. /// <returns></returns> public static int ExecuteNonQuery(IDbCommand cmd) { if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open(); int result = cmd.ExecuteNonQuery(); cmd.Connection.Close(); cmd.Dispose(); return result; } #endregion #region ExecuteScalar(commandText,paramList) /// /// 执行ExecuteScalar /// /// 语句s /// 参数 /// <returns></returns> public static object ExecuteScalar(string commandText, params object[] paramList) { SQLiteConnection cn = new SQLiteConnection(ConnectionString); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = commandText; AttachParameters(cmd, commandText, paramList); if (cn.State == ConnectionState.Closed) cn.Open(); object result = cmd.ExecuteScalar(); cmd.Dispose(); cn.Close(); return result; } #endregion #region ExecuteXmlReader(IDbCommand) /// /// ExecuteXmlReader返回xml格式 /// /// 语句 /// <returns>返回XmlTextReader对象</returns> public static XmlReader ExecuteXmlReader(IDbCommand command) { // open the connection if necessary, but make sure we // know to close it when we�1�7re done. if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } // get a data adapter SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command); DataSet ds = new DataSet(); // fill the data set, and return the schema information da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.Fill(ds); // convert our dataset to XML StringReader stream = new StringReader(ds.GetXml()); command.Connection.Close(); // convert our stream of text to an XmlReader return new XmlTextReader(stream); } #endregion #region AttachParameters(SQLiteCommand,commandText,object[] paramList) /// /// 增加参数到命令（自动判断类型） /// /// 命令语句 /// object参数列表 /// <returns>返回SQLiteParameterCollection参数列表</returns> /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks> private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList) { if (paramList == null || paramList.Length == 0) return null; SQLiteParameterCollection coll = cmd.Parameters; string parmString = commandText.Substring(commandText.IndexOf("@")); // pre-process the string so always at least 1 space after a comma. parmString = parmString.Replace(",", " ,"); // get the named parameters into a match collection string pattern = @"(@)\S*(.*?)\b"; Regex ex = new Regex(pattern, RegexOptions.IgnoreCase); MatchCollection mc = ex.Matches(parmString); string[] paramNames = new string[mc.Count]; int i = 0; foreach (Match m in mc) { paramNames[i] = m.Value; i++; } // now let's type the parameters int j = 0; Type t = null; foreach (object o in paramList) { t = o.GetType(); SQLiteParameter parm = new SQLiteParameter(); switch (t.ToString()) { case ("DBNull"): case ("Char"): case ("SByte"): case ("UInt16"): case ("UInt32"): case ("UInt64"): throw new SystemException("Invalid data type"); case ("System.String"): parm.DbType = DbType.String; parm.ParameterName = paramNames[j]; parm.Value = (string)paramList[j]; coll.Add(parm); break; case ("System.Byte[]"): parm.DbType = DbType.Binary; parm.ParameterName = paramNames[j]; parm.Value = (byte[])paramList[j]; coll.Add(parm); break; case ("System.Int32"): parm.DbType = DbType.Int32; parm.ParameterName = paramNames[j]; parm.Value = (int)paramList[j]; coll.Add(parm); break; case ("System.Boolean"): parm.DbType = DbType.Boolean; parm.ParameterName = paramNames[j]; parm.Value = (bool)paramList[j]; coll.Add(parm); break; case ("System.DateTime"): parm.DbType = DbType.DateTime; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDateTime(paramList[j]); coll.Add(parm); break; case ("System.Double"): parm.DbType = DbType.Double; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDouble(paramList[j]); coll.Add(parm); break; case ("System.Decimal"): parm.DbType = DbType.Decimal; parm.ParameterName = paramNames[j]; parm.Value = Convert.ToDecimal(paramList[j]); break; case ("System.Guid"): parm.DbType = DbType.Guid; parm.ParameterName = paramNames[j]; parm.Value = (System.Guid)(paramList[j]); break; case ("System.Object"): parm.DbType = DbType.Object; parm.ParameterName = paramNames[j]; parm.Value = paramList[j]; coll.Add(parm); break; default: throw new SystemException("Value is of unknown data type"); } // end switch j++; } return coll; } #endregion #region ExecuteNonQueryTypedParams(IDbCommand, DataRow) /// /// Executes non query typed params from a DataRow /// /// Command. /// Data row. /// <returns>Integer result code</returns> public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow) { int retVal = 0; // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Set the parameters values AssignParameterValues(command.Parameters, dataRow); retVal = ExecuteNonQuery(command); } else { retVal = ExecuteNonQuery(command); } return retVal; } #endregion #region AssignParameterValues /// /// This method assigns dataRow column values to an IDataParameterCollection /// /// The IDataParameterCollection to be assigned values /// The dataRow used to hold the command's parameter values /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception> protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow) { if (commandParameters == null || dataRow == null) { // Do nothing if we get no data return; } DataColumnCollection columns = dataRow.Table.Columns; int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) { // Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException(string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName]; else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } #endregion #region AssignParameterValues /// /// This method assigns dataRow column values to an array of IDataParameters /// /// Array of IDataParameters to be assigned values /// The dataRow used to hold the stored procedure's parameter values /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception> protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow) { if ((commandParameters == null) || (dataRow == null)) { // Do nothing if we get no data return; } DataColumnCollection columns = dataRow.Table.Columns; int i = 0; // Set the parameters values foreach (IDataParameter commandParameter in commandParameters) { // Check the parameter name if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new InvalidOperationException(string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", i, commandParameter.ParameterName)); if (columns.Contains(commandParameter.ParameterName)) commandParameter.Value = dataRow[commandParameter.ParameterName]; else if (columns.Contains(commandParameter.ParameterName.Substring(1))) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } #endregion #region AssignParameterValues /// /// This method assigns an array of values to an array of IDataParameters /// /// Array of IDataParameters to be assigned values /// Array of objects holding the values to be assigned /// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception> protected void AssignParameterValues(IDataParameter[] commandParameters, params object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { // Do nothing if we get no data return; } // We must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } // Iterate through the IDataParameters, assigning the values from the corresponding position in the // value array for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++) { if (commandParameters[i].Direction != ParameterDirection.ReturnValue) { // If the current array value derives from IDataParameter, then assign its Value property if (parameterValues[k] is IDataParameter) { IDataParameter paramInstance; paramInstance = (IDataParameter)parameterValues[k]; if (paramInstance.Direction == ParameterDirection.ReturnValue) { paramInstance = (IDataParameter)parameterValues[++k]; } if (paramInstance.Value == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = paramInstance.Value; } } else if (parameterValues[k] == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = parameterValues[k]; } k++; } } } #endregion } }
using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace Helper { /// /// 查询语句帮助类 /// public static class LinqHelper { /// /// 动态排序 /// /// <typeparam name="T">实体类型</typeparam> /// 数据源 /// 排序字段 /// 排序方式 /// <returns></returns> public static IQueryable<T> DataSorting<T>(IQueryable<T> source, string sortExpression, string sortDirection) { string sortingDir = string.Empty; if (sortDirection.ToUpper().Trim() == "ASC") return source.OrderBy(a => typeof(T).GetProperty(sortExpression).GetValue(a, null)); else return source.OrderByDescending(a => typeof(T).GetProperty(sortExpression).GetValue(a, null)); } /// /// 动态排序旧方法 /// /// <typeparam name="T">实体类型</typeparam> /// 数据源 /// 排序字段 /// 排序方式 /// <returns></returns> public static IQueryable<T> DataSorting1<T>(IQueryable<T> source, string sortExpression, string sortDirection) { string sortingDir = string.Empty; if (sortDirection.ToUpper().Trim() == "ASC") sortingDir = "OrderBy"; else if (sortDirection.ToUpper().Trim() == "DESC") sortingDir = "OrderByDescending"; ParameterExpression param = Expression.Parameter(typeof(T), sortExpression); PropertyInfo pi = typeof(T).GetProperty(sortExpression); Type[] types = new Type[2]; types[0] = typeof(T); types[1] = pi.PropertyType; Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortExpression), param)); IQueryable<T> query = source.AsQueryable().Provider.CreateQuery<T>(expr); return query; } /// /// 分页 /// /// <typeparam name="T">实体类型</typeparam> /// 数据源 /// 分页页码从第一页开始 /// 页码 /// <returns></returns> public static IQueryable<T> DataPaging<T>(IQueryable<T> source, int pageIndex, int pageSize) { return source.Skip((pageIndex-1) * pageSize).Take(pageSize); } /// /// 排序并分页 /// /// <typeparam name="T">实体类型</typeparam> /// 数据源 /// 排序字段 /// 排序方式 /// 分页页码从第一页开始 /// 页码 /// <returns></returns> public static IQueryable<T> SortingAndPaging<T>(IQueryable<T> source, string sortExpression, string sortDirection, int pageNumber, int pageSize) { IQueryable<T> query = DataSorting<T>(source, sortExpression, sortDirection); return DataPaging(query, pageNumber, pageSize); } } }
public enum LogType { Overall, } public class LogHelper { public static string LogPath { get { return AppDomain.CurrentDomain.BaseDirectory+@"\log"; } } public enum LogLevel { Info, Error } public static void Info(string message, LogType logType = LogType.Overall) { if (string.IsNullOrEmpty(message)) return; var path = string.Format(@"\{0}\", logType.ToString()); WriteLog(path, "", message); } public static void Error(string message, LogType logType = LogType.Overall) { if (string.IsNullOrEmpty(message)) return; var path = string.Format(@"\{0}\", logType.ToString()); WriteLog(path, "Error ", message); } public static void Error(Exception e, LogType logType = LogType.Overall) { if (e == null) return; var path = string.Format(@"\{0}\", logType.ToString()); WriteLog(path, "Error ", e.Message); } private static void WriteLog(string path, string prefix, string message) { path = LogPath + path; var fileName = string.Format("{0}{1}.log", prefix, DateTime.Now.ToString("yyyyMMdd")); if (!Directory.Exists(path)) Directory.CreateDirectory(path); using (FileStream fs = new FileStream(path + fileName, FileMode.Append, FileAccess.Write, FileShare.Write, 1024, FileOptions.Asynchronous)) { byte[] buffer = System.Text.Encoding.UTF8.GetBytes(DateTime.Now.ToString("HH:mm:ss") + " " + message + "\r\n"); IAsyncResult writeResult = fs.BeginWrite(buffer, 0, buffer.Length, (asyncResult) => { var fStream = (FileStream)asyncResult.AsyncState; fStream.EndWrite(asyncResult); }, fs); fs.Close(); } } }
using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using System.Xml; using System.Xml.Serialization; namespace HesPis.Helper { public static class XmlHelper { private static string SettingPath { get { return AppDomain.CurrentDomain.BaseDirectory + @"\Config"; } } #region Fields and Properties public enum XmlType { File, String } #endregion #region Methods /// /// 创建XML文档 /// /// 根节点名称 /// 根节点的一个属性值 /// <returns></returns> public static XmlDocument CreateXmlDocument(string name, string type) { /************************************************** * .net中调用方法：写入文件中,则： *document = XmlOperate.CreateXmlDocument("sex", "sexy"); *document.Save("c:/bookstore.xml"); ************************************************/ XmlDocument xmlDocument; try { xmlDocument = new XmlDocument(); xmlDocument.LoadXml("<" + name + "/>"); var rootElement = xmlDocument.DocumentElement; rootElement?.SetAttribute("type", type); } catch (Exception exception) { throw exception; } return xmlDocument; } /// /// 删除数据 /// /// 路径 /// 节点 /// 属性名，非空时删除该节点属性值，否则删除节点值 /// <returns></returns> public static void Delete(string path, string node, string attribute) { /************************************************** * 使用示列: * XmlHelper.Delete(path, "/Node", "") * XmlHelper.Delete(path, "/Node", "Attribute") ************************************************/ try { var xmlDocument = new XmlDocument(); xmlDocument.Load(path); var selectSingleNode = xmlDocument.SelectSingleNode(node); var xmlElement = (XmlElement)selectSingleNode; if (attribute.Equals("")) selectSingleNode?.ParentNode?.RemoveChild(selectSingleNode); else xmlElement?.RemoveAttribute(attribute); xmlDocument.Save(path); } catch (Exception exception) { throw exception; } } /// /// 读取XML资源到DataSet中 /// /// XML资源，文件为路径，否则为XML字符串 /// XML资源类型 /// <returns>DataSet</returns> public static DataSet GetDataSet(string source, XmlType xmlType) { try { var dataSet = new DataSet(); if (xmlType == XmlType.File) { dataSet.ReadXml(source); } else { var xmlDocument = new XmlDocument(); xmlDocument.LoadXml(source); var xmlNodeReader = new XmlNodeReader(xmlDocument); dataSet.ReadXml(xmlNodeReader); } return dataSet; } catch (Exception exception) { throw exception; } } /// /// 获得xml文件中指定节点的节点数据 /// /// <returns></returns> public static string GetNodeInfoByNodeName(string path, string nodeName) { try { var xmlString = ""; var xmlDocument = new XmlDocument(); xmlDocument.Load(path); var documentElementRoot = xmlDocument.DocumentElement; var selectSingleNode = documentElementRoot?.SelectSingleNode("//" + nodeName); if (selectSingleNode != null) xmlString = selectSingleNode.InnerText; return xmlString; } catch (Exception exception) { throw exception; } } /// /// 读取XML资源中的指定节点内容 /// /// XML资源 /// XML资源类型：文件，字符串 /// 节点名称 /// <returns>节点内容</returns> public static string GetNodeValue(string source, XmlType xmlType, string nodeName) { var xmlDocument = new XmlDocument(); if (xmlType == XmlType.File) xmlDocument.Load(source); else xmlDocument.LoadXml(source); var documentElement = xmlDocument.DocumentElement; var selectSingleNode = documentElement?.SelectSingleNode("//" + nodeName); return selectSingleNode?.InnerText; } /// /// 读取XML资源中的指定节点属性的内容 /// /// XML资源 /// XML资源类型：文件，字符串 /// 属性节点名称 /// /// <returns>节点内容</returns> public static string GetNodeAttributeValue(string source, XmlType xmlType, string nodeName, string attributeString) { var xmlDocument = new XmlDocument(); if (xmlType == XmlType.File) xmlDocument.Load(source); else xmlDocument.LoadXml(source); var documentElement = xmlDocument.DocumentElement; var selectSingleNode = (XmlElement)documentElement?.SelectSingleNode("//" + nodeName); //if (selectSingleNode != null) return selectSingleNode?.GetAttribute(attributeString); } /// /// 读取XML资源中的指定节点内容 /// /// XML资源 /// 节点名称 /// <returns>节点内容</returns> public static string GetNodeValue(string source, string nodeName) { if (source == null || nodeName == null || source == "" || nodeName == "" || source.Length < nodeName.Length * 2) return null; var start = source.IndexOf("<" + nodeName + ">", StringComparison.Ordinal) + nodeName.Length + 2; var end = source.IndexOf("</" + nodeName + ">", StringComparison.Ordinal); if (start == -1 || end == -1) return null; return start >= end ? null : source.Substring(start, end - start); } /// /// 读取XML资源到DataTable中 /// /// XML资源，文件为路径，否则为XML字符串 /// XML资源类型：文件，字符串 /// 表名称 /// <returns>DataTable</returns> public static DataTable GetTable(string source, XmlType xmlType, string tableName) { try { var dataSet = new DataSet(); if (xmlType == XmlType.File) { dataSet.ReadXml(source); } else { var xmlDocument = new XmlDocument(); xmlDocument.LoadXml(source); var xmlNodeReader = new XmlNodeReader(xmlDocument); dataSet.ReadXml(xmlNodeReader); } return dataSet.Tables[tableName]; } catch (Exception exception) { throw exception; } } /// /// 读取XML资源中指定的DataTable的指定行指定列的值 /// /// XML资源 /// XML资源类型：文件，字符串 /// 表名 /// 行号 /// 列名 /// <returns>值，不存在时返回Null</returns> public static object GetTableCell(string source, XmlType xmlType, string tableName, int rowIndex, string colName) { try { var dataSet = new DataSet(); if (xmlType == XmlType.File) { dataSet.ReadXml(source); } else { var xmlDocument = new XmlDocument(); xmlDocument.LoadXml(source); var xmlNodeReader = new XmlNodeReader(xmlDocument); dataSet.ReadXml(xmlNodeReader); } return dataSet.Tables[tableName].Rows[rowIndex][colName]; } catch (Exception exception) { throw exception; } } /// /// 读取XML资源中指定的DataTable的指定行指定列的值 /// /// XML资源 /// XML资源类型：文件，字符串 /// 表名 /// 行号 /// 列号 /// <returns>值，不存在时返回Null</returns> public static object GetTableCell(string source, XmlType xmlType, string tableName, int rowIndex, int colIndex) { try { var dataSet = new DataSet(); if (xmlType == XmlType.File) { dataSet.ReadXml(source); } else { var xmlDocument = new XmlDocument(); xmlDocument.LoadXml(source); var xmlNodeReader = new XmlNodeReader(xmlDocument); dataSet.ReadXml(xmlNodeReader); } return dataSet.Tables[tableName].Rows[rowIndex][colIndex]; } catch (Exception exception) { throw exception; } } /// /// 获取一个字符串xml文档中的dataSet /// /// 含有xml信息的字符串 /// public static void GetXmlValueDataSet(string xmlString, ref DataSet dataSet) { try { var xmlDocument = new XmlDocument(); xmlDocument.LoadXml(xmlString); var xmlNodeReader = new XmlNodeReader(xmlDocument); dataSet.ReadXml(xmlNodeReader); xmlNodeReader.Close(); } catch (Exception exception) { throw exception; } } /// /// 插入数据 /// /// 路径 /// 节点 /// 元素名，非空时插入新元素，否则在该元素中插入属性 /// 属性名，非空时插入该元素属性值，否则插入元素值 /// 值 /// <returns></returns> public static void Insert(string path, string node, string element, string attribute, string value) { /************************************************** * 使用示列: * XmlHelper.Insert(path, "/Node", "Element", "", "Value") * XmlHelper.Insert(path, "/Node", "Element", "Attribute", "Value") * XmlHelper.Insert(path, "/Node", "", "Attribute", "Value") ************************************************/ try { var xmlDocument = new XmlDocument(); xmlDocument.Load(path); var selectSingleNode = xmlDocument.SelectSingleNode(node); if (element.Equals("")) { if (!attribute.Equals("")) { var xmlElement = (XmlElement)selectSingleNode; xmlElement?.SetAttribute(attribute, value); } } else { var xmlElement = xmlDocument.CreateElement(element); if (attribute.Equals("")) xmlElement.InnerText = value; else xmlElement.SetAttribute(attribute, value); selectSingleNode?.AppendChild(xmlElement); } xmlDocument.Save(path); } catch (Exception exception) { throw exception; } } /// /// 读取数据 /// /// 路径 /// 节点 /// 属性名，非空时返回该属性值，否则返回串联值 /// <returns>string</returns> public static string Read(string path, string node, string attribute) { /************************************************** * 使用示列: * XmlHelper.Read(path, "/Node", "") * XmlHelper.Read(path, "/Node/Element[@Attribute='Name']", "Attribute") ************************************************/ var value = ""; try { var xmlDocument = new XmlDocument(); xmlDocument.Load(path); var selectSingleNode = xmlDocument.SelectSingleNode(node); value = attribute.Equals("") ? selectSingleNode?.InnerText : selectSingleNode?.Attributes?[attribute].Value; } catch (Exception exception) { throw exception; } return value; } /// /// 读取xml的节点 /// /// 路径 /// 节点名称 /// 属性名称 /// <returns></returns> public static string ReadXml(string path, string nodeName, string name) //读取XML { try { var xmlValue = ""; if (!File.Exists(path)) return xmlValue; var myFile = new FileStream(path, FileMode.Open); //打开xml文件 var xmlTextReader = new XmlTextReader(myFile); //xml文件阅读器 while (xmlTextReader.Read()) if (xmlTextReader.Name == nodeName) { //获取服务器的地址//获取升级文档的最后一次更新日期 xmlValue = xmlTextReader.GetAttribute(name); break; } xmlTextReader.Close(); myFile.Close(); return xmlValue; } catch (Exception exception) { throw exception; } } /// /// 读取xml文件，并将文件序列化为类 /// /// <typeparam name="T"></typeparam> /// /// <returns></returns> public static T ReadXML<T>(string path) { try { var reader = new XmlSerializer(typeof(T)); var file = new StreamReader(path); return (T)reader.Deserialize(file); } catch (Exception exception) { throw exception; } } /// /// 将DataTable写入XML文件中 /// /// 含有数据的DataTable /// 文件路径 public static void SaveTableToFile(DataTable dataTable, string filePath) { try { var dataSet = new DataSet("Config"); dataSet.Tables.Add(dataTable.Copy()); dataSet.WriteXml(filePath); } catch (Exception exception) { throw exception; } } /// /// 将DataTable以指定的根结点名称写入文件 /// /// 含有数据的DataTable /// 根结点名称 /// 文件路径 public static void SaveTableToFile(DataTable dataTable, string rootName, string filePath) { try { var dataSet = new DataSet(rootName); dataSet.Tables.Add(dataTable.Copy()); dataSet.WriteXml(filePath); } catch (Exception exception) { throw exception; } } /// /// 静态扩展 /// /// <typeparam name="T">需要序列化的对象类型，必须声明[Serializable]特征</typeparam> /// 需要序列化的对象 /// true:省略XML声明;否则为false.默认false，即编写 XML 声明。 /// <returns></returns> public static string SerializeToXmlStr<T>(T obj, bool omitXmlDeclaration) { try { return XmlSerialize(obj, omitXmlDeclaration); } catch (Exception exception) { throw exception; } } /// /// 修改数据 /// /// 路径 /// 节点 /// 属性名，非空时修改该节点属性值，否则修改节点值 /// 值 /// <returns></returns> public static void Update(string path, string node, string attribute, string value) { /************************************************** * 使用示列: * XmlHelper.Insert(path, "/Node", "", "Value") * XmlHelper.Insert(path, "/Node", "Attribute", "Value") ************************************************/ try { var xmlDocument = new XmlDocument(); xmlDocument.Load(path); var selectSingleNode = xmlDocument.SelectSingleNode(node); var xmlElement = (XmlElement)selectSingleNode; if (attribute.Equals("")) { if (xmlElement != null) xmlElement.InnerText = value; } else { xmlElement?.SetAttribute(attribute, value); } xmlDocument.Save(path); } catch (Exception exception) { throw exception; } } /// /// 更新XML文件中的指定节点内容 /// /// 文件路径 /// 节点名称 /// 更新内容 /// <returns>更新是否成功</returns> public static bool UpdateNode(string filePath, string nodeName, string nodeValue) { bool flag; var xmlDocument = new XmlDocument(); xmlDocument.Load(filePath); var documentElement = xmlDocument.DocumentElement; var selectSingleNode = documentElement?.SelectSingleNode("//" + nodeName); if (selectSingleNode != null) { selectSingleNode.InnerText = nodeValue; xmlDocument.Save(filePath); flag = true; } else { flag = false; } return flag; } /// /// 使用DataSet方式更新XML文件节点 /// /// XML文件路径 /// 表名称 /// 行号 /// 列名 /// 更新值 /// <returns>更新是否成功</returns> public static bool UpdateTableCell(string filePath, string tableName, int rowIndex, string colName, string content) { bool flag; var dataSet = new DataSet(); dataSet.ReadXml(filePath); var dataTable = dataSet.Tables[tableName]; if (dataTable.Rows[rowIndex][colName] != null) { dataTable.Rows[rowIndex][colName] = content; dataSet.WriteXml(filePath); flag = true; } else { flag = false; } return flag; } /// /// 使用DataSet方式更新XML文件节点 /// /// XML文件路径 /// 表名称 /// 行号 /// 列号 /// 更新值 /// <returns>更新是否成功</returns> public static bool UpdateTableCell(string filePath, string tableName, int rowIndex, int colIndex, string content) { bool flag; var dataSet = new DataSet(); dataSet.ReadXml(filePath); var dataTable = dataSet.Tables[tableName]; if (dataTable.Rows[rowIndex][colIndex] != null) { dataTable.Rows[rowIndex][colIndex] = content; dataSet.WriteXml(filePath); flag = true; } else { flag = false; } return flag; } /// /// 将对象写入XML文件 /// /// <typeparam name="T">C#对象名</typeparam> /// 对象实例 /// 路径 /// 标号 /// 结束符号（整个xml的路径类似如下：C:\xmltest\201111send.xml，其中path=C:\xmltest,jjdbh=201111,ends=send） /// <returns></returns> public static string WriteXML<T>(T item, string path, string jjdbh, string ends) { if (string.IsNullOrEmpty(ends)) ends = "send"; var i = 0; //控制写入文件的次数， var serializer = new XmlSerializer(item.GetType()); object[] obj = { path, "\\", jjdbh, ends, ".xml" }; var xmlPath = string.Concat(obj); while (true) try { var fileStream = File.Create(xmlPath); //用filestream方式创建文件不会出现“文件正在占用中，用File.create”则不行 fileStream.Close(); TextWriter writer = new StreamWriter(xmlPath, false, Encoding.UTF8); var xmlSerializerNamespaces = new XmlSerializerNamespaces(); xmlSerializerNamespaces.Add(string.Empty, string.Empty); serializer.Serialize(writer, item, xmlSerializerNamespaces); writer.Flush(); writer.Close(); break; } catch (Exception exception) { if (i < 5) i++; else { throw exception; } } return SerializeToXmlStr(item, true); } /// /// 使用XmlSerializer反序列化对象 /// /// 需要反序列化的xml字符串 /// <returns>反序列化后的对象</returns> public static T XmlDeserialize<T>(string xmlOfObject) where T : class { var xmlReader = XmlReader.Create(new StringReader(xmlOfObject), new XmlReaderSettings()); return (T)new XmlSerializer(typeof(T)).Deserialize(xmlReader); } /// /// 从文件读取并反序列化为对象 （解决: 多线程或多进程下读写并发问题） /// /// <typeparam name="T">返回的对象类型</typeparam> /// 文件地址 /// <returns></returns> public static T XmlFileDeserialize<T>(string path) { var bytes = ShareReadFile(path); if (bytes.Length < 1) //当文件正在被写入数据时，可能读出为0 for (var i = 0; i < 5; i++) { bytes = ShareReadFile(path); //5次机会,采用这样诡异的做法避免独占文件和文件正在被写入时读出来的数据为0字节的问题。 if (bytes.Length > 0) break; Thread.Sleep(50); //悲观情况下总共最多消耗1/4秒，读取文件 } var xmlDocument = new XmlDocument(); xmlDocument.Load(new MemoryStream(bytes)); if (xmlDocument.DocumentElement != null) return (T)new XmlSerializer(typeof(T)).Deserialize(new XmlNodeReader(xmlDocument.DocumentElement)); return default(T); /*var xmlReaderSettings = new XmlReaderSettings(); xmlReaderSettings.CloseInput = true; using (var xmlReader = XmlReader.Create(path, xmlReaderSettings)) { var obj = (T) new XmlSerializer(typeof(T)).Deserialize(xmlReader); return obj; }*/ } /// /// 使用XmlSerializer序列化对象 /// /// <typeparam name="T">需要序列化的对象类型，必须声明[Serializable]特征</typeparam> /// 需要序列化的对象 /// true:省略XML声明;否则为false.默认false，即编写 XML 声明。 /// <returns>序列化后的字符串</returns> public static string XmlSerialize<T>(T obj, bool omitXmlDeclaration) { /* This property only applies to XmlWriter instances that output text content to a stream; otherwise, this setting is ignored. 可能很多朋友遇见过 不能转换成Xml不能反序列化成为UTF8XML声明的情况，就是这个原因。 */ var xmlSettings = new XmlWriterSettings { OmitXmlDeclaration = omitXmlDeclaration, Encoding = new UTF8Encoding(false) }; var stream = new MemoryStream(); //var writer = new StringWriter(); var xmlwriter = XmlWriter.Create(stream /*writer*/, xmlSettings); //这里如果直接写成：Encoding = Encoding.UTF8 会在生成的xml中加入BOM(Byte-order Mark) 信息(Unicode 字节顺序标记) ， 所以new System.Text.UTF8Encoding(false)是最佳方式，省得再做替换的麻烦 var xmlSerializerNamespaces = new XmlSerializerNamespaces(); xmlSerializerNamespaces.Add(string.Empty, string.Empty); //在XML序列化时去除默认命名空间xmlns:xsd和xmlns:xsi var xmlSerializer = new XmlSerializer(typeof(T)); xmlSerializer.Serialize(xmlwriter, obj, xmlSerializerNamespaces); return Encoding.UTF8.GetString(stream.ToArray()); //writer.ToString(); } /// /// 使用XmlSerializer序列化对象 /// /// <typeparam name="T"></typeparam> /// 文件路径 /// 需要序列化的对象 /// true:省略XML声明;否则为false.默认false，即编写 XML 声明。 /// 是否移除默认名称空间(如果对象定义时指定了:XmlRoot(Namespace = "http://www.xxx.com/xsd")则需要传false值进来) /// <returns>序列化后的字符串</returns> public static void XmlSerialize<T>(string path, T obj, bool omitXmlDeclaration, bool removeDefaultNamespace) { var xmlWriterSettings = new XmlWriterSettings { OmitXmlDeclaration = omitXmlDeclaration }; using (var xmlWriter = XmlWriter.Create(path, xmlWriterSettings)) { var xmlSerializerNamespaces = new XmlSerializerNamespaces(); if (removeDefaultNamespace) xmlSerializerNamespaces.Add(string.Empty, string.Empty); //在XML序列化时去除默认命名空间xmlns:xsd和xmlns:xsi var xmlSerializer = new XmlSerializer(typeof(T)); xmlSerializer.Serialize(xmlWriter, obj, xmlSerializerNamespaces); } } private static byte[] ShareReadFile(string filePath) { byte[] bytes; //避免"正由另一进程使用,因此该进程无法访问此文件"造成异常 共享锁 flieShare必须为ReadWrite，但是如果文件不存在的话，还是会出现异常，所以这里不能吃掉任何异常，但是需要考虑到这些问题 using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { bytes = new byte[fileStream.Length]; var numBytesToRead = (int)fileStream.Length; var numBytesRead = 0; while (numBytesToRead > 0) { var bytesRead = fileStream.Read(bytes, numBytesRead, numBytesToRead); if (bytesRead == 0) break; numBytesRead += bytesRead; numBytesToRead -= bytesRead; } } return bytes; } #endregion } }
packageInstall-Package NPOINPOIHelperusing NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.Formula.Eval; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; using System.Web; namespace Helper { /// /// NPOIHelper /// public class NPOIHelper { /// /// 公司 /// public static string Company="NPOI"; /// /// 作者 /// public static string Author = "作者"; /// /// 创建程序信息 /// public static string ApplicationName = "创建程序信息"; /// /// 最后保存者信息 /// public static string LastAuthor = "最后保存者信息"; /// /// 作者信息 /// public static string Comments = "作者信息"; /// /// 标题信息 /// public static string Title = "标题信息"; /// /// 主题信息 /// public static string Subject = "主题信息"; /// /// 创建Excel /// /// <returns></returns> public static HSSFWorkbook CreateExcel() { HSSFWorkbook workbook = new HSSFWorkbook(); #region 右击文件 属性信息 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = Company; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; //填加xls文件作者信息 si.ApplicationName = ApplicationName; //填加xls文件创建程序信息 si.LastAuthor = LastAuthor; //填加xls文件最后保存者信息 si.Comments = Comments; //填加xls文件作者信息 si.Title = Title; //填加xls文件标题信息 si.Subject = Subject;//填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; #endregion return workbook; } #region 从datatable中将数据导出到excel /// /// DataTable导出到Excel的MemoryStream /// /// 源DataTable /// 表头文本 static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = CreateExcel(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion( new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose(); return ms; } } /// /// DataTable导出到Excel的MemoryStream /// /// 源DataTable /// 表头文本 static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 0) { #region 表头及样式 //{ // XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; // headerRow.HeightInPoints = 25; // headerRow.CreateCell(0).SetCellValue(strHeaderText); // XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; // headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // XSSFFont font = workbook.CreateFont() as XSSFFont; // font.FontHeightInPoints = 20; // font.Boldweight = 700; // headStyle.SetFont(font); // headerRow.GetCell(0).CellStyle = headStyle; // //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); // //headerRow.Dispose(); //} #endregion #region 列头及样式 { XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } workbook.Write(fs); fs.Close(); } /// /// DataTable导出到Excel文件 /// /// 源DataTable /// 表头文本 /// 保存位置 public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName) { string[] temp = strFileName.Split('.'); if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && dtSource.Rows.Count < 65536) { using (MemoryStream ms = ExportDT(dtSource, strHeaderText)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } else { if (temp[temp.Length - 1] == "xls") strFileName = strFileName + "x"; using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { ExportDTI(dtSource, strHeaderText, fs); } } } #endregion #region 从excel中将数据导出到datatable /// /// 读取excel 默认第一行为标头 /// /// excel文档路径 /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName) { DataTable dt = new DataTable(); IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read,FileShare.ReadWrite)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheetAt(0); dt = ImportDt(sheet, 0, true); return dt; } /// /// 读取Excel流到DataTable /// /// Excel流 /// <returns>第一个sheet中的数据</returns> public static DataTable ImportExceltoDt(Stream stream) { try { DataTable dt = new DataTable(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } ISheet sheet = wb.GetSheetAt(0); dt = ImportDt(sheet, 0, true); return dt; } catch (Exception) { throw; } } /// /// 读取Excel流到DataTable /// /// Excel流 /// 表单名 /// 列头所在行号，-1表示没有列头 /// <returns>指定sheet中的数据</returns> public static DataTable ImportExceltoDt(Stream stream, string sheetName, int HeaderRowIndex) { try { DataTable dt = new DataTable(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } ISheet sheet = wb.GetSheet(sheetName); dt = ImportDt(sheet, HeaderRowIndex, true); return dt; } catch (Exception) { throw; } } /// /// 读取Excel流到DataSet /// /// Excel流 /// <returns>Excel中的数据</returns> public static DataSet ImportExceltoDs(Stream stream) { try { DataSet ds = new DataSet(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } for (int i = 0; i < wb.NumberOfSheets; i++) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheetAt(i); dt = ImportDt(sheet, 0, true); ds.Tables.Add(dt); } return ds; } catch (Exception) { throw; } } /// /// 读取Excel流到DataSet /// /// Excel流 /// 字典参数，key：sheet名，value：列头所在行号，-1表示没有列头 /// <returns>Excel中的数据</returns> public static DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict) { try { DataSet ds = new DataSet(); IWorkbook wb; using (stream) { wb = WorkbookFactory.Create(stream); } foreach (string key in dict.Keys) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheet(key); dt = ImportDt(sheet, dict[key], true); ds.Tables.Add(dt); } return ds; } catch (Exception) { throw; } } /// /// 读取excel /// /// excel文件路径 /// 需要导出的sheet /// 列头所在行号，-1表示没有列头 /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read,FileShare.ReadWrite)) { wb = new HSSFWorkbook(file); } ISheet sheet = wb.GetSheet(SheetName); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, true); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// /// 读取excel /// /// excel文件路径 /// 需要导出的sheet序号 /// 列头所在行号，-1表示没有列头 /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read,FileShare.ReadWrite)) { wb = WorkbookFactory.Create(file); } ISheet isheet = wb.GetSheetAt(SheetIndex); DataTable table = new DataTable(); table = ImportDt(isheet, HeaderRowIndex, true); //ExcelFileStream.Close(); workbook = null; isheet = null; return table; } /// /// 读取excel /// /// excel文件路径 /// 需要导出的sheet /// 列头所在行号，-1表示没有列头 /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read,FileShare.ReadWrite)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheet(SheetName); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, needHeader); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// /// 读取excel /// /// excel文件路径 /// 需要导出的sheet序号 /// 列头所在行号，-1表示没有列头 /// <returns></returns> public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader) { HSSFWorkbook workbook; IWorkbook wb; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read,FileShare.ReadWrite)) { wb = WorkbookFactory.Create(file); } ISheet sheet = wb.GetSheetAt(SheetIndex); DataTable table = new DataTable(); table = ImportDt(sheet, HeaderRowIndex, needHeader); //ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// /// 将制定sheet中的数据导出到datatable中 /// /// 需要导出的sheet /// 列头所在行号，-1表示没有列头 /// <returns></returns> static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader) { DataTable table = new DataTable(); IRow headerRow; int cellCount; try { if (HeaderRowIndex < 0 || !needHeader) { headerRow = sheet.GetRow(0); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else { headerRow = sheet.GetRow(HeaderRowIndex); cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { DataColumn column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { DataColumn column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } } } int rowCount = sheet.LastRowNum; for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++) { try { IRow row; if (sheet.GetRow(i) == null) { row = sheet.CreateRow(i); } else { row = sheet.GetRow(i); } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j <= cellCount; j++) { try { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.String: string str = row.GetCell(j).StringCellValue; if (str != null && str.Length > 0) { dataRow[j] = str.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue); } else { dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue); } break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { dataRow[j] = strFORMULA.ToString(); } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } break; default: dataRow[j] = ""; break; } } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); throw; } } table.Rows.Add(dataRow); } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); throw; } } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); throw; } return table; } #endregion public static void InsertSheet(string outputFile, string sheetname, DataTable dt) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); IWorkbook hssfworkbook = WorkbookFactory.Create(readfile); //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); int num = hssfworkbook.GetSheetIndex(sheetname); ISheet sheet1; if (num >= 0) sheet1 = hssfworkbook.GetSheet(sheetname); else { sheet1 = hssfworkbook.CreateSheet(sheetname); } try { if (sheet1.GetRow(0) == null) { sheet1.CreateRow(0); } for (int coluid = 0; coluid < dt.Columns.Count; coluid++) { if (sheet1.GetRow(0).GetCell(coluid) == null) { sheet1.GetRow(0).CreateCell(coluid); } sheet1.GetRow(0).GetCell(coluid).SetCellValue(dt.Columns[coluid].ColumnName); } } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } for (int i = 1; i <= dt.Rows.Count; i++) { try { if (sheet1.GetRow(i) == null) { sheet1.CreateRow(i); } for (int coluid = 0; coluid < dt.Columns.Count; coluid++) { if (sheet1.GetRow(i).GetCell(coluid) == null) { sheet1.GetRow(i).CreateCell(coluid); } sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString()); } } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } #region 更新excel中的数据 /// /// 更新Excel表格 /// /// 需更新的excel表格路径 /// sheet名 /// 需更新的数据 /// 需更新的列号 /// 需更新的开始行号 public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid) { //FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); IWorkbook hssfworkbook = null;// WorkbookFactory.Create(outputFile); //HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluid) == null) { sheet1.GetRow(i + rowid).CreateCell(coluid); } sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { //readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } /// /// 更新Excel表格 /// /// 需更新的excel表格路径 /// sheet名 /// 需更新的数据 /// 需更新的列号 /// 需更新的开始行号 public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); readfile.Close(); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < coluids.Length; j++) { for (int i = 0; i < updateData[j].Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null) { sheet1.GetRow(i + rowid).CreateCell(coluids[j]); } sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } } try { FileStream writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } /// /// 更新Excel表格 /// /// 需更新的excel表格路径 /// sheet名 /// 需更新的数据 /// 需更新的列号 /// 需更新的开始行号 public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluid) == null) { sheet1.GetRow(i + rowid).CreateCell(coluid); } sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } /// /// 更新Excel表格 /// /// 需更新的excel表格路径 /// sheet名 /// 需更新的数据 /// 需更新的列号 /// 需更新的开始行号 public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); readfile.Close(); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < coluids.Length; j++) { for (int i = 0; i < updateData[j].Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null) { sheet1.GetRow(i + rowid).CreateCell(coluids[j]); } sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } } try { FileStream writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } #endregion /// /// 获取工作页的页数 /// /// /// <returns></returns> public static int GetSheetNumber(string outputFile) { int number = 0; try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); IWorkbook wb = WorkbookFactory.Create(readfile); number = wb.NumberOfSheets; } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); throw; } return number; } /// /// 获取工作页名称 /// /// /// <returns></returns> public static ArrayList GetSheetName(string outputFile) { ArrayList arrayList = new ArrayList(); try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read,FileShare.ReadWrite); IWorkbook wb =WorkbookFactory.Create(readfile); for (int i = 0; i < wb.NumberOfSheets; i++) { arrayList.Add(wb.GetSheetName(i)); } } catch (Exception exception) { //wl.WriteLogs(exception.ToString()); throw; } return arrayList; } public static bool isNumeric(String message, out double result) { Regex rex = new Regex(@"^[-]?\d+[.]?\d*$"); result = -1; if (rex.IsMatch(message)) { result = double.Parse(message); return true; } else return false; } ////////// 现用导出 \\\\\\\\\\ /// /// 用于Web导出 第一步 /// /// 源DataTable /// 表头文本 /// 文件名 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName) { HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer()); curContext.Response.End(); } /// /// DataTable导出到Excel的MemoryStream 第二步 /// /// 源DataTable /// 表头文本 public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息";//填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText)) { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion rowIndex = 1; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; } } /// /// /注：分浏览器进行编码（IE必须编码，FireFox不能编码，Chrome可编码也可不编码） /// /// /// /// public static void ExportByWeb(DataSet ds, string strHeaderText, string strFileName) { HttpContext curContext = HttpContext.Current; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.Charset = ""; if (curContext.Request.UserAgent.ToLower().IndexOf("firefox", System.StringComparison.Ordinal) > 0) { curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName); } else { curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)); } // curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" +strFileName); curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); curContext.Response.BinaryWrite(ExportDataSetToExcel(ds, strHeaderText).GetBuffer()); curContext.Response.End(); } /// /// 由DataSet导出Excel /// /// 要导出数据的DataTable /// 工作表名称 /// <returns>Excel工作表</returns> private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); string[] sheetNames = sheetName.Split(','); for (int i = 0; i < sheetNames.Length; i++) { ISheet sheet = workbook.CreateSheet(sheetNames[i]); #region 列头 IRow headerRow = sheet.CreateRow(0); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //取得列宽 int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count]; foreach (DataColumn item in sourceDs.Tables[i].Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } // 处理列头 foreach (DataColumn column in sourceDs.Tables[i].Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion #region 填充值 int rowIndex = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } #endregion } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; } /// /// 验证导入的Excel是否有数据 /// /// /// <returns></returns> public static bool HasData(Stream excelFileStream) { using (excelFileStream) { IWorkbook workBook = new HSSFWorkbook(excelFileStream); if (workBook.NumberOfSheets > 0) { ISheet sheet = workBook.GetSheetAt(0); return sheet.PhysicalNumberOfRows > 0; } } return false; } } }
using System; using System.Collections.Concurrent; using System.Collections.Generic; using System.Collections.Specialized; using System.ComponentModel; using System.Linq; using System.Reflection; using System.Web; using System.Web.Mvc; namespace Helper { public static class EnumHelper { #region Field private static ConcurrentDictionary<Type, Dictionary<int, string>> enumDisplayValueDict = new ConcurrentDictionary<Type, Dictionary<int, string>>(); private static ConcurrentDictionary<Type, Dictionary<string, int>> enumValueDisplayDict = new ConcurrentDictionary<Type, Dictionary<string, int>>(); private static ConcurrentDictionary<Type, Dictionary<int, string>> enumNameValueDict = new ConcurrentDictionary<Type, Dictionary<int, string>>(); private static ConcurrentDictionary<Type, Dictionary<string, int>> enumValueNameDict = new ConcurrentDictionary<Type, Dictionary<string, int>>(); private static ConcurrentDictionary<Type, Dictionary<int, Tuple<string, int>>> enumSeqDisplayValueDict = new ConcurrentDictionary<Type, Dictionary<int, Tuple<string, int>>>(); private static ConcurrentDictionary<string, Type> enumTypeDict = null; #endregion #region Method /// /// 获取枚举对象Key与显示名称的字典 /// /// /// <returns></returns> public static Dictionary<int, string> GetEnumDictionary(Type enumType) { if (!enumType.IsEnum) throw new Exception("给定的类型不是枚举类型"); Dictionary<int, string> names = enumNameValueDict.ContainsKey(enumType) ? enumNameValueDict[enumType] : new Dictionary<int, string>(); if (names.Count == 0) { names = GetEnumDictionaryItems(enumType); enumNameValueDict[enumType] = names; } return names; } private static Dictionary<int, string> GetEnumDictionaryItems(Type enumType) { FieldInfo[] enumItems = enumType.GetFields(BindingFlags.Public | BindingFlags.Static); Dictionary<int, string> names = new Dictionary<int, string>(enumItems.Length); foreach (FieldInfo enumItem in enumItems) { int intValue = (int)enumItem.GetValue(enumType); names[intValue] = enumItem.Name; } return names; } /// /// 获取枚举对象显示名称与Key的字典 /// /// /// <returns></returns> public static Dictionary<string, int> GetEnumValueNameItems(Type enumType) { if (!enumType.IsEnum) throw new Exception("给定的类型不是枚举类型"); Dictionary<string, int> values = enumValueNameDict.ContainsKey(enumType) ? enumValueNameDict[enumType] : new Dictionary<string, int>(); if (values.Count == 0) { values = TryToGetEnumValueNameItems(enumType); enumValueNameDict[enumType] = values; } return values; } private static Dictionary<string, int> TryToGetEnumValueNameItems(Type enumType) { FieldInfo[] enumItems = enumType.GetFields(BindingFlags.Public | BindingFlags.Static); Dictionary<string, int> values = new Dictionary<string, int>(enumItems.Length); foreach (FieldInfo enumItem in enumItems) { int intValue = (int)enumItem.GetValue(enumType); values[enumItem.Name] = intValue; } return values; } /// /// 获取枚举对象的值内容 /// /// /// /// <returns></returns> public static int TryToGetEnumValueByName(this Type enumType, string name) { if (!enumType.IsEnum) throw new Exception("给定的类型不是枚举类型"); Dictionary<string, int> enumDict = GetEnumValueNameItems(enumType); return enumDict.ContainsKey(name) ? enumDict[name] : enumDict.Select(d => d.Value).FirstOrDefault(); } /// /// 获取枚举类型 /// /// /// /// <returns></returns> public static Type TrytoGetEnumType(Assembly assembly, string typeName) { enumTypeDict = enumTypeDict ?? LoadEnumTypeDict(assembly); if (enumTypeDict.ContainsKey(typeName)) { return enumTypeDict[typeName]; } return null; } private static ConcurrentDictionary<string, Type> LoadEnumTypeDict(Assembly assembly) { Type[] typeArray = assembly.GetTypes(); Dictionary<string, Type> dict = typeArray.Where(o => o.IsEnum).ToDictionary(o => o.Name, o => o); ConcurrentDictionary<string, Type> enumTypeDict = new ConcurrentDictionary<string, Type>(dict); return enumTypeDict; } #endregion /// /// 枚举显示名(属性扩展) /// public class EnumDisplayNameAttribute : Attribute { private string _displayName; public EnumDisplayNameAttribute(string displayName) { this._displayName = displayName; } public string DisplayName { get { return _displayName; } } } public class EnumExt { /// /// 根据枚举成员获取自定义属性EnumDisplayNameAttribute的属性DisplayName /// /// /// <returns></returns> public static string GetEnumDisplayName(object o) { //获取枚举的Type类型对象 Type t = o.GetType(); //获取枚举的所有字段 FieldInfo[] ms = t.GetFields(); //遍历所有枚举的所有字段 foreach (FieldInfo f in ms) { if (f.Name != o.ToString()) { continue; } //第二个参数true表示查找EnumDisplayNameAttribute的继承链 if (f.IsDefined(typeof(EnumDisplayNameAttribute), true)) { return (f.GetCustomAttributes(typeof(EnumDisplayNameAttribute), true)[0] as EnumDisplayNameAttribute) .DisplayName; } } //如果没有找到自定义属性，直接返回属性项的名称 return o.ToString(); } /// /// 根据枚举转换成SelectList /// /// 枚举 /// <returns></returns> public static List<SelectListItem> GetSelectList(Type enumType) { List<SelectListItem> selectList = new List<SelectListItem>(); foreach (object e in Enum.GetValues(enumType)) { selectList.Add(new SelectListItem() { Text = GetDescription(e), Value = ((int)e).ToString() }); } return selectList; } } /// /// 根据枚举转换成SelectList并且设置默认选中项 /// /// /// 默认选中项 /// <returns></returns> public static List<SelectListItem> GetSelectList(Type enumType, object ObjDefaultValue) { int defaultValue = Int32.Parse(ObjDefaultValue.ToString()); List<SelectListItem> selectList = new List<SelectListItem>(); foreach (object e in Enum.GetValues(enumType)) { try { if ((int)e == defaultValue) { selectList.Add(new SelectListItem() { Text = GetDescription(e), Value = ((int)e).ToString(), Selected = true }); } else { selectList.Add(new SelectListItem() { Text = GetDescription(e), Value = ((int)e).ToString() }); } } catch (Exception ex) { string exs = ex.Message; } } return selectList; } /// /// 根据枚举转换成SelectList /// /// 枚举 /// <returns></returns> public static List<SelectListItem> GetSelectList(Type enumType) { List<SelectListItem> selectList = new List<SelectListItem>(); foreach (object e in Enum.GetValues(enumType)) { selectList.Add(new SelectListItem() { Text = GetDescription(e), Value = ((int)e).ToString() }); } return selectList; } /// /// 根据枚举成员获取自定义属性EnumDisplayNameAttribute的属性DisplayName /// /// /// <returns></returns> public static Dictionary<string, int> GetDescriptionAndValue(Type enumType) { Dictionary<string, int> dicResult = new Dictionary<string, int>(); foreach (object e in Enum.GetValues(enumType)) { dicResult.Add(GetDescription(e), (int)e); } return dicResult; } /// /// 根据枚举成员获取DescriptionAttribute的属性Description /// /// /// <returns></returns> public static string GetDescription(object o) { //获取枚举的Type类型对象 Type t = o.GetType(); //获取枚举的所有字段 FieldInfo[] ms = t.GetFields(); //遍历所有枚举的所有字段 foreach (FieldInfo f in ms) { if (f.Name != o.ToString()) { continue; } //// Description // //第二个参数true表示查找EnumDisplayNameAttribute的继承链 // if (f.IsDefined(typeof(EnumDisplayNameAttribute), true)) // { // return // (f.GetCustomAttributes(typeof(EnumDisplayNameAttribute), true)[0] as EnumDisplayNameAttribute) // .DisplayName; // } FieldInfo fi = o.GetType().GetField(o.ToString()); try { var attributes = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false); return (attributes != null && attributes.Length > 0) ? attributes[0].Description : o.ToString(); } catch { return "(Unknow)"; } } //如果没有找到自定义属性，直接返回属性项的名称 return o.ToString(); } #region 新增扩展方法 /// /// 扩展方法：根据枚举值得到相应的枚举定义字符串 /// /// /// /// <returns></returns> public static String ToEnumString(this int value, Type enumType) { NameValueCollection nvc = GetEnumStringFromEnumValue(enumType); return nvc[value.ToString()]; } /// /// 根据枚举类型得到其所有的 值 与 枚举定义字符串 的集合 /// /// /// <returns></returns> public static NameValueCollection GetEnumStringFromEnumValue(Type enumType) { NameValueCollection nvc = new NameValueCollection(); Type typeDescription = typeof(DescriptionAttribute); System.Reflection.FieldInfo[] fields = enumType.GetFields(); string strText = string.Empty; string strValue = string.Empty; foreach (FieldInfo field in fields) { if (field.FieldType.IsEnum) { strValue = ((int)enumType.InvokeMember(field.Name, BindingFlags.GetField, null, null, null)).ToString(); nvc.Add(strValue, field.Name); } } return nvc; } /// /// 扩展方法：根据枚举值得到属性Description中的描述, 如果没有定义此属性则返回空串 /// /// /// /// <returns></returns> public static String ToEnumDescriptionString(this int value, Type enumType) { NameValueCollection nvc = GetNVCFromEnumValue(enumType); return nvc[value.ToString()]; } /// /// 根据枚举类型得到其所有的 值 与 枚举定义Description属性 的集合 /// /// /// <returns></returns> public static NameValueCollection GetNVCFromEnumValue(Type enumType) { NameValueCollection nvc = new NameValueCollection(); Type typeDescription = typeof(DescriptionAttribute); System.Reflection.FieldInfo[] fields = enumType.GetFields(); string strText = string.Empty; string strValue = string.Empty; foreach (FieldInfo field in fields) { if (field.FieldType.IsEnum) { strValue = ((int)enumType.InvokeMember(field.Name, BindingFlags.GetField, null, null, null)).ToString(); object[] arr = field.GetCustomAttributes(typeDescription, true); if (arr.Length > 0) { DescriptionAttribute aa = (DescriptionAttribute)arr[0]; strText = aa.Description; } else { strText = ""; } nvc.Add(strValue, strText); } } return nvc; } #endregion } }
Packageusing System.IO.Compression; using System.IO.Compression.FileSystem;实例System.IO.Compression.ZipFile.CreateFromDirectory(@"C:\Users\Helj\Desktop\安装包\配置文件", @"C:\Users\Helj\Desktop\安装包\a.zip"); System.IO.Compression.ZipFile.ExtractToDirectory( @"C:\Users\Helj\Desktop\安装包\a.zip",@"C:\Users\Helj\Desktop\安装包\配置文件");
AESusing System; using System.Security.Cryptography; using System.Text; namespace Helper.Encryption { /// /// 加密 /// public class AES { /// /// DES加密字符串 /// /// 待加密的字符串 /// 加密密钥,要求为8位 /// <returns>加密成功返回加密后的字符串,失败返回源串</returns> public static string AESEncrypt(string encryptString, string encryptKey) { try { if (string.IsNullOrEmpty(encryptString)) return null; Byte[] toEncryptArray = Encoding.UTF8.GetBytes(encryptString); System.Security.Cryptography.RijndaelManaged rm = new System.Security.Cryptography.RijndaelManaged { Key = Encoding.UTF8.GetBytes(encryptKey), Mode = System.Security.Cryptography.CipherMode.ECB, Padding = System.Security.Cryptography.PaddingMode.PKCS7 }; System.Security.Cryptography.ICryptoTransform cTransform = rm.CreateEncryptor(); Byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length); return Convert.ToBase64String(resultArray, 0, resultArray.Length); } catch (Exception ex) { return ""; } } /// /// DES解密字符串 /// /// 待解密的字符串 /// 解密密钥,要求为8位,和加密密钥相同 /// <returns>解密成功返回解密后的字符串,失败返源串</returns> public static string AESDecrypt(string decryptString, string decryptKey) { try { if (string.IsNullOrEmpty(decryptString)) return null; Byte[] toEncryptArray = Convert.FromBase64String(decryptString); System.Security.Cryptography.RijndaelManaged rm = new System.Security.Cryptography.RijndaelManaged { Key = Encoding.UTF8.GetBytes(decryptKey), Mode = System.Security.Cryptography.CipherMode.ECB, Padding = System.Security.Cryptography.PaddingMode.PKCS7 }; System.Security.Cryptography.ICryptoTransform cTransform = rm.CreateDecryptor(); Byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length); return Encoding.UTF8.GetString(resultArray); } catch (Exception ex) { return ""; } } } } DESusing System; using System.IO; using System.Security.Cryptography; using System.Text; namespace Helper.Encryption { public class DES { /// /// 32位Key值： /// private static byte[] DESKey = new byte[] { 0x03, 0x0B, 0x13, 0x1B, 0x23, 0x2B, 0x33, 0x3B, 0x43, 0x4B, 0x9B, 0x93, 0x8B, 0x83, 0x7B, 0x73, 0x6B, 0x63, 0x5B, 0x53, 0xF3, 0xFB, 0xA3, 0xAB, 0xB3, 0xBB, 0xC3, 0xEB, 0xE3, 0xDB, 0xD3, 0xCB }; #region DES加密 /// /// DES加密 /// /// 待加密字串 /// <returns>加密后的字符串</returns> public static string DESEncrypt(string strSource) { return DESEncrypt(strSource, DESKey); } /// /// DES加密 /// /// 待加密字串 /// Key值 /// <returns>加密后的字符串</returns> public static string DESEncrypt(string strSource, byte[] key) { SymmetricAlgorithm sa = Rijndael.Create(); sa.Key = key; sa.Mode = CipherMode.ECB; sa.Padding = PaddingMode.Zeros; MemoryStream ms = new MemoryStream(); CryptoStream cs = new CryptoStream(ms, sa.CreateEncryptor(), CryptoStreamMode.Write); byte[] byt = Encoding.Unicode.GetBytes(strSource); cs.Write(byt, 0, byt.Length); cs.FlushFinalBlock(); cs.Close(); return Convert.ToBase64String(ms.ToArray()); } #endregion #region DES解密 /// /// DES解密 /// /// 待解密的字串 /// <returns>解密后的字符串</returns> public static string DESDecrypt(string strSource) { return DESDecrypt(strSource, DESKey); } /// /// DES解密 /// /// 待解密的字串 /// 32位Key值 /// <returns>解密后的字符串</returns> public static string DESDecrypt(string strSource, byte[] key) { SymmetricAlgorithm sa = Rijndael.Create(); sa.Key = key; sa.Mode = CipherMode.ECB; sa.Padding = PaddingMode.Zeros; ICryptoTransform ct = sa.CreateDecryptor(); byte[] byt = Convert.FromBase64String(strSource); MemoryStream ms = new MemoryStream(byt); CryptoStream cs = new CryptoStream(ms, ct, CryptoStreamMode.Read); StreamReader sr = new StreamReader(cs, Encoding.Unicode); return sr.ReadToEnd().Trim('\0'); } #endregion } }MD5using System.Security.Cryptography; using System.Text; namespace Helper.Encryption { public class MD5 { #region MD5函数 /// /// MD5函数,需引用：using System.Security.Cryptography; /// /// 原始字符串 /// <returns>MD5结果</returns> public static string Encrypto(string str) { //微软md5方法参考return FormsAuthentication.HashPasswordForStoringInConfigFile(str, "md5"); byte[] b = Encoding.Default.GetBytes(str); b = new MD5CryptoServiceProvider().ComputeHash(b); string ret = ""; for (int i = 0; i < b.Length; i++) ret += b[i].ToString("x").PadLeft(2, '0'); return ret; } #endregion } } SHA256using System; using System.Security.Cryptography; using System.Text; namespace Helper.Encryption { public partial class SHA256 { #region SHA256函数 /// /// SHA256函数 /// /// /// 原始字符串 /// <returns>SHA256结果</returns> public static string Encrypto(string str) { byte[] SHA256Data = Encoding.UTF8.GetBytes(str); SHA256Managed Sha256 = new SHA256Managed(); byte[] Result = Sha256.ComputeHash(SHA256Data); return Convert.ToBase64String(Result); //返回长度为44字节的字符串 } #endregion } }Symmetricusing System; using System.IO; using System.Security.Cryptography; using System.Text; namespace Helper.Encryption { /// /// 对称加密 /// public class Symmetric { private SymmetricAlgorithm mobjCryptoService; private string Key; /// /// 对称加密类的构造函数 /// public Symmetric() { mobjCryptoService = new RijndaelManaged(); Key = "Guz(%&hj7x89H$yuBI0456FtmaT5&fvHUFCy76*h%(HilJ$lhj!y6&(*jkP87jH7"; } /// /// 获得密钥 /// /// <returns>密钥</returns> private byte[] GetLegalKey() { string sTemp = Key; mobjCryptoService.GenerateKey(); byte[] bytTemp = mobjCryptoService.Key; int KeyLength = bytTemp.Length; if (sTemp.Length > KeyLength) sTemp = sTemp.Substring(0, KeyLength); else if (sTemp.Length < KeyLength) sTemp = sTemp.PadRight(KeyLength, ' '); return ASCIIEncoding.ASCII.GetBytes(sTemp); } /// /// 获得初始向量IV /// /// <returns>初试向量IV</returns> private byte[] GetLegalIV() { string sTemp = "E4ghj*Ghg7!rNIfb&95GUY86GfghUb#er57HBh(u%g6HJ($jhWk7&!hg4ui%$hjk"; mobjCryptoService.GenerateIV(); byte[] bytTemp = mobjCryptoService.IV; int IVLength = bytTemp.Length; if (sTemp.Length > IVLength) sTemp = sTemp.Substring(0, IVLength); else if (sTemp.Length < IVLength) sTemp = sTemp.PadRight(IVLength, ' '); return ASCIIEncoding.ASCII.GetBytes(sTemp); } /// /// 加密方法 /// /// 待加密的串 /// <returns>经过加密的串</returns> public string Encrypto(string Source) { byte[] bytIn = UTF8Encoding.UTF8.GetBytes(Source); MemoryStream ms = new MemoryStream(); mobjCryptoService.Key = GetLegalKey(); mobjCryptoService.IV = GetLegalIV(); ICryptoTransform encrypto = mobjCryptoService.CreateEncryptor(); CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Write); cs.Write(bytIn, 0, bytIn.Length); cs.FlushFinalBlock(); ms.Close(); byte[] bytOut = ms.ToArray(); return Convert.ToBase64String(bytOut); } /// /// 解密方法 /// /// 待解密的串 /// <returns>经过解密的串</returns> public string Decrypto(string Source) { byte[] bytIn = Convert.FromBase64String(Source); MemoryStream ms = new MemoryStream(bytIn, 0, bytIn.Length); mobjCryptoService.Key = GetLegalKey(); mobjCryptoService.IV = GetLegalIV(); ICryptoTransform encrypto = mobjCryptoService.CreateDecryptor(); CryptoStream cs = new CryptoStream(ms, encrypto, CryptoStreamMode.Read); StreamReader sr = new StreamReader(cs); return sr.ReadToEnd(); } } }UCS2 /// /// 使用UCS2进行编码，用于发送Unicode字符 /// /// 短信内容 /// <returns>编码结果</returns> string encodeUnicode(string str) { Encoding encodingUTF = Encoding.BigEndianUnicode; string strDestination = string.Empty; byte[] encodedBytes = encodingUTF.GetBytes(str); for (int i = 0; i < encodedBytes.Length; i++) { strDestination += BitConverter.ToString(encodedBytes, i, 1); } //下面一句加上后，编码包括长度，如“中文”编码为044E2D6587； //strDestination = String.Format("{0:X2}{1}", strDestination.Length / 2, strDestination); return strDestination; } /// /// 对UCS2编码进行解码 /// /// 解码字符串 /// <returns>解码结果</returns> public string decodingUnicode(string s) { byte[] buf = new byte[s.Length]; for (int i = 0; i < s.Length; i += 4) { buf[i / 2] = byte.Parse(s.Substring(2 + i, 2), System.Globalization.NumberStyles.AllowHexSpecifier); buf[i / 2 + 1] = byte.Parse(s.Substring(i, 2), System.Globalization.NumberStyles.AllowHexSpecifier); } return Encoding.Unicode.GetString(buf).Trim(); }