442 lines
17 KiB
C#
442 lines
17 KiB
C#
|
using PCHMI;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.ComponentModel;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Data.OleDb;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Reflection;
|
|||
|
|
using System.Runtime.Serialization.Formatters.Binary;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
using System.Windows.Forms;
|
|||
|
|
using static System.Net.Mime.MediaTypeNames;
|
|||
|
|
|
|||
|
|
namespace CowainHmi.Alarm
|
|||
|
|
{
|
|||
|
|
public class AlarmExcelHelper
|
|||
|
|
{
|
|||
|
|
public static DataTable ReadReleaseExcel<T>(string fileName, BindingList<T> bindingList)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string text = new FILE().GET_RELEASE_DIR() + $"\\DATA\\{fileName}.xls";
|
|||
|
|
//string text = Environment.CurrentDirectory + $"\\DATA\\{fileName}.xls";
|
|||
|
|
if (!File.Exists(text))
|
|||
|
|
{
|
|||
|
|
SaveBindingListToExcel(bindingList, fileName);
|
|||
|
|
}
|
|||
|
|
if (File.Exists(text))
|
|||
|
|
{
|
|||
|
|
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConnection);
|
|||
|
|
dataAdapter.SelectCommand = selectCommand;
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
dataAdapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
new PClass().WErrTxt("读取报警表格异常:" + ex.ToString());
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Close();
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection?.Close();
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataTable ReadErrExcel(string fileName)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string text = System.Windows.Forms.Application.StartupPath + $"\\DATA\\{fileName}.xls";
|
|||
|
|
if (File.Exists(text))
|
|||
|
|
{
|
|||
|
|
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", oleDbConnection);
|
|||
|
|
dataAdapter.SelectCommand = selectCommand;
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
dataAdapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
new PClass().WErrTxt("读取报警表格异常:" + ex.ToString());
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Close();
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection?.Close();
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 更新实时报警表格
|
|||
|
|
/// </summary>
|
|||
|
|
public static void UpdateAlarmRT(string pid, string type, string group, string id, string error, bool rw, string fileName)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (PClass.SystemRun)
|
|||
|
|
{
|
|||
|
|
string text = System.Windows.Forms.Application.StartupPath + $"\\UDTA\\{fileName}.xls";
|
|||
|
|
if (!File.Exists(text))
|
|||
|
|
{
|
|||
|
|
new JUDGE_DIR().CreateExcelFile(text, "AlarmLog", new string[]
|
|||
|
|
{
|
|||
|
|
"PLC",
|
|||
|
|
"DTIM",
|
|||
|
|
"报警类别",
|
|||
|
|
"报警组",
|
|||
|
|
"ID",
|
|||
|
|
"ErrText",
|
|||
|
|
"TP",
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
DataTable dt = GetAlarmRT(fileName);
|
|||
|
|
var dr = dt.Select($"报警类别='{type}' and 报警组='{group}' and ID='{id}'").FirstOrDefault();
|
|||
|
|
string sql = String.Empty;
|
|||
|
|
if (dr == null)
|
|||
|
|
{
|
|||
|
|
if (rw)
|
|||
|
|
{
|
|||
|
|
sql =
|
|||
|
|
$"insert into [AlarmLog$] (PLC,报警类别,报警组,ID,DTIM,TP,ErrText) values('{pid}','{type}','{group}','{id}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}','1','{error}')";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if (rw)
|
|||
|
|
{
|
|||
|
|
//更新记录
|
|||
|
|
sql =
|
|||
|
|
$"UPDATE [AlarmLog$] SET DTIM='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}',TP='1' WHERE 报警类别='{type}' and 报警组='{group}' and ID='{id}' ";
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
//删除记录
|
|||
|
|
sql =
|
|||
|
|
$"UPDATE [AlarmLog$] SET DTIM='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}',TP='0' WHERE 报警类别='{type}' and 报警组='{group}' and ID='{id}' ";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(sql))
|
|||
|
|
{
|
|||
|
|
new OleDbCommand(sql, oleDbConnection).ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection?.Close();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static DataTable GetAlarmRT(string fileName)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string text = System.Windows.Forms.Application.StartupPath + $"\\UDTA\\{fileName}.xls";
|
|||
|
|
if (File.Exists(text))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [AlarmLog$]", oleDbConnection);
|
|||
|
|
dataAdapter.SelectCommand = selectCommand;
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
dataAdapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
new PClass().WErrTxt("读取实时报警记录错误:" + ex.ToString());
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Close();
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection?.Close();
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
public static void SaveAlarmLog(string pid, string type, string group, string id, string tp, string error)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (PClass.SystemRun)
|
|||
|
|
{
|
|||
|
|
string text = System.Windows.Forms.Application.StartupPath + "\\UDTA\\AlarmDirectory";
|
|||
|
|
text = text + "\\" + DateTime.Now.ToLocalTime().ToString("yyMMdd") + ".xls";
|
|||
|
|
if (!File.Exists(text))
|
|||
|
|
{
|
|||
|
|
new JUDGE_DIR().CreateExcelFile(text, "AlarmLog", new string[]
|
|||
|
|
{
|
|||
|
|
"PLC",
|
|||
|
|
"报警类别",
|
|||
|
|
"报警组",
|
|||
|
|
"ID",
|
|||
|
|
"DTIM",
|
|||
|
|
"TP",
|
|||
|
|
"ErrText"
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
string sql =
|
|||
|
|
$"insert into [AlarmLog$] (PLC,报警类别,报警组,ID,DTIM,TP,ErrText) values('{pid}','{type}','{group}','{id}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffff")}','{tp}','{error}')";
|
|||
|
|
new OleDbCommand(sql, oleDbConnection).ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection?.Close();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static DataTable GetAlarmLog(string fileName)
|
|||
|
|
{
|
|||
|
|
OleDbConnection oleDbConnection = null;
|
|||
|
|
|
|||
|
|
string text = System.Windows.Forms.Application.StartupPath + "\\UDTA\\AlarmDirectory";//Application.StartupPath为文件执行路径
|
|||
|
|
text = text + "\\" + fileName + ".xls";
|
|||
|
|
if (File.Exists(text))//判断文件是否存在
|
|||
|
|
{
|
|||
|
|
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
|
|||
|
|
oleDbConnection = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + text + ";Extended Properties=Excel 8.0;");
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Open();
|
|||
|
|
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [AlarmLog$]", oleDbConnection);
|
|||
|
|
dataAdapter.SelectCommand = selectCommand;
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
dataAdapter.Fill(dt);
|
|||
|
|
return dt;
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
new PClass().WErrTxt("读取历史报警记录错误:" + ex.ToString());
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
oleDbConnection.Close();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
public static BindingList<T> GetDataList<T>(DataTable dt)
|
|||
|
|
{
|
|||
|
|
if (dt == null)
|
|||
|
|
{
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
var list = new BindingList<T>();
|
|||
|
|
var plist = new List<PropertyInfo>(typeof(T).GetProperties());
|
|||
|
|
foreach (DataRow item in dt.Rows)
|
|||
|
|
{
|
|||
|
|
T s = Activator.CreateInstance<T>();
|
|||
|
|
for (int i = 0; i < dt.Columns.Count; i++)
|
|||
|
|
{
|
|||
|
|
PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
|
|||
|
|
if (info != null)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!Convert.IsDBNull(item[i]))
|
|||
|
|
{
|
|||
|
|
object v = null;
|
|||
|
|
if (info.PropertyType.ToString().Contains("System.Nullable"))
|
|||
|
|
{
|
|||
|
|
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
v = Convert.ChangeType(item[i], info.PropertyType);
|
|||
|
|
}
|
|||
|
|
info.SetValue(s, v, null);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
list.Add(s);
|
|||
|
|
}
|
|||
|
|
return list;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public class EnumItemModel<T>
|
|||
|
|
{
|
|||
|
|
public string EnumName { get; set; }
|
|||
|
|
public int EnumValue { get; set; }
|
|||
|
|
public string Description { get; set; }
|
|||
|
|
public T EnumItem { get; set; }
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static List<EnumItemModel<T>> GetEnumItems<T>() where T : Enum
|
|||
|
|
{
|
|||
|
|
// 获取枚举的类型信息
|
|||
|
|
Type enumType = typeof(T);
|
|||
|
|
|
|||
|
|
// 获取枚举的名称数组和值数组
|
|||
|
|
string[] enumNames = Enum.GetNames(enumType);
|
|||
|
|
Array enumValues = Enum.GetValues(enumType);
|
|||
|
|
|
|||
|
|
// 创建一个列表来存储枚举项模型对象
|
|||
|
|
List<EnumItemModel<T>> enumItems = new List<EnumItemModel<T>>();
|
|||
|
|
|
|||
|
|
// 遍历枚举的名称和值数组
|
|||
|
|
for (int i = 0; i < enumNames.Length; i++)
|
|||
|
|
{
|
|||
|
|
string name = enumNames[i];
|
|||
|
|
//int value = 0;
|
|||
|
|
int value = Convert.ToInt32(enumValues.GetValue(i));
|
|||
|
|
string description = GetEnumDescription(enumType, name);
|
|||
|
|
|
|||
|
|
// 创建一个枚举项模型对象，并将其添加到列表中
|
|||
|
|
EnumItemModel<T> enumItem = new EnumItemModel<T>
|
|||
|
|
{
|
|||
|
|
EnumName = name,
|
|||
|
|
EnumValue = value,
|
|||
|
|
Description = description,
|
|||
|
|
EnumItem = (T)Enum.Parse(typeof(T), name)
|
|||
|
|
};
|
|||
|
|
enumItems.Add(enumItem);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 返回枚举项列表
|
|||
|
|
return enumItems;
|
|||
|
|
}
|
|||
|
|
public static string GetEnumDescription(Type enumType, string enumName)
|
|||
|
|
{
|
|||
|
|
// 使用反射获取枚举项的描述属性
|
|||
|
|
FieldInfo fieldInfo = enumType.GetField(enumName);
|
|||
|
|
DescriptionAttribute[] attributes = (DescriptionAttribute[])fieldInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);
|
|||
|
|
|
|||
|
|
// 如果存在描述属性，则返回描述值，否则返回空字符串
|
|||
|
|
return (attributes.Length > 0) ? attributes[0].Description : string.Empty;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static BindingList<T> DeepCopyList<T>(BindingList<T> other)
|
|||
|
|
{
|
|||
|
|
using (MemoryStream ms = new MemoryStream())
|
|||
|
|
{
|
|||
|
|
BinaryFormatter formatter = new BinaryFormatter();
|
|||
|
|
formatter.Serialize(ms, other);
|
|||
|
|
ms.Position = 0;
|
|||
|
|
return (BindingList<T>)formatter.Deserialize(ms);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void SaveBindingListToExcel<T>(BindingList<T> bindingList, string fileName)
|
|||
|
|
{
|
|||
|
|
string filePath = new FILE().GET_RELEASE_DIR() + $"\\DATA\\{fileName}.xls";
|
|||
|
|
//string filePath = Environment.CurrentDirectory + $"\\DATA\\{fileName}.xls";
|
|||
|
|
File.Delete(filePath);
|
|||
|
|
// 创建数据表
|
|||
|
|
DataTable dataTable = new DataTable(typeof(T).Name);
|
|||
|
|
|
|||
|
|
// 添加列
|
|||
|
|
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
|
|||
|
|
foreach (PropertyDescriptor prop in properties)
|
|||
|
|
{
|
|||
|
|
dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 添加行
|
|||
|
|
foreach (T item in bindingList)
|
|||
|
|
{
|
|||
|
|
DataRow row = dataTable.NewRow();
|
|||
|
|
foreach (PropertyDescriptor prop in properties)
|
|||
|
|
{
|
|||
|
|
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
|
|||
|
|
}
|
|||
|
|
dataTable.Rows.Add(row);
|
|||
|
|
}
|
|||
|
|
// 使用OleDb写入Excel
|
|||
|
|
using (OleDbConnection conn = new OleDbConnection(CONFIG.Is64Str() + "Data Source=" + filePath + ";Extended Properties=Excel 8.0;"))
|
|||
|
|
{
|
|||
|
|
conn.Open();
|
|||
|
|
string sheetName = "Sheet1";
|
|||
|
|
using (OleDbCommand cmd = new OleDbCommand($"CREATE TABLE [{sheetName}] ({string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => $"[{c.ColumnName}] {GetOleDbType(c.DataType)}"))})", conn))
|
|||
|
|
{
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (OleDbDataAdapter adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}]", conn))
|
|||
|
|
{
|
|||
|
|
using (OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter))
|
|||
|
|
{
|
|||
|
|
adapter.Update(dataTable);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private static string GetOleDbType(Type dataType)
|
|||
|
|
{
|
|||
|
|
if (dataType == typeof(string))
|
|||
|
|
return "Text";
|
|||
|
|
else if (dataType == typeof(int))
|
|||
|
|
return "Integer";
|
|||
|
|
else if (dataType == typeof(double))
|
|||
|
|
return "Double";
|
|||
|
|
else if (dataType == typeof(DateTime))
|
|||
|
|
return "DateTime";
|
|||
|
|
else
|
|||
|
|
return "Variant";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
