Copying hyperlinks from a cell to another with NPOI

Question

I am trying to copy certain data from a sheet to another, but some cells are simple strings and some are hyperlinks. If I use StringCellValue on the strings one it's ok, but I haven't found a method to copy the hyperlinks from the original sheet into the new one that I am constructing.

For the construction of the new sheet and for data copying I am using NPOI.

//UPDATE I have added the code to insert the hyperlinks but when I run the program it shows the following exception: Object reference not set to an instance of an object.

Here is my code:

  using (FileStream fs = new FileStream(@"C:\Users\File.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                Console.WriteLine("Time to wait....");
                templateWorkbook = new XSSFWorkbook(fs);

            }
              row.GetCell(6).SetCellValue(sheettoget.GetRow(1).GetCell(13).StringCellValue);
            var sourceLink = sheettoget.GetRow(1).GetCell(13).Hyperlink;


                    if(sourceLink != null)
              {
                        Console.WriteLine("Inserting first Juice session...");
                        var targetLink = new XSSFHyperlink(sourceLink.Type);

                        targetLink.Address = sourceLink.Address;
                       }

                        row.GetCell(6).Hyperlink = targetLink; 
                        row.GetCell(6).CellStyle = sheettoget.GetRow(1).GetCell(13).CellStyle;

score 3 · Accepted Answer · answered Oct 09 '16 at 23:07

3

You can copy a hyperlink like this, where sourceCell is the cell you are copying from, and targetCell is the cell you are copying to:

    targetCell.SetCellValue(sourceCell.StringCellValue);
    var sourceLink = sourceCell.Hyperlink;
    if (sourceLink != null)
    {
        var targetLink = new XSSFHyperlink(sourceLink.Type);
        targetLink.Address = sourceLink.Address;
        targetCell.Hyperlink = targetLink;

        // also copy the cell style to ensure the copied link still looks like a link
        targetCell.CellStyle = sourceCell.CellStyle;
    }

answered Oct 09 '16 at 23:07

Brian Rogers

125,747
31
299
300

hey, thanks again for the help :D but I get this: Object reference not set to an instance of an object – Work_God_Damnit Oct 10 '16 at 07:21
In your code above, the last two lines should be *inside* the closing brace from the `if (sourceLink != null)` as shown in my answer. If you're still getting a NullReferenceException after that, you'll need to debug and find out which line is causing the error. Once you know that, you can add a null check. See [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Brian Rogers Oct 10 '16 at 14:11

Copying hyperlinks from a cell to another with NPOI

1 Answers1