exporting datagrid to excel in vb.net
-
I want to display data in a datagrid and then want to export that data to excel in vb.net.plz help me ASAP. thnx josh
-
I want to display data in a datagrid and then want to export that data to excel in vb.net.plz help me ASAP. thnx josh
<%@ Import Namespace="System.Data.OleDb" %> <%@ Import Namespace="System.Data" %> Dim MyConnection As OleDbConnection Public Sub Page_Load(ByVal Sender As System.Object, ByVal E As System.EventArgs) Handles MyBase.Load MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & Server.MapPath("database/utenti.mdb")) BindGrid("id") End Sub Sub BindGrid(ByVal SortField As String) Dim DS As DataSet Dim MyCommand As OleDbDataAdapter MyCommand = New OleDbDataAdapter("select * from utenti", MyConnection) DS = New DataSet MyCommand.Fill(DS, "utenti") Dim Source As DataView = DS.Tables("utenti").DefaultView Source.Sort = SortField MyDataGrid.DataSource = Source MyDataGrid.DataBind() End Sub Sub ToExcel(sender As Object, e As System.EventArgs) Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False Dim tw As New System.IO.StringWriter Dim hw As New System.Web.UI.HtmlTextWriter(tw) MyDataGrid.RenderControl(hw) Response.Write(tw.ToString()) Response.End() End Sub_____________________ Proud to be Albanian _____________________ -
<%@ Import Namespace="System.Data.OleDb" %> <%@ Import Namespace="System.Data" %> Dim MyConnection As OleDbConnection Public Sub Page_Load(ByVal Sender As System.Object, ByVal E As System.EventArgs) Handles MyBase.Load MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & Server.MapPath("database/utenti.mdb")) BindGrid("id") End Sub Sub BindGrid(ByVal SortField As String) Dim DS As DataSet Dim MyCommand As OleDbDataAdapter MyCommand = New OleDbDataAdapter("select * from utenti", MyConnection) DS = New DataSet MyCommand.Fill(DS, "utenti") Dim Source As DataView = DS.Tables("utenti").DefaultView Source.Sort = SortField MyDataGrid.DataSource = Source MyDataGrid.DataBind() End Sub Sub ToExcel(sender As Object, e As System.EventArgs) Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False Dim tw As New System.IO.StringWriter Dim hw As New System.Web.UI.HtmlTextWriter(tw) MyDataGrid.RenderControl(hw) Response.Write(tw.ToString()) Response.End() End Sub_____________________ Proud to be Albanian _____________________ -
I subclassed a datagrid and use this code: Dim x As Integer, y As Integer Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim oExcel As Excel.Application Static SheetCount As Integer = 1 Dim tbl As DataTable = CType(Me.DataSource, DataTable) Cursor.Current = Cursors.WaitCursor If Not Me.ReadOnly Then Dim changedRows As New ArrayList Dim row As DataRow For Each row In tbl.Rows If row.RowState <> DataRowState.Unchanged Then changedRows.Add(row) End If Next If changedRows.Count > 0 Then Cursor.Current = Cursors.Default MsgBox("There are pending data updates for this grid. Please save these updates to the database prior to exporting the data to Excel.", MsgBoxStyle.Information, "Pending Updates") Exit Sub End If End If If oExcel Is Nothing Then oExcel = New Excel.Application End If wb = oExcel.Workbooks.Add ws = wb.Sheets.Add ws.Name = "Export" & SheetCount SheetCount = SheetCount + 1 'write column headers For x = 0 To tbl.Columns.Count - 1 ws.Range(Chr(65 + x) & "1").Value = tbl.Columns(x).ColumnName ws.Range(Chr(65 + x) & "1").Font.Bold = True Next 'write data For x = 0 To tbl.Rows.Count - 1 For y = 0 To tbl.Columns.Count - 1 ws.Range(Chr(65 + y) & (x + 2).ToString).Value = tbl.Rows(x).Item(y) Next Next oExcel.Application.Visible = True ' Release Application object and allow Excel to be closed by user: If Not oExcel.UserControl Then oExcel.UserControl = True System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) oExcel = Nothing Cursor.Current = Cursors.Default As you can see there are several references to "Me" in this routine for my use. In your case you would simply replace "Me" with the name of your datagrid. You will also need to add a reference to Excel in your project. Hope this works for you. Dean