Add Custom Properties to cell

Aspose.Cells Product Family
1

Is there a way to add custom properties to a cell, or are there existing properties I can utilize in Excel? I initially used Named Ranges (defined names) for this purpose. However, as the number of Named Ranges increased, I noticed that Excel took significantly more time to open

2

@Jayshiv
If you need to use one cell or range with a convenient or meaningful name in excel(for example, referencing to it in formula), we are afraid defined names is the only way. However, as you have found, if there are lots of defined names, the time cost is sure to increase when fetching and calculating them. We are afraid there is no better solution if you cannot use the cell name or range directly(such as A1, A1:C2…).

If you need the name to represent specified cell or range in your program only, we think building a map from the defined name to the cell or range by yourself should be a more efficient way.

3

Certainly! In the given scenario, I have a table in the first image, and I’m dynamically adding a row to it. For example, inserting a row between the existing rows 1 and 2 would shift the original row 2 to become row 3 after the addition. Similar scenarios apply to merging, deleting rows, columns, or cells.

The question is whether there is a way to identify precisely where rows or columns were added, deleted, or merged without utilizing the Name Range concept. It’s worth noting that using Name Ranges for this purpose is not preferred due to performance concerns in loading the Excel file.

Is there a method or approach that can efficiently track and identify these changes in the spreadsheet?
1st.png (2.8 KB)

2nd.png (2.2 KB)

4

@Jayshiv

For your requirement, we are afraid defined names should be the proper solution. Setting the cell references as formula to some other cells directly also can give you the expected result and may be a bit better performance. For example, using “Sheet2” to mark some cells in “Sheet1”:

sheet2.Cells["A1"].Formula="=Sheet1!A1";
sheet2.Cells["A2"].Formula="=Sheet1!A2";

then after inserting the row as your description, the formula of cell A2 in Sheet2 will become “Sheet1!A3”. Maybe you can try this way to see whether you can get better performance for your application.

5

If a cell already contains a formula, or the user subsequently applies another formula to that same cell

6

@Jayshiv
You can consider adding these named ranges to cells.Ranges after defining them. We will update the ranges in Cells.Ranges when doing add/delete/merge operations.

You can use the following code to observe changes.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

// Create a range of Cells from Cell A1 to C10
Range namedRange = cells.CreateRange("A1", "C10");

namedRange.Name = "MyRange";

Console.WriteLine(workbook.Worksheets.GetRangeByName("MyRange").RefersTo);

cells.Ranges.Add(namedRange);

cells.DeleteRow(2);

Console.WriteLine(workbook.Worksheets.GetRangeByName("MyRange").RefersTo);

Hope helps a bit.

7

@Jayshiv
If defining too many named ranges results in performance issues. You can directly create some ranges and then use Hashtable to maintain relationships. We will update the ranges in Cells.Ranges when doing add/delete/merge operations. At the same time, you can quickly find the range you need.

The sample code as follows:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

Hashtable hashtable = new Hashtable();  

Range range1 = cells.CreateRange("A1", "C10");
Range range2 = cells.CreateRange("D1", "F10");

hashtable.Add("range1", range1);
hashtable.Add("range2", range2);

IEnumerator iter = hashtable.Keys.GetEnumerator();
while (iter.MoveNext())
{
    string key = (string)iter.Current;
    Range range = (Range)hashtable[key];
    Console.WriteLine(key + " :  " + range.RefersTo);

}
cells.Ranges.Add(range1);
cells.Ranges.Add(range2);

cells.DeleteRow(2);

iter = hashtable.Keys.GetEnumerator();
while (iter.MoveNext())
{
    string key = (string)iter.Current;
    Range range = (Range)hashtable[key];
    Console.WriteLine(key + " :  " + range.RefersTo);

}

Hope helps a bit.