Excel Row and Column AutoFitin C# and VB.NET

GemBox.Spreadsheet supports autofitting column width and row height so that column / row takes just enough space to make content of all its cells visible.

Following sample shows how to autofit all columns in an Excel worksheet with C# and VB.NET.

Notice the usage of CalculateMaxUsedColumns method to get the total number of columns with a non-empty cells in a worksheet.

Screenshot

Excel Row and Column AutoFit Screenshot

See the full code below, use RUN EXAMPLE to execute.

Upload your file (Drag files here)

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
17
18        var ws = ef.Worksheets[0];
19
20        int columnCount = ws.CalculateMaxUsedColumns();
21        for (int i = 0; i < columnCount; i++)
22            ws.Columns[i].AutoFit(1, ws.Rows[1], ws.Rows[ws.Rows.Count - 1]);
23
24        ef.Save("Row_Column AutoFit.xls");
25    }
26}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")
16
17        Dim ws = ef.Worksheets(0)
18
19        Dim columnCount = ws.CalculateMaxUsedColumns()
20        For i As Integer = 0 To columnCount - 1
21            ws.Columns(i).AutoFit(1, ws.Rows(1), ws.Rows(ws.Rows.Count - 1))
22        Next
23
24        ef.Save("Row_Column AutoFit.xls")
25
26    End Sub
27
28End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.