C
Cordon
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
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