How to Export data to Excel using LINQ to Entity?

Question

Hi

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

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

Answer 2

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.

Answer 3

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.

Answer 4

Hi,

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

Answer 5

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

• http://npoi.codeplex.com/

Answer 6

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