288 lines
13 KiB
C#
288 lines
13 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.IO;
|
|
using System.Data.OleDb;
|
|
using System.Text;
|
|
using System.ComponentModel;
|
|
using System.Windows.Forms;
|
|
|
|
namespace LFP_Manager.Function
|
|
{
|
|
public static class csExcelExport
|
|
{
|
|
public static bool IsHexColumn(string columnName)
|
|
{
|
|
if (string.IsNullOrEmpty(columnName)) return false;
|
|
switch (columnName.Trim().ToLower())
|
|
{
|
|
case "op_status":
|
|
case "bms_status":
|
|
case "status":
|
|
case "alarm_status":
|
|
case "alarm":
|
|
case "warning":
|
|
case "protect":
|
|
case "protection":
|
|
case "fault":
|
|
case "error":
|
|
case "error_code":
|
|
case "cellballancestatus":
|
|
return true;
|
|
default:
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public static string FormatHexValue(object value)
|
|
{
|
|
if (value == null || value == DBNull.Value)
|
|
return "0x0000";
|
|
|
|
string valStr = value.ToString().Trim('\0').Trim();
|
|
if (string.IsNullOrEmpty(valStr))
|
|
return "0x0000";
|
|
|
|
if (valStr.StartsWith("0x", StringComparison.OrdinalIgnoreCase))
|
|
return valStr;
|
|
|
|
if (long.TryParse(valStr, out long longVal))
|
|
{
|
|
// 16비트 signed 음수 (-32768 ~ -1) -> 0x8000 ~ 0xFFFF
|
|
if (longVal >= short.MinValue && longVal < 0)
|
|
{
|
|
ushort uVal = (ushort)((short)longVal);
|
|
return string.Format("0x{0:X4}", uVal);
|
|
}
|
|
// 16비트 unsigned 범위 (0 ~ 65535)
|
|
else if (longVal >= 0 && longVal <= 0xFFFF)
|
|
{
|
|
return string.Format("0x{0:X4}", (ushort)longVal);
|
|
}
|
|
// 32비트 signed 음수
|
|
else if (longVal >= int.MinValue && longVal < 0)
|
|
{
|
|
uint uVal = (uint)((int)longVal);
|
|
return string.Format("0x{0:X8}", uVal);
|
|
}
|
|
// 32비트 unsigned 범위
|
|
else if (longVal >= 0 && longVal <= 0xFFFFFFFFL)
|
|
{
|
|
return string.Format("0x{0:X8}", (uint)longVal);
|
|
}
|
|
else
|
|
{
|
|
return string.Format("0x{0:X}", longVal);
|
|
}
|
|
}
|
|
|
|
return valStr;
|
|
}
|
|
|
|
public static void ExportToExcel(this DataTable dataTable, String filePath, bool overwiteFile = true)
|
|
{
|
|
if (Directory.Exists(Path.GetDirectoryName(filePath)) == false)
|
|
Directory.CreateDirectory(Path.GetDirectoryName(filePath));
|
|
if (File.Exists(filePath) && overwiteFile)
|
|
File.Delete(filePath);
|
|
|
|
//var conn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';", filePath);
|
|
var conn = "";
|
|
|
|
if (filePath.IndexOf(".xlsx") > -1) // 확장자에 따라서 provider 주의
|
|
conn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=3';", filePath);
|
|
else
|
|
conn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=3';", filePath);
|
|
|
|
using (OleDbConnection connection = new OleDbConnection(conn))
|
|
{
|
|
connection.Open();
|
|
using (OleDbCommand command = new OleDbCommand())
|
|
{
|
|
command.Connection = connection;
|
|
List<String> columnNames = new List<string>();
|
|
List<String> columnTypes = new List<string>();
|
|
foreach (DataColumn dataColumn in dataTable.Columns)
|
|
{
|
|
columnNames.Add(dataColumn.ColumnName);
|
|
string tName = "VARCHAR";
|
|
if (IsHexColumn(dataColumn.ColumnName))
|
|
{
|
|
tName = "VARCHAR";
|
|
}
|
|
else
|
|
{
|
|
switch (dataColumn.DataType.Name)
|
|
{
|
|
case "Int16": tName = "INTEGER"; break;
|
|
case "Int32": tName = "INTEGER"; break;
|
|
case "Int64": tName = "INTEGER"; break;
|
|
case "Double": tName = "DOUBLE"; break;
|
|
case "String": tName = "VARCHAR"; break;
|
|
default: tName = dataColumn.DataType.Name; break;
|
|
}
|
|
}
|
|
columnTypes.Add(tName);
|
|
}
|
|
String tableName = !String.IsNullOrWhiteSpace(dataTable.TableName) ? dataTable.TableName : Guid.NewGuid().ToString();
|
|
//command.CommandText = @"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.Select(c => $"[{c}] VARCHAR").ToArray())});";
|
|
//string join = String.Join(",", columnNames.Select(c => String.Format("[{0}] VARCHAR", c)).ToArray());
|
|
string join = "";
|
|
for (int i = 0; i < columnNames.Count; i++)
|
|
{
|
|
join += String.Format("[{0}] {1}", columnNames[i], columnTypes[i]);
|
|
if (i < (columnNames.Count - 1)) join += ",";
|
|
}
|
|
command.CommandText = String.Format("CREATE TABLE [{0}] ({1});",
|
|
tableName,
|
|
join
|
|
);
|
|
command.ExecuteNonQuery();
|
|
|
|
foreach (DataRow row in dataTable.Rows)
|
|
{
|
|
List<String> rowValues = new List<string>();
|
|
foreach (DataColumn column in dataTable.Columns)
|
|
{
|
|
if (IsHexColumn(column.ColumnName))
|
|
{
|
|
rowValues.Add(FormatHexValue(row[column]));
|
|
}
|
|
else
|
|
{
|
|
rowValues.Add((row[column] != null && row[column] != DBNull.Value) ? row[column].ToString() : String.Empty);
|
|
}
|
|
}
|
|
//command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.Select(c => $"[{c}]"))}) VALUES ({String.Join(",", rowValues.Select(r => $"'{r}'").ToArray())});";
|
|
string a = String.Join(",", columnNames.Select(c => String.Format("[{0}]", c)));
|
|
string b = String.Join(",", rowValues.Select(r => String.Format("'{0}'", (r == "") ? "0" : r)).ToArray());
|
|
command.CommandText = String.Format("INSERT INTO [{0}]({1}) VALUES ({2});", tableName, a, b);
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
public static void ExportToExcelExt(this DataTable dataTable, String filePath, bool overwiteFile = true)
|
|
{
|
|
string result = String.Empty;
|
|
|
|
Forms.fmxWait WaitForm = new Forms.fmxWait();
|
|
WaitForm.StartPosition = FormStartPosition.CenterScreen;
|
|
WaitForm.ShowOnTopMode = DevExpress.XtraWaitForm.ShowFormOnTopMode.AboveParent;
|
|
|
|
WaitForm.Show();
|
|
|
|
try
|
|
{
|
|
if (Directory.Exists(Path.GetDirectoryName(filePath)) == false)
|
|
Directory.CreateDirectory(Path.GetDirectoryName(filePath));
|
|
if (File.Exists(filePath) && overwiteFile)
|
|
File.Delete(filePath);
|
|
|
|
//var conn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';", filePath);
|
|
var conn = "";
|
|
|
|
if (filePath.IndexOf(".xlsx") > -1) // 확장자에 따라서 provider 주의
|
|
conn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=3';", filePath);
|
|
else
|
|
conn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=3';", filePath);
|
|
|
|
using (OleDbConnection connection = new OleDbConnection(conn))
|
|
{
|
|
connection.Open();
|
|
using (OleDbCommand command = new OleDbCommand())
|
|
{
|
|
command.Connection = connection;
|
|
List<String> columnNames = new List<string>();
|
|
List<String> columnTypes = new List<string>();
|
|
foreach (DataColumn dataColumn in dataTable.Columns)
|
|
{
|
|
columnNames.Add(dataColumn.ColumnName);
|
|
string tName = "VARCHAR";
|
|
if (IsHexColumn(dataColumn.ColumnName))
|
|
{
|
|
tName = "VARCHAR";
|
|
}
|
|
else
|
|
{
|
|
switch (dataColumn.DataType.Name)
|
|
{
|
|
case "Boolean": tName = "VARCHAR"; break;
|
|
case "Int16": tName = "INTEGER"; break;
|
|
case "Int32": tName = "INTEGER"; break;
|
|
case "Int64": tName = "INTEGER"; break;
|
|
case "Double": tName = "DOUBLE"; break;
|
|
case "Decimal": tName = "DOUBLE"; break;
|
|
case "String": tName = "VARCHAR"; break;
|
|
default: tName = dataColumn.DataType.Name; break;
|
|
}
|
|
}
|
|
columnTypes.Add(tName);
|
|
}
|
|
String tableName = !String.IsNullOrWhiteSpace(dataTable.TableName) ? dataTable.TableName : Guid.NewGuid().ToString();
|
|
//command.CommandText = @"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.Select(c => $"[{c}] VARCHAR").ToArray())});";
|
|
//string join = String.Join(",", columnNames.Select(c => String.Format("[{0}] VARCHAR", c)).ToArray());
|
|
string join = "";
|
|
for (int i = 0; i < columnNames.Count; i++)
|
|
{
|
|
join += String.Format("[{0}] {1}", columnNames[i], columnTypes[i]);
|
|
if (i < (columnNames.Count - 1)) join += ",";
|
|
}
|
|
command.CommandText = String.Format("CREATE TABLE [{0}] ({1});",
|
|
tableName,
|
|
join
|
|
);
|
|
command.ExecuteNonQuery();
|
|
|
|
int rNo = 0;
|
|
|
|
foreach (DataRow row in dataTable.Rows)
|
|
{
|
|
List<String> rowValues = new List<string>();
|
|
foreach (DataColumn column in dataTable.Columns)
|
|
{
|
|
if (IsHexColumn(column.ColumnName))
|
|
{
|
|
rowValues.Add(FormatHexValue(row[column]));
|
|
}
|
|
else
|
|
{
|
|
rowValues.Add((row[column] != null && row[column] != DBNull.Value) ? row[column].ToString().Trim('\0') : String.Empty);
|
|
}
|
|
}
|
|
//command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.Select(c => $"[{c}]"))}) VALUES ({String.Join(",", rowValues.Select(r => $"'{r}'").ToArray())});";
|
|
string a = String.Join(",", columnNames.Select(c => String.Format("[{0}]", c)));
|
|
string b = String.Join(",", rowValues.Select(r => String.Format("'{0}'", (r == "") ? "0" : r)).ToArray());
|
|
|
|
command.CommandText = String.Format("INSERT INTO [{0}]({1}) VALUES ({2});", tableName, a, b);
|
|
command.ExecuteNonQuery();
|
|
|
|
rNo++;
|
|
|
|
WaitForm.SetDescription(String.Format("{0}//{1}", rNo, dataTable.Rows.Count));
|
|
Application.DoEvents();
|
|
}
|
|
}
|
|
|
|
connection.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = ex.Message;
|
|
}
|
|
finally
|
|
{
|
|
WaitForm.Close();
|
|
|
|
if (result != String.Empty)
|
|
throw new Exception(result);
|
|
}
|
|
}
|
|
}
|
|
}
|