C# 将 List 中的值导出到 Excel

Exporting the values in List to excel

提问by susanthosh

Hi I am having a list container which contains the list of values. I wish to export the list values directly to Excel. Is there any way to do it directly?

嗨，我有一个包含值列表的列表容器。我希望将列表值直接导出到 Excel。有什么办法可以直接做到吗？

采纳答案by naeron84

OK, here is a step-by-step guide if you want to use COM.

好的，如果您想使用 COM，这里有一个分步指南。

1. You have to have Excel installed.
2. Add a reference to your project to the excel interop dll. To do this on the .NET tab select Microsoft.Office.Interop.Excel. There could be multiple assemblies with this name. Select the appropriate for your Visual Studio AND Excel version.
3. Here is a code sample to create a new Workbook and fill a column with the items from your list.

1. 您必须安装 Excel。
2. 将您的项目引用添加到 excel 互操作 dll。为此，请在 .NET 选项卡上选择 Microsoft.Office.Interop.Excel。可能有多个具有此名称的程序集。选择适合您的 Visual Studio 和 Excel 版本。
3. 这是一个代码示例，用于创建新工作簿并使用列表中的项目填充一列。

---

---

using NsExcel = Microsoft.Office.Interop.Excel;

public void ListToExcel(List<string> list)
{
    //start excel
    NsExcel.ApplicationClass excapp = new Microsoft.Office.Interop.Excel.ApplicationClass();

    //if you want to make excel visible           
    excapp.Visible = true;

    //create a blank workbook
    var workbook = excapp.Workbooks.Add(NsExcel.XlWBATemplate.xlWBATWorksheet);

    //or open one - this is no pleasant, but yue're probably interested in the first parameter
    string workbookPath = "C:\test.xls";
    var workbook = excapp.Workbooks.Open(workbookPath,
        0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
        true, false, 0, true, false, false);

    //Not done yet. You have to work on a specific sheet - note the cast
    //You may not have any sheets at all. Then you have to add one with NsExcel.Worksheet.Add()
    var sheet = (NsExcel.Worksheet)workbook.Sheets[1]; //indexing starts from 1

    //do something usefull: you select now an individual cell
    var range = sheet.get_Range("A1", "A1");
    range.Value2 = "test"; //Value2 is not a typo

    //now the list
    string cellName;
    int counter = 1;
    foreach (var item in list)
    {
        cellName = "A" + counter.ToString();
        var range = sheet.get_Range(cellName, cellName);
        range.Value2 = item.ToString();
        ++counter;
    }

    //you've probably got the point by now, so a detailed explanation about workbook.SaveAs and workbook.Close is not necessary
    //important: if you did not make excel visible terminating your application will terminate excel as well - I tested it
    //but if you did it - to be honest - I don't know how to close the main excel window - maybee somewhere around excapp.Windows or excapp.ActiveWindow
}

回答by Ian

Depending on the environment you're wanting to do this in, it is possible by using the Excel Interop. It's quite a mess dealing with COM however and ensuring you clear up resources else Excel instances stay hanging around on your machine.

根据您要在其中执行此操作的环境，可以使用 Excel Interop。然而，处理 COM 并确保您清理资源是相当混乱的，否则 Excel 实例会留在您的机器上。

Checkout this MSDN Exampleif you want to learn more.

如果您想了解更多信息，请查看此MSDN 示例。

Depending on your format you could produce CSV or SpreadsheetML yourself, thats not too hard. Other alternatives are to use 3rd party libraries to do it. Obviously they cost money though.

根据您的格式，您可以自己生成 CSV 或 SpreadsheetML，这并不难。其他替代方法是使用 3rd 方库来做到这一点。显然，他们虽然花钱。

回答by Jon Cage

You could output them to a .csv fileand open the file in excel. Is that direct enough?

您可以将它们输出到.csv 文件并在 excel 中打开该文件。够直接吗？

回答by Sapph

The most straightforward way (in my opinion) would be to simply put together a CSV file. If you want to get into formatting and actually writing to a *.xlsx file, there are more complicated solutions (and APIs) to do that for you.

最直接的方法（在我看来）是简单地将 CSV 文件放在一起。如果您想进行格式化并实际写入 *.xlsx 文件，有更复杂的解决方案（和API）可以为您做到这一点。

回答by IordanTanev

the one easy way to do it is to open Excel create sheet containing test data you want to export then say to excel save as xml open the xml see the xml format excel is expecting and generate it by head replacing the test data with export data

一种简单的方法是打开 Excel 创建包含要导出的测试数据的工作表，然后说 excel 另存为 xml 打开 xml 查看 excel 期望的 xml 格式并通过将测试数据替换为导出数据来生成它

SpreadsheetML Markup Spec

SpreadsheetML 标记规范

@lan this is xml fo a simle execel file with one column value i genereted with office 2003 this format is for office 2003 and above

@lan 这是一个带有一列值的simle execel 文件的xml，我用office 2003 生成此格式适用于office 2003 及更高版本

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Dancho</Author>
  <LastAuthor>Dancho</LastAuthor>
  <Created>2010-02-05T10:15:54Z</Created>
  <Company>cc</Company>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>13800</WindowHeight>
  <WindowWidth>24795</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>105</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">Value1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value2</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value3</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value4</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value5</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value6</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>5</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

回答by Matthew Flaschen

Using the CSV idea, if it's just a list of Strings. Assuming lis your list:

使用 CSV 的想法，如果它只是一个字符串列表。假设l是你的清单：

using System.IO;

using(StreamWriter sw = File.CreateText("list.csv"))
{
  for(int i = 0; i < l.Count; i++)
  {
    sw.WriteLine(l[i]);
  }
}

回答by Rizwan Patel

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

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

where cs.getcompletionreport()reference class file is Business Layer for App
I hope this helps.

其中cs.getcompletionreport()参考类文件是应用程序的业务层
我希望这会有所帮助。

回答by Tommaso Cerutti

Using ClosedXMLlibrary( there is no need to install MS Excel

使用ClosedXML库（无需安装 MS Excel

I just write a simple example to show you how you can name the file, the worksheet and select cells:

我只是写了一个简单的例子来向你展示如何命名文件、工作表和选择单元格：

    var workbook = new XLWorkbook();
    workbook.AddWorksheet("sheetName");
    var ws = workbook.Worksheet("sheetName");

    int row = 1;
    foreach (object item in itemList)
    {
        ws.Cell("A" + row.ToString()).Value = item.ToString();
        row++;
    }

    workbook.SaveAs("yourExcel.xlsx");

If you prefer you can create a System.Data.DataSet or a System.Data.DataTable with all data and then just add it as a workseet with workbook.AddWorksheet(yourDataset)or workbook.AddWorksheet(yourDataTable);

如果您愿意，可以使用所有数据创建 System.Data.DataSet 或 System.Data.DataTable，然后将其添加为工作表workbook.AddWorksheet(yourDataset)或使用workbook.AddWorksheet(yourDataTable)；

回答by leahsaif

The simplest way using ClosedXml.

使用 ClosedXml 的最简单方法。

Imports ClosedXML.Excel

var dataList = new List<string>() { "a", "b", "c" };
var workbook = new XLWorkbook();     //creates the workbook
var wsDetailedData = workbook.AddWorksheet("data"); //creates the worksheet with sheetname 'data'
wsDetailedData.Cell(1, 1).InsertTable(dataList); //inserts the data to cell A1 including default column name
workbook.SaveAs(@"C:\data.xlsx"); //saves the workbook

For more info, you can also check wiki of ClosedXml. https://github.com/closedxml/closedxml/wiki

有关更多信息，您还可以查看 ClosedXml 的 wiki。 https://github.com/closedxml/closedxml/wiki

回答by Mohammed Dawood Ansari

I know, I am late to this party, however I think it could be helpful for others.

我知道，我参加这个聚会迟到了，但我认为这可能对其他人有所帮助。

Already posted answers are for csv and other one is by Interop dll where you need to install excel over the server, every approach has its own pros and cons. Here is an option which will give you

已经发布的答案是针对 csv 的，另一个是针对 Interop dll 的，您需要在服务器上安装 excel，每种方法都有自己的优缺点。这是一个选项，它会给你

1. Perfect excel output [not csv]
2. With perfect excel and your data type match
3. Without excel installation
4. Pass list and get Excel output :)

1. 完美的 excel 输出 [不是 csv]
2. 用完美的excel和你的数据类型匹配
3. 不用excel安装
4. 通过列表并获得 Excel 输出 :)

you can achieve this by using NPOI DLL, available for both .net as well as for .net core

您可以通过使用NPOI DLL来实现这一点，可用于 .net 和 .net core

Steps :

脚步 ：

1. Import NPOI DLL
2. Add Section 1 and 2 code provided below
3. Good to go

1. 导入 NPOI DLL
2. 添加下面提供的第 1 节和第 2 节代码
3. 很高兴去

Section 1

第 1 节

This code performs below task :

此代码执行以下任务：

1. Creating New Excel object - _workbook = new XSSFWorkbook();
2. Creating New Excel Sheet object - _sheet =_workbook.CreateSheet(_sheetName);
3. Invokes WriteData()- explained later Finally, creating and
4. returning MemoryStreamobject

1. 创建新的 Excel 对象 - _workbook = new XSSFWorkbook();
2. 创建新的 Excel 工作表对象 - _sheet =_workbook.CreateSheet(_sheetName);
3. 调用WriteData()- 稍后解释 最后，创建和
4. 返回MemoryStream对象

=============================================================================

================================================== ============================

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
{
    public interface IAbstractDataExport
    {
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);
    }

    public abstract class AbstractDataExport : IAbstractDataExport
    {
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
        {
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                {
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
                };

                return response;
            }
        }

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);
    }
}

=============================================================================

================================================== ============================

Section 2

第 2 节

In section 2, we will be performing below steps :

在第 2 节中，我们将执行以下步骤：

1. Converts List to DataTable Reflection to read property name, your
2. Column header will be coming from here
3. Loop through DataTable to Create excel Rows

1. 将 List 转换为 DataTable 反射以读取属性名称，您的
2. 列标题将来自此处
3. 循环遍历数据表以创建 excel 行

=============================================================================

================================================== ============================

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
{
    public class AbstractDataExportBridge : AbstractDataExport
    {
        public AbstractDataExportBridge()
        {
            _headers = new List<string>();
            _type = new List<string>();
        }

        public override void WriteData<T>(List<T> exportData)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " ").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

            foreach (T item in exportData)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sheetRow = _sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell Row1 = sheetRow.CreateCell(j);

                    string type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

=============================================================================

================================================== ============================

Now you just need to call WriteData() function by passing your list, and it will provide you your excel.

现在您只需要通过传递您的列表来调用 WriteData() 函数，它就会为您提供您的 excel。

I have tested it in WEB API and WEB API Core, works like a charm.

我已经在 WEB API 和 WEB API Core 中对其进行了测试，效果非常好。