locked
Export SqlDataSource to excel RRS feed

  • Question

  • User-1687766116 posted

    Hi,

    I want export all data of SqlDataSource to excel,

    How i do?

    Help me, Please

    Tuesday, November 10, 2015 2:44 AM

Answers

All replies

  • User603616845 posted

    Hi,

    You can create a DataTable from the DataView returned by the SqlDataSource and export that to Excel. The resulting DataTable will contain all the columns.

    Since you are using a SqlDataSource then you can get a DataTable out of it like this:

    var dv = new DataView();
    var dt = new DataTable();
    dv = (DataView)mySQLDataSource.Select(DataSourceSelectArguments.Empty);
    dt = dv.ToTable();

    I wrote a set of extension methods to do DataTable to CSV, which you should be able to use easily.

    The rest of your code would be pretty simple then:

    var csv = dt.ToCSV();

    Here is a full example using this very method.

    The Markup:

    <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
        CodeBehind="Default.aspx.cs" Inherits="SO.WebUI._Default" %>
    
    <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    </asp:Content>
    <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeId"
            DataSourceID="SqlDataSourceLocal">
            <Columns>
                <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" InsertVisible="False"
                    ReadOnly="True" SortExpression="EmployeeId" Visible="False" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                <asp:BoundField DataField="Birthdate" HeaderText="Birthdate" SortExpression="Birthdate" />
            </Columns>
        </asp:GridView>
    
        <!-- This is the button to export CSV! -->
        <asp:Button ID="btnToCSV" runat="server" Text="Export to CSV" OnClick="GetCSV" />
    
        <asp:SqlDataSource ID="SqlDataSourceLocal" runat="server" 
        ConnectionString="<%$ ConnectionStrings:StackOverflowExamplesConnectionString %>"
            SelectCommand="SELECT * FROM [Employee]"></asp:SqlDataSource>
    </asp:Content>

    And the code-behind:

    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
    
        public void GetCSV(object sender, EventArgs e)
        {
            DataView dv = (DataView)SqlDataSourceLocal.Select(DataSourceSelectArguments.Empty);
            var dt = dv.ToTable();
    
            var csv = dt.ToCSV();
    
            WriteToOutput(csv, "export.csv", "text/csv");
        }
    
        private void WriteToOutput(String csv, String fileName, String mimeType)
        {
            Response.Clear();
            Response.ContentType = mimeType;
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
            Response.Write(csv);
            Response.End();
        }
    }

    There you go. The only thing not included here is the extension method source, but since I've linked to it, you should have no problems getting that into a static class. Here is thread on StackOverFlow.

    Hope this will help you.

    thanks

    Tuesday, November 10, 2015 3:00 AM
  • User-1716253493 posted

    You can get datatable like this

    C#

    DataTable dt = (DataTable)((DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty)).Table;

    VB

    Dim dt as DataTable = DirectCast(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView).Table

    Now, you can search how to export datatable to excel 

    Tuesday, November 10, 2015 3:04 AM
  • User1724605321 posted

    Hi mehr,

    I want export all data of SqlDataSource to excel,

    As other member suggested ,you could firstly create a DataTable from the DataView returned by the SqlDataSource . Then you could also use openxml sdk to export the data to excel ,please refer to links below for demo:

    http://stackoverflow.com/questions/11811143/export-datatable-to-excel-with-open-xml-sdk-in-c-sharp .

    http://www.codeproject.com/Tips/366446/Export-GridView-Data-to-Excel-using-OpenXml .

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 10, 2015 3:23 AM
  • User-1687766116 posted

    Error 11 'ToCSV' is not a member of 'System.Data.DataTable'

    Tuesday, November 10, 2015 3:25 AM
  • User-1687766116 posted

    in 

     Dim csv = dt.ToCSV()

    Error:

    'ToCSV' is not a member of 'System.Data.DataTable'

    Tuesday, November 10, 2015 3:39 AM
  • User-693045842 posted

    Hi ,

    Use openxml sdk or EPPlus to export data to excel .

    Tuesday, November 10, 2015 9:29 PM
  • User-1687766116 posted

    Hi,

    You can create a DataTable from the DataView returned by the SqlDataSource and export that to Excel. The resulting DataTable will contain all the columns.

    Since you are using a SqlDataSource then you can get a DataTable out of it like this:

    var dv = new DataView();
    var dt = new DataTable();
    dv = (DataView)mySQLDataSource.Select(DataSourceSelectArguments.Empty);
    dt = dv.ToTable();

    I wrote a set of extension methods to do DataTable to CSV, which you should be able to use easily.

    The rest of your code would be pretty simple then:

    var csv = dt.ToCSV();

    Here is a full example using this very method.

    The Markup:

    <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
        CodeBehind="Default.aspx.cs" Inherits="SO.WebUI._Default" %>
    
    <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    </asp:Content>
    <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeId"
            DataSourceID="SqlDataSourceLocal">
            <Columns>
                <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" InsertVisible="False"
                    ReadOnly="True" SortExpression="EmployeeId" Visible="False" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                <asp:BoundField DataField="Birthdate" HeaderText="Birthdate" SortExpression="Birthdate" />
            </Columns>
        </asp:GridView>
    
        <!-- This is the button to export CSV! -->
        <asp:Button ID="btnToCSV" runat="server" Text="Export to CSV" OnClick="GetCSV" />
    
        <asp:SqlDataSource ID="SqlDataSourceLocal" runat="server" 
        ConnectionString="<%$ ConnectionStrings:StackOverflowExamplesConnectionString %>"
            SelectCommand="SELECT * FROM [Employee]"></asp:SqlDataSource>
    </asp:Content>

    And the code-behind:

    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
    
        public void GetCSV(object sender, EventArgs e)
        {
            DataView dv = (DataView)SqlDataSourceLocal.Select(DataSourceSelectArguments.Empty);
            var dt = dv.ToTable();
    
            var csv = dt.ToCSV();
    
            WriteToOutput(csv, "export.csv", "text/csv");
        }
    
        private void WriteToOutput(String csv, String fileName, String mimeType)
        {
            Response.Clear();
            Response.ContentType = mimeType;
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
            Response.Write(csv);
            Response.End();
        }
    }

    There you go. The only thing not included here is the extension method source, but since I've linked to it, you should have no problems getting that into a static class. Here is thread on StackOverFlow.

    Hope this will help you.

    thanks

    my code is:

       Public Sub GetCSV(ByVal sender As Object, ByVal e As EventArgs)
            Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
            Dim dt = dv.ToTable
            Dim csv = dt.ToCSV
            Me.WriteToOutput(csv, "export.csv", "text/csv")
        End Sub
    
        Private Sub WriteToOutput(ByVal csv As String, ByVal fileName As String, ByVal mimeType As String)
            Response.Clear()
            Response.ContentType = mimeType
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName))
            Response.Write(csv)
            Response.End()
        End Sub

    but in:

     var csv = dt.ToCSV();

    i get error:

    'ToCSV' is not a member of 'System.Data.DataTable'. 

    Help me, Please,

    Thanks

    Tuesday, November 10, 2015 11:18 PM
  • User1724605321 posted

    Hi MEHR_83,

    You need to firstly convert DataTable to string which includes records information .Please refer to links below for how to export to excel from DataTable using C#.NET :

    http://www.dotnetgallery.com/lab/resource44-Export-to-excel-from-DataTable-using-CNET.aspx .

    http://www.codeproject.com/Articles/406704/Export-DataTable-to-Excel-with-Formatting-in-Cshar .

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 11, 2015 4:25 AM
  • User1708713926 posted

    hi mehr,

    as others suggested, You can create a DataTable from the DataGridView returned by the SqlDataSource, then export it to Excel. there is an easy way to export DataTable to excel through DataGridview by using free .NET DataExport control. refer to the sample code below:

    //Step 1: Load Data Information
    private void btnLoad_Click(object sender, EventArgs e)
    {
        using(OleDbConnection oleDbConnection = new OleDbConnection())
        {
            oleDbConnection.ConnectionString = this.textBox1.Text;
            OleDbCommand oleDbCommand = new OleDbCommand();
            oleDbCommand.CommandText = this.textBox2.Text;
            oleDbCommand.Connection = oleDbConnection;
            using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
    } 
    //Step 2: Set Export into Excel
    private void btnRun_Click(object sender, EventArgs e)
    {
        Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
        Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
        worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
        worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;
        worksheet1.StartDataCol = ((System.Byte)(0));
        cellExport.Sheets.Add(worksheet1);
        cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView; 
        cellExport.SaveToFile("20110223.xls");
    }
    

    for more information, click here.

    hope this can help you.

    Regards,

    alice

    Tuesday, November 17, 2015 3:57 AM