Forums - C# Corner

Forum guidelines
Rajesh M

Rajesh M

  • 1.4k
  • 1
  • 1.1k

Run Time Added Columns are not exporting into Excel from DataGrid

Dec 17 2012 3:19 AM
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

File APIs for .NET
Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!