Hide and Show Rows or Columns in Excel using C#

Hide or Show Rows and Columns in Excel using C#

As a C# developer, you can easily hide and show the rows or columns in the Excel worksheets programmatically. In this article, you will learn how to hide and show rows or columns of an Excel sheet using C#.

The following topics are discussed/covered in this article:

C# API to Hide and Show Rows or Columns

For hiding and showing the rows and columns in an Excel sheet, I will be using Aspose.Cells for .NET API. It is a well-known spreadsheet manipulation API that lets you create and process Excel files from within your .NET applications. The API allows you to hide any of the rows and columns in the Excel files or show the hidden rows and columns programmatically.

You can either download the DLL of the API or install it using NuGet.

Install-Package Aspose.Cells

Hide Rows and Columns using C#

You can hide rows and columns in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Hide the row by calling the HideRow() method and pass the row index to hide.
  • Hide the column by calling the HideColumn() method and pass the column index to hide.
  • Call the Save() method with the output file path.

The following code sample shows how to hide the rows and columns in an Excel sheet using C#.

Hide Rows and Columns using C#
Hide Rows and Columns using C#.

The Workbook class represents an Excel workbook and provides several properties and methods to work with the workbook. The Worksheets property of this class represents the collection of available worksheets. The Worksheet class represents a single worksheet of an Excel workbook. It exposes several properties and methods to perform various operations on the worksheet. The Cells property of this class represents a collection of cells available in the worksheet.

The HideRow() method of the Cells class hides a specific row. It takes a row index as an input parameter to hide that row. The Cells class also provides the HideColumn() method to hide a specific column based on the column index provided as an input parameter.

The Save() method of the Workbook class saves the workbook at the specified file path provided as an input parameter.

Show Hidden Rows and Columns using C#

You can show the specific hidden rows and columns in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Show the hidden row by calling the UnhideRow() method
  • Pass the row index of the hidden row and row height to set.
  • Show the hidden column by calling the UnhideColumn() method
  • Pass the column index of the hidden column and column width to set.
  • Call the Save() method with the output file path.

The following code sample shows how to show the specific hidden rows and columns in an Excel sheet using C#.

Show Hidden Rows and Columns using C#
Show Hidden Rows and Columns using C#

The UnhideRow() method of the Cells class shows a specific hidden row in the worksheet. It takes a row index of a hidden row as an input parameter to make it visible. The Cells class also provides the unhideColumn() method to show a hidden column based on the column index provided as an input parameter.

Hide Multiple Rows and Columns using C#

You can hide multiple rows and columns in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Call the HideRows() method, pass the starting row index and total rows to hide.
  • Call the HideColumns() method, pass the starting column index and total columns to hide.
  • Save by calling the Save() method with the output file path.

The following code sample shows how to hide multiple rows and columns in an Excel sheet using C#.

Hide Multiple Rows and Columns using C#
Hide Multiple Rows and Columns using C#.

The Cells class provides the HideRows() method to hide multiple rows. You need to specify the starting row index and the total number of rows to hide as input parameters. Similarly, for hiding multiple columns, the Cells class provides the HideColumns() method that takes the column index and the total number of columns to hide as input parameters.

Show All Hidden Rows and Columns using C#

You can show all the hidden rows and columns in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Check IsHidden property for all rows one by one, if true then
    • Call the UnhideRow() method with row index and row height to set.
  • Check IsHidden property for all columns one by one, if true then
    • Call the UnhideColumn() method with column index and column width to set.
  • Call the Save() method with the output file path.

The following code sample shows how to show all the hidden rows and columns in an Excel sheet using C#.

Show All Hidden Rows and Columns using C#
Show All Hidden Rows and Columns using C#.

The IsHidden property of the Row class indicates whether the row is hidden. Similarly, the IsHidden property of the Column class indicates whether the column is hidden.

Get a Free License

You can try the API without evaluation limitations by requesting a free temporary license.

Conclusion

In this article, you have learned how to hide columns and rows in Excel files using C#. You have also learned how to show hidden columns and rows in Excel files programmatically. Moreover, you have learned how to hide multiple rows and columns in an Excel sheet. This article also explained how to show all hidden rows and columns in Excel using C#. You can learn more about Aspose.Cells for .NET API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also