Range Values from Array

Aspose.Cells Product Family
1

is there a way in Aspose to import an array into a named range?

in vba this could be done by Range("ForcstPeriodHeaders") =headerArray

2

Hi,

Currently, Aspose.Cells does not support to use VBA. So, you can only create a named range based on a range of cells of the worksheet. And you can also use Cells.ImportArray() to import an array into the cells of the worksheet.

Thank you.

3

So how can I find the cells that are in a named range?

4

Hi,

Well, I write the following code for you which may help you for your need.

Workbook workbook = new Workbook();

Worksheet worksheet1 = workbook.Worksheets[0];

Range range = worksheet1.Cells.CreateRange("H1", "J4");//there are 4 * 3 = 12 cells in the range.

range.Name = "MyRange";

// range vertices are as follows.

range[0,0].PutValue("USA");

range[0,1].PutValue("SA");

range[0,2].PutValue("Israel");

range[1,0].PutValue("UK");

range[1,1].PutValue("AUS");

range[1,2].PutValue("Pak");

range[2,0].PutValue("France");

range[2,1].PutValue("India");

range[2,2].PutValue("Egypt");

range[3,0].PutValue("China");

range[3,1].PutValue("Philipine");

range[3,2].PutValue("Brazil");

MessageBox.Show(range.FirstColumn.ToString());// shows 7 ... as the starting col is H (index 7)

MessageBox.Show(range.FirstRow.ToString());// shows 0 ....as the starting range is H1.. so the row index is 0

MessageBox.Show(range.ColumnCount.ToString());// shows 3.... as the there are three columns involved in the range.

MessageBox.Show(range.RowCount.ToString());// shows 4 ....as there are 4 rows H1...J4...involved in it.

workbook.Save("d:\\test\\rangecells.xls");

Thank you.

5

To retrieve a named range in template file, you can use Worksheets.GetRangeByName method.

6

Perhaps I am not being clear. Going back to my original question, I need to import an array of data into a named range. since that can’t be done but I can import into Cells I need to know how to get the cells from a named range so I can then import into them from an array.

7

Hi,

Well, As long as we understand your inquiry, we have already told you how to access the cells of a named range by the example. You can access the cells in the range using Range.FirstRow, Range.FirstColumn, Range.RowCount and Range.ColumnCount properties. And you have already been told how to get the members (range[0,0]) of a named range, if you have already created the named range in your template file, you can use Worsheets.GetRangeByName() method which returns a Range object.

You can also try to use any suitable version of overloaded method Cells.ImportArray() to import data from the array for your need.

I try again and write another example, May it explain you, which accesses the range and then filled that range from an array of string. (Note: your range should be either horizontal (H1:S1) / vertical (H1:H12), otherwise you should manually input the member values of an array to the cells of range ):

Workbook workbook = new Workbook();

Worksheet worksheet1 = workbook.Worksheets[0];

// You can omit these two lines of code if you have already created your named range in

Range range = worksheet1.Cells.CreateRange("H1", "H12");

// your template file.

range.Name = "MyRange";

Range rng = workbook.Worksheets.GetRangeByName("MyRange");

string[] arr = new String[12];

arr[0] ="USA";

arr[1]= "SA";

arr[2]= "Israel";

arr[3]= "UK";

arr[4]= "AUS";

arr[5] = "Pak";

arrDevil [6] = "France";

arr[7] = "India";

arrMusic [8] = "Egypt";

arr[9] = "China";

arr[10] = "Philipine";

arr[11] = "Brazil";

worksheet1.Cells.ImportArray(arr,rng.FirstRow, (byte)rng.FirstColumn,true);

workbook.Save("d:\\test\\rangecells1.xls");

Thank you.