I am frustated...

Discussion in 'ASP .Net Datagrid Control' started by Cordon, Jul 11, 2005.

  1. Cordon

    Cordon Guest

    My problem is very simple... (All codes are in ASP.NET & VB.NET)

    There is a data grid like -

    Name Age Height
    KA 12 100.2300
    ZX 14 101.0500
    XY 9 98.1000


    When I export it to excel it gets like, -

    Name Age Height
    KA 12 100.23
    ZX 14 101.05
    XY 9 98.1

    I have following files in the project -
    DataGridExport.aspx
    DataGridExport.aspx.vb
    cmpDataGridToExcel.vb

    That is zero is removed by the excel. I have no idea how to fix this
    problem. Anyone pls help me. Here is the DataGridExport.aspx.vb Page
    code, -

    Imports System.Configuration

    Public Class DataGridExport
    Inherits System.Web.UI.Page
    Protected WithEvents SqlSelectCommand1 As
    System.Data.SqlClient.SqlCommand

    Protected WithEvents SqlConnection1 As
    System.Data.SqlClient.SqlConnection
    Protected WithEvents SqlDataAdapter1 As
    System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents dgToExport As
    System.Web.UI.WebControls.DataGrid
    Protected WithEvents btnExport As System.Web.UI.WebControls.Button
    Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
    Protected WithEvents DataSet1 As System.Data.DataSet

    #Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    InitializeComponent()
    Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
    Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
    Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
    Me.DataSet1 = New System.Data.DataSet
    CType(Me.DataSet1,
    System.ComponentModel.ISupportInitialize).BeginInit()
    '
    'SqlSelectCommand1
    '
    Me.SqlSelectCommand1.CommandText = "Select CustomerID,
    OrderDate, Freight From Orders " 'Where CustomerID = like(A*) "
    Me.SqlSelectCommand1.Connection = Me.SqlConnection1
    '
    'SqlConnection1
    '
    Me.SqlConnection1.ConnectionString = "workstation
    id=""LOGICON-SERVER1"";packet size=4096;user id=sa;data
    source=""LOGICON" & _
    "-SERVER1"";persist security info=False;initial
    catalog=Northwind"
    '
    'SqlDataAdapter1
    '
    Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
    Me.SqlDataAdapter1.TableMappings.AddRange(New
    System.Data.Common.DataTableMapping() {New
    System.Data.Common.DataTableMapping("Table", "Orders", New
    System.Data.Common.DataColumnMapping() {New
    System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New
    System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"), New
    System.Data.Common.DataColumnMapping("Freight", "Freight")})})
    '
    'DataSet1
    '
    Me.DataSet1.DataSetName = "NewDataSet"
    Me.DataSet1.Locale = New
    System.Globalization.CultureInfo("en-US")
    CType(Me.DataSet1,
    System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub

    #End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here

    BindGrid()

    End Sub

    Sub BindGrid()
    ' Fill our dataset
    SqlDataAdapter1.Fill(DataSet1)
    ' Assign the dataset to our Datagrid called dgToExport
    dgToExport.DataSource = DataSet1
    ' Finally bind the datagrid
    dgToExport.DataBind()
    End Sub

    Function ReturnName(ByVal strLastName, ByVal strFirstName)
    ' This is the function I'm calling in the aspx page to show the
    difference
    ' between exporting a dataset versus exporting a datagrid. This
    function is
    ' simply going to combine the first and last names and and
    return the
    ' full name to the datagrid template column for "Name".
    Dim strReturn As String
    strReturn = strFirstName & " " & strLastName
    Return strReturn
    End Function

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles btnExport.Click
    ' One line handles all of the export. We're simply calling the
    component (cmpDataGridToExcel),
    ' then we're using it's only method (DataGridToExcel), and
    we're passing our DataGrid (dgToExport)
    ' and the value reponse. Note: If you're using VS.Net, once you
    build your solution after creating
    ' the component, Intellisense will now include your component.
    Just remember you have to build it first.
    '
    ' You could also modify your datagrid here before exporting it.
    For instance in my invoice example
    ' we had a checkbox in our datagrid. If you have one of those
    the export will generate an error so
    ' we simply removed the column first like this:
    ' dgToExport.Columns.Remove(dgToExport.Columns.Item(11))

    cmpDataGridToExcel.DataGridToExcel(dgToExport, Response)
    End Sub
    End Class

    ======================================================

    and here is cmpDataGridToExcel.vb

    Public Class cmpDataGridToExcel
    Inherits System.ComponentModel.Component

    #Region " Component Designer generated code "

    Public Sub New(ByVal Container As System.ComponentModel.IContainer)
    MyClass.New()

    'Required for Windows.Forms Class Composition Designer support
    Container.Add(Me)
    End Sub

    Public Sub New()
    MyBase.New()

    'This call is required by the Component Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call

    End Sub

    'Component overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As
    Boolean)
    If disposing Then
    If Not (components Is Nothing) Then
    components.Dispose()
    End If
    End If
    MyBase.Dispose(disposing)
    End Sub

    'Required by the Component Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Component
    Designer
    'It can be modified using the Component Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    InitializeComponent()
    components = New System.ComponentModel.Container
    End Sub

    #End Region
    Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal
    response As HttpResponse)
    'clean up the response.object
    response.Clear()
    response.Charset = ""

    'set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel"

    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

    'instantiate a datagrid
    Dim dg As New DataGrid

    ' just set the input datagrid = to the new dg grid
    dg = dgExport

    ' I want to make sure there are no annoying gridlines
    dg.GridLines = GridLines.None

    ' Make the header text bold
    dg.HeaderStyle.Font.Bold = True

    ' If needed, here's how to change colors/formatting at the
    component level
    'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black
    'dg.ItemStyle.ForeColor = System.Drawing.Color.Black

    'bind the modified datagrid
    dg.DataBind()
    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    'output the html
    response.Write(stringWrite.ToString)
    response.End()
    End Sub
    End Class

    ===============================

    Pls help me
     
    Cordon, Jul 11, 2005
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page