0

Data displayed in EXCEL

1

Data read by the program

2

I found that the actual data obtained by decompressing the XLSX file is also different

O7 cell data

3

O8 cell data

4

I also tried to use NPOI to read, the result is the same, but not all date values have this problem.

Magnetron
  • 7,495
  • 1
  • 25
  • 41
user13439622
  • 11
  • 3
  • A DateTime is a number and you need to format the number to a string if you want a different format from the default format. When reading an Excel Cell data is often display incorrectly when the cell is set to General Format which provides not information about the type of data is in the cell. The NPOI driver has to guess at the type of data is in the cell so often a number is converted to a date and a date is converted to a number. – jdweng Dec 30 '20 at 10:47

2 Answers2

0

This is probably a rounding/truncating issue. Excel is rounding the DateTime while Console.WriteLine is truncating. Check the values of the O7 and O8 cells:

double o7=44203.4166666667;
double o8=44203.4166666088;
Console.WriteLine(DateTime.FromOADate(o7).ToString("yyyy-MM-dd HH:mm:ss.ffffff"));
Console.WriteLine(DateTime.FromOADate(o8).ToString("yyyy-MM-dd HH:mm:ss.ffffff"));

Results:

2021-01-07 10:00:00.000000
2021-01-07 09:59:59.995000

For more information on how to round DateTime objects, check this SO question.

Magnetron
  • 7,495
  • 1
  • 25
  • 41
0

Thank you for everyone’s reply. The cause of this problem was not found in the end, but since our actual data will not have decimals, I bypassed this problem by rounding. Thank you everyone

user13439622
  • 11
  • 3