Validarea datelor, AutoFilter și tabele în foi de calcul în Delphi cu HotXLS

Trei funcționalități din HotXLS împart o foaie de calcul, dar lucrează pe obiecte cu totul diferite, iar necazul începe atunci când presupui că fac lucruri asemănătoare. Validarea datelor atașează unui interval o regulă care constrânge ce poate tasta un utilizator în el. Un AutoFilter atașează unei regiuni o definiție de criterii stocată și schimbă ce rânduri îi arată unui privitor. Un tabel învelește un interval într-o structură denumită și tipizată, cu stilizare în benzi. Una constrânge intrarea, una consemnează o vedere, una impune o schemă. Niciuna nu mută de la sine vreo valoare de celulă, iar AutoFilter îi păcălește pe oameni în mod deosebit, pentru că vorba sugerează o acțiune, în timp ce el stochează doar o definiție. Să știi ce obiect atinge fiecare apel și când se materializează efectul este ceea ce desparte un registru de lucru care se poartă în Excel la fel ca în testele tale de unul care se abate pe tăcute

AutoFilter stochează o definiție, nu decupează rânduri

Un AutoFilter dintr-un fișier salvat este o înregistrare de criterii. Ascunderea rândurilor se întâmplă mai târziu, când Excel deschide registrul de lucru și evaluează criteriile față de date. HotXLS scrie fidel înregistrarea aceea și nu decupează nimic: fiecare rând pe care l-ai filtrat este încă prezent fizic în fișier. Un pipeline care aplică un filtru ca să scoată comenzile respinse și apoi citește registrul de lucru înapoi le va vedea pe toate, inclusiv pe cele respinse, iar codul este corect după API și greșit după modelul mental al autorului. Pe foaia de calcul XLSX, SetAutoFilter declară regiunea filtrată, iar AddAutoFilterColumn atașează criterii uneia dintre coloanele ei. Când codul de pe server are nevoie de rezultatul propriu-zis, pentru un număr de rânduri într-un rezumat sau ca să înainteze doar rândurile care se potrivesc, biblioteca evaluează criteriile în locul tău, în loc să pretindă că fișierul s-a schimbat:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Id-ul de coloană 3 = a patra coloană DIN INTERIORUL intervalului filtrat (decalaj de la 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible se potrivește acum cu ce va arăta Excel după deschiderea fișierului

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible răspunde rând cu rând, iar PreviewAutoFilterRows parcurge întreaga regiune printr-un callback atunci când ai nevoie de mulțimea potrivirilor dintr-o singură trecere. Există un caz în care niciunul nu este răspunsul potrivit: dacă cerința este ca rândurile excluse să nu existe deloc în fișier, adică o tăietură de confidențialitate, nu o vedere, șterge rândurile de-a binelea. Un filtru este unealta greșită acolo, pentru că orice destinatar îl șterge cu un clic și datele pe care voiai să le reții sunt din nou pe ecran

Id-ul de coloană este un decalaj, nu un număr de coloană

Comentariul din fragmentul de mai sus semnalează capcana care costă cel mai mult timp de depanare în acest API. AddAutoFilterColumn își identifică ținta prin poziția numerotată de la 0 din interiorul intervalului filtrat, nu prin coloana din foaia de calcul. Pentru un filtru pe A1:E500, cele două sisteme de numerotare se nimeresc să difere cu unu, exact genul de ratare la mustață care supraviețuiește unui test rapid și se rupe în clipa în care un coleg filtrează o altă coloană. Pentru un filtru care începe la coloana C, id-ul 0 înseamnă coloana C, iar nepotrivirea devine repede evidentă. Când intervalul filtrat este calculat la execuție, derivă id-ul de coloană din aceeași variabilă care a construit șirul de interval, niciodată dintr-o constantă de coloană a foii de calcul. Fiecare coloană acceptă o a doua condiție prin supraîncărcarea care primește doi operatori, două criterii și un conector și/sau, ceea ce oglindește dialogul de filtru personalizat din Excel. Fațada XLS acoperă același teren cu SetAutoFilter plus ApplyAutoFilter, ai cărui parametri de criterii și de operator urmează convențiile mai vechi, în stil COM, și numerotează câmpul de la 1. Schimbarea fațadei înseamnă schimbarea bazei de indexare, așa că locul apelului merită un comentariu care să spună care dintre ele este în joc

Regulile de validare sunt contractul sub care editează utilizatorii tăi

Dintre cele trei funcționalități, validarea este singura care constrânge activ intrările viitoare și primește cea mai multă atenție de proiectare în registrele de lucru care pleacă spre completare și se întorc spre procesare. Varianta cu listă duce cea mai mare parte din munca aceasta:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Cantități: numere întregi, zero sau mai mult
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Dincolo de liste și de numere întregi, aceeași familie acoperă zecimalele, datele calendaristice, orele, lungimea textului și formulele libere prin AddCustomValidation, iar genericul AddDataValidation expune întreaga matrice de tipuri și operatori pentru constructoare de reguli conduse din configurație. Stilul de eroare contează mai mult decât sugerează numele. xlsxDvErrStop respinge din start intrarea greșită; stilurile de avertisment și de informare lasă valoarea să treacă după un singur clic. Alege pe fiecare coloană în funcție de cât poate tolera codul care citește registrul de lucru înapoi o valoare din afara regulii. Două limite își au locul în textul de solicitare sau în fișierul README pe care îl livrezi împreună cu fișierul. Validarea din Excel păzește tastarea, dar lipirea unui bloc peste un interval validat trece pe lângă regulă, așa că orice cod care citește datele înapoi trebuie să valideze din nou, în loc să se încreadă în celule. Iar o regulă acoperă intervalul literal pe care i l-ai dat, ceea ce înseamnă că atașarea validării înainte să știi numărul final de rânduri lasă nepăzită coada adăugată ulterior. Scrie întâi datele, apoi dimensionează regulile după întinderea reală

Fațada moștenită oferă aceleași familii de reguli, cu o singură diferență de ergonomie. Creatorii din partea XLS, adică AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation și AddCustomValidation, întorc direct obiectul TDataValidation, nu un index, așa că setarea solicitării și a erorii se înlănțuie pe referința întoarsă, fără o căutare. Enumerarea de operatori (xlsDvBetween, xlsDvGreaterThan și restul) oglindește setul XLSX, așa că un cod de construire a regulilor se poartă între fațade în afara acelei diferențe de stil al returnării. Textul solicitării merită el însuși la fel de multă gândire ca regula. O listă derulantă care respinge intrarea cu o casetă de eroare goală îi învață pe utilizatori să scrie mailuri la IT; una care numește stările permise îi învață să repare celula și să meargă mai departe

O inversare de polaritate pe care biblioteca o absoarbe în locul tău

Oricine a citit de mână XML-ul de validare OOXML a dat peste atributul inversat showDropDown: în ISO/IEC 29500, o valoare adevărată înseamnă „suprimă săgeata listei derulante”, opusul a ceea ce se citește din nume. HotXLS inversează asta pe dinăuntru, așa că proprietatea ShowDropDown de pe o regulă de validare înseamnă exact ce spune, iar valoarea adevărată arată lista derulantă. Singurul mod de a te frige este să amesteci niveluri de adevăr, setând proprietatea din cod în timp ce un coleg auditează XML-ul salvat și „corectează” atributul care lui i se pare pe dos. Hotărăște dacă proprietatea sau XML-ul brut este autoritatea pentru instrumentele de recenzie și scrie inversarea acolo unde stă decizia aceea

Tabelele dau unui interval o schemă și un nume

Un tabel de foaie de calcul, ListObject în termenii Excel, învelește un interval într-un nume, coloane tipizate, stilizare în benzi și suport pentru referințe structurate. Este funcționalitatea care face ca un registru de lucru generat să pară terminat din clipa în care utilizatorii încep să sorteze și să îl extindă. Crearea este simetrică între fațade, iar AddTable primește un nume, un interval și o listă de coloane:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Pe partea XLSX, obiectul tabel rezultat expune StyleName (familia predefinită TableStyleMedium2 și rudele ei), comutatoare pentru dungi și un indicator de rând de totaluri, așa că aplicarea stilului casei este o atribuire de proprietate, nu o trecere manuală de formatare. În fișierele .xls vechi, același apel scrie înregistrările BIFF8 de tabel, iar fațada oferă și AddPivotTable pentru vederi de sinteză construite din câmpuri de rând, de coloană și de date, o amintire că „tabelele” din formatul mai vechi ajung mai departe decât ajunge ListObject-ul OOXML. Denumește tabelele așa cum denumești vederile dintr-o bază de date. Codul din aval care citește Orders[Amount] prin referință structurată supraviețuiește reordonării de coloane care rupe codul pozițional

Două convenții scutesc de curățenie mai târziu. Excel cere ca numele de tabel să fie unice în tot registrul de lucru, așa că un generator care emite câte o foaie pentru fiecare regiune are nevoie de o schemă precum Orders_EMEA, nu de refolosirea lui Orders. Un duplicat nu eșuează la scriere; iese la suprafață ca dialog de reparare atunci când utilizatorul deschide fișierul, adică în cel mai prost loc cu putință pentru a-l descoperi. Cealaltă convenție privește rândul de totaluri: când este activat, stă direct sub intervalul de date, așa că orice cod care adaugă mai târziu după regula „ultimul rând folosit plus unu” scrie în banda de totaluri, nu după ea. Ține evidența întinderii datelor separat de întinderea tabelului și adăugirile aterizează unde te aștepți

Cele trei funcționalități se compun firesc în livrabilele de introducere a datelor. Un tabel definește regiunea editabilă, validarea constrânge coloanele în care tastează utilizatorii, iar un filtru presetat scutește destinatarul de primele câteva clicuri. Există un argument rezonabil pentru a livra un filtru deja aplicat, astfel încât registrul de lucru să se deschidă concentrat pe rândurile care contează, atâta vreme cât ții minte că rândurile excluse sunt încă în fișier și că un destinatar curios le poate scoate la iveală. Aducerea eficientă a rezultatelor de interogare în foaie, jumătatea din amonte a acestui pipeline, este tratată în exportul rezultatelor din baza de date în Excel din Delphi, iar registrele de lucru în care formulele sintetizează datele validate au de câștigat din numele definite pentru referințe stabile între foi

Validarea, filtrele și tabelele fac diferența dintre a livra o grilă de valori și a livra o mică aplicație. Referința completă pentru reguli, filtre și tabele se află pe pagina de produs HotXLS Delphi Component