예제 #1
0
        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);
        }