how to export query data to excel

hi..

i have a query whereby i am currently putting the results in a datagrid.now the requirements have changed and i need to have a separate button on clicking to which I can put all the data of the query into an excel file.

this page is a part of the website which is running locally in my company ...its an intranet website not on the web or internet.

the data is being fetched from the database.

can anyone help me on this?

thanks a lot
LVL 1
samir25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GavinMannionCommented:
C# code

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeebukarthikeyanCommented:
hi,

if u have the data returned from a query in a dataset,just go through the records and make a html table from it.

suppose dsExcel is the dataset holding the records...just see the below code

StringBuilder strExcel      =      new StringBuilder();
strExcel.Append ("<table>" );
strExcel.Append ("<tr>" );

for( int i=0;i<dsExcel.Tables[0].Columns.Count;i++ )
{
 strExcel.Append ("<td>" );
 strExcel.Append ( dsExcel.Tables[0].Columns[i].ColumnName );
 strExcel.Append ("</td>" );
}

strExcel.Append ("</tr>" );
for( int j=0;j<dsExcel.Tables[0].Rows.Count;j++ )
{
 strExcel.Append ("<tr>" );

 for( int c=0;c<dsExcel.Tables[0].Columns.Count;c++ )
 {
  strExcel.Append ("<td>" );
  strExcel.Append ( dsExcel.Tables[0].Rows[j][c].ToString() );
  strExcel.Append ("</td>" );
}
strExcel.Append ("</tr>" );
}

strExcel.Append ("</table>" );
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
Response.Write( strExcel.ToString() );
Response.End();
samir25Author Commented:
i dont know whether i was clear or not. i actually want something like... if the user clicks the button then he can download an excel file which i genereate on this machine.

still i tried..and here is what i did after my bindgrid event

                BindData()
                Dim oStringWriter As New System.IO.StringWriter
                Dim oHTMLTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
                Me.dgDisplayModules.RenderControl(oHTMLTextWriter)
                Response.Write(oStringWriter.ToString)
                Response.End()

also no where we specified the name of the excel file etc...

after using the above code i get this error

Control 'dnn_ctr623_LoginCheck_dgDisplayModules__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

GavinMannionCommented:
For file name add

Response.AddHeader "content-disposition","attachment; filename=MyFileName.xls"

above the previous code I posted

You error is happening because you are missing the required form tags in your HTML.
samir25Author Commented:
here is hwo i am doing right now..
Sub BindData()
            Try
                GoToDBOpenConn(strSQL)
                Dim dt As New DataTable
                myDa.Fill(dt)
                Me.dgDisplayModules.DataSource = dt
                Me.dgDisplayModules.DataBind()
            Catch ex As Exception
                Throw ex
            Finally
                GoToDBCloseConn()
            End Try
        End Sub
        Public Sub GoToDBOpenConn(ByVal recdSQL As String)

            Dim connstring As String
            connstring = "PROVIDER=SQLBaseOLEDB;Data Source=hdidb;uid=SYSADM;pwd=SYSADM;"
            objConnection = New OleDbConnection(connstring)
            objConnection.Open()
            Dim objCommand As OleDbCommand
            objCommand = New OleDbCommand(recdSQL, objConnection)
            myDa.SelectCommand = objCommand

        End Sub

i m not using dataset...i m using dataadapter
samir25Author Commented:
"You error is happening because you are missing the required form tags in your HTML" ...
yes indeed the form tags are missing...becasue this is an ascx page.

so does it mean that i cannot generate an xls file if i m not using form tags...pls suggest
samir25Author Commented:
here is my complete html code:
<%@ Control Language="vb" AutoEventWireup="false"
Codebehind="LoginCheck.ascx.vb" Inherits="FVS.HelloWorld.LoginCheck"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<asp:datagrid id="dgDisplayModules" AllowSorting="True" OnSortCommand="SortCommand_OnClick" OnPageIndexChanged="dgDisplayModules_PageIndexChanged"
      PageSize="5" AllowPaging="True" Width="500" ShowFooter="True" ShowHeader="true" runat="server"
      CellPadding="3" Font-Size="smaller" borderwidth="0" HeaderStyle-Font-Bold="True" HeaderStyle-CssClass="Head"
      ItemStyle-CssClass="SubHead" AutoGenerateColumns="False">
      <ItemStyle CssClass="SubHead"></ItemStyle>
      <HeaderStyle Font-Bold="True" HorizontalAlign=Left CssClass="Head"></HeaderStyle>
      <Columns>
            <asp:BoundColumn ItemStyle-Width="30%" DataField="CALLID" SortExpression="CALLID" HeaderText="Reference No">
                  <ItemStyle HorizontalAlign=Left CssClass="SubHead"></ItemStyle>
            </asp:BoundColumn>
            <asp:TemplateColumn ItemStyle-Width="30%" HeaderText="Description">
                  <ItemStyle HorizontalAlign=Left CssClass="SubHead"></ItemStyle>
                  <ItemTemplate>
                        <%# DataBinder.Eval(Container.DataItem, "PROBLEM") %>
                        ...
                  </ItemTemplate>
            </asp:TemplateColumn>
            <asp:BoundColumn DataField="REPORTED" ItemStyle-Width="30%" SortExpression="REPORTED" HeaderText="Reported Date">
                  <ItemStyle HorizontalAlign=Left CssClass="SubHead"></ItemStyle>
            </asp:BoundColumn>
            <asp:BoundColumn DataField="CALLStatus" ItemStyle-Width="10%" SortExpression="CALLSTATUS" HeaderText="Status">
                  <ItemStyle HorizontalAlign=Left CssClass="SubHead"></ItemStyle>
            </asp:BoundColumn>
      </Columns>
      <PagerStyle NextPageText=" Next" Font-Bold="True" PrevPageText="Prev" HorizontalAlign="Right"></PagerStyle>
</asp:datagrid>
GavinMannionCommented:
When you place the user control on a .aspx page make sure you are placing it between the <form> tags...

samir25Author Commented:
wel i m not placing it any aspx page at all.

i am using dotnetnuke .here i need to create modules ... and then i just add this module def through the interface provided by dotnetnuke
GavinMannionCommented:
oh.. sorry dotnetnuke is out of my current experience level....

Try adding the form tags to your .ascx and see what happens :)...

When rendered look at the source to see if the <form> tag exists.
samir25Author Commented:
no its nothing to do with dotnetnuke...its works like any normal asp.net appl.

their wud be some way out?

i wil try and add form tags and provide input
samir25Author Commented:
i had put the form tags and still i get the same errror

isnt their any other way to resolve this....is their some other method to do it? looks very simle but i m confused. pls suggest if u can

thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.