Axolot based app cannot save TMS created .xlsx

FLX TMS FlexCel for VCL & FMX
1

Hi,


Here's a weird one.

I recently replaced Axolot's Excel component with TMS FlexCel in two apps.  Everything works great, except that the following occurs:

1.) Open an .xlsx with the new copy of the app, which uses FlexCel. Replace the string contents of some cells. Resave the file and free it. Close the app.

2.) Open the same .xlsx with an older copy of the app, which uses Axolot.  Follow the same process. App cannot save to the file.

This happens only with .xlsx, not with .xls. 

Any thoughts?

Thanks.

Other than this, your product is great!

Scott
2

Hi,


It is hard to diagnose with only this data. We try to make xlsx files as similar as Excel ones as possible, but there are always differences. Could it be that the version you have of axolot didn't fully support xlsx files? Which is the exception message you get?
3

I can't tell what error is being thrown without reverting back to the older code, as we have an intercept that generates a standard message.  We have not had any problems with the Axolot-based code not being able to save - when it has run into incompatible features in an excel file it has saved a corrupted file instead of throwing an error.


We can do more digging, but thought it might be something you knew about so it would save the recoding.

Thanks.
4

Sorry, I am not aware of anything that could be causing issues. Maybe the fact that FlexCel doesn't currently save file properties could be causing the crash (if they expect file properties to be saved, even when they aren't required by the spec). But it could be anything else really.

5

I recovered the Axolot code and the error, which seems to occur only after the .xlsx file has been edited and saved using FlexCel, is "Unknown ID in workbook relationship".  


This is using Win32.

Does that help?

Thanks again for your great service.

S
6

OK, I have tracked down the crash and the cause, but not being an xlsx expert it means little to me. Maybe it does to you.


When I create an .xlsx file using Excel (v12.0.6665.5003), the workbook.xml.rels has "relationship ID"s that all begin with "rId", for example rId1, rId2, etc.

When FlexCel reads and saves the file, those now begin with "flId", for example, flId1, flId2, flId3, etc.

Original File
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>


TMS Saved:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Relationship Id="flId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml" />
<Relationship Id="flId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" />
<Relationship Id="flId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" />
<Relationship Id="flId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml" /></Relationships>


Axolot, as a rule, rewrites the relationship IDs for worksheet, styles and sharedstrings as rIds, regardless of what they start out as.  It does NOT, for some reason, rewrite the relationship ID for theme, which it creates if it does not exist, but does not touch if it does, as follows:

procedure TXMLRelationsWorkbook.AddStd(SheetCount: integer);
var
  i: integer;
begin
  BeginUpdate;

  FStdCount := SheetCount + 2;

  if not HasType('theme') then begin
    with Add('rId0') do begin
      FRelType := 'theme';
      FWorkbookRel := wrTheme;
      FNamespace := xnsOpenXML;
      FTarget := 'theme/theme1.xml';
    end;
  end;

  for i := 1 to SheetCount do begin
    with Add('rId' + IntToStr(i)) do begin
      FRelType := 'worksheet';
      FWorkbookRel := wrWorksheet;
      FNamespace := xnsOpenXML;
      FTarget := Format('worksheets/sheet%d.xml',);
    end;
  end;
  with Add('rId' + IntToStr(SheetCount + 1)) do begin
    FRelType := 'styles';
    FWorkbookRel := wrStyles;
    FNamespace := xnsOpenXML;
    FTarget := 'styles.xml';
  end;
  with Add('rId' + IntToStr(SheetCount + 2)) do begin
    FRelType := 'sharedStrings';
    FWorkbookRel := wrSharedStrings;
    FNamespace := xnsOpenXML;
    FTarget := 'sharedStrings.xml';
  end;

  SortForWrite;
  Renumber;
  EndUpdate;
end;

Then, it later reads these lines, and expects them to say rId, and errors out if they do not.  Since it did not rewrite the theme relationship line written by FlexCel, it errors here:

procedure TXMLRelationsWorkbook.Renumber;
var
  i: integer;
begin
  for i := 0 to Count - 1 do begin
    if (Copy(Items.FId,1,3) = 'rId') and (StrToIntDef(Copy(Items.FId,4,MAXINT),-1) <> -1) then
      Items.FId := 'rId' + IntToStr(i + 1)
    else
      raise Exception.Create('Unknown Id in workbook relationship');
  end;
end;

when it gets to the theme relationship.

So, wise one, what, if anything, does this mean?

Thanks.

Scott

7

Thanks for the info. Indeed I think it helps. Actually when I saw the first message I think I already had figured out what was going on.


What happens is this: In an xlsx file, evrything is connected by relationships. For example, you have a workbook, this workbook has relationships with sheets, etc. Now, the name of those relationships is completely arbitrary, Excel happens to name them "rIdN" but that's not a requirement on the xlsx file format, they just happen to be named like that. We could call the relationship "adrian" and it would be ok, as long as it relates the correct parts. You normally name those relationships with a prefix ang go numbering them 1, 2...etc as you go writing the parts. But again, this isn't a requirement, and Excel will normally also change all ids if it saves the parts in a different order.

Now, FlexCel does indeed save its relationships as "flIdN" instead of "rIdN". This is done to avoid possible crashes with Excel own ids. That is, flexcel might decide to save a part it doesn't know about (it might be even a future part, defined by Excel 2015 or whatever). It reads the full xml without knowing what's going on, and saves it. Now, that part has an id "rid5", which will be saved to the final file. But maybe we also saved a completely unrelated "rId5" somewhere else, and that would make the file invalid. (you would have now 2 different and unrelated rid5)

So we save our own relationships as "flId" instead, to make sure we don't clash with Excel's own Ids. This is perfectly valid and encouraged, but it seems that Axolot has hardcoded "rIdN" as the only possible name for relationships. Excel happens to use "rId", but that's just a coincidence. As said, according to the xlsx spec, those relationships can be named as you please, it shouldn't matter at all.

Now, to fix this, as said the real solution would be to change axolot so it reads any relationship. But if you need a workaround, we could add a property "UseRId" or something that would make FlexCel use "rId" instead of "flId" for its relationships. Currently, flxId is defined in a const, but we could make it writeable so you can shoose your own prefix. Would that help?  
8

Just for the record, we' ve already added a new property "XlsxExtraCompatibility" that if you set to true, will use rID instead of flId for the relationships. Setting it to true will mean the possibility of some id clash with Excel's own ids in the future, but this is unlikely anyway, and well, this property should help you workaround this problem. 

9

forgot to say, this property will be available in FlexCel 6, that if there aren't any other delays (which I hope there won't) will be available tomorrow.

10

Thanks for your prompt and detailed response. In my case, the only time this is an issue is when the user has two of our apps and has only updated the one that is normally run first, so that the file is first modified by FlexCel in the updated app and then attempted to be modified and saved in a non-updated copy of the other app.  That actually would happen often because of the way the apps interact (the first calls the second under some common circumstances), but it is easily fixed by always requiring the second app to be updated when the first one is.  The problem will go away, as the apps must be updated regularly to stay in compliance with the requirements that they help the user meet.


Since Excel has no problem reading the files that FlexCel saves, and my users will be receiving Excel files from an unlimited number of sources, it seems like leaving the FlexCel setting as it is would be the "most compatible" way to go.  Do you agree? 

Thanks again.

Scott
11

Hi,

Indeed, if it isn't a problem for your workflow, it is best to keep the ids different for FlexCel, so we are 100% sure they won't clash with the ones in Excel. But it is mostly a theoretical issue, in reality, it will most likely be the same. So if you need to set XlsExtraCompatibility = true it will work too.