How do I get a non named range using cell address

Aspose.Cells Product Family
1

Hi, I want to get a range using referred area. So when I get precedent for a range I want to be able to get that precedent as a range object instead of a referred area object. How can I do that, the only option I have come across is createRange which I don’t want because that ends up adding new cells and removing the old values from the cells.

2

@Adhirath

To obtain a range using a cell address without creating a new range, you can utilize the createRange method from the Aspose.Cells library. However, since you want to avoid adding new cells and instead retrieve a range object directly, you can follow these steps:

  1. Access the Worksheet: First, ensure you have access to the worksheet where your cells are located.

  2. Get the Cell: Use the cell address to get the specific cell you are interested in.

  3. Create a Range: Instead of creating a new range that modifies the existing cells, you can create a range based on the cell address you have.

Here is a code example demonstrating how to achieve this:

import com.aspose.cells.*;

public class GetRangeFromCellAddress {
    public static void main(String[] args) throws Exception {
        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Access the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Get a specific cell using its address
        Cell cell = worksheet.getCells().get("A1");

        // Create a range based on the cell address
        Range range = worksheet.getCells().createRange("A1:F10"); // Adjust the range as needed

        // Now you can work with the range object without modifying existing cells
        // For example, you can set styles or perform other operations on the range

        // Save the workbook
        workbook.save("OutputFile.xls");
    }
}

In this example, the createRange method is used to define a range from “A1” to “F10”. You can adjust the range as necessary based on your requirements. This approach allows you to work with the range object without altering the existing cell values.

If you need further customization or specific functionality, please let me know!

3

@Adhirath,

Could you please refer to the following sample code if it gives you some insight on your requirements.
e.g.,
Sample code:

 
        // Initialize workbook and worksheet collection
        Workbook workbook = new Workbook();
        WorksheetCollection sheets = workbook.getWorksheets();

        // Add a new worksheet
        int newSheetIndex = sheets.add();
        Worksheet testSheet = sheets.get(newSheetIndex);
        testSheet.setName("SheetTest");

        // Access the first worksheet and its cells
        Cells cells = sheets.get(0).getCells();

        // Set formula in cell A1
        Cell cell = cells.get("A1");
        cell.setFormula("=SheetTest!B1+SUM(B1:B10)+[Book1.xls]Sheet1!A1");

        // Get precedents of the cell
        ReferredAreaCollection areas = cell.getPrecedents();
        if (areas != null) {
            for (int i = 0; i < areas.getCount(); i++) {
                ReferredArea area = areas.get(i);
                StringBuilder stringBuilder = new StringBuilder();

                if (area.isExternalLink()) {
                    stringBuilder.append("[");
                    stringBuilder.append(area.getExternalFileName());
                    stringBuilder.append("]");
                }

                stringBuilder.append(area.getSheetName());
                stringBuilder.append("!");
                stringBuilder.append(CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn()));

                if (area.isArea()) {
                    stringBuilder.append(":");
                    stringBuilder.append(CellsHelper.cellIndexToName(area.getEndRow(), area.getEndColumn()));
                }

                System.out.println(stringBuilder.toString());
            }
        }

Also, refer to the following document for your reference.

4

Is an external link always going to refer to a single cell, if not how do I get a range of cells in one go as a range? Also I need a range instead of a cell, how do I do that. My end goal is to get a range using cell address. One more question, does createRange() add cells to an existing sheet or just creates a range object for the passed address?

5

@Adhirath
CreatRange() simply creates a range object. You need to add it to the range collection of cells for management. If you need to find a range based on cells. You need to traverse the range collection and then obtain the desired range object based on the judgment criteria. Please refer to the following example code.


// Create a Workbook object
Workbook workbook = new Workbook();

// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

Cells cells = worksheet.getCells();
// Get a specific cell using its address
Cell cell = cells.get("A1");

// Create a range based on the cell address
Range range = cells.createRange("A1:F10"); // Adjust the range as needed

RangeCollection ranges = cells.getRanges();
System.out.println(ranges.getCount());
ranges.add(range);
System.out.println(ranges.getCount());


int rangeCount = ranges.getCount();
for (int i = 0; i < rangeCount; i++)
{
    Range temp = ranges.get(i);
    if(temp.getFirstRow() == cell.getRow() && temp.getFirstColumn() == cell.getColumn())
    {
    	System.out.println("Find range of cell " + cell.getName() + " : " + temp.getAddress());
    }
}

// Save the workbook
workbook.save(filePath + "out_java.xlsx");

The output:

0
1
Find range of cell A1 : A1:F10

Hope helps a bit.

6

@Adhirath

For external references, the single cell is just one example, in fact it can refer to a range too, not only single cell. However, for external references you cannot create one range object because Range is designed for references of local sheet(cells). It is just the main goal of ReferredArea to provide uniform api for all references, including external links and local ranges.

1 Like