CODEWITHSUNDEEP
c#.netexcelclosedxml
Lucas
Lucas

Reputation: 83

ClosedXML - Setting data type for cell does not work

I have similar question about ClosedXML, like this one but a little bit diferent. The SetDataType method does not work. For example, I have value string dps = "3.12.02" which is not a date nor number, it is valid text.

When I do this: ws.Cell(1, 1).Value = dps; ws.Cell(1, 1).SetDataType(XLCellValues.Text);

and save the file and then open it in Excel, it still convert it to some rubish like 37593 I tried to put it before and after setting the value, no change.

Can anybody help me please?

Upvotes: 5

Views: 10045

Answers (4)

Sumit Jambhale
Sumit Jambhale

Reputation: 635

For setting the cell data type of a particular column one can use the below function from the closedXML

ws.Column(2).Style.NumberFormat.SetNumberFormatId(
(int)XLPredefinedFormat.Number.Text);

ws is the worksheet object,

Here Column(2) is the second column from excel sheet

Note: The Column count in excel starts from 1 and not 0

Upvotes: 3

AnthonyVO
AnthonyVO

Reputation: 3983

I realize you are trying to set a Text value but I have found that LibreOffice created Excel files seem to somehow mess up ClosedXMLs ability to set a column to a DateTime data type.

// What I tried without success.
cell.Clear(XLClearOptions.All);
cell.SetValue<DateTime>((DateTime)cellValue);
cell.SetDataType(XLDataType.DateTime);

Nothing I did would fix the issue until I recreated the spreadsheet in Excel.

// This worked just fine on a proper Excel file
cell.SetValue((DateTime)cellValue);

As soon as I opened and saved the spreadsheet with LibreOffice, the ability to correctly set the column type disappeared.

Upvotes: 2

Francois Botha
Francois Botha

Reputation: 4849

The .Value method tries to guess the type of the value that you're setting. In your case, the value is a valid (i.e. parseable) DateTime, which are internally stored as numbers.

To set a value explicitly, without guessing, use:

string dps = "3.12.02";
ws.Cell(1, 1).SetValue(dps);

Upvotes: 16

Goran Mottram
Goran Mottram

Reputation: 6304

Although I can't tell you why it's not working, one easy way is to prepend your string with an apostrophe ('). This forces Excel to treat everything that follows as text.

string dps = "'3.12.02";
ws.Cell(1, 1).Value = dps;

Upvotes: 2

Related Questions