Forum guidelines
AuthorQuestion
Run Time Added Columns are not exporting into Excel from DataGrid
Posted on: 17 Dec 2012
Hi,

I am new to this Site as well as new in ASP.NET.

I just started working on existing ASP.NET site and need to make some changes on existing website as per my new requirement.

Here, I wanted to export the output data from datagrid to excel, But the issue is runtime adding columns(Selected columns from listbox) are not exporting. For example, I have included 5 standered columns in datagrid and remaining columns will be selected by the user  during the runtime based on their requirement. If user selects 3 column in listbox then totally 7 columns are displaying correct but when I export the data into excel only the 5 columns are exporting and the runtime selected columns are not exporting...I tried to sort it out but I could not

Here is my code.
==============
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim i As Integer
Dim PRIN_ENG_ID As String
If Not IsPostBack Then
 For i = 0 To DataGrid1.Items.Count - 1If Len(DataGrid1.Items(i).Cells(4).Text) >= 1 Then
PRIN_ENG_ID = Trim(DataGrid1.Items(i).Cells(4).Text)
 If Len(Trim(PRIN_ENG_ID)) >= 2 Then
 Session("PRIN_ENG_ID") = Trim(PRIN_ENG_ID)
 End If
 End If
Next
CAPS_DB.SelectCommand = "Select CSS_REPORT_No,IN_QUEUE, UPLOAD_FAILURE,PRIN_ENG_ID,ASSIGNED_COUNT,RESPONDED_DATETIME FROM CAPS_RECORDS WHERE (ACTIVE = 'Y') AND (LEAD_ASSIGNED_REPORT = 'LEAD')"
End If
End Sub

 Public Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
  Session("START_DATE") = Trim(DateList1.SelectedItem.Text)   Session("END_DATE") = Trim(DateList2.SelectedItem.Text)
  START_DATE = (Right(Session("START_DATE"), 2) + Mid(Session("START_DATE"), 4, 2) + Left(Session("START_DATE"), 2)).ToString   END_DATE = (Right(Session("END_DATE"), 2) + Mid(Session("END_DATE"), 4, 2) + Left(Session("END_DATE"), 2)).ToString

  If Not String.IsNullOrEmpty(START_DATE) Then
 
  If SelectList.Items.Count > 0 Then
 
 For X As Integer = 0 To SelectList.Items.Count - 1
  If SelectList.Items(X).Selected Then
  sr += "," + SelectList.Items(X).Text
  Dim column As New BoundColumn()
  column.HeaderText = SelectList.Items(X).Text
  column.DataField = SelectList.Items(X).Text
  DataGrid1.Columns.Add(column)
  End If
  Next
 
  If END_DATE > START_DATE Then
  Response.Write("<script>alert('Start date should be Higher than End Date');</script>")
  End If
 
  If END_DATE = START_DATE Then
  CAPS_DB.SelectCommand = "Select CSS_REPORT_No,IN_QUEUE, UPLOAD_FAILURE,PRIN_ENG_ID,ASSIGNED_COUNT,RESPONDED_DATETIME" & sr & " FROM CAPS_RECORDS WHERE (ACTIVE = 'Y') AND (LEAD_ASSIGNED_REPORT = 'LEAD') AND (DATE_STRING ='" & START_DATE & "')" ' ORDER BY RESPONDED_DATETIME "
   End If
 
  If END_DATE < START_DATE Then
  CAPS_DB.SelectCommand = "Select CSS_REPORT_No,IN_QUEUE, UPLOAD_FAILURE,PRIN_ENG_ID,ASSIGNED_COUNT,RESPONDED_DATETIME" & sr & " FROM CAPS_RECORDS WHERE (ACTIVE = 'Y') AND (LEAD_ASSIGNED_REPORT = 'LEAD') AND (DATE_STRING BETWEEN '" & END_DATE & "' AND '" & START_DATE & "')" 'ORDER BY RESPONDED_DATETIME "
  End If
 
  End If
 
  End If
  End Sub

Protected Sub Export_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Export.Click
Response.Clear()
Response.Buffer = True Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim stringWriter As New System.IO.StringWriter()
Dim htmlWriter As New System.Web.UI.HtmlTextWriter(stringWriter)
Me.ClearControls(DataGrid1)
Dim dg As New DataGrid()
dg.DataBind()
DataGrid1.RenderControl(htmlWriter)
Response.Write(stringWriter.ToString())
Response.End()
End Sub



Private Sub ClearControls(ByVal ctrl As Control)
Dim i As Integer
For i = ctrl.Controls.Count - 1 To 0 Step i - 1
ClearControls(ctrl.Controls(i))
Next
Dim ctrlType As Type = ctrl.GetType()
If Not ctrlType.Name = "TableCell" Then
If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl
ctrl.Parent.Controls.Add(literal)
Try
literal.Text = CType(ctrl.GetType().GetProperty("SelectedItem").GetValue(ctrl, Nothing), String)
Catch
End Try
ctrl.Parent.Controls.Remove(ctrl)
ElseIf Not ctrl.GetType().GetProperty("Text") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl
ctrl.Parent.Controls.Add(literal)
literal.Text = CType(ctrl.GetType().GetProperty("Text").GetValue(ctrl, Nothing), String)
ctrl.Parent.Controls.Remove(ctrl)
End If
End If
End Sub

Earlier help would be very much appreciated!!!

Thanks
  Rajesh

Smiles :)

      Rajesh

SPONSORED BY

Offshore Software and Database Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.