Transition Formula Evaluation support

Hi,


We want to use Aspose.Cells in a new project. The following requirement is mandatory:
- support “Transition formula evaluation” option

We did not found this feature in Aspose.Cell Worksheet options. MS Excel has this feature in “Tools\Options\Transition\Transition formula evaluation” and applies it per sheet. Client wants to have valid formlas like “=A1+A2+A3”, for example A1 contains number 12, A2 - text “test” and A3 - number 22. If “Transition formula evaluation” option is checked (Lotus 1-2-3 rules mode) this formula returns 34 because text or blanks or spaces in cells are treated as 0 in this mode. If “Transition formula evaluation” option is unchecked (standard MS Excel rules mode) formula returns “#VALUE!”.

Do you support this feature?
Do you have this option in TODO list for implementation in near time frame?

Thank you.

MSDN states the following:

Microsoft Excel Transition Options

Some Excel spreadsheet operations — such as calculating formulas, using the keyboard, and entering dates — work differently from those in other spreadsheet applications. However, Excel lets you decide how you want these features to work. You can select either the standard Excel operation or the operation that matches Lotus 1-2-3 and other Lotus 1-2-3-compatible spreadsheet applications.

Transition Formula Evaluation

Excel and Lotus 1-2-3 evaluate certain formulas and expressions differently. Transition formula evaluation allows Excel to calculate formulas and database criteria according to Lotus 1-2-3 rules.

To use Lotus 1-2-3 rules to calculate formulas and database criteria
  1. On the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.

  2. Under Sheet Options, select the Transition formula evaluation check box.

The following table compares expressions that are evaluated differently in Excel and Lotus 1-2-3.

This expression

Is evaluated this way in Lotus 1-2-3

And this way in Excel

Cells that contain text

When the cell is used in a formula, it is given a value of zero (0).

The cell containing text is ignored in the calculation. In Excel, you cannot combine text and numeric entries in the same formula.

Boolean expressions

Boolean expressions are evaluated to 0 or 1 and display 0 or 1 in the cell. For example, 2<3 shows a 1 in the cell to represent True.

Excel also calculates Boolean expressions as 0 and 1, but displays FALSE or TRUE, respectively, in the cell.

Note also the following differences:

  • In Excel, database criteria ranges are evaluated differently when you are extracting data, finding data, and using database functions.

    For example, computed criteria can use existing field names.

  • Certain Lotus 1-2-3 functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently than the equivalent Excel functions.

    For example, the Lotus 1-2-3 @VLOOKUP function performs literal matches on text, whereas the Excel VLOOKUP function returns a lookup value for nonliteral text, using the nearest entry in alphabetic order.

When you open a Lotus 1-2-3 worksheet in Excel, transition formula evaluation is automatically turned on for that sheet. This condition ensures that the formulas are calculated according to the preceding Lotus 1-2-3 rules.

If you save the Lotus 1-2-3 worksheet as an Excel workbook, the transition formula evaluation option remains turned on until you turn it off. For workbooks created in Excel, however, transition formula evaluation is not automatically turned on.

Note Avoid turning transition formula evaluation on and off while working with a document in Excel; otherwise, the values calculated on your worksheet might change. If you leave transition formula evaluation turned off, your worksheet adheres to Excel rules. If you leave the option turned on, your worksheet adheres to Lotus 1-2-3 rules.



Hi,

Thanks for your posting and using Aspose.Cells for Java.

We need to analyze your requirements. Could you please provide us your sample source xls/xlsx file and screenshots for review?

It will help us quickly sort out your issue and we will help you asap.

We have also logged your issue in our database. 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-40221.

Hi,


I attached screenshoots and test book, please review.

Thanks.

Hi,

Thanks for providing us requested files.

These will be helpful in investigating this issue.

We will get back to you asap.

Hi,

We should prepare final draft of the project estimation to the client (include Aspose.Cells usage).

Could you provide any time frame for implementation of "Transition Formula Evaluation" option?

Thanks.

Hi,

Thanks for your posting.

I have added your comment against the issue id: CELLSJAVA-40221

We will look into it and provide you ETA asap.

Hi,

We could not support this feature soon.

But we can try to fix the
issues.

For example: we have fixed the issue of calculating the formula
“=A1+A2+A3”, we will provide a fix soon.

If you face any issue, please post
your template file.

Hi,


The main feature that we want to have is the following:
- “cells that contain text are considered to have a value of 0 (zero) when the cell
is used in a formula”,

“text” in this statement implies any non-numeric data include spaces and empty cells, “formula” implies any arithmetic operations include parameter passing to Excel functions that takes numeric arguments, e.g. SUM, ROUND, MAX etc.

Have I got right that you plan implement this feature?
Could you provide any details or scope of planned implementation?

Thanks.

Hi,


" - “cells that contain text are considered to have a value of 0 (zero) when the cell is used in a formula”,"

Yes, we will try to fix the issue accordingly.

It is already in our plan to implement the feature, we have logged a ticket for it into our Issue tracking system as you already know. It might take some time based on the complexity of the feature. Once we have any update on it, we will let you know.

Thank you.

Hi,

Thank you for using Aspose.Cells for Java.

Please download and try the latest fix: Aspose.Cells for Java v7.2.2.4

We have supported the setting for transition formula evaluation. Please see the code example below.

Java

sheet.setTransitionEvaluation(true);

wb.calculateFormula();


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


This message was posted using Notification2Forum from Downloads module by aspose.notifier.