/// <summary>
/// Format cells in the range into a table with possible title
/// </summary>
/// <param name="range">The table's cells</param>
/// <param name="title">The table's title, if null, the table won't have title</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>
/// <returns></returns>
public static int FormatAsTableWithTitle(this ExcelRange range, string title, ExcelColor color = ExcelColor.Primary, bool showColumnHeader = true, bool showRowHeader = true)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
ExcelRange tableRange = range;
var nextRow = range.Start.Row;
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.DarkColor);
titleCells.Merge();
titleCells.BorderAround();
titleCells.Value = title;
//if there's title then the range should be changed after (pushed down by one row)
tableRange = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row + 1, range.End.Column];
nextRow++;
}
//Format table under title
tableRange.FormatAsTable(color, showColumnHeader, showRowHeader);
return(nextRow);
}
/// <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 legend
/// </summary>
/// <param name="range">Range of the legend</param>
/// <param name="text">Text of the legend</param>
/// <param name="title">Title of the legend (can be null)</param>
/// <param name="color">Color of the legend</param>
public static void InsertLegend(this ExcelRange range, string text, string title = null, ExcelColor color = ExcelColor.Info)
{
var ws = range.Worksheet;
var palette = PaletteStorage.GetPalette(color);
//Set legends text range
var legendRange = range;
//If legend has title, format it
if (title != null)
{
var titleCells = ws.Cells[range.Start.Row, range.Start.Column, range.Start.Row, range.End.Column];
titleCells.Merge();
titleCells.Value = title;
titleCells.BorderAround();
titleCells.Fill(palette.MainColor);
//Alter text's range
legendRange = ws.Cells[range.Start.Row + 1, range.Start.Column, range.End.Row, range.End.Column];
}
//Format legends text
legendRange.Merge();
legendRange.Value = text;
legendRange.BorderAround();
legendRange.Fill(palette.LightColor);
legendRange.Style.WrapText = true;
legendRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
legendRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
/// <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();
}