การเปิดและบันทึกสเปรดชีต ODS ใน Delphi ด้วย HotXLS

ระบบหลังบ้านของรายงาน Delphi ที่ปล่อย .xlsx มาหลายปีแล้ว รับข้อกำหนดใหม่เข้ามา: กฎการจัดซื้อจัดจ้างของลูกค้าภาครัฐกำหนดให้ต้องส่งออกเป็น OpenDocument Spreadsheet และนักวิเคราะห์ในบัญชีนั้นก็ส่งการแก้ไขกลับมาเป็นไฟล์ .ods ที่ save จาก LibreOffice ตอนนี้โค้ดตัวเดียวกันจึงต้องเขียน ODS และอ่านมันด้วย HotXLS ไลบรารี spreadsheet แบบ Object Pascal ของ losLab สำหรับ Delphi และ C++Builder จัดการทั้งสองทิศทางได้โดยไม่ต้องติดตั้ง Excel หรือ LibreOffice ที่ไหนเลย สิ่งที่มันไม่ทำคือทำให้สองทิศทางนั้นสมมาตรกัน การ export พกไปมากกว่าที่การ import กู้กลับมาได้มาก และทีมที่สันนิษฐานเป็นอย่างอื่นจะเห็นสูตรและ formatting ระเหยหายไปที่ไหนสักที่ระหว่างการแก้ไขของลูกค้ากับรายงานถัดไป โดยไม่มี error ให้ชี้สาเหตุเลย

การรองรับ ODS อาศัยอยู่บนส่วนหน้า XLSX ไม่ใช่ XLS

HotXLS มาพร้อม class hierarchy อิสระสองชุดในแพ็กเกจเดียว: TXLSWorkbook ใน unit lxHandle สำหรับไฟล์ .xls แบบ binary BIFF8 และ TXLSXWorkbook ใน unit lxHandleX สำหรับ package OOXML .xlsx จุดเข้าถึง OpenDocument ทุกจุด ทั้ง OpenODS, SaveAsODS, GetODSSheetNames แขวนอยู่บน TXLSXWorkbook ทั้งหมด ตำแหน่งที่ว่านี้ไม่ได้สุ่มเลือกมา package ODS ตามที่ระบุใน OASIS ODF 1.3 คือไฟล์ zip ที่พกสมาชิก mimetype, manifest และเนื้อหา content.xml ซึ่งทำให้มันเป็นญาติเชิงโครงสร้างของ zip แบบ OOXML ส่วน BIFF8 คือ binary record stream จากยุค 1990 ที่ไม่มีอะไรร่วมกันเลย

ตำแหน่งนั้นมีข้อจำกัดเชิงปฏิบัติอยู่: workbook .xls รุ่นเก่าจะกลายเป็น .ods ในการเรียกครั้งเดียวไม่ได้ คุณต้องเชื่อมเนื้อหา BIFF เข้าสู่โมเดล XLSX ก่อนด้วย SaveXLSWorkbookAsXLSX จาก unit lxXlsxExport เปิดผลลัพธ์นั้นใหม่ผ่าน TXLSXWorkbook แล้วค่อย export จากตรงนั้น bridge นี้ไม่ได้รักษาข้อมูลครบทุกอย่าง และควรรู้ช่องว่างของมันไว้ก่อนที่คุณจะสร้างต่อยอด มันคัดลอกค่า สูตร รูปแบบตัวเลข ฟอนต์ fill และความกว้างคอลัมน์ มันทิ้งเส้นขอบ range ที่ merge ไว้ comment แผนภูมิ และ conditional formatting แหล่งข้อมูล .xls ที่มี formatting หนัก ๆ จะไปถึง ODS ในสภาพที่ดูเรียบกว่าตอนที่มันจากมา และนั่นคือคุณสมบัติของ bridge ไม่ใช่ของตัวเขียน ODS

การตรวจจับฝั่ง import เป็นแบบอัตโนมัติ เมธอด Open ธรรมดาจดจำ package ODS ได้จากสมาชิก mimetype ของมัน แล้วถอยไปตรวจ content.xml ระดับบนสุดเมื่อสมาชิกนั้นไม่มีอยู่ ดังนั้น code path แบบทั่วไปที่ "เปิดอะไรก็ตามที่ผู้ใช้อัปโหลดมา" จึงไม่ต้องดมกลิ่นนามสกุลไฟล์ของตัวเองเลย หลังจากเปิดแล้ว property SourceFormat จะรายงานว่าสาขาไหนที่ทำงาน

การ export ไปยัง ODS ด้วย TODSExportOptions

call สำหรับ export เองเป็นแค่บรรทัดเดียว แต่ object ตัวเลือกที่ล้อมรอบมันพกการตัดสินใจที่ผู้ตรวจสอบจะถามถึงในภายหลัง:

var
  Book: TXLSXWorkbook;
  Opts: TODSExportOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-report.xlsx');
    Opts := TODSExportOptions.Create;        // ผู้เรียกเป็นเจ้าของและต้อง free มันเอง
    try
      Opts.Generator := 'ReportService 4.2'; // override ค่า meta:generator
      Opts.IncludeCharts := True;
      Opts.IncludeImages := True;
      Book.SaveAsODS('quarterly-report.ods', Opts);
    finally
      Opts.Free;
    end;
  finally
    Book.Free;
  end;
end;

object ตัวเลือกเป็นของผู้เรียก HotXLS จะไม่ free มันให้ ซึ่งเป็นเหตุผลว่าทำไม try..finally ชั้นในถึงอยู่ตรงนั้นและไม่ใช่ตัวเลือก property สองตัวที่เปลี่ยน output จริง ๆ แทนที่จะแค่ติดป้ายมัน คุ้มค่าที่จะมองใกล้ ๆ การตั้งค่า IncludeCharts := False ทำมากกว่าแค่ซ่อนแผนภูมิ: มันลอกเอกสารย่อยของแผนภูมิและรายการ manifest ของมันออกจาก package เลย ซึ่งเป็นสิ่งที่คุณต้องการพอดีเมื่อผู้บริโภคคือ data pipeline ที่จะสะดุดกับมัน Generator override สตริง meta:generator ของ ODF ซึ่งปกติจะอ่านว่า HotXLS/<version> override มันเมื่อเครื่องมือปลายทางระบุลายนิ้วมือผู้ผลิตไฟล์เพื่อจัดเส้นทาง support ถ้าไม่มีข้อไหนตรงกับกรณีของคุณ ให้ข้าม options object ไปเลย การเรียก SaveAs(FileName, xlsxOpenDocumentSpreadsheet) เหมือนกับ SaveAsODS ด้วยค่าเริ่มต้นทุกประการ และ overload แบบ stream บนทั้งสองตัวให้คุณเขียน package ตรงเข้า HTTP response ได้โดยไม่ต้องมีไฟล์ชั่วคราวเลย

สิ่งที่เส้นทาง import อ่าน และสิ่งที่มันข้ามไปอย่างตั้งใจ

อ่านส่วนนี้อย่างละเอียดก่อนที่คุณจะสัญญาความเที่ยงตรงของ round-trip ให้ใครก็ตาม การ import ODS ใน HotXLS เป็นเส้นทางแบบเบา ๆ อย่างตั้งใจ มันรักษาค่าเซลล์แบบ scalar และผลลัพธ์ที่แคชไว้ซึ่งแต่ละสูตรพกมาตอน save แล้วขยายแถวและคอลัมน์ที่ทำซ้ำออกมาเป็น grid มันไม่ดึงสไตล์ นิพจน์สูตรของ ODS หรือภาพวาดข้ามมาด้วย

ทางเลือกเรื่องสูตรคือสิ่งที่มีโอกาสกัดคุณมากที่สุด และมันถูกเลือกมาโดยตั้งใจ เซลล์ของ ODF เก็บสองสิ่งไว้เคียงข้างกัน: นิพจน์สูตรที่เขียนด้วยสำเนียง OpenFormula ที่นิยามไว้ใน ODF 1.3 Part 4 และค่าล่าสุดที่แอปพลิเคชันผู้ผลิตคำนวณไว้ให้มัน การแปล OpenFormula ให้เป็น syntax สูตรของ Excel เป็นปัญหาการแปลงสำเนียงในตัวของมันเอง พร้อม edge case จริง ๆ รอบคลังคำศัพท์ฟังก์ชัน syntax ของ reference และโมเดล error การอ่านค่าที่แคชไว้แทนช่วยเลี่ยงปัญหาการแปลผิดแบบเงียบ ๆ ทั้งกลุ่มนั้นไปได้ ดังนั้นตัวเลขที่คุณ import เข้ามาจึงเป็นตัวเลขเดียวกับที่ผู้ส่งเห็นครั้งล่าสุดเป๊ะ ๆ ต้นทุนคือมันมาถึงในรูปตัวเลข ไม่ใช่สูตรที่มีชีวิตซึ่งผลิตมันขึ้นมา

รูปแบบความล้มเหลวที่ควรออกแบบให้รอบคอบตามมาโดยตรง: spreadsheet ที่ผลรวมของมันถูกต้องตอนที่ LibreOffice save มันครั้งสุดท้าย จะ import เข้ามาพร้อมตัวเลขที่ถูกต้อง แต่ตัวเลขเหล่านั้นตอนนี้กลายเป็นค่าคงที่แล้ว แก้เซลล์ input คำนวณใหม่ แล้วไม่มีอะไรขยับเลย สูตรหายไปแล้ว เหลือแค่ผลลัพธ์สุดท้ายของมัน ถ้า workflow ต้องการสูตรที่มีชีวิตหลัง import ให้สร้างมันขึ้นใหม่ด้วยโปรแกรมจากกฎธุรกิจของคุณเองผ่าน Cell.Formula ซึ่งบนส่วนหน้าของ XLSX รับนิพจน์โดยไม่มีเครื่องหมายเท่ากับนำหน้า

ออกแบบรอบ round trip ที่ไม่สมมาตร

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

Book := TXLSXWorkbook.Create;
try
  Book.Open('vendor-revision.ods');          // ตรวจจับ format อัตโนมัติ
  if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
  begin
    // ค่าและผลลัพธ์สูตรที่แคชไว้จะมีอยู่หลังการ import ODS
    // แต่สไตล์และสูตรที่มีชีวิตจะไม่มี สร้างสิ่งที่ pipeline
    // ปลายทางต้องพึ่งพาขึ้นมาใหม่ก่อนที่จะ save
    Book.Sheets[0].Cells[2, 5].Formula := 'SUM(B2:D2)';
    Book.SaveAs('vendor-revision.xlsx');
  end;
finally
  Book.Free;
end;

รูปแบบเชิงสถาปัตยกรรมที่ตกผลึกออกมาจากนี้คือ: ปฏิบัติต่อไฟล์ .ods ที่เข้ามาเหมือนเป็น data feed ไม่ใช่เอกสารที่จะแก้ไขตรงที่มันอยู่ เก็บ workbook หลักไว้ในรูป .xlsx อ่านค่าออกมาจากการแก้ไขของลูกค้า แล้วปล่อย ODS ใหม่ตามต้องการจากสำเนาหลักนั้น การตรวจสอบควรอยู่ทั้งสองค่าย เปิดไฟล์ที่ export แล้วใน LibreOffice Calc ซึ่งเป็นผู้บริโภค ODF อ้างอิง และใน Excel ซึ่งอ่าน ODS มาหลายปีแล้วแต่ไม่ตรงกับ LibreOffice ที่ขอบเขตของการรองรับแผนภูมิและสไตล์ จำนวน sheet, เซลล์สำคัญไม่กี่ตัว และการมีอยู่ของแผนภูมิก็เพียงพอเป็น smoke check ต่อหนึ่ง export profile

คัดกรองไฟล์ ODS ก่อนที่จะเดินหน้า import

เมื่อ endpoint หนึ่งรับ upload การแสดงรายชื่อ sheet ถูกกว่าการ parse เต็มรูปแบบมาก และจับความประหลาดใจเชิงโครงสร้างได้ตั้งแต่เนิ่น ๆ:

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetODSSheetNames('incoming.ods', Names) <= 0 then
    raise Exception.Create('not a readable ODS package');
  if Names.IndexOf('Data') < 0 then
    raise Exception.Create('revision is missing the Data sheet');
finally
  Book.Free;
  Names.Free;
end;

ธรรมเนียมค่าที่คืนกลับมักทำให้คนสะดุด: call ของ HotXLS โดยทั่วไปคืนค่าจำนวนที่เป็นบวกหรือ 1 เมื่อสำเร็จ และ -1 เมื่อล้มเหลว โดยเคลียร์รายการทิ้งเมื่อมันล้มเหลว ดังนั้นให้ทดสอบด้วย <= 0 แทนที่จะเทียบกับค่าบวกค่าใดค่าหนึ่งโดยเฉพาะ GetODSSheetNames ไม่ reset และไม่เติมข้อมูลลงใน instance ของ workbook เลย ดังนั้น object ตรวจสอบตัวเดียวจึงตรวจสอบไฟล์ที่เข้ามาทั้งไดเรกทอรีได้ การตรวจสอบเชิงโครงสร้างแบบนี้จับความล้มเหลวที่พบบ่อยที่สุดในโลกจริงได้ นั่นคือนักวิเคราะห์เปลี่ยนชื่อหรือลบ sheet ก่อนส่งการแก้ไขกลับมา ที่ประตูทางเข้า ซึ่งข้อความ error ยังระบุชื่อไฟล์และ sheet ที่หายไปได้ แทนที่จะโผล่ขึ้นมาเป็น nil reference ที่ลึกลงไปอีกสามชั้น

ถ้าคุณกำลังสร้าง pipeline การแปลงที่กว้างขึ้นรอบสิ่งนี้ รูปแบบ workbook audit และ conversion workbench แสดงวิธีสำรวจฟีเจอร์ของไฟล์ก่อนเลือก format เป้าหมาย และ คู่มือประสิทธิภาพของ workbook ขนาดใหญ่ รักษาการ export แบบ batch ให้อยู่ในขอบเขตหน่วยความจำที่สมเหตุสมผล

HotXLS คือไลบรารี spreadsheet แบบ native สำหรับ Delphi และ C++Builder พร้อม source code เต็มรูปแบบ รายการฟีเจอร์ทั้งหมดและรายละเอียดการอนุญาตให้ใช้สิทธิ์อยู่ที่ หน้าผลิตภัณฑ์ HotXLS Delphi Component