[. NET development] C # How to create an Excel multilevel grouping

To set the display or hide the details under categorical data, it makes the document more aesthetically pleasing when it is easy to view and manage. So how do you create a multilevel group display of Excel data in C #? The following will be elaborated in detail. The free version of the component is used in the Spire.xls for. NET, download installs the component, after you create a console application, add the DLL file that references the component installation package to the project, and add the appropriate namespace. The following is a detailed code operation procedure, for reference.

Step one: Create a Wordbook class object, get the first worksheet

Workbook Workbook = new Workbook ();

Worksheet sheet = workbook. Worksheets[0];

Copy Code

Step Two: Write the data to the cell

Sheet. range["A1"]. Value = "Company Department";

Sheet. range["A3"]. Value = "General Department";

Sheet. range["A4"]. Value = "Administrative";

Sheet. range["A5"]. Value = "Personnel";

Sheet. range["A6"]. Value = "marketing Department";

Sheet. range["A7"]. Value = "Business unit";

Sheet. range["A8"]. Value = "Customer Service department";

Sheet. range["A9"]. Value = "Technical 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";

Copy Code

Step three: Set the Issummaryrowbelow property to False, that is, the summary row appears above the detail line

Sheet. Pagesetup.issummaryrowbelow = false;

Copy Code

Step four: Select the row to group, the parameter false to expand the current grouping, if you want to hide, set to True

Select rows for a first-level grouping

Sheet. Groupbyrows (2, (+), 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);

Copy Code

Step five: Define a CellStyle object, set and apply formatting 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;

Copy Code

Step Six: Set the area border style

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);

Copy Code

Step Seven: Save the document

Workbook. SaveToFile ("Output.xlsx", excelversion.version2013);

Copy Code

Run the program generation document (you can view the resulting document under the Bin>debug folder under the project folder)

Effect:

Full 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];

Writing data to cells

Sheet. range["A1"]. Value = "Company Department";

Sheet. range["A3"]. Value = "General Department";

Sheet. range["A4"]. Value = "Administrative";

Sheet. range["A5"]. Value = "Personnel";

Sheet. range["A6"]. Value = "marketing Department";

Sheet. range["A7"]. Value = "Business unit";

Sheet. range["A8"]. Value = "Customer Service department";

Sheet. range["A9"]. Value = "Technical 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, that is, the summary line appears above the detail line

Sheet. Pagesetup.issummaryrowbelow = false;

Select rows for a first-level grouping

The parameter false indicates that the current grouping is expanded to hide set to True

Sheet. Groupbyrows (2, (+), 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 font formatting in 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;

Set the area border style

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");

}

}

}

(Edit: Lelinpeng Source: Network)

[. NET development] C # How to create an Excel multilevel grouping