/// <summary>
/// Method designs table
/// </summary>
/// <param name="worksheet"></param>
/// <param name="worksheetPivot"></param>
/// <param name="worksheetPeeChart"></param>
public static void DesignMyTableExcel(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
worksheet.Activate();
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
// Now apply autofilter
Excel.Range firstRow = (Excel.Range)worksheet.Rows[1];
firstRow.AutoFilter(1,
Type.Missing,
Excel.XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
BordersMaker.DrawFullTableBorders(app, workbook, worksheet);
Range rng = worksheet.UsedRange;
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.get_Range("A1", "I1").Cells.Font.Size = 14;
rng.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rng.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range("A1", "I1").Cells.Font.Name = "Arial Narrow";
worksheet.get_Range("A1", "I1").Cells.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 71, 179));
worksheet.get_Range("A1", "I1").Cells.Font.Bold = true;
worksheet.Columns.AutoFit();
Excel.Range thisRange = worksheet.get_Range("H:H");
thisRange.NumberFormat = "0.00";
Marshal.FinalReleaseComObject(thisRange);
}
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);
}