Empty formula cells in readonly mode

[Jitsuryoku]

I generate excel files and allow users to download them from webdynpro application (download from external source). Opening files like that in read-only mode (default setting in our company for all external office files) prevents the formula cell from being calculated and getting a value, which leaves it blank.

To test this you need a abap2xlsx generated excel file with a simple formula like "if(1=1, "true", "false")".
Use any method to open that file in read only mode. Another test option (less reliable) is open the generated xlsx (with a formula) and try to close it if you are prompted to save the file, without having changed anything, it means formula updated some fields.

I checked workbook.xml for both MS Excel variant (formed manually) and abap2xlsx variant of the same form. Formulas formed with MS excel contain both the formula and a value in a cell (default use case with only 1 cell), however forming it using abap2xlsx only leaves formula field. I tried inputting the value portion of the cell manually into workbook.xml and that worked.

Is there any chance we can hope for a method to set formula with initial value specified? In most cases I can calculate the initial value before writing it and I can actually specify it right away, but in others it would be nice to write: "Open in edit mode to calculate" instead of having a blank cell.

[gregorwolf]

We are happy to merge your pull request when you implement your required feature.

[Jitsuryoku]

I actually found 2 potential changes I could make implement this, but I wanted to ask for a second opinion on this because it's a choice between a safe (for existing projects) option that is added on top of existing stuff and a change in general rule, that seems more intuitive to use, but will break projects relying on existing rule.

The problem:
The check is performed in Excel Writer 2007 (CREATE_XL_SHEET_DATA) and the code is actually written to ignore values if a formula exists.

    IF <ls_sheet_content>-cell_formula IS NOT INITIAL.
        "process and write formula
    ELSEIF <ls_sheet_content>-cell_value IS NOT INITIAL AND <ls_sheet_content>-cell_value <> lc_dummy_cell_content.
       "Process and write cell
    ENDIF.

Potential solutions
I could think of 2 changes that would enable this functionality, but both come with drawbacks.

1. A change in general rule. Change the conditions for formula and value to be processed separately, so that both value and formula are written if they are not initial.
   I find this option to be intuitive in terms of usage because whatever is specified is written, but at the same time any projects that rely on that rule to ignore values will suffer.
2. Add a flag 'FORCE_FORMULA_VALUE' to sheet_content table that would indicate that value needs to be written regardless of the formula and, in CREATE_XL_SHEET_DATA method (and any other methods writing values) check it to see if value needs to be written even if formula exists.
   This slaps the new logic on top to keep old logic intact, but adds an an arbitrary field in content table to manage, but keeps existing projects unaffected.