NetCoreTests/ExcelPackageTest.cs

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

namespace NetCoreTests;

[TestClass]
public class ExcelPackageTest {
  [TestMethod]
  public void TestGetAsByteArray() {
    var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("Times.xlsx")));
    var data = package.GetAsByteArray();

    // Verify that we can reload
    var newPackage = new ExcelPackage(new MemoryStream(data, false));
    newPackage.Workbook.Worksheets.Count.Should().Be(1);
    var worksheet = newPackage.Workbook.Worksheets.First();
    worksheet.Name.Should().Be("Times_csv");
  }

  [TestMethod]
  public void TestGetAsByteArray_PublicLibrary() {
    var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("PublicLibrary.xlsx")));
    var data = package.GetAsByteArray();

    // Verify that we can reload
    var newPackage = new ExcelPackage(new MemoryStream(data, false));
    newPackage.Workbook.Worksheets.Count.Should().Be(1);
    var worksheet = newPackage.Workbook.Worksheets.First();
    worksheet.Name.Should().Be("pusum11a");
  }

  [TestMethod]
  public void TestGetAsByteArray_DataValidation() {
    var package = new ExcelPackage(new FileInfo(GetTestWorkbookPath("DataValidation.xlsx")));
    var data = package.GetAsByteArray();

    // Verify that we can reload
    var newPackage = new ExcelPackage(new MemoryStream(data, false));
    newPackage.Workbook.Worksheets.Count.Should().Be(1);
    var worksheet = newPackage.Workbook.Worksheets.First();
    worksheet.Name.Should().Be("Sheet1");
    worksheet.DataValidations.Should().HaveCount(31);
    worksheet.DataValidations[1].ValidationType.Should().Be(ExcelDataValidationType.Whole);
  }

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