Specifying a cell range on a different worksheet

Aspose.Cells Product Family
1

I am working for a company that is currently evaluating whether to use Aspose.Excel in the system we are developing. So far the component has been terrific. We do have one problem though.

Sometimes we want to have formulas on one Excel worksheet reference a cell range on another worksheet. For example,

=SUM(Advertising!C2:C5)

where Advertising is the name of a worksheet other than the worksheet that contains the above formula.

The problem is that Aspose.Excel rejects this as an invalid formula. Am I doing something wrong? If this is not yet implemented could you please add support for it?

Thanks in advance.


P.S. While I am here posting, is there any way to alias a cell to a name? The same as if you entered text into the ‘name box’ in the upper left corner of Excel where it usually shows the cell coordinates. Thanks Again!

2

Hi, thanks for your consideration.

Aspose.Excel support the feature you requested.
In your case, if you haven’t create a worksheet named “Advertising”, Aspose.Excel will reject your formula. So you should create the worksheet in your designer file or using API.

For example:

Excel excel = new Excel();
int index = excel.Worksheets.Add();
excel.Worksheets[index].Name = “Advertising”;
Cells cells = excel.Worksheets[0].Cells;
cells[“A1”].Formula = “=SUM(Advertising!C2:C5)”;


You can use Cells.CreateRange to create a Range object, then using Range.Name to set the alias. For example:

Range range = cells.CreateRange(“A5”, “A5”);
range.Name = “test”;

3

@miscreant,
Aspose.Excel is replaced with an advanced version named Aspose.Cells which contains all the features in the latest MS Excel. You can create ranges using the following sample code with Aspose.Cells.

/Instantiating a Workbook object
Workbook workbook = new Workbook();
 
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
 
//Creating a named range
Range range = worksheet.Cells.CreateRange("A1", "B4");
 
//Setting the name of the named range
range.Name = "Test_Range";
 
for (int row = 0; row < range.RowCount; row++)
{
    for (int column = 0; column < range.ColumnCount; column++)
    {
        range[row, column].PutValue("Test");
    }
}
 
//Saving the modified Excel file in default (that is Excel 2003) format
workbook.Save("C:\\Test_Range.xls");

For more information please visit the link below:
Range Class
Creating a named range

Download the latest version of Aspose.Cells for .NET from the following link:
Aspose.Cells for .NET (Latest Version)

You can download the latest demos here.