Edit Range NumberFormat in existing document

Question

I can't change column format in an existing Excel document (xlsx). Columns content are numbers actually but shown as text and therefore green triangle appear telling that cells shown as text.

So I open this document in C# app and do the following thing:

sheet_.Range[sheet_.Cells[1, 2], sheet_.Cells[rowNum, 2]].EntireColumn.NumberFormat = "0";                

But it doesn't change column to appear content as numbers (they remain aligned by left side)

Answer 1

I know this is an old post, but I've been dealing with the same problem. I receive .xlsx files that already have green triangles denoting "Number as Text" errors. I couldn't find a way to programmatically run the Excel error-checking command "Convert to Number" that you can do by clicking in Excel, and changing the NumberFormat on cells with these errors didn't work for me, but I was able to "refresh" the cell format by using the TextToColumns method.

int lastCol = sheet.UsedRange.Columns.Count;
        if(lastCol > 1)
        {
            for (int i = 1; i <= lastCol; i++)
            {
                sheet.Columns[i].TextToColumns(Type.Missing, XlTextParsingType.xlDelimited, XlTextQualifier.xlTextQualifierNone);
            }

And from there you can change the NumberFormat. I happened to have long integers that were getting put into scientific notation, so I used this to make them regular integers again:

sheet.Cells.NumberFormat = "#";

(PS, if anyone finds a definitive guide on the symbols to use for customized NumberFormats, I'm still trying to find one!)