C#Excel日期值即将作为数字值 [英] C# Excel Date value is coming as numeric value

问题描述

Excel文件是由第三方应用程序生成的.我正在编写一个实用程序来进一步处理该Excel文件.当我读取包含日期值的列时，我得到了一个数字字符串，如05/02/2016(dd/MM/yyyy)变为42405.

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.

我尝试使用.Text，.Formula，.Value，.Value2-所有方法都返回数字值.

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

我了解FromOADateTime，但我的要求是将日期值作为字符串读取，确切地说就是在屏幕上显示的方式.

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.

谢谢.

当我在Excel中通过使用格式单元格"并选择文本"将列转换为文本"时，即使在Excel中，所有日期值都将转换为数字值！这有什么线索吗?可能第三方应用程序将数据存储为数值，但Excel在屏幕上将其显示为日期字符串.我想读取屏幕上显示的值.

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.

该问题并非与该列中的所有日期值一起出现.我的第一印象是第三方应用程序在将日期值写入Excel时不一致.但是我对此无能为力.

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.

这是下载文件的链接: http://wikisend.com/download/316956/Prob.xls .在此文件中，我需要将B列中的所有日期值读取为从第13行开始的文本

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

以下是其在我的计算机中的屏幕截图的链接: http://wikisend.com/download/443994/Screenshot1.jpg

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

在NineBerry的答案的帮助下，在我的代码中找到了罪魁祸首:在读取Text属性前几行，我分别调用了ws.Columns.ClearFormats()和ws.Rows.ClearFormats();

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();

我需要按照以下步骤进行操作:如何在excel工作表中获取占用单元格的范围…，以便正确获取工作表中的已用范围！

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!

原来的问题解决了.现在的问题是如何使用ws.UsedRange而不调用ClearFormats()来正确获取已用单元格的范围.

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.

我的具体要求是获取在任何存储单元中具有数据的二手ROWS范围，直到ColH.我不需要Excel中的整个UsedRange，在Col H中的UsedRange对我来说已经足够了.

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?

推荐答案

使用 Text 属性 做 给出要显示的字符串给用户.

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();
      }

如果您在此处看不到在Excel中看到的内容，则必须向我们提供更多详细信息.也许提供一个示例Excel文件，其中单元格的行为不符合预期.

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.

此代码确实输出文件中第二列的内容，该内容与Excel中显示的完全相同:

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.

这篇关于C#Excel日期值即将作为数字值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！