Jak automatycznie dopasować komórki w programie Excel przy użyciu języka C#

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.

Plik wejściowy

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

Wyjaśnienie kodu

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.

Plik wyjściowy

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

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.

Plik Excel do przetworzenia

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

Wyjaśnienie kodu

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.

Plik wyjściowy w formacie Excel

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.

Plik Excel do przetworzenia

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")

Wyjaśnienie kodu

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.

Plik wyjściowy w formacie Excel