Method to create multi-level grouping in Excel with C#

If you can group and display data with multi-level detail in Excel, it can clearly show the overall structure of the data table, giving the document a sense of hierarchy. According to your needs, set the display or hide detailed information under the classification data, which makes the document more beautiful while facilitating data viewing and management. So, how to create multi-level grouping display of Excel data in C#? The following will elaborate in detail. The free version component is used in the method.Free Spire.XLS for .NETDownload and install this component, create a console application, and add the dll file of the component package to the project, and add the corresponding namespace at the same time. The following is the specific code operation steps for reference.

Step 1:Create a Wordbook class object to get the first worksheet

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Step 2:Write data to the cell

sheet.Range["A1"].Value = "Company Department";
sheet.Range["A3"].Value = "General Department";
sheet.Range["A4"].Value = "Administration";
sheet.Range["A5"].Value = "Personnel";
sheet.Range["A6"].Value = "Marketing Department";
sheet.Range["A7"].Value = "Business Department";
sheet.Range["A8"].Value = "Customer Service Department";
sheet.Range["A9"].Value = "Technology Department";
sheet.Range["A10"].Value = "Technology Development";
sheet.Range["A11"].Value = "Technical Support";
sheet.Range["A12"].Value = "Pre-sales Support";
sheet.Range["A13"].Value = "After-sales Support";

Step 3:Set the IsSummaryRowBelow property to false, so the summary row is displayed above the detail row

sheet.PageSetup.IsSummaryRowBelow = false;

Step 4:Select rows to group, the parameter false indicates to expand the current group, set it to true if you want to hide

//Select rows for one-level grouping
sheet.GroupByRows(2, 13, false);
//Select rows for two-level grouping
sheet.GroupByRows(4,5 , false);
sheet.GroupByRows(7, 8, false);
sheet.GroupByRows(10,13 , false);
//Select rows for three-level grouping
sheet.GroupByRows(12,13, true);

Step 5:Define a CellStyle object, set and apply the format to the font inside the cell

CellStyle style = workbook.Styles.Add("style");
style.Font.IsBold = true;
style.Color = Color.LawnGreen;
sheet.Range["A1"].CellStyleName = style.Name;
sheet.Range["A3"].CellStyleName = style.Name;
sheet.Range["A6"].CellStyleName = style.Name;
sheet.Range["A9"].CellStyleName = style.Name;

Step 6:Set the style of the area border

sheet.Range["A4:A5"].BorderAround(LineStyleType.Thin);
sheet.Range["A4:A5"].BorderInside(LineStyleType.Thin);
sheet.Range["A7:A8"].BorderAround(LineStyleType.Thin);
sheet.Range["A7:A8"].BorderInside(LineStyleType.Thin);
sheet.Range["A10:A13"].BorderAround(LineStyleType.Thin);
sheet.Range["A10:A13"].BorderInside(LineStyleType.Thin);

Step 7:Save Document

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);

Run the program to generate the document (the generated document can be found in the bin>Debug folder of the project folder)

Effect display:

All code:

using System;
using System.Drawing;
using Spire.Xls;
namespace MultilevelGroup_XLS
{
 class Program
 {
  static void Main(string[] args)
  {
   //Create a Wordbook class object to get the first worksheet
   Workbook workbook = new Workbook();
   Worksheet sheet = workbook.Worksheets[0];
   //Write data to the cell
   sheet.Range["A1"].Value = "Company Department";
   sheet.Range["A3"].Value = "General Department";
   sheet.Range["A4"].Value = "Administration";
   sheet.Range["A5"].Value = "Personnel";
   sheet.Range["A6"].Value = "Marketing Department";
   sheet.Range["A7"].Value = "Business Department";
   sheet.Range["A8"].Value = "Customer Service Department";
   sheet.Range["A9"].Value = "Technology Department";
   sheet.Range["A10"].Value = "Technology Development";
   sheet.Range["A11"].Value = "Technical Support";
   sheet.Range["A12"].Value = "Pre-sales Support";
   sheet.Range["A13"].Value = "After-sales Support";
   //Set IsSummaryRowBelow to false, so the summary row is displayed above the detail row
   sheet.PageSetup.IsSummaryRowBelow = false;
   //Select rows for one-level grouping
   //The parameter false indicates that the current grouping is expanded. If you want to hide, set it to true
   sheet.GroupByRows(2, 13, false);
   //Select rows for two-level grouping
   sheet.GroupByRows(4,5 , false);
   sheet.GroupByRows(7, 8, false);
   sheet.GroupByRows(10,13 , false);
   //Select rows for three-level grouping
   sheet.GroupByRows(12,13, true);
   //Define a CellStyle object, set and apply the cell font format
   CellStyle style = workbook.Styles.Add("style");
   style.Font.IsBold = true;
   style.Color = Color.LawnGreen;
   sheet.Range["A1"].CellStyleName = style.Name;
   sheet.Range["A3"].CellStyleName = style.Name;
   sheet.Range["A6"].CellStyleName = style.Name;
   sheet.Range["A9"].CellStyleName = style.Name;
   //Set the style of the area border
   sheet.Range["A4:A5"].BorderAround(LineStyleType.Thin);
   sheet.Range["A4:A5"].BorderInside(LineStyleType.Thin);
   sheet.Range["A7:A8"].BorderAround(LineStyleType.Thin);
   sheet.Range["A7:A8"].BorderInside(LineStyleType.Thin);
   sheet.Range["A10:A13"].BorderAround(LineStyleType.Thin);
   sheet.Range["A10:A13"].BorderInside(LineStyleType.Thin);
   //Save Document
   workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);
   System.Diagnostics.Process.Start("output.xlsx");
  }
 }
}

The above is the complete operation step for setting Excel multi-level grouping.

That's all for this article. Hope it helps everyone's learning and also hope everyone will support the呐喊 tutorial more.

Declaration: The content of this article is from the network, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (Please replace # with @ when sending an email for reporting, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)