c# - 为 Epplus Excel 文件创建最小起订量

Question

我的第一个问题。我查看了我的查询，但找不到有用的答案。

我的任务是为我的 excel 文件编写单元测试用例。我面临的问题是我们将 Epplus 用于 excel 文件，我不确定我们如何为此编写单元测试用例。我查了一下，发现我们也可以用MOQ来mock up。但是我再次找不到任何有用的链接来模拟使用 Epplus 的 excel 文件。我找到了这个链接 Unit testing classes that use EPPlus但我不确定如何实现。

如果有人可以提供如何为 excel 文件编写简单单元测试的示例，我将不胜感激。测试可以是检查上传的文件是否为excel文件，检查excel是否为空等。

抱歉，目前我没有任何 sample 。我可以分享的是我正在读取 excel 文件的代码:

public class MyController : Controller
{
  [HttpPost("upload")]
  public async Task<IActionResult> UploadFile(IFormFile file)
  {
   JArray data = new JArray();
    using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
    {
      ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
      //Check if excel is empty.
      if (worksheet.Dimension == null)
      {
         return BadRequest("File is blank.");
      }
     data = Helper.CreateJson(worksheet);
                }
     return Ok(data);
  }
}

我创建了一个辅助类:

public static JArray CreateJson(ExcelWorksheet worksheet)
{
  JArray data = new JArray();
  JObject jobject = new JObject();

  int rowCount = worksheet.Dimension.End.Row;
  int colCount = worksheet.Dimension.End.Column;

    for (int row = 1; row <= rowCount; row++)
    {
       for (int col = 1; col <= colCount; col++)
        {
          var value = worksheet.Cells[row, col].Value;
          //Excel has 2 columns and I want to create a json from that.
          if (col == 1)              
          {
             jObject.Add("ID", rowValue.ToString());
          }
          else
          {
             jObject.Add("Name", rowValue.ToString());
          }
        }
         data.Add(jObject);
         jObject= new JObject();
     }
   return data;
}

这是我目前的测试类。

public class TestClass
{
    private MyController _controller;
    public TestClass()
    {
      _controller = new MyController (); 
    }

      [Fact]
    public void Upload_WhenCalled()
    {
        //var file = new FileInfo(@"C:\myfile.xlsx");
        //...what next?

        var file = new Mock<IFormFile>();
        var content = File.OpenRead(@"C:\myfile.xlsx");

        var result = _controller.UploadFile(file.Object);
        //When I debug it throws error "Object reference not set to an instance of an object."
    }
}

Answer 1

在这种情况下，模拟 IFormFile 以返回测试中的文件流并将其传递给被测操作。确保满足所有其他必要的依赖关系。

public class TestClass {
    private MyController _controller;
    public TestClass() {
      _controller = new MyController (); 
    }

    [Fact]
    public void Upload_WhenCalled() {
        //Arrange
        var content = File.OpenRead(@"C:\myfile.xlsx");
        var file = new Mock<IFormFile>();
        file.Setup(_ => _.OpenReadStream()).Returns(content);

        //Act
        var result = _controller.UploadFile(file.Object);

        //Assert
        //...
    }
}

虽然这应该可以帮助您解决当前的问题，但您确实应该采纳其他答案所建议的关于将 ExcelPackage 的紧密耦合从 Controller 中抽象出来的建议。将使 Controller 的单元测试更容易隔离。

您始终可以根据需要单独对包装器进行集成测试。

从当前 Controller 中抽象出的接口(interface)的简化示例

public interface IExcelService {
    Task<JArray> GetDataAsync(Stream stream);
}

这将有一个镜像 Controller 中代码的实现

public class ExcelService: IExcelService {
    public async Task<JArray> GetDataAsync(Stream stream) {
        JArray data = new JArray();
        using (ExcelPackage package = new ExcelPackage(stream)) {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
            if (worksheet.Dimension != null) {
                data = await Task.Run(() => createJson(worksheet));
            }
        }
        return data;
    }

    private JArray createJson(ExcelWorksheet worksheet) {
        JArray data = new JArray();
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++) {
            JObject jobject = new JObject();
            for (int col = 1; col <= colCount; col++) {
                var value = worksheet.Cells[row, col].Value;
                //Excel has 2 columns and I want to create a json from that.
                if (col == 1) {
                    jObject.Add("ID", rowValue.ToString());
                } else {
                    jObject.Add("Name", rowValue.ToString());
                }
                data.Add(jObject);
            }
        }
        return data;
    }
}

Controller 现在可以简化为遵循 Explicit Dependencies Principle

public class MyController : Controller {
    private readonly IExcelService excel;
    public MyController(IExcelService excel) {
        this.excel = excel;
    }

    [HttpPost("upload")]
    public async Task<IActionResult> UploadFile(IFormFile file) {
        JArray data = await excel.GetDataAsync(myFile.OpenReadStream());
        if(data.Count == 0)
            return BadRequest("File is blank.");
        return Ok(data);
    }
}

您将确保接口(interface)和实现已在 Startup 中的依赖倒置框架中注册

services.AddScoped<IExcelService, ExcelService>();

所以现在 Controller 只关心它在运行时被调用时应该做什么。我没有理由处理实现问题

public class MyControllerTests {
    [Fact]
    public async Task Upload_WhenCalled() {
        //Arrange
        var content = new MemoryStream();
        var file = new Mock<IFormFile>();
        file.Setup(_ => _.OpenReadStream()).Returns(content);
        var expected = new JArray();
        var service = new Mock<IExcelService>();
        service
            .Setup(_ => _.GetDataAsync(It.IsAny<Stream>()))
            .ReturnsAsync(expected);

        var controller = new MyController(service.Object);

        //Act
        var result = await controller.UploadFile(file.Object);

        //Assert
        service.Verify(_ => _.GetDataAsync(content));
        //...other assertions like if result is OkContentResult...etc
    }
}

要进行涉及实际文件的集成测试，您可以测试服务

public class ExcelServiceTests {
    [Fact]
    public async Task GetData_WhenCalled() {
        //Arrange
        var stream = File.OpenRead(@"C:\myfile.xlsx");
        var service = new ExcelService();

        //Act
        var actual = await service.GetDataAsync(stream);

        //Assert
        //...assert the contents of actual data.
    }
}

现在可以单独测试每个问题。