Introduction

Microsoft Excel provides a good feature to Auto Size the width and height of a cell according to its content. This feature is also available to Aspose.Cells users with the power of auto sizing the dimensions of a cell at runtime.

AutoFitting the Contents

Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file.

A worksheet is represented by the Worksheet class. Worksheet class provides a wide range of properties and methods to manage a worksheet. But, in this topic, we will see that how can we use Worksheet class to AutoFit rows or columns.

AutoFit Row - Simple

The most simple approach of auto-sizing the width and height of a row is to call the AutoFitRow method of the Worksheet class. AutoFitRow method takes the row index (of the row whose auto-sizing is desired) as a parameter.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream = new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Auto-fitting the 3rd row of the worksheet

worksheet.AutoFitRow(2);

 

 

//Saving the modified Excel file in default (that is Excel 2003) format

workbook.Save("C:\\output.xls",FileFormatType.Default);

 

 

//Closing the file stream to free all resources

fstream.Close();

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Accessing the first worksheet in the Excel file

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Auto-fitting the 3rd row of the worksheet

worksheet.AutoFitRow(2)

 

 

'Saving the modified Excel file in default (that is Excel 2003) format

workbook.Save("C:\\output.xls",FileFormatType.Default)

 

 

'Closing the file stream to free all resources

fstream.Close()

 

[JAVA]

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Opening the Excel file

workbook.open("C:\\book1.xls");

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);

 

 

//Auto-fitting the 3rd row of the worksheet

worksheet.autoFitRow(2);

 

 

//Saving the modified Excel file in default (that is Excel 2003) format

workbook.save("C:\\output.xls",FileFormatType.DEFAULT);

 

AutoFit Row in a Range of Cells

We know that one row is composed of many columns/cells. Aspose.Cells also allows developers to auto-fit a row based on the content in a range of cells within the row by calling an overloaded version of AutoFitRow method that takes three parameters as follows:

• Row Index, represents the index of the row whose contents need to auto-fit
• First Column Index, represents the index of the first column of the row
• Last Column Index, represents the index of the last column of the row

This method will check the contents of all columns in the row and then auto-fit the row.

Example:

[C#]

 

 

//Auto-fitting the 3rd row of the worksheet based on the contents in a range of

//cells (from 1st to 9th column) within the row

worksheet.AutoFitRow(2,0,8);

 

 

[VB.NET]

 

 

'Auto-fitting the 3rd row of the worksheet based on the contents in a range of

'cells (from 1st to 9th column) within the row

worksheet.AutoFitRow(2,0,8)

 

 

[JAVA]

 

//Auto-fitting the 3rd row of the worksheet based on the contents in a range of

//cells (from 1st to 9th column) within the row

worksheet.autoFitRow(2,0,8);

 

AutoFit Column - Simple

The simplest approach of auto-sizing the width and height of a column is to call the AutoFitColumn method of the Worksheet class. AutoFitColumn method takes the column index (of the column whose auto-sizing is desired) as a parameter.

Example:

[C#]

 

 

//Auto-fitting the 4th column of the worksheet

worksheet.AutoFitColumn(3);

 

 

[VB.NET]

 

 

'Auto-fitting the 4th column of the worksheet

worksheet.AutoFitColumn(3)

 

 

[JAVA]

 

 

//Auto-fitting the 4th column of the worksheet

worksheet.autoFitColumn(3);

 

AutoFit Column in a Range of Cells

One column is composed of many rows/cells. Developers can auto-fit a column based on the content in a range of cells within the column by calling an overloaded version of AutoFitColumn method that takes three parameters as follows:

• Column Index, represents the index of the column whose contents need to auto-fit
• First Row Index, represents the index of the first row of the column
• Last Row Index, represents the index of the last row of the column

This method will check the contents of all rows in the column and then auto-fit the column.

Example:

[C#]

 

 

//Auto-fitting the 4th column of the worksheet based on the contents in a range of

//cells (from 1st to 9th row) within the column

worksheet.AutoFitColumn(3,0,8);

 

 

[VB.NET]

 

 

'Auto-fitting the 4th column of the worksheet based on the contents in a range of

'cells (from 1st to 9th row) within the column

worksheet.AutoFitColumn(3,0,8)

 

 

[JAVA]

 

 

//Auto-fitting the 4th column of the worksheet based on the contents in a range of

//cells (from 1st to 9th row) within the column

worksheet.autoFitColumn(3,0,8);

 

Important to Know

If a cell is merged then AutoFit methods will not be applied on that cell, which is same as that of Microsoft Excel. Moreover, if a cell's text is wrapped, AutFitColumn method will not be applied on it also. Another important thing to know is that AutoFit methods are time consuming methods. So, you should call these methods as less as possible to ensure your application's efficiency.