How to set a data type for a column with ClosedXML?

Question

I see a lot of examples in documentation where data type for a cell is set:

ws.Cell(1, 1).SetDataType(XLCellValues.Text);

But when I try to set a data type for a column:

ws.Column(1).SetDataType(XLCellValues.Text)

ClosedXML generates a 5MB file and data type is not actually set.

This operation works as expected in Excel (right-click on the column header, format cells, ...).

If it is a limitation of ClosedXML, can it be easily fixed, or is there a workaround?

Answer 1

ClosedXML sets the data type for all cells in the column, down to the maximum (row 1 million or so). Use this to only set the data type for the used cells in the column:

ws.Column(1).CellsUsed().SetDataType(XLDataType.Text);

Furthermore, the data type in ClosedXML is not the same as the cell format in Excel (have a look at the example file from the documentation to see the difference).