/// <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="col">Starting column of the list</param> /// <param name="row">Starting row 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 row, int col, 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[row, col, row + length - 1, col + depth - 1]; //Format var palette = PaletteStorage.GetPalette(color); var startRow = 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.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(col, startRow, root); //Set columns to a low width for (int i = col; i < (col + depth - 1); i++) { ws.Column(i).Width = 2; } //set last column to autosize //ws.Column(col + depth - 1).AutoFit();//ehhez gdi+ kell }