Free Support Forum - aspose.com

Getting the value of a range on a different sheet

Aspose.Cells Product Family
#1

dear support,


I have developed custom user defined functions which I am now intepreting in Aspose.cells.

On Sheet2, cell(A1) I have a UDF in following format “=myUDF(Sheet1!C8:C9”,…)"
I am parsing this in a string array and I get Sheet1!C8:C9 as a string

How can I get the value of this string ( ie s = “Sheet1!C8:C9” ) ???

I have tried worksheet.cells.createrange, but this returns the a range C8:C9 on the spreadsheet that I was working on ( sheet2 ).

So question.

How can I get the value of a range in teh format "Sheet1:C8:c9"

thanks for your much appreciated help




#2

Hi,


Thanks for providing us some details.

Could you provide us simple sample code (runnable) with template file(s) (if any) that you are currently using which returns “C8:C9” string. We will evaluate it and may suggest some other way around (if possible) for your task.

Thank you.
#3

thanks for looking into it.


It is a little bit complicated to share the whole project becuase I would need to pass all Excel Addins also.

The point is that I have a string X somewhere with a value "Sheet1!C8:C9"
And I want to get the value within this range.

What I do now is that I split the string X in two parts, before and after the exclamation mark.
The first part is the sheetname, the second part is the range within that sheet
Note, the sheetname might have a single quote added to it, if it contains space, such as “‘My Sheet’!C8:C9” - however I dont know if this single quote uis the same for all language settings.

Then I get to the range using
workbook.spreadhseet(Firstpart).cells.createrange(SecondPart).value

However, there should be a direct direct way to get the values referenced by “Sheet1!C8:C9” ???












#4

Hi,


Thanks for your posting and using Aspose.Cells.

This is the correct way and the only way which you are following. I have tested this issue with the following sample code and it works fine. I have also attached the sample excel file for your reference.

VB.NET
Dim wb As New Workbook(“sample.xlsx”)

Dim rangeName As String = “‘My’’’’’’ Sheet’!$C$2:$C$11”

Dim splits As String = rangeName.Split(New String() {"!"}, StringSplitOptions.None)

Dim sheetName As [String] = splits(0).Replace("’’", “’”).TrimStart(New Char() {"’“c}).TrimEnd(New Char() {”’"c})
Dim rangeCells As [String] = splits(1)

Dim sh As Worksheet = wb.Worksheets(sheetName)

Dim rng As Range = wb.Worksheets(sheetName).Cells.CreateRange(rangeCells)

Debug.WriteLine(rng)


C#
Workbook wb = new Workbook(“sample.xlsx”);

string rangeName = “‘My’’’’’’ Sheet’!$C$2:$C$11”;

String[] splits = rangeName.Split(new string[] { “!” }, StringSplitOptions.None);

String sheetName = splits[0].Replace("’’", “’”).TrimStart(new char[] { ‘’’ }).TrimEnd(new char[] { ‘’’ });
String rangeCells = splits[1];

Worksheet sh = wb.Worksheets[sheetName];

Range rng = wb.Worksheets[sheetName].Cells.CreateRange(rangeCells);

Debug.WriteLine(rng);


Console Output
Aspose.Cells.Range [ My’’’ Sheet!C2:C11 ]

#5

Ok, good to know that my approach is correct.


However, is the format ‘My Sheet’!C8:C9’ always the same for all language and regional settings ?
Maybe for some reginal settings the format is different ?

thanks for your much appreciated help,

guido


#6

Hi,


Thanks for your posting and using Aspose.Cells.

Yes, it is true for all languages because even if the excel file is in other language, Aspose.Cells will always read it in English language.