ทำสำเนาเวิร์กชีต XLSX ใน Delphi ด้วย HotXLS

คุณสร้างชีตหนึ่งแผ่นได้ลงตัวทุกอย่างแล้ว แถบส่วนหัวถูกรวมเซลล์ ความกว้างคอลัมน์พอดีกับข้อมูล สองแถวบนสุดถูกตรึง พื้นที่พิมพ์และระยะขอบถูกตั้งไว้ให้ส่งออก A4 ได้เรียบร้อย และแท็บถูกใส่สีให้ฝ่ายการเงินหาเจอ ตอนนี้รายงานต้องการชีตแบบนี้สิบสองชุด ชุดละหนึ่งภูมิภาค โดยแต่ละชุดเริ่มจากเลย์เอาต์เดียวกัน การเขียนโค้ดสร้างชีตนั้นซ้ำสิบสองครั้งคือทางลัดไปสู่การคลาดเคลื่อนเล็กๆ น้อยๆ ภูมิภาคที่ 7 ได้คอลัมน์แคบลงไปหนึ่งจุด ภูมิภาคที่ 11 หลุดการตรึงแถว และไม่มีใครสังเกตจนกระทั่ง PDF ไปถึงโต๊ะผู้จัดการ สิ่งที่คุณต้องการจริงๆ คือรูปแบบโปรแกรมของคำสั่ง Excel คลิกขวา Move or Copy, Create a copy เอาชีตที่เสร็จแล้วมาสร้างสำเนาแยกกันให้ครบ

เอนจิน XLSX ใน HotXLS ซึ่งเป็นไลบรารี Delphi และ C++Builder แบบเนทีฟสำหรับอ่านและเขียนไฟล์ Excel โดยไม่ต้องควบคุม Excel เอง ทำได้อยู่แล้วทั้งการย้ายชีต ลบชีต และคัดลอกช่วงเซลล์ข้ามชีต สิ่งที่ยังทำไม่ได้จนถึง v2.91.0 คือการโคลนเวิร์กชีตทั้งแผ่นในคำสั่งเดียว รีลีสนี้เพิ่มจุดเรียกเข้าใหม่สองตัวคือ TXLSXWorksheet.CopyFrom ซึ่งคัดลอกสถานะระดับชีตจากเวิร์กชีตหนึ่งไปอีกเวิร์กชีตหนึ่ง และ TXLSXSheets.Duplicate ซึ่งเพิ่มชีตใหม่แล้วเรียก CopyFrom ให้คุณ สิ่งที่น่าสนใจไม่ใช่แค่มันคัดลอกอะไร แต่คือเส้นแบ่งที่ตั้งใจว่าสิ่งใดต้องคัดลอกแบบลึก และสิ่งใดไม่ต้อง รวมถึงเหตุผลที่เส้นนั้นอยู่ตรงนั้น

เรียกครั้งเดียวเพื่อโคลนชีตที่สร้างเสร็จแล้ว

การทำงานระดับสูงคือ Duplicate ส่งดัชนีของชีตต้นทางแบบเริ่มที่ 1 ให้มัน แล้วจะได้เวิร์กชีตใหม่เอี่ยมที่สะท้อนเลย์เอาต์และข้อมูลเดิมทั้งหมด การนับดัชนีสอดคล้องกับ Items[] ฝั่ง XLSX ดังนั้นชีตแรกมีดัชนี 1 ไม่ใช่ 0 ถ้าส่งดัชนีเกินช่วงจะได้ nil แทนข้อยกเว้น ซึ่งเป็นสัญญาการล้มเหลวแบบเดียวกับที่คอลเลกชันชีต XLSX ส่วนอื่นใช้

var
  Book: TXLSXWorkbook;
  Template, Copy: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Template := Book.Sheets.Add('Template');
    Template.Cells[1, 1].Value := 'Quarterly Statement';
    Template.Range['A1:C1'].Merge;
    Template.ColWidth[1] := 18;
    Template.FreezePanes(2, 1);          // freeze top row + first column
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clone with an explicit name...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...or let it pick the Excel-style default name.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

    Book.SaveAs('regions.xlsx');
  finally
    Book.Free;
  end;
end;

มีสองจุดในตัวอย่างนั้นที่ควรดูให้ชัด ประการแรก FreezePanes รับอาร์กิวเมนต์แบบแถวก่อน FreezePanes(ARow, ACol) จึงสอดคล้องกับการอ้างอิง Cells[Row, Col] สำเนาจะรับการแบ่ง freeze split แบบเดียวกันอย่างตรงตัว ประการที่สอง ชื่อเมธอดคือ Duplicate ไม่ใช่ Copy ที่ดูตรงไปตรงมากว่า ซึ่งไม่ได้เป็นแค่เรื่องสไตล์ Copy เป็นรูทีนมาตรฐานใน unit System ที่ใช้บ่อยกับสตริงและ dynamic array เมธอดชื่อ Copy บนคลาสจะไปบังชื่อรูทีนนั้นภายในบอดีของเมธอดและสร้างความกำกวมในการ resolve ที่จะตามมาทำให้ปวดหัวอีกหกเดือนข้างหน้า Duplicate เลี่ยงปัญหานั้นได้หมดและอ่านเข้าใจตรง call site

ชื่อเริ่มต้นทำตามกฎของ Excel เอง

เมื่อคุณเรียกโอเวอร์โหลดแบบอาร์กิวเมนต์เดียว หรือส่งสตริงชื่อว่าง ชีตใหม่จะถูกตั้งชื่อตามต้นฉบับพร้อมต่อท้าย (2) และเลขต่อท้ายจะขยับขึ้นเรื่อยๆ จนกว่าจะไม่ซ้ำ Duplicate ชีต Template หนึ่งครั้งจะได้ Template (2); ทำซ้ำอีกครั้งจะได้ Template (3) เพราะ Template (2) ถูกใช้ไปแล้ว นี่สะท้อนชื่อที่ Excel สร้างจากคำสั่ง Create a copy ของมันเอง ทำให้เวิร์กบุ๊กที่โค้ดคุณสร้างดูเหมือนเวิร์กบุ๊กที่ผู้ใช้คัดลอกด้วยมือ การตรวจความไม่ซ้ำจะเช็กกับชุดชีตที่มีอยู่จริง จึงข้ามชื่อที่คุณตั้งเองด้วย ไม่ใช่แค่ชื่อที่ได้จากการ duplicate ก่อนหน้า

ถ้าคุณกำลังสร้างหนึ่งชีตต่อหนึ่งภูมิภาคหรือหนึ่งเดือน ให้ใช้งานโอเวอร์โหลดที่ระบุชื่อเองจะดีกว่า แผนผังชื่อแบบ Region-North, Region-South ที่คาดเดาได้ง่ายกว่าจะอ้างอิงภายหลังสะดวกกว่าชุด suffix (2), (3) และยังทำให้ defined names กับสูตรข้ามชีตอ่านง่าย

สิ่งที่ `CopyFrom` คัดลอกแบบลึก

ภายใน Duplicate จะเพิ่มชีตก่อนแล้วเรียก CopyFrom(ASource) ซึ่งคุณสามารถเรียกตรงๆ ได้เมื่ออยากโคลนลงบนชีตที่สร้างไว้แล้ว CopyFrom กันสองกรณีผิดปกติไว้ตั้งแต่ต้น คือการคัดลอกจาก nil หรือคัดลอกชีตไปยังตัวมันเอง ทั้งสองกรณีจะคืนค่าทันทีและไม่ทำอะไรต่อ จากนั้นจึงเป็นงานคัดลอกจริง และตั้งใจให้ครอบคลุมกว้าง

ข้อมูลเซลล์มาก่อน CopyFrom ขอ UsedRange จากต้นทาง ซึ่งเป็นกรอบครอบที่แน่นที่สุดของเซลล์ที่มีข้อมูลและบริเวณที่ถูกรวมเซลล์ แล้วใช้กลไก CopyRangeTo ที่มีอยู่เพื่อพกทุกค่า ทุกสูตร และดัชนีสไตล์รายเซลล์ไปยังปลายทางโดยเริ่มที่ A1 เหนือชั้นเซลล์นั้น มันจะเล่นซ้ำสถานะระดับชีตทั้งหมดที่ทำให้เทมเพลตดูสมบูรณ์

ช่วงที่ถูกรวมเซลล์ สร้างใหม่ตามพิกัดเพื่อให้แถบหัวครอบคลุมสี่เหลี่ยมเดิม
ความกว้างคอลัมน์และความสูงแถว รวมถึงรายการ hidden, collapsed และ outline-level ถูกคัดลอกตรงตัวเพื่อให้แถวและคอลัมน์ที่ไม่ใช่ค่าเริ่มต้นตรงกันพอดี
Freeze panes และสถานะการมองเห็น ได้แก่ ระดับซูม การแสดงเส้นตารางและค่าเป็นศูนย์ ทิศทางขวาไปซ้าย และชนิดของมุมมอง
สถานะการป้องกันพร้อมตัวเลือกสิทธิ์รายรายการกระทำ เพื่อให้เทมเพลตที่ล็อกไว้ถูกล็อกเหมือนเดิม
ชุด page-setup ทั้งหมด ได้แก่ ระยะขอบ แนวกระดาษ ขนาดกระดาษ การปรับสเกลและ fit-to-page พื้นที่พิมพ์ ชื่อที่พิมพ์ส่วนหัวและท้ายกระดาษ และ flag print-gridlines กับ print-headings
ช่วง AutoFilter สีแท็บ และการมองเห็นของชีต

ผลลัพธ์คือชีตที่พิมพ์ กรอง และแสดงผลเหมือนต้นฉบับทุกประการ และเพราะเซลล์ ช่วงที่ถูกรวม และรายการมิติถูกสร้างใหม่จริงบนชีตใหม่แทนที่จะอ้างอิงร่วมกัน สำเนาจึงเป็นอิสระเต็มที่ เขียน 999 ลงในเซลล์บนสำเนา ต้นฉบับก็ยังคงค่าเดิมอยู่ ความเป็นอิสระนี้คือคุณสมบัติสำคัญที่สุดของการโคลนสำหรับรายงานระดับภูมิภาคที่ทำงานขนานกัน และเดโม SheetCopy ที่มากับผลิตภัณฑ์ก็ยืนยันเรื่องนี้ไว้ชัดเจน

สิ่งที่ปล่อยให้คัดลอกแบบตื้นไว้ และเพราะอะไร

มาถึงส่วนที่ต้องพูดตรงๆ กราฟ รูปภาพฝังในไฟล์ ตาราง XLSX data validations และกฎ conditional-formatting จะไม่ถูกคัดลอก นี่เป็นขอบเขตที่ตั้งใจและระบุไว้แล้ว ไม่ใช่ความหลงลืม และควรเข้าใจเหตุผลไว้ จะได้วางแผนรอบมันแทนที่จะประหลาดใจ

แต่ละคอลเลกชันเหล่านั้นมีอัตลักษณ์และรีเฟอเรนซ์ที่การคัดลอกระดับฟิลด์แบบง่ายไม่อาจรักษาไว้ได้ กราฟชี้ไปยังช่วงข้อมูลต้นทางและเป็นเจ้าของความสัมพันธ์การวาดในแพ็กเกจ OOXML การโคลนวัตถุโดยไม่ remap ความสัมพันธ์และ reference ของซีรีส์จะทำให้กราฟไปเรนเดอร์กับข้อมูลผิดชุด หรือทำให้ Excel แจ้งว่าไฟล์ต้องซ่อม ตารางมีชื่อที่ต้องไม่ซ้ำภายในเวิร์กบุ๊ก มีแถวหัวข้อที่ผูกกับคอลัมน์เฉพาะ และมีความสัมพันธ์ที่สร้างเองอัตโนมัติ Conditional formats และ data validations ผูกกับช่วงพิกัด และในกรณีของ validation ยังอ้างอิงช่วงอื่นด้วยสูตรได้ การคัดลอกแบบลึกให้ถูกต้องของสิ่งเหล่านี้จึงหมายถึงการเขียน reference ใหม่และสร้างอัตลักษณ์ใหม่ ซึ่งเป็นงานจริงที่มีจุดล้มเหลวจริงๆ การทำแค่ครึ่งทาง คือคัดลอกวัตถุแต่ไม่คัดลอกรีเฟอเรนซ์ แย่กว่าการไม่คัดลอกเลย เพราะมันจะได้ไฟล์ที่เปิดขึ้นมาพร้อมคำถามให้ซ่อมและแอบทิ้งเนื้อหาบางส่วน ดังนั้นเอนจินจึงคัดลอกเฉพาะสิ่งที่คัดลอกได้อย่างสะอาด แล้วปล่อยคอลเลกชันที่มีรีเฟอเรนซ์ไว้ให้ผู้เรียก ซึ่งย่อมรู้ว่าเป้าหมายควรชี้ไปที่อะไร

ในทางปฏิบัติ เวิร์กโฟลว์สำหรับเทมเพลตที่ซับซ้อนกว่าคือ ทำสำเนาชีตเพื่อเอาเซลล์ เลย์เอาต์ และการตั้งค่าพิมพ์มา จากนั้นสร้างกราฟ ตาราง validations หรือ conditional formats บนสำเนาใหม่ด้วย API เดิมที่ใช้สร้างครั้งแรก เพราะคุณกำลังสร้างมันขึ้นใหม่บนช่วงของสำเนาเอง รีเฟอเรนซ์จึงออกมาถูกต้องตามโครงสร้าง สำหรับกราฟที่อ่าน A1:C10 ให้สร้างกราฟใหม่บนสำเนาที่ชี้ไปยัง A1:C10 ของสำเนา สำหรับ AutoFilter ที่ต้องใช้งานจริง สังเกตว่าช่วงฟิลเตอร์ยังถูกพกมาด้วย ดังนั้นคุณต้อง re-apply เฉพาะเงื่อนไขคอลัมน์ ส่วนกฎ conditional-formatting และ data-validation ที่จะเพิ่มกลับสามารถเรียกผ่านคำสั่งเดียวกับที่อธิบายไว้ใน บทความเรื่องเซลล์ที่รวมและเลย์เอาต์เทมเพลตรายงาน ซึ่งไล่ดูตาราง merge และโมเดลช่วงที่สำเนารับต่อมา

ตำแหน่งของการทำสำเนาในสายงานรายงาน

การทำสำเนาเวิร์กชีตเป็นคู่หูตามธรรมชาติของการสร้างจาก placeholder แนวทางที่ยึด token ไว้เป็นหลักใน คู่มือการสร้างรายงานจากเทมเพลตใน Delphi แก้โจทย์การใส่ข้อมูลลงในเลย์เอาต์ที่คนอื่นแก้ไข ส่วนการทำสำเนาแก้โจทย์การต้องใช้เลย์เอาต์นั้นหลายครั้งในเวิร์กบุ๊กเดียว เมื่อเอาสองอย่างนี้มารวมกัน รูปแบบก็ชัดเจน เก็บชีต Template ที่ยังสะอาดหนึ่งแผ่นไว้กับ token, merges และการตั้งค่าพิมพ์ จากนั้นสำหรับแต่ละภูมิภาคหรือแต่ละช่วงเวลาให้เรียก Duplicate เติมข้อมูลของช่วงนั้นลงในสำเนา แล้วไปต่อ เทมเพลตต้นฉบับจะไม่ถูกแก้ไข จึงเป็นแหล่งต้นทางที่เชื่อถือได้สำหรับสำเนาถัดไป และทุกชีตผลลัพธ์เริ่มจากเลย์เอาต์ที่เหมือนกันทุกไบต์

มีข้อสังเกตเรื่องลำดับขั้นตอนที่ช่วยตัดความสับสนไปได้มาก ควร duplicate ชีตก่อนเทข้อมูลเข้าไป ไม่ใช่หลังจากนั้น เทมเพลตควรเก็บโครงสร้างและการจัดรูปแบบ ไม่ใช่ตัวเลขของไตรมาสก่อน และการโคลนชีตที่จัดรูปแบบไว้แต่ยังว่างจะทำให้แต่ละสำเนาเริ่มต้นอย่างสะอาด ถ้าคุณ duplicate ชีตที่มีข้อมูลอยู่แล้ว ข้อมูลนั้นจะตามมาด้วย เพราะ CopyFrom คัดลอก UsedRange อย่างแม่นยำ ซึ่งบางครั้งก็คือสิ่งที่ต้องการ แต่สำหรับรายงานแบบ fan-out มักไม่ใช่

นิสัยตรวจสอบแบบเร็ว

เพราะการแบ่งการคัดลอกแบบลึกกับการคัดลอกแบบตื้นมองไม่เห็นจนกว่าจะตรวจจริง ให้ใส่เช็กลิสต์ห้าบรรทัดไว้ในงานแทนที่จะไว้ใจว่าทุกอย่างจะถูกพกมาครบ หลังจาก duplicate แล้ว ให้อ่านค่าสัญญาณโครงสร้างที่สำเนาควรสืบทอดกลับมาและ assert ว่าตรงกับต้นฉบับ

Copy := Book.Sheets.Duplicate(1, 'Region-North');
WriteLn(Format('merged=%d  colA=%.1f  freezeRow=%d  tabAuto=%d',
  [Copy.MergedCells.Count, Copy.ColWidth[1],
   Copy.FreezeRow, Integer(Copy.TabColorIsAuto)]));
// Prove independence: mutate the copy, confirm the source is untouched.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value is still whatever it was.

จำนวน merge, ความกว้างคอลัมน์หนึ่งคอลัมน์, แถว freeze และ flag สีแท็บ บอกคุณว่าชั้นที่ควรถูกคัดลอกนั้นติดมาจริง ส่วนในชีตใดก็ตามที่มีกราฟ ตาราง validations หรือ conditional formats ให้ถือว่าสิ่งเหล่านั้นเป็นรายการที่ต้องสร้างใหม่บนสำเนา การที่มันไม่อยู่มาด้วยเป็นเรื่องตั้งใจ และสิ่งที่ต้องทำคือเรียกไม่กี่คำสั่ง ไม่ใช่เปิดบั๊ก แนวคิดนี้คือคัดลึกในส่วนที่ปลอดภัย และปล่อยตื้นในส่วนที่รีเฟอเรนซ์จะพัง ซึ่งเป็นภาพรวมทั้งหมดของการใช้ฟีเจอร์นี้ให้ดี

การทำสำเนาเวิร์กชีตและการคัดลอกสถานะชีตด้วย CopyFrom ที่อธิบายไว้ที่นี่ มาพร้อมใน v2.91.0 ของ ส่วนประกอบสเปรดชีต HotXLS สำหรับ Delphi แบบเนทีฟ พร้อมตัวอย่าง SheetCopy ที่รันได้จริงซึ่งสาธิตวงจรการโคลนและการปรับแก้ตั้งแต่ต้นจนจบ