การจัดรูปแบบตามเงื่อนไข ข้อความแบบพ้นจากรูปแบบปกติ

กฎ conditional formatting ใน OOXML คือสองสิ่งที่แยกจากกันแต่สวมชื่อเดียวกัน เงื่อนไข (การเปรียบเทียบ สูตร การจับคู่ข้อความ) ตัดสินว่าเซลล์ไหนเข้าเกณฑ์ รูปลักษณ์ (differential format record ซึ่งเรียกว่า dxf ในศัพท์ ECMA-376) ตัดสินว่าเซลล์เหล่านั้นหน้าตาเป็นอย่างไร dialog ของ Excel ซ่อนรอยต่อนี้ไว้โดยให้คุณกรอกทั้งสองอย่างพร้อมกัน แต่ HotXLS ไม่ทำแบบนั้น สร้างกฎ cellIs จาก Delphi แล้วข้ามส่วนสไตล์ไป กฎนั้นก็ยังใช้งานได้ ช่วงยังถูกต้อง สูตรยัง evaluate เป็น true บนเซลล์ที่ถูกต้องเป๊ะ ๆ และไม่มีอะไรเปลี่ยนสีเลย เพราะคำสั่งของกฎคือ "true แล้วไม่ต้องทาสีอะไร" ช่องว่างระหว่างเงื่อนไขกับผลลัพธ์นี้คือสิ่งแรกที่ต้องทำให้ถูกต้อง และมันคือสาเหตุของกฎส่วนใหญ่ที่ดูถูกต้องใน Manage Rules แต่ไม่ไฮไลต์อะไรเลย

HotXLS เขียน conditional formatting ลงในไฟล์ทั้ง BIFF8 .xls และ OOXML .xlsx แบบ native และมันทำแบบเดียวกันกับ rich text run และโมเดล cell-style แบบ pooled ทั้งสามฟีเจอร์นี้มีสายเชื่อมโยงกันมากกว่าที่พื้นผิว API แบบแบน ๆ จะบ่งบอก และจุดที่ output มักเบี่ยงเบนไปจากความตั้งใจมักเป็นรอยต่อระหว่างพวกมัน

เงื่อนไขต้องการผลลัพธ์: สไตล์ dxf

บน worksheet ของ XLSX กฎการเปรียบเทียบมาจาก AddConditionalFormat ซึ่งรับ range, operator จาก TXLSXCfOperator และสูตรหรือค่า literal จากนั้นคืนค่า index ของกฎใหม่ใน collection ConditionalFormats ของ sheet object ของกฎที่ index นั้นเปิด property Style ออกมา และนั่นคือที่ที่ไฮไลต์อาศัยอยู่ ตั้งค่า fill ให้มัน แล้วเซลล์ที่เข้าเกณฑ์ก็จะได้ fill นั้นไป ถ้าปล่อยไว้ไม่แตะต้อง คุณก็จะได้กฎที่มองไม่เห็นแบบที่อธิบายไว้ข้างต้น

แผนภาพกฎ cellIs ของ HotXLS ที่สร้างจาก Delphi เป็นสองครึ่ง: AddConditionalFormat คืนดัชนีกฎของเงื่อนไข, ConditionalFormats[Idx].Style.SetFillBgColor ให้ผลลัพธ์ dxf และกฎที่ไม่เคยตั้งสไตล์ตรวจสอบผ่านสบายแต่ไม่ระบายอะไรเลย — เงื่อนไขตัดสินว่าเซลล์ใดมีคุณสมบัติ และสไตล์ dxf ตัดสินว่าพวกมันหน้าตาเป็นอย่างไร การข้ามสไตล์จึงสร้างกฎที่มองไม่เห็นขึ้นมา

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // ค่าเบี่ยงเบนติดลบ: fill สีแดงอ่อน
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Order ID ที่ซ้ำกันจะถูกตั้งค่าสถานะด้วยวิธีเดียวกัน
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // กฎสูตรกำหนดเอง: ไฮไลต์แถวที่ actual ต่ำกว่า 90% ของ target
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

สีตรงนี้เป็นค่า ARGB แบบ 32 บิต ดังนั้น $FFFFC7CE คือ "light red" ของ Excel ที่คุณคุ้นเคยจาก dialog โดยมี alpha byte ทึบแสงเต็มค่าอยู่หน้า RGB กฎทุกชนิดที่ทำงานตามเงื่อนไขระดับเซลล์เดินตามรูปแบบ create-แล้วค่อย-style เหมือนกันหมด ตัวจับคู่ข้อความ (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) คืนค่า index ที่คุณค่อยไป style ทีหลัง และ AddCondFormatTop10, AddCondFormatAboveAverage รวมถึงตัวตรวจจับช่องว่างเปล่าและข้อผิดพลาดก็ทำแบบเดียวกัน เรียนรู้รูปแบบนี้ครั้งเดียว แล้วตระกูลข้อความ-และ-การเปรียบเทียบทั้งหมดก็จะทำงานเหมือนกัน

Data bar, color scale และ icon set ทาสีตัวเอง

กฎประเภทภาพทำงานสลับด้าน มันพกรูปลักษณ์ของตัวเองไว้ในนิยามของกฎ และเพิกเฉยต่อ property Style ไปเลย กำหนด fill ให้กฎ data bar แล้วไม่มีอะไรเกิดขึ้น ซึ่งอ่านดูเหมือน bug จนกว่าอนุกรมวิธานจะเข้าที่: AddCondFormatDataBar รับสีของแท่งเป็น argument ตรง ๆ color scale แบบสองจุดและสามจุดก็รับสีปลายทางของมันด้วยวิธีเดียวกัน และ AddCondFormatIconSet เลือกหนึ่งใน icon set 26 ชนิด เช่น icsTrafficLights3 ตรงนี้ไม่มี style record แยกต่างหากให้ลืมเลย เพราะไม่มี style record แยกต่างหากอยู่เลยตั้งแต่แรก

พารามิเตอร์ที่ควรพิจารณาในเรียกเหล่านี้คือจุดยึดค่า ซึ่งพิมพ์เป็น TXLSCfValueKind จุดปลายของแท่งหรือ scale วางไว้ที่ค่าต่ำสุด/สูงสุดของ range ได้ ที่ตัวเลข literal ได้ ที่เปอร์เซ็นต์หรือเปอร์เซ็นไทล์ได้ หรือที่ผลลัพธ์ของสูตรก็ได้ ค่าเริ่มต้น คือ min-of-range และ max-of-range ทำงานดีบนข้อมูล demo ที่เรียบร้อย แล้วก็หักหลังคุณบนข้อมูลจริงที่มี outlier: ค่าที่พุ่งกระโดดค่าเดียวยืด scale ออกและทำให้แท่งอื่นทุกแท่งแบนราบลงเหลือแค่ตอ เมื่อ dashboard ต้องถูกอ่านข้ามช่วงเวลากัน ให้ยึดจุดปลายไว้กับตัวเลขคงที่หรือเปอร์เซ็นไทล์แทน เพื่อให้ครึ่งแท่งในเดือนมีนาคมหมายถึงปริมาณเดียวกับครึ่งแท่งในเดือนเมษายน แท่งที่ปรับ scale อัตโนมัติเทียบได้แค่กับตัวมันเองเท่านั้น

ตัวเขียน XLS ครอบคลุมกฎสี่ชนิด ไม่มากกว่านั้น

ฝั่ง BIFF8 แบบเก่าไม่ใช่กระจกเงาย่อส่วนของฝั่ง XLSX มันคือ subset ที่ตั้งใจเลือกไว้ ส่วนหน้าของ XLS สร้างรูปแบบกฎแบบมีเงื่อนไขได้เป๊ะสี่แบบเท่านั้น คือ data bar, color scale สองสี, color scale สามสี และ icon set โดยปล่อยออกมาเป็น CF12 record เข้าไปใน stream มันไม่มี creation API สำหรับกฎ cellIs expression หรือข้อความเลย กฎประเภทเหล่านั้นที่มีอยู่แล้วในไฟล์ที่คุณเปิดจะถูกอ่าน เก็บไว้ และเขียนกลับโดยไม่เปลี่ยนแปลง ดังนั้นการเปิดและ save ไฟล์ .xls ของลูกค้าซ้ำจึงไม่มีวันทำลาย formatting ที่มันพกมาแต่แรก สิ่งที่คุณทำไม่ได้คือการสร้าง threshold highlighting ขึ้นมาใหม่ตั้งแต่ต้นลงใน .xls ทางเลือกตรงนั้นคือปลอมมันขึ้นมาด้วย cell fill ธรรมดาที่คำนวณในโค้ด หรือไม่ก็ทำให้ deliverable เป็น .xlsx ซึ่งตระกูลกฎเต็มรูปแบบพร้อมให้ใช้

นี่คือข้อจำกัดที่ควรตกลงกันก่อนที่ data layer จะมีอยู่ ไม่ใช่หลังจากนั้น เพราะมันเปลี่ยนการตัดสินใจเรื่อง file format สำหรับทุกอย่างที่มีรูปร่างเป็น dashboard ทีมที่เลือก .xls เพื่อความเข้ากันได้ แล้วค่อยไปกำหนด spec รายงาน KPI ที่มี threshold แบบ cellIs ได้เลือกสองสิ่งที่เข้ากันไม่ได้ และเวลาที่ถูกกว่าในการสังเกตเห็นคือตอนตัดสินใจเรื่อง format ไม่ใช่สามสัปดาห์เข้าไปแล้วในการสร้าง

การซ้อนกฎ ลำดับความสำคัญ และ range ที่ซ้อนทับกัน

dashboard จริงแทบไม่เคยรันกฎเดียวต่อหนึ่ง range คอลัมน์ค่าเบี่ยงเบนอาจพก data bar สำหรับขนาด กฎ cellIs สำหรับ threshold ที่ตายตัว และกฎ expression ระดับแถวอยู่เหนือทั้งสองอย่างสำหรับการยกระดับ TXLSXConditionalFormat แต่ละตัวเปิด property Priority ออกมา และ Excel จะแก้ปัญหากฎที่แข่งกันตามลำดับความสำคัญ เมื่อกฎสองตัวอยากทาสีเซลล์เดียวกัน ผู้ชนะจะถูกตัดสินด้วยตัวเลขที่คุณตั้งไว้ ไม่ใช่ด้วยลำดับที่ผู้ตรวจสอบบังเอิญเลื่อนผ่านใน dialog Manage Rules

ปฏิบัติต่อ priority แบบเดียวกับที่โปรแกรมวาดรูปปฏิบัติต่อ z-order กำหนดมันอย่างตั้งใจทุกที่ที่กฎสองตัวอาจแตะเซลล์เดียวกันได้ และเว้นช่องว่างระหว่างค่าไว้เพื่อให้กฎที่มาทีหลังแทรกเข้าไปได้โดยไม่ต้องเรียงเลขใหม่ทั้งหมด ในที่ที่กฎไม่มีทางชนกันได้ เช่น data bar ที่จำกัดอยู่แค่คอลัมน์ E และกฎข้อความที่จำกัดอยู่แค่คอลัมน์ G ลำดับการสร้างก็เพียงพอแล้ว priority ไม่คุ้มที่จะใส่ใจ ใช้ความใส่ใจนั้นไปที่ขอบเขตของ range แทน เพราะ bug ที่แพงที่สุดตรงนี้แทบไม่เคยเป็นการสลับลำดับ priority มันมักเป็น range แบบ B2:B200 บนรายงานที่โตขึ้นเป็น 350 แถว ซึ่งส่วนหางที่ไม่ถูกครอบคลุม render ออกมาเป็นเซลล์ธรรมดาที่ดูเหมือนข้อมูลปกติเป๊ะ ๆ ดึงทุก range ของกฎมาจากค่าจำนวนแถวสุดท้ายค่าเดียวกันที่ขับเคลื่อน chart series และ range การตรวจสอบข้อมูลที่อื่นใน workbook แล้วส่วนหางก็จะไม่หลุดหายไปอีก

นิสัยการตรวจสอบอย่างหนึ่งคุ้มค่าที่จะรักษาไว้ หลังการสร้าง ให้เปิดไฟล์ใน Excel เลือก range ที่ format ไว้ แล้วไล่ดู Manage Rules หนึ่งรอบทุกครั้งที่มีการเปลี่ยนแปลง template Conditional formatting เป็นหนึ่งในไม่กี่พื้นที่ที่ตัว renderer ที่น่าเชื่อถือได้จริงมีแค่แอปพลิเคชันที่อ่านไฟล์นั้นเอง ดังนั้น unit test บน XML พิสูจน์แค่ว่ากฎถูกเขียนแล้ว ไม่ได้พิสูจน์ว่า Excel ทาสีมันตามที่คุณตั้งใจ การมองด้วยตาสักนาทีปิดช่องว่างนั้นได้

Rich text: หลายรูปแบบภายในเซลล์เดียว

เซลล์ rich-text ในโมเดลของ XLSX เก็บลิสต์ของ run ไว้ โดยแต่ละ run คือช่วงข้อความบวกกับ attribute ฟอนต์ของตัวเอง คุณสร้างลิสต์ไว้ต่างหากเป็น object TXLSXRichText เพิ่ม run เข้าไป แล้วค่อยแนบทั้งชุดเข้ากับเซลล์ กฎเรื่อง ownership คือส่วนที่กัดคุณได้ การกำหนดค่าให้ Cell.RichText มอบ ownership ของ object นั้นให้เซลล์ และเซลล์จะ free มันตอนที่ตัวมันเองถูกทำลาย ถ้าคุณ free มันเองด้วยล่ะก็ คุณจะได้ double-free ชนิดที่เงียบสนิทตลอด run ที่ก่อมันขึ้น แล้วโผล่มาเป็น crash ที่ไหนสักที่ที่ไม่เกี่ยวข้องเลยในภายหลังนานมาก

แผนภาพ rich text run ของ HotXLS ใน Delphi: การกำหนดออบเจ็กต์ TXLSXRichText ให้ Cell.RichText ย้ายความเป็นเจ้าของไปที่เซลล์ การ Free ครั้งที่สองจึงทำ heap เสียในภายหลังอีกนาน และสีของ run จะมีผลก็ต่อเมื่อล้าง ColorIsAuto แล้วเท่านั้น — ความเป็นเจ้าของรายการ run ย้ายไปที่เซลล์ตอนกำหนดค่า และการกำหนดสีจะติดอยู่ก็ต่อเมื่อ ColorIsAuto ถูกเคลียร์แล้วเท่านั้น

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // ownership ย้ายไปที่เซลล์แล้ว: ห้าม Free
end;

ColorIsAuto := False ที่เขียนไว้ตรง ๆ ไม่ใช่การประดับตกแต่งที่เลือกทำหรือไม่ทำก็ได้ run หนึ่งพก flag สีอัตโนมัติไว้ และการกำหนดสีจะมีผลก็ต่อเมื่อ flag นั้นถูกล้างออกแล้วเท่านั้น กำหนด Color แล้วลืม ColorIsAuto แล้ว run ก็จะออกมาตัวหนาแต่ดำดื้อ ๆ ไม่ยอมเปลี่ยน โดยไม่มี error ให้ชี้สาเหตุเลย run ยังรองรับ strikethrough ตัวขีดเส้นใต้แบบต่าง ๆ และการจัดแนวแนวตั้งสำหรับ superscript กับ subscript ด้วย ในขณะที่ PlainText ทำให้ลิสต์ทั้งหมดแบนราบกลับมาเป็นสตริงเดียวเมื่อคุณต้องการ export หรือ diff เนื้อหาข้อความ

Rich text ระดับเซลล์มีแค่ใน XLSX เท่านั้น ส่วนหน้าของ XLS ไม่มี public API สำหรับเขียนมัน แม้ว่า run จะมีให้ใช้ที่นั่นบน comment และ text box ผ่าน TextRuns และ rich string ที่อ่านมาจาก .xls ที่มีอยู่แล้วก็รอด round-trip ได้อย่างสมบูรณ์ แรงดึงเป็นแบบเดียวกับ conditional formatting: อะไรก็ตามที่ผสมรูปแบบไว้ภายในเซลล์เดียวควรอยู่ในตัวเขียน XLSX

pool ของสไตล์ กับ off-by-one ที่หลุดออกไปกับ release

การจัดสไตล์เซลล์ธรรมดาในโมเดลของ XLSX เดินผ่าน collection แบบ pooled บน workbook Fonts.Add, Fills.AddSolid และ Borders.Add แต่ละตัวลงทะเบียนนิยามหนึ่งชุดและคืนค่า index ของมันใน pool index เหล่านั้นเริ่มนับที่ 0 property ฝั่งเซลล์ที่ใช้ค่าพวกนี้ เช่น FontIndex สงวนค่า 0 ไว้ให้ "default" ดังนั้นค่าที่คุณกำหนดให้เซลล์คือ index ของ pool บวกหนึ่ง:

แผนภาพ off-by-one ของ pool สไตล์ XLSX ใน HotXLS: Fonts.Add คืนดัชนี pool เริ่มจากศูนย์ ขณะที่ FontIndex ของเซลล์เริ่มจากหนึ่งโดยสงวนศูนย์ไว้ให้ค่าเริ่มต้น การตกบวกหนึ่งจึงทำให้หัวทุกคอลัมน์เรนเดอร์ไร้สไตล์อย่างเงียบๆ — ดัชนีของพูลเริ่มที่ศูนย์ และดัชนีเซลล์สงวนศูนย์ไว้สำหรับค่าเริ่มต้น ฝั่งเซลล์จึงต้องบวกหนึ่งเสมอ

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // index ของ pool เริ่มนับที่ 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // index ของเซลล์ เริ่มนับที่ 1

ตัด + 1 ออกไป แล้วทุก header ก็จะร่วงกลับไปใช้ฟอนต์ default ไม่มี exception ไม่มีคำเตือน มีแค่ workbook ที่ดูเหมือนไม่มีใครจัดสไตล์ให้มันเลย ข้อผิดพลาดลำดับที่สองซ่อนอยู่ใน loop: เรียก Fonts.Add หนึ่งครั้งต่อหนึ่งแถว นิยามฟอนต์ที่เหมือนกันจะถูก deduplicate ดังนั้นไฟล์จึงไม่เสียหาย แต่งานที่ทำไปก็สูญเปล่า และ pool ของ alignment โดยเฉพาะจะคืน object ใหม่กลับมาทุกครั้งที่เรียก แทนที่จะพับรายการที่ซ้ำกันเข้าด้วยกัน สร้างสไตล์ไม่กี่แบบครั้งเดียวก่อน loop แล้วนำ index ของมันกลับมาใช้ซ้ำ บนรายงานแสนแถว การเปลี่ยนแปลงเดียวนั้นคือหนึ่งในกลไกที่กล่าวถึงใน การปรับจูนประสิทธิภาพ workbook ขนาดใหญ่สำหรับ HotXLS เมื่อคุณต้องการแค่รูปลักษณ์เชิงความหมายสำเร็จรูป ทั้งสองส่วนหน้าเปิด ApplyBuiltinStyle บน range ออกมาให้ใช้ ซึ่ง map ไปยังสไตล์ในตัวของ Excel อย่าง Good, Bad, Neutral และ accent โดยที่คุณไม่ต้องแตะ pool เลย

Conditional formatting, rich text และสไตล์แบบ pooled คือไมล์สุดท้ายของรายงาน ที่ใช้หลังจากโมเดลข้อมูลและ layout ลงตัวแล้ว และขั้นตอนก่อนหน้านั้นคือหัวข้อของ การสร้างรายงานจาก template ด้วย HotXLS เอกสารอ้างอิงเต็มรูปแบบของกฎ run และสไตล์อยู่ที่หน้าผลิตภัณฑ์ HotXLS Delphi Component