Последовател на shared formula в XLSX не носи текст на формула. Неговият елемент <f t="shared" si="N"/> сочи към master клетка другаде в листа, а четецът трябва да преизгради текста, измествайки master формулата по разликата в ред и колона. HotXLS Component за Delphi и C++Builder прави тази разгъвка при отваряне, така че всеки последовател отчита пълна формула
Ако някога сте зареждали реален XLSX в библиотека на трета страна и сте открили, че колона от хиляда формули има текст в точно една клетка и празни низове в останалите 999, сте срещали тази функция от грешната страна. Нищо не е повредено. Файлът прави това, което ECMA-376 му позволява, а четецът просто е спрял в момента, в който XML-ът е спрял
Защо клетката с shared formula е празна?
Защото форматът умишлено съхранява формулата веднъж. В ECMA-376 Part 1 и ISO/IEC 29500-1, елементът <f> (§18.3.1.40) носи атрибут t от тип ST_CellFormulaType, а стойността shared означава, че тази клетка участва в група, идентифицирана от атрибута si. Точно една клетка в групата, master-ът, носи и атрибут ref, даващ диапазона, за който важи групата, и само тази клетка носи текста на формулата като съдържание на елемента. Всяка друга клетка в групата е последовател. Тя повтаря t="shared" и същия si, а съдържанието на нейния елемент е празно. Excel пише тези групи агресивно, защото fill-down над колона от 200 000 реда се свива от 200 000 формулни низа до един низ плюс 199 999 миниатюрни placeholder елемента. Спестяването е реално, а цената пада изцяло върху четеца: без разгъвка последователят няма самостоятелен смисъл
Изместването е превод, не текстово копие
HotXLS разрешава последовател, локализирайки master-а, регистриран под същия si, изчислявайки делтата на ред и колона от master anchor-а до текущата клетка, и превеждайки всяка референция в master формулата по тази делта. Относителните измерения се местят, абсолютните не, а смесените референции местят само неабсолютната си половина. Литералните низове се пропускат изцяло, така че формула, случайно съдържаща текста "A1", пази този текст непроменен във всеки последовател
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;Атрибутът ref е порта, не украса. Последовател, чиито координати падат извън приложимия диапазон на master-а, не се разгъва, защото файлът тогава прави твърдение, което групата не поддържа. Също така, когато изместване би избутало референция над ред едно или наляво от колона A, HotXLS излъчва #REF! за този token, вместо тихо да го "прищипва", което е и това, което самият Excel би произвел за същата редакция. Този превод е близък братовчед на, но не същото като, пренаписването на референции, случващо се при вмъкване или изтриване на редове. Този път има собствени правила за това какво прави диапазон, когато редакция го пресича, и е описан отделно в статията за корекция на формулни референции при вмъкване и изтриване. Shared разгъвката е по-проста: тя е чист offset от известен anchor, приложен веднъж, при парсване
Кои форми на референции трябва да покрие shifter-ът?
Всички, иначе разгъвката е bug за загуба на данни, преоблечен като функция. Наивен shifter, разбиращ само A1 и A1:B2, ще повреди или изтърве по-екзотичните форми, а истинските workbook-ове са пълни с тях. Транслаторът на shared formula на HotXLS разпознава цялото семейство A1, преди да реши какво да мести. Референции към външни workbook-ове като [Book.xlsx]Sheet1!A1 и 3D референции като Sheet1:Sheet3!A1 пазят префикса си недокоснат, докато крайната референция на клетка се измества. Цитираните имена на листове оцеляват, включително гадния случай, когато листът е буквално именуван A1, така че 'A1'!A1 измества само частта след удивителния знак. Цяла колона A:A мести своето измерение колона и нищо друго; цял ред 1:1 мести своето измерение ред и нищо друго; $A:$A изобщо не се мести. Структурирани table референции като Table[A1] остават недокоснати, защото частта в скоби е име на колона, не координата
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Имената на функции са тихата клопка тук. Token сканер, грабващ букви, следвани от цифри, с готовност ще пренапише LOG10 на LOG11 един ред по-надолу. HotXLS изисква граница на референция преди token кандидат и след него, така че идентификатор, продължаващ в буква, цифра, долна черта, точка, или отваряща скоба, не е референция на клетка. Ако работите в другото семейство нотации, същият проблем с граници се проявява различно, и статията за R1C1 нотация покрива къде двата модела се разминават
Защо самозатварящ се елемент f поглъща следващата стойност?
Защото самозатварящ се елемент не произвежда end-element събитие. Това е единичният най-скъп бъг в цялата функция, и не е специфичен за никой конкретен XML parser. В TXMLReader, <f t="shared" si="4"/> вдига точно едно Element събитие с IsEmptyElement, зададено на True, и никога не вдига съответстващото EndElement. Parser, затварящ състоянието си за улавяне на формула само при EndElement, затова остава вътре във формулата, а следващият текст, който вижда, а именно кешираният резултат вътре в <v>, се добавя в буфера на формулата. По-лошо, състоянието преживява границата на клетката, така че следващата клетка, притежаваща истински <f>, има текста си на формула погълнат от предишната клетка. Поправката е да се приключи състоянието на формулата на самото Element събитие, винаги когато IsEmptyElement е True, и да се изпълни цялата разгъвка на последовател там, вместо да се чака. Това означава четене на t, si, ref, aca, и ca от атрибутите, прилагане на shared разгъвката, записване на атрибутите за преизчисление върху клетката, и изчистване на shared състоянието, всичко вътре в клона, обработващ празния елемент. Отбележете, че форматът позволява и двете изписвания, <f t="shared" si="4"/> и <f t="shared" si="4"></f>, а второто наистина вдига EndElement. Правилен четец трябва да обработва двойката идентично, поради което HotXLS покрива и двете изписвания в един и същ regression файл
Разредени, неподредени стойности на si и опашката на изчакване
Атрибутът si е цяло число без знак, дадено от файла, не позиция в масив, която контролирате. Нищо в схемата не изисква shared индексите да са плътни, да започват от нула, или да се появяват във възходящ ред, и нищо не пречи на враждебен или просто странен файл да използва si="4294967290" на първата клетка. Оразмеряването на масив за търсене от най-голямото наблюдавано si е затова примитив за изчерпване на паметта, не оптимизация. HotXLS пази пътя за отваряне на workbook на сортирана разредена таблица вместо това: shared групите се регистрират под своя цял ключ в сортиран TStringList, което прави търсенето двоично търсене над колкото групи реално съществуват, без връзка с числения размер на индексите. Редът е втората половина от проблема. Master нормално предхожда последователите си в реда на документа, но това е конвенция, а не правило, така че всеки последовател, който не може да разреши своя si в момента на парсването, отива в опашка на изчакване. Когато листът приключи, опашката се преизпълнява спрямо вече завършената таблица, а закъснелите master-и разрешават своите осиротели. Клетки, никога намерили master, пазят празна формула, което е честният резултат за файл, реферирал група, която никога не е дефинирал
Разгъване на shared formulas без зареждане на workbook
Streaming четците са изправени пред същото изискване при много по-строг бюджет на памет, и го решават с локална за worksheet-а таблица. И TXLSDirectReader, и TXLSRowCursor разгъват последователите в пълни формули за всяка клетка, докато пазят своето поведение с ограничена памет и проекция, така че forward-only преминаване над 300 MB лист все още ви дава истински текст на формула
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Две ограничения произтичат от този дизайн. Първо, проекцията никога не може да прескочи master-а. Row филтър, зададен с FirstRow и LastRow, или колонен филтър, изграден с IncludeColumn, може да пропусне излъчването на master клетката към вашия callback, но parser-ът все още трябва да запише нейния si, anchor координатите, приложимия диапазон, и текста на формулата, иначе всеки последовател вътре в проекцията се разрешава до нищо. Само работата откъм последовател, изместването и декодирането на стойността, е безопасно да се пропусне. Второ, таблицата е за worksheet, а нейният жизнен цикъл трябва да се управлява изрично: TXLSRowCursor държи една инстанция за продължителността на преминаване през лист и я изчиства при рестарт, смяна на лист, край на файл, изключение, и затваряне, така че група, дефинирана на лист едно, никога не може да изтече в лист две. Понеже streaming пътят е горещ цикъл, той използва open-addressing цял хеш, вместо сортираната таблица от низове, което избягва конверсия цяло-в-низ за клетка
Какво се случва при save, и къде са границите
Щом последовател бъде разгънат, той е обикновена формула, а HotXLS я записва обратно като независим елемент <f> без t="shared" и без si. Round trip-ът е стабилен, а кешираните резултати <v> оцеляват, но изходът е по-голям от входа за силно споделен лист, а групирането, което Excel е създал, не се възстановява при save. Ако побайтова точност на shared групите има за вас по-голямо значение от истински текст на формула във всяка клетка, това е компромисът, който приемате. XLS страната е различна, между другото: BIFF8 записът SHRFMLA има собствено кодиране и собствен писател, с превключвател за shared-групи на workbook-а
Две свързани неща изрично не са shared formulas, макар да споделят елемента <f>. Legacy CSE array формулите използват t="array" с ref, покриващ закотвения диапазон, а dynamic arrays използват същото изписване t="array", но се идентифицират чрез атрибут cm, който верижи през cellMetadata до запис XLDAPR. Третирането на клетка за разливане на dynamic array като shared или CSE последовател е истински bug за коректност, а разделянето е разгледано в статията за dynamic array и spill формули. Четете трите случая като три parser-а, случайно споделящи име на таг, и кодът остава честен
Разгъвката на shared formula, streaming четците, и транслаторът на референции, описани тук, се доставят като част от HotXLS Excel компонент за Delphi и C++Builder; продуктовата страница носи пълния справочник за формули и direct-read API, включително свойствата за проекция, използвани по-горе