Aspose Formula not working

Aspose.Cells Product Family
1

For some reason I’m try to do this:

Cell cell = worksheet.Cells[row, i];
 if (cell.Value != null)
{
          cell.Formula = cell.Value.ToString();
}

When the cell.value is below it works fine:
=HYPERLINK("\server\share\test.pdf"|"\server\share\test.pdf")
However even though this is the correct syntax for cell.value it doesn’t work:
=HYPERLINK((ENCODEURL("\server\share\test.pdf")|"\server\share\test.pdf")

It comes back with error: Invalid ‘"’(Based on cell Data!M2)

Not sure if it’s because of c# string escaped characters.

2

@tkurian can you please post the problematic document. Also, please notice that you have an extra parenthesis in the expression:
=HYPERLINK( ( ENCODEURL("\server\share\test.pdf")|"\server\share\test.pdf")

4

I think that was a typo when I typed it in. All the documents work when I use just Hyperlink (except the ones with # in them that’s why I’m adding encodeURL). But I’m getting a runtime exception when I add the EncodeURL.

5

@tkurian,

It seems ‘|’ is the separator of parameters for formula for your region. If so, you need some special work to get the expected result.

Commonly we only support to set the standard formula expressions for cell. Same with regional settings of en_US, the separator of parameters should be ‘,’.

If you do need to use the special formula expression of specific region, you may try the LocaleDependent option. Code example:

cell.SetFormula("=HYPERLINK(ENCODEURL(\"\\server\\share\\test.pdf\")|\"\\server\\share\\test.pdf\")",
new FormulaParseOptions() { LocaleDependent = true }, null) ;

And please make sure the list separator of workbook’s regional setting is ‘|’. If not, please specify the correct regional settings for the workbook or your default cultureinfo to make sure the separator is ‘|’.

Or, you may implement your own GlobalizationSettings to specify the separator as ‘|’. Code example:

        class MG : GlobalizationSettings
        {
            public override char ListSeparator
            {
                get { return '|'; }
            }
        }
        workbook.Settings.GlobalizationSettings = new MG();

And currently we do not support ENCODEURL function, so in the generated file this function maybe cannot work correctly when opening it with ms excel. If you need, we may create one ticket for your requirement and try to support it.

6

Yes, please open a ticket for EncodeURL.

7

@tkurian
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-52860
8

@tkurian,

We have supported to set/read/save ENCODEURL function now. Please try this update . The calculation of this function has not been supported yet. We will update you here when it is supported in later versions.

9

@tkurian,

We are pleased to inform you that your issue has been resolved. The fix will be included in an upcoming release (Aspose.Cells v23.4) that we plan to release in the first half of April 2023. You will be notified when the next version is released.

10

The issues you have found earlier (filed as CELLSNET-52860) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi