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

c# closedxml
astef picture astef · Nov 18, 2015 · Viewed 24.5k times · Source

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

Raidri supports Monica picture Raidri supports Monica · Nov 18, 2015

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

Related questions

Reading from Excel File using ClosedXML

My Excel file is not in tabular data. I am trying to read from an excel file. I have sections within my excel file that are tabular. I need to loop through rows 3 to 20 which are tabular and read the …

Read More
Download file with ClosedXML

All How can I download a file so the user sees that it is downloading (like with a stream?) I am currently using ClosedXML, but if I use the SaveAs method, I have to give a hard-coded URL, and if …

Read More
How to make Excel wrap text in formula cell with ClosedXml

The problem is that the cell content is not wrapped, when that cell contains a formula referring to a cell with some long string. On CodePlex I found a thread on this issue and a simple code to see the …

Read More