Reference to a cell

Aspose.Cells Product Family
1

Is it possible to store a reference to a cell instead of a cell name?

If i get a cell with
Cell cell = workbook.Worksheets[0].Cells["D8"];

and when there is a table above that gets expanded, with listObject.PutCellValue the content of the cell d8 that lies underneath the table gets shifted down in excel, as expected, but the cell is still d8 and not the original cell anymore.

How is it possible to keep a reference to the same cell?
Like when i inser 20 values to the table, D8 would become D28.
Inserting values in my application happens dynamically, so it’s hard to keep track of that, a reference would be very handy.

2

@SimplyLiz,
You can achieve functionality through the following ways.

  1. Make these special cells defined names in Excel.
    Create a Defined Name in Excel using the following steps.
  • Select the cell or range of cells you want to name.
  • Go to the “Formulas” tab in the Excel ribbon.
  • Click on the “Define Name” button in the “Defined Names” group.
  • In the “New Name” dialog box, enter a name for the selected range in the “Name” field.
  • Optionally, specify the scope for the defined name (Workbook or specific worksheet).
  • Click “OK” to create the defined name.
  1. Using Aspose.Cells.
    a) Create named range.
    The sample code as follows:
Workbook workbook = new Workbook();

// Get the worksheet where you want to create the named range
Worksheet worksheet = workbook.Worksheets[0];

// Define the range of cells for the named range
CellArea area = new CellArea();
area.StartRow = 1;
area.StartColumn = 1;
area.EndRow = 4;
area.EndColumn = 2;

// Create the named range
Range namedRange = worksheet.Cells.CreateRange(area);
namedRange.Name = "MyNamedRange";

b) Create range, then calling Cells.AddRange(), we will maintain these added ranges.
The sample code as follows:

Workbook workbook = new Workbook();

// Get the worksheet where you want to create the range
Worksheet worksheet = workbook.Worksheets[0];

// Define the range of cells
int startRow = 1;
int startColumn = 1;
int endRow = 4;
int endColumn = 2;

// Create the range
Range range = worksheet.Cells.CreateRange(startRow, startColumn, endRow, endColumn);

worksheet.Cells.AddRange(range);

Hope helps a bit.

3

Thank you! I will give approach b a try, naming ranges in the template isn’t a option for our use case, because the result file is dynamically generated.

How is the performance when creating an maintaining lots of ranges? (100-1000) Is this an valid option?

I will give it a try :slight_smile:

4

@SimplyLiz
When we insert a region, we need to iterate the whole list.

The more elements in the list, the greater the impact on performance.

5

i feared as much. thanks for clarification.

6

@SimplyLiz,

You are welcome.

1 Like