Export dataset to excel

Question

1.44/5 (2 votes)

See more:

I have implemented export dataset to excel using
:Excel template, which contains namerange where the data needs to be inserted
:Httpcontext
:FileStream and byte array

The problem m facing is, everytime i write data into excel, It also appends column header present in the datatable.
I want to avoid the column header, as this is already present in the excel template(row above nameranges)

Please provide me a solution.
Atleast let me know, how to get the content of HttpContext object in debug mode, so that i can do RnD with that.

Posted 10-Dec-12 17:48pm

Vinay iGATE

Updated 17-Nov-16 5:42am

AnkitGoel.com

v2

Add a Solution

Comments

Vinay iGATE 11-Dec-12 22:25pm

I found out something in connection string as HDR.
Does anyone knows, whats the use of this?
HDR = YES / HDR = No

Phouttrat 22-Dec-15 5:56am

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
However it is possible that you'll also need to use "IMEX=1;", see the following link:
https://www.connectionstrings.com/excel/

But another problem that you have is selecting the named range in your template file. With OLEDB you're unable to do that so as an alternative you may want to try out this excel's library for C#. It can enable you to export a DataTable into an excel file's named range.
Also it has a direct way of exporting excel file to browser in ASP.NET.

So your implementation steps would be
: Read excel template
: Get required NamedRange
: Insert DataTable on that NamedRange's position
: Stream excel to client's browser

3 solutions

Solution 1

Try this I Hope it will solve your Issue

In .Aspx page

ASP.NET

<asp:DataGrid ID="myGridView" Width="100%" runat="server" AutoGenerateColumns="False"
            DataKeyNames="Id" AlternatingRowStyle-CssClass="alt">
            <ItemStyle BackColor="#EBEBEB" />
            <AlternatingItemStyle BackColor="White" />
            <Columns>
                <asp:TemplateColumn HeaderStyle-HorizontalAlign="Center" HeaderText="ID">
                    <ItemTemplate >
                        <asp:Label ID="lblId" runat="server" Text='<%# Eval("ID")%>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" Width="15%" />
                </asp:TemplateColumn>

            <asp:TemplateColumn ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" HeaderText="Name">
                    <ItemTemplate>
                        <asp:Label ID="lblName" Text='<%# Eval("Name")%>' runat="server"></asp:Label>
                    </ItemTemplate>mn
                    <ItemStyle HorizontalAlign="Center" Width="20%" />
                </asp:TemplateColumn>

</Columns>

</asp:DataGrid>

In .cs page

C#

       myGridView.DataSource = dd.tables[0];
        myGridView.DataBind();
        ExportToExcel(myGridView);



       public void ExportToExcel(System.Web.UI.Control ctl)
         {
         using (Control myCtl = ctl)
            {
        HttpContext.Current.Response.AppendHeader("Content-Disposition", attachment);
        HttpContext.Current.Response.Charset = charSet;
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.ContentType = content;
        myCtl.Page.EnableViewState = false;
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

       //Renders the control here.
        myCtl.RenderControl(htw);
        HttpContext.Current.Response.Write(sw.ToString());
        HttpContext.Current.Response.End();
    }
}

Keep smiling :):):) Happy coding:)

Posted 10-Dec-12 19:10pm

IpsitaMishra

Comments

Vinay iGATE 11-Dec-12 1:13am

I dont have datagridview. Data is coming directly from a table in a dataset :(

IpsitaMishra 11-Dec-12 6:32am

you can add a grid and make that invisible just a suggestion :)

Solution 3

This article may help: 9 Solutions to Export Data to Excel for ASP.NET[^]

Posted 11-Dec-12 16:07pm

Lacy00

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Accepted Answer · 2012-12-10T19:15:00

Refer:
Article: Fast Exporting from DataSet to Excel[^]
Similar answer: How to Export Value from Data Set to Excel Sheet[^]
Lot more similar stuff on CodeProject[^]

In short:

C#

for (int col = 0; col < dataTable.Columns.Count; col++)
{
    for (int row = 0; row < dataTable.Rows.Count; row++)
    {
        excelSheet.Cells[row + 1, col + 1] =
                dataTable.Rows[row].ItemArray[col];
    }
}

..and for bulk data you can go with:

C#

excelSheet.get_Range("A1:H25", Type.Missing).Value2 =
    bidimensionalObjectArray;