How to update hyperlink in excel cell using aspose.Cells in .net

Aspose.Total Product Family
1

Hi Team ,
how to update hyperlink display name and url(Address) in excel cell using aspose.cells

1.In sample attached excel in F2 cell having attachment (Attachment display name : Att1 , address url : http://www.google.com)

2.how to update attachment name and URL like (Attachment display name : Att1New , address url : https://www.aspose.com/) in same cell like F2

  1. Else how to remove hyperlink in cell F2 ,and add new hyperlink name and url in same cell F2

Hyperlink adding Coding:
Below sample coding we are using:
Aspose.Cells.Cell CellAttachement = worksheet.Cells[FpExcel.ActiveSheetView.ActiveRow, objlastUsedRangeRowCell];
//Add a hyperlink to it.
int index = worksheet.Hyperlinks.Add(CellAttachement.Name, 1, 1, Attachementlink);
worksheet.Hyperlinks[index].TextToDisplay = AttachementName;
Aspose.Cells.Style style = CellAttachement.GetStyle();
// Setting the vertical alignment of the text in a cell
style.VerticalAlignment = Aspose.Cells.TextAlignmentType.Top;
CellAttachement.SetStyle(style);

Sample Excel for your Reference; Sample Excel.zip (14.8 KB)

2

@thiru1711,

Thanks for the template file, sample code and details.

See the following sample code to accomplish the task for your requirements. The sample code uses your input file and update the link on your desired cell accordingly:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\Sample Excel\\SampleExe.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            //suppose your cell is F2;
            string dCell = "F2";
            int i = 0;

            int linkCount = worksheet.Hyperlinks.Count;



            for (i = 0; i < linkCount; i++)
            {
                Aspose.Cells.Hyperlink link = worksheet.Hyperlinks[i];
                Console.WriteLine("Address: " + link.Address);
                Console.WriteLine("TextToDisplay: " + link.TextToDisplay);

                CellArea cellAdr = link.Area;
                Aspose.Cells.Cell cell = worksheet.Cells[cellAdr.StartRow, cellAdr.StartColumn];
                Console.WriteLine(cell.Name);

                if (cell.Name == dCell)
                {
                    link.Address = "http://www.aspose.com/";
                    link.TextToDisplay = "Att1New";
                    break;
                }


            }

            workbook.Save("e:\\test2\\Sample Excel\\out1.xlsx");

Hope, this helps a bit.

3

Hi @Amjad_Sahi,
Thanks for quick replay.

int linkCount = worksheet.Hyperlinks.Count;-- This line for tacking for all hyperlinks for excel

i need select row by row Hyperlinks, it possible to read specific row for Hyperlinks .

sample Doc: Sample Excel.zip (7.5 KB)

4

@thiru1711,

Thanks for the template file.

Please note, hyperlinks are not attached to the cell object itself in MS Excel’s data structure (source model/data), these are separate entities. So, you have to browse hyperlinks areas to check whether any one of them contains your desired cell(s) in row/col to be checked or not.

5

hi, thanks for replay.
how remove hyperlink in excel document

//suppose our cell is F2 having hyperlink,

we know hyperlink cell reference (F2), how to remove hyperlink in F2 Cell

6

@thiru1711,

See the following sample code for your needs:
e.g
Sample code:

 Workbook workbook = new Workbook("e:\\test2\\Sample Excel\\SampleExe.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            //suppose your cell is F2;
            string dCell = "F2";
            int i = 0;

            int linkCount = worksheet.Hyperlinks.Count;


            for (i = linkCount - 1; i >= 0; i--)
            {
                Aspose.Cells.Hyperlink link = worksheet.Hyperlinks[i];
                Console.WriteLine("Address: " + link.Address);
                Console.WriteLine("TextToDisplay: " + link.TextToDisplay);

                CellArea cellAdr = link.Area;
                Aspose.Cells.Cell cell = worksheet.Cells[cellAdr.StartRow, cellAdr.StartColumn];
                Console.WriteLine(cell.Name);

                if (cell.Name == dCell)
                {
                    //Remove hyperlink from your desired cell. e.g F2
                    worksheet.Hyperlinks.RemoveAt(i);
                    break;
                }


            }
            workbook.Save("e:\\test2\\Sample Excel\\out1.xlsx");

Hope, this helps a bit.

7

hi @Amjad_Sahi, thanks for replay,
my excel document having 10000 hyperlinks, how can i remove on specific cell (F2),with out for looping.
int linkCount = worksheet.Hyperlinks.Count;
(for (i = linkCount - 1; i >= 0; i–))–with out loop

8

@thiru1711,

As we already told you in one of the previous post that hyperlinks are not attached to the cell object itself in MS Excel’s data structure (source model/data), these are separate entities. So, you have to loop through the hyperlink collection for the purpose.