Hi my code is working. But i can't export in excel. i can export other gridview but not this on with dynamic column based.
please see my code..
<asp:CheckBoxList runat="server" ID="chkFields" DataTextField="Column_name" DataValueField="Column_name" RepeatDirection="Horizontal" RepeatLayout="Flow" />
<asp:Button ID="btnSub" CssClass="btn btn-warning" runat="server" Text="Show" OnClick="ShowGrid" />
<asp:GridView ID="GridView1" CssClass="table table-condensed table-bordered" runat="server" EnableViewState="false" AutoGenerateColumns="false" />
Public Sub Export(ByRef grdview As GridView, filename As String, contenttype As String)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=" & filename)
Response.Charset = ""
Response.ContentType = "application/" & contenttype 'vnd.ms-word vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
grdview.AllowPaging = False
grdview.DataBind()
grdview.RenderControl(hw)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()
End Sub
Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs) Handles btnExportToExcel.Click
Export(GridView1, "Report.xls", "vnd.ms-excel")
'End If
End Sub
Private _connStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Private Sub BindTableColumns()
Dim table As New DataTable()
Using conn As New SqlConnection(_connStr)
Using cmd As New SqlCommand("sp_columns", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@table_name", "qryCustomisedReport")
' get the adapter object and attach the command object to it
Using ad As New SqlDataAdapter(cmd)
' fire Fill method to fetch the data and fill into DataTable
ad.Fill(table)
End Using
chkFields.DataSource = table
chkFields.DataBind()
End Using
End Using
End Sub
Private Sub GetData()
Dim table As New DataTable()
' get the connection
Using conn As New SqlConnection(_connStr)
' write the sql statement to execute
Dim sql As String = "SELECT ProjectID, ProjectName, ProjectRef, ProjectSponsor, [Project Status], Phase, Confidential, Strategic, BusinessArea, StartDate, FinishDate, Description, ProjectSmallChange, GDPR, [Project Registration Status], [Project Manager], Overall FROM qryCustomisedReport order by ProjectRef"
' instantiate the command object to fire
Using cmd As New SqlCommand(sql, conn)
' get the adapter object and attach the command object to it
Using ad As New SqlDataAdapter(cmd)
' fire Fill method to fetch the data and fill into DataTable
ad.Fill(table)
End Using
End Using
End Using
' specify the data source for the GridView
GridView1.DataSource = table
' bind the data now
GridView1.DataBind()
End Sub
Protected Sub ShowGrid(sender As Object, e As EventArgs)
For Each item As ListItem In chkFields.Items
If item.Selected Then
Dim b As New BoundField()
b.DataField = item.Value
b.HeaderText = item.Value
GridView1.Columns.Add(b)
End If
Next
Me.GetData()
End Sub