Problems with Essential Studio 3.2.1.0

[Mike Adewole]

We are having the following issues with Calculate in our Essential Studio 3.2.1.0:

(1) How do we preserve spaces in worksheet names ? The spaces are completely ignored and using single quotes like in Excel didn''t work.

(2) How do we add a formula to CalcEngine using R1C1 notation ? The engine seems to be always using the C1 part and ignoring the R1 part.

PS: I have submitted this issue through Direct-Trac but wanted to post it here too just in case we get a faster response this way.

Thanks

[Administrator]

1) How are you seeing the problem? Are you trying to read an XLS file into an ExcelRWCalcWorkbook class as in our \Syncfusion\Essential Studio\3.3.0.0\Windows\Calculate.Windows\Samples\XlsFileUsingExcelRW sample? I went into the XLS file used in that sample and changed the Output sheet to have the name Out put (with a space). I then changed all occurrences of Output to Out put in the Fo1m1 file. The program seemed to work ok with these changes. Can you upload a sample project showing the problem you are seeing?

2) CalcEngine does not support the R1C1 notation. It uses the A1 notation provided you are using an object on which you have implemented ICalcData.

[Mike Adewole]

We are using CalcSheet and CalcWorkbook. In one or more CalcSheets there are formulas like "Sheet X!A1" and "SheetX!A1" (note the spaces before X in the first formula and their absence in the second formula).

But Calculate treats "Sheet X" and "SheetX" as the same sheets. We want them to be treated as different sheets.

In Excel or OWC, we use ''Sheet X''!A1 and SheetX!A1 but this didn''t work with Essential Calculate.

[Administrator]

Multiple sheetnames that differ only by spaces get hashed to the same sheet token which is causing the problem you are seeing. I know of no way around this problem without addressing it in our source code. Would it be possible to replace the space with an underscore in your sheet names?

[Mike Adewole]

We already considered using underscore but had to reject this option because the sheetnames could appear in complex formulas and we''d rather not have to parse each formula just to solve this problem.

Also, when the calculation is completed, we''d have to parse each formula again to replace the underscores with spaces to make the transformation transparent to our users.

In short, the best option for us will be a fix to your code that allows sheetnames to be quoted in formulas like ''My Sheet X''!A1 or [My Sheet X]!A1.

PS: this is a rather important requirement for us, so a proper fix will be greatly appreciated.

Thanx a lot.

[Administrator]

I have made this a feature request.
http://www.syncfusion.com/support/features/calculate/Default.aspx?ToDo=view&questId=541

Modifying our code to make spaces significant in sheetnames will be non-trivial, and will require robust testing after the change.

Are you using Excel to create these spreadsheets? If you change the sheet names in Excel, then all the formulas will be adjusted for you automatically. You would not have to retype a single formula - only change the sheet name.

If you are not using Excel, and have control over the formulas as they are being loaded into the CalcSheet, then flipping the spaces to underscores would be a pretty straight forward work around.

One off the wall comment is using sheetnames that only vary by spaces in long complicated formulas is likely to be a user headache.

[Mike Adewole]

>I have made this a feature request.
>http://www.syncfusion.com/support/features/calculate/Default.aspx?ToDo=view&questId=541
>
>Modifying our code to make spaces significant in sheetnames will be non-trivial, and will require robust testing after the change.
>
Thanks a lot. Any idea when we can get hold of this fix ?

>Are you using Excel to create these spreadsheets? If you change the sheet names in Excel, then all the formulas will be adjusted for you automatically. You would not have to retype a single formula - only change the sheet name.
>
Nope. We are using a third party grid control in addition to Calculate to create our own spreadsheet.

>If you are not using Excel, and have control over the formulas as they are being loaded into the CalcSheet, then flipping the spaces to underscores would be a pretty straight forward work around.
>
We''d have to parse each formula on the way in and on the way out from CalcSheet in order to make the process transparent to our users. Not acceptable to our stakeholders :)

>One off the wall comment is using sheetnames that only vary by spaces in long complicated formulas is likely to be a user headache.
>
True except that the worksheets are named by our users (not by us) and we can''t argue with our users.

Thanks a lot.

[Administrator]

We have a proposed fix in our code now. It will be tested as part of the next public release. (I do not have a date yet.) It is a change to parsing code, so we have to take care to try to spot unforseen problems that it may affect.

[Mike Adewole]

Thanks a lot, Clay.

We look forward to getting this fix real quick(tm).