NetCoreTests/TimeTest.cs

using System;
using System.IO;
using System.Linq;
using System.Text;
using FluentAssertions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;

namespace NetCoreTests;

[TestClass]
public class TimeTest
{
    static TimeTest()
    {
        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
    }
    
    [TestMethod]
    public void TestIncorrectDurationFromOADate()
    {
        ExcelWorksheet.IncorrectDurationFromOADate(2.75).Should().Be(new(1900, 1, 1, 18, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(1.75).Should().Be(new(1899, 12, 31, 18, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(0.75).Should().Be(new(1899, 12, 30, 18, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(0.5).Should().Be(new(1899, 12, 30, 12, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(0.25).Should().Be(new(1899, 12, 30, 6, 0, 0));

        ExcelWorksheet.IncorrectDurationFromOADate(0).Should().Be(new(1899, 12, 30, 0, 0, 0));
        
        ExcelWorksheet.IncorrectDurationFromOADate(-0.25).Should().Be(new(1899, 12, 29, 18, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(-0.5).Should().Be(new(1899, 12, 29, 12, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(-0.75).Should().Be(new(1899, 12, 29, 6, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(-1.75).Should().Be(new(1899, 12, 28, 6, 0, 0));
        ExcelWorksheet.IncorrectDurationFromOADate(-2.75).Should().Be(new(1899, 12, 27, 6, 0, 0));
    }

    [TestMethod]
    public void TestDateParse()
    {
        using var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("Times.xlsx")));
        var sheet = package.Workbook.Worksheets.First();

        sheet.Cells["B3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
        sheet.Cells["C3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 0));
        sheet.Cells["D3"].Value.Should().Be(0.13541666666666666);
        sheet.Cells["E3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
        sheet.Cells["F3"].Value.Should().Be(0.13576388888888888);
        sheet.Cells["G3"].Value.Should().Be(0.13541666666666666);
        sheet.Cells["H3"].Value.Should().Be(new DateTime(1899, 12, 30, 3, 15, 30));
        
        sheet.Cells["B7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
        sheet.Cells["C7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 0));
        sheet.Cells["D7"].Value.Should().Be(0.9993055555555556);
        sheet.Cells["E7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
        sheet.Cells["F7"].Value.Should().Be(0.999988425925926);
        sheet.Cells["G7"].Value.Should().Be(0.9993055555555556);
        sheet.Cells["H7"].Value.Should().Be(new DateTime(1899, 12, 30, 23, 59, 59));
    }

    private static string GetTestWorkbookPath(string filename)
    {
        var assemblyPath = Path.GetDirectoryName(typeof(TimeTest).Assembly.Location)!;
        return Path.Combine(assemblyPath, "TestWorkbooks", filename);
    }
}