/// <summary>
/// Extracting Input Output flags from array of resources
/// </summary>
/// <param name="resources">The resources.</param>
/// <returns>Detected flags</returns>
private static dataTableIOFlags GetIOFlags(this object[] resources)
{
dataTableIOFlags IOFlags = dataTableIOFlags.defaultFlags;
if (resources != null)
{
foreach (var r in resources)
{
if (r != null)
{
if (r is dataTableIOFlags rflag)
{
IOFlags = rflag;
break;
}
}
}
}
return(IOFlags);
}
/// <summary>
/// Serializes the data table into choosed format and returns file path
/// </summary>
/// <param name="source">The source.</param>
/// <param name="format">The format.</param>
/// <param name="filename">The filename, without extension.</param>
/// <param name="directory">The directory to save into.</param>
/// <returns></returns>
public static string serializeDataTable(this DataTable source, dataTableExportEnum format, string filename, folderNode directory, params object[] resources)
{
dataTableIOFlags IOFlags = resources.GetIOFlags();
if (source == null)
{
return("");
}
if (source.Columns.Count == 0)
{
throw new dataException("Source table [0 columns]: ", null, source, "Export to excell : table is not applicable");
}
if (source.Rows.Count == 0)
{
return("");
}
format = checkFormatByFilename(format, filename);
string output = filename;
FileInfo fileInfo = null;
aceAuthorNotation authorNotation = resources.getFirstOfType <aceAuthorNotation>(false, false, true);
if (authorNotation == null)
{
authorNotation = new aceAuthorNotation();
}
if (directory == null)
{
directory = new DirectoryInfo(Directory.GetCurrentDirectory());
}
string cleanfilename = source.FilenameForTable(filename);
filename = directory.pathFor(cleanfilename, getWritableFileMode.overwrite, "Exported DataTable [" + source.GetTitle() + "][" + source.GetDescription() + "]. ");
switch (format)
{
case dataTableExportEnum.csv:
output = source.toCSV(true);
output = output.saveStringToFile(imbSciStringExtensions.ensureEndsWith(filename, ".csv")).FullName;
break;
case dataTableExportEnum.excel:
//fileInfo = new FileInfo(filename.ensureEndsWith(".xlsx"));
filename = imbSciStringExtensions.ensureEndsWith(filename, ".xlsx");
fileInfo = filename.getWritableFile(getWritableFileMode.overwrite);
//if (File.Exists(fileInfo.FullName)) File.Delete(fileInfo.FullName);
try
{
using (ExcelPackage pck = new ExcelPackage(fileInfo))
{
DataTableForStatistics dt_stat = null;
if (source is DataTableForStatistics)
{
dt_stat = source as DataTableForStatistics;
}
else
{
dt_stat = source.GetReportTableVersion(true);
}
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(source.GetTitle());
dt_stat.RenderToWorksheet(ws);
/*
* pck.Workbook.Properties.Title = source.GetTitle();
* pck.Workbook.Properties.Comments = authorNotation.comment;
* pck.Workbook.Properties.Category = "DataTable export";
* pck.Workbook.Properties.Author = authorNotation.author;
* pck.Workbook.Properties.Company = authorNotation.organization;
* pck.Workbook.Properties.Application = authorNotation.software;
* //pck.Workbook.Properties.Keywords = meta.keywords.content.toCsvInLine();
* pck.Workbook.Properties.Created = DateTime.Now;
* pck.Workbook.Properties.Subject = source.GetDescription();
*
* ws.Cells["A1"].LoadFromDataTable(source, true);
* ExcelRow row = ws.Row(1); //.Height = 100;
* row.Height = 100;
* row.Style.WrapText = true;
*
* row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
*
* Int32 rc = ws.Dimension.Rows - 1;
*
* for (int i = 0; i < rc; i++)
* {
* var ex_row = ws.Row(i+1);
* var in_row = source.Rows[i];
* if (dt_stat != null)
* {
* if (dt_stat.extraRows.Contains(in_row))
* {
* if (dt_stat.extraRows.IndexOf(in_row)%2 > 0)
* {
* ex_row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* ex_row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
* } else
* {
* ex_row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* ex_row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
* }
* }
* }
*
* ex_row.Style.WrapText = true;
* // row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
* }
*/
pck.Save();
}
}
catch (Exception ex)
{
throw new dataException("Excell: " + ex.Message, ex, source, "Export to excell");
}
output = fileInfo.FullName;
break;
case dataTableExportEnum.json:
output = objectSerialization.SerializeJson(source);
// JsonConvert.SerializeObject(source, Newtonsoft.Json.Formatting.Indented);
fileInfo = output.saveStringToFile(imbSciStringExtensions.ensureEndsWith(filename, ".json")); //.FullName;
output = fileInfo.FullName;
break;
case dataTableExportEnum.markdown:
output = source.markdownTable();
fileInfo = output.saveStringToFile(imbSciStringExtensions.ensureEndsWith(filename, ".md"));
output = fileInfo.FullName;
break;
case dataTableExportEnum.xml:
filename = imbSciStringExtensions.ensureEndsWith(filename, ".xml");
source.WriteXml(filename, false);
output = openBase.openFileToString(filename, true, false);
break;
default:
break;
}
return(output);
}
public static string serializeDataSet(this DataSet source, string filename, folderNode directory, dataTableExportEnum format, params object[] resources)
{
dataTableIOFlags IOFlags = resources.GetIOFlags();
string output = filename;
FileInfo fileInfo = null;
format = checkFormatByFilename(format, filename);
aceAuthorNotation authorNotation = resources.getFirstOfType <aceAuthorNotation>(false, false, true);
if (authorNotation == null)
{
authorNotation = new aceAuthorNotation();
}
if (directory == null)
{
directory = new DirectoryInfo(Directory.GetCurrentDirectory());
}
string cleanfilename = source.FilenameForDataset(filename);
filename = directory.pathFor(cleanfilename, getWritableFileMode.overwrite, "Exported DataSet [" + source.DataSetName + "] with [" + source.Tables.Count + "] tables. " + source.GetDesc());
DirectoryInfo dix = null;
switch (format)
{
case dataTableExportEnum.csv:
dix = directory.Add(cleanfilename, source.DataSetName, "Folder with CSV exports of DataSet [" + source.DataSetName + "] with [" + source.Tables.Count + "] tables. " + source.GetDesc());
foreach (DataTable table in source.Tables)
{
table.serializeDataTable(format, table.FilenameForTable(), dix);
}
output = dix.FullName;
break;
case dataTableExportEnum.excel:
//fileInfo = new FileInfo(filename.ensureEndsWith(".xlsx"));
filename = imbSciStringExtensions.ensureEndsWith(filename, ".xlsx");
fileInfo = filename.getWritableFile(getWritableFileMode.overwrite);
//if (File.Exists(fileInfo.FullName)) File.Delete(fileInfo.FullName);
try
{
using (ExcelPackage pck = new ExcelPackage(fileInfo))
{
pck.Workbook.Properties.Title = source.GetTitle();
pck.Workbook.Properties.Comments = authorNotation.comment;
pck.Workbook.Properties.Category = "DataTable export";
pck.Workbook.Properties.Author = authorNotation.author;
pck.Workbook.Properties.Company = authorNotation.organization;
pck.Workbook.Properties.Application = authorNotation.software;
//pck.Workbook.Properties.Keywords = meta.keywords.content.toCsvInLine();
pck.Workbook.Properties.Created = DateTime.Now;
pck.Workbook.Properties.Subject = source.GetDesc();
int c = 0;
foreach (DataTable table in source.Tables)
{
string title = table.GetTitle();
if (title.Length > 20)
{
title = title.toWidthMaximum(15, "");
}
title = title + c.ToString("D3");
c++;
while (pck.Workbook.Worksheets.Any(x => x.Name == title))
{
title = title + c.ToString("D3");
c++;
}
if (title == dataTableRenderingSetup.TABLE_DEFAULTNAME)
{
title = "Table" + source.Tables.Count.ToString("D3");
}
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(title);
DataTableForStatistics dt_stat = table as DataTableForStatistics;
if (table is DataTableForStatistics)
{
dt_stat = table as DataTableForStatistics;
}
else
{
dt_stat = table.GetReportTableVersion(true);
}
table.SetTitle(title);
table.TableName = title;
//DataTableForStatistics dt_stat = table as DataTableForStatistics;
dt_stat.RenderToWorksheet(ws);
/*
*
* ws.Cells["A1"].LoadFromDataTable(table, true);
*
* ExcelRow row = ws.Row(1); //.Height = 100;
* row.Height = 100;
* row.Style.WrapText = true;
*
* row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
*
* Int32 rc = ws.Dimension.Rows - 1;
*
* for (int i = 0; i < rc; i++)
* {
* var ex_row = ws.Row(i + 1);
* var in_row = table.Rows[i];
* if (dt_stat != null)
* {
* if (dt_stat.extraRows.Contains(in_row))
* {
* if (dt_stat.extraRows.IndexOf(in_row) % 2 > 0)
* {
* ex_row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* ex_row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
* }
* else
* {
* ex_row.Style.Fill.PatternType = ExcelFillStyle.Solid;
* ex_row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
* }
* }
* }
*
* ex_row.Style.WrapText = true;
* // row.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.WhiteSmoke);
* }
*/
}
pck.Save();
}
}
catch (Exception ex)
{
throw new dataException("Excell: " + ex.Message, ex, source, "Export to excell");
}
output = fileInfo.FullName;
break;
case dataTableExportEnum.json:
dix = directory.Add(cleanfilename, source.DataSetName, "Folder with JSON exports of DataSet [" + source.DataSetName + "] with [" + source.Tables.Count + "] tables. " + source.GetDesc());
output = objectSerialization.SerializeJson <DataSet>(source);
fileInfo = output.saveStringToFile(imbSciStringExtensions.ensureEndsWith(filename, ".json")); //.FullName;
output = fileInfo.FullName;
break;
case dataTableExportEnum.markdown:
dix = directory.Add(cleanfilename, source.DataSetName, "Folder with Markdown exports of DataSet [" + source.DataSetName + "] with [" + source.Tables.Count + "] tables. " + source.GetDesc());
foreach (DataTable table in source.Tables)
{
table.serializeDataTable(format, table.FilenameForTable(), dix);
}
output = dix.FullName;
break;
case dataTableExportEnum.xml:
dix = directory.Add(cleanfilename, source.DataSetName, "Folder with XML exports of DataSet [" + source.DataSetName + "] with [" + source.Tables.Count + "] tables. " + source.GetDesc());
foreach (DataTable table in source.Tables)
{
table.serializeDataTable(format, table.FilenameForTable(), dix);
}
output = dix.FullName;
break;
default:
break;
}
return(output);
}
/// <summary>
/// Deserializes the data table.
/// </summary>
/// <param name="filename">The filename or filepath.</param>
/// <param name="format">The format to read from</param>
/// <param name="directory">The directory (if filename was supplied and not full filepath)</param>
/// <param name="table">The table.</param>
/// <param name="resources">Supports: IObjectWithNameAndDescription</param>
/// <returns></returns>
/// <exception cref="dataException">
/// File path not accessable: " + filepath - null - null - deserializeDataTable
/// or
/// Deserialization error - imbDataTableExtensions->deserialize
/// </exception>
/// <exception cref="NotImplementedException"></exception>
public static DataTable deserializeDataTable(this string filename, dataTableExportEnum format, folderNode directory = null, DataTable table = null, params object[] resources)
{
dataTableIOFlags iOFlags = resources.GetIOFlags();
string output = "";
string filepath = "";
FileInfo fileInfo = null;
if (Path.IsPathRooted(filename))
{
filepath = filename;
}
else
{
if (directory == null)
{
directory = new DirectoryInfo(Directory.GetCurrentDirectory());
}
filepath = directory.pathFor(filename, getWritableFileMode.existing, table.GetDescription());
}
if (!File.Exists(filepath))
{
throw new dataException("File path not accessable: " + filepath, null, null, "deserializeDataTable");
}
fileInfo = new FileInfo(filepath);
if (table == null)
{
table = new DataTable();
}
IObjectWithNameAndDescription ownerWithName = resources.getFirstOfType <IObjectWithNameAndDescription>(false, null, true);
if (ownerWithName == null)
{
table.TableName = Path.GetFileNameWithoutExtension(filepath);
}
else
{
table.TableName = ownerWithName.name;
table.ExtendedProperties.Add(templateFieldDataTable.data_tabledesc, ownerWithName.description);
}
switch (format)
{
case dataTableExportEnum.csv:
// <---------------- postoji vec u ekstenzijama CSV resenje
StreamReader sr = new StreamReader(fileInfo.FullName);
throw new NotImplementedException();
//var csvr = new CsvReader(sr);
// csvr.ReadHeader();
// csvr.Configuration.WillThrowOnMissingField = false;
// foreach (string column in csvr.FieldHeaders)
// {
// var DataColumn = table.Columns.Add(column.Replace("__", "_"));
// }
//while (csvr.Read())
//{
// var row = table.NewRow();
// foreach (DataColumn column in table.Columns)
// {
// string vl = csvr.GetField(column.DataType, column.ColumnName).toStringSafe();
// vl = vl.Replace(".", "");
// vl = vl.Replace(",", ".");
// row[column.ColumnName] = vl;
// }
// table.Rows.Add(row);
//}
/*
* IEnumerable<DataRow> rows = csvr.GetRecords<DataRow>();
* foreach (DataRow dr in rows)
* {
* DataRow tdr = table.NewRow();
* foreach (DataColumn dc in table.Columns)
* {
* tdr[dc] = dr[dc.ColumnName];
* }
* table.Rows.Add(tdr);
* }*/
break;
case dataTableExportEnum.excel:
try
{
using (ExcelPackage pck = new ExcelPackage(fileInfo))
{
var dts = pck.ToDataSet(iOFlags.HasFlag(dataTableIOFlags.firstRowColumnNames));
table = dts.Tables.imbFirstSafe() as DataTable;
}
}
catch (Exception ex)
{
throw new dataException("Deserialization error", ex, table, "imbDataTableExtensions->deserialize");
}
/*
*
* // <--- OVO POSTOJI DOLE U KOMANDI toDataSet
* using (ExcelPackage pck = new ExcelPackage(fileInfo))
* {
* ExcelWorksheet ws = pck.Workbook.Worksheets[0];
* //ws.GetValue(0,)
* Int32 c = 1;
* Int32 r = 1;
* Boolean headersOk = false;
* List<String> headers = new List<string>();
* String head = ws.GetValue(r, c).toStringSafe();
*
* for (int i = 1; i < ws.Dimension.Columns; i++)
* {
* DataColumn dc = new DataColumn(c.toOrdinalLetter(false));
* head = ws.GetValue(r, c).toStringSafe();
* if (head.isWord())
* {
* headers.Add(head);
* }
*
* dc.ExtendedProperties.Add(templateFieldDataTable.col_id, i);
* dc.ExtendedProperties.Add(templateFieldDataTable.col_name, i.toOrdinalLetter());
* table.Columns.Add(dc);
* c++;
* }
*
* if (headers.Count() >= ws.Dimension.Columns) headersOk = true;
*
* if (headersOk)
* {
* c = 0;
* foreach (DataColumn dc in table.Columns)
* {
* dc.Caption = headers[c];
* c++;
* }
* r++;
* }
*
* for (int i = r; i < ws.Dimension.Rows; i++)
* {
* DataRow dr = table.NewRow();
* foreach (DataColumn dc in table.Columns)
* {
* dr[dc] = ws.GetValue(i, dc.Ordinal);
* }
* table.Rows.Add(dr);
* r++;
* }
* }
*/
output = fileInfo.FullName;
break;
case dataTableExportEnum.json:
string json = openBase.openFileToString(filepath, true);
table = objectSerialization.DeserializeJson <DataTable>(json);
break;
case dataTableExportEnum.markdown:
throw new NotImplementedException();
break;
case dataTableExportEnum.xml:
string xml = openBase.openFileToString(filepath, true);
table.ReadXml(xml);
break;
default:
break;
}
table.NormalizeColumnNames();
return(table);
}
/// <summary>
/// Deserializes a excel file to the DataSet.
/// </summary>
/// <param name="path">The path.</param>
/// <param name="target">The target.</param>
/// <param name="log">The log.</param>
/// <returns></returns>
public static DataSet deserializeExcelFileToDataSet(this string path, DataSet target, ILogBuilder log, dataTableIOFlags IOFlags = dataTableIOFlags.defaultFlags)
{
string name = Path.GetFileNameWithoutExtension(path);
if (target == null)
{
target = new DataSet(name);
}
FileInfo fi = new FileInfo(path);
using (ExcelPackage pck = new ExcelPackage(fi))
{
DataSet dts = pck.ToDataSet(false);
dts.NormalizeTableAndColumnNames();
int c = 0;
int r = 0;
foreach (DataTable dt in dts.Tables)
{
string nm = Path.GetFileNameWithoutExtension(fi.Name);
if (c > 0)
{
nm = nm + c.ToString("D3");
}
dt.TableName = nm;
target.Tables.Add(dt.Copy());
r = r + dt.Rows.Count;
if (log != null)
{
log.log("> Imported DataTable [" + nm + "] with [" + dt.Rows.Count + "] rows");
}
c++;
}
if (log != null)
{
log.log("Total [" + r + "] rows imported from [" + fi.Name + "] in [" + target.Tables.Count + "] data tables");
}
}
return(target);
}
