Regras OPC que fazem o Excel reparar um XLSX em Delphi

O Excel mostra «Encontrámos um problema com algum conteúdo» num XLSX que o LibreOffice e qualquer leitor caseiro abrem sem queixas, porque o Excel impõe duas coisas que esses leitores ignoram: atributos exigidos pelo esquema e as regras de unicidade das Open Packaging Conventions. O HotXLS, o componente nativo de folhas de cálculo Excel para Delphi e C++Builder, deparou-se exatamente com isso na v2.382.5 na primeira vez que a sua saída passou por uma instância COM real do Excel, e as três causas eram um <phoneticPr> sem fontId, um Override duplicado no [Content_Types].xml e duas relações de raiz a partilhar o rId4

Porque é que o Excel rejeita um pacote que todos os outros leitores aceitam?

Porque o aviso de reparação é um validador de esquema e de pacote, não uma falha do parser. O corpus do HotXLS andava há semanas a fazer round-trip de um modelo de crédito com 4805 fórmulas através da biblioteca, do LibreOffice e dos validadores XML da suite de testes. O ficheiro gravado estava estruturalmente são no sentido OPC usado no artigo sobre resolução de relações OPC em XLSX: todas as partes alcançáveis, todos os destinos resolvíveis. Depois apareceu uma máquina Windows com o Excel 16.0 build 20326, o runner do corpus abriu o modelo gravado através de Workbooks.Open numa instância COM isolada com o DisplayAlerts desligado, e a chamada falhou de imediato. Interativamente, o mesmo ficheiro produz o diálogo habitual a propor reparação, e o registo de reparação, quando o Excel se dá ao trabalho de escrever um, indica a parte mas não a regra. Estavam três defeitos independentes escondidos naquele único aviso, e o Excel não os reporta um a um; rejeita o livro e deixa-o a si encontrá-los e analisá-los. O que se segue é cada regra, a linha do HotXLS que a violava e a correção que saiu, porque cada uma delas é uma regra em que qualquer writer XLSX em Delphi pode tropeçar

Regra 1: o fontId do phoneticPr é obrigatório, mesmo quando é zero

O elemento <phoneticPr> transporta um atributo fontId declarado use="required" na ECMA-376 Parte 1 §18.4.3, e um valor de 0 é um índice de tipo de letra legal, não uma ausência. O antigo writer de folhas de cálculo do HotXLS tratava o zero como «não definido» e só emitia o atributo quando Sheet.PhoneticFontId > 0. É um reflexo natural em Delphi, já que os campos inteiros assumem zero por predefinição, mas produz <phoneticPr type="noConversion"/> para qualquer livro cujo tipo de letra fonético seja o primeiro em styles.xml, que é exatamente o que o modelo de crédito do corpus do HotXLS trazia. O Excel rejeita então, na volta, um valor que ele próprio tinha escrito

// lxHandleX.pas, writer de folha de cálculo — antes da v2.382.5
phoneticXml:= '<phoneticPr';
if Sheet.PhoneticFontId > 0 then
  phoneticXml:= phoneticXml+ ' fontId="'+ IntToStr(Sheet.PhoneticFontId)+ '"';

// v2.382.5 — o atributo é obrigatório, zero incluído
phoneticXml:= '<phoneticPr fontId="'+ IntToStr(Sheet.PhoneticFontId)+ '"';
phoneticXml:= phoneticXml+ ' type="'+ XlsxEscapeAttr(Sheet.PhoneticType)+ '"';
if Sheet.PhoneticAlignment<> '' then
  phoneticXml:= phoneticXml+ ' alignment="'+ XlsxEscapeAttr(Sheet.PhoneticAlignment)+ '"';

O HotXLS continua a emitir o elemento apenas quando o TXLSXWorksheet.PhoneticType não está vazio, pelo que os livros que nunca tiveram definições fonéticas não são afetados. O teste de regressão PhoneticSettings_DefaultFontIsExplicit põe o PhoneticFontId a zero numa folha nova, grava e verifica que <phoneticPr fontId="0" está presente em xl/worksheets/sheet1.xml. A lição mais ampla é que «omitir quando é a predefinição» só é seguro quando o esquema declara uma predefinição; type e alignment têm predefinições nesse elemento, o fontId não tem

Regra 2: um único Override por nome de parte no [Content_Types].xml

O stream de tipos de conteúdo pode declarar cada nome de parte no máximo uma vez, e o Excel trata um segundo Override para o mesmo PartName como corrupção, mesmo quando as duas entradas trazem o mesmo ContentType. O HotXLS tem dois writers a alimentar esse stream. O BuildContentTypesXml declara todas as partes que o modelo de objetos gera: workbook, styles, shared strings, theme, folhas de cálculo e, quando TXLSXWorkbook.CustomProperties.Count > 0, /docProps/custom.xml. Quando o PreserveUnsupportedParts está ligado, o TXLSXOpaquePackage acrescenta depois um Override para cada parte que capturou textualmente do pacote de origem, para que esses bytes continuem declarados à saída. A colisão é uma parte que vive dos dois lados. As propriedades de documento personalizadas são analisadas para o modelo, mas o docProps/custom.xml do pacote de origem também foi capturado de forma opaca, pelo que o stream resultante da junção o declarava duas vezes, e as partes de gráfico e de cache de tabela dinâmica podem cair no mesmo ponto quando o modelo regenera uma parte que a camada opaca também reteve. Antes da v2.382.5, o ContentTypeOverridesXml não tinha visão do que o modelo já tinha escrito, pelo que não o podia saber

<!-- O que o Excel via antes da v2.382.5 -->
<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"/>

A correção passa o XML gerado para dentro do ContentTypeOverridesXml e deixa o writer opaco analisá-lo antes de emitir seja o que for. Dois pormenores sustentam a correção. O OpcLowerPartName passa a minúsculas, troca as barras invertidas por barras normais e retira as barras iniciais antes da comparação, porque os nomes de parte OPC são comparados sem distinguir maiúsculas e o modelo escreve-os com uma barra inicial enquanto a camada opaca guarda os nomes de item ZIP sem ela. E o chamador no BuildContentTypesXml passa Result + '</Types>', fechando o documento parcialmente construído para que o TXMLReader veja entrada bem formada em vez de um stream truncado. A regra que daí resulta é a de que ganha o primeiro, com o modelo à frente: o que o modelo de objetos declara é que manda, e a repetição opaca só preenche lacunas

// lxOpcPackage.pas — TXLSXOpaquePackage.ContentTypeOverridesXml
function TXLSXOpaquePackage.ContentTypeOverridesXml(const ExistingXml: WideString): WideString;
begin
  ...
  UsedNames.Sorted:= True;
  UsedNames.Duplicates:= dupIgnore;
  if ExistingXml<> '' then
    // Analisar o stream gerado pelo modelo e recolher todos os PartName declarados.
    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;                       // já declarado, ou uma parte rels
    UsedNames.Add(String(OpcLowerPartName(Part.PartName)));
    Result:= Result+ '<Override PartName="/'+ OpcXmlEscapeAttribute(Part.PartName)+
      '" ContentType="'+ OpcXmlEscapeAttribute(Part.ContentType)+ '"/>';
  end;
end;

Regra 3: os Id de relação são únicos dentro de uma parte de relações

Cada Relationship numa parte .rels precisa de um Id único dentro dessa parte, e o Excel recusa o pacote quando duas partilham o mesmo. O HotXLS escreve o _rels/.rels ao nível do pacote com identificadores fixos: rId1 para o workbook, rId2 e rId3 para as propriedades de documento principais e estendidas, e rId4 para as propriedades personalizadas quando o modelo tem algumas. O pacote opaco acrescenta depois as relações de raiz que reteve da origem, renumerando qualquer identificador que já esteja numa lista UsedIds. A lista conhecia os rId1 a rId3. Não conhecia o rId4, nem sabia que o modelo estava prestes a emitir a sua própria relação de propriedades personalizadas, pelo que um pacote de origem cuja relação de propriedades personalizadas fosse também rId4, que é o que o Excel escreve por predefinição, saía com duas entradas rId4 a apontar para o mesmo destino. O chamador, BuildRootRelsXml, passa agora Workbook.FCustomProps.Count > 0 como segundo argumento, de modo que a reserva e o salto são guiados pela mesma condição que decide se o modelo emite sequer o rId4. Renumerar é seguro na raiz do pacote porque nada dentro do livro referencia os identificadores de relação da raiz pelo nome; o mesmo truque estaria errado um nível abaixo, onde os atributos r:id no workbook.xml se ligam a identificadores na parte de relações do livro, e é por isso que o MergeWorkbookRelationshipsXml mantém um mapa de identificadores separado

// lxOpcPackage.pas — TXLSXOpaquePackage.RootRelationshipsXml
UsedIds.CaseSensitive:= False;
UsedIds.Add('rId1');
if EmitCustomProps then UsedIds.Add('rId4');   // reservado pelo writer do modelo
if EmitDocProps then
begin
  UsedIds.Add('rId2');
  UsedIds.Add('rId3');
end;
for i:= 0 to FRootRelationships.Count- 1 do
begin
  Rel:= TXLSXOpaqueRelationship(FRootRelationships[i]);
  // O modelo é agora dono das propriedades personalizadas; não repetir a cópia de origem.
  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);      // rIdN livre mais baixo
  UsedIds.Add(String(Id));
  ...
end;

O que têm em comum as três falhas?

As três são sintomas de um writer com duas fontes e sem um único dono dos invariantes do pacote. O modelo de objetos gera as partes que compreende; a camada opaca repete as que não compreende, para que um round-trip mantenha gráficos, caches de tabela dinâmica, XML personalizado e tudo o resto descrito nas notas sobre round-trip sem perdas de theme, extLst e calcChain. Cada lado era individualmente coerente. As restrições que a OPC impõe ao pacote inteiro, nomes de parte de Override únicos e identificadores de relação únicos por parte, só existem na costura onde os dois se concatenam, e até à v2.382.5 ninguém verificava a costura. O bug do fontId tem a mesma forma um nível abaixo: o writer sabia o que queria omitir mas nunca consultou o esquema que diz que não pode. A correção com que o HotXLS ficou é uma precedência fixa e não uma heurística de junção. O modelo escreve primeiro, a camada opaca vê o que foi escrito e cede perante qualquer colisão, e o runner do corpus passa agora a impor os invariantes do exterior com verify_opc_uniqueness, que lê o [Content_Types].xml e todos os itens .rels de um pacote gravado e falha o caso perante qualquer PartName, Extension ou Id duplicado. Essa verificação é barata, não precisa do Excel e teria apanhado dois dos três defeitos logo na primeira corrida do corpus

No mesmo lote: áreas de impressão que são fórmulas e não intervalos

A passagem pelo Excel também assinalou o _xlnm.Print_Area do modelo de crédito, que o Excel reportava como $A$1:$J$29 no original e tinha de reportar de forma idêntica na cópia gravada. Atrás dessa única asserção estavam dois bugs separados. Na importação, o XlsxStripSheetPrefix cortava tudo até ao primeiro ! não entre aspas, pelo que uma área de impressão dinâmica como OFFSET('Print Data'!$A$1,0,0,2,2) voltava como $A$1,0,0,2,2), e uma união qualificada como 'Print Data'!$A$1:$B$2,'Print Data'!$D$1:$E$2 perdia o prefixo apenas no primeiro segmento. Na exportação, o writer acrescentava o nome da folha uma vez ao PrintArea guardado por inteiro, pelo que uma união simples $A$1:$B$2,$D$1:$E$2 deixava a biblioteca com o primeiro segmento qualificado e o segundo nu, o que o Excel não aceita como definição de _xlnm.Print_Area segundo a ECMA-376 Parte 1 §18.2.5

// Importação: só retirar o prefixo quando o que resta é um sqref simples
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;                                  // O OFFSET(...) é devolvido intacto

// Exportação: qualificar todos os segmentos separados por vírgulas, ou nenhum
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;                           // uma fórmula: emitir textualmente
  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;

A regra de emparelhamento é a mesma dos dois lados: uma área de impressão só é um intervalo nu se todos os segmentos o forem, caso contrário é uma fórmula e viaja textualmente. O PrintArea_FormulaDefinitionSurvivesRoundTrip cobre a base com nome, a base qualificada pela folha e a união ao longo de dois ciclos de gravar e reabrir. A forma como as áreas de impressão interagem com a configuração de página e o resto do modelo de impressão é tratada no artigo sobre proteção de folhas, configuração de página e impressão

Como é que se descobre a que regra o Excel está a objetar?

Comece por assumir que o seu próprio validador está errado, porque passou. O validador do Open XML SDK indica uma violação de esquema como o fontId em falta, com a parte e o XPath, e a camada de empacotamento por baixo recusa-se a abrir um pacote com entradas de tipo de conteúdo duplicadas, pelo que vale a pena corrê-lo antes de qualquer outra coisa. Quando ele fica calado e o Excel continua a reparar, bisseccione o pacote: descomprima, apague uma parte e a sua relação e o seu Override, volte a comprimir e reabra, reduzindo o conjunto de candidatos a metade de cada vez até o aviso desaparecer. Os três defeitos aqui caíram por essa ordem, e nenhum deles seria visível no ficheiro reparado que o Excel se oferece para gravar, já que a reparação descarta ou renumera silenciosamente as entradas problemáticas. Vale a pena enunciar os limites da correção da v2.382.5 com a mesma clareza. A desduplicação é a de ganhar o primeiro, com o modelo à frente, pelo que se o pacote de origem declarava um tipo de conteúdo diferente para uma parte que o modelo também gera, a declaração do modelo ganha e a da origem é descartada, o que é correto para as partes que o HotXLS regenera e não é uma junção geral. O verify_opc_uniqueness verifica apenas a unicidade; não valida esquemas, pelo que um futuro atributo obrigatório continuaria a precisar do Excel ou de um validador de esquema para vir ao de cima. E a passagem extra do TXMLReader sobre o stream de tipos de conteúdo gerado corre em todas as gravações com o PreserveUnsupportedParts ligado, um custo pequeno face a um stream que raramente passa de uns quantos kilobytes. Com isto no lugar, as compilações Win32 e Win64 do modelo de crédito abrem agora no Excel sem aviso, recalculam as 4805 fórmulas verificadas com zero divergências e reportam a mesma área de impressão que o original

Se escreve XLSX a partir de Delphi por sua conta, a lista de verificação é curta: emita todos os atributos que o esquema marca como obrigatórios, independentemente do valor, declare cada nome de parte uma só vez e mantenha uma lista única de identificadores usados por parte de relações em todos os writers que lhe toquem. Se preferir que essa lista já exista e seja testada contra o Excel e não apenas contra o seu próprio leitor, o writer de pacotes aqui descrito vem no componente de folhas de cálculo Delphi do HotXLS, juntamente com o round-trip de partes opacas que é o que torna a costura digna de ser guardada