Help: Aspose.Cells modifies custom link (atp://data/file/view/) after save

Aspose.Cells Product Family
1

I have an simple excel file (c:\mydoc.xls), which contains a custom hyperlink,such as atp://data/file/view/ for our internal application usage. If I use Aspose.Cells to open this doc and save it. However, the link tooltip shows as something file:///c\mydoc.xls-atp://data/file/view/, if I open hyperlink to edit, the address is blank. Why happens like this and how to fix it? Can someone help, we are in production, need to address this problem ASAP. Thanks

I am using v4.4.3.1 , and Office Excel 2003

Chang

2

Hi Chang,

Thanks for pointing out the hyperlink issue,

We found the issue implementing your scenario and will fix it soon.

Thanks you.

3

Hi Chang,

Please try this fix.

4

Hi Warren,

After first simple test, seems your patch works. will apply it to the project for a complete test. Thank you so so much for your great support.

Chang

5

Hi Warren,

I am still having the problem: I feel you had just fixed my example, but actual, my real link is more complicated, one of them can be like this:

aura://82c29c22-67ea-45db-b7df-aad2f1ad2ad5/5f0fa673-9400-46fa-bc6b-ea0d2351ba69_0/65956757-2119-470e-892a-a9dd92285790/13/4/0/

and, I need to replace it with different value, such as, all the values are dynamically change based on doc:

aura://091A0986-231E-4405-BC75-24D4BBD08DF8/5E49E556-5BCD-4EF5-A9BF-7C35A6AD46E2_0/853B6FC8-1A79-482F-A22D-31E01A525429/13/4/0/

My code is like this:

string docFile = @"c:\temp\book1.xls"; //which has one my custom link..

System.IO.FileStream fstream = new System.IO.FileStream(docFile, FileMode.Open);

Workbook workbook = new Workbook();

workbook.Open(fstream);

fstream.Close();

foreach (Worksheet worksheet in workbook.Worksheets) {

foreach (Aspose.Cells.Hyperlink link in worksheet.Hyperlinks){

string newUrl = FixLink(link.Address); //my function to replace existing link with new address

link.Address = newUrl;

//link.TextToDisplay = newUrl;

//link.ScreenTip = newUrl;

}

}

workbook.Save(docFile, FileFormatType.Default);

workbook = null;

I tried with WorkBook.Open(docFile) method, still doesn't work ...

I only want to change the address, not any other properties of the link. Can you have a generic solution to fix, or for first step, see if you can fix with address format on the top, (aura://..../....)?

Thanks

Chang

6

Hi Chang,

We will figure out the issue and support your desired hyperlink address i.e., "aura://.." as well.

Thank you.

7

hi Amjad,

how is the fix, any updates, we are waiting.... thanks

chang


8

Hi,

Please try this fix.

9

It works ! Thanks.

Chang

10

Hi,

We are still have a problem with hyperlink change.

Your previous fix works fine if I open excel after replace the old links with new values, however, if I open the same excel file (after link updated) with Excel Interop Application, and try to search for the the hyperlinks, like code :

        object oFalse = (object)false;
        object oTrue = (object)true;
        object missingParm = Type.Missing;

        Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;

        Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(file, oFalse, oTrue, missingParm, missingParm,
                missingParm, missingParm, missingParm, missingParm, missingParm,
                missingParm, missingParm, missingParm, missingParm, missingParm);


        workbook.RefreshAll();
        
        foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in workbook.Worksheets)
        {
            
            foreach (Microsoft.Office.Interop.Excel.Hyperlink link in sheet.Hyperlinks)
            {
                Console.WriteLine("{0} = {1}", link.Address, link.Name);
            }
        }

The link.Address will still show me the old value, but link.Name does show new value (suppose should show me the name of link, not url address!), more interesting, if I move mouse pointer to link.Name for a few second, and link.Address will refresh to the new value. There is something background updating on link.Address value!

If I manually modify the link and save, then verify with above code, everything looks fine: link.Address is showed as the value I enter, link.Name is the link name, not the address.

Can you please verify it and let me know? thanks

Chang

11

Hi,

Could you create a simple poject here(inculdes tempalte file ,created file and some codes) ? We will check it soon.In VBA ,link.Name should be same as link.TextToDisplay. If you want to change the name of the link, you should change Hyperlink.TextToDisplay property.

12

Hi,

After checking the file, we find MS Excel caches some info about Hyperlink in custom properties. If we remove them, it will works fine. I think MS Excel Interop get the info from the cache.So please remove cache before saving the file.See following codes:

static bool FixExcelLinks(string docFile)
{
Workbook workbook = null;
workbook = new Workbook();
workbook.Open(docFile);

foreach (Worksheet worksheet in workbook.Worksheets)
{
foreach (Aspose.Cells.Hyperlink link in worksheet.Hyperlinks)
{
link.Address = newUrl;
}
}

workbook.Worksheets.CustomDocumentProperties.Remove("_PID_HLINKS");
workbook.Save(docFile);

workbook = null;
return true;
}

13

Hi, your fix (4.4.3.12) is working fine for Excel 2003, however, it fails with 2007.

basically, I have custom link in excel 2007 xlsx file , like this:

aura://ABCDabcd-6789-0123-3579-0E08DA97B00A/24BF8774-162C-4347-94DA-307F9CC92AAE_0/128b3448-3ca1-409d-8db6-d40d84e5ee13/36/20/0/

after I replace it with:

aura:\\12345678-6789-0123-3579-0E08DA97B00A\24BF8774-162C-4347-94DA-307F9CC92AAE_0\128b3448-3ca1-409d-8db6-d40d84e5ee13\36\20\0\

with code like:

Workbook workbook = new Workbook();

workbook.Open(docFile);

foreach (Worksheet worksheet in workbook.Worksheets)

{

foreach (Aspose.Cells.Hyperlink link in worksheet.Hyperlinks)

{

link.Address = newUrl;

}

}

workbook.Worksheets.CustomDocumentProperties.Remove("_PID_HLINKS");

workbook.Save(docFile,FileFormatType.Excel2007Xlsx);

workbook = null;

Then I open the excel file, the link is changed to:

/aura:\12345678-6789-0123-3579-0E08DA97B00A\24BF8774-162C-4347-94DA-307F9CC92AAE_0\128b3448-3ca1-409d-8db6-d40d84e5ee13\36\20\0\

with tooltip something like file:///aura:\12345678-6789-0123-3579-0E08DA97B00A...

Chang

14

Hi Chang,

Thanks for your details.

Kindly try the attached fix (4.5.0.21) if it works fine.

Thank you.