USING IRONXL

How to Autofit Cells in Excel Using C#

Updated April 30, 2024
Share:

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.

  1. Install IronXL library to the project.
  2. Autofit Column width using IronXL library.

  3. Autofit Row height using IronXL library.

  4. 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

How to Autofit Cells in Excel Using C#: Figure 1 - Open Visual Studio, and Add a new project of type Console Application in C#.

Provide a project name and location

How to Autofit Cells in Excel Using C#: Figure 2 - Next, specify Project name and Location.

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

How to Autofit Cells in Excel Using C#: Figure 3 - Then, select the required .NET Framework version and click on Create.

Step2: Install IronXL library to the project.

Install IronXL library from Visual Studio NuGet Package Manager

How to Autofit Cells in Excel Using C#: Figure 4 - Install IronXL using the Manage NuGet Package for Solution by searching "IronXL" in the search bar of NuGet Package Manager, then select the project and click on the Install button.

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
VB   C#

How to Autofit Cells in Excel Using C#: Figure 5 - IronXL.Excel NuGet package

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.

How to Autofit Cells in Excel Using C#: Figure 6 - Input Excel 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
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
VB   C#

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.

How to Autofit Cells in Excel Using C#: Figure 7 - Output excel with auto-sized column width.

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.

How to Autofit Cells in Excel Using C#: Figure 8 - Input Excel 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
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
VB   C#

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

How to Autofit Cells in Excel Using C#: Figure 9 - Output excel with auto-sized column height.

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

How to Autofit Cells in Excel Using C#: Figure 10 - 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")
VB   C#

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

How to Autofit Cells in Excel Using C#: Figure 11 - Output excel with manual-sized column width and height.

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
VB   C#

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.

NEXT >
How to parse an Excel file in Python

Ready to get started? Version: 2024.5 just released

Start Free Trial Total downloads: 823,510
View Licenses >