Click here to Skip to main content
12,501,207 members (39,670 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 10-Dec-12 19:37pm
v2
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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:

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:
excelSheet.get_Range("A1:H25", Type.Missing).Value2 =
    bidimensionalObjectArray;
  Permalink  
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try this I Hope it will solve your Issue

In .Aspx page
<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">
In .cs page
       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:)
  Permalink  
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 :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 11 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100