Open and save XLSM/XLTM files

Hi everyone.

I have some codes to open, fill data and save XLSM/XLTM files. But, after save the file, the Microsoft Excel show me some errors while opening the file.


Translating: We found one problem with the 'REPORT_grading_bulgaria_20191219171235628.xlsm' file content. Do you want we try to recover the maximum possible content? If you trust in this workbook source click Yes.

Even clicking Yes, a new error message is shown:


This messages shows me that the file was not completely recovered or repaired. To open the file, I used this code:

FFlexCelExport.Open(IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + 'models\grading_bg_model_barley.xlsm', TFileFormats(5), ';', 1, 1, [], TEncoding.UTF8, True);

And to save the file I'm using this code:

FFlexCelExport.Save(FFileName, TFileFormats(5));

Where FFileName receives the complete path to the file and its name, preserving the original file extension.

There's any other parametrization or method to open and save XLSM/XLTM files that avoid the errors while opening file?

Thank you.

Hi,

The code you posted should work. I would write TFileFormats.Xlsm instead of TFIleFormats(5), but it is the same.

There are 2 issues I think could be the problem here:
1. Old FlexCel versions (pre-Excel 2016) could have issues when the file was opened in Excel 2016 or newer. this was fixed long ago (before Excel 2016 was out of beta) but if you have a very old FlexCel version, this could be the cause. So if you aren't in the latest version, the first thing is to update it and check it again.

2. If you are in the latest version, then it seems like some bug in how FlexCel handles that particular file. Can you mail me the file (or any file that reproduces the error) to adrian@tmssoftware.com so I can see what is going on?
Hi,
Thanks for the files. The problem is some effect in the images which was introduced recently in Excel 2016 and that FlexCel is not processing correctly (because it is not aware of it). We are going to fix it here and send you a fix.

Hi,

An update:  We are still going to fix this in a better way for the next version, but right now the problem that is causing Excel to show those errors seems to be something you can/should fix, because we can't.

As said, there are some errors in the images which we are going to fix. But the problem triggering this errors is that you set a font with font size=0 in the file, and font sizes in Excel should go from 1 to 409 points. 

So just to confirm, if you just call:

FFlexCelExport.Open(IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + 'models\grading_bg_model_barley.xlsm', TFileFormats(5), ';', 1, 1, [], TEncoding.UTF8, True);
FFlexCelExport.Save(FFileName, TFileFormats(5));


Is the file generated ok? It is when I test it here.

If it is ok, then you need to search for .AddFont or .AddFormat calls in your code, and make sure that you have initialized all the values of the TFlxFont/TFlxFormat parameters, in special of the font size.

You should probably do something like this:

  fmt := xls.GetCellVisibleFormatDef(row, col);  //This will have the formats initialized
  fmt.SomeProperty := SomeValue
  ...
  xls.AddFormat(fmt);

To make sure fmt doesn't have uninitialized values. In the next FlexCel version we will check for this and if the value is 0 we will save a font size of 1 (because that is the minimum Excel allows), but you should be able to workaround this in your current version by making sure you don't save a 0 size font.

Ps: In the code above, I used the same as you posted. But you could simplify that code to:

FFlexCelExport.Open(IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0))) + 'models\grading_bg_model_barley.xlsm');
FFlexCelExport.Save(FFileName, TFileFormats.Xlsm);