Пример #1
0
        /// <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);
        }
Пример #2
0
        /// <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);
            }
        }
Пример #3
0
        /// <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;
        }
Пример #4
0
        /// <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();
        }