CODEWITHSUNDEEP
c#.netexcel-interopnumber-formatting
Jeffrey Rasmussen
Jeffrey Rasmussen

Reputation: 393

Edit Range NumberFormat in existing document

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)

Upvotes: 3

Views: 11505

Answers (5)

Tamás Varga
Tamás Varga

Reputation: 25

For me using the Style and the NumberFormatLocal solved the problem:

sheet_.get_Range("A1", "A100").Style.NumberFormatLocal = "0";

Upvotes: 0

Grinfish
Grinfish

Reputation: 368

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

Upvotes: 4

hima
hima

Reputation: 620

I know this is a old post but I stumbled over this and have a solution for this. Have you tried to not assign any NumberFormat? by default excel decides based on the cell content so you wouldnt get green triangle if you have numbers which are stored as text. If you want read values based on data type then refer this post

http://www.codeproject.com/Articles/335589/Export-Multiple-Datasets-to-Multiple-Excel-sheets

Upvotes: 0

Jeffrey Rasmussen
Jeffrey Rasmussen

Reputation: 393

I didn't find ideal solution to this issue and ended with the following:

    sheet_.get_Range("A1", "A100").NumberFormat = "0";

    for(int i = 1; i <= 100; i++)
    {
       sheet_.Cells[1, i].Value = sheet_.Cells[1, i].Value;
    }

Upvotes: 0

Sylca
Sylca

Reputation: 2545

Try to access to cell value over get_Range method! for example and for what number format you want, lets say that you have in your excel cell this number : 1546,65

sheet_.get_Range("P10", "Q10").NumberFormat = "0"; // returns 1546
sheet_.get_Range("P10", "Q10").NumberFormat = "0,00"; // returns 1546,65
sheet_.get_Range("P10", "Q10").NumberFormat = "#.##0,00"; // returns 1.546,65

And you can play with these number formats!

Hope it helps you

Upvotes: 3

Related Questions