使用C#在Excel中转​​置值 [英] Transpose values in excel using C#

问题描述

我看到了此链接-用C#Transpose()方法在Excel表格中的行和列中进行转置，这是我正在尝试做的事情.但是，由于他没有提供所需的全部信息，因此他对答案毫无帮助.我只是想转置我的Excel工作表中的单元格A9:B15，然后将它们复制到新的xls文件，新的工作表中，或者更好地删除当前工作表的内容并将其替换为新转置的粘贴内容.显然，可以通过WorksheetFunction.Transpose方法完成此操作，但是由于我不知道rng或value2是什么，所以我似乎无法使其正常工作.我可以创建一个数据表，但使用此方法似乎更合适.这是来自stackoverflow问题的代码. .

I saw this link - C# Transpose() method to transpose rows and columns in excel sheet in stackoverflow and this is what I am trying to do. But the guy is pretty unhelpful in the answers as he does not provide the full information needed. I am simply wanting to transpose cells A9:B15 in my excel sheet and then copy them either into a new xls file, a new worksheet, or better yet delete the current worksheet contents and replace it with the newly transposed paste contents. Clearly it can be done through the WorksheetFunction.Transpose method but I can't seem to get it to work as I don't know what rng or value2 are? I could create a datatable but surly using this method seems a more appropriate way of doing it. Here is the code from the stackoverflow question. .

Object[,] transposedRange = (Object[,])xlApp.WorksheetFunction.Transpose(rng.Value2);

xlApp.ActiveSheet.Range("A1").Resize(transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1)) = transposedRange;

到目前为止，这是我的代码:

Here is my code so far:

        Application excel = new Application();
        Workbook wb = excel.Workbooks.Open(@"P:\Visual Studio 2013\Projects\Debugging\Debugging\test.htm");
        Microsoft.Office.Interop.Excel.Range rng = excel.get_Range("A9:B15");
        Object[,] transposeRange = (Object[,])excel.WorksheetFunction.Transpose(rng);
        transposeRange = excel.ActiveSheet.Range("A1").Resize(transposeRange.GetUpperBound(0), transposeRange.GetUpperBound(1));
        wb.SaveAs(@"P:\Visual Studio 2013\Projects\Debugging\Debugging\testing.xls");

不确定我是否正确执行了rng.我对此感到困惑.

Not sure if I have done the rng right. I am so confused by this.

推荐答案

这是很久以前问过的，但是我还是让我自己解决.

This was asked a long time ago but I will let my sollution anyway.

参考:

using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

这里的技巧是获取_Application变量

The trick here is to get the _Application Variable

如果您将VSTO ADDIN与Workbook一起使用，则可以这样做:

in case you are using VSTO ADDIN with Workbook you can do like this:

var app = Globals.ThisWorkbook.Parent as _Application;

对于其他类型的项目，请执行以下操作:

For other kind of project do like this:

_Application app2 = new Excel.Application();

我的样本(sheet1是我的工作表):

My sample (sheet1 is my worksheet):

        var sheet1 = Globals.Planilha1;
        var arr = new string[] 
        {
            "test1",
            "test2",
            "test3",
            "test4",
            "test5",
            "test6",
        };
        
        // For VSTO ADDINS with Workbook
        //var app = Globals.ThisWorkbook.Parent as _Application;

        // For any kind of Excel Interop project
        _Application app = new Excel.Application();

        sheet1.Range["A1:A" + arr.Length].Value = app.WorksheetFunction.Transpose(arr);

Transpose函数只能处理数组，列表将不起作用.

The Transpose function can only deal with arrays, lists won't work.

只需将数组放在app.WorksheetFunction.Transpose函数中，它将很好地工作.

Just place the array inside the app.WorksheetFunction.Transpose function and it will work pretty well.

输出:

这篇关于使用C#在Excel中转​​置值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！