HotXLS ODS 상호 운용: Excel이 읽는 수식과 규칙

Excel과 LibreOffice 양쪽이 올바르게 읽는 ODS 파일을 만들려고, HotXLS는 모든 수식을 선언된 of: 네임스페이스 아래의 OpenFormula 구문으로 쓰고, 모든 값 또는 수식 조건부 서식은 두 번 씁니다. 커버되는 각 셀의 스타일에 붙는 <style:map>으로, Excel 16이 읽는 유일한 형태이고, 그리고 calcext:conditional-formats 블록으로, LibreOffice가 신뢰하는 형태입니다. 각 애플리케이션은 상대편을 위한 절반을 무시하므로, 한쪽에서 올바르게 보이는 파일은 다른 쪽에 대해 아무것도 증명하지 못합니다

마지막 문장이 v2.384.55부터 v2.384.72 사이의 여섯 HotXLS 릴리스 뒤에 있는 교훈입니다. 각 수정은 HotXLS가 쓰고 완벽하게 읽어 돌린 파일을 두 대상 애플리케이션 중 하나가 틀리게 읽는 데서 시작됐습니다. 이어지는 내용은 각 애플리케이션이 실제로 받아들이는 것, 양쪽을 만족시키는 마크업, 그것을 Delphi에서 만들어 내는 HotXLS API 호출입니다

ODS 파일이 한 애플리케이션에서는 멀쩡하고 다른 쪽에서는 깨지는 이유는?

ODS 파일이 한 애플리케이션에서는 멀쩡하고 다른 쪽에서는 깨지는 이유는 Excel과 LibreOffice가 같은 패키지의 다른 부분을 읽기 때문입니다. OpenDocument는 수식과 조건부 서식에 하나 이상의 합법적 표기를 허용하고, LibreOffice는 자기 확장 네임스페이스를 그 위에 얹으며, 소비자마다 자기가 구현한 부분집합을 고릅니다. 소비자 하나에 대해서만 테스트된 writer는 다른 쪽이 조용히 오독하는 마크업에 기꺼이 수렴합니다

어느 애플리케이션도 오류를 보고하지 않습니다. LibreOffice는 파싱하지 못한 수식의 셀에 #VALUE!를 표시하고, Excel은 조건부 서식이 그저 없는 채로 통합 문서를 열거나, #NAME?이나 상수 0으로 평가되는 무언가로 다시 쓴 수식과 함께 엽니다. 자기 출력을 왕복시키는 writer는 이런 것을 결코 보지 못합니다. HotXLS는 정확히 그 함정을 수식 네임스페이스로 밟았습니다. reader가 of: 접두를 평문으로 매칭했으므로, 자기 왕복은 모두 통과하는 동안 LibreOffice는 모든 수식 셀에 #VALUE!를 표시했습니다

기능	Excel 16이 읽는 것	LibreOffice 26.2가 읽는 것
A:A로 쓴 열 전체	A:(A)로 오독	허용됨
[.A:.A]로 쓴 열 전체	예	예
<style:map>의 조건부 서식	예, 읽는 유일한 형태	calcext가 있으면 무시됨
calcext:conditional-formats의 조건부 서식	무시됨	예, 선호됨
calcext:operator 속성이 있는 calcext 값 규칙	무시됨	"0과 같음"으로 임포트됨
is-true-formula(...)로 쓴 calcext 수식 규칙	무시됨	0과의 값 비교로 임포트됨

ODS의 OpenFormula: 네임스페이스를 선언하고 구문을 바로 잡기

ODS의 수식 셀은 table:formula의 of: 접두가 선언된 XML 네임스페이스로 해석될 때만 LibreOffice가 읽을 수 있습니다. 접두는 장식이 아닙니다. of:는 urn:oasis:names:tc:opendocument:xmlns:of:1.2로, 그리고 HotXLS가 OpenFormula 번역기가 모델링하지 않는 수식에 쓰는 접두인 msoxl:은 http://schemas.microsoft.com/office/excel/formula로 사상됩니다. v2.384.56 전의 content.xml 루트는 둘 다 선언하지 않고 두 접두를 썼고, LibreOffice는 수식 문법을 전혀 식별하지 못했습니다

<!-- v2.384.56 전: 접두를 쓰되 선언하지 않음, LibreOffice는 #VALUE! 표시 -->
<office:document-content xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" ...>
  <table:table-cell table:formula="of:=SUM([.A1:.A3])" office:value-type="float" office:value="245"/>

<!-- v2.384.56부터: 두 수식 네임스페이스를 루트에 선언 -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

네임스페이스를 고쳐도 식 자체는 OpenDocument 1.3 Part 4가 정의한 유효한 OpenFormula여야 합니다. 함정은 Excel 구문과 OpenFormula가 비슷해 보이지만 같지 않은 자리들입니다:

• 셀 참조는 괄호와 점 접두를 쓰고 $ 표식은 참조의 일부입니다. [.$A$1]과 [.A$1:.$B2]는 유효한 OpenFormula입니다. v2.384.55 전의 HotXLS writer는 모든 $을 떨어뜨렸으므로 절대 참조가 상대 참조로 돌아왔고, 누군가 셀을 복사하기 전까지는 틀린 줄 몰랐습니다
• 열 전체와 행 전체는 괄호 형태 [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]를 써야 합니다. 맨 of:=SUM(A:A)은 LibreOffice가 허용하지만 Excel 16은 #NAME?과 함께 =SUM(A:(A))으로 열고, 행 참조와 $A:$B는 상수 0으로 만듭니다. HotXLS는 v2.384.65부터 괄호 형태를 씁니다
• 함수 인자는 ,가 아니라 ;로 구분됩니다
• 참조 합집합은 ~ 연산자를 씁니다. Excel의 AREAS((A1,B2))는 AREAS(([.A1]~[.B2]))가 됩니다. 그 쉼표를 ;로 번역하면 합집합 인자 하나가 인자 둘로 바뀝니다
• 인라인 배열은 열을 ;로, 행을 |로 나눕니다. Excel의 {1,2;3,4}는 {1;2|3;4}가 됩니다. v2.384.55 전의 HotXLS는 값 넷의 한 행인 {1;2;3;4}를 만들어 냈습니다

쉼표가 어려운 부분입니다. Excel 문자 하나가 세 가지 의미를 지니기 때문입니다. v2.384.55부터 HotXLS writer는 번역하는 동안 괄호 스택을 추적합니다. 이름 바로 뒤의 (는 함수 호출을 열며 그 쉼표는 ;가 되고, 그 외의 (는 묶음 괄호이며 그 쉼표는 ~가 되고, {} 안의 쉼표는 배열 열 구분자입니다. 이것과 네임스페이스 수정으로 LibreOffice 26.2는 여덟 배열과 합집합 탐침 수식 전부, 합집합 위의 INDEX와 AREAS까지 올바르게 평가했습니다

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 120;
    Sheet.Cells[2, 1].Value := 80;
    Sheet.Cells[3, 1].Value := 45;
    Sheet.Cells[1, 2].Value := 0.2;

    // v2.384.65부터 of:=SUM([.A:.A])로 쓰임
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // of:=[.A1]*[.$B$1]로 쓰임, $ 표식은 v2.384.55부터 생존
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

    Book.SaveAsODS('orders.ods');
  finally
    Book.Free;
  end;
end;

번역기가 모델링하지 않는 수식은 Excel 텍스트 그대로 msoxl:=으로 폴백되며, 그래서 msoxl 선언도 중요합니다. 현재 writer에서 그 경로에는 Sheet2!A1 같은 시트 한정 참조와 구조화된 테이블 참조가 포함됩니다. HotXLS는 임포트 때 msoxl: 수식을 다시 읽으므로 자기 왕복은 식을 온전히 유지하지만, 다른 애플리케이션이 그것을 어떻게 다루는지는 writer의 통제 밖입니다. 소비자가 의존하는 수식이 msoxl: 접두로 나온다면, 배포 전에 두 애플리케이션에서 파일을 열어 보세요

calcext로만 쓴 조건부 서식을 Excel은 왜 보지 못할까?

Excel 16이 calcext 조건부 서식을 보지 못하는 이유는 ODS 조건부 서식을 오로지 셀 스타일의 <style:map> 자식에서만 읽고 calcext:conditional-formats 블록을 완전히 무시하기 때문입니다. 이를 확인하는 실험은 짧습니다. LibreOffice가 저장한 ODS에서 style:map 요소를 지우면 Excel은 규칙을 0개 읽고, 대신 calcext 블록을 지우면 Excel은 여전히 전부 읽습니다. LibreOffice는 반대로 동작합니다. calcext는 ODF 표준의 일부가 아니라 LibreOffice의 확장 네임스페이스이며, calcext 규칙이 있으면 LibreOffice는 그것을 취하고 style:map을 무시합니다

v2.384.69 전의 HotXLS는 calcext만 썼으므로, 하이라이트가 완벽한 ODS 파일이 Excel에서는 값 규칙도 수식 규칙도 없이 열렸습니다. HotXLS는 이제 두 형태를 모두 씁니다. style:map 절반은 OpenDocument 스키마(ODF 1.3 Part 3)의 조건 문법을 쓰며, Excel 16과 LibreOffice 26.2가 ODS를 저장할 때 둘 다 만들어 내는 바로 그 표기들입니다:

<!-- 단순화함. A1:A50 모든 셀의 캐리어 스타일(값 규칙 두 개) -->
<style:style style:name="ce3" style:family="table-cell">
  <style:map style:condition="cell-content()&gt;100"
             style:apply-style-name="CF_Hit"
             style:base-cell-address="Orders.A1"/>
  <style:map style:condition="cell-content-is-between(1,10)"
             style:apply-style-name="CF_Low"
             style:base-cell-address="Orders.A1"/>
</style:style>

<!-- C1:C50 모든 셀의 캐리어 스타일(수식 규칙 하나) -->
<style:style style:name="ce4" style:family="table-cell">
  <style:map style:condition="is-true-formula(COUNTIF([.$C:.$C];[.C1])&gt;1)"
             style:apply-style-name="CF_Dup"
             style:base-cell-address="Orders.C1"/>
</style:style>

style:map의 까다로운 점은 셀 스타일 위에 살아 있어 셀 단위라는 것입니다. 규칙 범위의 모든 셀은 빈 셀을 포함해 맵을 지닌 스타일을 달고 있어야 하며, 그렇지 않으면 Excel에서 규칙은 그저 그 셀을 커버하지 않습니다. HotXLS는 각 셀의 기존 서식 스타일을 복사해 맵을 덧붙이고, 원래 스타일과 맵 텍스트의 쌍으로 캐리어 스타일을 중복 제거하므로, 동일한 서식의 500셀 범위도 여전히 스타일 하나를 만들어 냅니다. writer는 또한 쓰인 테이블을 규칙 범위까지 확장하므로, 규칙 안의 빈 꼬리 행은 버려지는 대신 출력됩니다. v2.384.69부터 styles.xml은 빈 Default 셀 스타일도 싣습니다. 그러므로 style:apply-style-name="Default"은 언제나 대상을 가집니다

LibreOffice가 실제로 받아들이는 calcext 표기

LibreOffice는 비교 연산자가 값 텍스트의 일부일 때, >3이나 between(1,10)처럼, calcext 값 규칙을 받아들이고, 수식 규칙은 formula-is(...)로 쓰였을 때만 받아들입니다. 두 지점 모두 HotXLS에게 릴리스 하나의 대가였습니다. 잘못된 표기는 오류 없이 임포트되는 규칙을 만들고 나서 엉뚱한 셀에 맞기 때문입니다

첫 실수는 calcext:value 옆의 calcext:operator 속성이었습니다. 자연스럽게 읽히지만 지어낸 것입니다. LibreOffice는 그 속성을 모르므로 모든 값 규칙을 "0과 같음"으로 임포트했죠. 두 번째는 style:map 표기인 is-true-formula(...)를 calcext 조건에 넣은 것으로, LibreOffice는 그것 역시 0과의 셀 값 비교로 임포트했습니다. 수식 수정은 v2.384.66에, 값 수정은 v2.384.69에 배송됐습니다:

<!-- 잘못됨: LibreOffice는 calcext:operator를 무시하고 "0과 같음"으로 임포트 -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- 맞음: 연산자는 값 안으로 -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:value="&gt;100" calcext:base-cell-address=".A1"/>
<calcext:condition calcext:apply-style-name="CF_Low"
                   calcext:value="between(1,10)" calcext:base-cell-address=".A1"/>

<!-- 맞음: 수식 규칙은 formula-is, 베이스 셀에 고정된 상대 참조 -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

베이스 셀이 상대 참조에 의미를 부여하는 장치입니다. HotXLS는 모든 규칙을 첫 범위 영역의 왼쪽 위 셀에 고정하므로, C1을 위해 쓴 수식은 C2, C3로 범위를 따라 내려가며 평가됩니다. Excel 자체 조건부 서식에서와 정확히 같은 방식입니다. 규칙 식은 셀 수식과 같은 번역기를 통과하므로, 배열, 합집합, 열 전체, $ 표식은 위에서 기술한 형태로 나옵니다. Delphi 쪽에서는 .xlsx 파일에 하듯 똑같이 규칙을 추가합니다

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // 값 규칙: style:map cell-content()>100에 calcext value ">100"을 더함
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: 연한 빨강

  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
  Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);

  // Excel 구문의 수식 규칙(쉼표 구분자, C1에 상대적):
  // style:map is-true-formula(...)에 calcext formula-is(...)를 더함
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: 연한 노랑

  Opts := TODSExportOptions.Create;
  try
    Opts.Generator := 'OrderExport 3.1';
    Book.SaveAsODS('orders.ods', Opts);
  finally
    Opts.Free;
  end;
end;

Excel과 LibreOffice의 ODS를 Delphi로 다시 읽기

HotXLS가 ODS 파일을 열 때 reader는 두 조건부 서식 방언과 두 calcext 표기를 모두 받아들이고, 파일이 두 형태로 규칙을 싣고 있어도 규칙을 두 번 세지 않습니다. 실제 파일은 저마다 습관이 있는 세 writer에서 나옵니다:

• 옛 calcext와 새 calcext. v2.384.69 전의 HotXLS가 쓴 ODS를 포함해 calcext:operator 속성이 있는 파일은 여전히 레거시 파싱을 거칩니다. 수식 조건은 formula-is(...)와 is-true-formula(...) 어느 쪽으로도 인식됩니다
• Excel의 style:map 표기. Excel은 조건에 of:를 접두로 붙이고(of:cell-content-is-between(1,10)처럼) 값 규칙에서 베이스 셀을 생략합니다. 둘 다 받아들여집니다
• 빈 셀. Excel과 LibreOffice는 모두 빈 셀의 맵을 셀이 아니라 열 기본 스타일에 붙이므로, reader는 맵을 모으기 전에 반복 셀의 열 기본 스타일을 해석합니다
• 영역 재구축. 맵은 셀 단위로 모으므로, 시트를 읽은 뒤 reader는 같은 조건과 베이스 셀을 공유하는 셀을 다시 범위로 병합합니다. 먼저 행마다 가로질러, 그다음 맞는 열 구간을 따라 내려가며, calcext에서 이미 읽은 규칙은 버립니다

v2.384.72 수정은 규칙이 아니라 숫자 스타일에 관한 것입니다. Excel 16과 LibreOffice 26.2는 모두 General 형식을 number:number 요소가 number:decimal-places 없는 숫자 스타일로 쓰며, 보통 <number:number number:min-integer-digits="1"/>입니다. HotXLS reader는 없는 자릿수를 고정 소수 둘로 다뤄, Default 스타일의 모든 값이 0.00으로 임포트되고 1.5는 1.50으로 표시됐습니다. v2.384.72부터 소수 자릿수도, 최소 소수도, 그룹화도 없고 정수 자릿수가 최대 하나인 순수 숫자 요소는 General로 사상되고, 홀로 있는 General은 셀을 숫자 서식 없는 채로 둡니다. 주변 텍스트는 General" kg"처럼 유지되며, 그룹화된 숫자는 Excel에 그룹화된 General 형식이 없으므로 이전 사상을 유지합니다

uses
  SysUtils, lxCondFormat, lxHandleX;

procedure DumpOdsRules(const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Rule: TXLSXConditionalFormat;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <= 0 then
      raise Exception.Create('cannot open ' + FileName);
    if Book.SourceFormat <> xlsxOpenDocumentSpreadsheet then
      raise Exception.Create('not an ODS package');

    Sheet := Book.Sheets[1]; // Sheets 인덱서는 1 기반
    for I := 0 to Sheet.ConditionalFormats.Count - 1 do
    begin
      Rule := Sheet.ConditionalFormats[I];
      case Rule.Kind of
        cfkCellIs:
          Writeln(Rule.Range, ' value rule ', Ord(Rule.Op), ' ',
            Rule.Formula1, ' ', Rule.Formula2);
        cfkExpression:
          Writeln(Rule.Range, ' formula rule ', Rule.Formula1);
      end;
    end;

    // Excel의 General 스타일 셀은 v2.384.72부터 '0.00' 대신
    // 숫자 서식 없이 읽힘
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

규칙 수식은 쉼표 구분자를 쓴 Excel 구문으로 돌아오며, AddCondFormatExpression에 넘길 형태와 같으므로, HotXLS가 쓴 규칙은 동일한 문자열로 읽어 돌아옵니다. ODS 임포트 경로가 무엇을 유지하고 버리는지의 더 넓은 그림은 HotXLS ODS 열기와 저장 왕복 가이드를, Excel과 LibreOffice의 반복 행이 임포트 때 어떻게 펼쳐지는지는 행 높이 구간으로서의 ODS 반복 행을 참조하세요

HotXLS ODS 조건부 서식 상호 운용의 한계는?

이중 마크업 접근은 값 비교 규칙과 수식 규칙을 커버하고 거기서 멈춥니다. 그 밖의 모든 것은 한쪽 전용이거나 아예 쓰이지 않습니다:

• 색조와 데이터 막대는 calcext 요소로만 쓰이므로, LibreOffice는 보여 주고 Excel은 그렇지 않습니다
• 다른 규칙 종류, 아이콘 집합, 텍스트 규칙, top-N, 평균 이상, 중복 규칙 같은 것들은 현재 writer에 ODS 출력이 없습니다. 텍스트 규칙은 보통 수식 규칙으로 다시 표현할 수 있습니다. 예컨대 B2:B200에 대한 ISNUMBER(SEARCH("late",B2))는 그 후 양쪽 애플리케이션에 닿습니다
• C:C 같은 열 전체와 행 전체 규칙은 1,048,576행 모두가 아니라 실제로 쓰인 테이블 영역 위에만 깔리므로, Excel은 이 규칙들을 파일에 존재하는 셀에서만 봅니다
• style:map만 있는 파일. 파일에 calcext 블록이 없으면 HotXLS는 수식 규칙의 상대 참조를 표기된 베이스 셀에서 시프트하는 게 아니라 재구축된 범위의 왼쪽 위 모서리에서 해석합니다
• LibreOffice의 겹치는 규칙. 한 셀이 여러 규칙으로 커버되면 LibreOffice는 첫 규칙의 맵만 그 위에 씁니다. 그런 파일은 style:map만으로는 완전히 읽을 수 없으며, 둘 다 있을 때 reader가 calcext를 선호하는 또 한 가지 이유입니다

이 모든 것보다 절차적 한계가 더 중요합니다. 이 릴리스들 뒤의 결함들은 ODS를 쓰고 HotXLS로 읽어 돌리는 왕복을 통과했고, 일부는 엉뚱한 애플리케이션에서의 수동 검사도 통과했을 겁니다. 열 전체 수식은 LibreOffice에서 잘 되는 동안 Excel은 #NAME?을 보여 줬고, v2.384.66부터 수식 규칙은 LibreOffice에서 잘 되는 동안 Excel은 v2.384.69까지 규칙을 전혀 보여 주지 않았습니다. ODS 상호 운용이 요구사항이라면 인수 테스트는 파일을 Excel과 LibreOffice에서 열어 각자 무엇을 보여 주는지 비교하는 것입니다. 같은 규율이 규칙이 가리키는 스타일에도 적용됩니다. HotXLS 조건부 서식과 스타일 글이 하이라이트 스타일이 통합 문서 쪽에서 어떻게 정의되는지 다룹니다

빠른 참조: 두 애플리케이션이 모두 읽는 ODS

• content.xml 루트에 xmlns:of와 xmlns:msoxl을 선언할 것. 그렇지 않으면 LibreOffice는 모든 수식에 #VALUE!를 표시합니다(HotXLS는 v2.384.56부터)
• 참조는 [.A1]로 쓰고 모든 $을 유지하며, 열 전체와 행 전체는 [.A:.A]와 [.1:.1]로 쓸 것(v2.384.55와 v2.384.65부터)
• 인자에는 ;, 참조 합집합에는 ~, 인라인 배열 행 사이에는 |를 쓸 것
• 각 값 또는 수식 규칙은 Excel을 위해 커버되는 모든 셀의 스타일에 <style:map>으로, LibreOffice를 위해 calcext 조건으로 쓸 것(v2.384.69부터)
• calcext에서는 연산자를 값 안에 두고(>3, between(1,10)) 수식 규칙은 베이스 셀과 함께 formula-is(...)로 쓸 것(v2.384.66과 v2.384.69부터)
• 임포트 때 number:decimal-places 없는 General 숫자 스타일을 예상할 것. HotXLS는 v2.384.72부터 그것을 General로 읽습니다
• 새 내보내기 프로필은 Excel과 LibreOffice 양쪽에서 파일을 열어 검증할 것. 한쪽만으로는 절대 안 됩니다

HotXLS는 Excel이나 LibreOffice 설치 없이 XLS, XLSX, ODS를 읽고 쓰는 네이티브 Delphi와 C++Builder 스프레드시트 라이브러리입니다. 전체 소스, 기능 목록, 라이선싱은 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다