How to Auto size Excel ClosedXml cells in c#

Question

I am trying to resize cells so that it fits the maximum length of the text using ClosedXMl.Excel but the cell is giving me this error message:

Severity Code Description Project File Line Suppression State Error CS0119 'IXLRangeBase.Cells()' is a method, which is not valid in the given context

C#:

var workbook = new XLWorkbook();     //creates the workbook
var wsDetailedData = workbook.AddWorksheet("data"); //creates the worksheet with sheetname 'data'

wsDetailedData.Cell(1, 1).InsertTable(dataList); //inserts the data to cell A1 including default column name
wsDetailedData.Cells.SizeToContent = true;
workbook.SaveAs(@"C:\data.xlsx"); //saves the workbook

Solution

You can try to adjust the column & Rows instead of adjusting the cell:

wsDetailedData.Columns().AdjustToContents();  // Adjust column width
wsDetailedData.Rows().AdjustToContents();     // Adjust row heights

Comments

[Zeek2]

Thank you. The above worked well - but not perfectly - for me, as upgrading from ClosedXML 0.68.1 to 0.95.1 spoilt the previously very good default column widths :( The remaining problem: I have a date-time column that was not expanded - or was not sufficiently expanded, so displays as #######. Not terrible but annoying and inconvenient, esp. compared to 0.68.1 default behavior. A bug in ClosedXML 0.95.1 I would think :(.

[Zeek2]

The column widths in the Excel spreadsheet initially look very good when our webapp opens the generated Excel spreadsheet (similar to default behaviour in v0.68.1) but then the columns are shrunk down horribly and pointlessly :( Your fix code above does fix the most columns though, just not the date-time column. UPDATE: We generate another spreadsheet, that also required your fix after update but the Date-Time columns widths there look good. Hmm

[Sanushi Salgado]

Thanks! This worked for me. The columns get adjusted according to the data.