CODEWITHSUNDEEP
c#excel
Dhaval Patel
Dhaval Patel

Reputation: 7601

Getting ###### values while reading excel file in C#

I have one problem while reading excel file using C#. In my Excel file one column that is DateTime and it's value in excel file is long. So Excel will convert it into ###### format when I read the Same Excel using C# same value getting in C#. Can anyone please help: how can I get the Date Time value instead of #####?

My code looks like:

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
 ExcelObj = new Microsoft.Office.Interop.Excel.Application();
             Microsoft.Office.Interop.Excel.Workbook theWorkbook =   ExcelObj.Workbooks.Open(oldg.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing);
             Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
for (int i = 3; i <= worksheet.UsedRange.Rows.Count; i++)
{
    var getvalue = excel_getValue("B" + i);
}

Upvotes: 1

Views: 1217

Answers (3)

jwg
jwg

Reputation: 5847

You need to read Value2 rather than Value of the cell in question. Value will return a string related to the displayed contents of the cell, whereas Value2 will return an object which shows the understanding Excel has of that cell - ie. it could be a double, a DateTime, etc. and you will have to test it and cast it.

However, I'm not sure of exactly what you are doing in your code, and it looks like you have left something out in this line?

var getvalue = excel_getValue("B" + i);

If you meant something like 

worksheet.getValue("B" + i);

which should be the same thing as worksheet.Value["B" + i], just replace Value with Value2. If excel_getValue is your own function which does something similar, look for the access to the Value property and change it to Value2. I usually access things by row and column number and for me the two options would look like this:

string value = worksheet.Cells[2, i].Value;
object val2 = worksheet.Cells[2, i].Value2;

Upvotes: 0

ChrisF
ChrisF

Reputation: 137198

Your date conversion may be failing if you have a date colum and the value is out of range. If you enter "5555555555555555555555" (say) into a date column you get a series of "#####" and the tooltip states:

Dates and times that are negative or too large display as ######.

The most common causes of this would be that you have a date in US format (MM/dd/yyyy) and you are trying to convert it to a date in European format (dd/MM/yyyy) or you are not converting the string at all.

For more information on why Excel displays "######" instead of the actual column value see this Super User question

Upvotes: 4

Pranav
Pranav

Reputation: 8871

Try Like This :- 

var conv = DateTime.FromOADate("B"+i);

Upvotes: 1

Related Questions