Free Support Forum - aspose.com

Aspose.Excel issue (while reading data from excel cell which has formula)

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

Do you have a license? It seems a license limitation. Please check http://www.aspose.com/wiki/default.aspx/Aspose.Cells/Licensing.html .

Yes we do, I have made changes in the code to use the license file. Please see below,

Dim excapp As Aspose.Excel.Excel

Dim license As License = New License

license.SetLicense("Aspose.Excel.lic.xml")

Regards

Agnello

Please check if there is a license warning worksheet in your Excel file after your call Excel.Save method.

If yes, I think the license file isn't put on the correct path. Please call license with explicit path, for example:

license.SetLicense("d:\lic\Aspose.Excel.lic.xml")

If no, please zip and send your sample project to nanjing@aspose.com. I will check it ASAP.