How to Export data to Excel using LINQ to Entity?

Question

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.

If it is using OLEDB, it is straight forward as it is here: 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

http://www.hanselman.com/blog/BackToBasicsKeepItSimpleAndDevelopYourSenseOfSmellFromLinqToCSV.aspx — Craig Stuntz, Feb 05 '10 at 13:38

score 3 · Answer 1 · answered Mar 29 '10 at 01:00

You can still insert into the Excel spreadsheet using the same technique as identified in the linked article.

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

score 1 · Answer 2 · answered Oct 23 '12 at 18:29

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.

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).

score 1 · Answer 3 · answered Feb 05 '10 at 15:15

1

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.

answered Feb 05 '10 at 15:15

Corey Coogan

1,569
2
17
31

I found the solution. Here you go. http://stackoverflow.com/questions/1932568/exporting-a-html-table-to-excel – Rita Feb 05 '10 at 17:41
You can also take a look here: http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c/ – Leniel Maccaferri Feb 28 '10 at 20:33

score 0 · Answer 4 · edited Mar 29 '10 at 01:09

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.

<?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.

score 0 · Answer 5 · answered Mar 22 '10 at 07:30

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.

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.

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.

score 0 · Answer 6 · answered Apr 05 '10 at 15:12

0

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

http://npoi.codeplex.com/

answered Apr 05 '10 at 15:12

rtpHarry

13,019
4
43
64

score 0 · Answer 7 · answered Apr 07 '10 at 12:00

0

The easy way is to bind a grid view and export it, see http://aspalliance.com/771 (first esample find with google)

answered Apr 07 '10 at 12:00

Flatlineato

1,066
15
32

How to Export data to Excel using LINQ to Entity?

7 Answers7