AdvGridExcel Problem

Hello Bruno i have 2 problems with this highly appreciated component
a) To allow formatted numbers written back to a *.xls chart, i want to use the event "AdvGridExcelIOCellFormat" (by double clickking within the IDE
it generates me the empty code lines for this event BUT
 the last part of its definiton which is "Format: TFlxFormat" is unknown for my compiler (Delphi XE6) why ?
b) Importing the "result of a formula" to an advStringGrid (not the formula itself!), i sometimes get "#NULL' as a result even if the excel chart  shows me that the formula is handled correctly.
best regards

a) declared in unit tmsuflxformats

b) not sure, do you have a test XLS file with which this can be reproduced? What are your AdvGridExcelIO Options settings?

Hello Bruno
a) i will have a try. Thx so far

b) trying to import from this (example)file
   import goes from the forth TabSet, ExelCell "B1"

  advgridexcelio.Options.ImportCellFormats    := True;
  advgridexcelio.Options.ImportCellProperties := True;
  advgridexcelio.Options.ImportCellSizes      := True;
  advgridexcelio.Options.ImportFormulas       := False;
  advgridexcelio.Options.ExportFormulas       := False;
  advgridexcelio.Options.ExportCellFormats    := True;
  advgridexcelio.Options.ExportCellProperties := True;

Question a) = answered, works now... Thx !

- once i've added ",tmsuflxformats" to my uses list, the event worked well
- but would be nicer for us programmers having no need to do that....
2 more things maybe also helpful for the documentation of your Excel-component:
besides you already mentioned that the "cell format" must be in english annotation
=> 2 more things must be also in English annotation, in order not to raise an exception whilst exporting
1) Function-name (eg. use "VLOOKUp" instead of "SVERWEIS")
2) separators within several functions (eg german excel uses ";" -> write ",")
wrong:   =VLOOKUP(I;A$4:B$6;2;0)';
correct: =VLOOKUP(I,A$4:B$6,2,0)';
Question b) i still have no clue why i get a "#NULL' for some imported formulas
Might be the reason, that my import *.xls file/Excel are in german and the components "Import routines" expect also only english Excel format here ??
best regards

Was your XLS file calculated?
Please note that TAdvGridExcelIO itself will just import, it will not perform the calculation itself.

Hi Bruno, yes, of course
"Calulation = Auto" is also standard for each Excel Installation

as of my other experience (see above) i could imagine that it also has to do with "English formatting"

unfortunately, even if English -> German conversion works  fine while exporting from EcxelGird -> MS-Excel

the ExcelTabsheet of a "german Excel version" does not understand "english forulated functions". Might it be that it missinterpretes "German functiosn while importing to Excel-Grid ??

best regards


About the recalculation issue, the file you linked shows fine here with the import options you mentioned. Is this the actual file you are importing, or has it been saved by Excel first?  If it was modified by Excel, then it might have had recalculated it, and this could be the reason I can't see this here.

Normally the files with #null are created by 3rd party tools which don't recalculate, as it is the case of AdvExcelIO. But in a file saved by Excel, I've never seen a problem loading formula results. (and I've seen thousands of files).

About the formula names, the localized name doesn't matter, as it isn't saved in the file at all. Formulas in an xls file are saved in binary RPN notation. To make this example clearer (or maybe to make t more complex) the formula =VLOOKUP(1;A$4:B$6;2;0)'; would be stored as this byte array:

00 00 00 00 3E 00 02 00 2A 00 00 00 FF FF 00 00 FA 0F 2D FC 16 00 1E 01 00 25 03 00 05 00 00 40 01 40 1E 02 00 1E 00 00 42 04 66 00 

Where the red "66" is the hexadecimal identifier for VLOOKUP. When you open this in an English Excel, it will see the "66" and show "VLOOKUP" While the German version will show "SVERWEIS" and the spanish version "BUSCARV".  The same way, when you write "=SVERWEIS" in a german Excel, it will convert it to 66, and will save 66 in the xls file. The localized name isn't stored anywhere in the file, and this allows that any Excel can open it, without having to know all the localized names to convert.  (As a side note, in xlsx, the names are stored instead of a byte array, but the names stored in the file are always the english names, it would be a nightmare if every localized Excel saved different names to the file).

What is happening when you export to xls is that we parse the names, and when we find the word "VLOOKUP" we write "66". Also when we read 66, we show "VLOOKUP". No conversion is going on.

 You can if you really want change the names we use: The names are stored in the file:
If you edit this file, and change this line:
  (Index:  102 ; Name: 'VLOOKUP'; MinArgCount: 3 ; MaxArgCount: 4; Volatile: False; ReturnType: fmValue; ParamType:'VRRV'),

  (Index:  102 ; Name:'SVERWEIS'; MinArgCount: 3 ; MaxArgCount: 4; Volatile: False; ReturnType: fmValue; ParamType:'VRRV'),

And you'll have localized names.  (note that the function index 102 = hex(66))

Now, coming back to the bytes that get stored for the vlookup formula above, not all of those bytes are for the RPN definition of the formula. There are 8 bytes on there which contain a double with the formula result (or a pointer to a string if the formula result is a string). If you are getting #null, this is because those 8 bytes are saved to the null identifier. We don't really parse the formula or do anything with it to show the result, we just show what is in those 8 bytes. If we are importing a null, then it is because a null is saved in those bytes.

Can you recheck with the file you sent and see if it shows null? If it does, can you send me the full code you are using?

Another thing I think might be worth mentioning. A way you could be getting those Null values is if you open the file with AdvExcelIO, then save it and open it again. AdvExcelIO uses TMS FlexCel 3 under the hood to save to xls, and FlexCel 3 doesn't recalculate: It will write null in all formula results, and leave the formulas so Excel recalculates when you open the file. So if you open->save->open you'll see null in the formulas.

The new AdvGridFilters which use FlexCel 6 instead of 3 don't have this problem, since FlexCel 6 has a recalculation engine which supports over 300 functions and it will recalculate before saving, so it will store the formula results besides the formula RPN data.

Hello Adrian

after testing around a bit: It is exactly as you said. As i save and re-open without Excel within (that is exactly what i need.....) i got the Null values.
Thx also for the other explanations
Now i either try to find a solution for my problem "without interperting forula results" whilst importing with AdvExcelIO and the Flexcel 3 engine,
or maybe even use  FlexCel 6