In Microsoft Excel, the row height and column width of a cell are set as 15 and 8.43 inches by default. Sometimes you may need to resize the row heights and column widths to ensure that all data in your report displays clearly. Microsoft Excel offers several ways to manipulate the row height and column width, for instance, manually changing the row height and column width to a specific value or having them adjusted automatically to fit the data. In this article, I will demonstrate how to programmatically achieve these tasks in C# and VB.NET using Free Spire.XLS for .NET library.
Installation
You can install Free Spire.XLS for .NET via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console, and then executing the following command:
PM> Install-Package FreeSpire.XLS
Alternatively, you can also download the API from this website and install it, then add the DLL files under the Bin folder to your project as references.
Change the Column Width and Row Height in Excel in C# and VB.NET
The following are the steps to change the width of a column and the height of a row:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile(string fileName) method.
- Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
- Change the height of a specific row using Worksheet.SetRowHeight(int rowIndex, double height) method. Note the row index here is 1-based.
- Change the width of a specific column using Worksheet.SetColumnWidth(int columnIndex, double width) method. Note the column index here is 1-based.
- Save the result file using Workbook.SaveToFile(string filename, ExcelVersion version) method.
C#
using Spire.Xls;
namespace ChangeColumnWidthAndRowHeight
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Test.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Change the height of the first row
sheet.SetRowHeight(1, 108);
//Change the width of the first column
sheet.SetColumnWidth(1, 26.7);
//Save the result file
workbook.SaveToFile("ChangeColumnWidthAndRowHeight.xlsx", ExcelVersion.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Namespace ChangeColumnWidthAndRowHeight
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'Create a Workbook instance
Dim workbook As Workbook = New Workbook()
'Load an Excel file
workbook.LoadFromFile("Test.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Change the height of the first row
sheet.SetRowHeight(1, 108)
'Change the width of the first column
sheet.SetColumnWidth(1, 26.7)
'Save the result file
workbook.SaveToFile("ChangeColumnWidthAndRowHeight.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
AutoFit the Column Width and Row Height in Excel in C# and VB.NET
You can autofit the column widths and row heights of a specific cell range or the used cell range in an Excel worksheet. The steps are as follows:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile(string fileName) method.
- Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
- Get a specific cell range through Worksheet.Range[int row, int column, int lastRow, int lastColumn] property. Or get the used cell range through Worksheet.AllocatedRange property.
- AutoFit the column widths and the row heights of the cell range using CellRange.AutoFitColumns() and CellRange.AutoFitRows() methods.
- Save the result file using Workbook.SaveToFile(string filename, ExcelVersion version) method.
C#
using Spire.Xls;
namespace AutoFitColumnWidthAndRowHeight
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Test1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get a specific cell range in the worksheet
//CellRange range = sheet.Range[1, 1, 10, 5];
//Get the used cell range in the worksheet
CellRange usedRange = sheet.AllocatedRange;
//AutoFit the column widths and row heights of the used cell range
usedRange.AutoFitColumns();
usedRange.AutoFitRows();
//Save the result file
workbook.SaveToFile("AutoFitColumnWidthAndRowHeight.xlsx", FileFormat.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Namespace AutoFitColumnWidthAndRowHeight
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'Create a Workbook instance
Dim workbook As Workbook = New Workbook()
'Load an Excel file
workbook.LoadFromFile("Test1.xlsx")
'Get the first worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get a specific cell range in the worksheet
'CellRange range = sheet.Range[1, 1, 10, 5];
'Get the used cell range in the worksheet
Dim usedRange As CellRange = sheet.AllocatedRange
'AutoFit the column widths and row heights of the used cell range
usedRange.AutoFitColumns()
usedRange.AutoFitRows()
'Save the result file
workbook.SaveToFile("AutoFitColumnWidthAndRowHeight.xlsx", FileFormat.Version2013)
End Sub
End Class
End Namespace
Office Dev Blog 