• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4494
  • Last Modified:

How To Export Data in a DataGrid on an ASP . NET WebForm to Microsoft Excel

How To Export Data in a DataGrid on an ASP . NET WebForm to Microsoft Excel

i did google and i did get lots of sample pages and all are not completed or its not my requirement

what i want is export all the datagrid data into excel and in my datagrid i have
allow paging=true
and also allow sorting = true

is anybody can help me

i already wasted my 5 hours finding out this silly exporting business and finally i gave up and hope i will resolve very soon here

is anybody here willing to work with me for few minutes if you are expert?

thanks
0
allday
Asked:
allday
  • 11
  • 4
  • 3
  • +3
1 Solution
 
raterusCommented:
Here is my home built export a datagrid code, It's not perfect, but will get the values out of your simple columns, like a bound column.  Take it or leave it :-)

usage:
ExportAsResponse(myDataGrid, "csv")
or
ExportAsResponse(myDataGrid, "html")

        Public Sub ExportAsResponse(ByVal dg as DataGrid, ByVal exportType As String, Optional ByVal Filename As String = "export.csv")
            Dim exportVal As String = String.Empty, contentType As String
            Dim Response As System.Web.HttpResponse = dg.Page.Response

            exportType = exportType.ToLower

            Select Case exportType
                Case "csv"
                    exportVal = Export(dg, exportType)
                    contentType = "text/csv"

                Case "html"
                    exportVal = Export(dg, exportType)
                    contentType = "application/ms-excel"
                Case Else

            End Select

            Response.Clear()
            Response.ClearHeaders()
            Response.ClearContent()
            Response.ContentType = contentType
            Response.AppendHeader("Content-disposition", "attachment; filename=" & Filename)
            Response.Write(exportVal)
            Response.End()
        End Sub

        Public Function Export(ByVal dg as DataGrid, ByVal exportType As String) As String
            Dim retVal As String, i As Integer = 0
            exportType = exportType.ToLower

            Dim dt As DataTable = New DataTable

            'get Data in a datatable first
            For Each dgc As DataGridColumn In dg.Columns
                If dgc.Visible = True Then
                    Dim dc As DataColumn = New DataColumn(dgc.HeaderText)
                    dt.Columns.Add(dc)
                End If
            Next


            For Each dgi As DataGridItem In Me.Items
                Dim dr As DataRow = dt.NewRow
                For i = 0 To dgi.Cells.Count - 1
                    If Me.Columns(i).Visible = True Then
                        dr(i) = dgi.Cells(i).Text
                    End If
                Next

                dt.Rows.Add(dr)
            Next

            Select Case exportType
                Case "csv"
                    retVal = GetCSVExport(dt)
                Case "html"
                    retVal = GetHTMLExport(dt)
            End Select

            Return retVal
        End Function

        Private Function GetCSVExport(ByVal dt As DataTable) As String
            Dim comma As String = ""
            Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder(1000)

            For Each c As DataColumn In dt.Columns
                sb.Append(comma & """" & c.Caption.ToLower & """")
                comma = ","
            Next

            sb.Append(vbCrLf)

            For Each dr As DataRow In dt.Rows
                comma = ""
                For i As Integer = 0 To dt.Columns.Count - 1
                    Dim var As String = CStr(dr(i)).Replace(Chr(34), """")

                    If var.IndexOf(","c) >= 0 OrElse var.IndexOf(vbCrLf) >= 0 Then
                        var = """" & var & """"
                    End If

                    sb.Append(comma & var)

                    comma = ","
                Next

                sb.Append(vbCrLf)
            Next

            Return sb.ToString
        End Function

        Private Function GetHTMLExport(ByVal dt As DataTable) As String
            Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder(1000)

            Dim firstpass As Boolean = True
            sb.Append("<html><table><tr><td>")

            For Each c As DataColumn In dt.Columns
                If firstpass <> True Then
                    sb.Append("</td><td>")
                End If

                sb.Append(c.Caption)
                firstpass = False
            Next
            sb.Append("</td></tr>" & vbCrLf & "<tr><td>" & vbCrLf)

            For Each dr As DataRow In dt.Rows
                firstpass = True
                For i As Integer = 0 To dr.Table.Columns.Count - 1
                    If firstpass <> True Then
                        sb.Append("</td><td>")
                    End If
                    sb.Append(CStr(dr(i)))
                    firstpass = False
                Next
                sb.Append("</td></tr>" & vbCrLf & "<tr><td>")
            Next
            sb.Append("</td></tr></table></html>" & vbCrLf)

            Return sb.ToString
        End Function
0
 
alldayAuthor Commented:
thank you but im looking for export to excel?
0
 
alldayAuthor Commented:
i have my dataset and my datagrid is populating but the only thing i want is to populate into excel

how can i do that?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sriggummaCommented:
allday ,

Here is useful link:
http://www.codeproject.com/aspnet/ExportClassLibrary.asp
(exporting data to CSV/Excel file)

Hope this helps.
0
 
alldayAuthor Commented:
sriggumma:

what is this

Imports RKLib.ExportData ?

do i have to carry this dll with my project... is there any other simple way doing rather then having thirdparty dll... my boss will not trust using somebody dll with my project.

0
 
alldayAuthor Commented:
i saw the source code is for dll rklib but its not in vb

im looking in asp.net(vb)
0
 
tovvenkiCommented:
Hi,

check whether this article helps you
http://www.dotnetjohn.com/articles.aspx?articleid=78

I am sorry if you have already seen it and it is not help ful.

Regards,
venki
0
 
raterusCommented:
the code I posted will export to excel, at least excel will open it up.  If you truly want an .xls file, you are going to need a third party component.
0
 
Ramesh SrinivasTechnical ConsultantCommented:
You can programmatically export to excel and customise your data until your heart is content....

Some code to get you started:

Import Excel reference to your project then...


Dim oXL As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

'create app    
oXL = CreateObject("Excel.Application")

'creat workbook
oBook = oXL.Workbooks.Add()

Set oBook = oXL.ActiveWorkbook

'selecting sheets/renaming sheets
oBook.Worksheets("Sheet1").Select
Set oSheet = oBook.Worksheets("Sheet1")
'rename
oSheet.Name = "MySheet"

'adding more than the defualt 3 sheets
Set oSheet = oBook.Worksheets.Add(, oBook.Worksheets(oBook.Worksheets.Count))
'to name sheet
oSheet.Name = "MyFourthSheet"


*************loop thru your dataset/datatable ***********************
Dim i
Dim dr as datarow

For Each dr in YourDataset.Tables(0).Rows
   'To write to sheet
   'row 1 column 1
   oSheet.Cells(i,1) = dr("Data")
   'row 1 column 2
   oSheet.Cells(i,2) = dr("Data")
   i += 1
Next

'saving...
Dim fPath = "Yourpath"
Dim fileName = fPath & "\" & myFileName ".xls"
oSheet.SaveAs(fileName)

oXL.Workbooks.Close()
oXL.Quit()
oXL = Nothing
0
 
sriggummaCommented:
Immediately I can't provide the VB code. But you may use this link to convert the c# code to VB and try.

http://www.kamalpatel.net/ConvertCSharp2VB.aspx


Hope this helps.
0
 
alldayAuthor Commented:
raturs:

this is what im getting in excel after i click twice to the dialog box for open:

<html><table><tr><td>Cont. ID</td><td>Sub ID</td><td>Plan Name</td><td>Cont. Status</td><td>Date of Move</td><td>Moved By</td><td>QC Analyst</td><td>QC Date</td></tr>
<tr><td>
</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<tr><td></td></tr></table></html>
0
 
alldayAuthor Commented:
i called

ExportAsResponse(myDataGrid, "html")
0
 
raterusCommented:
What does the columns collection of your datagrid look like?  Like I said earlier, this code just picks up on the simple columns, like boundcolumns.  If you have more complicated columns, you will have to adapt the code.
0
 
alldayAuthor Commented:
i can not believe that its so hard to get done the export

none of the posts help me and i don't really have time to do research...

im using databound column and is anybody there to take me to the point i want ?

appreciate!
0
 
Ramesh SrinivasTechnical ConsultantCommented:
You have a few solutions here, you should attmept to implement some of them.
0
 
alldayAuthor Commented:
saleek, i tried all solutions except one which is yours

can you tell me what is the Import Excel reference will be ?

im trying your solution now...
0
 
Ramesh SrinivasTechnical ConsultantCommented:
The right hand side of Vsiaul Studio interface is your Solution Explorer.

In there Right click the "References" and select "Add Reference".

From the "COM" tab list, look for "Microsoft Excel" (it will have a version number next to it - make sure its the highest one).

Select it, and click okay to add it to your project.

That is it.

regards,

KS
0
 
alldayAuthor Commented:
saleek:

getting this error:

Cannot create ActiveX component.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Cannot create ActiveX component.

0
 
alldayAuthor Commented:
if i use : (NEW) then i get the below error

 Dim oXL As New Excel.Application


Access is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Access is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.

Source Error:


Line 232:    Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
Line 233:
Line 234:        Dim oXL As New Excel.Application
Line 235:        Dim oBook As Excel.Workbook
Line 236:        Dim oSheet As Excel.Worksheet
 
0
 
Ramesh SrinivasTechnical ConsultantCommented:
"Cannot create ActiveX component."

You must have excel installed on the machine. Make sure you have also imported the reference.
0
 
alldayAuthor Commented:
of course, i do have excel and i did import the reference.
0
 
nisarkhanCommented:
this code can be run outside of page, or can be converted to run in and extended datagrid control

imports System.Drawing
Imports System.Web.UI
Imports System.Web.UI.WebControls


Namespace demetz


    Public Class DemExportGridExcel

        Sub RenderGridToExcelFormat(ByVal grid As DataGrid, ByVal saveAsFile As String)
            ' check Excel rows limit
            If grid.Items.Count.ToString + 1 < 65536 Then
                HttpContext.Current.Response.Clear()
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & saveAsFile & ".xls")
                ' Remove the charset from the Content-Type header.
                HttpContext.Current.Response.Charset = ""
                'HttpContext.Current.Response.WriteFile("style.txt")
                ' Turn off the view state.
                grid.EnableViewState = False
                Dim tw As New System.IO.StringWriter()
                Dim hw As New System.Web.UI.HtmlTextWriter(tw)
                ' Get the HTML for the control.
                grid.HeaderStyle.ForeColor = Color.Black
                grid.HeaderStyle.BackColor = Color.Red
                grid.ItemStyle.ForeColor = Color.Black
                grid.BorderColor = Color.White
                ClearControls(grid)
                grid.RenderControl(hw)
                ' Write the HTML back to the browser.
                HttpContext.Current.Response.Write(tw.ToString())
                ' End the response.
                HttpContext.Current.Response.End()
            Else
               
                HttpContext.Current.Response.Write("Too many rows - Export to Excel not possible")
            End If
        End Sub

        Sub ClearControls(ByVal control As Control)
            Dim i As Integer
            For i = control.Controls.Count - 1 To 0 Step -1
                ClearControls(control.Controls(i))
            Next i

            If TypeOf control Is System.Web.UI.WebControls.Image Then
                control.Parent.Controls.Remove(control)
            End If

            If (Not TypeOf control Is TableCell) Then
                If Not (control.GetType().GetProperty("SelectedItem") Is Nothing) Then
                    Dim literal As New LiteralControl()
                    control.Parent.Controls.Add(literal)
                    Try
                        literal.Text = CStr(control.GetType().GetProperty("SelectedItem").GetValue(control, Nothing))
                    Catch
                    End Try
                    control.Parent.Controls.Remove(control)
                Else
                    If Not (control.GetType().GetProperty("Text") Is Nothing) Then
                        Dim literal As New LiteralControl()
                        control.Parent.Controls.Add(literal)
                        literal.Text = CStr(control.GetType().GetProperty("Text").GetValue(control, Nothing))
                        control.Parent.Controls.Remove(control)
                    End If
                End If
            End If
            Return
        End Sub 'ClearControls

    End Class

End Namespace


0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now