//Prints hierarchical list's data from x,y position private static void PrintHierarchicalList(this ExcelWorksheet ws, int x, int y, HierarchyElement root) { ws.Cells[y, x].Value = root.Content; if (root.Children.Count > 0) { int startRow = y + 1; int startColumn = x + 1; foreach (var child in root.Children) { ws.PrintHierarchicalList(startColumn, startRow, child); startRow += child.GetCount(); } } }
static void Main(string[] args) { using (ExcelPackage pkg = new ExcelPackage(new System.IO.FileInfo(@"test.xlsx"))) { ExcelWorksheet ws; if (pkg.Workbook.Worksheets["Sheet1"] == null) { ws = pkg.Workbook.Worksheets.Add("Sheet1"); } else { ws = pkg.Workbook.Worksheets["Sheet1"]; } ///Clear worksheet for testing ws.Cells["A1:Z100"].Clear(); /// Insert table with 2D array string[,] data = { { "ID", "Name", "Color" }, { "A1", "Chocolate", "Brown" }, { "A2", "Milk", "White" }, }; ws.InsertTable(2, 2, data, "2D Array"); /// Insert table with IEnumerable var list = new List <TestObject> { { new TestObject("Társasjáték", "Játék", 8) }, { new TestObject("Videojáték", "Játék", 10) }, { new TestObject("Kenyér", "Pékáru", 5) }, { new TestObject("Alma", "Gyümölcs", 10) }, { new TestObject("Autó", "Jármű", 1) }, { new TestObject("Éjjeliszekrény", "Bútor", 3) } }; ws.InsertTable(2, 7, list, "IEnumerable", ExcelColor.Succes); /// Insert table with Key-Value pairs var dictionary = new Dictionary <int, TestObject>(); for (int i = 0; i < 3; i++) { dictionary.Add(i, list[i]); } ws.InsertTable(6, 2, dictionary, "Key-Value", ExcelColor.Danger); /// Insert hierarchical list var root = new HierarchyElement("Benchmark") { new HierarchyElement("Aláíráshitelesítő") { new HierarchyElement("Transzformációk") { new HierarchyElement("Átmáretezés") { new HierarchyElement("Vízszintes átméretezés"), new HierarchyElement("Függőleges átméretezés") }, new HierarchyElement("Eltolás") }, new HierarchyElement("Osztályozók") { new HierarchyElement("O1"), new HierarchyElement("O2") } } }; ws.InsertHierarchicalList(9, 2, root, "Hierarchical list", ExcelColor.Secondary); /// Insert legend ws.Cells["B17:M23"].InsertLegend( "Ez egy általános leírás arról, hogym it tud ez a táblázat Ez egy általános leírás arról, hogym it tud ez a táblázat Ez egy általános leírás arról, hogym it tud ez a táblázat Ez egy általános leírás ", "Bemutatkozó"); /// Insert link if (pkg.Workbook.Worksheets["Sheet2"] == null) { pkg.Workbook.Worksheets.Add("Sheet2"); } ws.Cells["A1"].Value = "To Sheet2"; ws.Cells["A1"].InsertLink("Sheet2"); ws.Cells["B1"].Value = "To B2 in Sheet2"; ws.Cells["B1"].InsertLink("Sheet2", "B2"); ///Insert graphs var ws2 = pkg.Workbook.Worksheets["Sheet2"]; ws2.Drawings.Clear(); ws2.Cells["A1:Z100"].Clear(); string[,] chartHeader = { { "xLabel", "FAR", "FRR" } }; double [,] chartData = { { 0.5, 0, 100 }, { 0.9, 10, 80 }, { 1.4, 30, 60 }, { 1.9, 50, 50 }, { 2.2, 70, 30 }, { 2.7, 90, 20 }, { 3, 100, 0 } }; ws2.InsertTable(2, 2, chartHeader, null, ExcelColor.Transparent, false, false); ws2.InsertTable(2, 3, chartData, null, ExcelColor.Transparent, false, false); ws2.InsertLineChart(ws2.Cells["B3:D9"], 6, 1, "Error Rates", ws2.Cells["B2"].Value?.ToString(), "yLabel", ws2.Cells["C2:D2"], 500, 400); ws2.InsertColumnChart(ws2.Cells["B3:D9"], 14, 1, "Error Rates 2", ws2.Cells["B2"].Value?.ToString(), "yLabel", ws2.Cells["C2:D2"], 500, 400, "Error Rates"); ws2.InsertColumnChart(ws2.Cells["B3:C9"], 6, 23, "Error Rates 3", ws2.Cells["B2"].Value?.ToString(), "yLabel", ws2.Cells["C2:D2"], 500, 400, "Error Rates"); pkg.Save(); Console.WriteLine("Done"); Console.ReadKey(); } }
/// <summary> /// Insert a hierarchical list in tree style into the worksheet /// </summary> /// <param name="ws">Worksheet in wich the list is inserted</param> /// <param name="x">Starting row of the list</param> /// <param name="y">Starting column of the list</param> /// <param name="root">Root element of the list</param> /// <param name="title">Title of the list</param> /// <param name="color">color of the list</param> public static void InsertHierarchicalList(this ExcelWorksheet ws, int x, int y, HierarchyElement root, string title = null, ExcelColor color = ExcelColor.Primary) { //Get hierarchy debth and the number of items in it int depth = root.GetDepth(); int length = root.GetCount(); //Get table range ExcelRange range = ws.Cells[y, x, y + length - 1, x + depth - 1]; //Format var palette = PaletteStorage.GetPalette(color); var startRow = y; if (title != null) { //Get the title's cells and format title var titleCells = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column]; titleCells.Fill(palette.MainColor); titleCells.Merge(); titleCells.BorderAround(); titleCells.Value = title; //if there's title then the range should be changed after (pushed down by one row) range = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row + 1, range.End.Column]; startRow++; } //Format table under title range.BorderAround(); range.Fill(palette.LightColor); //Insert data into formatted cells ws.PrintHierarchicalList(x, startRow, root); //Set columns to a low width for (int i = x; i < (x + depth - 1); i++) { ws.Column(i).Width = 2; } //set last column to autosize ws.Column(x + depth - 1).AutoFit(); }