C# 如何使用 LINQ to Entity 将数据导出到 Excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2202606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Export data to Excel using LINQ to Entity?
提问by Rita
I have the data coming from Entity Data model table on my ASP.NET page. Now I have to export this data into Excel on button click.
我的 ASP.NET 页面上的数据来自实体数据模型表。现在我必须在单击按钮时将此数据导出到 Excel 中。
If it is using OLEDB, it is straight forward as it is here: http://csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm
如果它使用 OLEDB，它是直接的，因为它在这里：http: //csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm
Here is my function to read data from inquiries table:
这是我从查询表中读取数据的函数：
var model = from i in myEntity.Inquiries
where i.User_Id == 5
orderby i.TX_Id descending
select new {
RequestID = i.TX_Id,
CustomerName = i.CustomerMaster.FirstName,
RequestDate = i.RequestDate,
Email = i.CustomerMaster.MS_Id,
DocDescription = i.Document.Description,
ProductName = i.Product.Name
回答by Corey Coogan
You can just write a string representation of your data - tab delimited for each field and \r\n delimited for each row. Then stream that out the browser as a .csv file, which will open automatically in Excel.
您可以只编写数据的字符串表示形式 - 为每个字段分隔的制表符和为每一行分隔的 \r\n 。然后将其作为 .csv 文件从浏览器中流出，该文件将在 Excel 中自动打开。
回答by StevenzNPaul
You can consider using the SpreadSheetML, basically which is a XML file with a Cocoon process mentioned on top of the xml file to launch the excel application on double clicking the xml file. Sample for SpreadSheetML provided as below.
您可以考虑使用 SpreadSheetML，它基本上是一个 XML 文件，在 xml 文件顶部提到了 Cocoon 进程，以在双击 xml 文件时启动 excel 应用程序。如下提供的 SpreadSheetML 示例。
<?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" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:ms="urn:schemas-microsoft-com:xslt">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author xmlns="urn:schemas-microsoft-com:office:spreadsheet">Author<"/Author>
<LastAuthor xmlns="urn:schemas-microsoft-com:office:spreadsheet">Author<"/LastAuthor>
<Created xmlns="urn:schemas-microsoft-com:office:spreadsheet"/>
<LastSaved xmlns="urn:schemas-microsoft-com:office:spreadsheet"/>
<Company xmlns="urn:schemas-microsoft-com:office:spreadsheet">Author<"/Company>
<Version xmlns="urn:schemas-microsoft-com:office:spreadsheet">11.8132<"/Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight xmlns="urn:schemas-microsoft-com:office:spreadsheet">12660<"/WindowHeight>
<WindowWidth xmlns="urn:schemas-microsoft-com:office:spreadsheet">19020<"/WindowWidth>
<WindowTopX xmlns="urn:schemas-microsoft-com:office:spreadsheet">120<"/WindowTopX>
<WindowTopY xmlns="urn:schemas-microsoft-com:office:spreadsheet">105<"/WindowTopY>
<ProtectStructure xmlns="urn:schemas-microsoft-com:office:spreadsheet">False<"/ProtectStructure>
<ProtectWindows xmlns="urn:schemas-microsoft-com:office:spreadsheet">False<"/ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="s21">
<NumberFormat ss:Format="Percent"/>
</Style>
<Style ss:ID="s22">
<NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yyyy;@"/>
</Style>
<Style ss:ID="s23">
<NumberFormat ss:Format="mm/dd/yyyy;@"/>
</Style>
<Style ss:ID="s24">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="SomeSheetName">
<Table ss:ExpandedColumnCount="33" ss:ExpandedRowCount="5768" x:FullColumns="1" x:FullRows="1">
<Column ss:Width="111"/>
<Row>
<Cell ss:StyleID="s24">
<Data ss:Type="String">ABCD<"/Data>
</Cell>
<Cell ss:StyleID="s24">
<Data ss:Type="String">ABCD<"/Data>
</Cell>
<Cell ss:StyleID="s24">
<Data ss:Type="String">ABCD<"/Data>
</Cell>
<Cell ss:StyleID="s24">
<Data ss:Type="String">ABCD<"/Data>
</Cell>
<Cell ss:StyleID="s24">
<Data ss:Type="String">ABCD<"/Data>
</Cell>
</Row>
</Column>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<ProtectObjects>False<"/ProtectObjects>
<ProtectScenarios>False<"/ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
Hope this helps.
希望这可以帮助。
回答by schernichkin
As mentioned before, the easiest way to export data to excel is to generate text or xml presentation. As for me, I prefer to use SpreadSheetML and T4 text templating engine for file generation. You may take a look on sample T4 file here: http://lilium.codeplex.com/SourceControl/changeset/view/40985#803959.
如前所述，将数据导出到 excel 的最简单方法是生成文本或 xml 演示文稿。至于我，我更喜欢使用 SpreadSheetML 和 T4 文本模板引擎来生成文件。您可以在此处查看示例 T4 文件：http: //lilium.codeplex.com/SourceControl/changeset/view/40985#803959。
If you decide to use T4 please keep in mind that T4 is the part of MS Visual Studio and you MAY NOT distribute it separately. The problem can be solved by installing Visual Studio Express Edition on target machine.
如果您决定使用 T4，请记住 T4 是 MS Visual Studio 的一部分，您可能不会单独分发它。该问题可以通过在目标机器上安装 Visual Studio Express Edition 来解决。
Alternatively you may use build-in aspx templating engine, used for aspx view classes generation. Take a look how it's done here [oops, it's don't allow me to post more hyperlinks, if you still interested, let me know] (note that this is a real-world application so code quite large and dirty). Aspx engine handles Style tag in it's own way, so you will have to use qualified name to make it work, automatic formatting within Visual Studio also will not work correctly.
或者，您可以使用内置的 aspx 模板引擎，用于生成 aspx 视图类。看看这里是如何完成的 [哎呀，它不允许我发布更多超链接，如果您仍然感兴趣，请告诉我]（请注意，这是一个真实世界的应用程序，因此代码非常庞大且肮脏）。Aspx 引擎以自己的方式处理 Style 标记，因此您必须使用限定名称才能使其工作，Visual Studio 中的自动格式化也将无法正常工作。
回答by Hidden_au
You can still insert into the Excel spreadsheet using the same technique as identified in the linked article.
您仍然可以使用链接文章中确定的相同技术插入 Excel 电子表格。
Just use the following psuedo code
只需使用以下伪代码
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
myCommand.CommandText = "Insert into [Sheet1$] (id,name) values('@p1', '@p2')";
myCommand.Parameters.Add("@p1", OleDbType.VarChar, 100);
myCommand.Parameters.Add("@p2", OleDbType.VarChar, 100);
// define query to entity data model
var model = from i in myEntity.Inquiries select i;
foreach(var m in model)
{
cmd.Parameters["@p1"].Value = m.RequestID;
cmd.Parameters["@p2"].Value = m.CustomerName;
// .. Add other parameters here
cmd.ExecuteNonQuery();
}
}
回答by rtpHarry
You can use the open source NPOI library to write out excel, doc, powerpoint, etc
可以使用开源的NPOI库写出excel、doc、powerpoint等
回答by Flatlineato
The easy way is to bind a grid view and export it, see http://aspalliance.com/771(first esample find with google)
简单的方法是绑定网格视图并将其导出，请参阅http://aspalliance.com/771（第一个 esample 用 google 查找）
回答by William
You could use reflection to get a list of properties, then use that list of properties (and reflection) to plug the result of your query into an old ADO.Net DataTable. DataTable has WriteXML, which can be used to store a temporary XML file in the temp folder (using System.IO). Then simply use OpenXML in the Excel Application.
您可以使用反射来获取属性列表，然后使用该属性列表（和反射）将查询结果插入到旧的 ADO.Net 数据表中。DataTable 具有 WriteXML，可用于在 temp 文件夹中存储临时 XML 文件（使用 System.IO）。然后只需在 Excel 应用程序中使用 OpenXML。
I have code samples at http://social.msdn.microsoft.com/Forums/en-US/whatforum/thread/69869649-a238-4af9-8059-55499b50dd57if you want to try this approach. IMO this seems to be the quickest (at least, a lot quicker then trying to write directly to Excel), easiest(at least, a lot easier then trying to convert your query to some XML format on your own), and most reusable method (plus in the code sample we're late-binding, so you can develop against mixed environment assuming at least Excel 2003).
如果您想尝试这种方法，我在http://social.msdn.microsoft.com/Forums/en-US/whatforum/thread/69869649-a238-4af9-8059-55499b50dd57 上有代码示例。IMO 这似乎是最快的（至少，比尝试直接写入 Excel 快很多），最简单的（至少，比尝试自己将查询转换为某种 XML 格式要容易得多），并且是最可重用的方法（加上在代码示例中我们是后期绑定的，因此您可以在至少假设 Excel 2003 的情况下针对混合环境进行开发）。
