Bookmark Cells

@tahir.manzoor

The hyperlink issue has been resolved. Thank you for taking the time and effort to assist me with the item.

I would also have to provide a similar change to Excel. I am looking into providing bookmarks for specific cells with hyperlink text. I have attached a copy of my current code as well as the excel file.

In the code that I have provided, you will notice that the foreach loop is searching for hyperlinks within the range of cells in the Excel file. The code is essentially iterating through each row of the worksheet until a hyperlink has been identified. In addition, an IF condition is used to identify whether the hyperlink object is an email.

I would like to identify a way to add bookmarks outside of the IF condition within the foreach loop.

Excel-Upload.zip (10.8 KB)

@whong4

Your query is related to Aspose.Cells. We are moving this post to Aspose.Cells forum where you will be guided appropriately.

@whong4,
We will analyze this issue and provide our feedback soon.

@whong4,
Could you please elaborate bit more about your requirements. Also share expected output and some screenshots for analysis.

@ahsaniqbalsidiqui
Thank you for your response. In the excel file that I sent you, you will notice that I have attached a name box for each cell with non-emails. I would like to be able to insert name boxes for non-emails programmatically.

Once the name boxes has been entered, I would then like to be able to convert the Excel files to a PDF file. I already have a separate C# file that handles this conversion, but I would need to include additional logic. In this case, I would need a foreach loop to be able to loop through each name box and convert each text with a unique name box to an image file.

The following logic converts text with bookmarks to a text image. I would like to be able to replicate this for name box.

                    foreach (Aspose.Words.Bookmark bookmark in wordDoc.Range.Bookmarks)
                    {
                        Image image = DrawText(bookmark.Text, "Arial", 10, Color.Black, Color.White, 0, 0);
                        bookmark.Text = "";
                        builder.MoveToBookmark(bookmark.Name);
                        builder.Font.Underline = Aspose.Words.Underline.None;
                        builder.InsertImage(image);
                    }

Excel-Output.zip (18.2 KB)

You can loop through named ranges (Names) and get its RefersTo attribute value/string. Now set this string as printable area for the sheet and render to image. This way, you will get picture for each named range only. See the sample code on how to specify printable area, you need to add such code before rendering to image format.
e.g
Sample code:

var worksheet = workbook.Worksheets[0];

            worksheet.PageSetup.PrintArea = printArea;//you will get it using Name.RefersTo attribute.
            worksheet.PageSetup.TopMargin = 0;
            worksheet.PageSetup.LeftMargin = 0;
            worksheet.PageSetup.RightMargin = 0;
            worksheet.PageSetup.BottomMargin = 0;

Hope, this helps a bit.

@Amjad_Sahi

Thank you for your feedback. I would like to be able to insert name boxes per cell rather than assign names to a certain range of cells. In the following code snippet I have provided, I am looping through a range of cells. As it iterates through each cell in the given range, it is identifying the value for each cell.

The cell.Name will be able to identify the name for a given cell, but this property is read-only and cannot be assigned. Would it be possible for me to manipulate the following logic to only add names to cells without an email address string value.

                    IEnumerator ie = range.GetEnumerator();
                    while (ie.MoveNext())
                    {
                        Cell cell1 = (Cell)ie.Current;
                        Console.WriteLine(cell1.Value + ":" + cell1.StringValue);
                        if (Literals.RegEx.EmailAddress.IsMatch(cell1.StringValue))
                        {
                            continue;
                        }
                    }

Thanks,
Woon Gi

@whong4,

Please note, Cell.Name refers to cell’s name/label (e.g A1, A2, A3…B1, B2, B3,… etc.) in Excel sheet which cannot be changed, so it is a read-only attribute. I think you have misunderstand the property. I am afraid, you cannot use this property as a Name (named range).

Well, it does not matter if you create the named range (Name) based on single cell or a range of cells. You got to devise by yourself what I have told you above, i.e., “…You can loop through named ranges (Names) and get its RefersTo attribute value/string…”. Something like, see the sample code segment below:
e.g
Sample code:

.........

            WorksheetCollection worksheets = workbook.Worksheets;
            NameCollection names = worksheets.Names;

            for (int i = 0; i < names.Count; i++)
            {

                Console.WriteLine("Name:" + names[i].Text + ", Referes To: " + names[i].RefersTo + ", Visible:  " + names[i].IsVisible);


            }
.....

@Amjad_Sahi

I am still having a hard time figuring out how to create a named range on a single cell. I can understand the approach that you are taking with the name range for loop, but this process is only valuable after a named range has been created.

Based on the following logic that I have created, I would like to figure out how to create a named range for cells with hyperlinks.

                    while (ie.MoveNext())
                    {
                        Hyperlink cell1 = (Hyperlink)ie.Current;

                        if (Literals.RegEx.IsEmailAddress.IsMatch(cell1.Address))
                        {
                            continue;
                        }
                        
                        cell1.Delete();

                    }

Would you be able to provide resources that I would be able to reference from? If not, I would like additional guidance as to how I can work around this.

Thanks,
Woon Gi

@whong4,

See the sample code for your reference:
e.g
Sample code:

.......
			//get the first sheet.
			Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];  
			//Create a Cells object ot fetch all the cells.
			Aspose.Cells.Cells cells = worksheet.Cells;
			//Create some ranges.
			Range r1 = cells.CreateRange("A1","A1");
			r1.Name = "Range1";
			Range r2 = cells.CreateRange("C1","C1");
			r2.Name = "Range2";
			Range r3 = cells.CreateRange("D1","D1");
			r3.Name = "Range3"; 
........

Hope, this helps a bit.

@Amjad_Sahi

Thank you for your feedback. I have been working with the following logic that you have provided, but I stumbled upon another issue. I noticed that you have changed the name of the Range object to create 3 separate named range instances.

I currently placed similar logic in a while loop. However, I am only using one range object, which ends up creating 1 named range instance. I would like to be able to create separate named range instances. Since the named range logic is taking place inside a while loop, the named range keeps getting overwritten. I need to find a way to store the values separately to prevent the named range from getting overwritten.

                    IEnumerator ie2 = range.GetEnumerator();
                    while (ie2.MoveNext())
                    {
                        int i = 1;
                        Cell cell2 = (Cell)ie2.Current;
                        System.Console.WriteLine(cell2.Name + ":" + cell2.Value);
                        
                        if (Literals.RegEx.EmailAddress.IsMatch(cell2.StringValue))
                        {
                            continue;
                        }

                        Aspose.Cells.Range bookmark = cells.CreateRange(cell2.Name, cell2.Name);
                        bookmark.Name = "bookmark" + i;
                        //System.Console.WriteLine(cell2.Name);
                        i++;
                    }

@whong4,

I tested using the following sample code, it works absolutely fine. I used single Range object and created three named ranges with different names. I can use Name Manager in Ms Excel to confirm all three named ranges which are created properly. I have also attached the output file for your reference.

I guess you should evaluate your logic and matching code. Also, debug your code line by line to fix it accordingly by yourselves.
files1.zip (6.4 KB)

e.g
Sample code:

//Create a Workbook.
			Aspose.Cells.Workbook wbk=new Aspose.Cells.Workbook();
			//Create a Worksheet and get the first sheet.
			Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];  
			//Create a Cells object ot fetch all the cells.
			Aspose.Cells.Cells cells = worksheet.Cells;
			//Create some ranges.
			Range r1 = cells.CreateRange("A1","A1");
			r1.Name = "Range1";
			r1 = cells.CreateRange("C1","C1");
			r1.Name = "Range2";
			r1 = cells.CreateRange("D1","D1");
			r1.Name = "Range3";
			
            wbk.Save("e:\\test2\\out1.xlsx");

@Amjad_Sahi

I took a look at the output file and I noticed that the named ranges are only appearing for one row. Did this occur because you only provided data for a single row?

@whong4,

It does not matter whether the named ranges are defined in a single row or in multiple rows. I even updated the code segment and again the output file is fine tuned:
e.g
Sample code:

//Create a Workbook.
			Aspose.Cells.Workbook wbk=new Aspose.Cells.Workbook();
			//Create a Worksheet and get the first sheet.
			Aspose.Cells.Worksheet worksheet = wbk.Worksheets[0];  
			//Create a Cells object ot fetch all the cells.
			Aspose.Cells.Cells cells = worksheet.Cells;
			//Create some ranges.
			Range r1 = cells.CreateRange("A1","A1");
			r1.Name = "Range1";
			r1 = cells.CreateRange("A2","A2");
			r1.Name = "Range2";
			r1 = cells.CreateRange("A3","A3");
			r1.Name = "Range3";
			
            wbk.Save("e:\\test2\\output2.xlsx"); 

Please find attached the output file for your reference.
output2.zip (6.4 KB)

@Amjad_Sahi

I figured out the error from my previous sample code. I think I should be set! Thank you for your help!

Thanks,
Woon Gi

@whong4,

Good to know that you have sorted it out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.