HotXLS로 Delphi에서 Excel 구조적 테이블 참조 다루기

HotXLS는 이제 구조적 테이블 참조를 평가하므로, =SUM(Table1[Amount])는 건너뛰어지는 대신 숫자를 만들어냅니다. 리졸버는 Table[Column], Table[[Column]], Table[[Q1]:[Q4]] 같은 열 범위, 그리고 항목 지정자 [#Data], [#All], [#Headers], [#Totals]를 처리하며, 각각을 파싱 시점에 통합 문서의 테이블 모델에 대해 해결하는 동안 원본 수식 텍스트는 그대로 왕복됩니다

한 가지 형식은 의도적으로 빠져 있으며, 사람들이 가장 먼저 마주치는 형식이기도 합니다. 현재 행 축약형인 [@Column]은 지원되지 않는데, 무작정 우회하기보다는 이해할 가치가 있는 구조적인 이유 때문입니다

구조적 참조는 왜 이름만 친절한 범위가 아닌가?

정의된 이름은 주소를 고정하지만 테이블 참조는 그렇지 않기 때문입니다. Sheet1!$A$2:$D$100을 가리키는 이름으로 DataBlock을 작성하면, 무언가 그것을 다시 쓸 때까지 그 사각형 그대로 남습니다. Sales[Amount]를 작성하면 그것은 "Sales 테이블의 Amount 열"을 뜻하는데, 수식이 평가되는 시점에 그 테이블의 범위가 무엇이든 그것을 가리킵니다. 테이블에 스무 개의 행을 추가하면 합계는 그것들을 포함하게 됩니다. 조정할 참조가 없는 이유는 애초에 수식 안에 주소가 존재한 적이 없기 때문입니다

바로 그 상징적인 성질 때문에 이 참조는 문자열 치환으로는 해결될 수 없습니다. 리졸버는 통합 문서 안에서 이름으로 테이블을 찾고, 헤더 텍스트로 열을 조회하고, 요청된 항목 지정자가 어느 행을 포괄하는지 결정한 다음, 구체적인 사각형을 만들어내야 합니다. HotXLS는 테이블 모델을 통해 수식 컴파일 도중 이 작업을 수행하며, 그래서 테이블이 커지기 전에 작성된 수식도 여전히 테이블의 현재 범위에 대해 평가됩니다

HotXLS가 해결하는 문법

지원되는 규격 문법은 단일한 직사각형 결과를 포괄하며, 정확히 짚어둘 가치가 있습니다. Excel 문서는 대부분의 엔진이 구현하는 것보다 훨씬 넓은 범위를 제시하기 때문입니다. HotXLS는 [Col]과 대괄호 변형인 [[Col]], 순수 항목 지정자인 [#Data], [#All], [#Headers], [#Totals], 결합 형식인 [[#Data],[Col]], 항목 지정자 안의 범위인 [[#Data],[Col1]:[Col2]], 그리고 순수 범위인 [Col1]:[Col2]를 받아들입니다

이 집합이 제공하는 것은 하나의 연속된 블록을 만들어내는 모든 참조 형태입니다. 열 하나, 인접한 열들의 연속, 또는 그중 하나의 본문만 혹은 헤더를 포함한 슬라이스입니다. 인접하지 않은 합집합과 여러 영역에 걸친 결과는 이 범위 밖입니다. 참조를 해결할 수 없을 때, 수식은 추측으로 대체하는 대신 기존의 값 없이 건너뛰는 동작을 유지하므로, 해결할 수 없는 참조가 그럴듯한 오답으로 둔갑하는 일은 결코 없습니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... 헤더 행과 24개의 데이터 행을 작성 ...

    Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

현재 행 형식은 왜 의도적으로 제외되었는가?

[@Column]과 [#This Row]는 "이 수식이 있는 행에서 그 열의 셀"을 뜻합니다. 따라서 그 값은 테이블뿐만 아니라 평가되는 셀의 위치에도 달려 있습니다. 이는 다른 종류의 참조입니다. 컴파일러가 한 번에 해결할 수 있는 사각형이 아니라, 수식이 차지하는 모든 행마다 다시 이루어져야 하는 셀 단위 해결입니다

HotXLS는 이런 형식에 대해 테이블 범위 리졸버에서 False를 반환하며, 이는 그것들을 값 없이 건너뛰는 경로로 보냅니다. 수식 텍스트는 보존되어 변경 없이 다시 기록되므로, [@Amount]를 사용하는 통합 문서는 여러분의 애플리케이션을 거쳐 왕복한 뒤에도 Excel에서 올바르게 열립니다. 다만 HotXLS가 계산한 값만 없을 뿐입니다. 값이 없는 것과 잘못된 행에 대해 계산된 값 사이에서 선택해야 한다면, 없는 쪽이 여러분이 탐지할 수 있는 쪽입니다

실용적인 우회책은 기계적입니다. 여러분이 생성하는 통합 문서에서는 동등한 A1 스타일 상대 참조를 작성하십시오. 어차피 이는 Excel이 상당수의 테이블 범위 로직에 대해 내부적으로 저장하는 방식이기도 합니다. 여러분이 단지 처리만 하는 통합 문서에서는 수식을 그대로 두고 Excel이 이미 저장해 둔 캐시된 값을 읽으십시오. 이는 로드 후 보고하는 파이프라인이 보통 원하는 것입니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table <> nil then
    begin
      // 테이블 본문에 대한 레코드셋 방식 조회, 1부터 시작하는 행 결과
      Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
      while Row > 0 do
      begin
        Log(VarToStr(Sheet.Cells[Row, 4].Value));
        Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
      end;
    end;
  finally
    Book.Free;
  end;
end;

테이블 모양이 바뀌면 무슨 일이 일어나는가

구조적 참조는 그것이 이름 붙인 대상이 사라졌을 때 조용히 다른 곳을 가리키는 대신 무효화됩니다. 열을 삭제하면 그 열을 참조하는 수식은 Excel이 무효화하는 방식 그대로 무효화됩니다. 테이블을 삭제하거나 이름을 바꿔도 그것을 참조하는 것들은 같은 방식으로 처리됩니다. 이는 올바른 동작이며, 삽입과 삭제에 대한 수식 참조 조정에서 설명하는 일반적인 참조 조정을 그대로 반영합니다. 그곳에서 엔진의 역할은 수식을 유효해 보이게 유지하는 것이 아니라 정직하게 유지하는 것입니다

행 증가는 정반대의 경우이며 어떤 조정도 전혀 필요하지 않습니다. 참조가 사각형이 아니라 테이블의 이름을 가리키므로, 테이블 범위 안에 행을 추가하면 수식 하나 건드리지 않고도 [#Data]가 포괄하는 범위가 넓어집니다. 바로 이 속성이 보고서 템플릿에서 테이블을 쓸 가치가 있게 만들어줍니다. 합계 행은 가져오기가 만들어낸 행이 몇 개이든 그 전부를 계속 합산합니다

왕복 규율

HotXLS는 원본 수식 텍스트를 유지합니다. SUM(SalesTable[Amount])로 로드된 통합 문서는 해결된 SUM(D2:D25)가 아니라 SUM(SalesTable[Amount])로 저장됩니다. 이는 보이는 것보다 더 중요합니다. 여러분의 출력물을 Excel에서 여는 사용자는 자신이 작성한 수식을 그대로 보기를 기대하며, 해결된 주소는 스스로 유지되는 모델을 조용히 새로운 행을 더 이상 포괄하지 못하는 부서지기 쉬운 모델로 바꿔놓게 됩니다

관련된 두 가지 기능이 이 그림을 완성합니다. 헤더 없는 테이블과 테이블별 주석을 포함해 테이블 정의 자체는 데이터 유효성 검사, AutoFilter, Excel 테이블에서 설명하는 테이블 모델을 통해 왕복합니다. 그리고 많은 셀이 하나의 패턴을 공유할 때, XLSX는 이를 공유 수식으로 한 번만 저장하며, 이는 공유 수식 si 확장에서 다루는 대로 펼쳐졌다가 다시 내보내집니다. 공유 수식 안의 구조적 참조는 두 경로를 모두 거치므로 둘 다 올바르게 동작해야 하며, 실제로 그렇게 동작합니다

HotXLS는 Excel 설치나 Office 자동화 없이 Delphi와 C++Builder에서 XLS, XLSX, ODS를 읽고 쓰며, 수식을 자체 엔진에서 평가합니다. 테이블 모델, 수식 엔진, 재계산 API는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 문서화되어 있습니다