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