Amount 열이 =[@Qty]*[@Price]를 담고 있는 Excel 테이블은 저장된 파일 어디에도 [@Qty]를 저장하지 않습니다. 통합 문서의 압축을 풀면 시트 XML의 <f> 요소가 Excel이 실제로 기록하는 것을 보여 줍니다. 긴 테이블 한정 철자 Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]]입니다. ECMA-376 Part 1 §18.17의 수식 문법에는 #This Row 키워드가 있고 @는 없기 때문입니다. 아래쪽의 요약 셀은 SUM(Lines[Amount])을 담고 있습니다. 어느 문자열도 셀 주소를 하나도 담지 않으므로, A1 참조만 파싱하는 엔진은 테이블이 어디 앉는지, 어느 행이 헤더를 나르고 수식 자체가 어느 행에 사는지 알기 전까지는 계산할 것이 없습니다
마지막 정보가 성가신 것입니다. 열 참조는 테이블당 한 번 사각형으로 바꿀 수 있습니다. 현재 행 참조는 안 됩니다. 복사되는 행마다 다른 셀을 의미합니다
구조적 참조는 친절한 이름을 붙인 범위와 뭐가 다른가?
정의된 이름은 주소를 얼려 두지만 테이블 참조는 그렇지 않습니다. DataBlock이라는 이름을 Sheet1!$A$2:$D$100에 지목하면 무언가 다시 쓰기 전까지 그 사각형에 머뭅니다. Sales[Amount]는 "Sales 테이블의 Amount 열", 즉 수식이 평가되는 시점에 테이블 범위가 어디든 그것을 의미합니다. 테이블에 행 스무 개를 더하면 합계가 그것들을 커버합니다. 조정할 참조가 없습니다. 수식이 애초에 주소를 담지 않았기 때문입니다
HotXLS는 평가를 위해 수식을 컴파일할 때마다 이 참조들을 해석합니다. TXLSXWorkbook은 통합 문서의 테이블 모델에서 이름으로 테이블을 찾고(TXLSXTable.Name 또는 DisplayName, 대소문자 무시 비교), 열을 TXLSXTable.Columns에 저장된 헤더 텍스트와 대조하고, 항목 지정자를 적용해 행 밴드를 고른 다음, 'Sales'!$D$2:$D$25 같은 절대 범위를 컴파일러에 넘깁니다. 재작성된 텍스트는 그 컴파일에서만 존재합니다. 셀은 수식을 기록된 그대로 유지하고, 룩업이 재계산 시점에 그 순간의 TXLSXTable.Range를 대상으로 돌기 때문에, 테이블이 자라기 전에 쓰인 수식도 더 큰 테이블을 대상으로 평가됩니다
사각형 문법: 열, 스팬, 항목 지정자
HotXLS가 평가하는 테이블 전체 폼은 모두 하나의 연속된 블록을 만듭니다. 받아들이는 철자는 [Col]과 괄호가 겹친 자매 [[Col]], 맨 항목 지정자 [#Data], [#All], [#Headers], [#Totals], 결합 형태 [[#Data],[Col]], [[#Data],[Col1]:[Col2]] 같은 항목 지정자 안의 스팬, 그리고 맨 스팬 [[Col1]:[Col2]]입니다. 스팬은 오른쪽에서 왼쪽으로 쓸 수 있습니다. [[Q2]:[Q1]]은 [[Q1]:[Q2]]와 같은 두 열로 해석됩니다
각 지정자가 고르는 행 밴드는 테이블의 두 플래그를 따릅니다. TXLSXTable.HasHeaderRow가 Range의 첫 행이 헤더인지를 결정하고, TXLSXTable.TotalsRowShown이 마지막 행이 합계 행인지를 결정합니다. 맨 열 참조와 [#Data]는 그 둘 사이의 행을 커버합니다. [#All]은 헤더와 합계를 포함한 Range 전체를 가져갑니다. 헤더 행 없는 테이블에 [#Headers]를, 합계 행 없는 테이블에 [#Totals]를, 본문에 행이 없는 테이블의 열을 요구하면 참조는 #REF!가 됩니다. 같은 요청에 Excel이 보여 주는 것과 일치하며, 불가능한 참조가 합이 0인 빈 범위로 조용히 변하는 일을 막습니다
괄호, 해시, 골뱅이, 어포스트로피를 담은 열 이름은 Excel의 이스케이프 규칙을 따릅니다. 그런 글자마다 참조 안에서 어포스트로피가 접두사로 붙으므로, Unit [USD]라는 헤더는 Sales[Unit '[USD']]로 기록됩니다. 단일 열과 현재 행 형태는 매칭 전에 그 이스케이프를 디코딩합니다. 스팬과 결합형 [#Data],[Col] 형태는 수식 자신의 시트 위 테이블을 대상으로 해석되므로, 다른 시트의 테이블 위로 스팬이 필요한 요약 시트는 열마다 맨 열 참조가 더 나은 선택입니다
아래 예제는 24행짜리 판매 테이블을 만들고 테이블과 같은 시트의 F2에서 F6까지 요약 수식 다섯 개를 옆에 놓습니다. 1행이 헤더 행이고 2~25행이 본문이며 합계 행은 없으므로, SalesTable[#All]은 25행에 걸치고 SalesTable[Amount]는 24행에 걸칩니다
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[2, 6].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
그 배치에서 기대되는 값입니다. F5는 25, F6은 4로 헤더 셀마다 하나씩 셉니다. F2에서 F4는 기록하는 데이터에 따릅니다
[@Column]은 어느 행을 읽어야 할지 어떻게 아나?
현재 행 참조는 평가되고 있는 셀에서 자기 행을 가져옵니다. Recalculate가 7행의 Lines[@Qty]를 담은 수식에 도달하면 HotXLS는 그것을 7행의 Qty 열로 해석하고, 8행의 같은 수식 텍스트는 8행을 읽습니다. Excel이 디스크에 기록하는 긴 철자 Lines[[#This Row],[Qty]]는 같은 참조로 취급되며, 공백을 담은 헤더에 대한 Lines[@[Unit Price]]도 마찬가지입니다
테이블 이름이 앞에 없는 비한정 형태 [@Qty]는 사실 하나가 더 필요합니다. 어느 테이블에 속하는가. HotXLS는 위치로 답합니다. 평가 중인 셀이 같은 시트 위 테이블의 본문 행과 열 스팬 안에 앉아 있으면 그 테이블이 열을 공급하고, 그런 테이블 안에서 [@[Q1]:[Q2]]는 그 열들에 걸친 한 행짜리 범위가 됩니다. 그 외의 곳에서는 참조가 해석되지 않은 채 남습니다. 수식이 현재 행 참조를 쓰는 정의된 이름은 그 이름을 가리키는 셀에서 행을 가져오는데, 인라인 수식이 얻는 것과 같은 동작입니다
현재 행 수식은 테이블 자신의 본문 행에 두고, 코드가 생성할 때는 테이블 이름으로 한정하십시오. 한정된 참조는 옮겨져도 살아남고, Excel이 저장하는 방식과 같게 읽히며, 테이블 자신의 행 위의 수식이야말로 Excel과 파일의 다른 소비자가 @의 의미에 동의하는 유일한 사례입니다
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Orders');
Cols.Add('Item');
Cols.Add('Qty');
Cols.Add('Price');
Cols.Add('Amount');
Sheet.Tables.Add('Lines', 'A1:D13', Cols);
// ... 헤더 행과 2..13행의 품목, 수량, 가격을 기록 ...
// 하나의 수식 텍스트, 열두 개의 서로 다른 결과: 각 셀이 자기 행을 읽는다
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// 테이블 아래 교차 검증: 열 합계 대 재계산한 곱
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// 기대: 모든 Amount 셀이 Qty * Price와 일치하면 D15는 0으로 평가
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
D15의 검사는 저렴하고 생성 중인 리포트에 남겨둘 가치가 있습니다. 잘못된 행을 집어 든 행별 수식은 두 합계를 어긋나게 만들고, 그 차이는 누구나 읽을 수 있는 셀에 0이 아닌 숫자로 드러납니다
테이블이 모양이 바뀌면 무슨 일이 벌어지나
성장은 정의가 실제로 자라기만 하면 쉬운 사례입니다. Range가 A1:D25인 테이블 아래 26행에 값을 기록한다고 26행이 테이블의 일부가 되지는 않습니다. 자동 확장은 누군가 테이블 옆에 입력할 때 발동하는 Excel 편집 기능이며, HotXLS를 통한 셀 기록은 그것을 작동시키지 않습니다. 임포트 뒤에 TXLSXTable.Range를 새 범위로 설정하거나, TXLSXWorksheet.InsertRows로 행을 테이블 안에 삽입하십시오. 후자는 시트의 다른 모든 참조와 함께 Tables[i].Range를 밀어냅니다. 어느 쪽이든 [#Data]는 넓어지고 수식은 하나도 바뀌지 않습니다
줄어들 때 참조가 깨질 수 있고, HotXLS는 Excel의 규칙을 따릅니다. 테이블 열에 걸친 DeleteCols는 그 열을 지목하는 모든 구조적 참조를 셀 수식, 조건부 서식, 데이터 유효성 규칙, 차트 시리즈, 정의된 이름에서 #REF!로 다시 씁니다. 헤더 행을 제거하는 DeleteRows는 테이블을 삭제하고 그 참조들을 같은 방식으로 무효화하며, 합계 행을 제거하는 것은 TotalsRowShown을 지워 그 뒤 [#Totals]가 가리킬 행이 없게 만듭니다. 평범한 A1 참조도 같은 조정을 거치는데, 삽입과 삭제에서의 수식 참조 조정에서 기술합니다
TXLSXTables.DeleteByName과 TXLSXTable.Name을 통한 이름 변경은 테이블 모델에만 작용합니다. 오래된 이름을 언급하는 수식은 자기 텍스트를 유지하므로, 코드가 테이블 이름을 바꿀 때는 같은 패스에서 그 수식들의 이름도 바꾸십시오
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]; // 통합 문서 시트는 1 기반
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... 임포트한 20행을 A26:D45에 기록 ...
Table.Range := 'A1:D45'; // 이제 본문은 2행부터 45행까지
Book.Recalculate; // F2의 SUM(SalesTable[Amount])이 이제 44행을 커버
// 테이블 본문 위의 레코드셋식 룩업, 1 기반 행 결과, 0 = 없음
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log는 여러분 자신의 루틴
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst와 FindNext는 문자열을 대소문자 무시로, 숫자를 값으로 비교하며 본문 행만 훑으므로, 헤더 텍스트 Region이 일치로 반환되는 일은 없습니다
왕복 규율
SUM(SalesTable[Amount])으로 로드된 통합 문서는 SUM(SalesTable[Amount])으로 저장되고, Lines[[#This Row],[Qty]]를 담은 셀은 그 텍스트를 온전히 지닌 채 나갑니다. 해석된 주소가 사용자가 쓴 것을 대체하는 일은 없습니다. HotXLS가 대신 SUM($D$2:$D$25)를 저장한다면 파일은 Excel에서 멀쩡히 열리다가 누군가 테이블을 확장하는 첫 순간 새 행을 커버하지 못합니다. 원인까지 거슬러 올라가기 가장 느린 종류의 파손입니다
헤더 없는 테이블과 테이블별 주석을 포함한 테이블 정의 자체는 데이터 유효성 검사, 자동 필터, Excel 테이블에서 다루는 테이블 모델을 통해 왕복합니다. 많은 셀이 하나의 수식 패턴을 공유할 때 XLSX는 그것을 공유 수식으로 한 번 저장할 수 있고, 그 셀들의 확장은 공유 수식 si 확장에서 다룹니다. Excel이 저장한 계산된 열은 흔히 그렇게 도착하므로, 테이블 본문의 구조적 참조는 평가되기 전에 두 경로를 모두 거칩니다. 공유 수식이 먼저 셀별 텍스트로 확장되고, 그다음 각 셀이 자기 행을 해석합니다
여기서 쓴 테이블 모델, Recalculate 엔진, 워크시트 삽입·삭제 호출은 HotXLS의 일부이며, HotXLS는 Excel 설치 없이 Delphi와 C++Builder에서 XLS, XLSX, ODS를 읽고 씁니다. 전체 API 레퍼런스는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다