121 lines
4.3 KiB
C#
121 lines
4.3 KiB
C#
using System.Data;
|
|
using System.Data.OleDb;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
|
|
namespace Mesa_Backlog.Library;
|
|
|
|
public class ExcelReader
|
|
{
|
|
|
|
/// <summary>
|
|
/// https://social.msdn.microsoft.com/Forums/en-US/2e030743-5d66-4e53-bbff-bb2eee0cbc9b/readingwriting-excel-without-excel?forum=Vsexpressvcs
|
|
/// </summary>
|
|
private static DataTable GetSheet(string fileName, string selectSql)
|
|
{
|
|
DataTable results = new();
|
|
#if Linux
|
|
()("Built on Linux!");
|
|
#elif OSX
|
|
()("Built on macOS!");
|
|
#elif Windows || !NETCORE
|
|
#pragma warning disable CA1416
|
|
for (int i = 0; i < int.MaxValue; i++)
|
|
{
|
|
try
|
|
{
|
|
OleDbConnectionStringBuilder connectionStringBuilder = new()
|
|
{
|
|
Provider = "Microsoft.ACE.OLEDB.12.0",
|
|
DataSource = fileName
|
|
};
|
|
connectionStringBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;IMEX=1;ReadOnly=1;");
|
|
using OleDbConnection connection = new(connectionStringBuilder.ConnectionString);
|
|
connection.Open();
|
|
using (OleDbDataAdapter adapter = new(selectSql, connection))
|
|
adapter.Fill(results);
|
|
connection.Close();
|
|
break;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
if (i > 3) //2019-12-28 - 001
|
|
throw;
|
|
Thread.Sleep(1000);
|
|
}
|
|
#pragma warning restore CA1416
|
|
}
|
|
#else
|
|
()("Built in unknown!");
|
|
#endif
|
|
return results;
|
|
}
|
|
|
|
private static string DataTableToJSON(DataTable table)
|
|
{
|
|
string name;
|
|
string value;
|
|
object @object;
|
|
StringBuilder jsonString = new();
|
|
if (table.Rows.Count > 0)
|
|
{
|
|
_ = jsonString.Append('[');
|
|
for (int i = 0; i < table.Rows.Count; i++)
|
|
{
|
|
_ = jsonString.Append('{');
|
|
for (int j = 0; j < table.Columns.Count; j++)
|
|
{
|
|
@object = table.Rows[i][j];
|
|
if (@object is null)
|
|
value = string.Empty;
|
|
else
|
|
value = string.Concat(@object);
|
|
if (value.Contains('"'))
|
|
value = value.Replace("\"", "\\\"");
|
|
if (value.Contains('\n'))
|
|
value = value.Replace("\t", " ").Replace("\n", "<br />");
|
|
name = table.Columns[j].ColumnName.ToString();
|
|
if (name == ",")
|
|
name = "Title";
|
|
if (name.Contains('"'))
|
|
name = name.Replace("\"", "\\\"");
|
|
if (name.Contains('\n'))
|
|
name = name.Replace("\t", " ").Replace("\n", "<br />");
|
|
_ = jsonString.Append('"').Append(name).Append("\":").Append('"').Append(value).Append('"');
|
|
if (j < table.Columns.Count - 1)
|
|
_ = jsonString.Append(',');
|
|
}
|
|
_ = jsonString.Append('}');
|
|
if (i != table.Rows.Count - 1)
|
|
_ = jsonString.Append(',');
|
|
}
|
|
_ = jsonString.Append(']');
|
|
}
|
|
return jsonString.ToString();
|
|
}
|
|
|
|
public static string GetJson(string reportFullPath, string sheet)
|
|
{
|
|
string result;
|
|
string selectSql = string.Concat("SELECT * FROM [", sheet, "$]");
|
|
using (DataTable dataTable = GetSheet(reportFullPath, selectSql))
|
|
{
|
|
if (dataTable.Rows.Count == 0)
|
|
throw new Exception("No rows");
|
|
result = DataTableToJSON(dataTable);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static FIBacklogMesa[] GetFIBacklogMesaCollection(string json)
|
|
{
|
|
FIBacklogMesa[]? results;
|
|
results = JsonSerializer.Deserialize<FIBacklogMesa[]>(json, new JsonSerializerOptions() { PropertyNameCaseInsensitive = true });
|
|
if (results is null || !results.Any())
|
|
throw new NullReferenceException();
|
|
return results;
|
|
}
|
|
|
|
public static string GetJson(FIBacklogMesa[]? fIBacklogMesaCollection) => JsonSerializer.Serialize(fIBacklogMesaCollection, new JsonSerializerOptions() { WriteIndented = true });
|
|
|
|
} |