public static void CreateNewExcelFile()
{
Microsoft.Vbe.Interop.VBComponent xlMod;
Microsoft.Vbe.Interop.VBComponent xlModule;
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = "worksheet_table";
//add the macro to the module excel workbook here.
xlModule = workbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
xlModule.Name = "ModuleBasic";
//add the macro to the sheet workbook here.
xlMod = workbook.VBProject.VBComponents.Item(worksheet.CodeName);
//Add(VBIDE.vbext_ComponentType.vbext_ct_Document);
//xlMod.Name = "ModuleBasic";
InsertionMacroVBAinFile.InsertMacroInExcel(app, xlMod, xlModule);
DataParser.ParseJsonToObject(app, workbook, worksheet);
DataParser.ParseObjectToSpreadSheet();
SorterExcelElements.SortByColumnAscending(worksheet);
FilteringOnHeadersForTable.AddHeaderFilters(worksheet);
BordersMaker.DrawFullTableBorders(app, workbook, worksheet);
PivotTable.CreatePivotTable(app, workbook, worksheet);
Button.ExcelAddButtonWithVBA(app, workbook, worksheet);
SaveExcelFile.SaveNewExcelFile(app, workbook, worksheet, xlMod, xlModule);
}
/// <summary>
/// Method writes data in Excelfile
/// </summary>
/// <param name="dictionaryByFileNames"></param>
public static void WriteDataInExcel(Dictionary <int, string> dictionaryByFileNames)
{
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[1];
Microsoft.Vbe.Interop.VBComponent xlMod;
Microsoft.Vbe.Interop.VBComponent xlModule;
//workbook = (Excel.Workbook)(app.Workbooks.Add(Missing.Value));
//worksheet = (Excel.Worksheet)workbook.ActiveSheet;
worksheet.Name = "worksheet_table";
//add the macro to the excel workbook here.
xlMod = workbook.VBProject.VBComponents.Item(worksheet.CodeName);
xlModule = workbook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
xlModule.Name = "ModuleBasic";
int row = 2;
foreach (var element in dictionaryByFileNames)
{
string[] words = element.Value.Split(',');
//worksheet.Cells[row, 1].Value = element.Key;
worksheet.Cells[row, 2].Value = words[0];
worksheet.Cells[row, 3].Value = Int32.Parse(words[3]);
worksheet.Cells[row, 7].Value = words[1];
worksheet.Cells[row, 8].Value = Math.Round(double.Parse(words[2].Replace(".", ",")), 2);
worksheet.Cells[row, 9].Value = Math.Round(double.Parse(words[4].Replace(".", ",")), 2);
row++;
}
SorterExcelElements.SortByColumnAscending(worksheet);
int elementNumeration = 0;
int newRow = 1;
for (int i = 0; i < dictionaryByFileNames.Count; i++)
{
elementNumeration++;
newRow++;
worksheet.Cells[newRow, 1].Value = elementNumeration;
worksheet.Cells[newRow, 4].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(230, 230, 230));
}
//conditional formatting
Excel.Range last = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = worksheet.get_Range("B2", last);
int lastUsedRow = last.Row;
Excel.Range oResizeRange = worksheet.Range[worksheet.Cells[1, 2],
worksheet.Cells[lastUsedRow - 1, 2]];
oResizeRange.Select();
Excel.Range xlS = app.Selection;
xlS.FormatConditions.AddUniqueValues();
var xlFC = xlS.FormatConditions[xlS.FormatConditions.Count];
xlFC.SetFirstPriority();
var xlFC1 = xlS.FormatConditions[1];
Excel.UniqueValues UV = (Excel.UniqueValues)xlS.FormatConditions[1];
UV.DupeUnique = Excel.XlDupeUnique.xlDuplicate;
xlFC1.Interior.Pattern = Excel.XlPattern.xlPatternAutomatic;
xlFC1.Interior.TintAndShade = 0;
xlFC1.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 230);
worksheet.Cells[1, 1].Value = "Numeration";
worksheet.Cells[1, 2].Value = "FileName";
worksheet.Rows[1].RowHeight = 60;
worksheet.Range[worksheet.Cells[1, 5], worksheet.Cells[1, 6]].Merge();
worksheet.Cells[1, 3].Value = string.Concat("Found", Environment.NewLine, "(Is threre a weapon or not?)", Environment.NewLine);
worksheet.Cells[1, 4].Value = string.Concat("Correctness", Environment.NewLine, "(Is the AI decision right?)", Environment.NewLine);
worksheet.Cells[1, 5].Value = "Conclusion";
worksheet.Cells[1, 6].Value = "Conclusion";
worksheet.Cells[1, 7].Value = "Label";
worksheet.Cells[1, 8].Value = "Confidence";
worksheet.Cells[1, 9].Value = "Latency_ms";
DesignMyTableExcel(app, workbook, worksheet);
Controls.PivotTable.CreatePivotTable(app, workbook, worksheet);
InsertionMacroVBAinFile.InsertMacroInExcel(app, xlMod, xlModule);
Button.ExcelAddButtonWithVBA(app, workbook, worksheet);
SaveExcelFile.SaveNewExcelFile(app, workbook, worksheet, xlMod, xlModule);
}