using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
namespace AZ.EPricing.Utility
{
public static class Utilities
{
/// <summary>
/// DataTable 转成 T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static T FillModel<T>(DataTable dt) where T : new()
{
if (dt == null || dt.Rows.Count < 1) // 判断ds的null和是否包含数据
{
return new T();
}
T model = new T();
PropertyInfo[] propertys = model.GetType().GetProperties(); // 获得此模型的公共属性
foreach (PropertyInfo pi in propertys) // 遍历该对象的所有属性
{
if (dt.Columns.Contains(pi.Name)) // 检查DataTable是否包含此列（列名==对象的属性名）
{
object value = dt.Rows[0][pi.Name]; // 取值
if (value != DBNull.Value) // 如果非空，则赋给对象的属性
{
pi.SetValue(model, value, null);
}
}
}
return model;
}
/// <summary>
/// DataTable 转成List<T>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List<T> FillModelList<T>(DataTable dt) where T : new()
{
if (dt == null || dt.Rows.Count < 1) // 判断ds的null和是否包含数据
{
return null;
}
List<T> list = new List<T>();
foreach (DataRow dr in dt.Rows)
{
T model = new T();
PropertyInfo[] propertys = model.GetType().GetProperties(); // 获得此模型的公共属性
foreach (PropertyInfo pi in propertys) // 遍历该对象的所有属性
{
if (dt.Columns.Contains(pi.Name)) // 检查DataTable是否包含此列（列名==对象的属性名）
{
object value = dr[pi.Name]; // 取值
if (value != DBNull.Value) // 如果非空，则赋给对象的属性
{
pi.SetValue(model, value, null);
}
}
}
list.Add(model);
}
return list;
}
/// <summary>
/// 根据DataTable导出Excel到内存
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public static MemoryStream Export1(DataTable dtSource, string pathFile)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("dd/mm/yyyy");//yyyy-mm-dd
dateStyle.Alignment = HorizontalAlignment.Center;
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.BorderTop = BorderStyle.Thin;
HSSFCellStyle tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();
tdStyle.Alignment = HorizontalAlignment.Center;
tdStyle.BorderBottom = BorderStyle.Thin;
tdStyle.BorderLeft = BorderStyle.Thin;
tdStyle.BorderRight = BorderStyle.Thin;
tdStyle.BorderTop = BorderStyle.Thin;
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
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);
}
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = tdStyle;
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);
if (drValue != "")
{
newCell.SetCellValue(dateV);
newCell.CellStyle = tdStyle;
newCell.CellStyle = dateStyle;//格式化时间显示
}
else
{
newCell.SetCellValue(drValue);
}
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);
newCell.CellStyle = tdStyle;//cqz添加
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("");
newCell.CellStyle = tdStyle;//cqz添加
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strFileName">保存位置</param>
public static void Export(DataTable dtSource, string strFileName)
{
using (MemoryStream ms = Export1(dtSource, strFileName))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
fs.Dispose();
}
}
}
#region JsonHelper
/// <summary>
/// 将对象序列化为JSON格式
/// </summary>
/// <param name="o">对象</param>
/// <returns>json字符串</returns>
public static string SerializeObject(object o)
{
string json = JsonConvert.SerializeObject(o);
return json;
}
/// <summary>
/// 解析JSON字符串生成对象实体
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="json">json字符串(eg.{"ID":"112","Name":"石子儿"})</param>
/// <returns>对象实体</returns>
public static T DeserializeJsonToObject<T>(string json) where T : class
{
JsonSerializer serializer = new JsonSerializer();
StringReader sr = new StringReader(json);
object o = serializer.Deserialize(new JsonTextReader(sr), typeof(T));
T t = o as T;
return t;
}
/// <summary>
/// 解析JSON数组生成对象实体集合
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="json">json数组字符串(eg.[{"ID":"112","Name":"石子儿"}])</param>
/// <returns>对象实体集合</returns>
public static List<T> DeserializeJsonToList<T>(string json) where T : class
{
JsonSerializer serializer = new JsonSerializer();
StringReader sr = new StringReader(json);
object o = serializer.Deserialize(new JsonTextReader(sr), typeof(List<T>));
List<T> list = o as List<T>;
return list;
}
/// <summary>
/// 反序列化JSON到给定的匿名对象.
/// </summary>
/// <typeparam name="T">匿名对象类型</typeparam>
/// <param name="json">json字符串</param>
/// <param name="anonymousTypeObject">匿名对象</param>
/// <returns>匿名对象</returns>
public static T DeserializeAnonymousType<T>(string json, T anonymousTypeObject)
{
T t = JsonConvert.DeserializeAnonymousType(json, anonymousTypeObject);
return t;
}
#endregion
#region AES加密解密
public static string AesEncrypt(string encryptStr)
{
string AESKey = "epricing@163epricing216@epricing";//长度32位
string AESIV = "@816epricing816@";//长度16位
byte[] keyArray = UTF8Encoding.UTF8.GetBytes(AESKey);
byte[] ivArray = UTF8Encoding.UTF8.GetBytes(AESIV);
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(encryptStr);
RijndaelManaged rDel = new RijndaelManaged();
rDel.Key = keyArray;
rDel.IV = ivArray;
rDel.Mode = CipherMode.CBC;
rDel.Padding = PaddingMode.PKCS7;
ICryptoTransform cTransform = rDel.CreateEncryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}
public static string AesDecrypt(string decryptStr)
{
string AESKey = "epricing@163epricing216@epricing";//长度32位
string AESIV = "@816epricing816@";//长度16位
byte[] keyArray = UTF8Encoding.UTF8.GetBytes(AESKey);
byte[] ivArray = UTF8Encoding.UTF8.GetBytes(AESIV);
byte[] toEncryptArray = Convert.FromBase64String(decryptStr);
RijndaelManaged rDel = new RijndaelManaged();
rDel.Key = keyArray;
rDel.IV = ivArray;
rDel.Mode = CipherMode.CBC;
rDel.Padding = PaddingMode.PKCS7;
ICryptoTransform cTransform = rDel.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
string conn = UTF8Encoding.UTF8.GetString(resultArray);
return conn;
}
#endregion
}
}
