Urgent!!!!! Problem while exporting data to excel sheet in ap.net

G

Guest

Hi

I am exporting data from database to an excel sheet. Some of the data have
"+/-" .For example: "120+/-10"

When i connect to the local database and export the data, it does export
correctly say "120+/-10" . But when i connect to production database, it does
not export data correctly say "120 10" .

In the page_load event i have this code,

Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" &
Request("wcell") & ".xls")
Response.ContentType = "application/vnd.ms-excel"


Does any have idea of what is going wrong here.

Pls Help
Sileesh
 
G

Guest

Here is the code

Imports MISService
Imports System.Math
Public Class ExportPcnsToExcel
Inherits PageBase
Protected WithEvents message As System.Web.UI.WebControls.Label
Protected WithEvents pcnTbl As System.Web.UI.HtmlControls.HtmlTable
#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

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
Dim ts As New MISService.WorkOrderService()
'
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" &
Request("wcell") & ".xls")
Response.ContentType = "application/vnd.ms-excel"
'
If Request("wcell") = "MidStream" Then
ExportMidStreamPCNS(Request("wcell"), Request("selIdx"))
End If
'
End Sub

Private Sub ExportMidStreamPCNS(ByVal workcell As String, ByVal sGrp As
String)
Dim ts As New TycoMISService.WorkOrderService()
Dim shwoLst As ArrayList
Dim pcnitem As PCN
Dim errForServerCom As Boolean
'
errForServerCom = False
Try
If sGrp = "0" Then
shwoLst = ts.GetPCNListByWorkCell(workcell)
Else
shwoLst = ts.GetPCNListByWorkCellAndSGroup(workcell, sGrp)
End If
Catch
errForServerCom = True
End Try
'
If errForServerCom = True Then
dprintf("Error in comminication with MIS Server")
Exit Sub
End If
'
If (shwoLst.Count() = 0) Then
dprintf("No PCN found for " & workcell & " workcell.")
Exit Sub
End If
'
If workcell = "MidStream" Or Request("wcell") = "Midstream" Then
FillMidStreamHeaders()
For Each pcnitem In shwoLst
ExportMidStreamData(pcnitem)
Next
End If
'
End Sub

Private Sub FillMidStreamHeaders()
Dim NodeNameRow As New HtmlTableRow()
Dim ActTextRow As New HtmlTableRow()
Dim CActTextRow As New HtmlTableRow()
Dim i As Integer
'first row
For i = 1 To 5
addTextCellPCN(0, "gen", "", NodeNameRow, "")
Next
'
addTextCellPCN(0, "gen", "", NodeNameRow, "ProductLine")
addTextCellPCN(0, "gen", "", NodeNameRow, "Family")
addTextCellPCN(0, "gen", "", NodeNameRow, "Group")
addTextCellPCN(0, "gen", "", NodeNameRow, "Yield")
addTextCellPCN(0, "gen", "", NodeNameRow, "TCF")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PRR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBOORHI")
'add first row to table
pcnTbl.Controls.AddAt(0, NodeNameRow)
'second row
For i = 1 To 8
addTextCellPCN(0, "gen", "", ActTextRow, "")
Next
'
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Non-Resistance
Data", 1, 2)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post Punch MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post H/T MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post T/C MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post Resistance Sort
MPM", 1, 5)
'add second row to table
pcnTbl.Controls.AddAt(1, ActTextRow)
'third row
For i = 1 To 3
addTextCellPCN(0, "gen", "", CActTextRow, "")
Next
'
addTextCellPCN(0, "gen", "", CActTextRow, "RPN")
addTextCellPCN(0, "gen", "", CActTextRow, "Description")
addTextCellPCN(0, "gen", "", CActTextRow, "Family")
addTextCellPCN(0, "gen", "", CActTextRow, "Group")
addTextCellPCN(0, "gen", "", CActTextRow, "SubGrp")
addTextCellPCN(0, "gen", "", CActTextRow, "Yield")
addTextCellPCN(0, "gen", "", CActTextRow, "TCF")
'
For i = 1 To 4
addTextCellPCN(0, "gen", "", CActTextRow, "type")
addTextCellPCN(0, "gen", "", CActTextRow, "LSL")
addTextCellPCN(0, "gen", "", CActTextRow, "USL")
addTextCellPCN(0, "gen", "", CActTextRow, "LOOR")
addTextCellPCN(0, "gen", "", CActTextRow, "HOOR")
Next
'add third row
pcnTbl.Controls.AddAt(2, CActTextRow)
'
End Sub

Private Sub ExportMidStreamData(ByVal pcnitem As PCN)
Dim Ptr As New HtmlTableRow()
Dim idx As Integer
'
idx = pcnTbl.Rows.Count
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.WorkCell)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.PcnType)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.subGroup)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.PCNNumber)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.PcnDesc)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.ProductLine)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.Family)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.Group)
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("Yield"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("TCF"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PRR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBOORHI"))
'
pcnTbl.Controls.AddAt(idx, Ptr)
'
End Sub

Public Sub dprintf(ByVal msg As String)
If Len(message.Text) = 0 Then
message.Text = msg
Else
message.Text = message.Text & "<br>" & msg
End If
End Sub

Private Sub FillMidStreamHeaders()
Dim NodeNameRow As New HtmlTableRow()
Dim ActTextRow As New HtmlTableRow()
Dim CActTextRow As New HtmlTableRow()
Dim i As Integer
'first row
For i = 1 To 5
addTextCellPCN(0, "gen", "", NodeNameRow, "")
Next
'
addTextCellPCN(0, "gen", "", NodeNameRow, "ProductLine")
addTextCellPCN(0, "gen", "", NodeNameRow, "Family")
addTextCellPCN(0, "gen", "", NodeNameRow, "Group")
addTextCellPCN(0, "gen", "", NodeNameRow, "Yield")
addTextCellPCN(0, "gen", "", NodeNameRow, "TCF")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PPOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PHOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PTOORHI")
addTextCellPCN(0, "gen", "", NodeNameRow, "PRR")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBLCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBUCL")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBOORLO")
addTextCellPCN(0, "gen", "", NodeNameRow, "PBOORHI")
'add first row to table
pcnTbl.Controls.AddAt(0, NodeNameRow)
'second row
For i = 1 To 8
addTextCellPCN(0, "gen", "", ActTextRow, "")
Next
'
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Non-Resistance
Data", 1, 2)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post Punch MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post H/T MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post T/C MPM", 1, 5)
addTextCellCRSpanPCN(0, "gen", "", ActTextRow, "Post Resistance Sort
MPM", 1, 5)
'add second row to table
pcnTbl.Controls.AddAt(1, ActTextRow)
'third row
For i = 1 To 3
addTextCellPCN(0, "gen", "", CActTextRow, "")
Next
'
addTextCellPCN(0, "gen", "", CActTextRow, "RPN")
addTextCellPCN(0, "gen", "", CActTextRow, "Description")
addTextCellPCN(0, "gen", "", CActTextRow, "Family")
addTextCellPCN(0, "gen", "", CActTextRow, "Group")
addTextCellPCN(0, "gen", "", CActTextRow, "SubGrp")
addTextCellPCN(0, "gen", "", CActTextRow, "Yield")
addTextCellPCN(0, "gen", "", CActTextRow, "TCF")
'
For i = 1 To 4
addTextCellPCN(0, "gen", "", CActTextRow, "type")
addTextCellPCN(0, "gen", "", CActTextRow, "LSL")
addTextCellPCN(0, "gen", "", CActTextRow, "USL")
addTextCellPCN(0, "gen", "", CActTextRow, "LOOR")
addTextCellPCN(0, "gen", "", CActTextRow, "HOOR")
Next
'add third row
pcnTbl.Controls.AddAt(2, CActTextRow)
'
End Sub

Public Sub addTextCellPCN(ByVal idx As Integer, ByVal key As String,
ByVal href As String, ByRef Ptr As HtmlTableRow, ByVal txtStr As String)
Dim anch As New HtmlAnchor()
Dim Ptc1 As New HtmlTableCell()
Dim BgColor As String
'
If (href.Length <> 0) Then
anch.InnerText = txtStr
anch.HRef = href
Ptc1.Controls.Add(anch)
Else
Ptc1.InnerText = txtStr
End If
Ptc1.Align = "center"
Ptc1.BgColor = BgColor
Ptr.BgColor = BgColor
Ptr.Controls.Add(Ptc1)
End Sub

End Class



In the ExportMidStreamData procedure i am trying to display the data. if i
go and see the vaues of following at runtime it is showing me the "+/-" where
ever i have "+/-" in the database. After it exports data to excel, in excel
sheet it is not showing me "+/-"

addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PPOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PHOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PTOORHI"))
'
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PRR"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBLCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBUCL"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBOORLO"))
addTextCellPCN(0, "KEY", "", Ptr, pcnitem.GetSetPointData("PBOORHI"))

if i remove following 3 lines of code from Page_Load event then data is
displaying on browser and there it is displaying "+/-".

Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" &
Request("wcell") & ".xls")
Response.ContentType = "application/vnd.ms-excel"

Please Help me.
 
J

Jeff Dillon

Sheesh..I meant show enough code to just reproduce the problem.

You should be able to do that in 10 lines or less. Create a new page and/or
project if necessary.

Jeff
 
G

Guest

here is test.aspx file
----------------------

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="test.aspx.vb"
Inherits="TycoMIS.test"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>
test
</title>
<meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
<meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
<LINK href="resources/newClass.css" type="text/css" rel="STYLESHEET">
</HEAD>
<body class="pagebackground" leftMargin="1" topMargin="1" rightMargin="2">
<form id="Form2" method="post" runat="server">
<table id="TestTable" runat=server cellSpacing="0" cellPadding="0"
width="100%" border="0">
</table>
</form>
</body>
</HTML>

here is test.aspx.vb file
-------------------------

Public Class test
Inherits System.Web.UI.Page
Protected WithEvents TestTable As System.Web.UI.HtmlControls.HtmlTable
#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

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
Response.Buffer = True
Response.AddHeader("Content-Disposition",
"attachment;filename=test.xls")
Response.ContentType = "application/vnd.ms-excel"
'
Dim i As Integer
For i = 0 To 9
Dim Ptr As New HtmlTableRow()
addData(Ptr, "70+/-2")
TestTable.Controls.AddAt(i, Ptr)
Next
'
End Sub
'
Public Sub addData(ByRef Ptr As HtmlTableRow, ByVal txtStr As String)
Dim Ptc1 As New HtmlTableCell()
'
Ptc1.InnerText = txtStr
Ptr.Controls.Add(Ptc1)
'
End Sub
'
End Class


Now if we run this file it should open excel file and write "70+/-2" in 10
lines. but it is writing "702" in 10 lines.

if i comment following 3 lines in page_load events then it is displaying
"70+/-2" in 10 lines on browser.

Response.Buffer = True
Response.AddHeader("Content-Disposition",
"attachment;filename=test.xls")
Response.ContentType = "application/vnd.ms-excel"

please help me.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Urgent Pls!!!!! Problem while exporting data to excel sheet in asp 1
change the sheet name while exporting datagrid data to excel 0
Exporting to Excel directly from ObjectDataSource? 2
exporting gridview to excel issue 7
Exporting to Excel (xlsx files) 3
exporting to excel question 1
Problem with diacritic characters when exporting data to Excel 0
Exporting Datagrid's data to an Excel Sheet. 1

Members online

No members online now.
Total: 90 (members: 0, guests: 90)
Robots: 418

Forum statistics

Threads
473,768
Messages
2,569,574
Members
45,048
Latest member
verona

Latest Threads

Top