Excel worksheets in asp.net

DavidC

unread,

Mar 18, 2010, 4:58:01 PM3/18/10

to

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
--
David

Mark Rae [MVP]

unread,

Mar 18, 2010, 6:01:30 PM3/18/10

to

"DavidC" <dlc...@lifetimeinc.com> wrote in message
news:E5002307-6989-43E7...@microsoft.com...

> 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?

Yes, but not natively:
http://www.aspose.com/categories/product-family-packs/aspose.cells-product-family/default.aspx

--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Andy O'Neill

unread,

Mar 19, 2010, 10:38:15 AM3/19/10

to

"DavidC" <dlc...@lifetimeinc.com> wrote in message
news:E5002307-6989-43E7...@microsoft.com...

>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?

Yes, but using a different technique.
You can do this with xslt.

DavidC

unread,

Mar 19, 2010, 3:06:02 PM3/19/10

to

"Andy O'Neill" wrote:

> .
>
Can you point me to where I can find out more about this? Thanks.

David

Andy O'Neill

unread,

Mar 21, 2010, 12:06:58 PM3/21/10

to

"DavidC" <dlc...@lifetimeinc.com> wrote in message

news:6FEDB03B-F7C7-4489...@microsoft.com...

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.