HotXLS คอมโพเนนต์ Excel สำหรับ Delphi และ C++Builder แบ่ง conditional formatting หรือ data validation rule หนึ่งกฎออกเป็น rule object แยกกันสองชิ้นหรือมากกว่าโดยอัตโนมัติ ทุกครั้งที่การแทรกหรือลบแถวหรือคอลัมน์ตัดช่วงที่ rule นั้นครอบคลุมออกเป็นชิ้นๆ ที่ต้องการจุดอ้างอิงสูตรแบบสัมพัทธ์ต่างกัน แล้วกำหนดหมายเลขความสำคัญ (priority) ใหม่ที่ไม่ซ้ำกันให้กับทุก conditional-format rule พฤติกรรมนี้เปิดตัวในเวอร์ชัน 2.196 ของ XLSX engine และทำงานโดยอัตโนมัติ ไม่มีการตั้งค่าให้ปิดได้ ตัวกระตุ้นนั้นแคบแต่พบได้บ่อย คือ rule แบบ cellIs หรือ expression ที่สูตรของมันอ่านเซลล์แบบสัมพัทธ์กับช่วงของตัวเอง อยู่บนเวิร์กชีตที่ภายหลังมีการแทรกหรือลบแถวตรงกลางของช่วงนั้นพอดี
บทความส่วนใหญ่ที่เขียนเกี่ยวกับ Excel automation หยุดอยู่แค่ปัญหาข้อความสูตรเท่านั้น คือเลื่อนหมายเลขแถวและคอลัมน์ภายในทุก SUM() และทุก VLOOKUP() เพื่อให้การอ้างอิงยังคงชี้ไปยังเซลล์ที่ถูกต้อง ครึ่งหนึ่งของเรื่องนั้นเป็นเรื่องจริง และครอบคลุมอยู่ในบทความคู่กันเรื่องวิธีที่ HotXLS เขียนการอ้างอิงสูตรใหม่เมื่อแถวและคอลัมน์เคลื่อนที่ แต่ conditional format หรือ data validation rule ไม่ใช่แค่สูตรที่นั่งอยู่ในเซลล์เดียว มันจับคู่สูตรเข้ากับช่วง sqref ในศัพท์ของ ECMA-376 และทั้งสองต้องเคลื่อนที่ไปด้วยกัน เมื่อการแก้ไขเชิงโครงสร้างตัดช่วงนั้นออกเป็นสองส่วนที่ต้องการ offset สัมพัทธ์ต่างกันสองแบบเพื่อให้ถูกต้อง การเก็บ rule object เดียวไว้ด้วยสูตรเดียวก็ไม่ใช่ตัวเลือกอีกต่อไป และการแสร้งทำเป็นอย่างอื่นคือวิธีที่กฎไฮไลต์เริ่มเปรียบเทียบแถวผิดโดยไม่รู้ตัว
ทำไมการแทรกแถวถึงแบ่ง conditional formatting rule แทนที่จะแค่ย้ายมัน
conditional format หรือ data validation rule เก็บสูตรเดียวเป๊ะสำหรับทั้งช่วงของมัน ประเมินแบบสัมพัทธ์กับเซลล์ anchor เดียว ดังนั้นเมื่อการแก้ไขบังคับให้สองส่วนของช่วงนั้นต้องการ offset สัมพัทธ์สองแบบที่ต่างกัน สูตรเดียวก็ไม่สามารถอธิบายทั้งสองส่วนได้อย่างถูกต้องอีกต่อไป ECMA-376 แสดงขอบเขตของ rule เป็น attribute sqref บน element conditionalFormatting หรือ dataValidation และ Excel ประเมิน Formula1 กับ Formula2 เสมือนว่าข้อความนั้นถูกพิมพ์เข้าไปในเซลล์บนซ้ายของ sqref นั้นและเติมข้ามที่เหลือของมัน แบบเดียวกับที่สูตรสัมพัทธ์ธรรมดาเติมลงมาตามคอลัมน์ ลองนึกภาพ rule ไฮไลต์ความแปรปรวนบน B2:B50 ที่ flag ตัวเลขจริงใดๆ ที่เกินงบประมาณของมัน สร้างเป็น rule แบบ cellIs ที่ Formula1 ของมันเป็นข้อความตรงตัว C2 หมายถึงเปรียบเทียบเซลล์ B ของแถวปัจจุบันกับเซลล์ C ของแถวเดียวกันนั้น
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25แทรกแถวคั่นหนึ่งแถวนั้นที่แถวเดิม 25 แล้วแถวเหนือจุดแทรกจะไม่ขยับ ดังนั้นส่วนของ rule ที่เป็นของแถวเหล่านั้นยังคงอ่าน Formula1 เป็น C2 ได้ถูกต้อง แถวที่เคยเป็น 25 ถึง 50 เลื่อนลงไปเป็น 26 ถึง 51 และสำหรับแถวเหล่านั้น C2 ตอนนี้กลายเป็นเซลล์ที่ผิดโดยสิ้นเชิง เพราะแถว 26 ต้องเปรียบเทียบกับ C26 ไม่ใช่กับตัวเลขงบประมาณที่อยู่เหนือขึ้นไปยี่สิบกว่าแถว
HotXLS ตัดสินใจว่า rule ต้องแบ่งหรือไม่อย่างไร
HotXLS จะสร้าง rule object เพิ่มเติมก็ต่อเมื่อ geometry ต้องการมันจริงๆ เท่านั้น รูทีนภายในตัวหนึ่งชื่อ XlsxBuildShiftedRuleParts เดินผ่านทุกพื้นที่ที่แยกจากกันใน sqref ของ rule คำนวณว่าเซลล์ anchor ของพื้นที่นั้นเป็นอะไรก่อนการแก้ไขและกลายเป็นอะไรหลังการแก้ไข แล้วตรวจสอบว่าทุกชิ้นที่ได้จะต้องการการแก้ไข relative-offset แบบเดียวกันหรือไม่ ถ้าทุกชิ้นตรงกัน rule เดียวก็จะอยู่รอด โดย sqref ของมันถูกสร้างใหม่เป็นการรวมของชิ้นที่เลื่อนแล้ว และสูตรของมันถูก rebase ครั้งเดียว การแบ่งจริงๆ จะเกิดขึ้นก็ต่อเมื่อชิ้นต่างๆ ไม่ตรงกันเท่านั้น ตรงกับกรณี B2:B50 ข้างต้นเป๊ะ ที่บล็อกบนคง anchor เดิมไว้ และบล็อกล่างต้องการ anchor ใหม่
การ rebase สูตรของชิ้นหนึ่งคือการเคลื่อนไหวสองขั้นตอนที่นำกลไกที่ HotXLS มีอยู่แล้วสำหรับกลุ่มสูตรที่ใช้ร่วมกันของ OOXML (shared formula) มาใช้ซ้ำ ขั้นแรกสูตรถูกแปลเสมือนว่ามันถูก anchor ไว้ที่เซลล์บนซ้ายของชิ้นนั้นเองตั้งแต่แรก โดยใช้คณิตศาสตร์ relative-offset แบบเดียวกับที่ขยาย shared formula ข้ามช่วงของมัน จากนั้นผลลัพธ์วิ่งผ่านตัวสแกนการเลื่อนแถวและคอลัมน์ตัวเดียวกันที่เขียนสูตรเวิร์กชีตธรรมดาใหม่ นั่นคือวิธีที่ Formula1 เปลี่ยนจาก C2 เป็น C26 ด้วยสองขั้นตอนแทนที่จะเป็นกรณีพิเศษที่เขียนด้วยมือหนึ่งครั้ง แปล C2 ไปข้างหน้า 23 แถวเพื่อให้ได้ C25 เสมือนว่า rule เริ่มต้นที่นั่นมาตลอด แล้วปล่อยให้การเลื่อนธรรมดาที่แถว 25 ผลักมันต่อไปเป็น C26 property อื่นทุกอย่าง สีเติม, stop-if-true, ตัว operator เอง ล้วนติดไปกับ rule object ใหม่โดยไม่เปลี่ยนแปลง ดังนั้นทั้งสองครึ่งยังคงเติมสีเซลล์ตามที่มันเคยทำมาตลอด
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)data bar และ icon set แบ่งแบบเดียวกับ cellIs rule หรือไม่
ไม่ HotXLS แบ่งเฉพาะชนิด rule ที่ความถูกต้องของมันขึ้นอยู่กับสูตรสัมพัทธ์ต่อพื้นที่จริงๆ คือการเปรียบเทียบแบบ cellIs และ expression rule เท่านั้น และปล่อยให้ conditional-format ชนิดอื่นทุกชนิดเป็น rule object เดียวที่ sqref ของมันแค่ขยายให้ครอบคลุมชิ้นที่เลื่อนแล้วเป็นการรวมแบบหลายพื้นที่ ภายใน branch นี้เป็นแค่การตรวจสอบ Kind ธรรมดา cf.Kind in [cfkCellIs, cfkExpression] ไม่มีอะไรซับซ้อนไปกว่านั้น data bar, scale สองและสามสี, icon set, การจัดอันดับบนและล่าง และตัวตรวจจับค่าซ้ำ ค่าว่าง และข้อผิดพลาด ล้วนพก payload ที่บรรยายทั้งช่วงที่ครอบคลุมพร้อมกันทีเดียว เช่น สี bar, ชุดจุดหยุดของ scale, ตระกูล icon แทนที่จะเป็นการเปรียบเทียบแบบสัมพัทธ์ต่อเซลล์ ดังนั้นการแบ่งพวกมันออกเป็น rule object ที่มีลำดับความสำคัญหลายชิ้นจะไม่ได้อะไรเพิ่มด้านความถูกต้องเลย และจะแค่เพิ่ม rule ให้ต้องจัดการมากขึ้น เมื่อการแก้ไขแบ่งช่วงของพวกมัน HotXLS จะรวมชิ้นเหล่านั้นกลับเข้าเป็น rule เดียวที่มี sqref แบบหลายพื้นที่ และ re-anchor payload เป็นหน่วยเดียว แทนที่จะ clone rule object ใหม่ต่อชิ้น ความแตกต่างนี้สอดคล้องกับ taxonomy ของชนิด rule ในบทความพื้นฐานเรื่อง conditional formatting และ rich text data bar, color scale และ icon set แยกตัวออกจาก cellIs rule อยู่แล้วด้วยการเพิกเฉยต่อ property Style โดยสิ้นเชิง และตอนนี้ก็ปรากฏว่าพวกมันแยกตัวออกจากการ re-anchor ต่อพื้นที่ด้วยเหตุผลพื้นฐานเดียวกัน
ทำไมลำดับความสำคัญของ rule ถึงเปลี่ยนหลังการแก้ไขเชิงโครงสร้าง
ลำดับความสำคัญเปลี่ยนเพราะทุกสำเนาเริ่มต้นด้วยค่าลำดับความสำคัญเดียวกันเป๊ะกับ rule ที่มันแบ่งมาจาก และ HotXLS รันรอบ normalization หลังจากนั้นที่แก้ค่าซ้ำกันเหล่านั้นให้เป็นลำดับที่สะอาดและไม่มีช่องว่าง แทนที่จะปล่อยให้ rule สองตัวเสมอกันในลำดับเดียวกัน รูทีนภายในตัวที่สองชื่อ XlsxNormalizeConditionalFormatPriorities รับลำดับความสำคัญปัจจุบันของทุก conditional format, fallback ไปใช้ตำแหน่งของ rule นั้นใน collection สำหรับ rule ใดๆ ที่ไม่เคยถูกตั้งค่าไว้อย่างชัดเจน, เรียงลำดับทั้งรายการแบบ stable เพื่อให้ค่าที่เสมอกันคงลำดับสัมพัทธ์เดิมไว้ แล้วกำหนดหมายเลขผลลัพธ์ที่เรียงแล้วใหม่เป็นลำดับแน่น 1, 2, 3 ที่ไม่มีช่องว่างและไม่มีการซ้ำ HotXLS รันมันครั้งหนึ่งก่อนที่การเลื่อนจะเริ่ม ดังนั้นการ clone จึงเริ่มจาก baseline ที่สะอาด และรันอีกครั้งหลังทุกการแบ่งและหลังทุก rule ที่ว่างเปล่าถูกลบออก ดังนั้นไฟล์ที่ถูกบันทึกจะไม่มี rule entry สองตัวที่อ้างลำดับความสำคัญเดียวกันเลย เรื่องนี้สำคัญถ้าคุณทำตามคำแนะนำในบทความพื้นฐานเรื่อง conditional formatting ที่ให้เว้นช่องว่างระหว่างค่าลำดับความสำคัญ เพื่อให้ rule ในภายหลังแทรกเข้าไปได้โดยไม่ต้องกำหนดหมายเลขที่เหลือใหม่ ช่องว่างเหล่านั้นจะอยู่รอดจนกว่าการแก้ไขแถวหรือคอลัมน์ครั้งถัดไปจะแตะเวิร์กชีตนั้น แล้วก็จะยุบลง เพราะ normalization รับประกันแค่ความไม่ซ้ำและลำดับที่เสถียรเท่านั้น ไม่ใช่ว่ารูปแบบการกำหนดหมายเลขเดิมของคุณจะกลับมาโดยไม่เปลี่ยนแปลง
Data validation rule ก็แบ่งเช่นกัน โดยไม่มีลำดับความสำคัญให้กำหนดใหม่
Data validation rule ผ่าน logic การแบ่งช่วงเดียวกันกับ cellIs และ expression conditional format และต่างจาก conditional formatting ตรงที่ทุกชนิด validation ใช้เส้นทางนั้นอย่างสม่ำเสมอ HotXLS ไม่มีตระกูลแบบไม่ใช่สูตรแยกต่างหากสำหรับ data validation แบบที่ data bar และ icon set มีสำหรับ conditional formatting ดังนั้น rule แบบรายการหรือจำนวนเต็มธรรมดาจึงถูกแบ่งด้วยรูทีนตัวเดียวกันเป๊ะกับที่จัดการสูตรแบบกำหนดเองที่สัมพัทธ์ สิ่งที่ต่างคือลำดับความสำคัญ ECMA-376 ไม่ได้ให้ attribute priority ใดๆ เลยกับ element dataValidation ดังนั้นจึงไม่มีขั้นตอนกำหนดหมายเลขใหม่สำหรับ validation แบบที่มีสำหรับ conditional format ลองนึกภาพ validation แบบสูตรกำหนดเองที่กันไม่ให้จำนวนเงินจริงของแต่ละแถวเกินงบประมาณของตัวเองในคอลัมน์ข้างๆ
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)เรื่องนี้สำคัญด้วยเหตุผลเดียวกับที่บทความพื้นฐานเรื่อง data validationเตือนไม่ให้ผูก rule ก่อนที่จำนวนแถวจะสรุปแน่นอน validation ครอบคลุมแค่เซลล์ตัวอักษรที่คุณให้ไว้เท่านั้น และการแก้ไขเชิงโครงสร้างในภายหลังอาจทิ้ง rule สองตัวหรือมากกว่าไว้ทำงานที่ตัวเดียวเคยทำ ไม่มีอะไรพังในเชิงฟังก์ชัน ทุกเซลล์ในช่วงเดิมยังคงถูก validate โดยอะไรสักอย่าง แต่โค้ดที่สมมติว่ามี DataValidations entry เดียวต่อคอลัมน์จะเริ่ม index ผิดหลังการแก้ไขครั้งแรกแตะมัน มีเพดานแข็งว่าสิ่งนี้จะไปไกลได้แค่ไหน ถ้าการแบ่งจะผลักเวิร์กชีตให้เกิน 65,534 data-validation rule HotXLS จะยก exception แทนที่จะเขียนไฟล์ที่ Excel จะปฏิเสธอย่างเงียบๆ ซึ่งเป็นการที่ไลบรารีปฏิเสธที่จะผลิต workbook ที่เสียหาย มากกว่าจะเป็นขีดจำกัดที่การใช้งานทั่วไปมีแนวโน้มจะไปถึง
สิ่งที่ควรตรวจสอบหลังการแทรกหรือลบแบบ bulk
สองสิ่งที่ควรตรวจสอบหลังจากสคริปต์รันชุดการแก้ไขแถวหรือคอลัมน์บนชีตที่เต็มไปด้วย conditional format และ validation คือจำนวน rule รวมและลำดับความสำคัญ เพราะทั้งคู่สามารถเลื่อนไหลไปในแบบที่มองข้ามได้ง่ายใน code review แต่ชัดเจนทันทีที่ใครเปิด Manage Rules ใน Excel การแก้ไขครั้งเดียวไม่ค่อยสร้างความเสียหายมากนัก การแทรกครั้งเดียวกลางหนึ่ง cellIs rule สร้าง rule object ได้มากที่สุดสองตัวจากที่เคยมีหนึ่งตัว ความเสี่ยงจะทบต้นเมื่อรูทีนสร้างรายงานแทรกแถวทีละแถวใน loop บนชีตที่มี rule ที่ยึด anchor ด้วยสูตรอยู่แล้วหลายตัว แต่ละรอบสามารถแบ่ง rule ที่รอบก่อนหน้าแบ่งไปแล้วซ้ำอีก และ cellIs rule ต้นฉบับห้าตัวสามารถลงเอยเป็นเศษชิ้นเล็กชิ้นน้อยมูลค่าต่ำจำนวนหลายเท่าที่ครอบคลุมเสี้ยวเล็กๆ ของช่วงเดิม การจัดกลุ่มการแก้ไขเชิงโครงสร้างเข้าด้วยกัน แทรกบล็อกใหม่ทั้งหมดในการเรียกครั้งเดียวแทนที่จะทำทีละแถว จะรักษาจำนวน rule ให้ผูกกับจำนวน anchor ที่ต่างกันจริงๆ แทนที่จะผูกกับจำนวนการแก้ไขที่ทำ
การแบ่ง rule และการ normalize ลำดับความสำคัญมาเป็นพฤติกรรมมาตรฐานของ XLSX engine ในHotXLS Delphi Excel Componentสำหรับ Delphi และ C++Builder หน้าผลิตภัณฑ์มีเอกสารอ้างอิง API การแก้ไขเวิร์กชีตแบบเต็ม รวมถึง method ของ conditional formatting และ data validation ที่อธิบายไว้ในบทความนี้