using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published March 28, 2023

How to Unhide All Rows in Excel

1.0 Introduction

To prevent users from accessing unwanted areas of a worksheet, you can hide rows from their view. This technique is also used to hide unused or uninteresting sections, which helps keep users' attention on essential information. Additionally, hiding rows can be used to conceal sensitive data or calculations. Conversely, when changing your own sheets or studying inherited spreadsheets, you may want to unhide all rows and columns to access all data and understand dependencies. This article will cover both options. Hiding rows in Excel is particularly useful when working with large documents or when you want to hide unused rows to conceal information you won't need until later.

2.0 Hiding Rows in Excel

Rows can be hidden in Excel using the ribbon button, the right-click menu, or a keyboard shortcut, as with most basic actions in Excel.

To hide rows, start by selecting the rows you want to hide:

Click on a row's heading to select it.
Drag the mouse across the row headings to select multiple adjacent rows. Alternatively, select the last row while holding down the Shift key after selecting the first row.
Hold down the Ctrl key while clicking on the headings of additional rows you want to select when choosing non-contiguous rows by selecting the first row's heading first.

After selecting the rows, choose one of the following options:

2.1 Hiding Rows Using Ribbon Button

We can hide rows in Excel using the Ribbon button located at the top of the Excel sheet.

Step 1: Click the Format button in the Cells group under the Home tab.

Step 2: Choose Hide Rows from the Hide & Unhide menu under Visibility.

Excel Hide & Unhide

As another option, you can select Home > Format > Row Height and enter 0 in the field for Row Height. In either case, the selected rows will immediately be hidden from view.

If you don't want to search for the Hide command on the ribbon, you can access it from the context menu by right-clicking on the column or row header and choosing the Hide option.

Hide in context menu

2.3 Hiding Rows Using Keyboard Shortcut

We can also use a keyboard shortcut to hide rows in an Excel document by pressing Ctrl + 9 to hide all rows.

3.0 Unhiding Rows in Excel

Hidden rows can be unhidden using the ribbon button, right-click menu, or keyboard shortcut, as with most basic actions in Excel.

3.1 Hide rows using Ribbon Button

We are able to hide rows by using the Ribbon button available at the top of the Excel sheet.

Step 1: Click the Format button in the Cells group under the Home tab.

Step 2: Choose UnHide Rows from the Hide & Unhide menu under Visibility.

Hiding rows using the ribbon

We can unhide columns using yjr option from the context menu by right-clicking the chosen rows and choosing "Unhide".

Unhide rows

3.3 Hide using Keyboard Shortcut

We can use keyboard shortcuts to unhide all hidden rows in an Excel document by pressing Ctrl + Shift + 9, which helps us to make hidden rows visible again in the spreadsheet.

4.0 Hiding Rows Using IronXL

Microsoft Excel documents can be read and edited in C# using the IronXL .NET Framework. The standalone .NET software library for Excel can read a variety of spreadsheet formats, and there is no need to install Microsoft Excel or Interop.

The user-friendly C# API IronXL makes it easy to read, modify, and generate Excel spreadsheet files in the .NET environment. IronXL provides full support for .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure. One of the best Excel spreadsheet libraries for C# is IronXL, which is part of the .NET Framework and .NET Core.

4.1 IronXL Features

It is possible to edit, read, and load data from XLS, XLSX, CSV, and TSV.
Data export and storage in the formats CSV, TSV, JSON, XLS, and XLSX.
Ranges are simple to use. Syntax for WorkSheet["A1:B10"]. Ranges can logically be combined.
Columns, rows, and ranges can all be sorted.
Cell styles, such as: font, size, border, alignment, lock, freeze cells, hide/unhide rows, and number formats.

4.2 Hiding Rows in IronXL

Using IronXL, we can hide row numbers in Excel with just a few lines of code. We can hide multiple rows using IronXL, and below is a code example to hide a row in Excel.

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet
    ws = wb.GetWorkSheet("Sheet1");

ws.GetColumn(1).Hidden = true;

wb.SaveAs("sample1.xlsx");

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet
    ws = wb.GetWorkSheet("Sheet1");

ws.GetColumn(1).Hidden = true;

wb.SaveAs("sample1.xlsx");

Imports IronXL
Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

ws.GetColumn(1).Hidden = True

wb.SaveAs("sample1.xlsx")

VB C#

In the example above, we first load an existing Excel document using the WorkBook.LoadExcel method by providing the path and file name. Once the Excel document is loaded in the WorkBook object, we can select a specific worksheet using the WorkBook method GetWorkSheet and passing the sheet name as a parameter. This creates a WorkSheet object, which provides functionality for the entire worksheet.

After getting the WorkSheet object, we can select a column using the method GetColumn by passing the column index as a parameter. This allows us to use the boolean value hidden and set it to true, which allows us to hide and unhide rows in an Excel document. The above code is an example that allows us to hide the second column of the Excel document.

4.3 UnHide Rows in IronXL

We are able to unhide multiple rows in the excel, by using the following code:

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetColumn(1).Hidden = false;
wb.SaveAs("sample1.xlsx");

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetColumn(1).Hidden = false;
wb.SaveAs("sample1.xlsx");

Imports IronXL
Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

ws.GetColumn(1).Hidden = False
wb.SaveAs("sample1.xlsx")

VB C#

To unhide specific rows, we should follow the aforementioned steps and modify the instructions by selecting the column through the method GetColumn, either by passing the column index as a parameter or by specifying the column. This will allow us to set the boolean value of "hidden" to false.

Conclusion

The IronXL library is a development tool that provides all the advanced features required for a complex Excel application. It has an excellent feature that offers both developers and users a free trial, making it easy to evaluate its effectiveness. IronXL is one of the fastest libraries available, and developers can easily learn how to create an Excel document with just a few lines of code. With the library's assistance, we can perform a wide range of Excel functions. To learn more about IronXL, click here for more examples or refer to this link.