How to Autofit Cells in Excel Using C#

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

Step 2: 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

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

// Save the autofitted workbook as a new file
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);
}

// Save the autofitted workbook as a new file
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

' Save the autofitted workbook as a new file
workBook.SaveAs("autoResize.xlsx") ' Result file

Code Explanation

1. Initially, we load the FinancialSample.xlsx using the WorkBook.Load() method.
2. Then we get the count of columns for adjustments.
3. We iterate through all column indexes to auto-size the column widths.
4. Finally, we save the results to a new file named 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++)
{
    // Auto-fit row height for index i
    workSheet.AutoSizeRow(i);
}

// Save the autofitted workbook as a new file
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++)
{
    // Auto-fit row height for index i
    workSheet.AutoSizeRow(i);
}

// Save the autofitted workbook as a new file
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
	' Auto-fit row height for index i
	workSheet.AutoSizeRow(i)
Next i

' Save the autofitted workbook as a new file
workBook.SaveAs("autoResizeRows.xlsx") ' Result file

Code Explanation

1. Initially, we load the FinancialSample.xlsx using the WorkBook.Load() method.
2. We retrieve the total row count.
3. We iterate through all row indexes to auto-size the row heights.
4. Finally, we save the results to a new file named 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 the option to manually adjust the width and height 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;

// Manually set row height and column width
RangeRow row = workSheet.GetRow(0);
row.Height = 500; // Set height
RangeColumn col = workSheet.GetColumn(0);
col.Width = 5000; // Set width

// Save the workbook with specific values set
workBook.SaveAs("specificValue.xlsx");

using IronXL;
// Load example spreadsheet
WorkBook workBook = WorkBook.Load("FinancialSample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Manually set row height and column width
RangeRow row = workSheet.GetRow(0);
row.Height = 500; // Set height
RangeColumn col = workSheet.GetColumn(0);
col.Width = 5000; // Set width

// Save the workbook with specific values set
workBook.SaveAs("specificValue.xlsx");

Imports IronXL
' Load example spreadsheet
Private workBook As WorkBook = WorkBook.Load("FinancialSample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Manually set row height and column width
Private row As RangeRow = workSheet.GetRow(0)
row.Height = 500 ' Set height
Dim col As RangeColumn = workSheet.GetColumn(0)
col.Width = 5000 ' Set width

' Save the workbook with specific values set
workBook.SaveAs("specificValue.xlsx")

Code Explanation

1. Load the FinancialSample.xlsx using the WorkBook.Load() function.
2. Retrieve specific rows and columns to manually adjust their height and width.
3. Set the desired height and width values.
4. Save the modified 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"
}

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.