OpenXML How to add hyperlink that points to another sheet

Question

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?

Daniil T. · Accepted Answer · 2020-10-11 22:46:34Z

So after some time of banging my head over the table, I decided to focus on my OpenXML Excell investigation and I succeeded!

I found a solution on how to insert a hyperlink to an excel file, even more, now my program can create multiple sheets which count depends on my dataObject. I'm so excited and I want to share my achievement with everybody.

First of all, you need to create SpreadSheetDocument for this reason I created a method CreatePackage and passing as parameters filePath and also my DataObject

public void CreatePackage(string filePath, List<DataObject> data)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)
    )
    {
        CreateParts(document, data);
    }
 }

CreateParts method which accepts SpreadsheetDocument and DataObject

private void CreateParts(SpreadsheetDocument document, List<DataObject> data)
    {
        ExtendedFilePropertiesPart extendedFilePropertiesPart = document.AddNewPart<ExtendedFilePropertiesPart>();
        extendedFilePropertiesPart.Properties = new Properties();

        WorkbookPart workbookPart = document.AddWorkbookPart();
        //Create new sheet for every Unique error
        GenerateWorkbookPart(workbookPart,data.Count);
        //generates new SheetPart for every sheet
        GenerateWorkSheetsParts(workbookPart,data);

    }

I couldn't find why and how ExtendedFilePropertiesPart depends on HyperLink in Excell but I know for sure that without this hyperlink doesn't work even Excell file will be generated corrupted.

Next, we need to create a workbook for WorkBookPart also for workbook we need to create a Sheets which will hold all sheet. For every sheet we need also create WorkSheetPart but to this, I'll get back later. For now:

private void GenerateWorkbookPart(WorkbookPart workbookPart, int dataCount)
        {

            Workbook workbook = new Workbook();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            for (int i = 1; i < dataCount+2; i++)
            {
                var relId = "rId" + i;
                if (i == 1)
                {
                    sheet = new Sheet()
                    {
                        Name = "Main",
                        SheetId = (uint) i,
                        Id = relId
                    };
                }
                else
                {
                    sheet = new Sheet()
                    {
                        Name = "Unique" + (i-1),
                        SheetId = (uint)i,
                        Id = relId
                    };
                }

                sheets.AppendChild(sheet);
            }
            workbook.AppendChild(sheets);
            workbookPart.Workbook = workbook;
        }

Why I'm adding +2 to the total count? Because first of all, for some reason, RelationshipId ( Id ) of the sheet cannot be 0 so I need to start from 1, and the second, the first page in my excel is the navigation page so I skipping it too.

After I created WorkBookPart for my document I'll start creating WorkSheetPart for each Sheet and populating it with data.

private void GenerateWorkSheetsParts(WorkbookPart workbookPart, List<DataObject> data)
        {
            for (int i = 1; i < data.Count+2; i++)
            {
                var relId = "rId" + i;
                if (i == 1)
                {
                    WorksheetPart workSheetPart = workbookPart.AddNewPart<WorksheetPart>(relId);
                    GenerateWorkSheetPartContent(workSheetPart, i, data);
                }
                else
                {
                    WorksheetPart workSheetPart = workbookPart.AddNewPart<WorksheetPart>(relId);
                    GenerateWorkSheetPartContent(workSheetPart, i, data[i-2].NestedObject);
                }

            }
        }

 private void GenerateWorkSheetPartContent(WorksheetPart worksheetPart,int indexer, List<NestedObject> data)
        {

            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            Hyperlinks hyperlinks = new Hyperlinks();

            if (indexer == 1)
            {
                sheetData.AppendChild(ConstructHeader("Unique errors", "Count"));
                
                foreach (var @event in data)
                {
                    indexer++;
                    Row row = new Row();
                    row.Append(ConstructCell(@event.ErrorMessage, "A" + indexer, CellValues.String), ConstructCell(@event.ListOfErrorsObject.Count.ToString(), CellValues.Number));
                    sheetData.AppendChild(row);
                    Hyperlink hyperlink = new Hyperlink()
                    {
                        Reference = "A" + indexer,
                        Location = "Unique" + (indexer - 1)+"!A1",
                        Display = "Unique" + indexer
                    };
                    hyperlinks.AppendChild(hyperlink);
                }
                worksheet.Append(sheetData, hyperlinks);
                worksheetPart.Worksheet = worksheet;
            }
            else
            {
                worksheet.AppendChild(sheetData);
                worksheetPart.Worksheet = worksheet;
            }
        }

Indexer - as my objects go one after another I can ensure that the Indexer will be the same as rId number and also equals the First Column row number.

I also made few helpers ConstructHeader which constructs headers at the first row and it takes params of String and ConstructCell which helps faster to construct cells that I can append to a row. It also has one overload for constructing a cell which should be with hyperlink.

        private Row ConstructHeader(params string[] headers)
    {
        Row row = new Row();
        foreach (var header in headers)
        {
            row.AppendChild(ConstructCell(header, CellValues.String));
        }
        return row;
    }

    private Cell ConstructCell(string value, CellValues dataType)
    {
        return new Cell()
        {
            CellValue = new CellValue(value),
            DataType = new EnumValue<CellValues>(dataType),
        };
    }
    private Cell ConstructCell(string value, string cellReference, CellValues dataType)
    {
        return new Cell()
        {
            CellReference = cellReference,
            CellValue = new CellValue(value),
            DataType = dataType,
        };
    }

Summary: This code will create an Excell document with the first Sheet as navigation and other sheets with populated data from DataObject. I hope this will help somebody. Also If you have any comments or feedback - please share I would like to hear it out.

Nice post! That's exactly what I searched for. Thanks! But how is your List<DataObjects> build? I struggle with the NestedObject Part.
@crank as I remember it had structure of string and List of correlation ids

ssz · Accepted Answer · 2021-04-13 06:12:20Z

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\")";

Collectives™ on Stack Overflow

OpenXML How to add hyperlink that points to another sheet

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related