OpenXML How to add hyperlink that points to another sheet

user_1452390 0
Viewed 2814

I'm looking for example how to add hyperlink to cell. Seems that it is not so easy as adding a text.

I got this peace of code that generates right XML but in excel links do not appear

    private SheetData PopulateSheetWithData(SheetData sheetData, IList<Event> events, SpreadsheetDocument document)
    {
        int errorIndex = 0;
        foreach (var @event in events)
        {
            errorIndex++;

            Hyperlinks hyperlinks = new Hyperlinks();
            Hyperlink hyperlink = new Hyperlink()
            {
                Location = "UniqueError_" + errorIndex + "!A1",
                Display = @event.LOG_CORR_KEY,
                Reference = "A"+(errorIndex+1),
                Id = "UniqueError_" + errorIndex
            };
            hyperlinks.AppendChild(hyperlink);
            sheetData.AppendChild(hyperlinks);
        }
        return sheetData;
    }

Am I doing something wrong ? I found this article how to add hyperLink but It looks like it's too ever complicated. Any help with this question?

openxml hyperlink excel .net c#
2 Answers

This works for me and links C1 in Sheet1 of "test.xlsx" to A10 in Sheet2

static void AddHyperlink()
    {

        var filePath = "test.xlsx";

        // C1 in Sheet1
        var sheetName1 = "Sheet1";
        var column = "C";
        var row = 1;

        // Link to A10 in Sheet2
        var pathToOtherFile = filePath;
        var sheetName2 = "Sheet2";
        var cellRef = "A10";

        using (var document = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart wbPart = document.WorkbookPart;



            var sheets = document.WorkbookPart.Workbook.Sheets.Cast<Sheet>().ToList();



            var sheet = sheets.Where(x => x.Name == sheetName1).FirstOrDefault();

            string relationshipId = sheets.First().Id.Value;
            //get the worksheetpart by Id
            WorksheetPart worksheetPart = (WorksheetPart)wbPart.GetPartById(relationshipId);
            var worksheet = worksheetPart.Worksheet;


            Cell cell = GetCell(worksheet, column, row);
            var s = cell.LastChild;

            var cell1 = cell;

            CellFormula cellFormula1 = new CellFormula() { Space = SpaceProcessingModeValues.Preserve };
            cellFormula1.Text = $"HYPERLINK(\"[{pathToOtherFile}]{sheetName2}!{cellRef}\", \"Radio Clash\")";
            cell1.CellFormula = cellFormula1;

        }

    }


    private static Cell GetCell(Worksheet worksheet, string columnName, int rowIndex)
    {
        Row row = GetRow(worksheet, rowIndex);

        if (row == null)
            return null;

        return row.Elements<Cell>().Where(c => string.Compare
               (c.CellReference.Value, columnName +
               rowIndex, true) == 0).First();
    }


    // Given a worksheet and a row index, return the row.
    private static Row GetRow(Worksheet worksheet, int rowIndex)
    {
        return worksheet.GetFirstChild<SheetData>().
          Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
    }

If you want to use self-reference (not referencing another file), you can use:

    cellFormula1.Text = $"=HYPERLINK(\"[\" & MID(CELL(\"filename\"),SEARCH(\"[\",CELL(\"filename\"))+1, SEARCH(\"]\",CELL(\"filename\"))-SEARCH(\"[\",CELL(\"filename\"))-1) &\"]{sheetName2}!{cellRef}\", \"Radio Clash\")";

Instead of:

cellFormula1.Text = $"HYPERLINK(\"[{pathToOtherFile}]{sheetName2}!{cellRef}\", \"Radio Clash\")";
user_27351630
Related