Formatting Datagrid Export to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
Below is my code for exporting a datagrid to Excel. It works fine, but
we're hoping to format the output as well - setting the font size and type,
and giving each column a specific width, possibly bolding some text.
Currently we are using a macro to do this, but we would like the report to be
formatted when it's generated so that a macro will be unnecessary.
Thanks!
John

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Dim objConn As New System.Data.SqlClient.SqlConnection(strConnString)
objConn.Open()

Dim strSQL As String
Dim objDataset As New DataSet
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter

strSQL = "exec " & sDBAllianceSync & ".dbo.spTruckerPlanning
@StartDiv, @EndDiv, @StartTeam, @EndTeam, @StartETAPOE, @EndETAPOE "

objAdapter.SelectCommand = New
System.Data.SqlClient.SqlCommand(strSQL, objConn)
objAdapter.SelectCommand.Parameters.Add("@StartDiv",
Session("StartingDivision"))
objAdapter.SelectCommand.Parameters.Add("@EndDiv",
Session("EndingDivision"))
objAdapter.SelectCommand.Parameters.Add("@StartTeam",
Session("StartingTeam"))
objAdapter.SelectCommand.Parameters.Add("@EndTeam",
Session("EndingTeam"))
objAdapter.SelectCommand.Parameters.Add("@StartETAPOE",
Session("StartingETAPOE"))
objAdapter.SelectCommand.Parameters.Add("@EndETAPOE",
Session("EndingETAPOE"))
objAdapter.SelectCommand.CommandTimeout = 120

' Fill the dataset.
objAdapter.Fill(objDataset)

' Create a new view.
Dim oView As New DataView(objDataset.Tables(0))
' Set up the data grid and bind the data.
DataGrid1.DataSource = oView
DataGrid1.DataBind()

' Set the content type to Excel.
Response.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Response.Write(tw.ToString())
' End the response.
Response.End()
End Sub
 
You'll need to add the styles used by the datagrid into the HTML stream
as inline style definitions.

John H.
 
Would you have sample code? I don't have an idea about how to add styles
into an HTML stream.
Thanks!
John
 
Back
Top