Exported Data Not Exported Correctly

[johnsonlim026]

Hi,

I am exporting data from gridview to excel file.
My problem is that for all the string which contain "+", the rest of data begining from it is missing in excel file.

For example: PWE-WER+78, when exported it will become PWE-WER.

Below is my code.

Response.ClearContent()  
Response.Buffer = True  
Response.AddHeader("Content-Disposition", "attachment;filename=" & "ExportedExcel.xls")  
Response.ContentType = "application/vnd.ms-excel"  
Response.Charset = ""  
 
lobjStringWriter = New StringWriter  
lobjHtmlWriter = New HtmlTextWriter(lobjStringWriter)  
dgdResult.RenderControl(lobjHtmlWriter)  
Response.Write(lobjStringWriter.ToString())  
Response.End()  
Response.ClearContent()
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" & "ExportedExcel.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""                
lobjStringWriter = New StringWriter
lobjHtmlWriter = New HtmlTextWriter(lobjStringWriter)
dgdResult.RenderControl(lobjHtmlWriter)
Response.Write(lobjStringWriter.ToString())
Response.End()

But i manage to find out the root cause of this problem.

The scenario is as below:


I have two column of data to be exported.

Data1 Data2
------- -----
PWE-WER+123 UF+20-20%
PWE-WER+456 UF+20-20%
PWE-WER+789 UF+20-20%


When data is exported to excel, the result is as below

Data1 Data2
------- -----
PWE-WER UF+20%
PWE-WER UF+20%
PWE-WER UF+20%


Both data are exported wrongly.


I find out that when only Data1 column is exported only, the data is exported correct.
Moreover, I find out that both data are displayed wrongly in Excel is caused by the content in Column Data2 , that is UF+20-20%.

I have changed data in Column Data2 to
UF-20+20%
UF+20
UF-20%
F+20
F-20%
F+20%


and it all work!

It fail when the data in Column Data2 is

F+20-20%
+20-20%
-20+20%

I find out that unless i have " ' " at the beginning and ending of the data, both the data is not displayed correctly.

The data inside column Data2 is dynamic and entered by user.


Does anyone has idea on this?
Please help !!

[dglienna]

Doesn't sound like it's a TEXT column, which is why, probably. Also, might be the formatting used.

[johnsonlim026]

Hi,
I have try to export it to TEXT column in excel file. But it consists of character the column cannot be exported to text column.My code is as below.

 Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"



 For intTemp As Integer = 1 To ldstCompareResult.Tables(0).Rows.Count - 1
     dgdResult.Items(intTemp).Cells(0).Attributes.Add("class", "text")
 Next

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

 Dim style As String
 style = "<style> .text { mso-number-format:\@; } </style> "
 Response.Write(style)

Does anyone has idea on this?