Why Excel Repairs a Valid XLSX: OPC Package Rules in Delphi

Excel shows "We found a problem with some content" on an XLSX that LibreOffice and every homegrown reader open without complaint because Excel enforces two things those readers ignore: schema-required attributes and the uniqueness rules of the Open Packaging Conventions. Three of those rules are easy to break in any writer that combines generated parts with preserved ones: a <phoneticPr> needs its fontId, each part name gets exactly one Override in [Content_Types].xml, and two root relationships must never share an identifier such as rId4. This article walks through each one and how HotXLS, the native Excel spreadsheet component for Delphi and C++Builder, writes the package to satisfy it

Why does Excel reject a package that every other reader accepts?

Because the repair prompt is a schema and package validator, not a parser failure. A workbook can round-trip through your own library, through LibreOffice, and through XML validators, and be structurally sound in the OPC sense used in the article on XLSX OPC relationship resolution: every part reachable, every target resolvable. Open it through Workbooks.Open in an isolated COM instance with DisplayAlerts off and the call can still fail outright. Interactively the same file produces the familiar dialog offering to repair, and the repair log, when Excel bothers to write one, names the part but not the rule. Several independent violations can hide behind that one prompt, and Excel does not report them one at a time; it rejects the workbook and leaves you to find and analyse them. What follows is each rule, the natural way to violate it, and how HotXLS writes the package instead, because every one of them is a rule any Delphi XLSX writer can trip over

Rule 1: phoneticPr fontId is required, even when it is zero

The <phoneticPr> element carries a fontId attribute declared use="required" in ECMA-376 Part 1 §18.4.3, and a value of 0 is a legal font index, not an absence. A writer that treats zero as "unset" and only emits the attribute when Sheet.PhoneticFontId > 0 follows a natural Delphi reflex, since integer fields default to zero, but it produces <phoneticPr type="noConversion"/> for any workbook whose phonetic font happens to be the first font in styles.xml, which is easy to hit because index 0 is simply the first font. Excel then rejects, on the way back in, a value it wrote itself

// The natural reflex: zero treated as unset, fontId dropped
phoneticXml:= '<phoneticPr';
if Sheet.PhoneticFontId > 0 then
  phoneticXml:= phoneticXml+ ' fontId="'+ IntToStr(Sheet.PhoneticFontId)+ '"';

// lxHandleX.pas, worksheet writer — the attribute is required, zero included
phoneticXml:= '<phoneticPr fontId="'+ IntToStr(Sheet.PhoneticFontId)+ '"';
phoneticXml:= phoneticXml+ ' type="'+ XlsxEscapeAttr(Sheet.PhoneticType)+ '"';
if Sheet.PhoneticAlignment<> '' then
  phoneticXml:= phoneticXml+ ' alignment="'+ XlsxEscapeAttr(Sheet.PhoneticAlignment)+ '"';

HotXLS emits the element only when TXLSXWorksheet.PhoneticType is non-empty, so workbooks that never carried phonetic settings are unaffected. Set PhoneticFontId to zero on a fresh sheet, save, and xl/worksheets/sheet1.xml carries <phoneticPr fontId="0". The wider lesson is that "omit when default" is only safe when the schema declares a default; type and alignment have defaults in that element, fontId does not

Rule 2: one Override per part name in [Content_Types].xml

The content types stream may declare each part name at most once, and Excel treats a second Override for the same PartName as corruption even when both entries carry the same ContentType. HotXLS has two writers feeding that stream. BuildContentTypesXml declares every part the object model generates: workbook, styles, shared strings, theme, worksheets, and, when TXLSXWorkbook.CustomProperties.Count > 0, /docProps/custom.xml. When PreserveUnsupportedParts is on, TXLSXOpaquePackage then appends an Override for every part it captured verbatim from the source package so those bytes stay declared on the way out. The collision is a part that lives on both sides. Custom document properties are parsed into the model, but the source package's docProps/custom.xml is also captured opaquely, so a naive merge declares it twice, and chart and pivot cache parts can land in the same spot when the model regenerates a part the opaque layer also retained. An opaque writer with no view of what the model has already written cannot know, which is why ContentTypeOverridesXml is given that view

<!-- What a naive merge produces -->
<Override PartName="/docProps/custom.xml"
    ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/>
...
<Override PartName="/docProps/custom.xml"
    ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/>

HotXLS passes the generated XML into ContentTypeOverridesXml and lets the opaque writer parse it before it emits anything. Two details carry the correctness. OpcLowerPartName lowercases, flips backslashes to forward slashes, and strips leading slashes before comparison, because OPC part names are compared case-insensitively and the model writes them with a leading slash while the opaque layer stores ZIP item names without one. And the caller in BuildContentTypesXml passes Result + '</Types>', closing the partially built document so TXMLReader sees well-formed input rather than a truncated stream. The rule that emerges is first-wins with the model in front: whatever the object model declares is authoritative, and the opaque replay only fills gaps

// lxOpcPackage.pas — TXLSXOpaquePackage.ContentTypeOverridesXml
function TXLSXOpaquePackage.ContentTypeOverridesXml(const ExistingXml: WideString): WideString;
begin
  ...
  UsedNames.Sorted:= True;
  UsedNames.Duplicates:= dupIgnore;
  if ExistingXml<> '' then
    // Parse the model-generated stream and collect every declared PartName.
    while Reader.Read do
      if (Reader.NodeType= xmlntElement)and (Reader.Name= 'Override') then
      begin
        Index:= Reader.AttributeIndex('PartName');
        if Index>= 0 then
          UsedNames.Add(String(OpcLowerPartName(Reader.Attribute[Index].Value)));
      end;
  for i:= 0 to FParts.Count- 1 do
  begin
    Part:= TXLSXOpaquePart(FParts[i]);
    if (Part.ContentType= '')or (LowerCase(ExtractFileExt(String(Part.PartName)))= '.rels')or
      (UsedNames.IndexOf(String(OpcLowerPartName(Part.PartName)))>= 0) then
      Continue;                       // already declared, or a rels part
    UsedNames.Add(String(OpcLowerPartName(Part.PartName)));
    Result:= Result+ '<Override PartName="/'+ OpcXmlEscapeAttribute(Part.PartName)+
      '" ContentType="'+ OpcXmlEscapeAttribute(Part.ContentType)+ '"/>';
  end;
end;

Rule 3: relationship Ids are unique within a relationships part

Every Relationship in a .rels part needs an Id that is unique within that part, and Excel refuses the package when two share one. HotXLS writes the package-level _rels/.rels with fixed identifiers: rId1 for the workbook, rId2 and rId3 for core and extended document properties, and rId4 for custom properties when the model has any. The opaque package then appends whatever root relationships it retained from the source, renumbering any identifier already in a UsedIds list. If that list knows only rId1 through rId3, and not that the model is about to emit its own custom-properties relationship, a source package whose custom-properties relationship is also rId4, which is what Excel writes by default, comes out with two rId4 entries pointing at the same target. RootRelationshipsXml therefore reserves rId4 and skips the source copy whenever the model emits custom properties. The caller, BuildRootRelsXml, passes Workbook.FCustomProps.Count > 0 as the second argument, so the reservation and the skip are driven by the same condition that decides whether the model emits rId4 at all. Renumbering is safe at the package root because nothing inside the workbook references root relationship identifiers by name; the same trick would be wrong one level down, where r:id attributes in workbook.xml bind to identifiers in the workbook relationships part, which is why MergeWorkbookRelationshipsXml keeps a separate identifier map

// lxOpcPackage.pas — TXLSXOpaquePackage.RootRelationshipsXml
UsedIds.CaseSensitive:= False;
UsedIds.Add('rId1');
if EmitCustomProps then UsedIds.Add('rId4');   // reserved by the model writer
if EmitDocProps then
begin
  UsedIds.Add('rId2');
  UsedIds.Add('rId3');
end;
for i:= 0 to FRootRelationships.Count- 1 do
begin
  Rel:= TXLSXOpaqueRelationship(FRootRelationships[i]);
  // The model owns custom properties; do not replay the source copy.
  if EmitCustomProps and OpcEndsWith(LowerCase(Rel.RelType), '/custom-properties') then
    Continue;
  Id:= Rel.Id;
  if (Id= '')or (UsedIds.IndexOf(String(Id))>= 0) then
    Id:= AllocateRelationshipId(UsedIds);      // lowest free rIdN
  UsedIds.Add(String(Id));
  ...
end;

What do the three rules have in common?

All three guard against a writer with two sources and no single owner of the package invariants. The object model generates parts it understands; the opaque layer replays parts it does not, so that a round trip keeps charts, pivot caches, custom XML, and everything else described in the notes on lossless round-trip of theme, extLst, and calcChain. Each side can be individually consistent. The constraints OPC puts on the whole package, unique Override part names and unique relationship identifiers per part, only exist at the seam where the two are concatenated, so something has to own the seam. The fontId rule is the same shape one level down: a writer can know what it wants to omit and never consult the schema that says it may not. HotXLS settles this with a fixed precedence rather than a merge heuristic. The model writes first, and the opaque layer sees what was written and yields on any collision. The invariants are also cheap to check from the outside: read [Content_Types].xml and every .rels item in a saved package and fail on any duplicate PartName, Extension, or Id. That check needs no Excel and covers the two uniqueness rules; the fontId rule still needs a schema validator

Print areas that are formulas, not ranges

The print area is the other thing Excel compares on a saved copy: a template whose _xlnm.Print_Area Excel reports as $A$1:$J$29 has to report identically after a round trip. Two traps sit behind that. On import, a prefix strip that cuts everything up to the first unquoted ! turns a dynamic print area such as OFFSET('Print Data'!$A$1,0,0,2,2) into $A$1,0,0,2,2), and makes a qualified union such as 'Print Data'!$A$1:$B$2,'Print Data'!$D$1:$E$2 lose the prefix on its first segment only. On export, prepending the sheet name once to the whole stored PrintArea gives a plain union $A$1:$B$2,$D$1:$E$2 a qualified first segment and a bare second one, which Excel does not accept as a _xlnm.Print_Area definition under ECMA-376 Part 1 §18.2.5

// Import: only strip the prefix when what remains is a plain sqref
function XlsxPrintAreaFromDefinition(const Formula: WideString): WideString;
begin
  Result:= Formula;
  if not XlsxReadFormulaSheetPrefixAt(Formula, 1, Prefix, SheetPart, Start) then
    Exit;
  Area:= Copy(Formula, Start, Length(Formula));
  if XlsxParseSqrefPart(Area, R1, C1, R2, C2) then
    Result:= Area;                    // 'Sheet'!$A$1:$J$29 -> $A$1:$J$29
end;                                  // OFFSET(...) is returned untouched

// Export: qualify every comma-separated segment, or none of them
function XlsxPrintAreaDefinition(const SheetName, Area: WideString): WideString;
begin
  Result:= Area;
  ... split Area on ',' with StrictDelimiter ...
  for I:= 0 to Parts.Count- 1 do
    if not XlsxParseSqrefPart(WideString(Trim(Parts[I])), R1, C1, R2, C2) then
      Exit;                           // a formula: emit verbatim
  Result:= '';
  for I:= 0 to Parts.Count- 1 do
  begin
    if I> 0 then Result:= Result+ ',';
    Result:= Result+ XlsxQuoteSheetName(SheetName)+ '!'+ WideString(Trim(Parts[I]));
  end;
end;

The pairing rule is the same on both sides: a print area is a bare range only if every segment parses as one, otherwise it is a formula and travels verbatim. With that rule, the named base, the sheet-qualified base, and the union all survive repeated save-and-reopen cycles. How print areas interact with page setup and the rest of the printing model is covered in the article on sheet protection, page setup, and printing

How do you find which rule Excel is objecting to?

Start from the assumption that your own validator is wrong, because it passed. The Open XML SDK validator will name a schema violation like the missing fontId with the part and the XPath, and the packaging layer underneath it refuses to open a package with duplicate content-type entries at all, so run it before anything else. When it is silent and Excel still repairs, bisect the package: unzip, delete a part and its relationship and its Override, rezip, and reopen, halving the candidate set each time until the prompt disappears. The three violations above surface in that order, and none of them would be visible in the repaired file Excel offers to save, since the repair silently drops or renumbers the offending entries. Two design choices in the HotXLS writer are worth knowing. The deduplication is first-wins with the model in front, so if the source package declared a different content type for a part the model also generates, the model's declaration wins and the source's is discarded, which is correct for the parts HotXLS regenerates and is not a general merge. And the extra TXMLReader pass over the generated content types stream runs on every save with PreserveUnsupportedParts enabled, a small cost against a stream that is rarely more than a few kilobytes. With these rules in place, a 4805-formula loan template saved by both the Win32 and Win64 builds opens in Excel without a prompt, recalculates with zero mismatches, and reports the same print area as the original

If you are writing XLSX from Delphi yourself, the checklist is short: emit every attribute the schema marks required regardless of its value, declare each part name once, and keep one list of used identifiers per relationships part across every writer that touches it. If you would rather that list already exist and be tested against Excel rather than only against your own reader, the package writer described here ships in the HotXLS Delphi spreadsheet component, together with the opaque-part round-trip that made the seam worth guarding in the first place