需要在很大程度上依赖于Office.Interop(COM)类的自定义类上运行单元测试 [英] Need to run unit tests on a custom class that is heavely dependant on Office.Interop (COM) classes

问题描述

我有一个名为ExcelManager的类，该类负责在其构造函数中采用指向excel文件的路径，并具有一种将工作表名称作为字符串或工作表索引作为int的方法，然后解析该工作表上的数据减去标题，然后构建并返回一个DataTable.

I have a class called ExcelManager that is responsible for taking a path to an excel file in its constructor and has a method that takes a Sheet Name as a string or Sheet Index as an int, it then parses the data on that sheet minus headers and builds and returns a DataTable.

我编写了集成测试，可以成功测试大多数ExcelManager类，但是它们只能在安装了Excel的系统上运行.

I have written integration tests that successfully test most of the ExcelManager class however they can only run on a system that has Excel installed.

我想要做的是分解ExcelManager类，以便无需Excel即可模拟电子表格并进行测试，或者找到一种方法进行单元测试并可能出于测试目的注入模拟对象(我不是确定哪种方法更好)，但我真的看不到这样做的方法，下面是一些Excel Manager代码:

What I am looking to do is either break down the ExcelManager class so that I can mock a spreadsheet and test without the need for Excel or find a way to unit test and possible inject mocked object for testing purposes (i'm not sure which is better practice) but I can't really see a way to do this, here is some of the Excel Manager code:

public ExcelManager(string FilePath)
    {
        try
        {
            application = new ApplicationClass();
        }
        catch (Exception)
        {
            throw new Exception("This feature requires a version of Microsoft Excel developed in 2002 or later, which is not detected on your computer.");
        }

        application.Visible = false;
        application.ScreenUpdating = false;
        application.DisplayAlerts = false;
        this.FilePath = FilePath;

        workbook = application.Workbooks.Open(FilePath, false, false, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);

    }

这是打开指定工作表并构建DataTable的方法 headerRng行用于将选择范围限制为用户输入的数据，电子表格中有一些常量字段不需要读取.

This is the method that opens the specified sheet and builds a DataTable The headerRng lines are used to limit the selection to user entered data, there are some constant fields in the spreadsheet that need not be read in.

public DataTable GetRangeValue(int SheetIndex)
{
    worksheet = (Worksheet)workbook.Sheets[SheetIndex];

    var headerRng = worksheet.get_Range("A1", miss);
    headerRng = headerRng.get_End(XlDirection.xlToRight);
    headerRng = worksheet.get_Range("A1", headerRng);
    var headerData = (object[,])headerRng.Value2;
    var columnCount = headerData.GetLength(1);

    var last = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);

    var dataRng = worksheet.get_Range("A2", last);

    var sheetData = (object[,])dataRng.Value2; // the rest of the code is the DataTable processing

还有另一种方法来获取工作表的字符串名称，并使用工作表索引调用该名称.

There is another method that takes the string name for the sheet and calls this one with the sheet index.

推荐答案

最好的选择是将应用程序对象传递到ExcelManager类的构造函数中.

Your best bet would be pass the application object into the constructor of your ExcelManager class.

这将允许您使用模拟框架创建伪造的excel版本，并将其传递到单元测试中的类中.

This would allow you to use a mocking framework to create a fake version of excel passing it into your class in your unit tests.

不利的一面是，模拟对象的设置最终大量了解了Manager类的内部行为，这实际上是行为测试，而不是黑盒测试，并且在重构时可能会很痛苦(一种脆弱的测试代码气味)

The downside is that the mock object's setup ends up knowing alot about the internal behaviour of your Manager class which is really behavioural testing rather than black box testing and can be a pain when refactoring (a kind of fragile tests code smell)

不太理想，但是在我发现的单元测试中，com-interop的东西有些薄弱.

Not ideal but com-interop stuff a bit of a weak area when it comes to unit testing I've found.

此处上有一篇有趣的文章演示了这种方法.

There is an interesting article demonstrating such an approach here

这篇关于需要在很大程度上依赖于Office.Interop(COM)类的自定义类上运行单元测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！