CODEWITHSUNDEEP
c#.netexcelrangeworksheet
Gobliins
Gobliins

Reputation: 4026

C# read data from excel file results in ###

i read data from an excel file with this code

        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        Microsoft.Office.Interop.Excel.Range range;

        int rCnt = 0;
        int cCnt = 0;
        string[,] data;

        xlWorkSheet = (Worksheet)wb.Worksheets.get_Item(sheetId);

        range = xlWorkSheet.UsedRange;

        data = new string[range.Rows.Count, range.Columns.Count];

        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {          
                data[rCnt - 1, cCnt - 1] = (string)
                (range.Cells[rCnt, cCnt] as Range).Text;
            }
        }

Now i am running sometimes into cells where the value in excel is displayed as ### because the column space is not big enough to display the number. In excel of course i can see the right value in the line on the top when i click on the cell but in my program i am getting the ### as value instead of the right number.

Any advice here?

Upvotes: 4

Views: 2331

Answers (3)

Steve
Steve

Reputation: 216293

I have seen numerous examples where the Range is read using the property Value OR Value2.
Could you try this?

data[rCnt - 1, cCnt - 1] = (string)(range.Cells[rCnt, cCnt] as Range).Value.ToString();

The Value2 property is similar to Value but don't translate well the Date columns, so it's better to use the Value if you have date columns in your Excel File. See here for an article on differences between Value and Value2

Upvotes: 1

Brijesh Mishra
Brijesh Mishra

Reputation: 2748

use (string) (range.Cells[rCnt, cCnt] as Range).Value as Value will return the actual value of a cell. Text will return what you actually see on the spreadsheet

Upvotes: 0

Dmitrii Erokhin
Dmitrii Erokhin

Reputation: 1347

Try using Range.Value property instead of Text.

So instead of 

(range.Cells[rCnt, cCnt] as Range).Text;

you would write 

(range.Cells[rCnt, cCnt] as Range).Value;

Upvotes: 4

Related Questions