Hello,
I am trying to read the data from an excel worksheet in the aspx code-behind file.
Sometimes, the data read from the excel cell containing a formula is returned in the form on strange characters eg. The actual cell data is NEW YORK This value is populated in the excel cell using a formula (the formula uses an excel function "PROPER" and passes the value of another cell present on a different worksheet, to the "PROPER" function)
When Aspose.Excel reads the data, it return the value as 2G7Rnkc5v3.
The code used to read the cell data is shown below.
excapp = New Aspose.Excel.Excel
excapp.Open(strDocName)
Dim intWorkSheetIndex As Integer = excapp.Worksheets.Count - 1
Dim ds As New DataSet("Export")
ds.Tables.Add(excapp.Worksheets(intWorkSheetIndex).Cells.ExportDataTable(0, 1, 100, 1))
.....
strRetXML = strRetXML & "" & MakeXMLSafe(ds.Tables(0).Rows(38)(0)) & ""
...
Function MakeXMLSafe(ByVal sConvMsg)
sConvMsg = Replace(sConvMsg, "&", "&", 1, -1, 1)
sConvMsg = Replace(sConvMsg, "<", "<", 1, -1, 1)
sConvMsg = Replace(sConvMsg, ">", ">", 1, -1, 1)
sConvMsg = Replace(sConvMsg, "'", "'", 1, -1, 1)
sConvMsg = Replace(sConvMsg, """", """, 1, -1, 1)
MakeXMLSafe = sConvMsg
End Function
Before using the code shown above, we also tried to read the cell data using an alternate method,.ie by passing the column and row number and retreiving the "StringValue" of the cell.
The results were the same. THIS strange phenomenon does NOT HAPPEN at ALL TIMES. Its very sporadic.
Regards
Agnello D'Costa