List All Named Ranges and insert row

[kumar_1981]

Hi

i need to get all range names in an excel sheet as i am working with charts.i need to get rows of a named range table and insert rows into it.

if i am considering sheet it is displaying all the rows included charts which are in another namedrange.



please help me in resolving this


Thanks and Regards
Vinay kumar

[Hack]

Are you doing this in C# or in Excel VBA?

[kumar_1981]

i am doing it in c#.

[mendhak]

I don't know much about using Excel with C#, but I know enough to point you in a vaguely correct direction.

The page I found however was not 'working' at the time, so I'm just pasting the text here

Accessing All Named Ranges in the Worksheets

Developers can call GetNamedRanges method of the Worksheets collection to get all named ranges in a spreadsheet. GetNamedRanges method returns an array of all names ranges in the Worksheets collection.

Example:

The following example shows how to access all the named ranges in a workbook.

 [C#]


 //Getting all named ranges
 Range[] range = workbook.Worksheets.GetNamedRanges();


 [Visual Basic]


 'Getting all named ranges 
 Dim range() As Range =  workbook.Worksheets.GetNamedRanges()


 [JAVA]
 //Getting all named ranges
 NamedRange[] namedRanges = worksheets.getNamedRanges();


 //Getting all named ranges of the specific worksheet
 NamedRange[] namedRanges1 = sheet.getNamedRanges();

Accessing a Specific Named Range

Developers can call GetRangeByName method of the Worksheets collection to get a range by the specified name. A typical GetRangeByName method takes the name of the named range and returns the specified named range as an instance of Range class.

Example:

The following example shows how to access a specified range by its name.

 [C#]


 //Getting the specified named range
 Range range = workbook.Worksheets.GetRangeByName("TestRange");


 [Visual Basic]


 'Getting the specified named range
 Dim range as Range = workbook.Worksheets.GetRangeByName("TestRange")


 [JAVA]
 //Getting the specified named range
 NamedRange namedRange = worksheets.getRangeByName("TestRange");

Identifying Cells in the Range and Inserting Data into the Individual Cells in a Specific Range

You can insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2]. Aspose.Cells provides you some useful Properties of Range class to access the individual cells in the range. You may use the following properties to identify the cells in the ranage:

    * FirstRow returns the index of the first row in the named range.
    * FirstColumn returns the index of the first column in the named range.
    * RowCount returns the total number of rows in the named range.
    * ColumnCount returns the toal number of columns in the named range.

Example:

The following example shows how to input some values into the cells of a specified range.

 [C#]


 //Instantiate a new Workbook.
 Workbook workbook = new Workbook();
 //Get the first worksheet in the workbook.        
 Worksheet worksheet1 = workbook.Worksheets[0];
 //Create a range of cells based on H1:J4.
 Range range = worksheet1.Cells.CreateRange("H1", "J4");
 //Name the range.        
 range.Name = "MyRange";
 //Input some data into cells in the range.        
 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("Canada");
 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");
 //Identify range cells.
 int firstrow = range.FirstRow;
 int firstcol = range.FirstColumn;
 int trows = range.RowCount        
 int tcols = range.ColumnCount;
 //Save the excel file.
 workbook.Save("d:\\test\\rangecells.xls");


 [VB]


 'Instantiate a new Workbook.
 Dim workbook As Workbook = New Workbook()
 'Get the first worksheet in the workbook.        
 Dim worksheet1 As Worksheet = workbook.Worksheets(0)
 'Create a range of cells based on H1:J4.
 Dim range As Range = worksheet1.Cells.CreateRange("H1", "J4")
 'Name the range.        
 range.Name = "MyRange"
 'Input some data into cells in the range.        
 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("Canada")
 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")
 'Identify range cells.
 Dim firstrow As Integer = range.FirstRow
 Dim firstcol As Integer = range.FirstColumn
 Dim trows As Integer = range.RowCount        
 Dim tcols As Integer = range.ColumnCount
 'Save the excel file.
 workbook.Save("d:\test\rangecells.xls")

[kumar_1981]

Thanks for the reply.

Regards
Vinay Kumar

[mendhak]

No prob, glad (and surprised) it actually helped.