Excel worksheets in asp.net

  • Thread starter Thread starter DavidC
  • Start date Start date
D

DavidC

I have a process to send data to Excel in a browser window from both a server
control and via dataset code. I would like to create multiple worksheets
(tabs in lower part of Excel spreadsheet) in one xls file. Is that possible?
Below is a sample of code I am using to dump to Excel. Thanks.

Sub ExportDataSetToExcel(ByVal Source As Object, ByVal E As EventArgs)
strExcelSQL = "mc_selPeopleSearchHomeExcel"
Response.Clear()
Response.ContentType = "application/vnd.excel"
Response.Charset = "us-ascii"
Response.AddHeader("content-disposition",
"attachment;filename=search.xls")

Dim gv As New GridView
Using conFileData As SqlConnection = New
SqlConnection(DBClass.GetCoreConnectionString)
conFileData.Open()

Dim ddl As DropDownList =
Page.Master.FindControl("ddlProgramCode")
Dim intProgramID As Int32 = Convert.ToInt32(ddl.SelectedValue)
Dim tb As TextBox = Page.Master.FindControl("txtFindText")

'Get records based on sql
Dim cmdSel As SqlCommand = New SqlCommand(strExcelSQL,
conFileData)
cmdSel.CommandType = Data.CommandType.StoredProcedure

Dim parameter As New SqlParameter()
parameter.ParameterName = "@ProgramID"
parameter.SqlDbType = Data.SqlDbType.Int
parameter.Direction = Data.ParameterDirection.Input
parameter.Value = intProgramID
cmdSel.Parameters.Add(parameter)

parameter = New SqlParameter()
...
...

Dim dtr As SqlDataReader
dtr = cmdSel.ExecuteReader()
gv.DataSource = dtr
gv.DataBind()
dtr.Close()

End Using

Dim sw As New StringWriter()
Dim htw As New System.Web.UI.HtmlTextWriter(sw)
EnableViewState = False
gv.RenderControl(htw)
Response.Write(sw.ToString())
Response.End()
End Sub
 
DavidC said:
I have a process to send data to Excel in a browser window from both a
server
control and via dataset code. I would like to create multiple worksheets
(tabs in lower part of Excel spreadsheet) in one xls file. Is that
possible?
Click to expand...

Yes, but using a different technique.
You can do this with xslt.
 
DavidC said:
Can you point me to where I can find out more about this? Thanks.

David
Click to expand...

If you google on
dataset xslt excel

There are a number of examples.
For all your formatting, the best way is to create an excel spreadsheet like
you want.
Save it as xml and take a look at what you get.
 
Back
Top