C# Excel Date value is coming as numeric value

Question

An excel file is generated by a third party app. I am writing a utility to further process that Excel file. When I read a column containing date value, I am getting a numeric string like 05/02/2016 (dd/MM/yyyy) is coming as 42405.

I have tried using .Text, .Formula, .Value, .Value2 - all the approaches are returning the numeric value.

I know about FromOADateTime, but my requirement is to read the date value as a string, exactly the way it is displayed on the screen.

Thanks.

EDIT 1: When I convert the column to Text in Excel, by using Format Cells and selecting Text, all the date values convert to numeric values even in Excel! Does that give any clue? Probably the third party app stores the data as numeric value, but Excel displays it as a date string on screen. I want to read the value displayed on screen.

EDIT 2:

This problem does not come with all the date values in that column. My first impression is that the third party app is not consistent while writing date values to Excel. But I have no control over it.

EDIT 3: Here is the link to download file: http://wikisend.com/download/316956/Prob.xls . In this file, I need to read all date values in Column B as text starting from Row 13

And here is the link to the screenshot of how it looks in my machine: http://wikisend.com/download/443994/Screenshot1.jpg

EDIT 4: Found the culprit in my code with the help of NineBerry's answer: a few lines before reading the Text property, I was calling ws.Columns.ClearFormats() and ws.Rows.ClearFormats();

I needed to do this as per this: How to get the range of occupied cells in excel sheet…, in order to correctly get the used range in the sheet!

The original problem is solved. Now the problem is how to use ws.UsedRange without calling ClearFormats() to correctly get the range of used cells.

My specific requirement is to get the range of used ROWS having data in any cells up to Col H. I Dont need the entire UsedRange in Excel, UsedRange within Col H is good enough for me.

Any solutions to this? Or should I post a new thread?

`exactly the way it is displayed on the screen` - then `.Text` should work for you. — GSerg, Oct 08 '16 at 11:02
I have also tried setting the NumberFormat to "@". But still I get numeric value. If I set the NumberFormat to "dd/MM/yyyy", I get the date time value but not in the same way the cell might be displaying; example cell may be displaying 05/02/2016, or 05-02-2016 etc. FYI, the column is containing mixed data; like some cells contain text string, and some contain date values, if that helps. — AllSolutions, Oct 08 '16 at 11:09
But the way it is displayed is variable depending on the client locale settings, so it doesn't make sense to have that requirement — Mister Magoo, Oct 08 '16 at 11:12
In my error message, I want to show him the text exactly the way he would see it (depending on his locale) if he were to open the Excel file and check! — AllSolutions, Oct 08 '16 at 11:13
You misunderstand how dates are handled and how formatting works. Excel does store dates as numbers and this is correct. The formatting you set does not work as you expect because the elements of the formatting string are placeholders, not literal values. If you set `NumberFormat` to `dd/MM/yyyy`, the `/` will mean "the date separator for the current locale", and if the date separator for the current locale is a `-` you will see that in the cell where `/` was in the formatting string. If you want a literal `/`, you need to escape it in the formatting string: `dd\/MM\/yyyy`. — GSerg, Oct 08 '16 at 11:29
If I change the NumberFormat to dd/MM/yyyy, I will get a string containing date and time stamp both. My requirement is simple: I want to read the date value just as Excel displays on the screen. Which part of my requirement have you not understood? — AllSolutions, Oct 08 '16 at 11:32
@AllSolutions, could you add a link to your file wit a different downloader provider? wikisend is being reported as "unsafe" by my browser — user3598756, Oct 08 '16 at 13:56

NineBerry · Accepted Answer · 2016-10-08T13:23:24.057

2

Using the Text property does give the string that would be displayed to a user.

      private void button_Click(object sender, EventArgs e)
      {
           string path = @"C:\Test.xlsx";

           Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
           Workbook wb = excel.Workbooks.Open(path);
           Worksheet excelSheet = wb.ActiveSheet;

           //Read a specific cell
           Range cell = excelSheet.Cells[1, 3] as Range;

           // Show text content
           MessageBox.Show(cell.Text);

           wb.Close();
      }

If you don't see here what you see in Excel, you have to give us more details. Maybe provide a sample Excel file where a cell does not behave as expected.

This code DOES output the content of the second column in the file provided exactly as displayed in Excel:

  private void button1_Click(object sender, EventArgs e)
  {
       string path = @"D:\Downloads\Windows\Prob.xls";

       Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
       Workbook wb = excel.Workbooks.Open(path);
       Worksheet excelSheet = wb.ActiveSheet;

       //Read a specific cell
       for (int i = 1; i <= 150; i++ )
       {
            Range cell = excelSheet.Cells[i + 1, 2] as Range;
            textBox1.AppendText(cell.Text + Environment.NewLine);

       }
       wb.Close();
  }

If you don't see that, you should check that you are actually reading the correct file, and that you do not modify the file yourself from code before reading.

edited Oct 08 '16 at 13:23

answered Oct 08 '16 at 11:39

NineBerry

26,306
3
62
93

Yes even I will like to share the Excel file. How can I share? There is no attachment facility in StackOverflow. – AllSolutions Oct 08 '16 at 11:51
Use a service like Dropbox, Microsoft OneDrive, Google Drive or similar and then post a link here. – NineBerry Oct 08 '16 at 11:57
Ok. Here is the link to download file: http://wikisend.com/download/316956/Prob.xls . In this file, I need to read all date values in Column B as text starting from Row 13. – AllSolutions Oct 08 '16 at 13:08
Which library are you using? For me, your code is not even compiling. Getting a few errors like: No overload for method 'Open' takes '1' arguments, and Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Worksheet' – AllSolutions Oct 08 '16 at 13:32
I am using standard Microsoft.Office.Interop.Excel. What are you using?!? You haven't shown any code so far. – NineBerry Oct 08 '16 at 14:01
In the standard Interop Excel library, there is no Open method which takes a single argument. I am using Microsoft Excel 12.0 Object Library. Anyway, I modified your code to compile and ran it, and the date values were coming properly using .Text property. I found the culprit in my code, a few lines before reading the Text property, I was calling ws.Columns.ClearFormats() and ws.Rows.ClearFormats();. I needed to do this as per this: http://stackoverflow.com/questions/1284388/how-to-get-the-range-of-occupied-cells-in-excel-sheet, in order to correctly get the used range in the sheet! – AllSolutions Oct 08 '16 at 14:07
So thanks for helping :) !! The original problem is solved. Now the problem is how to use ws.UsedRange without calling ClearFormats(). – AllSolutions Oct 08 '16 at 14:08
Please mark as answer. For your other problem, check the code at http://stackoverflow.com/questions/12276745/find-the-last-used-row-in-excel-with-c-sharp – NineBerry Oct 08 '16 at 14:19
Marked the answer. BTW, how did you compile the code by passing a single argument to the Open method? – AllSolutions Oct 08 '16 at 14:25
Using Excel 14 and Visual Studio 2015. Maybe not supported in older versions of VS / Excel Interop – NineBerry Oct 08 '16 at 14:27
@AllSolutions I think optional parameters in C# are available since Visual Studio 2010, so it will not compile on earlier versions without replacing all missing parameters with `Missing.Value` https://social.msdn.microsoft.com/Forums/vstudio/en-US/98c9418e-c5fe-40e5-a646-260251757397/why-use-object-missvalue-systemreflectionmissingvalue-?forum=csharpgeneral – Slai Oct 08 '16 at 14:41
It's probably formatted incorrectly in Excel. Be default, everything in excel is formatted as General. You can easily change it to the Date format in Excel. That should solve all your problems. – Dec 28 '16 at 15:29

C# Excel Date value is coming as numeric value

1 Answers1

Linked