Search code examples
c#closedxml

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

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?

Solution

  • Update for version 0.100:

    Starting with version 0.100 you don't need to set a datatype for a cell, because the type of the cell value will be used.

    From the migration guide:

    SetDataType methods removed
    Method SetDataType has been removed from all interfaces (IXLCell, IXLColumn, IXLColumns, IXLRange …). There is no replacement, if you need to reinterpret existing data, do it in application code and set a new value with a specific type.

    For older versions (up to 0.97):

    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).