การประเมิน Conditional Format ของ Excel ใน Delphi ด้วย HotXLS

HotXLS คือ spreadsheet component เนทีฟสำหรับ Delphi และ C++Builder และตั้งแต่เวอร์ชัน 2.209.0 มันตอบคำถามที่ Excel มักเก็บไว้กับตัวเองได้ นั่นคือสำหรับเซลล์นี้เป๊ะ ๆ กฎ conditional formatting ตัวไหนทำงาน และให้สี fill, ฟอนต์, data bar หรือไอคอนอะไร คำตอบนั้นคือสิ่งที่คุณต้องการทันทีที่ผลลัพธ์ของคุณเป็นรายงาน HTML, PDF หรือ grid ที่คุณวาดเอง

นี่เป็นปัญหาต่างจากการสร้างกฎ บันทึกก่อนหน้านี้สองบทความครอบคลุมด้านการเขียน conditional formatting กับสไตล์ rich text พูดถึงการผูกกฎและ differential format เข้ากับช่วง และ การแบ่งส่วน conditional format ที่ยึดตำแหน่ง พูดถึงสิ่งที่เกิดขึ้นกับช่วงของกฎเมื่อมีการแทรกหรือลบแถวและคอลัมน์ ทั้งสองเป็นเรื่องเชิงโครงสร้าง บทความนี้เป็นเรื่องความหมาย เมื่อมี workbook ที่มีกฎอยู่แล้ว คำนวณ highlight ให้

ทำไมรูปแบบไฟล์ถึงไม่บอกว่าเซลล์ไหนสว่างขึ้น

คำตอบสั้น ๆ คือ ECMA-376 และ ISO 29500-1 กำหนดเรื่องการจัดเก็บ ไม่ใช่การประเมินผล element conditionalFormatting (§18.3.1.18) พก sqref และรายการ child ของ cfRule (§18.3.1.10) และแต่ละกฎพก type, operator ทางเลือก, priority, flag stopIfTrue, child formula หนึ่งหรือสองตัว และสำหรับตระกูลภาพจะมีชุด threshold cfvo ทุกตัวในนี้อธิบายสิ่งที่ผู้ใช้ตั้งค่าไว้อย่างซื่อสัตย์ และไม่มีตัวไหนเป็นอัลกอริทึมเลย สำหรับครึ่งหนึ่งของประเภทกฎ ช่องว่างนั้นไม่สำคัญ cellIs กับ operator="greaterThan" หมายถึงมากกว่า และ containsText หมายถึงมี substring อยู่ ช่องว่างเปิดกว้างขึ้นในตระกูลรวม กฎ top10 ที่ rank="10" และ percent="1" เหนือเซลล์ตัวเลขที่มีข้อมูล 27 เซลล์จะเน้นกี่เซลล์ สองจุดเจ็ดไม่ใช่ตัวเลข ปัดกลม ปัดลง หรือปัดขึ้น สเปกเงียบ และเลือกผิดหมายความว่า PDF ของคุณไม่ตรงกับ workbook ที่ลูกค้าเปิดอยู่ข้าง ๆ

กฎระดับเซลล์เดียวและจุดที่ TCondFormatRule.Evaluate หยุด

HotXLS เลือกทำครึ่งที่ถูกกว่าก่อน TCondFormatRule.Evaluate ใน lxCondFormat.pas เพิ่มเข้ามาใน 2.199.0 ตอบว่ากฎหนึ่งทำงานสำหรับเซลล์หนึ่งหรือไม่โดยไม่รู้อะไรเกี่ยวกับส่วนที่เหลือของช่วงเลย มันจัดการ comparison operator ของ BIFF ทั้งแปดตัวที่อยู่หลัง cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), กฎ expression แบบอิสระที่ประเมินที่เซลล์เพื่อให้ relative reference คำนวณใหม่อย่างถูกต้อง, predicate ข้อความสี่ตัว และ predicate ของค่าว่างกับข้อผิดพลาด threshold มาจาก FFormula1 และ FFormula2 ที่คำนวณผ่าน TXLSCalculator.GetRangeValue ที่ตำแหน่งเซลล์ และขอบเขตที่กลับด้านจะถูกสลับแทนที่จะถูกปฏิเสธ

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

ส่วนที่ตรงไปตรงมาของเมธอดนี้คือสิ่งที่มันปฏิเสธจะเดา top10, aboveAverage, belowAverage, duplicateValues และ uniqueValues คืนค่า False ไม่ใช่เพราะมันยาก แต่เพราะมันตัดสินไม่ได้จากเซลล์เดียว ทุกตัวต้องการสถิติเหนือทั้งโดเมน สี่ตระกูลภาพ dataBar, colorScale2, colorScale3 และ iconSet คืน False ด้วยเหตุผลต่างกัน มันไม่เคยสร้าง boolean เลย มันสร้าง payload สำหรับเรนเดอร์ และ Boolean return type คือรูปแบบที่ผิดสำหรับมัน

ตัวประเมินระดับ worksheet หลีกเลี่ยงการสแกน sheet ซ้ำได้อย่างไร

ด้วยการคำนวณปริมาณที่ใช้ร่วมกันทุกตัวเพียงครั้งเดียวตอนสร้าง และไม่ทำอีกเลย TXLSXConditionalFormatEvaluator ใน lxHandleX.pas คือ snapshot แบบไม่เปลี่ยนแปลงสำหรับหนึ่ง worksheet สร้างผ่าน TXLSXWorksheet.CreateConditionalFormatEvaluator และการออกแบบทั้งหมดของมันคือการป้องกันไม่ให้เกิดการทำงานแบบไร้เดียงสาที่ทุกเซลล์ที่ถูกวาดกระตุ้นการสแกนช่วงเต็มทั้งหมด

มีสี่อย่างเกิดขึ้นใน constructor sqref หลายพื้นที่ที่แตกต่างกันแต่ละตัวจะถูก parse เพียงครั้งเดียวเข้าไปใน TXlsxCfRangeSnapshot ดังนั้นกฎสิบตัวที่ใช้ช่วงเดียวกันจะใช้ parse เดียวและ pass สถิติเดียวร่วมกัน pass นั้นจะ stream ค่าเฉลี่ย, ค่าเบี่ยงเบนมาตรฐานของประชากร, ค่าต่ำสุดและค่าสูงสุดเหนือเซลล์ที่มีข้อมูลในการเดินครั้งเดียว และจะเก็บ array ตัวเลขที่เรียงลำดับแล้วก็ต่อเมื่อกฎ Top/Bottom หรือ percentile ต้องการ order statistic จริง ๆ เท่านั้น key ของ duplicate และ unique ถูกสร้างแบบ Unicode-safe และเรียงลำดับเป็นชุดครั้งเดียวแทนที่จะทำต่อการค้นหาแต่ละครั้ง จากนั้นแกนแถวจะถูกตัดออกเป็นแถบที่ขอบเขตของแต่ละพื้นที่ ดังนั้น EvaluateCell จึง binary-search แถบและไปดูเฉพาะกฎที่ช่วงของมันอาจไปถึงแถวนั้นได้เท่านั้น

อย่างที่สี่คือสิ่งที่สำคัญที่สุดในระดับใหญ่ สูตรกฎแบบสัมพัทธ์ เช่น =A1>AVERAGE($A$1:$A$100) มีความหมายต่างกันในทุกเซลล์ของโดเมน และการทำงานแบบตรงไปตรงมาจะ compile syntax tree ใหม่ต่อเซลล์ TXlsxCfRulePlan compile มันครั้งเดียวและประเมิน tree เดียวกันซ้ำผ่าน coordinate offset ที่ย้อนกลับได้ ซึ่งรักษาพฤติกรรม anchor ของ Excel ไว้โดยไม่ต้อง allocate syntax-tree ต่อเซลล์ กฎจะถูกจัดชั้นตาม priority และการจับคู่กับกฎที่ StopIfTrue ตั้งไว้จะหยุดลูป ตรงตามที่ Excel ตัดวงจรสั้น

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Excel ปัดกลมกฎ Top 10 percent จริง ๆ อย่างไร

มันปัดลง โดยมีค่าต่ำสุดคือหนึ่ง และรวม tie ที่จุดตัดด้วย เรื่องนี้ไม่ได้เขียนไว้ที่ไหนใน ISO 29500-1 เลย มันถูกตรึงไว้ด้วยการทดสอบ Excel 16 ด้วย workbook ที่สร้างขึ้นเองแล้วอ่านย้อนกลับว่าแอปพลิเคชันเน้นเซลล์ไหน HotXLS ทำแบบนั้นเป๊ะ ๆ จำนวน rank คือ Floor(Count * Min(Rank, 100) / 100) ยกขึ้นเป็น 1 เมื่อตกที่ศูนย์ clamp ให้เท่ากับจำนวนที่มีข้อมูล และค่าตัดจะถูกเปรียบเทียบด้วย >= ดังนั้นทุกเซลล์ที่เท่ากับขอบเขตจะถูกเน้นแม้ว่ามันจะเกินจำนวนที่ร้องขอไปก็ตาม ยี่สิบเจ็ดค่าและกฎ 10 percent จะเน้นสองเซลล์ บวกกับเซลล์ใดก็ตามที่เสมอกับเซลล์ที่สอง

กฎ above-average ซ่อนความคลุมเครือที่สอง aboveAverage กับ stdDev="1" เลือกเซลล์ที่สูงกว่าค่าเฉลี่ยหนึ่งค่าเบี่ยงเบนมาตรฐาน แต่ค่าเบี่ยงเบนของ sample กับของ population ต่างกันด้วย Bessel correction และไม่ตรงกันอย่างเห็นได้ชัดในช่วงเล็ก ๆ ซึ่งเป็นจุดที่ conditional formatting มักถูกใช้พอดี Excel 16 ใช้ค่าเบี่ยงเบนของ population และ HotXLS ทำตาม โดย flag equalAverage จะเปลี่ยนการเปรียบเทียบแบบเข้มงวดให้รวมค่าเท่ากันด้วยเฉพาะเมื่อไม่มีแถบค่าเบี่ยงเบนอยู่ในการเล่นเท่านั้น กฎ duplicate และ unique เปลี่ยนไปใช้ key identity แทน ถ้าเซลล์หนึ่งมีตัวเลข 100 และอีกเซลล์มีข้อความ "100" Excel จะถือว่าทั้งสองเป็น duplicate key เดียวกัน ดังนั้น HotXLS จึง normalize ข้อความตัวเลขให้เข้าไปในพื้นที่ key ตัวเลขแทนที่จะเทียบสาย string ดิบ เซลล์ว่างเปล่าคือกรณีตรงกันข้าม เซลล์ว่างจริง ๆ จะมีส่วนร่วมในการนับช่วงแต่ตัวมันเองจะไม่ถูกจัดสไตล์ ดังนั้นเซลล์ว่างในคอลัมน์จึงไม่สว่างขึ้นทั้งหมดในฐานะ duplicate ของกันและกัน

Color scale และ icon set: การประมาณค่าและกฎขอบเขต

ตระกูลภาพจะคำนวณเป็นตัวเลขพร้อมเรนเดอร์แทนที่จะเป็น boolean และพฤติกรรมขอบของมันถูกตรึงไว้ด้วยวิธีเดียวกัน สำหรับ color scale ที่มี threshold ตัวเลขชัดเจน HotXLS จะ clamp สัดส่วนตำแหน่งให้อยู่ในช่วงปิดศูนย์ถึงหนึ่ง แล้วประมาณค่าต่อ channel ด้วยการตัดทอนแทนการปัดกลม ค่าต่ำกว่าจุดต่ำสุดจะได้สีต่ำสุดแทนที่จะเป็นสีที่ประมาณค่านอกช่วง scale สามจุดเลือกคู่ของมันด้วยการเทียบกับจุดกึ่งกลาง และ scale ที่เสื่อมสภาพซึ่งปลายทั้งสองมี threshold เท่ากันจะยุบลงเป็นสีบนสุดแทนการหารด้วยศูนย์ Icon set ต้องการความระมัดระวังแบบตรงข้าม เพราะ cfvo แต่ละตัวหลังตัวแรกพกความเข้มงวดของการเปรียบเทียบของตัวเอง HotXLS อ่าน ThresholdEqualsInclude ต่อ threshold และใช้ >= หรือ > ตามนั้น เดินขึ้นไปเพื่อให้ threshold สูงสุดที่ผ่านเงื่อนไขชนะ icon index ชุดที่กลับด้านจะสลับดัชนีที่คำนวณได้แทนที่จะสลับ threshold, การ override ต่อไอคอนสามารถดึง glyph จากตระกูลอื่นได้ และ threshold ที่ไม่ถูกต้องใด ๆ จะยกเลิกกฎแทนที่จะสร้างไอคอนที่ดูสมเหตุสมผลแต่ผิด

ป้อนข้อมูลให้ grid, HTML export และ PDF จากผลลัพธ์เดียว

เพราะ EvaluateCell คืน TXLSXCfCellResult ที่คำนวณเสร็จสมบูรณ์แล้ว มีสี fill และฟอนต์แบบ differential พร้อม theme tint ที่ใช้แล้ว, ตัวหนา, ตัวเอียง, ขีดเส้นใต้, number format id, ระยะ bar บวกและลบตามทิศทาง, ตำแหน่งแกน, ตระกูลไอคอนและดัชนี ทุกตัวที่ใช้ผลลัพธ์อ่าน record เดียวกัน และไม่มีตัวไหนต้องเข้าใจการทำงานภายในของกฎ HotXLS ใช้เส้นทางเดียวนี้สำหรับ HTML export, PDF export และตัวแสดงผลแบบโต้ตอบ ซึ่งเป็นวิธีเดียวที่ใช้ได้จริงในการป้องกันไม่ให้ตัวเรนเดอร์สามตัวเบี่ยงเบนออกจากกัน เวอร์ชัน 2.210.0 เชื่อมมันเข้ากับ TXLSWorkbookViewer ซึ่ง cache ตัวประเมินที่เตรียมไว้แล้วหนึ่งตัวต่อ worksheet ที่ทำงานอยู่ และใช้ซ้ำในการเลื่อน การเลือก และการวาดใหม่ ปล่อยมันเมื่อ workbook หรือ worksheet เปลี่ยน การสร้าง snapshot ใหม่ทุกครั้งที่ Paint จะทำลายการออกแบบตอนสร้างทั้งหมดไป cache นั้นก็เป็นเหตุผลที่ TXLSWorkbookViewer.RefreshConditionalFormats มีอยู่ snapshot นั้นไม่เปลี่ยนแปลง ดังนั้นถ้าคุณแก้ไข workbook ที่ผูกอยู่โดยตรง สถิติรวมและ threshold ที่คำนวณไว้แล้วจะค้างจนกว่าคุณจะเรียกมัน

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

สิ่งที่ตัวประเมินจะไม่ทำให้คุณ

มีขอบเขตสามอย่างที่ควรพูดให้ชัด TCondFormatRule.Evaluate แบบเซลล์เดียวคลาสสิก และ TXLSXConditionalFormatEvaluator ระดับ worksheet เป็นพื้นผิวต่างกันที่มีความสามารถต่างกัน และตัวเซลล์เดียวปฏิเสธตระกูลรวมและตระกูลภาพโดยตั้งใจแทนที่จะประมาณค่ามัน ถ้าคุณต้องการ Top/Bottom หรือ color scale ให้สร้างตัวประเมิน ช่วงเวลาแบบสัมพัทธ์ขึ้นอยู่กับนาฬิกาเครื่องตอนที่ประเมิน ดังนั้นกฎ timePeriod จะเรนเดอร์ต่างกันใน PDF ที่สร้างวันนี้กับที่สร้างสัปดาห์หน้า ซึ่งเป็นพฤติกรรมที่ถูกต้องและยังคงเป็น support ticket ที่รอเกิดขึ้นถ้าคลังเก็บของคุณต้องคงที่ทุกไบต์ ข้อที่สามเป็นเรื่องไวยากรณ์มากกว่าเทคนิค ไวยากรณ์สูตร conditional-format ห้ามใช้ structured table reference ดังนั้นกฎจึงระบุคอลัมน์ของตารางด้วยชื่อแบบที่สูตร worksheet ทำไม่ได้ และนั่นคือข้อจำกัดของรูปแบบ ไม่ใช่ของการทำงาน

ถ้าคุณกำลังสร้างผลลัพธ์รายงาน export pipeline หรือ grid แบบกำหนดเองที่ต้องตรงกับ Excel เซลล์ต่อเซลล์ ผลลัพธ์ที่คำนวณแล้วชุดเดียวกันก็ขับเคลื่อน VCL spreadsheet grid แบบกำหนดเอง ที่กล่าวไว้ในที่อื่นบนบล็อกนี้ เอกสาร API ฉบับเต็ม โมเดลกฎ และรุ่นทดลองดาวน์โหลดสำหรับ HotXLS Delphi spreadsheet component มีอยู่ที่หน้าผลิตภัณฑ์