Range by String

Aspose.Cells Product Family
1

Greetings.


There is exists some way to create Range by String value.

For example:

String rangeName = “‘Sheet1’!A3”;
Range range = workbook.createRange(rangeName);

Like this method exists for object com.aspose.cells.Cells, but it ignored Sheet name.

Best regards.


2

Hi,


Please see the document for your reference:
http://www.aspose.com/docs/display/cellsjava/Create+Workbook+%28Global%29++and++Worksheet+Scoped+Named+Ranges

Please see the following sample code on how to create Worksheet level/ scoped named range:
e.g

Sample code:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Get Worksheets collection

WorksheetCollection worksheets = workbook.getWorksheets();


//Accessing the first worksheet in the Excel file

Worksheet sheet = worksheets.get(0);


//Get worksheet Cells collection

Cells cells = sheet.getCells();


Range namedRange = cells.createRange("A1", "C10");
namedRange.setName("Sheet1!local");

//Saving the modified Excel file in default format

workbook.save(“C:\output.xls”);



3

Excuse me. I think i wrote not clear question.


For example. Assume that we have a workbook with two worksheets (Sheet1, Sheet2).


public static Range getRange(Workbook wb, String rangeName) {
//Now there is wrong implementation
return wb.getWorksheets().get(0).getCells().createRange(rangeName);
}

public void createRange() {
String fileName = “input.xlsx”;
try {
Workbook workbook = new Workbook(“D:\” + fileName);

Range r1 = getRange(workbook, “‘Sheet1’!A3”);
assertEquals(“Sheet1”, r1.getWorksheet().getName());
assertEquals(2, r1.getFirstRow());
assertEquals(0, r1.getFirstColumn());

Range r2 = getRange(workbook, “‘Sheet2’!A2”);
assertEquals(“Sheet2”, r1.getWorksheet().getName());
assertEquals(1, r1.getFirstRow());
assertEquals(0, r1.getFirstColumn());

} catch (Exception e) {
e.printStackTrace();
}
}

There is exists some way for right implement method getRange?
4

Hi,


Well, in MS Excel, there is no way to create a range with the title/name as “‘Sheet1’!A3”, you may confirm this in Ms Excel manually. I think, you may use WorksheetCollection.getRangeByName() method to accomplish your task, see the sample code below (If a range is local, you will use its name with respect to Sheet name and if a range is global use its name without anything else ):
e.g
Sample code:

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

//Get Worksheets collection
WorksheetCollection worksheets = workbook.getWorksheets();

//Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);

//Get worksheet Cells collection
Cells cells = sheet.getCells();


Range namedRange = cells.createRange(“A1”);
namedRange.setName(“Sheet1!local”);


Range myRange = worksheets.getRangeByName(“Sheet1!local”);
System.out.println(myRange.getName());


If you still find any issue, kindly do provide your template file and sample code (runnable) to show the issue, we will check your issue soon.

Thank you.
5

Thanks for quick response, but actually i don’t want to create Named range by function. I want to receive some structure with:

1) Sheet index
2) Row index
3) Col index
4) Row of end index
5) Col of end index

by String, that actually is reference to a cell or range

please check my previous message. may be getRange should returns another class.

For example. I have a string “Sheet1!A3”. I know, that string describe some cell in a book. Have i some tool to receive information about location for this cell?
6

Now i have used following method:


private static Pattern pattern = Pattern.compile("^(([^’!]+)|’(.+)’)!(\\$)?([A-Z]+)(\\$)?([1-9]+)(:(\\$)?([A-Z]+)(\\$)?([1-9]+))?$");


public static AsposeRangeRefAdapter formulaToRangeRef(Workbook wb, String formula) {
Matcher matcher = pattern.matcher(formula);
if (matcher.find()) {
String sheetName1 = matcher.group(2);
String sheetName2 = matcher.group(3);
String colName = matcher.group(5);
String rowName = matcher.group(7);
String col2Name = matcher.group(10);
String row2Name = matcher.group(12);

String sheetName = Strings.isEmpty(sheetName1) ? sheetName2 : sheetName1;

Worksheet sheet = wb.getWorksheets().get(sheetName);
if (sheet == null) {
throw new ModelException("Can’t find sheet with name " + sheetName);
}
int col = Common.getExcelColumnIndexByName(colName);
int row = Integer.parseInt(rowName)-1;
int col2 = Common.getExcelColumnIndexByName(col2Name);
int row2 = Integer.parseInt(row2Name)-1;

AsposeRangeRefAdapter ref = new AsposeRangeRefAdapter();
ref.sheet = sheet;
ref.row1 = row;
ref.col1 = col;
ref.row2 = row2;
ref.col2 = col2;

return ref;
}
return null;
}

But i think there is should be another way.

Best regards. Alexey
7

Hi,


I think you may try to use CellsHelper static class methods (the class has some useful methods) which Aspose.Cells for Java provides for your needs. You may get your desired data or information, see the sample code below with comments:
e.g
Sample code:

Workbook wb = new Workbook();
//Range String
String val = “Sheet1!$A$1:$C$10”;
//Get the first split
String [] strSplitData1 = val.split("!");
//Get the worksheet name from first array
System.out.println(“Sheet Name: " +strSplitData1[0]);

//Performing operations on the second array, replace the “$” first.
String strCellRange = strSplitData1[1].replace(”$", “”);
//Splitting by “:” - we will get two arrays.
String [] strCellRanges = strCellRange.split(":");

//Get the first row and first column from the first array.
int [] frowcol = CellsHelper.cellNameToIndex(strCellRanges[0]);
//Get the last row and last column indices from second array
int []lrowcol = CellsHelper.cellNameToIndex(strCellRanges[1]);
//Print the first array
System.out.println("First Row: " + frowcol[0]);
System.out.println("First Col: " + frowcol[1]);

//Print the second(last) array.
System.out.println("End Row: " + lrowcol[0]);
System.out.println("End Col: " + lrowcol[1]);

Hope, this helps you a bit.

Thank you.


8

Thank you very much! You are best.


Best regards. Alexey
9

Hi,


Good to know that it works for you. Feel free to contact us anytime if you have any other issue or queries, we will be happy to assist you soon.

thank you.