?
Solved

export contents of grid view control to excel

Posted on 2009-05-09
11
Medium Priority
?
433 Views
Last Modified: 2013-11-27
Hello Guys! I'm tryting to export the contents of a grid view control to excel, but am facing an error. I have pasted the code below. I have used this code sucessfully while using a datagrid. Could you please point out as to where i am wrong.
Dim attachment As String = "attachment; filename=Contents.xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"
        Dim sw As New System.IO.StringWriter
        Dim htw As New HtmlTextWriter(sw)
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.End()

Open in new window

image.bmp
0
Comment
Question by:preethamonline
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24344272
It's an ASP.NET Page.  And everything must be placed in a form... like this

<form id="myform" runat="server">
<asp:gridview id="GridView1" ....  >
</form>
0
 

Author Comment

by:preethamonline
ID: 24344318
Its placed inside a form only. Let me paste the code. Thanks in advance
<form id="form1" runat="server">
   
    <asp:TextBox ID="TextBox1" runat="server" 
        style="z-index: 1; left: 193px; top: 60px; position: absolute"></asp:TextBox>
 
    <asp:TextBox ID="TextBox2" runat="server" 
        style="z-index: 1; left: 360px; top: 60px; position: absolute"></asp:TextBox>
 
    <asp:Button ID="Button1" runat="server" 
        style="z-index: 1; left: 520px; top: 53px; position: absolute" 
        Text="Button" />
 
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        AutoGenerateEditButton="True" CellPadding="4" Font-Names="Verdana" 
        Font-Size="Small" ForeColor="#333333" 
        style="z-index: 1; left: 33px; top: 173px; position: absolute; height: 38px; width: 187px">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:BoundField DataField="empid" HeaderText="ID" ReadOnly="True" />
            <asp:BoundField DataField="empname" HeaderText="Name" />
            <asp:BoundField DataField="empdesignation" HeaderText="Designation" />
            <asp:BoundField DataField="empstatus" HeaderText="Status" />
            <asp:BoundField DataField="empgrade" HeaderText="Grade" />
            <asp:BoundField DataField="empdepartment" HeaderText="Department" />
            <asp:BoundField DataField="empdoj" DataFormatString="{0:d}" HeaderText="DOJ" />
            <asp:BoundField DataField="empdol" DataFormatString="{0:d}" HeaderText="DOE" />
            <asp:BoundField DataField="empnationality" HeaderText="Nationality" />
            <asp:BoundField DataField="empvisastatus" HeaderText="Visa Status" />
            <asp:BoundField DataField="empvisaexpiry" DataFormatString="{0:d}" 
                HeaderText="Visa Expiry" />
            <asp:BoundField DataField="emppassportnumber" HeaderText="Passport No." />
            <asp:BoundField DataField="emppassportexpiry" DataFormatString="{0:d}" 
                HeaderText="Passport Expiry" />
            <asp:BoundField DataField="empbasicsalary" HeaderText="Basic Salary" />
            <asp:BoundField DataField="emphrastatus" HeaderText="HRA Status" />
            <asp:BoundField DataField="emphratype" HeaderText="HRA Type" />
            <asp:BoundField DataField="emphraallowence" HeaderText="HRA Allowance" />
            <asp:BoundField DataField="emphractc" HeaderText="HRA CTC" />
            <asp:BoundField DataField="emptransportationstatus" 
                HeaderText="Transport Status" />
            <asp:BoundField DataField="emptransporttype" HeaderText="Transport Type" />
            <asp:BoundField DataField="emptransportallowence" 
                HeaderText="Transport Allowance" />
            <asp:BoundField DataField="emptransportctc" HeaderText="Transport CTC" />
            <asp:BoundField DataField="empsplallowence" HeaderText="Spl. Allowance" />
            <asp:BoundField DataField="empsplallowenceremarks" 
                HeaderText="Spl. Allowance Remarks" />
            <asp:BoundField DataField="empfuelstatus" HeaderText="Fuel / Salik" />
            <asp:BoundField DataField="empfuel" HeaderText="Fuel / Salik Allowance" />
            <asp:BoundField DataField="empleavedays" HeaderText="Paid Leave (Days)" />
            <asp:BoundField DataField="empleavecost" HeaderText="Paid Leave Cost" />
            <asp:BoundField DataField="empgratuitydays" HeaderText="Gratuity (Days)" />
            <asp:BoundField DataField="empgratuitycost" HeaderText="Gratuity Cost" />
            <asp:BoundField DataField="empairtravelstatus" 
                HeaderText="Air Travel (Years)" />
            <asp:BoundField DataField="empairtravelcost" HeaderText="Air Travel Cost" />
            <asp:BoundField DataField="empairtravelfamilystatus" 
                HeaderText="Air Travel Family" />
            <asp:BoundField DataField="empairtravelfamilycost" 
                HeaderText="Air Travel Family Cost" />
            <asp:BoundField DataField="empmedicalinsurance" 
                HeaderText="Medical Insurance" />
            <asp:BoundField DataField="empgrouplifeinsurance" 
                HeaderText="Group Life Insurance" />
            <asp:BoundField DataField="empactive" HeaderText="Active" />
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:Label ID="lblerror" runat="server" 
        style="z-index: 1; left: 113px; top: 20px; position: absolute; width: 436px" 
        Text="Label"></asp:Label>
    <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="~/excel.bmp" 
        style="z-index: 1; left: 0px; top: 173px; position: absolute" />
    </form>

Open in new window

0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24344341
The code over is assigned to this ASPX file "CodeBehind"?
Can you show us the codebehind?

Because if this piece of code is really in code behind of this ASPX file, then it should work.
As soon as you type "GridView1." in the CodeBehind, if you see the intellisense menu, it mean that the GridView1 is accessible.  (unless you defined it too in your codebehind)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:preethamonline
ID: 24344377
When i type Gridview1 i do get the intellisense menu. This the code that i've used for when the user clicks a button to export it to excel. Should i delete the gridview control and create it again?

        Dim attachment As String = "attachment; filename=Contents.xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"
        Dim sw As New System.IO.StringWriter
        Dim htw As New HtmlTextWriter(sw)
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.End()
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24344465
Maybe you could try it... just add a simplified version just to test, with AutoGeneratedColumn="True".
You don't seems to have an SqlDataSource (DataSourceID) defined in your GridView1.  Is this normal in your case?
0
 

Author Comment

by:preethamonline
ID: 24346811
I'm actually not sure. New to using the grid view control. Should I use a sqldatasource. I guess by using this it would be easy for us to update the data right?
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24346900
Well, i think that using an SQLDataSource is quite easy.  All what you have to do is to define your SelectCommand, UpdateCommand, etc... and also their parameters.  Once you set the DataSourceID of your gridview to your SQLDataSource, then your gridview know what to do when you select, update, insert rows...

How do you actually fill your gridview?

by the way, I don't know what's the RenderControl method.  Can you explain what it does?
0
 

Author Comment

by:preethamonline
ID: 24347282
Occasionally there is a need to get string representation of ASP.NET control in other words - render it into string instead of let it be rendered on the page.

I guess its to get the contents from the datagrid and write it to the excel file
0
 

Accepted Solution

by:
preethamonline earned 0 total points
ID: 24348679
Hey guys. I just found this code snippet at this site. This one solved my problem.
http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24351170
Wow, that's great and seems to be extremly easy to use.  Thanks for sharing it.
0
 

Author Comment

by:preethamonline
ID: 24362636
No worries.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question