C＃Transpose（）方法转换excel表中的行和列

I want to transpose rows and columns of an excel sheet and save it. I want to use C# transpose() method for this purpose. Can anybody tell how it can be used in C# with examples, what is the syntax, what object shoud be created to call transpose() method?

我想转置Excel工作表的行和列并保存它。我想为此目的使用C#transpose()方法。任何人都可以告诉它如何在C#中使用示例,语法是什么,创建什么对象来调用transpose()方法?

Thank you.

2 个解决方案

#1

your method is part of the Excel object model but included in C# through Visual Studio Tools for Office / Excel DNA / Managed XLL etc

您的方法是Excel对象模型的一部分,但通过Visual Studio Tools for Office / Excel DNA / Managed XLL等包含在C#中

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

Then paste the transposedRange back into Excel:

然后将transposedRange粘贴回Excel:

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

#2

First you have to convert excel sheet to datatable and then programmatically transpose rows and columns of datatable.For converting excel sheet to datatable,you can search question on * as it has lot of similar questions.For trasposing datatable below code works;

首先,你必须将excel表转换为datatable,然后以编程方式转换datatable的行和列。为了将excel表转换为datatable,你可以在*上搜索问题,因为它有很多类似的问题。对于trasposing数据表下面的代码工作;

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();

     // Add columns by looping rows

     // Header row's first column is same as in inputTable
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns        
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}

#1