Excel weigert sich, ein XLSX-Workbook zu öffnen, dessen Listendatenüberprüfung eine Inline-Liste länger als 255 Zeichen hält, und das gilt sogar für eine Datei, die Excel selbst gesichert hat. HotXLS, die native Excel-Komponente für Delphi und C++Builder, lädt jene Listen und modelliert Cross-Sheet-Listenregeln als x14-Erweiterungen, aber die dauerhafte Lösung ist überall dieselbe: Halte lange Listen in einem Worksheet-Bereich oder einem definierten Namen
Der Bugreport kommt üblicherweise von einem Kunden, nicht aus deinen Tests. Eine Vorlage bekommt eine Region- oder Produkt-Spalte, deren Dropdown bei acht Einträgen startete und jetzt sechzig hat. In der XLSB-Kopie, die jemand auf einem Share-Laufwerk hält, funktioniert weiterhin alles. Dann schreibt ein Export-Job dieselbe Regel in ein XLSX, und Excel antwortet mit einem Reparatur-Prompt oder einer schlichten Verweigerung. Nichts in deinem Code hat sich geändert außer der Länge eines Strings, genau deshalb lohnt es sich, diese Grenze zu verstehen, bevor sie dich findet
Wo greift das 255-Zeichen-Listenlimit tatsächlich?
Das Limit greift bei der Inline-Form einer Listenüberprüfung in XLSX, dem quotierten, kommagetrennten String, gespeichert in formula1, und Excel setzt es beim Öffnen der Datei durch, nicht nur, wenn du die Liste im Datenüberprüfungs-Dialog tippst. Direkt gegen Excel getestet ergab ein sauberes Bild: Eine 253-Zeichen-Liste öffnete, 260-Zeichen- und 421-Zeichen-Listen wurden verweigert. Der 421-Zeichen-Fall ist der lehrreiche, denn er stammte aus einem Workbook, das Excel selbst verfasst hatte. Excel schreibt eine Datei, die Excel nicht zurückliest
Das Schema rettet dich hier nicht. ECMA-376 Part 1, §18.3.1.32 definiert das dataValidation-Element und seine Formeln, ohne eine 255-Zeichen-Deckelung zu nennen, eine Datei kann also schema-valide sein und trotzdem abgelehnt werden. Das ist eine Anwendungsbeschränkung, und die einzige verlässliche Referenz dafür ist Excels eigenes Verhalten
HotXLS setzt jenen Deckel nicht für dich durch. TXLSXWorksheet.AddListValidation wickelt die Einträge in Anführungszeichen und speichert sie, wie gegeben; es kürzt weder noch weist es eine lange Liste ab, eine 400-Zeichen-Liste wandert also exakt wie angefragt in das gesicherte XLSX, und Excel verweigert dann das Ergebnis. Das ist eine bewusste Entscheidung, denn eine Liste lautlos zu kürzen würde Daten in einer Weise korrumpieren, die niemand bemerkt, aber es legt die Längenprüfung auf deine Seite. Ein kleiner Helfer hält sie ehrlich:
const
// Excel öffnete eine 253-Zeichen-Liste und verweigerte 260 und 421 Zeichen.
// Halte eine Marge, statt auf die exakte Kante zu zielen.
MaxInlineListChars = 250;
function InlineListLength(const AItems: array of WideString): Integer;
var
I: Integer;
begin
Result := 0;
for I := 0 to High(AItems) do
Inc(Result, Length(AItems[I]));
if Length(AItems) > 1 then
Inc(Result, High(AItems)); // trennende Kommas
end;Rechne eingebettete doppelte Anführungszeichen als je zwei Zeichen, denn AddListValidation verdoppelt sie, um den Formel-String valide zu halten
Warum XLSB Listen tragen kann, die XLSX nicht tragen kann
XLSB speichert eine Inline-Liste als einzelnes PtgStr-String-Token, dessen Länge ein 16-Bit-Feld ist, das Binärformat hat also keine 255-Zeichen-Mauer. Eine native Stichprobe mit 60 Einträgen und 419 Zeichen lebt in einem Token und läuft in Excel ohne Murren hin und her. Dieselbe Liste in XLSX ist der oben genannte 421-Zeichen-Fall
Ein Loader, der das 255-Zeichen-Dialoglimit auf XLSB anwendete, würde jene Datei ablehnen. Die HotXLS-XLSB-Validierungs-Token-Prüfung begrenzt ein String-Token stattdessen bei 32.767 Zeichen, derselbe Deckel wie bei einem Excel-Zellenstring. Zwei Einschränkungen verdienen es, klar gesagt zu werden. Der Loader deckelt außerdem eine vollständige Validierungsformel bei 16.384 Bytes, der praktische Deckel für eine Inline-Liste liegt also niedriger, als die Token-Prüfung allein vermuten lässt. Und durch NUL-Zeichen getrennte Listen innerhalb eines PtgStr fallen außerhalb des unterstützten Profils, sie werden also als nicht unterstützt gemeldet, statt erraten zu werden
Die Falle sitzt in der Konvertierung. Eine lange Liste, die in XLSB kerngesund ist, wird zu einer verweigerten Datei, sobald irgendetwas, HotXLS eingeschlossen, das Workbook als XLSX sichert. Konvertiert eine Pipeline Kunden-XLSB-Dateien, fahre die Längenprüfung über jede Listenregel vor dem XLSX-Sichern, nicht erst, nachdem ein Kunde es meldet
Was geschieht mit Listen, die auf ein anderes Sheet zeigen?
Excel speichert eine Liste, deren Quelle auf einem anderen Worksheet sitzt, in der x14:dataValidations-Erweiterung, nicht im gewöhnlichen dataValidations-Element. Die Regel lebt innerhalb des extLst des Worksheets unter der Erweiterungs-URI {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definiert in [MS-XLSX], und ihre Quellreferenz ist ein xm:f-Element wie Lists!$A$2:$A$61. XLSB hält dieselbe Regel in seinen eigenen Erweiterungs-Records
HotXLS parst jene XLSX-Erweiterungsregeln in die typisierte DataValidations-Sammlung und markiert jede mit TXLSXDataValidation.IsExtension. Eine Cross-Sheet-Liste taucht also neben gewöhnlichen Regeln auf, mit lesbarem Bereich, Typ und Formel, und sie übersteht Änderungen, Sicherungen und Wiederöffnungen. Beim Sichern hält der Writer Erweiterungsregeln aus dem gewöhnlichen Element heraus und baut die x14-Erweiterung an ihrer Schemaposition am Ende des Worksheets wieder auf. Beide Formeln werden bewahrt, Ganzzahl-, Dezimal-, Textlängen-, Datums- und Zeitregeln mit between oder notBetween behalten also beide Grenzen
Das typisierte Erweiterungsprofil deckt die ganze Regel ab. Neben type, operator, allowBlank, den beiden Show-Flags, dem Bereich und beiden Formeln bewahrt eine Erweiterungsregel ihren Eingabe-Prompt-Titel und -Text, ihre Fehlerwarnungs-Titel und -Text, den Warnstil, den IME-Modus und die Hide-Dropdown-Einstellung über ein Laden und Wiedersichern, mit denselben Escaping-Regeln wie eine gewöhnliche Validierung
uses
SysUtils, lxHandleX, lxDiagnostics;
procedure AuditListRules(const FileName: WideString);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Dv: TXLSXDataValidation;
S, I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then
raise Exception.Create('Cannot open ' + FileName);
for S := 1 to Book.Sheets.Count do // Sheets[] ist 1-basiert
begin
Sheet := Book.Sheets[S];
for I := 0 to Sheet.DataValidations.Count - 1 do
begin
Dv := Sheet.DataValidations[I];
if Dv.ValidationType <> xlsxDvList then
Continue;
if Dv.IsExtension then
Writeln(Sheet.Name, ' ', Dv.Range, ': x14 list from ', Dv.Formula1)
else if (Dv.Formula1 <> '') and (Dv.Formula1[1] = '"') and
(Length(Dv.Formula1) - 2 > MaxInlineListChars) then
Writeln(Sheet.Name, ' ', Dv.Range, ': inline list of ',
Length(Dv.Formula1) - 2, ' chars, refused by Excel as XLSX');
end;
end;
finally
Book.Free;
end;
end;Für das Lesen der Einträge hinter einem Dropdown, das Wählen von Einträgen und das Prüfen getippter Eingaben gegen diese Regeln siehe Dropdown-Einträge lesen und Eingaben mit HotXLS validieren; dieser Artikel bleibt bei den Speicherformaten
Wie Excel und HotXLS mit unbekannten Validierungstypen umgehen
Excel öffnet ein dataValidation, dessen type-Attribut ein unerkanntes Token hält, als eine Regel vom Typ none, behält ihre Formel und schreibt sie beim Sichern als nacktes dataValidation-Element ohne type-Attribut um, das unbekannte Attribut fallengelassen. Solche Tokens kommen aus handeditiertem XML, Dritt-Generatoren und künftigen Office-Features, und Excels Antwort ist Toleranz, nicht Verweigerung
HotXLS folgt derselben Toleranz. Statt die Regel fallen zu lassen, was sie beim Wiedersichern spurlos verlieren würde, behält der Reader sie als xlsxDvNone mit ihren Formeln und ihrem Bereich und vermerkt je normalisierte Regel eine Warndiagnose mit Code 1307, exponiert als die xlsDiagnosticValidationTypeNormalized-Konstante in lxDiagnostics. Beim Sichern wird eine none-Regel als nacktes Element geschrieben, dieselbe Form, die Excel erzeugt. Eine none-Regel validiert nichts, die Diagnose ist also dein Hinweis, dass eine Regel, die der Autor für aktiv hielt, die Eingabe nicht mehr einschränkt
for I := 0 to Book.Diagnostics.Count - 1 do
if Book.Diagnostics[I].Code = xlsDiagnosticValidationTypeNormalized then
Writeln('Untyped rule kept on sheet ', Book.Diagnostics[I].SheetName,
': ', Book.Diagnostics[I].Message);Warum kann Excel ein Workbook nach einem Round-Trip verweigern?
Excel verweigert ein wiedergesichertes Workbook, wenn ein wiederabgespieltes Erweiterungsfragment einen Namespace-Präfix verwendet, den die neue Datei nie deklariert, denn das Ergebnis ist kein wohlgeformtes XML. Erweiterungsfragmente tragen routinemäßig präfixierte Attribute wie xr:uid, dessen xmlns:xr-Deklaration auf der ursprünglichen Worksheet-Wurzel sitzt, nicht innerhalb des Fragments. Kopiere das Fragment wortwörtlich in eine neue Worksheet-Wurzel, der jene Deklaration fehlt, und die XML-Namespace-Regeln sind gebrochen, bevor Excel eine einzige Zelle ansieht
HotXLS erledigt das im Capture-Schritt. Beim Lesen scannt es jedes wiederabgespielte Fragment auf Element- und Attribut-Präfixe, die das Fragment nicht selbst deklariert, löst jeden Präfix über den Reader zu seiner URI auf und schreibt jene Deklarationen beim Sichern auf die Worksheet-Wurzel. Der Streaming-Sicherungs-Pfad baut die Wurzel über dieselbe Routine, also liefern beide Sicherungsmodi wohlgeformte Ausgabe, und eine wiedergesicherte Datei öffnet in Excel mit jeder Validierungsregel an Bord, inklusive einer wiederabgespielten Cross-Sheet-x14-Liste. Das größere Bild, wie HotXLS extLst-Inhalt trägt, den es nicht modelliert, behandelt lossless XLSX round-trips für Themes, extLst und calcChain, und die allgemeinen Regeln hinter Excels Reparatur-Prompt stehen in warum Excel einen Reparatur-Prompt zeigt
Lange Listen gehören in einen Bereich oder einen definierten Namen
Das robuste Design für jede Liste, die wachsen könnte, ist, die Einträge in Worksheet-Zellen zu speichern und die Validierung über einen definierten Namen darauf zu zeigen. Eine Bereichsquelle hat in beiden Formaten kein 255-Zeichen-Limit, die Einträge lassen sich editieren, ohne die Regel anzufassen, und ein definierter Name hält die Regel im gewöhnlichen dataValidation-Element, selbst wenn die Zellen auf einem separaten Sheet sitzen, was jeder Consumer der Datei lesen kann
// Lists ist ein dediziertes Sheet, z. B. Book.Sheets.Add('Lists')
function AddListRule(Book: TXLSXWorkbook; Target, Lists: TXLSXWorksheet;
ACol: Integer; const ARange, AName: WideString;
const AItems: array of WideString): Integer;
var
I: Integer;
ColRef: WideString;
begin
if InlineListLength(AItems) <= MaxInlineListChars then
begin
Result := Target.AddListValidation(ARange, AItems);
Exit;
end;
for I := 0 to High(AItems) do
Lists.WriteAutoValue(I + 1, ACol, AItems[I]); // 1-basierte Zeile, Spalte
ColRef := Chr(Ord('A') + ACol - 1); // Spalten A..Z
Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
ColRef + '$' + IntToStr(Length(AItems)));
// Das führende '=' markiert eine Referenz; ohne es wird der Name als Text quotiert
Result := Target.AddListValidation(ARange, '=' + AName);
end;Dieses führende Gleichheitszeichen zählt. AddListValidation behandelt ein Argument, das mit = beginnt, als Referenz und streift das Zeichen, lässt ein Argument mit ! als Bereich unquotiert und quotiert alles andere als Inline-Liste. Übergib 'StatusList' ohne das Zeichen, und du bekommst eine Ein-Eintrag-Dropdownliste, die wortwörtlich StatusList liest. Zwei kleinere Punkte: WriteAutoValue konvertiert Text, wie Excels Zelleingabe es tut, ein Eintrag wie TRUE wird also ein Boolean, und ein Sheetname mit Leerzeichen braucht einfache Anführungszeichen in der Referenz
Für eine Regel, die ohne Namen direkt auf ein anderes Sheet zeigen muss, setze IsExtension := True auf der Regel am Index, den AddListValidation lieferte, und HotXLS schreibt sie als die x14-Erweiterung, dieselbe Form, die Excel selbst verwendet. Der Pfad über den definierten Namen ist einfacher und bleibt funktionierend für Werkzeuge, die nie Erweiterungen parsen, deshalb ist er hier die Default-Empfehlung
Das gesamte hier beschriebene Verhalten kommt mit der HotXLS Delphi Excel component für Delphi, C++Builder und Lazarus, wo die Produktseite die unterstützten Validierungstypen, Dateiformate und die volle Diagnose-Referenz listet