בניית סביבת עבודה לביקורת והמרת חוברות עבודה ב-Delphi עם HotXLS

עבודת נורמליזציה המונית של גיליונות אלקטרוניים היא שלוש בעיות לבושות במעיל אחד. יש לכם ארכיון של פורמטים מעורבים: .xls מתקופת BIFF, .xlsx מודרני, פיזור של .ods מאיזה ניסוי LibreOffice, וחופן קבצים שאיש אינו יכול לפתוח מפני שהסיסמה הלכה עם עובד לשעבר. המטרה היא להמיר הכול ל-XLSX ול-CSV. הגרסה של אותה עבודה שרוב האנשים כותבים היא לולאה שפותחת כל קובץ ושומרת אותו תחת סיומת חדשה, והיא עובדת עד הרגע שבו מישהו שואל אילו קבצים איבדו את התרשימים שלהם, השמיטו את המאקרו שלהם, או מעולם לא נפתחו כלל. ללולאה אין תשובה, מפני שהמרה לבדה אינה שומרת רישום. סביבת עבודה כן שומרת: היא קודם עורכת מצאי, אז ממירה, ואז מאמתת, ועל שלושת השלבים לחלוק מידע כדי שכל זה יהיה אמין

הרכבת אותה סביבת עבודה ב-Delphi או ב-C++Builder פירושה חיבור של ארבע יכולות HotXLS, שאף אחת מהן אינה זקוקה ל-Excel מותקן בשום מקום בצינור. יש שני מנועים מקוריים, מעטפת BIFF8 ל-.xls ומעטפת OOXML ל-.xlsx ול-.ods. יש קריאות בדיקה זולות שקוראות מטא-נתונים מבלי לנתח את כל הקובץ. יש מוני ביקורת לכל גיליון שמספרים לכם מה חוברת עבודה באמת מחזיקה. ויש מטריצת המרה עם פרופיל נאמנות מתועד לכל מסלול. העבודה היא בידיעה היכן לכל אחד מאלה יש קצה חד, מפני שלכל אחד מהם יש, והקצוות הם בדיוק הדברים שהופכים אצווה לילית נקייה לאירוע של בוקר יום שני

בדקו לפני שאתם טוענים: שמות גיליונות וזיהוי הצפנה

פתיחת חוברת עבודה של 200 MB רק כדי לגלות שהיא מוצפנת מבזבזת דקות לכל קובץ, ומוכפלת על פני ארכיון גדול היא מבזבזת ימים. שתי המעטפות חושפות את 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 אינה מאפסת או מאכלסת את מופע חוברת העבודה, ולכן אובייקט בדיקה יחיד יכול לסווג אלפי קבצים מבלי שייווצר מחדש. וגרסת מעטפת ה-XLS של אותה קריאה מבינה גם חבילות .xlsx, מה שהופך אותה לבדיקה בודדת נוחה כשאי-אפשר לסמוך על סיומות הקבצים, כפי שלעיתים רחוקות אפשר בארכיון ישן כל כך. מיון לפני טעינה ראוי לטיפול משלו; מנגנון הבדיקה קל-המשקל נמצא במאמר שלנו על רשימת גיליונות ובדיקה קלת-משקל של חוברות עבודה

ספירה של מה שחוברת עבודה באמת מכילה

ברגע שקובץ עובר את המיון, מעבר הביקורת מחליט על מסלול ההמרה שלו. מעטפת ה-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, והיא נושאת את הסטייה-באחד שמכשילה כמעט כל אחד בפעם הראשונה: UsedRange.FirstRow ואחיו מבוססי-0, בעוד Cells.Item[Row, Col] מבוסס-1. מעבר ששוכח להוסיף אחד לכל גבול עורך ביקורת על המלבן הלא נכון ולעולם אינו אומר זאת

שני מנופים חותכים את עלות מעבר ביקורת-בלבד על קבצים ישנים גדולים. הגדרת _DisableGraphics ל-true לפני פתיחת .xls מדלגת לחלוטין על ניתוח שכבת הציור OfficeArt, מה שחוסך זמן אמיתי בחוברות עבודה צפופות בצורות. אך זו אופטימיזציה לקריאה-בלבד באופן מוחלט: שמירה ממופע שנפתח כך תשמיט את הציורים שמעולם לא נותחו, ולכן הדגל שייך רק למסלולים שלעולם לא יכתבו את הקובץ בחזרה. כשהביקורת זקוקה לתוכן לכל תא ולא לספירות, התקראה החוזרת ForEachCell צועדת ישירות על התאים המאוכלסים ועוקפת את תקורת ה-Variant שמאפייני תאים מאונדקסים משלמים בכל קריאה, מה שמצטבר מהר על פני מיליוני תאים

נרמלו מוקדם את קודי ההחזרה הלא עקביים

קריאות הקלט/פלט של HotXLS מדווחות על שגיאות דרך תוצאות שלמות ולא דרך חריגות, והמוסכמות אינן אחידות על פני ה-API. רוב קריאות הפתיחה והשמירה מחזירות 1 בהצלחה ו-1- בכישלון. GetSheetNames מחזירה את ספירת הגיליונות, או 1- עם הרשימה מנוקה. ה-XLSX SaveAsHTML שובר את התבנית שוב ומחזיר 0 בהצלחה, 1- עבור אינדקס גיליון מחוץ לטווח. סביבת עבודה שבודקת = 1 בכל מקום תסווג בשקט בצורה שגויה את הקריאות המסמנות הצלחה בדרך אחרת, וכזו שבודקת <> -1 תבלע את אלה שנכשלות עם קוד שונה

הכלל ששורד מגע עם כל ה-API צר יותר ממה שהוא נראה: התייחסו ל-<= 0 ככישלון עבור קריאות מחזירות-ספירה, בדקו את ערך ההצלחה המתועד עבור כל שגרת שמירה שאתם באמת משתמשים בה, והעמידו את שניהם מאחורי פונקציית בדיקת-תוצאה קטנה אחת כדי שהמוסכמה תחיה במקום אחד בדיוק. צינורות אצווה נכשלים הרבה יותר מהצטברות איטית של קודי החזרה לא נבדקים מאשר מכל באג מנתח אקזוטי, ועלות הטעות בכך מתבררת ארבעים אלף קבצים מאוחר יותר, כשאיש אינו זוכר אילו המרות באמת התרחשו

מטריצת ההמרה והיכן כל דרך מאבדת נתונים

שתי המעטפות מחלקות ביניהן את עבודת ההמרה. 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 הוא גשר נתונים, לא גשר פריסה

מעטפת ה-BIFF אינה יכולה לכתוב OOXML ישירות, ולכן המעבר מ-.xls ל-.xlsx רץ דרך הפונקציה SaveXLSWorkbookAsXLSX ביחידה lxXlsxExport. נאמנות הגשר הזה ראויה לאמירה ברורה, מפני שהשם מרמז על יותר ממה שהוא עושה. הוא מעתיק ערכים, נוסחאות, פורמטי מספרים, צבעי מילוי, מאפייני גופן ליבה, רוחבי עמודות והגדרות תצוגה כגון קווי רשת. הוא אינו מעתיק גבולות, טווחים ממוזגים, הערות, תרשימים או עיצובים מותנים. עבור נורמליזציה ברמת-נתונים, שבה מערכות במורד הזרם ינתחו את התוצאה ואיש אינו מסתכל על העיצוב, זה בדיוק מספיק ושום דבר שמישהו צריך אינו הולך לאיבוד. עבור דוח הנהלה מעוצב המיועד להיקרא בידי אדם, זה אינו מספיק, וכאן בדיוק מוני הביקורת מצדיקים את מקומם: קובץ שהביקורת סימנה כנושא תרשימים ועיצובים מותנים צריך להינתב אל תור ידני, לא דרך גשר שישמיט את שניהם בלי מילה

var
  Legacy: IXLSWorkbook;        // interface reference: do not 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;     // stream sheet XML into the zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

הלולאה למעלה גם מראה את מנוף קצב-העברה בצד ה-OOXML. הגדרת StreamingWrite ל-true מזרימה XML של גיליון עבודה ישירות אל חבילת הפלט במקום להעמיד אותו כמחרוזת ענקית אחת בזיכרון, וזה ההבדל בין ריצה נוחה לקריסת חוסר-זיכרון ברגע שקבצים מגיעים למאות אלפי שורות. גודל והתנהגות זיכרון עבור מצב זה מקבלים טיפול משלהם במאמר שלנו על כתיבות הזרמה לעבודות אצווה בשרת. מאפיין נוסף חשוב לאצווה הרוצה להשתמש בכל ליבה: אף אחת מהמעטפות אינה בטוחת-שרשורים, אך אף אחת גם אינה חולקת מצב גלובלי, ולכן התבנית הנתמכת להמרה מקבילית היא מופע חוברת עבודה אחד לכל שרשור עובד, ללא נעילה ביניהם

קובצי הסיסמה, ומה לעשות איתם

הקבצים הנעולים של הארכיון מתפצלים בנקיות לפי פורמט, והפיצול מחליט לאן הם הולכים. הצפנת .xls ישנה, בין אם RC4, RC4 מעל CryptoAPI, או ערפול ה-XOR הישן, ניתנת לקריאה: העבירו את הסיסמה ל-Open והקובץ מומר כמו כל אחר. חבילות .xlsx מוצפנות הן סיפור אחר. HotXLS מזהה אותן עם CanReadEncrypted אך אינה יכולה לפענח אותן, ולכן המהלך הכן היחיד הוא לנתב אותן אל תור שבו אדם פותח ושומר מחדש כל אחת ב-Excel לפני שהיא מצטרפת בחזרה לצינור. כדאי לתכנן עבור אי-הסימטריה הזו מראש, מפני שקובצי ה-XLSX המוצפנים הם אלה שסביר ביותר שיהיו הרשומות שמישהו באמת אכפת לו מהן

סגירת הלולאה עם אימות

השלב השלישי הוא זה שמדלגים עליו, והדילוג עליו הוא מה שהופך המרה המונית להתחייבות. שום מסלול שמירה ב-HotXLS אינו מעריך נוסחאות. Excel מחשב מחדש כשהוא פותח קובץ, ולכן המרת XLSX-ל-XLSX נשארת נכונה, אך יעד CSV מקבל את טקסט הנוסחה מילה במילה אלא אם הצינור מריץ תחילה Calculate על התאים וכותב את התוצאות בחזרה. לדעת זאת מראש הוא ההבדל בין CSV מלא במספרים לבין CSV מלא במחרוזות =SUM(...) שאיש אינו מבחין בהן עד שייבוא במורד הזרם נחנק בהן

האימות עצמו זול דיו עד שאין תירוץ להשמיטו. פתחו מחדש כל קובץ מומר עם אותה ספרייה, הריצו מחדש את מוני הביקורת, והשוו אותם מול המספרים שלפני-ההמרה שמעבר המצאי כבר רשם. ספירת גיליונות שירדה, ספירת תרשימים שהגיעה לאפס במקום שבו למקור היו שלושה, ספירת תאים שצנחה מצוק: כל אחת היא אובדן שקט שנתפס במחיר של פתיחה שנייה. בדקו מדגם בעין ב-Excel או ב-LibreOffice מעבר לכך, והשילוב תופס את הרוב המכריע של נזק ההמרה לפני שהוא נשלח. זו כל הסיבה שבגללה שלב המצאי מזין את שלב האימות. ללא מספרי-הלפני, מספרי-האחרי אינם מוכיחים דבר

סביבת עבודה מבוססת-ביקורת-תחילה הופכת המרה המונית מסוכנת לתהליך מדיד עם נתיב הסגר עבור הקבצים שאינם יכולים לעבור בנקיות. כל קריאות הבדיקה, הספירה וההמרה שהוצגו כאן הן חלק מ-HotXLS Component, שמריץ אותן באופן מקורי בתהליך ללא אוטומציית Excel