CellsException: Invalid formula

Aspose.Cells Product Family
1

Hi,

I am using aspose-cells-8.1.0.jar.
I want range from the a worksheet so i use below code with range specification as
'Allowed ‘();@&=+$,%#’!r1c1:r10c10

But response exception i get :
com.aspose.cells.CellsException: Invalid formula."'Allowed ‘();@&=+$,%#’!r1c1:r10c10"


private Range createR1C1PatternBasedRange(Workbook workbook, String R1C1pattern/* =‘Sheet1’!R1C1:R10C12 */) {
try {
int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);
Name name = workbook.getWorksheets().getNames().get(rangeIndex);
name.setR1C1RefersTo("'Allowed ‘();@&=+$,%#’!r1c1:r10c10");
name = workbook.getWorksheets().getNames().get(rangeIndex);
return name.getRange();
} catch (Exception e) {
return null;
}
}

Attached is the workbook with special charatcters in sheets.
& I am referring to second sheet.

Thanks,
Jaspreet


2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing the following code using the latest version: Aspose.Cells
for Java v8.2.0.1. The exception occurs because of special characters in the worksheet name.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40971.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Copy+1.xlsx”;


Workbook workbook = new Workbook(filePath);


int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);

Name name = workbook.getWorksheets().getNames().get(rangeIndex);

name.setR1C1RefersTo(“'Allowed ‘();@&=+$,%#’!r1c1:r10c10”);

name = workbook.getWorksheets().getNames().get(rangeIndex);

Exception:
Exception in thread “main” com.aspose.cells.CellsException: Invalid formula.“'Allowed ‘();@&=+$,%#’!r1c1:r10c10”.
at com.aspose.cells.us.a(Unknown Source)
at com.aspose.cells.Name.setRefersTo(Unknown Source)
at com.aspose.cells.Name.setR1C1RefersTo(Unknown Source)
at ClsAsposeTest.f1(ClsAsposeTest.java:412)
at ClsAsposeTest.main(ClsAsposeTest.java:47)

3

Hi,


Please change the line of code:
i.e.,
name.setR1C1RefersTo("'Allowed ‘();@&=+$,%#’!r1c1:r10c10");

with:
name.setR1C1RefersTo("‘Allowed ‘’();@&=+$,%#’!r1c1:r10c10");

it works fine as I tested.

Thank you.
4

Hi,

I tried as mentioned above but it didnt work or may be there is some other issue.

I have a workbook with sheet name Allo’wed.
How do i create RC pattern range as shown in the example below :


private Range createR1C1PatternBasedRange(Workbook workbook) {
try {
int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);
Name name = workbook.getWorksheets().getNames().get(rangeIndex);
name.setR1C1RefersTo("‘Allo’wed’!r1c4:r5c5");
name = workbook.getWorksheets().getNames().get(rangeIndex);
return name.getRange();
} catch (Exception e) {
return null;
}
}



Thanks,
Jaspreet
5

Hi,


Thanks for the sample code and template file.

After an initial test, I observed the issue as you mentioned. I used the following sample
code with your newly attached template file. I noticed MS Excel prompts
“Excel found unreadable content…” error when opening the output file
into it.

e.g


Sample code:


String filePath = "Book1_Allowed.xlsx";

Workbook workbook = new Workbook(filePath);

int rangeIndex = workbook.getWorksheets().getNames().add("dummyrange");
Name name = workbook.getWorksheets().getNames().get(rangeIndex);

name.setR1C1RefersTo("'Allo''wed'!R3C1:R9C4");
name = workbook.getWorksheets().getNames().get(rangeIndex);

workbook.save("out1.xlsx");


I have logged a separate ticket with an id "CELLSJAVA-41042" for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
6

Hi,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.2.1.4 and let us know your feedback.

7

The issues you have found earlier (filed as CELLSJAVA-41042) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
8

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan