Обединени клетки и оформление на шаблони за отчети в HotXLS за Delphi

Обходете клетките на току-що отворен шаблон за отчет и обединеното заглавие ще се държи като теч. Вие четете A1 и получавате "Quarterly Statement"; четете от B1 до F1, които видимо се намират под същия банер, и не получавате нищо. Запишете стойност в C1, за да коригирате заглавната част, и тя никога няма да се появи на екрана. Мрежата не е загубила вашите данни. Тя прави точно това, което означава обединяването: както в XLS, така и в XLSX, обединеният правоъгълник визуализира съдържанието на една клетка (горната лява закотвена клетка - anchor) и третира останалите като покрито пространство, което съдържа стойности, но никога не ги показва. Потребителите на Excel научават това по метода на пробата и грешката. Генераторът на отчети трябва да го кодира като правило, тъй като в генерирания код симптомът е празна област без изключение, по което да бъде проследен. HotXLS, оригинална Object Pascal библиотека, която чете и записва и двата формата на Excel от Delphi и C++Builder, извежда таблицата на обединяванията достатъчно ясно, за да можете да програмирате според това правило, вместо да го откривате отново чрез тикет за поддръжка.

Една стойност, едно закотвяне

Обединяването е инструкция за показване, насложена върху мрежа, която не променя формата си. Всяка покрита клетка все още съществува във файла като собствено гнездо; записът за обединяване само казва на консуматора да изрисува съдържанието на закотвената клетка върху целия правоъгълник. Тази разлика определя три поведения, които си струва да усвоите, преди да пишете код за оформление. Четенето на покрита клетка връща нейната собствена съхранена стойност, която за банер, който сте създали, обикновено е празна, така че всеки код, който инспектира обединено заглавие, трябва да намери и прочете закотвената клетка. Записването в покрита клетка завършва успешно на ниво файл и не се показва никъде, което е капанът с невидимото заглавие от началото на статията. А разединяването на регион разкрива всичко, което е било под него през цялото време, така че случайна стойност, записана в покритото пространство, се превръща във видим дефект в деня, в който някой развали обединяването.

От страна на XLSX тази таблица е обект от първи клас. Sheet.MergedCells съдържа Add('A1:C1'), FindAt(Row, Col), DeleteAt и Items, като извикването, към което посягате най-често, е FindAt: подайте му произволна координата и то ще върне обединения регион, покриващ тази клетка, или nil, когато клетката е самостоятелна. Това единично търсене е в основата на двете части за правилно управление на обединяването: безопасното четене и защитата при запис, и двете от които ще разгледаме по-нататък.

Две фасади, два идиома за обединяване

HotXLS поддържа класическия BIFF8 .xls двигател и OOXML .xlsx двигателя като отделни обектни модели и те описват обединяването по различен начин, тъй като произлизат от различни конвенции. Фасадата XLS следва Excel COM идиома: вземате диапазон от диапазонно свойство с два аргумента и извиквате Merge с OleVariant, чиято стойност определя геометрията, която получавате.

var
  Book: IXLSWorkbook;   // interface-counted: no manual Free
  Sh: IXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sh := Book.Sheets[1];                 // XLS sheet collection is 1-based
  Sh.Range['A1', 'F1'].Merge(False);    // False = one merged block
  Sh.Cells.Item[1, 1].Value := 'Quarterly Statement';
  Sh.Range['A3', 'F4'].Merge(True);     // True = merge across: one merge per row
  Book.SaveAs('layout.xls');
end;

Аргументът на Merge е частта, която хората бъркат. В диапазон от два реда, Merge(True) произвежда две независими обединявания от по един ред, което е функционалността "Merge Across" на Excel и е точно това, което искате за подредена заглавна лента, чиито редове трябва да останат разделими. Merge(False) слива целия правоъгълник в един блок. Диапазонът също така отчита MergeCells като флаг за състояние, връща съдържащия го регион чрез MergeArea и се разединява с Unmerge. Фасадата XLSX излага същите операции под други имена: Sheet.MergeCells(Row1, Col1, Row2, Col2) приема целочислени граници, TXLSXRange.Merge приема еквивалентния вариант Across, а колекцията MergedCells съдържа резултата.

Шаблон, който расте заедно с данните си

Реалният шаблон за отчет не е фиксирана мрежа. Заглавната част и общите суми са фиксирани, но разделът с детайли между тях се разтяга според резултатите от заявката. Моделът, който работи успешно, запазва един напълно стилизиран ред за детайли в шаблона, клонира го по веднъж за всеки запис и след това отваря празно пространство преди блока с общите суми, така че всичко, закотвено отдолу, да се плъзне надолу, без да губи форматирането си.

Sheet.Range['A1:F1'].Merge;
Sheet.Cells[1, 1].Value := 'INVOICE #2026-0611';    // value goes to the anchor, A1
Sheet.RowHeight[1] := 28;
TitleFont := Book.Fonts.Add('Calibri', 16, True, False);
Sheet.Cells[1, 1].FontIndex := TitleFont + 1;        // pool index 0-based, cell side 1-based

// row 5 is the styled detail template line
for I := 0 to ItemCount - 1 do
  Sheet.CopyRange(5, 1, 5, 6, 6 + I, 1);             // styles and formulas travel with it

// open a gap above the totals block; content below shifts down
Sheet.InsertRows(6 + ItemCount, 1);
Sheet.Range['A1:F1'].SetBorders(xlsxEdgeOutline, xlsxBorderMedium);

Два реда заслужават втори поглед. Присвояването на шрифт съдържа разлика с единица (off-by-one), която може неусетно да ви изненада: Fonts.Add връща 0-базирана позиция в пула, докато клетката съхранява 1-базирана препратка към шрифт, където 0 означава шрифта по подразбиране, така че изпускането на + 1 не предизвиква грешка, а просто форматира заглавието с грешен шрифт. Другият ред е CopyRange, който премества форматирането и формулите заедно със стойностите. Това е цялата причина да се клонира ръчно създаден ред от шаблон, вместо да се възстановява неговият изглед в кода. Дизайнерът определя външния вид веднъж, в шаблона; генераторът само излива данни в копия от него.

Това разделение се мащабира допълнително, когато шаблоното оформление живее в собствена работна книга, например лист със заглавни и долни колонтитули, споделяни между отчетите. CopyRangeTo извършва същото клониране през границите на работните листове, приемайки целевия лист плюс координатите на местоназначението, така че генераторът може да поддържа един чист шаблон и да копира неговите региони в толкова изходни листове, колкото са необходими за задачата. Алтернативата, да променяте шаблона на място и да се опитвате да го възстановите след това, е от нещата, които работят до деня, в който процесът се прекъсне наполовина.

Какво премества InsertRows и какво не

Моделът с разрастващ се шаблон работи само защото InsertRows в XLSX е структурна редакция, а не просто разместване на клетки. Когато отваря празнина, той релокира обединените региони, височините на редовете, хипервръзките, коментарите, замразените панели, диапазоните на автофилтрите, условните формати, валидирането на данни, таблиците, дефинираните имена, закотвянията на изображения и диаграми, които се намират под точката на вмъкване, а не само стойностите на клетките. Това позволява на блока с общите суми да пристигне на новия си ред със запазени обединявания и числови формати, вместо да пристигне изчистен от тях.

Неговите две документирани ограничения са тези, с които трябва да се съобрази дизайнът. Коригирането на формулите е ограничено до редактирания лист: препратките вътре в него се пренаписват, а формула на друг лист, която сочи към преместената област, също се пренаписва, но корекцията следва само препратки, които са насочени към редактирания лист, така че всяка схема с препратки между различни работни книги заслужава собствен одит, вместо сляпо доверие. Второ ограничение е по-строго и е от страна на XLS. Обобщените таблици (pivot tables) оцеляват при цикли на отваряне и запис като необработени запазени записи, а не като моделирани обекти, които HotXLS може да мести, така че вмъкването на редове не релокира отпечатъка на пивота. Всеки шаблон, който създавате за формата .xls, трябва да разполага своите обобщени таблици далеч от всяка разрастваща се област.

Отказ за запис на данни в пространството за оформление

Проблемът с обединените клетки, който действително достига до продукция, не е козметичен. Той е структурен: детайлен ред навлиза в обединен сегмент от оформлението, стойностите му попадат в покрити клетки и стават невидими, а общите суми на колоните тихо спират да съответстват на това, което всеки, който чете листа, може да види. Тъй като FindAt отговаря на въпроса за покриващия регион за всяка координата, генераторът може да откаже този запис в момента, в който той би се случил, вместо да достави отчет, който тихо неотчита реалните данни.

// refuse to write detail data into a merged layout region
if Sheet.MergedCells.FindAt(Row, 1) <> nil then
  raise Exception.CreateFmt('row %d overlaps a merged layout region', [Row]);
Sheet.Cells[Row, 1].Value := Detail.Description;

Същата гранична проверка трябва да се прилага навсякъде, където по-късно потребител ще сортира или филтрира изхода. Диапазон с обединявания вътре в него не може да се сортира чисто, тъй като сортирането мести редовете независимо, а обединяване, обхващащо редове, няма единствен ред, с който да се премести; Excel отговори с грешка или развалено оформление. Дисциплината, която поддържа отчетите коректни, е географска. Ограничете обединяванията до заглавни ленти, разделители на секции и блокове за подписи и поддържайте табличната средна част на листа плоска. Статията за генериране на отчети по шаблони развива това разделение на оформление и данни в пълен процес, управляван от плейсхолдъри, а статията за условно форматиране и богат текст обхваща стилизирането на този плосък диапазон от данни.

Как се влошават обединяванията при експортиране

Обединяването е концепция на работната книга и всеки текстово ориентиран формат за експорт го зачита в различна степен. Познаването на трите поведения предварително спестява QA цикъл. HTML експортът възпроизвежда обединяванията вярно, излъчвайки colspan и rowspan в една таблица, така че отчетът в браузъра запазва лентовия си вид. RTF експортът изобщо не обединява колони: текстът от закотвената клетка попада в собствена клетка, а останалата ширина от обединяването се появява като празни клетки, което оставя широкото заглавие визуално прибутано вляво в текстовия процесор. CSV няма концепция за обединяване, така че закотвената стойност заема едно поле, а всяка покрита клетка се излъчва като празно поле. Изводът за работна книга, която захранва и експорт с разделители, е да държите всяка важна информация извън обединената геометрия; статията за експортиране на CSV, TSV и HTML разглежда подробно всеки формат.

Едно успокоение за всеки, който претегля това спрямо размера на файла: обединяванията не струват почти нищо в мащаба на отчета. Таблицата с обединявания е миниатюрна в сравнение с данните в клетките, а четенето на покрита клетка все пак минава през FindAt, вместо да сканира. Натискът върху производителността при големите работни книги идва от другаде, главно от нарастването на пула от стилове и паметта, която заема пътят за запис, което статията за производителност на големи работни книги разглежда директно. Двата API за обединяване, операциите за структурно редактиране и демонстрациите на шаблони се доставят с компонента HotXLS.