none
Create a hyperlink to an existing cell to another sheet using Open XML sdk RRS feed

  • Question

  • I want to set hyperlink to the existing value in the cell to another sheet. Here is the code i am using, but its not working. Please help me to figure out this

    Hyperlinks hyperlinks1 = new Hyperlinks();Hyperlink hyperlink1 = new Hyperlink() { Reference = cell.CellReference, Location = "Sheet2!A3", Tooltip = "Enter employee details", Id = "UNIQUE" };  hyperlinks1.Append(hyperlink1);PageMargins pageMargins = worksheetPart.Worksheet.Descendants<PageMargins>().First();

    worksheetPart.Worksheet.InsertBefore<Hyperlinks>(hyperlinks1, pageMargins);
    worksheetPart.Worksheet.Save();

    Monday, May 5, 2014 2:28 PM

Answers

  • Hi,

    According to your description, you want to set a hyperlink of an existing Cell to another sheet. I try to reproduce your issue with the code below and it works well to set the hyperlink of Cell "A1" in Sheet1 to the Cell "A3" of Sheet2.

    I suggest you checking your code to see whether it helps. If not, did you get any error message when you run the code? Have you checked the "cell.CellReference" in your code? It's better for you to share the whole sample code for us to reproduce your issue.

    private static void InsertHyperLinkInWorksheet()
    {
    
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\Users\luna\Documents\test.xlsx", true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == "Sheet1");
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return;
            }
    
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            Hyperlinks hyperlinks1 = new Hyperlinks();
    
            DocumentFormat.OpenXml.Spreadsheet.Hyperlink hyperlink1 = new DocumentFormat.OpenXml.Spreadsheet.Hyperlink() { Reference = "A1", Location = "Sheet2!A1", Display = "tt" };
    
            hyperlinks1.Append(hyperlink1);
    
            DocumentFormat.OpenXml.Spreadsheet.PageMargins pageMargins = worksheetPart.Worksheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.PageMargins>().First();
            worksheetPart.Worksheet.InsertBefore<Hyperlinks>(hyperlinks1, pageMargins);
            worksheetPart.Worksheet.Save();
        }
    }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 7, 2014 6:17 AM
    Moderator
  • >>And I run the code with previous workbook. Is it the issue?<<

    You should create a new workbook to test since your previous workbook may be crashed and have some wrong XML elements.

    Monday, June 9, 2014 10:55 AM

All replies

  • Hi,

    According to your description, you want to set a hyperlink of an existing Cell to another sheet. I try to reproduce your issue with the code below and it works well to set the hyperlink of Cell "A1" in Sheet1 to the Cell "A3" of Sheet2.

    I suggest you checking your code to see whether it helps. If not, did you get any error message when you run the code? Have you checked the "cell.CellReference" in your code? It's better for you to share the whole sample code for us to reproduce your issue.

    private static void InsertHyperLinkInWorksheet()
    {
    
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\Users\luna\Documents\test.xlsx", true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == "Sheet1");
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return;
            }
    
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            Hyperlinks hyperlinks1 = new Hyperlinks();
    
            DocumentFormat.OpenXml.Spreadsheet.Hyperlink hyperlink1 = new DocumentFormat.OpenXml.Spreadsheet.Hyperlink() { Reference = "A1", Location = "Sheet2!A1", Display = "tt" };
    
            hyperlinks1.Append(hyperlink1);
    
            DocumentFormat.OpenXml.Spreadsheet.PageMargins pageMargins = worksheetPart.Worksheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.PageMargins>().First();
            worksheetPart.Worksheet.InsertBefore<Hyperlinks>(hyperlinks1, pageMargins);
            worksheetPart.Worksheet.Save();
        }
    }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 7, 2014 6:17 AM
    Moderator
  • Am getting the following error message when open the downloaded excel file from save or open prompt. 

     "Excel found unreadable content in test.xlsx. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes"

    When i click on Yes button, sheet opens with empty values (blank sheet). I assume that the code runs successfully, but the file gets corrupted while adding the hyperlinks to worksheet. Also i checked the  "cell.CellReference", it is "C56". 

    Following is the complete code snippet.   

    string relationshipId = sheets.First().Id.Value; WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

                        //// Hyperlink
                        Hyperlinks hyperlinks1 = new Hyperlinks();
                        Row excelRow56 = GetExcelRow(56, worksheetPart);
                        foreach (Cell excelCell56 in excelRow56.Elements<Cell>())
                        {
                            string cellText = ReadExcelCell(workBookPart, excelCell56);
                            if (cellText == "Enter Data")
                            {
                                Hyperlink hyperlink1 = new Hyperlink() { Reference = excelCell56.CellReference, Location = "Sheet2!A3", Display = "Enter Data", Tooltip = "Enter Employee Data", Id = "UNIQUE" };
                                hyperlinks1.Append(hyperlink1);                          
                                PageMargins pageMargins = workSheet.GetFirstChild<PageMargins>();
                                //PageMargins pageMargins1 = worksheetPart.Worksheet.Descendants<PageMargins>().First();                           
                                worksheetPart.Worksheet.InsertBefore<Hyperlinks>(hyperlinks1, pageMargins);
                            }
                        }

                        worksheetPart.Worksheet.Save();

    Hyperlink generated as folows:
    Note: ref="L24" and ref="M24" are already existing links, but the link 
    ref="N56" dynamically created as shown in my code, also missing < r:id >  and <relationship tags> ie {xmlns:r} for this dynamically created hyperlink.

    <x:hyperlink ref="L24" 
    r:id="rId11" display="mailto:sas@gmail.com" 
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
    xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />

    <x:hyperlink ref="M24" 
    r:id="rId12" display="mailto:sas@gmail.com" 
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
    xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />


    <x:hyperlink ref="N56" 
    location="Sheet2!A3" 
    display="Enter Data" 
    xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />

    








    • Edited by Sanooj V S Monday, May 12, 2014 1:38 PM description added
    Monday, May 12, 2014 7:56 AM
  • Have you run the code in a new workbook or the previous one?

    Have you tried Luna's code?

    Wednesday, May 14, 2014 9:05 AM
  • Yes.. tried Luna's code but didn't work.

    And I run the code with previous workbook. Is it the issue?


    • Edited by Sanooj V S Monday, June 2, 2014 6:54 AM modified
    Monday, June 2, 2014 6:30 AM
  • >>And I run the code with previous workbook. Is it the issue?<<

    You should create a new workbook to test since your previous workbook may be crashed and have some wrong XML elements.

    Monday, June 9, 2014 10:55 AM
  • Yes... It is working with a new workbook. As you (chunchencool) said previous workbook may be crashed and have some wrong XML elements. 
    Note: P
    osted code of both Luna Zhang and mine are working fine.

    Thanks all for your help and support.


    • Edited by Sanooj V S Wednesday, June 18, 2014 6:59 AM modified names
    Wednesday, June 18, 2014 6:58 AM