Excel file - Cell contains a combobox construct with Data validation

FLX TMS FlexCel for VCL & FMX
1

Hi,

I want to update the index of the combo box in a specific cell in a Excel file by code using Flexcel but I can't find any properties help to me to update it.

Some information :

  • using delphi XE10.2
  • using FlexCel Version: 7.17
  • Format of the Excel file : xlsx
  • The cell contains the combobox is construct using data validation (sheet Data on Excel) and linked to a table (see my printscreen)

image

Thanks for help
Joel THOMAS

2

Hi,

If the combobox is from data validation, then just write another value in the cell, the combo will update automatically. Note that FlexCel by default doesn't check that the data you write in the cell satisfies the data validation criteria, you can check that with TXlsFile.CheckDataValidationsInWorkbook Method | FlexCel Studio for VCL and FireMonkey documentation

Also, you can read the data validation of a cell with TXlsFile.GetDataValidation Method | FlexCel Studio for VCL and FireMonkey documentation to see for example if the valid values for the combo box,

3

Hi,

No change, the value is updated but all formula used this cell do not updated the new value link to this cell
Any idea ?
Thanks

Joel

4

Hi,
For performance reasons, FlexCel doesn't recalculate automatically each time you change a cell (it would be too slow to fill a spreadsheet if it was recalculating every time you change a value). Have you done xls.Recalc after changing the values? That should update the values of the linked formulas

5

Hi,

I had this test but no change.
Is it possible this is due to the Flexcel version ?
Because when I have test my code, I used the version before 7.17 and it worked.

Thanks
Joel

6

No, FlexCel has recalculated sheets since version 5 long, long ago. Data validations are really a layer over the cells, they don't matter for calculations or values. It doesn't matter if there is a data validation in a cell or not, what matters is what you have in the cell. You can even write a value that is not valid in the cell with FlexCel, and Excel won't complain. Data Validations are only applied when you edit the cell.

I think there should be something more basic going on here, but I am not sure what. Can you send me a small file that shows what you are seeing?

I've tried it here with the code below:

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  FlexCel.VCLSupport,
  FlexCel.Core,
  FlexCel.XlsAdapter;

begin
  var xls := TXlsFile.Create(1, true);
  try
    xls.SetCellValue(1, 1, 'a');
    xls.SetCellValue(2, 1, 'b');
    xls.SetCellValue(3, 1, 'c');
    xls.SetCellValue(4, 1, 'd');

    //Data Validation

    var Validation := TDataValidationInfo.Create(
      TDataValidationDataType.List,
      TDataValidationConditionType.Between,
       '=$A$1:$A$4',
       '',
       True,
       True,
       True,
       True, '', '',
       True, '', '',
       TDataValidationIcon.Information);
    xls.AddDataValidation(TXlsCellRange.Create(2, 2, 2, 2), Validation);

    xls.SetCellValue(3,2, TFormula.Create('="hello " & B2'));
    xls.SetCellValue(2, 2, 'b');

    xls.Recalc;
    WriteLn(xls.GetCellValue(3, 2).AsFormula.FormulaResult.AsString);


    xls.SetCellValue(2, 2, 'potato'); //this value is invalid, doesn't matter
    xls.Recalc;
    WriteLn(xls.GetCellValue(3, 2).AsFormula.FormulaResult.AsString);

    xls.Save('test.xlsx');
  finally
    xls.Free;
  end;

  readln;
end.

And it works as expected. It shows the formula value is modified:
image

And if I open the file, the value is changed as it should (even when the data validation is not even valid):

image

7

Hi again,

Last question : if my cell is a formula, I have a function to get the result value only ?
Thanks

Joel

8

You have to use CellValue.AsFormula.FormulaResult to get the formula result

9

Thanks, I will test again.
Thanks
Joel

10

btw, what I mean with that the data validation don't matter, is that if you removed the code to add the datavalidation in the code above, it would work exactly the same. The only difference when you add a data validation is that Excel shows the dropdown and verifies the value is valid when you press enter to enter a new value.

11

I used this function CellValue.AsFormula.FormulaResult.Asstring but the result is empty
The cell contains a Pourcent format and a value but when I using this function, I get an empty result
It is also a formula
Any idea ?
Thanks and by advance have a good WE.
Thanks
joel

12

GetCellValue won't return a formatted string. Does GetStringFromCell also return empty? Also make sure that xls.ActiveSheet is the correct sheet, or you might be reading the result from a different sheet.

If the problem is still there, can you send me a file that reproduces the problem so we can take a look? You can post it here or send it to adrian@tmssoftware.com if it has confidential data.

13

Hi Adrian
I will send to you next week an example. (code and source file)
I just describe below the situation :

  • I open my source file xlsx format with xls.open
  • I modify the cell with Xls.SetCellValue(4, 14, 'new code');
  • I launch xls.recalc(true) procedure
  • I check values => all result are empty
  • I save the file in another folder
  • I opened the new file with Excel => I have an automatic update for the different value : it is the result I want to have with Flexcel
  • I closed the file without saving
  • I open the new file with the same code => I havent't automatic update for the different value
  • Other test, I opened the new file with Excel and I closed the file with saving
  • I open the new file with the same code using flexcel => I have new value

If you have any idea, thanks for all
Joel

14

Hi,
The problem is with the function GETPIVOTDATA which we don't support (as FlexCel doesn't recalculate pivot tables). You can find a list of every function we support here:

https://doc.tmssoftware.com/flexcel/vcl/about/supported-excel-functions.html

And with the demo at Validating FlexCel recalculation (Delphi) | FlexCel Studio for VCL and FireMonkey documentation you can check if we support all the functions in a file. Sadly for this case GETPIVOTDATA is not supported:

image
image1510×920 94.3 KB

15

Hi Adrian

Thanks for your comeback.
Have a good day.

Joel