将 C# 列表列表导出到 Excel

Export a C# List of Lists to Excel

提问by Matt

Using C#, is there a direct way to export a List of Lists (i.e., List<List<T>>) to Excel 2003?

使用 C#，是否有将列表列表（即List<List<T>>）导出到 Excel 2003的直接方法？

I am parsing out large text files and exporting to Excel. Writing one cell at a time creates way too much overhead. I chose to use List<T>so that I would not have to worry about specifying the number of rows or columns.

我正在解析大文本文件并导出到 Excel。一次写入一个单元格会产生过多的开销。我选择使用List<T>这样我就不必担心指定行数或列数。

Currently, I wait until end of file, then put the contents of my List<List<object>>into a 2-dimensional array. Then the array can be set as the value of an Excel.Range object. It works, but it seems like I should be able to take my List of Lists, without worrying about the number of rows or columns, and just dump it into a worksheet from A1 to wherever.

目前，我等到文件结束，然后将 my 的内容List<List<object>>放入一个二维数组中。然后可以将该数组设置为 Excel.Range 对象的值。它有效，但似乎我应该能够获取我的列表列表，而不必担心行数或列数，只需将其从 A1 转储到工作表中的任何位置即可。

Here's a snippet of the code I'd like to replace or improve on:

这是我想替换或改进的代码片段：

object oOpt = System.Reflection.Missing.Value; //for optional arguments
Excel.Application oXL = new Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel._Workbook oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;

int numberOfRows = outputRows.Count;
int numberOfColumns = int.MinValue;

//outputRows is a List<List<object>>
foreach (List<object> outputColumns in outputRows)
{
        if (numberOfColumns < outputColumns.Count)
        { numberOfColumns = outputColumns.Count; }
}

Excel.Range oRng = oSheet.get_Range("A1", oSheet.Cells[numberOfRows,numberOfColumns]);

object[,] outputArray = new object[numberOfRows,numberOfColumns];

for (int row = 0; row < numberOfRows; row++)
{
        for (int col = 0; col < outputRows[row].Count; col++)
        {
                outputArray[row, col] = outputRows[row][col];
        }
}

oRng.set_Value(oOpt, outputArray);

oXL.Visible = true;
oXL.UserControl = true;

This works, but I'd rather use the List directly to Excel than having the intermediary step of creating an array just for the sake of Excel. Any ideas?

这有效，但我宁愿将 List 直接用于 Excel，也不愿为了 Excel 而创建数组的中间步骤。有任何想法吗？

采纳答案by Mike Rosenblum

Strategically, you are doing it correctly. As Joe says, it is massively faster to execute cell value assignments by passing an entire array of values in one shot rather than by looping through the cells one by one.

从战略上讲，你做对了。正如 Joe 所说，通过在一次拍摄中传递整个值数组而不是通过一个一个循环遍历单元格来执行单元格值分配要快得多。

Excel is COM based and so operates with Excel via the .NET interop. The interop is ignorant of generics, unfortunately, so you cannot pass it a List<T> or the like. A two dimensional array really is the only way to go.

Excel 是基于 COM 的，因此可以通过 .NET 互操作与 Excel 一起运行。不幸的是，互操作对泛型一无所知，因此您不能将它传递给 List<T> 之类的。二维数组确实是唯一的出路。

That said, there are a few ways to clean up your code to make it a bit more manageable. Here are some thoughts:

也就是说，有几种方法可以清理您的代码，使其更易于管理。以下是一些想法：

(1) If you are using .NET 3.0, you can use LINQ to shorten your code from:

(1) 如果您使用的是 .NET 3.0，您可以使用 LINQ 来缩短您的代码：

int numberOfColumns = int.MinValue;

foreach (List<object> outputColumns in outputRows)
{
        if (numberOfColumns < outputColumns.Count)
        { numberOfColumns = outputColumns.Count; }
}

to a single line:

到一行：

int numberOfColumns = outputRows.Max(list => list.Count);

(2) Don't use the _Worksheetor _Workbookinterfaces. Make use of Worksheetor Workbookinstead. See here for a discussion: Excel interop: _Worksheet or Worksheet?.

(2) 不要使用_Worksheet或_Workbook接口。使用Worksheet或Workbook代替。请参阅此处进行讨论：Excel 互操作：_Worksheet 还是 Worksheet？.

(3) Consider making use of the Range.Resizemethod, which comes through as Range.get_Resizein C#. This is a toss-up though -- I actually like the way you are setting your range size. But it's something that I thought that you might want to know about. For example, your line here:

（3）考虑利用所述的Range.Resize方法，该方法通过附带如Range.get_Resize在C＃。虽然这是一个折腾 - 我实际上喜欢你设置范围大小的方式。但这是我认为你可能想知道的事情。例如，您在这里的线路：

Excel.Range oRng = oSheet.get_Range("A1", oSheet.Cells[numberOfRows,numberOfColumns]);

Could be changed to:

可以改为：

Excel.Range oRng = 
    oSheet.get_Range("A1", Type.Missing)
        .get_Resize(numberOfRows, numberOfColumns);

(4) You do not have to set the Application.UserControlto true. Making Excel visible to the user is enough. The UserControlproperty is not doing what you think it does. (See the help files here) If you want to control whether the user can control Excel or not, you should utilze Worksheet protection, or you couldset Application.Interactive = falseif you want to lock out your users. (Rarely a good idea.) But if you want to allow the user to use Excel, then simply making it visible is enough.

(4) 您不必将 设置Application.UserControl为true。让 Excel 对用户可见就足够了。该UserControl物业没有按照您的想法行事。（请参阅此处的帮助文件）如果您想控制用户是否可以控制 Excel，您应该使用工作表保护，或者您可以设置Application.Interactive = false是否要锁定您的用户。（很少是一个好主意。）但是如果你想让用户使用 Excel，那么简单地让它可见就足够了。

Overall, with these in mind, I think that your code could look something like this:

总的来说，考虑到这些，我认为您的代码可能如下所示：

object oOpt = System.Reflection.Missing.Value; //for optional arguments
Excel.Application oXL = new Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel.Workbook oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet oSheet = (Excel.Worksheet)oWB.ActiveSheet;

//outputRows is a List<List<object>>
int numberOfRows = outputRows.Count;
int numberOfColumns = outputRows.Max(list => list.Count);

Excel.Range oRng = 
    oSheet.get_Range("A1", oOpt)
        .get_Resize(numberOfRows, numberOfColumns);

object[,] outputArray = new object[numberOfRows, numberOfColumns];

for (int row = 0; row < numberOfRows; row++)
{
    for (int col = 0; col < outputRows[row].Count; col++)
    {
        outputArray[row, col] = outputRows[row][col];
    }
}

oRng.set_Value(oOpt, outputArray);

oXL.Visible = true;

Hope this helps...

希望这可以帮助...

Mike

麦克风

回答by Joe

It's muchfaster to pass a two-dimensional array to Excel than to update cells one at a time.

这是很多更快地传递一个二维数组到Excel中，而不是在一个时间更新单元之一。

Create a 2-dimensional array of objects with values from your list of lists, redimension the Excel range to the dimensions of your array, and then call range.set_Value, passing your two-dimensional array.

使用列表列表中的值创建对象的二维数组，将 Excel 范围重新调整为数组的维度，然后调用 range.set_Value，传递二维数组。

回答by Rizwan Patel

List<"classname"> getreport = cs.getcompletionreport();

var getreported = getreport .Select(c => new { demographic = c.rName);

hopes this helps

希望这会有所帮助

where cs.getcompletionreport()is reference class file is Business Layer for App

cs.getcompletionreport()引用类文件在哪里是应用程序的业务层

回答by Stana Macala

For future searchers: It is true that if you want to copy list to excel you have to paste object[,](2 dimensional). Just in case someone has one dimensional list you can run just one for loopwith second dimesion 1:

对于未来的搜索者：确实，如果您想将列表复制到 excel，您必须粘贴object[,]（二维）。以防万一有人有一个一维列表，你可以只运行一个for loop第二维度1：

List<DateTime> listDate; //I'm filling it with data from datagridview
...
object[,] outDate = new object[listDate.Count, 1];
for(int row = 0; row < listDate.Count; row++)
{
    outDate[row, 0] = listDate[row];
}