Wrox Programmer Forums
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 2nd, 2008, 01:55 PM
Authorized User
 
Join Date: Jul 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Export to Excel

Hi Everyone

I m working on asp.net 2.0 I need to export the sql data to excel using asp.net .I have excel viewer on my server(where is My .net application resides) I checked on many sites they give export data from grid to excel which I dont want.I need to get the data from sql server and export them to excel.


Thank you
Lathika

.NET Programmer
__________________
.NET Programmer
 
Old July 4th, 2008, 12:53 AM
Authorized User
 
Join Date: Apr 2008
Posts: 54
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Use the content-disposition to set the response to be an attachment. and then change the content type to appropriate as follows

Response.AppendHeader("content-disposition", "attachment; filename=Export.xls")
Response.ContentType = "application/vnd.ms-excel"

alternatively you can visit this page to find out more on excel exporting.

http://www.vbknowledgebase.com/WebAp...adsheet-Export

Pon Saravanan
http://www.vbknowledgebase.com/WebAp...ew-walkthrough
 
Old July 14th, 2008, 10:49 AM
Authorized User
 
Join Date: Jul 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks

But the link you gave doesnt show how to export to excel.Moreover I am not using grid. I have to export directly from sql server to excel file.

.NET Programmer
 
Old July 15th, 2008, 09:00 AM
Authorized User
 
Join Date: Apr 2008
Posts: 54
Thanks: 0
Thanked 4 Times in 4 Posts
Default

this is not only exports the data in gridview but it exports the entire page to excel.

You can use the SqlServer Reporting services to generate the excel. But i am not familiar with it.

the other old way is to use the Excel.Application in the WebServer. Which has its own difficulties in deployment and runtime But for your requirement it may fit. I have a VB sample (windows forms application in VB6) . you can understand how you can use the Excel.Application, workbook, and worksheet to export data.

http://www.vbknowledgebase.com/WebAp...Excel-and-Save

Pon Saravanan
http://www.vbknowledgebase.com
 
Old July 21st, 2008, 03:53 PM
Authorized User
 
Join Date: Jul 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you so much!
I am able to export the excel in local system But I m having trouble on server I am using Microsoft office dll in my application .
Do I have to register these dll on server to run the application.?

.NET Programmer
 
Old July 21st, 2008, 08:03 PM
Authorized User
 
Join Date: Apr 2008
Posts: 54
Thanks: 0
Thanked 4 Times in 4 Posts
Default

hi lathika
thats what i have highlighted in my last post.
yes you have to deploy the dlls in the server and you need to configure the dcom configurations.
you need to do give some permissions also.

for detailed information read this

http://www.vbknowledgebase.com/WebAp...-Excel-library



Pon Saravanan
http://www.vbknowledgebase.com
 
Old July 24th, 2008, 05:43 AM
Registered User
 
Join Date: Feb 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

all you have to do is get your data into a datatable and then use this sub:

Public Sub exportDataTable2Excel(ByVal dt As DataTable, ByVal fileName As String)
        Dim strHtml, strCells As String

        Response.Clear()

        Response.AddHeader("content-disposition", "attachment;filename=" & fileName & ".xls")

        Response.Charset = ""

        ' If you want the option to open the Excel file without saving than
        ' // comment out the line below

        ' // Response.Cache.SetCacheability(HttpCacheability.No Cache);
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("Content-Disposition", "attachment;filename=ReportExport.xls")
        Response.Write("<!DOCTYPE html PUBLIC " & Chr(34) & "-//W3C//DTD XHTML 1.0 Transitional//EN" & Chr(34) & " " & Chr(34) & _
             "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" & Chr(34) & ">" & vbCrLf & _
             "<html xmlns=" & Chr(34) & "http://www.w3.org/1999/xhtml" & Chr(34) & ">" & vbCrLf & _
             "<head>" & vbCrLf & _
             "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" & vbCrLf & _
             "</head><body>")

        Response.ContentEncoding = System.Text.Encoding.UTF8
        Dim stringWrite As New System.IO.StringWriter

        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

        ' = System.Text.Encoding.UTF8
        htmlWrite.WriteLine("<table cellspacing='0' rules='all' border='1' dir ='rtl'>")

        Dim i, colCount As Integer
        strHtml = "<tr>"


        colCount = dt.Columns.Count
        For i = 0 To colCount - 1
            strHtml &= "<th>" & dt.Columns(i).Caption & "</th>"
        Next

        strHtml &= "</tr>"
        htmlWrite.WriteLine(strHtml)
        Dim dr As DataRow
        For Each dr In dt.Rows
            strHtml = "<tr>"
            strCells = ""

            For i = 0 To colCount - 1

                strCells &= "<td>" & dr(i).ToString & "</td>"
            Next


            strHtml &= strCells & "</tr>"
            htmlWrite.WriteLine(strHtml)
        Next

        strHtml = "</table>"
        htmlWrite.WriteLine(strHtml)
        Response.Write(stringWrite.ToString())

        Response.End()
    End Sub






Similar Threads
Thread Thread Starter Forum Replies Last Post
Export to excel moonbreeze Reporting Services 1 November 20th, 2006 08:42 PM
export to excel roy_mm Reporting Services 0 September 2nd, 2006 11:53 PM
Export to Excel using C# lily611 Crystal Reports 2 July 23rd, 2006 10:23 AM
excel export msrnivas Classic ASP Components 0 June 9th, 2004 10:15 PM
excel export msrnivas .NET Web Services 1 March 29th, 2004 03:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.