如何将值设置为二维Excel范围? [英] how to set values to a two-dimensional Excel range?

问题描述

我需要根据特定格式的测试用例列表构建一个excel表，以便将其上载到服务器. 我试图在文件中填充期望"和实际"的二维范围. 为了填充标头(一维数组)和步骤(二维)，我使用了相同的方法.

I need to build an excel sheet from a list of test-cases in a specific format in order to upload it it to the server. I've trubles to populate the two dimensional range of "expected" and "actual" in the file. I use the same methods in order to populate the headers, which is a one-dimensional array, and the steps (which is two-dims).

流为:

1. 为TestCase范围退款(某些标头+步骤).假设:第一次迭代从A1到E14.
2. 在标题的testCase范围内为子(本地)范围定界(例如:A1到C1).
3. 在头的testCase范围内删除另一个子(局部)范围(在我的情况下为D1至E14).
4. 用测试用例值(标头和步骤)填充两个子范围.
5. 通过使用相同的本地范围(步骤2-3)为下一个电子表格范围(在我的情况下为A14至E28)退款，然后填充它们，依此类推，以此类推...

源值是一个字典，代表测试用例的步骤(键=预期值，值=实际值).

The source value is a Dictionary which represents the test-case's steps (key = expected and value = actual).

这是我使用的代码:

public class TestCase
{
    public Dictionary<string, string> steps;
}

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbooks workBooks = excelApp.Workbooks;
Workbook workbook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
excelApp.Visible = true;

foreach (TestCase testCase in TestCaseList.Items.GetList())
{
    Range worksheetRange = GetRangeForTestCase(worksheet);
    //The step above is equivalent to:
    //    Range worksheetRange = worksheet.get_Range("A1", "E14");
    //for the first foreach iteration.

    Range stepsRange = worksheetRange.get_Range("D1", "E14");
    //This is a local range within the worksheetRange, not the worksheet,
    //so it is always have to be between D1 to E14 for a 14th steps test case.
    //Anyway, it should work at least for the 1st iteration.

    //for test evaluation only. All cells between D1 to E14 are filled with "ccc"
    test.Value = "ccc";

    //This list of pairs which are converted to Array[,] is about to be converted to a two dimensional array
    list = new List<object>();
    foreach (KeyValuePair<string, string> item in testCase.Steps)
    {
        //Here I build the inside Array[,]
        object[] tempArgs = {item.Key, item.Value};
        list.Add(tempArgs);
    }
    object[] args = { Type.Missing, list.ToArray() };

    test.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, test, args);

    //Now, all the "ccc" within the Excel worksheet are disapeared, so the Range is correct, but the value of args[] are not set!!
}

运行此代码的实际结果是定义了范围(可能正确)，但其值设置为null， 虽然-我可以在运行时在args数组中看到正确的值. 我还尝试设置一个更大的范围，并用range.Value ="Pake value"填充它，并看到在运行我的代码后，正确的步骤范围变为空白！ 因此，范围是正确的，数组填充了我的值，InvokeMember方法被正确调用了:) 但是，所有值都设置为null..

The actual results of running this code is that the range is defined (probably correctly) but its values are set to null, although - I can see the correct values in the args array in run time. I've also tried to set a wider range and populate it with range.Value = "Pake value" and saw that, after running my peace of code, the correct range of steps become blank! So, the range is correct, the array is filled with my values, the InvokeMember method is correctly invoked :) But, all values are set to null..

帮助...

推荐答案

可以通过以下方式之一设置一个单元格或1dim数组:

One cell or 1dim array can be set via one of the following:

Range range = SetRange();//Let's say range is set between A1 to D1
object[] args = {1, 2, 3, 4 }; 

//Directly
range.Value = args;

//By Reflection
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, args);

不能直接设置2dim数组，因此必须使用反射流来设置值矩阵.必须在集合之前构建此矩阵，如下所示:

A 2dim array cannot be directly set, so one has to use the reflection flow to set a matrix of values. This matrix has to be built before the set, like this:

Range range = SetRange();//Let's say range is set between A1 to C5
int rows = 5;
int columns = 3;
object[,] data = new object[rows, columns];
for (int i = 0; i < rows; i++)
{
    for (int j = 0; j < columns; j++)
    {
        //Here I build the inside Array[,]
        string uniqueValue = (i + j).ToString();
        data[i, j] = "Insert your string value here, e.g: " + uniqueValue;
    }
}
object[] args = { data };
range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, range, args);

这篇关于如何将值设置为二维Excel范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！