NetCoreTests/TimeTest.cs

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

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