HotXLS, de Delphi- en C++Builder-Excel-component, splitst automatisch een voorwaardelijke-opmaak- of gegevensvalidatieregel op in twee of meer aparte regelobjecten wanneer het invoegen of verwijderen van een rij of kolom het door de regel gedekte bereik in stukken snijdt die verschillende relatieve formuleankers nodig hebben, en wijst vervolgens elke voorwaardelijke-opmaakregel een nieuw, uniek prioriteitsnummer toe. Dit gedrag verscheen in versie 2.196 van de XLSX-engine en draait automatisch, zonder instelling om het uit te schakelen. De trigger is smal maar veelvoorkomend: een cellIs- of expressieregel waarvan de formule een cel relatief ten opzichte van zijn eigen bereik leest, op een werkblad waar later ergens midden in dat exacte bereik een rij wordt ingevoegd of verwijderd
De meeste beschrijvingen van Excel-automatisering stoppen bij het formuletekstprobleem: verschuif de rij- en kolomnummers binnen elke SUM() en elke VLOOKUP(), zodat de verwijzingen nog steeds naar de juiste cellen wijzen. Die helft van het verhaal klopt, en wordt behandeld in het bijbehorende artikel over hoe HotXLS formuleverwijzingen herschrijft wanneer rijen en kolommen verschuiven, maar een voorwaardelijke opmaak of een gegevensvalidatieregel is niet alleen een formule die in een cel staat. Het koppelt een formule aan een bereik, sqref in ECMA-376-termen, en de twee moeten samen bewegen. Wanneer een structurele bewerking dat bereik in twee stukken snijdt die twee verschillende relatieve offsets zouden nodig hebben om correct te blijven, houdt het bewaren van één regelobject met één formuletekst op een optie te zijn, en doen alsof dat niet zo is, is hoe een markeringsregel stilletjes de verkeerde rijen begint te vergelijken
Waarom splitst het invoegen van een rij een voorwaardelijke-opmaakregel op in plaats van deze gewoon te verplaatsen?
Een voorwaardelijke opmaak- of gegevensvalidatieregel houdt precies één formule voor zijn hele bereik, geëvalueerd relatief ten opzichte van één ankercel, dus zodra een bewerking twee delen van dat bereik dwingt twee verschillende relatieve offsets nodig te hebben, kan één formule beide delen niet langer correct beschrijven. ECMA-376 drukt de dekking van een regel uit als het sqref-attribuut op het conditionalFormatting- of dataValidation-element, en Excel evalueert Formula1 en Formula2 alsof de tekst was getypt in de linkerbovencel van die sqref en over de rest ervan was doorgevoerd, op dezelfde manier als een gewone relatieve formule een kolom naar beneden doorvoert. Stel u een variantiemarkering voor over B2:B50 die elk werkelijk cijfer markeert dat zijn budget overschrijdt, opgebouwd als een cellIs-regel waarvan Formula1 de letterlijke tekst C2 is, wat betekent: vergelijk de B-cel van de huidige rij met de C-cel van diezelfde rij
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Voeg die ene scheidingsrij in op oude rij 25, en de rijen boven het invoegpunt bewegen niet, dus hun deel van de regel leest Formula1 nog steeds correct als C2. De rijen die vroeger 25 tot en met 50 waren, schuiven naar beneden naar 26 tot en met 51, en voor hen is C2 nu volledig de verkeerde cel, aangezien rij 26 moet vergelijken tegen C26, niet tegen een budgetcijfer twee dozijn rijen hogerop
Hoe beslist HotXLS of een regel moet worden opgesplitst?
HotXLS maakt alleen extra regelobjecten aan wanneer de geometrie dit werkelijk vereist: een interne routine, XlsxBuildShiftedRuleParts, doorloopt elk losstaand gebied in de sqref van de regel, bepaalt wat het ankercel van dat gebied was vóór de bewerking en wat het erna wordt, en controleert of elk resulterend stuk dezelfde relatieve-offsetcorrectie nodig zou hebben. Als alle stukken het eens zijn, overleeft één regel, waarvan de sqref herbouwd wordt als de vereniging van de verschoven stukken en de formule eenmaal opnieuw wordt gebaseerd. Een echte splitsing gebeurt alleen wanneer de stukken het oneens zijn, precies het B2:B50-geval hierboven, waarbij het bovenste blok zijn oorspronkelijke anker behoudt en het onderste blok een nieuw anker nodig heeft
Het opnieuw baseren van de formule van een stuk is een beweging in twee stappen die machinerie hergebruikt die HotXLS al draagt voor OOXML gedeelde-formulegroepen: eerst wordt de formule vertaald alsof deze oorspronkelijk verankerd was in de linkerbovencel van dat stuk zelf, met dezelfde relatieve-offsetberekening die een gedeelde formule over zijn bereik uitbreidt, en vervolgens doorloopt het resultaat dezelfde rij-en-kolom-verschuivingsscanner die gewone werkbladformules herschrijft. Zo gaat Formula1 in twee stappen van C2 naar C26 in plaats van in één handgeschreven speciaal geval: vertaal C2 23 rijen vooruit om C25 te krijgen, alsof de regel daar altijd was begonnen, en laat vervolgens de gewone verschuiving bij rij 25 het verder naar C26 duwen. Elke andere eigenschap, vulkleur, stop-als-waar, de operator zelf, gaat ongewijzigd mee naar het nieuwe regelobject, zodat beide helften cellen blijven kleuren zoals ze altijd deden
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Splitsen databalken en pictogramsets op dezelfde manier als cellIs-regels?
Nee: HotXLS partitioneert alleen de regelsoorten waarvan de correctheid daadwerkelijk afhangt van een relatieve formule per regio, cellIs-vergelijkingen en expressieregels, en laat elk ander type voorwaardelijke opmaak als één regelobject waarvan de sqref gewoon groeit om de verschoven stukken als een unie van meerdere gebieden te dekken. Intern is de vertakking een gewone Kind-controle, cf.Kind in [cfkCellIs, cfkExpression], niets exotischer dan dat. Databalken, twee- en driekleurenschalen, pictogramsets, top- en bodemrangschikkingen, en de detectors voor duplicaten, lege cellen en fouten dragen een payload, een balkkleur, een set schaalpunten, een pictogramfamilie, die het hele gedekte bereik in één keer beschrijft in plaats van een relatieve vergelijking per cel, dus het opsplitsen ervan in meerdere geprioriteerde regelobjecten zou geen correctheid opleveren en zou alleen regels toevoegen om te beheren. Wanneer een bewerking hun bereik verdeelt, voegt HotXLS de stukken samen tot één regel met een sqref van meerdere gebieden en verankert de payload opnieuw als één eenheid in plaats van een nieuw regelobject per stuk te klonen. Het onderscheid sluit aan bij de regelsoort-taxonomie in het artikel over de basisprincipes van voorwaardelijke opmaak en rich text: databalken, kleurenschalen, en pictogramsets staan al apart van cellIs-regels doordat ze de Style-eigenschap volledig negeren, en nu blijkt dat ze ook apart staan van per-regio-herverankering om dezelfde onderliggende reden
Waarom veranderen regelprioriteiten na een structurele bewerking?
Prioriteiten veranderen omdat elke kloon begint met precies dezelfde prioriteitswaarde als de regel waarvan hij is afgesplitst, en HotXLS voert daarna een normalisatiepas uit die de resulterende duplicaten oplost tot een schone, gatvrije ordening in plaats van twee regels gelijk te laten eindigen op dezelfde rang. Een tweede interne routine, XlsxNormalizeConditionalFormatPriorities, neemt de huidige prioriteit van elke voorwaardelijke opmaak, valt terug op de positie van die regel in de verzameling voor elke regel die er nooit expliciet één had ingesteld, sorteert de hele lijst stabiel zodat gelijkspel hun oorspronkelijke relatieve volgorde behoudt, en hernummert het gesorteerde resultaat naar een dichte reeks 1, 2, 3 zonder gaten en zonder herhalingen. HotXLS voert dit eenmaal uit voordat een verschuiving begint, zodat klonen begint vanaf een schone basislijn, en opnieuw na elke splitsing en na verwijdering van elke leeggelopen regel, zodat het bestand dat wordt opgeslagen nooit twee regelvermeldingen heeft die dezelfde prioriteit claimen. Dat doet ertoe als u het advies uit het artikel over de basisprincipes van voorwaardelijke opmaak heeft opgevolgd om gaten tussen prioriteitswaarden te laten zodat een latere regel kan invoegen zonder de rest te hernummeren: de gaten overleven totdat de volgende rij- of kolombewerking dat werkblad raakt, en storten dan in, omdat normalisatie alleen uniciteit en stabiele volgorde garandeert, niet dat uw oorspronkelijke nummeringsschema ongewijzigd terugkomt
Gegevensvalidatieregels splitsen ook, zonder prioriteit om te hernummeren
Gegevensvalidatieregels doorlopen dezelfde bereik-partitioneringslogica als cellIs- en expressie-voorwaardelijke-opmaak, en in tegenstelling tot voorwaardelijke opmaak volgt elk validatietype dat pad uniform: HotXLS heeft geen aparte niet-formulefamilie voor gegevensvalidatie zoals databalken en pictogramsets die hebben voor voorwaardelijke opmaak, dus een gewone lijst- of geheel-getal-regel wordt gepartitioneerd door dezelfde routine die een relatieve aangepaste formule behandelt. Wat verschilt, is prioriteit: ECMA-376 geeft het dataValidation-element helemaal geen priority-attribuut, dus is er geen hernummeringsstap voor validaties zoals die er is voor voorwaardelijke opmaak. Stel u een aangepaste-formule-validatie voor die het werkelijke bedrag van elke rij ervan weerhoudt zijn eigen budget in de kolom ernaast te overschrijden
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Dit doet ertoe om dezelfde reden dat het artikel over de basisprincipes van gegevensvalidatie waarschuwt tegen het koppelen van een regel voordat het rijaantal definitief is: een validatie dekt alleen de letterlijke cellen die u eraan gaf, en een latere structurele bewerking kan twee of meer regels achterlaten die het werk doen dat er eerder één deed. Er breekt functioneel niets: elke cel in het oorspronkelijke bereik wordt nog steeds door iets gevalideerd, maar code die aanneemt dat er één DataValidations-vermelding per kolom is, begint verkeerd te indexeren zodra de eerste bewerking hem raakt. Er is een harde grens aan hoe ver dit kan gaan: als splitsen een werkblad over 65.534 gegevensvalidatieregels zou duwen, werpt HotXLS een uitzondering op in plaats van een bestand te schrijven dat Excel stilzwijgend zou afwijzen, wat de bibliotheek is die weigert een corrupt werkboek te fabriceren, geen limiet die gewoon gebruik waarschijnlijk zal bereiken
Wat te controleren na een bulkinvoeging of -verwijdering
De twee dingen die de moeite waard zijn om te verifiëren nadat een script een batch rij- of kolombewerkingen heeft uitgevoerd over een blad vol voorwaardelijke opmaak en validaties, zijn het totale regelaantal en de prioriteitsvolgorde, aangezien beide op manieren kunnen afdrijven die gemakkelijk over het hoofd worden gezien bij codebeoordeling en overduidelijk zijn zodra iemand Regels beheren in Excel opent. Eén bewerking richt zelden veel schade aan: één enkele invoeging midden in één cellIs-regel produceert hoogstens twee regelobjecten waar er één was. Het risico stapelt zich op wanneer een rapportgeneratieroutine rijen één voor één invoegt in een lus over een blad dat al meerdere formule-verankerde regels draagt: elke doorloop kan regels opnieuw splitsen die een vorige doorloop al had gesplitst, en vijf oorspronkelijke cellIs-regels kunnen uiteindelijk een veelvoud daarvan aan laagwaardige fragmenten worden die reepjes van het oorspronkelijke bereik dekken. Structurele bewerkingen batchen, het hele nieuwe blok in één aanroep invoegen in plaats van rij voor rij, houdt het regelaantal gekoppeld aan het aantal werkelijk verschillende ankers in plaats van aan het aantal uitgevoerde bewerkingen
Regelpartitionering en prioriteitsnormalisatie worden als standaardgedrag van de XLSX-engine meegeleverd in de HotXLS Delphi Excel-component voor Delphi en C++Builder; de productpagina bevat de volledige API-referentie voor werkbladbewerking, inclusief de hier beschreven methoden voor voorwaardelijke opmaak en gegevensvalidatie