如何使用LINQ将数据导出到Excel中?

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上的实体数据模型表的数据。网络页面。现在我需要在单击按钮时将这些数据导出到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

7 个解决方案

#1

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

只需使用下面的psuedo代码

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();
    }
  } 

#2

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中自动打开。

#3

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中。净数据表。DataTable具有WriteXML，可用于在temp文件夹(使用System.IO)中存储临时XML文件。然后在Excel应用程序中使用OpenXML。

I have code samples at http://social.msdn.microsoft.com/Forums/en-US/whatforum/thread/69869649-a238-4af9-8059-55499b50dd57 if 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有代码示例，如果您想尝试这种方法的话。国际海事组织这似乎是最快的(至少,很多更快然后试图直接写入Excel),简单的(至少,更容易尝试将您的查询转换为一些自己的XML格式),和最可重用方法(加在我们后期绑定的代码示例,那么您可以开发针对混合环境假设至少Excel 2003)。

#4

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是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引擎以它自己的方式处理样式标记，因此您必须使用限定名才能使其工作，Visual Studio中的自动格式也不能正常工作。

#5

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文件的顶部提到了一个茧过程，在双击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.

希望这个有帮助。

#6

You can use the open source NPOI library to write out excel, doc, powerpoint, etc

您可以使用开放源码的NPOI库编写excel、doc、powerpoint等

• http://npoi.codeplex.com/
• http://npoi.codeplex.com/

#7

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查找)