/// <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); }