/// <summary>
/// Loads the BibTex file and converts it to Excel
/// </summary>
/// <param name="filePath">The file path.</param>
/// <param name="author">The author.</param>
/// <param name="log">The log.</param>
/// <param name="customTemplate">Custom data annotation template, to be used for Excel file generation. If not specified, default <see cref="template"/> is used</param>
/// <returns>Extended DataTable that is written in Excel file</returns>
public static DataTableForStatistics ExportToExcel(String filePath, aceAuthorNotation author, ILogBuilder log = null, propertyAnnotationPreset customTemplate = null, folderNode outputFolder = null)
{
BibTexDataFile bibFile = new BibTexDataFile(filePath);
FileInfo fi = new FileInfo(filePath);
if (outputFolder == null)
{
outputFolder = fi.Directory;
}
if (customTemplate == null)
{
customTemplate = template;
}
DataTable reportTable = bibFile.ConvertToDataTable(null, customTemplate, log); //.GetReportAndSave(fi.Directory, author, bibFile.name);
DataTableForStatistics report = reportTable.GetReportAndSave(outputFolder, author);
if (log != null)
{
log.log("BibTex [" + filePath + "] -> Excel [" + report.lastFilePath + "]");
}
return(report);
}
/// <summary>
/// Creates generic DataTable collection, adds 5 rows and generates Excel and CSV file
/// </summary>
public void ExampleOne_DataTableDataAnnotation()
{
// creating typed DataTable collection, holding DataEntryTest class
DataTableTypeExtended<DataEntryTest> dataTableTypeExtended = new DataTableTypeExtended<DataEntryTest>(nameof(DataEntryTest), nameof(ExampleOne_DataTableDataAnnotation));
// adding five rows
dataTableTypeExtended.AddRow(new DataEntryTest());
dataTableTypeExtended.AddRow(new DataEntryTest());
dataTableTypeExtended.AddRow(new DataEntryTest());
dataTableTypeExtended.AddRow(new DataEntryTest());
dataTableTypeExtended.AddRow(new DataEntryTest());
// Generating and exporting report into Excel file
DataTableForStatistics report = dataTableTypeExtended.GetReportAndSave(folderResults);
}
/// <summary>
/// Publishes the table blocks.
/// </summary>
/// <param name="folder">The folder.</param>
/// <param name="blockSize">Size of the block.</param>
/// <param name="blockCount">The block count.</param>
/// <returns></returns>
public DataTableTypeExtended <FeatureCWPFrequencies> PublishTableBlocks(folderNode folder, Int32 blockSize = 500, Int32 blockCount = 10)
{
if (!this.Any())
{
return(null);
}
foreach (var pair in this)
{
pair.Value.Compute();
}
Int32 b = 0;
for (int i = 0; i < blockCount; i++)
{
var p = this.First();
String dt_n = name + "_" + i.ToString();
DataTableTypeExtended <FeatureCWPFrequencies> cwpMetrics = new DataTableTypeExtended <FeatureCWPFrequencies>(dt_n, "Collected metrics");
// DataTableTypeExtended<FeatureCWPFrequencies> cwpFrequencies = new DataTableTypeExtended<FeatureCWPFrequencies>(dt_n + "_freq", "frequency metrics");
// p.Value.SetDataTable(datatable);
// DataColumn nm = datatable.Columns.Add("Name");
Int32 c = 0;
foreach (var pair in this)
{
if (c > (i * blockSize) && c < (i + 1) * blockSize)
{
cwpMetrics.AddRow(pair.Value);
// cwpFrequencies.AddRow(term_finders.)
}
c++;
}
if (cwpMetrics.Rows.Count > 0)
{
DataTableConverterASCII dataTableConverterASCII = new DataTableConverterASCII();
dataTableConverterASCII.ConvertToFile(cwpMetrics, folder, dt_n);
DataTableForStatistics report = cwpMetrics.GetReportAndSave(folder, null, dt_n);
}
}
DataTableTypeExtended <FeatureCWPFrequencies> output = new DataTableTypeExtended <FeatureCWPFrequencies>();
foreach (var p in this)
{
output.AddRow(p.Value);
}
output.Save(folder, null, "stats_frequencies");
return(output);
}
/// <summary>Performs post-processing of data collected by the workload plugin</summary>
/// <remarks><para>Loads all saved DataTables, groups rows in averages for each measure group and creates summary table with all experiments</para></remarks>
/// <param name="searchPattern">pattern used to select input files</param>
/// <param name="groupColumn">column name used for row grouping</param>
/// <param name="overviewColumns">columns to include in overview table</param>
/// <seealso cref="aceOperationSetExecutorBase"/>
public void aceOperation_runWorkloadData(
[Description("pattern used to select input files")] string searchPattern = "results*.xml",
[Description("column name used for row grouping")] string groupColumn = "measureGroup")
// [Description("columns to include in overview table")] String overviewColumns = "DataLoad,CrawlerIterations,ContentPages,dlcMaximum")
{
aceOperation_selectFiles(searchPattern, "index\\workload", true);
folder = folder["index\\workload"];
List <DataTable> tables = new List <DataTable>();
dataPointAggregationType aggType = dataPointAggregationType.avg;
int ci = 1;
int c = selectedFiles.Count();
output.log("[" + c + "] DataTable in the cue.");
List <DataTable> allTables = new List <DataTable>();
DataSet dSet = new DataSet();
aceDictionarySet <string, DataTable> byCrawler = new aceDictionarySet <string, DataTable>();
aceDictionarySet <string, DataTableForStatistics> byCrawlerRT = new aceDictionarySet <string, DataTableForStatistics>();
DataTableForStatistics rt = null;
foreach (FileInfo fi in selectedFiles)
{
try
{
objectTable <reportPlugIn_workloadEntry> workloadEntry = new objectTable <reportPlugIn_workloadEntry>(fi.FullName, true, "EntryID", "");
objectTable <reportPlugIn_workloadEntry> workloadGrouped = new objectTable <reportPlugIn_workloadEntry>("EntryID", "aggregated");
aceDictionarySet <int, reportPlugIn_workloadEntry> workloadGroups = workloadEntry.GetGroups <int>(groupColumn, "terminationWarning = 0");
collectionAggregationResultSet <reportPlugIn_workloadEntry> aggregateSet = new collectionAggregationResultSet <reportPlugIn_workloadEntry>();
foreach (var set in workloadGroups)
{
collectionAggregationResult <reportPlugIn_workloadEntry> aggregates = null;
aggregates = set.Value.GetAggregates(aggType);
var aggregate = aggregates[aggType];
aggregate.measureGroup = set.Key;
aggregate.EntryID = set.Key.ToString("D5") + "_" + aggType.ToString();
workloadGrouped.AddOrUpdate(aggregate);
aggregateSet.Add(aggregate.EntryID + "_" + fi.Name, aggregates);
}
string filename = (fi.Name + "_" + groupColumn + "_" + aggType.ToString()).getFilename();
string n = reportPlugIn_workload_state.ExtractEntryID(aggregateSet.lastItem.EntryID) + dSet.Tables.Count.ToString("D2");
DataTable dt = workloadGrouped.GetDataTable(dSet, n);
dt.SetDescription("Collection of [" + aggregateSet.recordType.Name + "] records grouped by [" + groupColumn + "]");
dt.SetAggregationAspect(dataPointAggregationAspect.subSetOfRows);
dt.SetAggregationOriginCount(aggregateSet.Count);
dt.SetAdditionalInfoEntry("Aggregation Type:", aggType);
dt.SetAdditionalInfoEntry("Data source file:", fi.Name);
dt.SetAdditionalInfoEntries("Last", aggregateSet.lastItem, typeof(string));
dt.SetTitle(n);
byCrawler.Add(aggregateSet.firstItem.Crawler, dt);
// dt.TableName = n;
// dSet.AddTable(dt);
rt = dt.GetReportAndSave(folder, imbWEMManager.authorNotation, n.getFilename(), true);
byCrawlerRT.Add(aggregateSet.firstItem.Crawler, rt);
response.AppendLine("[" + ci + " / " + c + "] DataTable [" + fi.Name + "] had [" + workloadGroups.Keys.Count() + "] groups. Result saved as: " + filename);
ci++;
} catch (Exception ex)
{
output.log("[" + ci + " / " + c + "] DataTable [" + fi.FullName + "] failed.");
output.log(ex.Message);
}
}
output.log("[" + c + "] DataTable processed.");
dSet.serializeDataSet("workload_all", folder, dataTableExportEnum.excel, imbWEMManager.authorNotation);
foreach (string key in byCrawler.Keys)
{
string filename = key.getFilename();
DataSet sd = new DataSet(key);
foreach (DataTable dti in byCrawler[key])
{
sd.AddTable(dti.Copy());
}
sd.AddTable(byCrawlerRT[key].First().RenderLegend());
sd.serializeDataSet(filename, folder, dataTableExportEnum.excel, imbWEMManager.authorNotation);
}
}
/// <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>
/// Adds row with information taken from the <see cref="DataTable.ExtendedProperties" /> --- at the current position in the table
/// </summary>
/// <param name="dt">The dt.</param>
/// <param name="extra">The extra.</param>
/// <param name="widthLimit">The width limit.</param>
/// <returns></returns>
public static DataRow AddExtraRow(this DataTable dt, Enum extra, int widthLimit = 25)
{
Dictionary <DataColumn, List <string> > extraLines = new Dictionary <DataColumn, List <string> >();
aceDictionary2D <int, DataColumn, string> exLines = new aceDictionary2D <int, DataColumn, string>();
DataRow dr = dt.NewRow();
DataTableForStatistics dt_stat = null;
if (dt is DataTableForStatistics)
{
dt_stat = dt as DataTableForStatistics;
}
foreach (DataColumn col in dt.Columns)
{
if (col.ExtendedProperties.ContainsKey(extra))
{
object vl = col.ExtendedProperties[extra];
if (vl is string)
{
string vlstr = vl as string;
List <string> lines = vlstr.wrapLineBySpace(widthLimit);
if (lines.Count > 1)
{
dr[col] = lines[0];
for (int i = 1; i < lines.Count; i++)
{
exLines[i, col] = lines[i];
}
}
else
{
dr[col] = vlstr;
}
}
else
{
dr[col] = vl;
}
}
else
{
dr[col] = null;
}
}
dt.Rows.Add(dr);
// if (dt_stat != null) dt_stat.extraRows.Add(dr);
if (exLines.Count > 0)
{
DataRow dre = dt.NewRow();
foreach (var ex in exLines)
{
foreach (DataColumn col in dt.Columns)
{
if (ex.Value.ContainsKey(col))
{
dre[col] = ex.Value[col];
}
else
{
dre[col] = null;
}
}
}
dt.Rows.Add(dre);
// if (dt_stat != null) dt_stat.extraRows.Add(dre);
}
return(dr);
}
