بسط si فرمول مشترک XLSX در Delphi: نکات مهم

یک پیروی فرمول مشترک در XLSX هیچ متن فرمولی حمل نمی‌کند. عنصر <f t="shared" si="N"/> آن به یک سلول مادر جایی دیگر در برگه اشاره می‌کند، و خواننده باید متن را با شیفت‌دادن فرمول مادر با تفاوت ردیف و ستون بازسازی کند. مؤلفه HotXLS برای Delphi و C++Builder این بسط را در زمان بازکردن انجام می‌دهد، پس هر پیرو یک فرمول کامل گزارش می‌کند

اگر تا‌به‌حال یک XLSX واقعی را در یک کتابخانه ثالث بارگذاری کرده باشید و دیده باشید ستونی از هزار فرمول متن دارد دقیقاً در یک سلول و رشته‌های خالی در ۹۹۹تای دیگر، شما این ویژگی را از سمت اشتباه ملاقات کرده‌اید. هیچ‌چیز فاسد نیست. فایل کاری را انجام می‌دهد که ECMA-376 اجازه می‌دهد، و خواننده به‌سادگی در نقطه‌ای که XML متوقف شد متوقف شده

چرا سلول فرمول مشترک خالی است؟

چون فرمت عمداً فرمول را یک‌بار ذخیره می‌کند. در ECMA-376 بخش ۱ و ISO/IEC 29500-1، عنصر <f> (§18.3.1.40) یک ویژگی t از نوع ST_CellFormulaType حمل می‌کند، و مقدار shared یعنی این سلول در یک گروه شناسایی‌شده با ویژگی si مشارکت دارد. دقیقاً یک سلول در گروه، مادر، همچنین یک ویژگی ref حمل می‌کند که بازه‌ای را که گروه اعمال می‌شود می‌دهد، و فقط آن سلول متن فرمول را به‌عنوان محتوای عنصر حمل می‌کند. هر سلول دیگر در گروه یک پیرو است. آن t="shared" و همان si را تکرار می‌کند، و محتوای عنصرش خالی است. Excel این گروه‌ها را تهاجمی می‌نویسد، چون یک fill-down روی یک ستون ۲۰۰,۰۰۰ ردیفی از ۲۰۰,۰۰۰ رشته فرمول به یک رشته به‌علاوه ۱۹۹,۹۹۹ عنصر جانگهدار کوچک فروکاسته می‌شود. صرفه‌جویی واقعی است و هزینه‌اش کاملاً روی خواننده فرود می‌آید: بدون بسط، پیرو هیچ معنایی به‌تنهایی ندارد

شیفت یک ترجمه است، نه یک کپی متن

HotXLS یک پیرو را با یافتن مادر ثبت‌شده زیر همان si، محاسبه دلتای ردیف و ستون از لنگر مادر تا سلول جاری، و ترجمه هر ارجاع در فرمول مادر با آن دلتا حل می‌کند. ابعاد نسبی حرکت می‌کنند، ابعاد مطلق حرکت نمی‌کنند، و ارجاعات مختلط فقط نیمه غیرمطلق‌شان را حرکت می‌دهند. رشته‌های تحت‌اللفظی کاملاً رد می‌شوند، پس فرمولی که تصادفاً متن "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+&quot;A1&quot;+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 یک دروازه است، نه تزئین. پیرویی که مختصاتش بیرون از بازه اعمال‌پذیر مادر بیفتد بسط نمی‌یابد، چون در آن صورت فایل ادعایی می‌کند که گروه پشتیبانی نمی‌کند. به همین ترتیب، وقتی یک شیفت یک ارجاع را بالای ردیف یک یا چپ ستون A هل بدهد، HotXLS برای آن توکن #REF! منتشر می‌کند به‌جای اینکه بی‌صدا محدودش کند، که همان چیزی است که خود Excel برای همان ویرایش تولید می‌کرد. این ترجمه پسرعموی نزدیک بازنویسی ارجاع است که وقتی ردیف‌ها را درج یا حذف می‌کنید رخ می‌دهد، اما همان چیز نیست. آن مسیر قواعد خودش را درباره اینکه یک بازه هنگام برش یک ویرایش از میانش چه‌کاری می‌کند دارد، و در مقاله درباره تنظیم ارجاع فرمول هنگام درج و حذف جداگانه شرح داده شده. بسط مشترک ساده‌تر است: یک آفست خالص از یک لنگر معلوم است، که یک‌بار در زمان تجزیه اعمال می‌شود

کدام شکل‌های ارجاع باید شیفت‌دهنده را پوشش دهند؟

همه‌شان، وگرنه بسط یک باگ از‌دست‌دادن‌داده در لباس مبدل است. یک شیفت‌دهنده ساده‌لوح که فقط A1 و A1:B2 را می‌فهمد فرم‌های عجیب‌تر را فاسد یا حذف می‌کند، و workbookهای واقعی پر از آن‌ها هستند. مترجم فرمول مشترک HotXLS کل خانواده A1 را پیش از تصمیم‌گیری درباره اینکه چه چیزی را حرکت دهد تشخیص می‌دهد. ارجاعات workbook خارجی مانند [Book.xlsx]Sheet1!A1 و ارجاعات سه‌بعدی مانند Sheet1:Sheet3!A1 پیشوندشان را دست‌نخورده نگه می‌دارند در حالی که ارجاع سلول پسینی شیفت می‌کند. نام‌های برگه نقل‌قول‌شده جان سالم به‌در می‌برند، شامل مورد بد که برگه دقیقاً A1 نام‌گذاری شده باشد، پس 'A1'!A1 فقط بخش پس از علامت تعجب را شیفت می‌دهد. A:A ستون کامل بُعد ستونش را حرکت می‌دهد و بس؛ 1:1 ردیف کامل بُعد ردیفش را حرکت می‌دهد و بس؛ $A:$A اصلاً حرکت نمی‌کند. ارجاعات جدولی ساختاریافته مانند 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]

نام تابع تله ساکت اینجاست. یک اسکنر توکن که حروف دنبال‌شده با ارقام را می‌گیرد با کمال میل LOG10 را یک ردیف پایین‌تر به LOG11 بازنویسی می‌کند. HotXLS یک مرز ارجاع پیش و پس از یک توکن نامزد الزام می‌کند، پس یک شناسه‌ای که به یک حرف، رقم، زیرخط، نقطه، یا یک پرانتز باز ادامه یابد یک ارجاع سلول نیست. اگر در خانواده نشانه‌گذاری دیگری کار می‌کنید، همین مسئله مرزی به‌شکلی متفاوت ظاهر می‌شود، و مقاله نشانه‌گذاری R1C1 جایی که دو مدل واگرا می‌شوند را پوشش می‌دهد

چرا یک عنصر f خودبسته مقدار بعدی را می‌بلعد؟

چون یک عنصر خودبسته هیچ رویداد پایان-عنصر تولید نمی‌کند. این پرهزینه‌ترین باگ در کل این ویژگی است، و مختص هیچ تجزیه‌گر XML خاصی نیست. در TXMLReader، <f t="shared" si="4"/> دقیقاً یک رویداد Element با IsEmptyElement ست‌شده به True پرتاب می‌کند، و هرگز EndElement متناظر را پرتاب نمی‌کند. یک تجزیه‌گر که وضعیت گیرندگی-فرمولش را فقط روی EndElement می‌بندد بنابراین درون فرمول باقی می‌ماند، و متن بعدی که می‌بیند، که نتیجه کش‌شده درون <v> است، به بافر فرمول ضمیمه می‌شود. بدتر، وضعیت از مرز سلول جان سالم به‌در می‌برد، پس سلول بعدی که یک <f> واقعی دارد فرمولش توسط سلول قبلی جذب می‌شود. رفع مشکل این است که وضعیت فرمول را در خود رویداد Element پایان دهیم هرجا IsEmptyElement True باشد، و کل حل پیرو را آنجا اجرا کنیم به‌جای منتظرماندن. این یعنی خواندن t، si، ref، aca، و ca از ویژگی‌ها، اعمال بسط مشترک، نوشتن ویژگی‌های محاسبه‌مجدد روی سلول، و پاک‌کردن وضعیت مشترک، همه درون شاخه‌ای که عنصر خالی را مدیریت می‌کند. توجه کنید که فرمت هر دو املا را اجازه می‌دهد، <f t="shared" si="4"/> و <f t="shared" si="4"></f>، و دومی واقعاً یک EndElement پرتاب می‌کند. یک خواننده درست باید با آن جفت یکسان رفتار کند، به همین دلیل HotXLS هر دو املا را در یک فایل رگرسیون یکسان پوشش می‌دهد

مقادیر si پراکنده و نامرتب، و صف در‌انتظار

ویژگی si یک عدد صحیح بی‌علامت فایل‌داده‌شده است، نه یک موقعیت آرایه‌ای که شما کنترل کنید. هیچ‌چیز در شِما شاخص‌های مشترک را ملزم به متراکم‌بودن، شروع از صفر، یا ظاهرشدن به‌ترتیب صعودی نمی‌کند، و هیچ‌چیز یک فایل خصمانه یا صرفاً عجیب را از استفاده si="4294967290" روی اولین سلول بازنمی‌دارد. بنابراین اندازه‌دهی یک آرایه جست‌وجو از بزرگ‌ترین si مشاهده‌شده یک ابزار تخلیه حافظه است، نه یک بهینه‌سازی. HotXLS مسیر بازکردن workbook را روی یک جدول پراکنده مرتب نگه می‌دارد: گروه‌های مشترک زیر کلید صحیحشان در یک TStringList مرتب ثبت می‌شوند، که جست‌وجو را یک جست‌وجوی دودویی روی هرچند گروهی که واقعاً وجود دارد می‌کند، بدون هیچ رابطه‌ای با اندازه عددی شاخص‌ها. ترتیب نیمه دوم مسئله است. یک مادر معمولاً پیش از پیروهایش در ترتیب سند می‌آید، اما این یک قرارداد است نه یک قاعده، پس هر پیرویی که نتواند siاش را در لحظه‌ای که تجزیه می‌شود حل کند به یک صف در‌انتظار می‌رود. وقتی برگه تمام شود، صف علیه جدول اکنون کامل بازپخش می‌شود، و مادرهای دیرآمده یتیم‌های خودشان را حل می‌کنند. سلول‌هایی که هرگز مادری نمی‌یابند یک فرمول خالی نگه می‌دارند، که خروجی صادقانه برای فایلی است که به گروهی ارجاع می‌دهد که هرگز تعریف نکرده

بسط فرمول‌های مشترک بدون بارگذاری workbook

خواننده‌های استریمینگ همان الزام را با یک بودجه حافظه بسیار تنگ‌تر روبه‌رو می‌شوند، و آن را با یک جدول محلی-برگه حل می‌کنند. TXLSDirectReader و TXLSRowCursor هر دو پیروها را به فرمول‌های کامل به‌ازای هر سلول بسط می‌دهند در حالی که رفتار حافظه-محدود و تصویرشان را حفظ می‌کنند، پس یک گذر فقط-جلوسو روی یک برگه ۳۰۰ مگابایتی هنوز متن فرمول واقعی به شما می‌دهد

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;

دو محدودیت از آن طراحی بیرون می‌آید. اول، تصویرسازی هرگز نمی‌تواند مادر را رد کند. یک فیلتر ردیف که با FirstRow و LastRow ست شده، یا یک فیلتر ستون که با IncludeColumn ساخته شده، ممکن است انتشار سلول مادر به callback شما را رد کند، اما تجزیه‌گر همچنان باید si، مختصات لنگر، بازه اعمال‌پذیر، و متن فرمولش را ثبت کند، وگرنه هر پیرو درون تصویرسازی به هیچ حل می‌شود. فقط کار سمت-پیرو، شیفت و رمزگشایی مقدار، امن است که رد شود. دوم، جدول به‌ازای هر برگه است و طول عمرش باید صریحاً مدیریت شود: TXLSRowCursor یک نمونه را برای طول یک گذر برگه نگه می‌دارد و روی راه‌اندازی‌مجدد، تعویض برگه، پایان فایل، استثنا، و بستن پاکش می‌کند، پس گروهی که روی برگه یک تعریف شده هرگز نمی‌تواند به برگه دو نشت کند. چون مسیر استریمینگ یک حلقه داغ است، از یک هش صحیح آدرس‌گشوده استفاده می‌کند نه جدول رشته‌ای مرتب، که یک تبدیل صحیح-به-رشته به‌ازای هر سلول را اجتناب می‌کند

هنگام ذخیره چه اتفاقی می‌افتد، و مرزها کجا هستند

وقتی یک پیرو بسط داده شد، یک فرمول معمولی است، و HotXLS آن را به‌عنوان یک عنصر <f> مستقل بدون t="shared" و بدون si بازمی‌نویسد. سفر رفت‌وبرگشت پایدار است و نتایج کش‌شده <v> جان سالم به‌در می‌برند، اما خروجی برای یک برگه به‌شدت مشترک بزرگ‌تر از ورودی است، و گروه‌بندی‌ای که Excel ساخته در ذخیره بازسازی نمی‌شود. اگر وفاداری سطح-بایتی گروه‌های مشترک برایتان بیش از داشتن متن فرمول واقعی در هر سلول اهمیت دارد، این معامله‌ای است که می‌پذیرید. سمت XLS به‌طور اتفاقی متفاوت است: رکورد SHRFMLA در BIFF8 رمزگذاری و نویسنده خودش را دارد، با یک سوییچ گروه‌مشترک روی workbook

دو چیز مرتبط صراحتاً فرمول‌های مشترک نیستند هرچند عنصر <f> را به‌اشتراک می‌گذارند. فرمول‌های آرایه‌ای قدیمی CSE از t="array" با یک ref که بازه لنگرشده را می‌پوشاند استفاده می‌کنند، و آرایه‌های پویا از همان املای t="array" استفاده می‌کنند اما با یک ویژگی cm شناسایی می‌شوند که از راه cellMetadata به یک رکورد XLDAPR زنجیر می‌شود. رفتارکردن با یک سلول ریزش آرایه پویا به‌عنوان یک پیرو مشترک یا CSE یک باگ صحت واقعی است، و تفکیک در مقاله فرمول‌های آرایه پویا و ریزش پوشش داده شده. سه مورد را به‌عنوان سه تجزیه‌گر که تصادفاً یک نام تگ را به اشتراک می‌گذارند بخوانید، و کد صادق می‌ماند

بسط فرمول مشترک، خواننده‌های استریمینگ، و مترجم ارجاع که در اینجا شرح داده شد به‌عنوان بخشی از مؤلفه HotXLS Excel برای Delphi و C++Builder ارائه می‌شود؛ صفحه محصول مرجع کامل فرمول و API خواندن مستقیم را شامل ویژگی‌های تصویرسازی استفاده‌شده در بالا در بر دارد