CODEWITHSUNDEEP
c#excel-interop
BHuelse
BHuelse

Reputation: 2981

How to disable autoformat, when writing to Excel-Cell from C#

I have written an application which writes content into an Excelsheet. So far so good. But in some cases the autoformat function from Excel changed my values. For example, I write 'true'/'false' to a cell, but Excel interprets it and translate it. Second example, I write a telephonenumber to Excel and Excel cuts the leading '0', so '012345' changed to '12345'.

How can I say, that a cell is just a text/string cell?

This is a samplecode, how I write to Excel:

ws.get_Range("A1", "A1").Value = "true";
ws.get_Range("A1", "A1").Cells.WrapText = true;
ws.get_Range("A1", "A1").Rows.AutoFit();

best regards

Upvotes: 1

Views: 2130

Answers (2)

Slai
Slai

Reputation: 22876

Another way to make Excel treat values as text is to add ' in front of the values:

ws.get_Range("A1").Value = "'0123";

The ' will be visible only in the cell formula but not in it's value/text.

You can also set multiple cells at once:

object[,] values = { { "true", "0123" }, { "false", "0125" } };
var range = ws.get_Range("A1:B2")
range.NumberFormat = "@"; // Text format to treat the values as Text
range.Value = values

or

Clipboard.SetText("'true\t'01234\n'false\t'01235");
ws.get_Range("A1").PasteSpecial(); // or just ws.Paste();

Upvotes: 2

DeeGo
DeeGo

Reputation: 17

Try this for leading zero

ws.get_Range("A1", "A1").NumberFormat = "@";
ws.get_Range("A1", "A1").Value = "012345";

For boolean value

ws.get_Range("B1", "B1").Value = "True/False";

Upvotes: 0

Related Questions