การตรวจสอบความถูกต้องของข้อมูล ตัวกรองอัตโนมัติ และตารางแผ่นงานใน Delphi ด้วย HotXLS

สามฟีเจอร์ใน HotXLS ใช้ worksheet ร่วมกัน แต่ทำงานกับ object ที่แตกต่างกันโดยสิ้นเชิง และปัญหาจะเริ่มขึ้นเมื่อคุณสันนิษฐานว่ามันทำสิ่งที่คล้ายกัน Data validation ผูกกฎเข้ากับ range ที่จำกัดว่าผู้ใช้พิมพ์อะไรลงไปได้บ้าง AutoFilter ผูกนิยาม criteria ที่เก็บไว้เข้ากับพื้นที่หนึ่ง แล้วเปลี่ยนว่าแถวไหนที่ผู้ดูจะเห็น table ห่อ range ไว้ในโครงสร้างที่มีชื่อ มีชนิดข้อมูล พร้อม styling แบบสลับแถบสี อย่างหนึ่งจำกัด input อย่างหนึ่งบันทึกมุมมอง อีกอย่างกำหนด schema ไม่มีอันไหนขยับค่าเซลล์แม้แต่ตัวเดียวด้วยตัวมันเอง และ AutoFilter โดยเฉพาะหลอกคนได้ง่าย เพราะคำนี้บอกเป็นนัยถึงการกระทำ ทั้งที่มันเก็บแค่นิยามเท่านั้น การรู้ว่าแต่ละ call แตะ object ตัวไหน และผลจริง ๆ ปรากฏขึ้นเมื่อไหร่ คือสิ่งที่แยก workbook ที่ทำงานเหมือนกันทั้งใน Excel และในการทดสอบของคุณ ออกจาก workbook ที่ค่อย ๆ เบี่ยงเบนไปอย่างเงียบ ๆ

AutoFilter เก็บนิยาม ไม่ได้ตัดแถวออก

AutoFilter ในไฟล์ที่ save แล้วคือ record ของ criteria การซ่อนแถวเกิดขึ้นทีหลัง ตอนที่ Excel เปิด workbook แล้วประเมิน criteria กับข้อมูล HotXLS เขียน record นั้นอย่างซื่อสัตย์และไม่ตัดอะไรทิ้งเลย: ทุกแถวที่คุณกรองไว้ยังคงอยู่จริงในไฟล์ pipeline ที่ใช้ filter เพื่อตัด order ที่ถูกปฏิเสธออก แล้วอ่าน workbook กลับเข้ามาจะเห็นทุกแถว รวมทั้งแถวที่ถูกปฏิเสธด้วย และโค้ดนั้นก็ถูกต้องตาม API แต่ผิดตามโมเดลความคิดของผู้เขียน บน worksheet ของ XLSX SetAutoFilter ประกาศพื้นที่ที่ถูกกรอง และ AddAutoFilterColumn ผูก criteria เข้ากับหนึ่งคอลัมน์ในนั้น เมื่อโค้ดฝั่งเซิร์ฟเวอร์ต้องการผลลัพธ์จริง ๆ สำหรับจำนวนแถวในสรุป หรือเพื่อส่งต่อเฉพาะแถวที่ตรงเกณฑ์ ไลบรารีจะประเมิน criteria ให้คุณ แทนที่จะแสร้งทำเป็นว่าไฟล์เปลี่ยนไปแล้ว:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Column id 3 = คอลัมน์ที่สี่ ภายในช่วงของ filter (offset เริ่มนับที่ 0)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // ตอนนี้ Visible ตรงกับสิ่งที่ Excel จะแสดงหลังเปิดไฟล์

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible ตอบเป็นรายแถว และ PreviewAutoFilterRows วนดูทั้งพื้นที่ผ่าน callback เมื่อคุณต้องการชุดที่ตรงเกณฑ์ในรอบเดียว มีอยู่กรณีหนึ่งที่ไม่มีตัวไหนถูกต้อง: ถ้าข้อกำหนดคือแถวที่ถูกตัดออกต้องไม่มีอยู่ในไฟล์เลย นั่นคือการตัดเพื่อความเป็นส่วนตัว ไม่ใช่แค่มุมมอง ก็ให้ลบแถวออกไปตรง ๆ filter คือเครื่องมือที่ผิดตรงนั้น เพราะผู้รับคนไหนก็เคลียร์มันได้ด้วยคลิกเดียว แล้วข้อมูลที่คุณตั้งใจจะกักไว้ก็กลับมาปรากฏบนหน้าจอ

column id คือ offset ไม่ใช่หมายเลขคอลัมน์

comment ในตัวอย่างข้างบนชี้ให้เห็นกับดักที่กินเวลา debug มากที่สุดใน API นี้ AddAutoFilterColumn ระบุเป้าหมายด้วยตำแหน่งภายในช่วงของ filter ที่เริ่มนับจาก 0 ไม่ใช่คอลัมน์ของ worksheet สำหรับ filter บน A1:E500 ระบบนับเลขทั้งสองแบบบังเอิญต่างกันแค่หนึ่ง ซึ่งเป็นความคลาดเคลื่อนแบบเฉียดฉิวที่รอดจากการทดสอบเร็ว ๆ แล้วพังทันทีที่เพื่อนร่วมงานกรองคอลัมน์อื่น สำหรับ filter ที่เริ่มที่คอลัมน์ C, id เท่ากับ 0 หมายถึงคอลัมน์ C และความคลาดเคลื่อนนั้นก็ชัดเจนขึ้นเร็ว เมื่อช่วงของ filter ถูกคำนวณตอน runtime ให้ดึง column id มาจากตัวแปรเดียวกันกับที่สร้างสตริงช่วง อย่าดึงมาจากค่าคงที่คอลัมน์ของ worksheet เด็ดขาด แต่ละคอลัมน์รับเงื่อนไขที่สองได้ผ่าน overload ที่รับ operator สองตัว criteria สองตัว และตัวเชื่อม and/or ซึ่งสะท้อน dialog custom filter ของ Excel ส่วนหน้าของ XLS ครอบคลุมพื้นที่เดียวกันด้วย SetAutoFilter บวก ApplyAutoFilter ซึ่งพารามิเตอร์ criteria และ operator ของมันตามธรรมเนียมสไตล์ COM แบบเก่า และนับเลขฟิลด์เริ่มจาก 1 การสลับส่วนหน้าหมายถึงการสลับฐานของ index ด้วย ดังนั้นจุดที่เรียกใช้จึงควรมี comment บอกไว้ว่ากำลังใช้แบบไหนอยู่

กฎการตรวจสอบคือ contract ที่ผู้ใช้ของคุณแก้ไขอยู่ภายใต้มัน

ในสามฟีเจอร์นี้ validation คือฟีเจอร์เดียวที่จำกัด input ในอนาคตอย่างจริงจัง และมันสมควรได้รับความใส่ใจด้าน design มากที่สุดใน workbook ที่ถูกส่งออกไปให้กรอกแล้วส่งกลับมาประมวลผล ตัวแปร list พกงานส่วนใหญ่นั้นไว้:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // จำนวน: เลขจำนวนเต็ม ตั้งแต่ศูนย์ขึ้นไป
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

นอกเหนือจาก list และเลขจำนวนเต็ม ตระกูลเดียวกันนี้ยังครอบคลุมทศนิยม วันที่ เวลา ความยาวข้อความ และสูตรแบบอิสระผ่าน AddCustomValidation และ AddDataValidation แบบทั่วไปก็เปิดเมทริกซ์ชนิด-และ-operator เต็มรูปแบบให้ตัวสร้างกฎที่ขับเคลื่อนด้วย configuration ใช้ สไตล์ error สำคัญกว่าที่ชื่อของมันบ่งบอก xlsxDvErrStop ปฏิเสธ input ที่ผิดโดยตรง ส่วนสไตล์ warning และ information ปล่อยให้ค่าผ่านไปได้หลังคลิกครั้งเดียว เลือกทีละคอลัมน์ตามว่าโค้ดที่อ่าน workbook กลับมาทนต่อค่านอกกฎได้หรือไม่ มีขอบเขตสองอย่างที่ควรอยู่ในข้อความ prompt หรือ README ที่คุณส่งไปพร้อมไฟล์ validation ใน Excel คุ้มครองการพิมพ์ แต่การวางบล็อกทับ range ที่ validate ไว้จะหลุดผ่านกฎไปได้ ดังนั้นโค้ดใดก็ตามที่อ่านข้อมูลกลับมาต้อง validate ซ้ำอีกครั้ง ไม่ใช่เชื่อเซลล์เฉย ๆ และกฎหนึ่งครอบคลุมแค่ range ตามตัวอักษรที่คุณส่งให้มันเท่านั้น ซึ่งหมายความว่าการผูก validation ก่อนที่คุณจะรู้จำนวนแถวสุดท้าย จะทิ้งส่วนหางที่ถูกต่อเข้ามาทีหลังไว้โดยไม่มีการป้องกัน เขียนข้อมูลก่อน แล้วค่อยปรับขนาดกฎให้ตรงกับขอบเขตจริง

ส่วนหน้าแบบเก่าให้ตระกูลกฎเดียวกัน โดยมีความต่างด้าน ergonomic อยู่หนึ่งจุด ตัวสร้างฝั่ง XLS คือ AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation และ AddCustomValidation จะคืนค่า object TDataValidation ออกมาตรง ๆ แทนที่จะเป็น index ดังนั้นการตั้งค่า prompt และ error จึงต่อเนื่องจาก reference ที่คืนมา ไม่ใช่จากการค้นหา operator enumeration (xlsDvBetween, xlsDvGreaterThan และตัวอื่น ๆ) สะท้อนชุดของ XLSX ดังนั้นโค้ดสร้างกฎจึงย้ายไปมาระหว่างส่วนหน้าได้ ยกเว้นความต่างเรื่องรูปแบบค่าที่คืนกลับ ตัวข้อความ prompt เองก็สมควรได้รับความคิดพอ ๆ กับตัวกฎ dropdown ที่ปฏิเสธ input ด้วยกล่อง error ว่างเปล่าจะสอนให้ผู้ใช้ส่งอีเมลไปหา IT ส่วนตัวที่ระบุสถานะที่ถูกต้องไว้จะสอนให้พวกเขาแก้เซลล์แล้วไปต่อ

การกลับขั้วหนึ่งอย่างที่ไลบรารีดูดซับไว้ให้คุณ

ใครก็ตามที่เคยอ่าน XML ของ validation ใน OOXML ด้วยมือคงเคยเจอ attribute showDropDown ที่กลับขั้ว: ใน ISO/IEC 29500 ค่า true หมายถึง "ซ่อนลูกศร dropdown" ซึ่งตรงข้ามกับสิ่งที่ชื่อของมันดูเหมือนจะบอก HotXLS พลิกมันกลับภายในให้ ดังนั้น property ShowDropDown บนกฎ validation จึงหมายความตามที่มันบอกจริง ๆ โดย true คือแสดง dropdown วิธีเดียวที่จะโดนเผาคือการปนกันระหว่างสองระดับความจริง คือตั้งค่า property จากโค้ด ในขณะที่เพื่อนร่วมงานตรวจ XML ที่ save ไว้แล้ว "แก้ไข" attribute ที่ดูกลับด้านสำหรับพวกเขา ตัดสินใจว่า property หรือ XML ดิบคือแหล่งความจริงสำหรับเครื่องมือตรวจสอบ แล้วเขียนเรื่องการกลับขั้วนี้ไว้ตรงจุดที่การตัดสินใจนั้นอาศัยอยู่

Table ให้ range หนึ่งมีทั้ง schema และชื่อ

Table ของ worksheet หรือ ListObject ในศัพท์ของ Excel ห่อ range ไว้ในชื่อ คอลัมน์ที่มีชนิดข้อมูล styling แบบสลับแถบสี และรองรับ structured reference มันคือฟีเจอร์ที่ทำให้ workbook ที่สร้างขึ้นรู้สึกเสร็จสมบูรณ์ทันทีที่ผู้ใช้เริ่ม sort และขยายมัน การสร้างสมมาตรกันข้ามส่วนหน้า โดย AddTable รับชื่อ range และรายการคอลัมน์:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

ฝั่ง XLSX object table ที่ได้จะเปิด StyleName (ตระกูล TableStyleMedium2 ในตัวและพี่น้องของมัน) ตัวสลับลาย stripe และ flag แถวรวม ดังนั้นการใส่สไตล์ตามแบบองค์กรก็แค่กำหนดค่า property เดียว แทนที่จะทำ formatting ด้วยมือ ในไฟล์ .xls แบบเก่า call เดียวกันเขียน record ตาราง BIFF8 และส่วนหน้ายังมี AddPivotTable ให้ใช้สำหรับมุมมองสรุปที่สร้างจากฟิลด์แถว คอลัมน์ และข้อมูล เป็นเครื่องเตือนว่า "table" ในฟอร์แมตเก่านั้นเอื้อมไปได้ไกลกว่า ListObject ของ OOXML ตั้งชื่อ table เหมือนที่คุณตั้งชื่อ view ของฐานข้อมูล โค้ดปลายทางที่อ่าน Orders[Amount] ผ่าน structured reference จะรอดจากการสลับลำดับคอลัมน์ที่ทำให้โค้ดแบบอิงตำแหน่งพัง

ธรรมเนียมสองอย่างช่วยประหยัดงาน cleanup ในภายหลัง Excel กำหนดให้ชื่อ table ต้องไม่ซ้ำกันทั่วทั้ง workbook ดังนั้น generator ที่ปล่อยหนึ่ง sheet ต่อหนึ่งพื้นที่จึงต้องใช้รูปแบบอย่าง Orders_EMEA แทนที่จะใช้ Orders ซ้ำ ชื่อซ้ำจะไม่ fail ตอนเขียน มันจะโผล่ขึ้นมาเป็น dialog ซ่อมแซมตอนที่ผู้ใช้เปิดไฟล์ ซึ่งเป็นที่ที่แย่ที่สุดในการค้นพบมัน ธรรมเนียมอีกอย่างเกี่ยวกับแถวรวม: เมื่อเปิดใช้งาน มันจะอยู่ตรงใต้ range ข้อมูลทันที ดังนั้นโค้ดใดก็ตามที่ต่อท้ายภายหลังด้วย "แถวที่ใช้ล่าสุดบวกหนึ่ง" จะเขียนลงในแถบแถวรวมแทนที่จะเขียนต่อจากมัน ติดตามขอบเขตข้อมูลแยกจากขอบเขตของ table แล้วการต่อท้ายก็จะลงตรงตำแหน่งที่คุณคาดไว้

สามฟีเจอร์นี้ประกอบกันได้เป็นธรรมชาติใน deliverable แบบกรอกข้อมูล table กำหนดพื้นที่ที่แก้ไขได้ validation จำกัดคอลัมน์ที่ผู้ใช้พิมพ์ลงไป และ filter ที่ตั้งไว้ล่วงหน้าช่วยผู้รับให้ไม่ต้องคลิกไม่กี่ครั้งแรก มีเหตุผลที่สมเหตุสมผลสำหรับการส่งมอบ workbook ที่มี filter ใช้งานอยู่แล้ว เพื่อให้มันเปิดขึ้นมาโดยโฟกัสที่แถวที่สำคัญ ตราบใดที่คุณจำได้ว่าแถวที่ถูกตัดออกยังอยู่ในไฟล์ และผู้รับที่อยากรู้อยากเห็นก็เปิดเผยมันได้ การนำผลลัพธ์ query เข้าสู่ sheet อย่างมีประสิทธิภาพ ซึ่งเป็นครึ่งต้นของ pipeline นี้ อธิบายไว้ใน การส่งออกผลลัพธ์จากฐานข้อมูลไปยัง Excel จาก Delphi และ workbook ที่สูตรสรุปข้อมูลที่ validate แล้วก็ได้ประโยชน์จาก defined name สำหรับ reference ข้าม sheet ที่มั่นคง

Validation, filter และ table คือความต่างระหว่างการส่งมอบ grid ของค่าธรรมดา กับการส่งมอบแอปพลิเคชันขนาดเล็ก เอกสารอ้างอิงเต็มรูปแบบของกฎ filter และ table อยู่ที่หน้าผลิตภัณฑ์ HotXLS Delphi Component