Formula escaping incorrectly for hyperlinks

Aspose.Cells Product Family
1

Hi,

I am making a excel spreadsheet which needs links to particular worksheets in other excel spreadsheets.
With the standard hyperlink functionality I can open the external file but not the worksheet I need so this is no good unfortunately.
I saw a solution though that you can use a formula to specify an external link but unfortunately Aspose doesnt cope with the escaping of quotes properly.
i.e.
I set the formula content like say this:
cell.Formula = “=HYPERLINK(”[C:\myFiles\myOtherExcel.xls]‘Table 1’!A20";“Linked Excel File”)“
but the link in the saved Excel file is not navigable (is broken) and the formula is written like this:
=HYPERLINK(”[C:\myFiles\myOtherExcel.xls]‘Table 1’!A20"";"“Linked Excel File”)
Where the “;” in the center is transformed to “”;""
Strangely the first and last escaped quote are fine.
If you change the double quote to simgle quote in the excel file then the link works perfectly.

I’m wondering if there is a trick or something that I am missing or is this a bug?

2

Hi Daire,

Thanks for your posting and using Aspose.Cells.

We need to investigate your issue. Could you provide xls/xlsx file containing your formula so that we could look into this issue precisely?

Please also download and try the latest version: Aspose.Cells
for .NET v7.7.1.4 and see if it makes any difference.

3

Hi,

Using the new dll does not help unfortunately.

I put together a sample class which creates one excel file with some sheets and another then which has a hyperlink defined in a formula which links to the second of the sheets.

public static void Main(string[] args)
{
License licence = new License();
licence.SetLicense(“Aspose.Total.lic”);

CreateTargetExcelFile();
CreateExcelFileWithHyperLink();
}
private static void CreateTargetExcelFile()
{
Workbook myTarget = new Workbook();
myTarget.Worksheets[0].Name = “SheetA”;
myTarget.Worksheets.Add(“SheetB”);
myTarget.Worksheets.Add(“SheetC”);

string baseDir = GetBaseDir();
myTarget.Save(Path.Combine(baseDir, “targetFile.xls”));
}

private static void CreateExcelFileWithHyperLink()
{
string baseDir = GetBaseDir();
Workbook excelFileWithLink = new Workbook();
excelFileWithLink.Worksheets[0].Name = “Sheet1”;

Cell fileNameCell = excelFileWithLink.Worksheets[0].Cells[0, 0];

string hyperlinkTargetString = string.Format("[{0}]’{1}’!A1", Path.Combine(baseDir, “targetFile.xls”), “SheetB”);
string hyperlinkFormulaString = string.Format("=HYPERLINK("{0}";"{1}")", hyperlinkTargetString, “targetFile.xls - Sheet B”);
fileNameCell.Formula = hyperlinkFormulaString;
excelFileWithLink.Save(Path.Combine(baseDir, “fileWithLink.xls”));
}

private static string GetBaseDir()
{
DirectoryInfo debugDir = Directory.GetParent(Environment.CurrentDirectory);
DirectoryInfo baseDir = Directory.GetParent(debugDir.ToString());
return baseDir.ToString();
}

This will create a file called “fileWithLink.xls” which will contain a link to the other excel file.
When written by aspose the link is not valid.
But if you modidy by hand then the “”;"" to “;” in the center of the text of the formula then it will work!

4

Hi Daire,

Thanks for your sample code and using Aspose.Cells.

You need to use comma , instead of semicolon ; as a separator to parameters in the functions. Aspose.Cells work with commas instead of semicolons. Once, you will do, it will fix your issue.

We have tested your issue with the following code and it generated the correct xls file. We have attached the xls file for your reference.

C#


string baseDir = @“F:\Shak-Data-RW\Downloads”;


Workbook excelFileWithLink = new Workbook();

excelFileWithLink.Worksheets[0].Name = “Sheet1”;


Cell fileNameCell = excelFileWithLink.Worksheets[0].Cells[0, 0];


string hyperlinkTargetString = string.Format("[{0}]’{1}’!A1", Path.Combine(baseDir, “targetFile.xls”), “SheetB”);

string hyperlinkFormulaString = string.Format("=HYPERLINK("{0}","{1}")", hyperlinkTargetString, “targetFile.xls - Sheet B”);

fileNameCell.Formula = hyperlinkFormulaString;


excelFileWithLink.Save(“fileWithLink1.xls”);