Introduction

In today's data-driven world, Microsoft Excel remains an indispensable tool for businesses and professionals alike. Whether it's crunching numbers, creating reports, or visualizing data, Excel's versatility is unmatched. However, when it comes to programmatically manipulating Excel files in C#, developers often face challenges. This is where IronXL library comes into play, offering a seamless solution for Excel automation within the C# environment.

IronXL library from Iron Software provides intuitive APIs for handling Excel documents seamlessly in both C# .NET and VB.NET environments. With IronXL, there's no requirement to install Microsoft Office or Excel Interop components. It enables developers to effortlessly read, edit, and create Excel spreadsheet files, offering full support for various .NET frameworks including .NET 8, 7, 6, Core Frameworks, and Azure environments.

In this article, we will learn how to autofit column width and height in Excel using C# and IronXL.

1. Create a Visual Studio Project to Demo Autofit Cells in Excel worksheet.

2. Install IronXL library to the project.
3. Autofit Column width using IronXL library.
4. Autofit Row height using IronXL library.
5. Set Column width and Row height Manually using IronXL library.

IronXL

IronXL library is a versatile .NET library designed to simplify Excel spreadsheet manipulation for C# developers. Offering a fast and intuitive API, IronXL enables users to effortlessly access, modify, and generate Excel files without relying on Office Interop. Its compatibility extends to various .NET frameworks, including .NET Core and Azure, without requiring any special dependencies or the installation of Microsoft Office. With support for multiple .NET languages such as C#, VB.NET, and F#, IronXL caters to a wide range of development scenarios, including console, web, and desktop applications. Moreover, IronXL's compatibility spans across different platforms, including Windows, Linux, macOS, Docker, Azure, and AWS, making it an indispensable tool for developers seeking efficient Excel integration across diverse environments.

Key Features of IronXL library

1. Easy Integration: IronXL seamlessly integrates with C# applications, providing a simple and intuitive API for Excel automation. Developers can quickly get started with a minimal learning curve.
2. Read and Write Excel Files: With IronXL, you can effortlessly read data from existing Excel files and write data to new or existing spreadsheets. This capability is essential for tasks such as data import/export and report generation.
3. Formatting and Styling: IronXL enables you to apply formatting and styling to Excel cells, including fonts, colors, borders, and more. This ensures that your generated Excel files maintain a professional and polished appearance.
4. Formula Support: Need to perform complex calculations or apply formulas to Excel data? IronXL has you covered with support for Excel formulas, allowing you to automate calculations and data manipulation.
5. Autofit Cells: One of the handy features offered by IronXL is the ability to autofit cells in Excel, ensuring that the content fits neatly within each cell without truncation.

Step 1: Create a Visual Studio Project to Demo Autofit Cells in Excel worksheet.

To get started, let's create a Visual Studio project as shown below. Select the Console application template from the list

Provide a project name and location

Select the required .NET version from the dropdown and click create

Step2: Install IronXL library to the project.

Install IronXL library from Visual Studio NuGet Package Manager

The package can also be installed using the NuGet Command line

dotnet add package IronXL.Excel --version 2024.3.20
// or
Install-Package IronXL.Excel

dotnet add package IronXL.Excel --version 2024.3.20
// or
Install-Package IronXL.Excel

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'dotnet add package IronXL.Excel --version 2024.3.20 Install-Package IronXL.Excel

With this we are ready to start coding.

Step 3: Autofit Column width using IronXL library

The AutoSizeColumn method from IronXL library adjusts the width of a column to fit its content. This method calculates the text width based on the font used in the cell. If the exact font isn't available on the system, it will attempt to measure it using a default font. If no default font is found, it will try to utilize any available font. However, if no fonts are found, an exception will be thrown. It uses a zero-based column index as a parameter. It must be within the bounds defined by 0 and (ColumnCount - 1). ArgumentException is thrown if the provided columnIndex is outside the valid range. SixLabors.Fonts.FontException is thrown when no fonts are installed on the machine.

Now, let's see how we can use this to resize an Excel file. You can download a sample Excel file from here.

Input File

Here you can see the column widths are not properly defined and hence very difficult to read. Now let's
see how we can use the IronXL library to Autofit column widths in an excel sheet.

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
var colCount = workSheet.Columns.Count();
for (int i = 0; i < colCount; i++)
{ 
    // auto fit column width for index i
    workSheet.AutoSizeColumn(i);
}
workBook.SaveAs("autoResize.xlsx"); // result file

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
var colCount = workSheet.Columns.Count();
for (int i = 0; i < colCount; i++)
{ 
    // auto fit column width for index i
    workSheet.AutoSizeColumn(i);
}
workBook.SaveAs("autoResize.xlsx"); // result file

Imports IronXL
' Load example spreadsheet
Private workBook As WorkBook = WorkBook.Load("FinancialSample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private colCount = workSheet.Columns.Count()
For i As Integer = 0 To colCount - 1
	' auto fit column width for index i
	workSheet.AutoSizeColumn(i)
Next i
workBook.SaveAs("autoResize.xlsx") ' result file

Code Explanation

1. Initially we load the FinancialSample.xlsx using WorkBook.Load() method.
2. Then we get the cell range for which the column widths need to be adjusted, in this case we get the column count.
3. Then we iterate through all the indexes to auto-size the column widths
4. Then we save the result to a new file with the name autoResize.xlsx

Output File

Below you can see all the columns are auto-sized and visible.

Step 4: Autofit Row height using IronXL library.

The AutoSizeRow method adjusts the height of a row to accommodate its content. It's important to note that this method calculates the text height based on the font used in the cell. If the exact font isn't available on the system, it will attempt to measure it using a default font. If no default font is found, it will try to utilize any available font. However, if no fonts are found, an exception will be thrown. It uses a zero-based row index as a parameter. It must be within the bounds defined by 0 and (RowCount - 1). ArgumentException is thrown if the provided rowIndex is outside the valid range. SixLabors.Fonts.FontException is thrown when no fonts are installed on the machine

Input Excel File

In the below example, you can see some of the rows are not sized to fix the data appropriately. Now let's look into the C# code to autofit excel row heights.

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
var rowCount = workSheet.RowCount;
for (int i = 0; i < rowCount; i++)
{
    workSheet.AutoSizeRow(i);
}
workBook.SaveAs("autoResizeRows.xlsx"); // result file

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
var rowCount = workSheet.RowCount;
for (int i = 0; i < rowCount; i++)
{
    workSheet.AutoSizeRow(i);
}
workBook.SaveAs("autoResizeRows.xlsx"); // result file

Imports IronXL
' Load example spreadsheet
Private workBook As WorkBook = WorkBook.Load("FinancialSample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private rowCount = workSheet.RowCount
For i As Integer = 0 To rowCount - 1
	workSheet.AutoSizeRow(i)
Next i
workBook.SaveAs("autoResizeRows.xlsx") ' result file

Code Explanation

1. Initially, we load the FinancialSample.xlsx using WorkBook.Load() method.
2. Then we get the cell range for which the row heights needs to be adjusted, in this case we get the row count.
3. Then we iterate through all the indexes to auto-size the row heights
4. Then we save the result to a new file with the name autoResizeRows.xlsx

Output Excel File

Step 5: Set Column width and Row height Manually using IronXL

In addition to auto-sizing rows and columns, IronXL also offers to adjust the width and height manually to a specific row height value and specific column width value

Input Excel File

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
RangeRow row = workSheet.GetRow(0);
row.Height = 500; // Set height
RangeColumn col = workSheet.GetColumn(0);
col.Width = 5000; // Set width
workBook.SaveAs("specificValue.xlsx");

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
RangeRow row = workSheet.GetRow(0);
row.Height = 500; // Set height
RangeColumn col = workSheet.GetColumn(0);
col.Width = 5000; // Set width
workBook.SaveAs("specificValue.xlsx");

Imports IronXL
' Load example spreadsheet
Private workBook As WorkBook = WorkBook.Load("FinancialSample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private row As RangeRow = workSheet.GetRow(0)
row.Height = 500 ' Set height
Dim col As RangeColumn = workSheet.GetColumn(0)
col.Width = 5000 ' Set width
workBook.SaveAs("specificValue.xlsx")

Code Explanation

1. Initially, we load the FinancialSample.xlsx using WorkBook.Load() function.
2. Then we get the cell range for which the row heights needs to be adjusted and also the cell range for which the column widths needs to be adjusted
3. Then we set the height and width values
4. Then we save the result to a new file with the name specificValue.xlsx

Output Excel File

Licensing

IronXL.

The obtained license has to be placed in the appSettings.json file

{
  "IronXL.License.LicenseKey":"myTrialKey"
}

{
  "IronXL.License.LicenseKey":"myTrialKey"
}

If True Then
  "IronXL.License.LicenseKey":"myTrialKey"
End If

Conclusion

With features like AutoSizeColumn and AutoSizeRow from IronXL, developers can effortlessly ensure that Excel spreadsheets dynamically adjust to the content they contain, whether it's imported data, dynamic reports, user-generated content, or data analysis results.

IronXL's compatibility with various .NET frameworks, including .NET Core and Azure, ensures that developers can incorporate Excel automation into a wide range of applications without dependencies on Microsoft Office or Excel Interop. Moreover, IronXL's support for multiple .NET languages and platforms makes it a versatile choice for developers seeking to streamline Excel manipulation tasks across diverse environments.

In essence, IronXL empowers developers to enhance productivity, accuracy, and flexibility in Excel automation projects, offering a comprehensive suite of features to meet the demands of modern C# development. Whether it's generating reports, analyzing data, or presenting information, IronXL provides the tools necessary to excel in Excel automation within the C# ecosystem.