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
{
    /// <summary>
    /// 查询语句帮助类
    /// </summary>
    public static class LinqHelper
    {


        /// <summary>
        /// 动态排序
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="source">数据源</param>
        /// <param name="sortExpression">排序字段</param>
        /// <param name="sortDirection">排序方式</param>
        /// <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));
        }
        /// <summary>
        /// 动态排序旧方法
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="source">数据源</param>
        /// <param name="sortExpression">排序字段</param>
        /// <param name="sortDirection">排序方式</param>
        /// <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;
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="source">数据源</param>
        /// <param name="pageIndex">分页页码从第一页开始</param>
        /// <param name="pageSize">页码</param>
        /// <returns></returns>
        public static IQueryable<T> DataPaging<T>(IQueryable<T> source, int pageIndex, int pageSize)
        {
            return source.Skip((pageIndex-1) * pageSize).Take(pageSize);
        }
        /// <summary>
        /// 排序并分页
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="source">数据源</param>
        /// <param name="sortExpression">排序字段</param>
        /// <param name="sortDirection">排序方式</param>
        /// <param name="pageNumber">分页页码从第一页开始</param>
        /// <param name="pageSize">页码</param>
        /// <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

        /// <summary>
        ///     创建XML文档
        /// </summary>
        /// <param name="name">根节点名称</param>
        /// <param name="type">根节点的一个属性值</param>
        /// <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;
        }

        /// <summary>
        ///     删除数据
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="node">节点</param>
        /// <param name="attribute">属性名，非空时删除该节点属性值，否则删除节点值</param>
        /// <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;
            }
        }


        /// <summary>
        ///     读取XML资源到DataSet中
        /// </summary>
        /// <param name="source">XML资源，文件为路径，否则为XML字符串</param>
        /// <param name="xmlType">XML资源类型</param>
        /// <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;
            }
        }


        /// <summary>
        ///     获得xml文件中指定节点的节点数据
        /// </summary>
        /// <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;
            }
        }


        /// <summary>
        ///     读取XML资源中的指定节点内容
        /// </summary>
        /// <param name="source">XML资源</param>
        /// <param name="xmlType">XML资源类型：文件，字符串</param>
        /// <param name="nodeName">节点名称</param>
        /// <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;
        }


        /// <summary>
        ///     读取XML资源中的指定节点属性的内容
        /// </summary>
        /// <param name="source">XML资源</param>
        /// <param name="xmlType">XML资源类型：文件，字符串</param>
        /// <param name="nodeName">属性节点名称</param>
        /// <param name="attributeString"></param>
        /// <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);
        }

        /// <summary>
        ///     读取XML资源中的指定节点内容
        /// </summary>
        /// <param name="source">XML资源</param>
        /// <param name="nodeName">节点名称</param>
        /// <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);
        }


        /// <summary>
        ///     读取XML资源到DataTable中
        /// </summary>
        /// <param name="source">XML资源，文件为路径，否则为XML字符串</param>
        /// <param name="xmlType">XML资源类型：文件，字符串</param>
        /// <param name="tableName">表名称</param>
        /// <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;
            }
        }


        /// <summary>
        ///     读取XML资源中指定的DataTable的指定行指定列的值
        /// </summary>
        /// <param name="source">XML资源</param>
        /// <param name="xmlType">XML资源类型：文件，字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="colName">列名</param>
        /// <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;
            }
        }


        /// <summary>
        ///     读取XML资源中指定的DataTable的指定行指定列的值
        /// </summary>
        /// <param name="source">XML资源</param>
        /// <param name="xmlType">XML资源类型：文件，字符串</param>
        /// <param name="tableName">表名</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="colIndex">列号</param>
        /// <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;
            }
        }


        /// <summary>
        ///     获取一个字符串xml文档中的dataSet
        /// </summary>
        /// <param name="xmlString">含有xml信息的字符串</param>
        /// <param name="dataSet"></param>
        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;
            }
        }


        /// <summary>
        ///     插入数据
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="node">节点</param>
        /// <param name="element">元素名，非空时插入新元素，否则在该元素中插入属性</param>
        /// <param name="attribute">属性名，非空时插入该元素属性值，否则插入元素值</param>
        /// <param name="value">值</param>
        /// <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;
            }
        }


        /// <summary>
        ///     读取数据
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="node">节点</param>
        /// <param name="attribute">属性名，非空时返回该属性值，否则返回串联值</param>
        /// <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;
        }

        /// <summary>
        ///     读取xml的节点
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="nodeName">节点名称</param>
        /// <param name="name">属性名称</param>
        /// <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;
            }
        }


        /// <summary>
        ///     读取xml文件，并将文件序列化为类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="path"></param>
        /// <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;
            }
        }


        /// <summary>
        ///     将DataTable写入XML文件中
        /// </summary>
        /// <param name="dataTable">含有数据的DataTable</param>
        /// <param name="filePath">文件路径</param>
        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;
            }
        }


        /// <summary>
        ///     将DataTable以指定的根结点名称写入文件
        /// </summary>
        /// <param name="dataTable">含有数据的DataTable</param>
        /// <param name="rootName">根结点名称</param>
        /// <param name="filePath">文件路径</param>
        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;
            }
        }


        /// <summary>
        ///     静态扩展
        /// </summary>
        /// <typeparam name="T">需要序列化的对象类型，必须声明[Serializable]特征</typeparam>
        /// <param name="obj">需要序列化的对象</param>
        /// <param name="omitXmlDeclaration">true:省略XML声明;否则为false.默认false，即编写 XML 声明。</param>
        /// <returns></returns>
        public static string SerializeToXmlStr<T>(T obj, bool omitXmlDeclaration)
        {
            try
            {
                return XmlSerialize(obj, omitXmlDeclaration);
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        ///     修改数据
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="node">节点</param>
        /// <param name="attribute">属性名，非空时修改该节点属性值，否则修改节点值</param>
        /// <param name="value">值</param>
        /// <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;
            }
        }


        /// <summary>
        ///     更新XML文件中的指定节点内容
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="nodeName">节点名称</param>
        /// <param name="nodeValue">更新内容</param>
        /// <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;
        }


        /// <summary>
        ///     使用DataSet方式更新XML文件节点
        /// </summary>
        /// <param name="filePath">XML文件路径</param>
        /// <param name="tableName">表名称</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="colName">列名</param>
        /// <param name="content">更新值</param>
        /// <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;
        }


        /// <summary>
        ///     使用DataSet方式更新XML文件节点
        /// </summary>
        /// <param name="filePath">XML文件路径</param>
        /// <param name="tableName">表名称</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="colIndex">列号</param>
        /// <param name="content">更新值</param>
        /// <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;
        }


        /// <summary>
        ///     将对象写入XML文件
        /// </summary>
        /// <typeparam name="T">C#对象名</typeparam>
        /// <param name="item">对象实例</param>
        /// <param name="path">路径</param>
        /// <param name="jjdbh">标号</param>
        /// <param name="ends">结束符号（整个xml的路径类似如下：C:\xmltest\201111send.xml，其中path=C:\xmltest,jjdbh=201111,ends=send）</param>
        /// <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);
        }

        /// <summary>
        ///     使用XmlSerializer反序列化对象
        /// </summary>
        /// <param name="xmlOfObject">需要反序列化的xml字符串</param>
        /// <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);
        }

        /// <summary>
        ///     从文件读取并反序列化为对象 （解决: 多线程或多进程下读写并发问题）
        /// </summary>
        /// <typeparam name="T">返回的对象类型</typeparam>
        /// <param name="path">文件地址</param>
        /// <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;
            }*/
        }


        /// <summary>
        ///     使用XmlSerializer序列化对象
        /// </summary>
        /// <typeparam name="T">需要序列化的对象类型，必须声明[Serializable]特征</typeparam>
        /// <param name="obj">需要序列化的对象</param>
        /// <param name="omitXmlDeclaration">true:省略XML声明;否则为false.默认false，即编写 XML 声明。</param>
        /// <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();
        }

        /// <summary>
        ///     使用XmlSerializer序列化对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="path">文件路径</param>
        /// <param name="obj">需要序列化的对象</param>
        /// <param name="omitXmlDeclaration">true:省略XML声明;否则为false.默认false，即编写 XML 声明。</param>
        /// <param name="removeDefaultNamespace">是否移除默认名称空间(如果对象定义时指定了:XmlRoot(Namespace = "http://www.xxx.com/xsd")则需要传false值进来)</param>
        /// <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
    }
}

package

Install-Package NPOI

NPOIHelper

using 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
{
    /// <summary>
    /// NPOIHelper
    /// </summary>
    public class NPOIHelper
    {
        /// <summary>
        /// 公司
        /// </summary>
        public static string Company="NPOI";
        /// <summary>
        /// 作者
        /// </summary>
        public static string Author = "作者";
        /// <summary>
        /// 创建程序信息
        /// </summary>
        public static string ApplicationName = "创建程序信息";
        /// <summary>
        /// 最后保存者信息
        /// </summary>
        public static string LastAuthor = "最后保存者信息";
        /// <summary>
        /// 作者信息
        /// </summary>
        public static string Comments = "作者信息";
        /// <summary>
        /// 标题信息
        /// </summary>
        public static string Title = "标题信息";
        /// <summary>
        /// 主题信息
        /// </summary>
        public static string Subject = "主题信息";

        /// <summary>
        /// 创建Excel
        /// </summary>
        /// <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
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        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;
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        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();
        }

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        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
        /// <summary>
        /// 读取excel 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <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;
        }

        /// <summary>
        /// 读取Excel流到DataTable
        /// </summary>
        /// <param name="stream">Excel流</param>
        /// <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;
            }
        }

        /// <summary>
        /// 读取Excel流到DataTable
        /// </summary>
        /// <param name="stream">Excel流</param>
        /// <param name="sheetName">表单名</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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;
            }
        }

        /// <summary>
        /// 读取Excel流到DataSet
        /// </summary>
        /// <param name="stream">Excel流</param>
        /// <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;
            }
        }

        /// <summary>
        /// 读取Excel流到DataSet
        /// </summary>
        /// <param name="stream">Excel流</param>
        /// <param name="dict">字典参数，key：sheet名，value：列头所在行号，-1表示没有列头</param>
        /// <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;
            }
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <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中的数据
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        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;
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        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;
            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        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;
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        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
        /// <summary>
        /// 获取工作页的页数
        /// </summary>
        /// <param name="outputFile"></param>
        /// <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;
        }
        /// <summary>
        /// 获取工作页名称
        /// </summary>
        /// <param name="outputFile"></param>
        /// <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;

        }



        //////////  现用导出  \\\\\\\\\\  
        /// <summary>
        /// 用于Web导出                                                                                             第一步
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        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();
        }



        /// <summary>
        /// DataTable导出到Excel的MemoryStream                                                                      第二步
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        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;
            }
        }

        /// <summary>
        /// /注：分浏览器进行编码（IE必须编码，FireFox不能编码，Chrome可编码也可不编码）
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strFileName"></param>
        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();
        }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="sheetName">工作表名称</param>
        /// <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;
        }


        /// <summary>
        /// 验证导入的Excel是否有数据
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <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
        /// <summary>
        /// 获取枚举对象Key与显示名称的字典
        /// </summary>
        /// <param name="enumType"></param>
        /// <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;
        }

        /// <summary>
        /// 获取枚举对象显示名称与Key的字典
        /// </summary>
        /// <param name="enumType"></param>
        /// <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;
        }


        /// <summary>
        /// 获取枚举对象的值内容
        /// </summary>
        /// <param name="enumType"></param>
        /// <param name="display"></param>
        /// <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();
        }

        /// <summary>
        /// 获取枚举类型
        /// </summary>
        /// <param name="assembly"></param>
        /// <param name="typeName"></param>
        /// <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
        /// <summary>
        /// 枚举显示名(属性扩展)
        /// </summary>
        public class EnumDisplayNameAttribute : Attribute
        {
            private string _displayName;

            public EnumDisplayNameAttribute(string displayName)
            {
                this._displayName = displayName;
            }

            public string DisplayName
            {
                get { return _displayName; }
            }
        }

        public class EnumExt
        {
            /// <summary>
            /// 根据枚举成员获取自定义属性EnumDisplayNameAttribute的属性DisplayName
            /// </summary>
            /// <param name="o"></param>
            /// <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();
            }

            /// <summary>
            /// 根据枚举转换成SelectList
            /// </summary>
            /// <param name="enumType">枚举</param>
            /// <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;
            }
        }

        /// <summary>
        /// 根据枚举转换成SelectList并且设置默认选中项
        /// </summary>
        /// <param name="enumType"></param>
        /// <param name="ObjDefaultValue">默认选中项</param>
        /// <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;
        }

        /// <summary>
        /// 根据枚举转换成SelectList
        /// </summary>
        /// <param name="enumType">枚举</param>
        /// <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;
        }
        /// <summary>
        /// 根据枚举成员获取自定义属性EnumDisplayNameAttribute的属性DisplayName
        /// </summary>
        /// <param name="objEnumType"></param>
        /// <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;
        }

        /// <summary>
        /// 根据枚举成员获取DescriptionAttribute的属性Description
        /// </summary>
        /// <param name="o"></param>
        /// <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 新增扩展方法

        /// <summary>  
        /// 扩展方法：根据枚举值得到相应的枚举定义字符串  
        /// </summary>  
        /// <param name="value"></param>  
        /// <param name="enumType"></param>  
        /// <returns></returns>  
        public static String ToEnumString(this int value, Type enumType)
        {
            NameValueCollection nvc = GetEnumStringFromEnumValue(enumType);
            return nvc[value.ToString()];
        }

        /// <summary>  
        /// 根据枚举类型得到其所有的 值 与 枚举定义字符串 的集合  
        /// </summary>  
        /// <param name="enumType"></param>  
        /// <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;
        }

        /// <summary>  
        /// 扩展方法：根据枚举值得到属性Description中的描述, 如果没有定义此属性则返回空串  
        /// </summary>  
        /// <param name="value"></param>  
        /// <param name="enumType"></param>  
        /// <returns></returns>  
        public static String ToEnumDescriptionString(this int value, Type enumType)
        {
            NameValueCollection nvc = GetNVCFromEnumValue(enumType);
            return nvc[value.ToString()];
        }

        /// <summary>  
        /// 根据枚举类型得到其所有的 值 与 枚举定义Description属性 的集合  
        /// </summary>  
        /// <param name="enumType"></param>  
        /// <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
    }
}

Package

using 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\安装包\配置文件");

AES

using System;
using System.Security.Cryptography;
using System.Text;

namespace Helper.Encryption
{
    /// <summary>
    /// 加密
    /// </summary>
    public class AES
    {
        /// <summary>
        /// DES加密字符串
        /// </summary>
        /// <param name="encryptString">待加密的字符串</param>
        /// <param name="encryptKey">加密密钥,要求为8位</param>
        /// <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 "";
            }
        }

        /// <summary>
        /// DES解密字符串
        /// </summary>
        /// <param name="decryptString">待解密的字符串</param>
        /// <param name="decryptKey">解密密钥,要求为8位,和加密密钥相同</param>
        /// <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 "";
            }

        }
    }
}

DES

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace Helper.Encryption
{
    public class DES
    {
        /// <summary>
        /// 32位Key值：
        /// </summary>
        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加密
        /// <summary>
        /// DES加密
        /// </summary>
        /// <param name="strSource">待加密字串</param>
        /// <returns>加密后的字符串</returns>
        public static string DESEncrypt(string strSource)
        {
            return DESEncrypt(strSource, DESKey);
        }
        /// <summary>
        /// DES加密
        /// </summary>
        /// <param name="strSource">待加密字串</param>
        /// <param name="key">Key值</param>
        /// <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解密
        /// <summary>
        /// DES解密
        /// </summary>
        /// <param name="strSource">待解密的字串</param>
        /// <returns>解密后的字符串</returns>
        public static string DESDecrypt(string strSource)
        {
            return DESDecrypt(strSource, DESKey);
        }
        /// <summary>
        /// DES解密
        /// </summary>
        /// <param name="strSource">待解密的字串</param>
        /// <param name="key">32位Key值</param>
        /// <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
    }
}

MD5

using System.Security.Cryptography;
using System.Text;

namespace Helper.Encryption
{
    public  class MD5
    {
        #region MD5函数
        /// <summary>
        /// MD5函数,需引用：using System.Security.Cryptography;
        /// </summary>
        /// <param name="str">原始字符串</param>
        /// <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

    }
}

SHA256

using System;
using System.Security.Cryptography;
using System.Text;

namespace Helper.Encryption
{
    public partial class SHA256
    {
        #region SHA256函数
        /// <summary>
        /// SHA256函数
        /// </summary>
        /// /// <param name="str">原始字符串</param>
        /// <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
    }
}

Symmetric

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace Helper.Encryption
{
    /// <summary>
    /// 对称加密
    /// </summary>
    public class Symmetric
    {
        private SymmetricAlgorithm mobjCryptoService;
        private string Key;
        /// <summary>   
        /// 对称加密类的构造函数   
        /// </summary>   
        public Symmetric()
        {
            mobjCryptoService = new RijndaelManaged();
            Key = "Guz(%&hj7x89H$yuBI0456FtmaT5&fvHUFCy76*h%(HilJ$lhj!y6&(*jkP87jH7";
        }
        /// <summary>   
        /// 获得密钥   
        /// </summary>   
        /// <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);
        }
        /// <summary>   
        /// 获得初始向量IV   
        /// </summary>   
        /// <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);
        }
        /// <summary>   
        /// 加密方法   
        /// </summary>   
        /// <param name="Source">待加密的串</param>   
        /// <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);
        }
        /// <summary>   
        /// 解密方法   
        /// </summary>   
        /// <param name="Source">待解密的串</param>   
        /// <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

 /// <summary>
        /// 使用UCS2进行编码，用于发送Unicode字符
        /// </summary>
        /// <param name="str">短信内容</param>
        /// <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;
        }
 
        /// <summary>
        /// 对UCS2编码进行解码
        /// </summary>
        /// <param name="s">解码字符串</param>
        /// <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();
        }

Package

Install-Package ChineseConverter
Install-Package Microsoft.International.Converters.PinYinConverter -Version 1.0.0

ChineseHelper

using Microsoft.International.Converters.PinYinConverter;
using Microsoft.International.Converters.TraditionalChineseToSimplifiedConverter;
using Microsoft.International.Formatters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Helper
{
    public static class ChineseHelper
    {
        /// <summary>
        /// 获取拼音缩写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string GetSpellingAbbreviations(string str)
        {
            List<string> list = new List<string>();
            list.Add("");
            string tempStr = "";
            foreach (char c in str)
            {
                if (!ChineseChar.IsValidChar(c))
                {
                    for (int i = 0; i < list.Count; i++)
                        list[i] += c.ToString().ToLower();
                    continue;
                }
                ChineseChar cc = new ChineseChar(c);
                List<string> listChar = new List<string>();
                if (cc.PinyinCount > 0)
                {
                    for (int i = 0; i < cc.PinyinCount; i++)
                    {
                        listChar.Add(cc.Pinyins[i].Substring(0, 1).ToLower());
                    }
                    var listNewChar = listChar.Distinct().ToList();
                    if (listNewChar.Count > 1)
                    {
                        for (int i = 1; i < listNewChar.Count; i++)
                        {
                            int count = list.Count;
                            for (int j = 0; j < count; j++)
                            {
                                list.Add(list[j]);
                            }

                        }
                    }
                    for (int i = 0; i < listNewChar.Count; i++)
                    {
                        int count = list.Count / listNewChar.Count;

                        for (int j = i * count; j < list.Count; j++)
                        {
                            if (j < (i + 1) * count)
                                list[j] += listNewChar[i];
                        }

                    }
                }
                else
                {
                    for (int i = 0; i < list.Count; i++)
                        list[i] += c.ToString().ToLower();
                }
            }
            foreach (var item in list)
            {
                tempStr += item + ";";
            }
            return tempStr;
        }
        /// <summary>
        /// 获取拼音缩写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static List<List<string>> PinYinAbbreviation(string str)
        {
            List<List<string>> list = new List<List<string>>();
            foreach (char c in str)
            {
                List<string> listChar = new List<string>();
                if (ChineseChar.IsValidChar(c))
                {
                    ChineseChar cc = new ChineseChar(c);
                    for (int i = 0; i < cc.PinyinCount; i++)
                    {
                        listChar.Add(cc.Pinyins[i].Substring(0,1));
                    }
                }
                else
                {
                    listChar.Add(c.ToString());
                }
                list.Add(listChar);
            }
            return list;
        }
        /// <summary>
        /// 获取拼音全拼
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static List<List<string>> PinYinFull(string str)
        {
            List<List<string>> list = new List<List<string>>();
            foreach (char c in str)
            {
                List<string> listChar = new List<string>();
                if (ChineseChar.IsValidChar(c))
                {
                    ChineseChar cc = new ChineseChar(c);
                    for (int i = 0; i < cc.PinyinCount; i++)
                    {
                        listChar.Add(cc.Pinyins[i]);
                    }
                }
                else
                {
                    listChar.Add(c.ToString());
                }
                list.Add(listChar);
            }
            return list;
        }
        /// <summary>
        /// 获取汉字，简体转繁体
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string SimplifiedToTraditional(string str)
        {
            return ChineseConverter.Convert(str, ChineseConversionDirection.SimplifiedToTraditional);
        }
        /// <summary>
        /// 获取汉字，繁体转简体
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string TraditionalToSimplified(string str)
        {
            return ChineseConverter.Convert(str, ChineseConversionDirection.TraditionalToSimplified);
        }
        /// <summary>
        /// 将数字货币转换为大写
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public static string NumericToLC(decimal number)
        {
            return string.Format(new EastAsiaNumericFormatter(), "{0:Lc}", number);
        }
        /// <summary>
        /// 强数字货币转换为小写
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public static string NumericToLN(decimal number)
        {
            return string.Format(new EastAsiaNumericFormatter(), "{0:Ln}", number);
        }
    }
}

Config

<connectionStrings>
    <add name="DBHelper" connectionString="Data Source=.;Initial Catalog=DB_Helper;User ID=sa;password=123456;" providerName="System.Data.SqlClient" />
</connectionStrings>

DBHelper

using System;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace Helper
{
    /// <summary>
    /// 数据库访问帮助类
    /// 必备
    /// System.Data
    /// System.Configuration
    /// </summary>
    public class DBHelper
    {

        //托管对象
        private static string dbProviderName;
        //连接字符串
        private static string dbConnectionString;
        //数据库连接
        private DbConnection connection;
        /// <summary>
        /// 构造函数
        /// 初始化数据库连接
        /// </summary>
        public DBHelper()
        {
            string name = this.GetType().Name;
            dbProviderName = ConfigurationManager.ConnectionStrings[name].ProviderName;
            dbConnectionString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
            this.connection = CreateConnection(DBHelper.dbConnectionString);
        }
        public DBHelper(string providerName)
        {
            dbProviderName = ConfigurationManager.ConnectionStrings[providerName].ProviderName;
            dbConnectionString = ConfigurationManager.ConnectionStrings[providerName].ConnectionString;
            this.connection = CreateConnection(DBHelper.dbConnectionString);
        }

        /// <summary>
        /// 构造函数
        /// 初始化数据库连接
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="connectionString"></param>
        public DBHelper(string providerName, string connectionString)
        {
            dbProviderName = providerName;
            dbConnectionString = connectionString;
            this.connection = CreateConnection(connectionString);
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        public static DbConnection CreateConnection()
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = DBHelper.dbConnectionString;
            return dbconn;
        }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public static DbConnection CreateConnection(string connectionString)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = connectionString;
            return dbconn;
        }

        /// <summary>
        /// 执行的存储过程
        /// </summary>
        /// <param name="storedProcedure">设置针对数据源运行的文本命令</param>
        /// <returns></returns>
        public DbCommand GetStoredProcCommand(string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            return dbCommand;
        }
        /// <summary>
        /// 执行的sql语句
        /// </summary>
        /// <param name="sqlQuery">sql语句</param>
        /// <returns></returns>
        public DbCommand GetSqlStringCommand(string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
            return dbCommand;
        }

        #region 增加参数

        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="dbParameterCollection">参数集合</param>
        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }
        /// <summary>
        /// 增加输出参数 适用于存储过程
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }
        /// <summary>
        /// 增加参数列表
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">参数值</param>
        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }
        /// <summary>
        /// 增加返回参数 适用于存储过程
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="dbType">参数类型</param>
        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameterName">参数名</param>
        /// <returns></returns>
        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        #region 执行

        /// <summary>
        /// 执行sql或者存储过程返回一个DataSet对象
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行sql或者存储过程返回一个DataTable对象
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }
        /// <summary>
        /// 执行sql语句或者存储过程返回一个DbDataReader对象
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            cmd.Connection.Open();
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        /// <summary>
        /// 执行一条sql语句或者存储过程，返回受影响的行数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection.Open();
            int ret = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return ret;
        }
        /// <summary>
        /// 执行一条sql语句或者存储过程，返回object对象
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <returns></returns>
        public object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection.Open();
            object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return ret;
        }

        #endregion

        #region 执行事务

        /// <summary>
        /// 执行sql或者存储过程返回一个dataSet对象+事务
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="t">事务执行操作</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行sql或者存储过程返回一个DataTable对象+事务
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="t">事务执行操作</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }
        /// <summary>
        /// 执行sql或者存储过程返回一个DbDataReader对象+事务
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="t">事务执行操作</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            return reader;
        }
        /// <summary>
        /// 执行一条sql语句或者存储过程，返回受影响的行数+事务
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="t">事务执行操作</param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            int ret = cmd.ExecuteNonQuery();
            return ret;
        }
        /// <summary>
        /// 执行sql或者存储过程返回一个object对象+事务
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="t">事务执行操作</param>
        /// <returns></returns>
        public object ExecuteScalar(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            object ret = cmd.ExecuteScalar();
            return ret;
        }

        #endregion
    }


    public class Trans : IDisposable
    {
        //数据库连接
        private DbConnection conn;
        //事务
        private DbTransaction dbTrans;

        /// <summary>
        /// 数据库连接
        /// </summary>
        public DbConnection DbConnection
        {
            get { return this.conn; }
        }
        /// <summary>
        /// 事务
        /// </summary>
        public DbTransaction DbTrans
        {
            get { return this.dbTrans; }
        }

        /// <summary>
        /// 构造函数
        /// 打开数据库连接
        /// 开启事务
        /// </summary>
        public Trans()
        {
            conn = DBHelper.CreateConnection();
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
        /// <summary>
        /// 构造函数
        /// 打开数据库连接
        /// 开启事务
        /// </summary>
        /// <param name="connectionString">数据库连接</param>
        public Trans(string connectionString)
        {
            conn = DBHelper.CreateConnection(connectionString);
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public void Commit()
        {
            dbTrans.Commit();
            this.Colse();
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollBack()
        {
            dbTrans.Rollback();
            this.Colse();
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            this.Colse();
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Colse()
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

如果您看到这篇文章,表示您的 blog 已经安装成功.