HotXLS: การตรวจสอบเวิร์กบุ๊กและแปลงรูปแบบใน Delphi

งานทำให้สเปรดชีตเป็นมาตรฐานเป็นสามปัญหาสวมโค้ทเดียว คุณมีคลังเก็บรูปแบบผสม: .xls ยุค BIFF, .xlsx สมัยใหม่, .ods กระจัดกระจายจากการทดลอง LibreOffice บางอย่าง และไฟล์ไม่กี่ไฟล์ที่ไม่มีใครเปิดได้เพราะรหัสผ่านเดินออกไปพร้อมอดีตพนักงาน เป้าหมายคือแปลงทุกอย่างเป็น XLSX และ CSV เวอร์ชันของงานที่คนส่วนใหญ่เขียนคือลูปที่เปิดแต่ละไฟล์และบันทึกภายใต้นามสกุลใหม่ และมันทำงานได้ดีจนกระทั่งมีคนถามว่าไฟล์ไหนเสียแผนภูมิ ทิ้งมาโคร หรือไม่เปิดเลย ลูปไม่มีคำตอบ เพราะการแปลงเพียงอย่างเดียวไม่เก็บบันทึก เวิร์กเบนช์ทำ: ทำสินค้าคงคลังก่อน แปลงทีสอง และตรวจสอบทีสาม และสามขั้นตอนต้องแบ่งปันข้อมูลเพื่อให้สิ่งใดสิ่งหนึ่งน่าเชื่อถือได้

การประกอบเวิร์กเบนช์นั้นใน Delphi หรือ C++Builder หมายถึงการเดินสายความสามารถ HotXLS สี่อย่างเข้าด้วยกัน ซึ่งไม่มีอย่างไหนต้องติดตั้ง Excel ที่ใดใน pipeline มีเอนจินพื้นฐานสองตัว คือ facade BIFF8 สำหรับ .xls และ facade OOXML สำหรับ .xlsx และ .ods มีการเรียกสอบถามราคาถูกที่อ่านเมทาดาต้าโดยไม่แยกวิเคราะห์ทั้งไฟล์ มีตัวนับการตรวจสอบต่อชีตที่บอกคุณว่าเวิร์กบุ๊กถืออะไรจริง ๆ และมีเมทริกซ์การแปลงที่มีโปรไฟล์ความเที่ยงตรงที่เป็นเอกสารสำหรับแต่ละเส้นทาง งานคือการรู้ว่าแต่ละอย่างมีขอบคมที่ไหน เพราะทุกอย่างมี และขอบคมเหล่านั้นคือสิ่งที่เปลี่ยนชุดค้างคืนที่สะอาดเป็นเหตุการณ์เช้าวันจันทร์

สอบถามก่อนโหลด: ชื่อชีตและการตรวจจับการเข้ารหัส

การเปิดเวิร์กบุ๊ก 200 MB เพียงเพื่อค้นพบว่ามันเข้ารหัสเป็นการเสียนาทีต่อไฟล์ และคูณข้ามคลังเก็บใหญ่เป็นการเสียวัน facade ทั้งสองเปิดเผย GetSheetNames ซึ่งอ่านเมทาดาต้าของชีตโดยไม่เติมเวิร์กบุ๊ก การใช้งาน BIFF สแกนเฉพาะเรคคอร์ด BoundSheet ที่ด้านหน้าของสตรีม; การใช้งาน OOXML อ่านเฉพาะ workbook.xml ภายใน zip ข้างๆมัน CanReadEncrypted ตรวจจับคอนเทนเนอร์การเข้ารหัสโดยไม่พยายามถอดรหัส:

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

รายละเอียดการปฏิบัติการสองอย่างทำให้ลูปนี้ถูก GetSheetNames ไม่รีเซ็ตหรือเติม instance เวิร์กบุ๊ก ดังนั้นออบเจกต์สอบถามเดียวสามารถจัดประเภทไฟล์นับพันโดยไม่สร้างใหม่ และเวอร์ชัน XLS-facade ของการเรียกเดียวกันก็เข้าใจแพ็คเกจ .xlsx ด้วย ซึ่งทำให้มันเป็นการสอบถามเดียวที่สะดวกเมื่อไม่สามารถเชื่อถือนามสกุลไฟล์ได้ ซึ่งแทบไม่ได้ในคลังเก็บเก่าขนาดนั้น การจัดลำดับก่อนโหลดสมควรการรักษาเป็นเอกเทศ; กลไกของการตรวจสอบน้ำหนักเบาอยู่ใน บทความของเราเกี่ยวกับการแสดงรายการชีตและการตรวจสอบเวิร์กบุ๊กน้ำหนักเบา

การนับสิ่งที่เวิร์กบุ๊กมีจริง ๆ

เมื่อไฟล์ผ่านการจัดลำดับ การผ่านการตรวจสอบจะตัดสินเส้นทางการแปลงของมัน facade XLSX เปิดเผยตัวนับสำหรับทุกตระกูลคุณสมบัติที่มีผลต่อการตัดสินใจความเที่ยงตรง: เซลล์ที่ผสาน แผนภูมิ รูปภาพ รูปแบบตามเงื่อนไข การตรวจสอบความถูกต้องของข้อมูล ตาราง ไฮเปอร์ลิงก์ และความคิดเห็น บวกแฟลกระดับเวิร์กบุ๊กสำหรับมาโคร การป้องกัน และรูปแบบแหล่งที่มา เส้นทางการแปลงของไฟล์ขึ้นอยู่แทบทั้งหมดกับว่าอะไรกลับมาไม่เป็นศูนย์

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

อ่าน Cells.Count ด้วยข้อควรระวังหนึ่งในใจ ที่เก็บเซลล์เบาบาง ดังนั้นจำนวนนับอินสแตนซ์เซลล์ที่สร้างขึ้น ไม่ใช่พื้นที่สี่เหลี่ยมของช่วงที่ใช้ ชีตที่มีค่าหนึ่งใน A1 และอีกค่าใน ZZ9999 รายงานสองเซลล์ ไม่ใช่ประมาณล้านที่อยู่ระหว่างนั้น การสแกนที่เทียบเท่าบนด้าน BIFF ใช้ขอบเขต UsedRange ร่วมกับ ForEachCell และมันมี off-by-one ที่สะดุดเกือบทุกคนครั้งแรก: UsedRange.FirstRow และพี่น้องของมันเป็น 0-based ในขณะที่ Cells.Item[Row, Col] เป็น 1-based การข้ามที่ลืมบวกหนึ่งให้แต่ละขอบเขตจะตรวจสอบสี่เหลี่ยมผิดและไม่บอก

คานสองอันลดต้นทุนของการผ่านตรวจสอบเท่านั้นบนไฟล์เดิมขนาดใหญ่ การตั้งค่า _DisableGraphics เป็น true ก่อนเปิด .xls ข้ามการแยกวิเคราะห์เลเยอร์การวาด OfficeArt ทั้งหมด ซึ่งประหยัดเวลาจริงบนเวิร์กบุ๊กที่หนาแน่นด้วยรูปร่าง เป็นการเพิ่มประสิทธิภาพแบบอ่านอย่างเดียวอย่างเคร่งครัด: การบันทึกจาก instance ที่เปิดแบบนั้นจะทิ้งภาพวาดที่มันไม่เคยแยกวิเคราะห์ ดังนั้นแฟลกนี้อยู่บนเส้นทางที่จะไม่เขียนไฟล์กลับเท่านั้น เมื่อการตรวจสอบต้องการเนื้อหาเซลล์ต่อเซลล์แทนจำนวน callback ForEachCell เดินเซลล์ที่เติมโดยตรงและหลีกเลี่ยงค่าใช้จ่าย Variant ต่อการเข้าถึงที่คุณสมบัติเซลล์ที่จัดทำดัชนีจ่ายในทุกการอ่าน ซึ่งเพิ่มขึ้นเร็วข้ามเซลล์นับล้าน

ทำให้รหัสผลลัพธ์ที่ไม่สอดคล้องเป็นปกติตั้งแต่เนิ่นๆ

การเรียก I/O ของ HotXLS รายงานข้อผิดพลาดผ่านผลลัพธ์จำนวนเต็มแทนข้อยกเว้น และธรรมเนียมไม่สม่ำเสมอข้าม API การเปิดและบันทึกส่วนใหญ่คืน 1 เมื่อสำเร็จและ -1 เมื่อล้มเหลว GetSheetNames คืนจำนวนชีต หรือ -1 กับรายการที่ล้าง SaveAsHTML ของ XLSX ทำลายรูปแบบอีกครั้งและคืน 0 สำหรับสำเร็จ, -1 สำหรับดัชนีชีตนอกช่วง เวิร์กเบนช์ที่ทดสอบ = 1 ทุกที่จะจัดประเภทการเรียกที่ส่งสัญญาณสำเร็จด้วยวิธีอื่นผิดอย่างเงียบ ๆ และที่ทดสอบ <> -1 จะกลืนที่ล้มเหลวด้วยรหัสอื่น

กฎที่อยู่รอดการสัมผัสกับ API ทั้งหมดแคบกว่าที่ดู: ปฏิบัติ <= 0 เป็นความล้มเหลวสำหรับการเรียกที่คืนจำนวน ตรวจสอบค่าสำเร็จที่เป็นเอกสารสำหรับแต่ละรูทีนบันทึกที่คุณใช้จริง และใส่ทั้งสองไว้หลังฟังก์ชันตรวจสอบผลลัพธ์เล็ก ๆ หนึ่งเพื่อให้ธรรมเนียมอยู่ในที่เดียวพอดี pipeline แบบแบตช์ล้มเหลวบ่อยกว่ามากจากการสะสมช้าของรหัสผลลัพธ์ที่ไม่ถูกตรวจสอบมากกว่าจากบั๊กตัวแยกวิเคราะห์แปลก ๆ และต้นทุนของการทำผิดมาถึงสี่หมื่นไฟล์ให้หลัง เมื่อไม่มีใครจำว่าการแปลงใดเกิดขึ้นจริง

เมทริกซ์การแปลงและที่แต่ละเส้นทางสูญเสียข้อมูล

facade ทั้งสองแบ่งงานการแปลงระหว่างกัน TXLSXWorkbook เปิด XLSX, ODS และ CSV และบันทึก XLSX, ODS, CSV, HTML, RTF และ XLSX ที่เข้ารหัส AES TXLSWorkbook เปิดและบันทึก BIFF และส่งออก HTML, RTF และ CSV สิ่งที่มีประโยชน์คือแต่ละเส้นทางมาพร้อมโปรไฟล์ความเที่ยงตรงที่เป็นเอกสาร ไม่ใช่สัญญาความถูกต้องที่คลุมเครือ ดังนั้นคุณสามารถตัดสินใจล่วงหน้าว่าเส้นทางใดปลอดภัยสำหรับไฟล์ใด

การส่งออก CSV เขียน UTF-8 กับ BOM, ปิดบรรทัด CRLF และการใส่เครื่องหมายคำพูด RFC 4180 สิ่งที่มันไม่ทำคือประเมินสูตร: เซลล์ที่ถือ =SUM(...) ส่งออกเป็นข้อความสูตรตามตัวอักษร ดังนั้นชีตของสูตรเปลี่ยนเป็นชีตของสตริง เว้นแต่คุณคำนวณค่าก่อน การส่งออก HTML ผลิตตารางเดียว โดย colspan และ rowspan ยืนแทนเซลล์ที่ผสานและสไตล์พื้นฐานแบบอินไลน์ การส่งออก RTF มีขีดจำกัดคมกว่า: มันไม่สามารถขยายเซลล์ที่ผสานข้ามคอลัมน์ ดังนั้นเซลล์ต่อเนื่องของการผสานออกมาว่าง การนำเข้า ODS เบาโดยเจตนา ตามเอกสารของไลบรารีเอง ค่าสเกลาร์และผลลัพธ์สูตรที่แคชมาผ่าน; สไตล์, นิพจน์สูตร ODF สด และภาพวาดไม่ผ่าน นั่นสำคัญเมื่อคลังเก็บมีไฟล์ OpenDocument จริงที่ควบคุมโดย OASIS ODF 1.3 ที่ซึ่งสิ่งใดก็ตามที่ใกล้เคียงกับการแปลงที่เที่ยงตรงทางสายตาต้องการมากกว่าที่เส้นทางนำเข้านี้สร้างมาเพื่อรับ และการผ่านการตรวจสอบคือสิ่งที่บอกคุณว่าไฟล์เหล่านั้นมีอยู่ก่อนที่แบตช์จะทำให้มันแบนราบอย่างเงียบ ๆ

SaveXLSWorkbookAsXLSX เป็นสะพานข้อมูล ไม่ใช่สะพานเลย์เอาต์

facade BIFF ไม่สามารถเขียน OOXML โดยตรง ดังนั้นการข้ามจาก .xls ไป .xlsx วิ่งผ่านฟังก์ชัน SaveXLSWorkbookAsXLSX ใน unit lxXlsxExport ความเที่ยงตรงของสะพานนั้นสมควรกล่าวตรงๆ เพราะชื่อแสดงมากกว่าที่มันทำ มันคัดลอกค่า สูตร รูปแบบตัวเลข สีเติม คุณสมบัติฟอนต์หลัก ความกว้างคอลัมน์ และการตั้งค่ามุมมองเช่นเส้นตาราง มันไม่คัดลอกเส้นขอบ ช่วงที่ผสาน ความคิดเห็น แผนภูมิ หรือรูปแบบตามเงื่อนไข สำหรับการทำให้เป็นมาตรฐานระดับข้อมูล ที่ซึ่งระบบปลายน้ำจะแยกวิเคราะห์ผลลัพธ์และไม่มีใครดูการจัดรูปแบบ นั่นพอดีและไม่มีอะไรที่ใครต้องการสูญหาย สำหรับรายงานคณะกรรมการที่จัดรูปแบบแล้วที่มุ่งให้คนอ่าน มันไม่พอ และนี่คือที่ที่ตัวนับการตรวจสอบได้ที่ของมัน: ไฟล์ที่การตรวจสอบติดธงว่าถือแผนภูมิและรูปแบบตามเงื่อนไขควรส่งไปคิวด้วยมือ ไม่ผ่านสะพานที่จะทิ้งทั้งสองโดยไม่บอก

var
  Legacy: IXLSWorkbook;        // การอ้างอิง interface: อย่า Free
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // สตรีม XML ของชีตเข้าไปใน zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

ลูปข้างต้นยังแสดงคานปริมาณงานบนด้าน OOXML การตั้งค่า StreamingWrite เป็น true สตรีม XML เวิร์กชีตโดยตรงเข้าแพ็คเกจผลลัพธ์แทน staging เป็นสตริงยักษ์ครั้งเดียวในหน่วยความจำ ซึ่งเป็นความแตกต่างระหว่างการรันสบายและความล้มเหลวหน่วยความจำไม่พอเมื่อไฟล์ถึงหลักแสนแถว การกำหนดขนาดและพฤติกรรมหน่วยความจำสำหรับโหมดนั้นได้รับการรักษาเป็นเอกเทศใน บทความของเราเกี่ยวกับการเขียนสตรีมสำหรับงานแบตช์เซิร์ฟเวอร์ คุณสมบัติหนึ่งเพิ่มเติมสำคัญสำหรับแบตช์ที่ต้องการใช้ทุกคอร์: facade ไม่ใช่ thread-safe แต่ไม่แชร์สถานะโกลบอล ดังนั้นรูปแบบที่รองรับสำหรับการแปลงขนานคือหนึ่ง instance เวิร์กบุ๊กต่อเธรดผู้ปฏิบัติงาน โดยไม่มีการล็อกระหว่างกัน

ไฟล์รหัสผ่าน และสิ่งที่ต้องทำกับมัน

ไฟล์ล็อกของคลังเก็บแบ่งสะอาดตามรูปแบบ และการแบ่งนั้นตัดสินว่ามันไปไหน การเข้ารหัส .xls เดิม ไม่ว่า RC4, RC4 เหนือ CryptoAPI หรือ XOR obfuscation เก่า สามารถอ่านได้: ส่งรหัสผ่านไปยัง Open และไฟล์แปลงเหมือนอย่างอื่น แพ็คเกจ .xlsx ที่เข้ารหัสเป็นเรื่องต่าง HotXLS ตรวจจับมันด้วย CanReadEncrypted แต่ไม่สามารถถอดรหัส ดังนั้นการเคลื่อนไหวที่ซื่อที่สุดเพียงอย่างเดียวคือส่งมันไปคิวที่คนเปิดและบันทึกใหม่แต่ละไฟล์ใน Excel ก่อนกลับเข้า pipeline ความไม่สมมาตรนั้นสมควรออกแบบไว้ล่วงหน้า เพราะไฟล์ XLSX ที่เข้ารหัสเป็นไฟล์ที่น่าจะเป็นเรคคอร์ดที่ใครบางคนสนใจจริง ๆ

ปิดลูปด้วยการตรวจสอบ

ขั้นตอนที่สามคือขั้นที่ถูกข้าม และการข้ามมันคือสิ่งที่เปลี่ยนการแปลงเป็นจำนวนมากเป็นความรับผิด เส้นทางบันทึกไม่มีใน HotXLS ประเมินสูตร Excel คำนวณใหม่เมื่อเปิดไฟล์ ดังนั้นการแปลง XLSX-เป็น-XLSX ยังคงถูกต้อง แต่เป้าหมาย CSV รับข้อความสูตรตามตัวอักษร เว้นแต่ pipeline รัน Calculate บนเซลล์ก่อนและเขียนผลลัพธ์กลับ การรู้ล่วงหน้าคือความแตกต่างระหว่าง CSV เต็มตัวเลขและ CSV เต็มสตริง =SUM(...) ที่ไม่มีใครสังเกตจนกว่าการนำเข้าปลายน้ำจะสำลัก

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

เวิร์กเบนช์ตรวจสอบก่อนเปลี่ยนกระบวนการแปลงจำนวนมากที่เสี่ยงเป็นกระบวนการที่วัดได้ที่มีเลนกักกันสำหรับไฟล์ที่ไม่สามารถผ่านสะอาด การเรียกสอบถาม นับ และแปลงทั้งหมดที่แสดงที่นี่เป็นส่วนหนึ่งของ HotXLS Delphi Component ซึ่งรันมันในกระบวนการโดยกำเนิดโดยไม่มีระบบอัตโนมัติของ Excel