How can I export a Dataview to Excel

[Paul D. Fox]

I've figured out how to export a Dataset and a Datagrid, but how can I
export a Dataview?

Paul

 

[Elton Wang]

So it will be easy.

DataGrid.DataSourse = dataview

HTH

Elton Wang
(e-mail address removed)

 

[Mike Chamberlain]

So it will be easy.

DataGrid.DataSourse = dataview

That's not what he asked. I am also very interested in the solution.

Mike

 

[Paul D. Fox]

I think I've figured out the solution. I asked about exporting a Dataview
because my Datagrid is rather complex with the use of bi-directional sorting
and paging (thus the use of a Dataview). What I found out was that if the
Datagrid "Has Controls" they need to be stripped out, or at least converted
to a literal prior to exporting to Excel.

Here is my example in C# which can easily be converted to VB.NET

private void ExportDataGrid()

{

Response.Clear();

Response.Buffer= true;

Response.ContentType = "application/vnd.ms-excel";

Response.AppendHeader("Content-Disposition",
"attachment;filename=Lead_FeedBack.xls");

Response.Charset = "";

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new
System.Web.UI.HtmlTextWriter(oStringWriter);

// Create a DataGrid control and Pass content from first DataGrid to it

DataGrid DataGrid2 = new DataGrid();

DataGrid2 = DataGrid1;

this.RemoveControls(DataGrid2);

//Lets make it a generic looking spreadsheet

DataGrid2.HeaderStyle.BackColor = Color.LightGray;

DataGrid2.AlternatingItemStyle.BackColor = Color.White;

DataGrid2.PagerStyle.BackColor = Color.LightGray;

DataGrid2.HeaderStyle.Font.Bold = true;

DataGrid2.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();

}

private void RemoveControls(Control control)

{

// This routine will remove all the controls such as Sorting,Paging,
Buttons, etc.

for (int i=control.Controls.Count -1; i>=0; i--)

{

RemoveControls(control.Controls);

}

if (!(control is TableCell))

{

if (control.GetType().GetProperty("SelectedItem") != null)

{

control.Parent.Controls.Remove(control);

}

else

if (control.GetType().GetProperty("Text") != null)

{

control.Parent.Controls.Remove(control);

}

}

return;

}

private void ReplaceControls(Control control)

{

// This routine will replace all the controls such as Sorting,Paging,
Buttons, etc. with Literal controls (text)

for (int i=control.Controls.Count -1; i>=0; i--)

{

ReplaceControls(control.Controls);

}

if (!(control is TableCell))

{

if (control.GetType().GetProperty("SelectedItem") != null)

{

LiteralControl literal = new LiteralControl();

control.Parent.Controls.Add(literal);

try

{

literal.Text =
(string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);

}

catch

{

}

control.Parent.Controls.Remove(control);

}

else

if (control.GetType().GetProperty("Text") != null)

{

LiteralControl literal = new LiteralControl();

control.Parent.Controls.Add(literal);

literal.Text =
(string)control.GetType().GetProperty("Text").GetValue(control,null);

control.Parent.Controls.Remove(control);

}

}

return;

}



Paul