/// <summary>
/// Format cells in the range into a table
/// </summary>
/// <param name="range">The table's cells</param>
/// <param name="color">Color palette of the table</param>
/// <param name="showColumnHeader">Defines if the table has column header</param>
/// <param name="showRowHeader">Defines if the table has row header</param>
public static void FormatAsTable(this ExcelRange range, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
range.BorderEverywhere();
//First fill every cell as Light color from palette
range.Fill(palette.LightColor);
//Row header's color is main
if (showRowHeader)
{
//row header starts from first row first column, and ends at first row last column
var headerRange = ws.Cells[range.Start.Row, range.Start.Column, range.End.Row, range.Start.Column];
headerRange.Fill(palette.MainColor);
}
//Column header's color is main
if (showColumnHeader)
{
//Column header starts from first row first column, and ends at first row last column
var headerRange = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
headerRange.Fill(palette.MainColor);
}
}
/// <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();
}