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 -

    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

    Protected WithEvents SqlConnection1 As
    Protected WithEvents SqlDataAdapter1 As
    Protected WithEvents dgToExport As
    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
    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
    Me.SqlSelectCommand1.CommandText = "Select CustomerID,
    OrderDate, Freight From Orders " 'Where CustomerID = like(A*) "
    Me.SqlSelectCommand1.Connection = Me.SqlConnection1
    Me.SqlConnection1.ConnectionString = "workstation
    id=""LOGICON-SERVER1"";packet size=4096;user id=sa;data
    source=""LOGICON" & _
    "-SERVER1"";persist security info=False;initial
    Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
    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")})})
    Me.DataSet1.DataSetName = "NewDataSet"
    Me.DataSet1.Locale = New

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


    End Sub

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

    Function ReturnName(ByVal strLastName, ByVal strFirstName)
    ' This is the function I'm calling in the aspx page to show the
    ' 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)

    'Required for Windows.Forms Class Composition Designer support
    End Sub

    Public Sub New()

    'This call is required by the Component Designer.

    '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
    If disposing Then
    If Not (components Is Nothing) Then
    End If
    End If
    End Sub

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

    'NOTE: The following procedure is required by the Component
    'It can be modified using the Component Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    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.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
    'tell the datagrid to render itself to our htmltextwriter
    'output the html
    End Sub
    End Class


    Pls help me
    Cordon, Jul 11, 2005
    1. Advertisements

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.