누군가 이 통합 문서를 몇 년 전에 만들었습니다. 데이터가 바뀌면 다시 계산되고, 합계는 이제 아무도 예측하지 못하는 방식으로 움직이며, 문서는 없습니다. 수식이 곧 문서이기 때문입니다. 숫자를 읽는 것만으로는 그 숫자가 어떻게 만들어졌는지 알 수 없습니다. 규칙을 읽어야 알 수 있습니다.
Spire.XLS for JavaScript는 스프레드시트 엔진을 WebAssembly로 컴파일하므로, React 앱에서 브라우저에서 기존 .xlsx 파일을 열고 셀을 순회하며 각 셀에 담긴 규칙을 추출할 수 있습니다. 통합 문서는 가상 파일 시스템(VFS)을 통해 이동하므로 아무것도 업로드되지 않고 백엔드도 관여하지 않습니다.
모든 셀에 두 가지 질문을 던집니다. 수식을 포함하고 있는가, 그리고 그렇다면 그 수식은 무엇을 말하는가? 첫 번째는 속성 확인이고, 두 번째는 읽기입니다. 이 글의 거의 모든 내용은 이 둘을 구분하는 데서 나옵니다.
프로젝트 설정은 React 프로젝트에서 Spire.XLS for JavaScript 통합하기를 참고하세요. 아래 예제는 패키지가 설치되어 있고 WebAssembly 모듈이 초기화되었다고 가정합니다.
숫자가 아니라 수식이 필요할 때
값이 아니라 규칙을 읽어야 하는 이유는 거의 항상 다음 중 하나입니다.
- 아무도 문서화하지 않은 모델을 인수할 때. 규칙은 그 통합 문서가 하는 일에 대해 유일하게 남은 설명입니다.
- 스프레드시트 밖으로 계산을 옮길 때. 애플리케이션 코드에서 계산을 다시 구현하려면 마지막 결과가 아니라 정확한 식을 알아야 합니다.
- 일관성을 확인할 때. 주변 행과 다른 규칙을 조용히 사용하는 한 행은 값에서는 보이지 않지만 수식에서는 분명히 드러납니다.
- 변경 요청을 작성할 때. 셀 목록과 그 안에 담긴 규칙은 비즈니스 사용자가 검토하고 수정할 수 있는 자료입니다.
- 직접 작성한 코드가 생성한 통합 문서를 검증할 때. 기록한 내용이 실제로 저장되었는지 확인하는 작업입니다. 이 짝의 쓰기 쪽은 JavaScript(React)에서 Excel 수식과 함수를 삽입하는 방법을 참고하세요.
사전 요구 사항
Spire.XLS for JavaScript가 설치되어 있고 WebAssembly 모듈이 초기화된 React 프로젝트가 필요하며, 모듈은 window.wasmModule.spirexls에서 접근할 수 있어야 합니다. 검사하려는 통합 문서는 이미 VFS에 있어야 합니다. 애플리케이션의 public 폴더에서 FetchFileToVFS로 로드하거나, 다른 곳에서 전달된 경우 바이트로 기록해 두면 됩니다.
결과에 서식(열 너비 등)을 적용할 예정이라면, 예제에서처럼 폰트도 VFS에 로드하세요.
모든 셀에 던져야 할 두 가지 질문
먼저 워크시트에 실제로 사용하는 영역을 물어보는 것부터 시작합니다.
// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;
for (const cell of usedRange.Cells) {
if (cell.HasFormula) {
// this cell holds a rule
}
}
AllocatedRange는 이 코드 조각에서 여러분을 보호해 주는 부분입니다. 사용 중인 행이 열두 개뿐인 시트에서 A1:Z1000을 순회하면 대부분의 시간을 빈 셀에 낭비하고, 나중에 그것들을 걸러내야 합니다. 시트에 할당된 영역을 물어보면 반복 횟수가 내용에 비례하게 유지되며, 이는 통합 문서가 실제로 쓰이는 순간부터 중요해집니다.
그다음 HasFormula가 무엇을 읽을 가치가 있는지 결정합니다. 이는 단순한 불리언 값으로, 셀이 수식을 포함하는지라는 단 하나의 질문에만 답합니다. 이 질문은 들리는 것보다 훨씬 좁은 범위입니다.
완전한 예제
아래 컴포넌트는 기존 통합 문서를 로드하고 사용 영역을 순회하며, 찾은 모든 수식을 새 시트에 읽기 쉬운 한 줄로 기록합니다. 즉 셀 주소와 그 안에 저장된 규칙입니다.
function App() {
const readFormulasAndFunctions = async () => {
// Get the Spire.XLS WASM module
const xlsModule = window.wasmModule?.spirexls;
// Check whether the module is ready
if (!xlsModule) {
alert('Spire.Xls is not ready yet');
return;
}
// Load the font and Excel file into the VFS
await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
const inputFileName = 'FormulasAndFunctions.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
// Create a Workbook object
const workbook = new xlsModule.Workbook();
// Load the Excel workbook
workbook.LoadFromFile({ fileName: inputFileName });
// Get the first worksheet
const sheet = workbook.Worksheets.get(0);
// Get the used cell range of the worksheet
const usedRange = sheet.AllocatedRange;
// Create an output workbook
const output = new xlsModule.Workbook();
const outSheet = output.Worksheets.get(0);
let outRow = 1;
// Loop through the used cells
for (const cell of usedRange.Cells) {
// Check whether the cell contains a formula or function
if (cell.HasFormula) {
// Get the cell name
const cellname = cell.RangeAddressLocal;
// Get the formula or function in the cell
const formula = cell.Formula;
// Write the cell name and formula that were read
outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
outRow += 1;
}
}
// Set the output column width so the text displays completely
outSheet.SetColumnWidth(1, 45);
// Save the output workbook
const outputFileName = 'ReadFormulasAndFunctions_output.xlsx';
output.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });
// Release resources
output.Dispose();
// Read the converted file from the VFS and trigger a download
const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
a.click();
URL.revokeObjectURL(url);
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Read Formulas and Functions</h1>
<button onClick={readFormulasAndFunctions}>
Start
</button>
</div>
);
}
export default App;
Excel 워크시트에서 수식과 함수 결과 읽기
코드가 원본 통합 문서에 대해 하는 일에 주목하세요. 읽기만 하고 그 외에는 아무것도 하지 않습니다. 출력을 위해 두 번째 Workbook이 생성되므로 검사 대상 파일은 절대 수정되지 않습니다. 이는 다른 사람의 문서를 검사할 때 중요한데, 검사는 저장하지 않기로 기억하는 것이 아니라 구조적으로 비파괴적이어야 합니다.
수식 또는 값
여기서 HasFormula를 좁게 해석하는 것이 이득을 봅니다. 셀에서 읽을 수 있는 속성들이 모두 같은 것을 반환하지는 않기 때문입니다.
| 속성 | 반환되는 값 | 사용 시기 |
|---|---|---|
HasFormula |
셀이 수식을 포함하는지 여부 | 아무것도 읽기 전에 범위를 선별할 때 |
Formula |
저장된 그대로의 수식 문자열 — =SUM(B1:F1)
|
규칙이 필요할 때 |
FormulaNumberValue |
해당 수식을 평가한 숫자 결과 | 규칙이 만들어낸 숫자가 필요할 때 |
NumberValue |
데이터 셀에 담긴 숫자 | 셀이 규칙이 아니라 데이터일 때 |
Text |
셀에 입력된 그대로의 텍스트 | 표시 문자열이 필요할 때 |
가장 많은 혼란을 일으키는 짝은 Formula와 FormulaNumberValue입니다. 같은 셀이지만 완전히 다른 두 가지 답입니다. 하나는 규칙이고, 다른 하나는 그 규칙이 만들어낸 결과입니다. 잘못된 것을 요청하면 기술적으로는 유효하지만 찾고 있던 것이 아닌 값을 얻게 됩니다. 숫자를 반환하는 수식 감사, 또는 수식을 반환하는 값 추출처럼요.
수식 목록 구성하기
예제는 찾은 항목을 각각 두 번째 통합 문서에 기록한 뒤 다운로드합니다. 목록 자체가 문서일 때 — 검토자에게 건네주거나 티켓에 첨부할 때 — 알맞은 형태입니다.
목록을 화면에 표시하려는 경우에는 같은 데이터를 먼저 수집한 뒤 표시 방법을 나중에 결정하세요.
// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
if (cell.HasFormula) {
inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
}
}
RangeAddressLocal이 결과를 쓸모 있게 만듭니다. 행과 열의 쌍(기술적으로는 동일하지만 실제로는 읽기 어려운) 대신 시트 자체 표기법으로 주소를 반환합니다. 즉 사람이 그 셀을 이야기할 때 쓰는 이름입니다. B7이라고 적힌 항목은 바로 활용할 수 있지만, 7행 2열이라고 적힌 항목은 먼저 변환해야 합니다.
워크시트가 둘 이상일 때
위의 반복문은 시트 하나만 다룹니다. 통합 문서 전체 목록을 만들려면 각 워크시트에 대해 차례대로 반복해야 하며, 첫 번째 시트를 가져온 것과 같은 방식으로 각 시트를 가져오고 Workbook.Worksheets.get(i)에 인덱스를 전달합니다.
규모를 키우기 전에 제대로 해 두어야 할 두 가지 세부 사항이 있습니다. 각 항목이 어느 워크시트에서 왔는지 기록하세요. 두 시트의 B7은 서로 다른 셀이며, 이를 구분하지 않는 목록은 정확히 중요한 순간에 모호해집니다. 또한 출력 열 너비를 충분히 넓게 유지하세요. 주소와 규칙 문자열은 길고, 잘린 목록은 좁은 목록보다 더 나쁩니다.
수식 셀이 감지되지 않을 수 있는 이유
=SUM(B1:F1)을 표시하는 셀이 반드시 수식을 담고 있는 것은 아닙니다. Formula 대신 Text나 Value를 통해 기록되었거나, 이미 텍스트로 서식이 지정된 셀에 입력되었다면 그 문자들은 문자열로 저장됩니다. 시트에는 수식이 표시되지만 셀에는 레이블이 들어 있는 것입니다.
HasFormula는 이를 false로 정확히 보고하므로, 해당 셀을 찾으리라 기대한 스캔 결과는 비어 있게 됩니다. 이것이 이 워크플로의 함정인데, 실패처럼 보이지 않기 때문입니다. 통합 문서에는 분명히 수식이 보이고, 코드는 오류 없이 실행되며, 목록은 텍스트로 입력된 셀 수만큼 부족할 뿐입니다.
목록에서 수식이 빠진 것처럼 보이면, 읽기 코드를 확인하기 전에 그 수식이 어떻게 기록되었는지 확인하세요. 통합 문서가 여러분의 애플리케이션에서 생성된 것이라면, 이는 수식 삽입에서 쓰기 관점으로 다루는 것과 동일한 속성 구분입니다.
일반적인 문제
스캔 결과가 아무것도 없는데 시트는 수식으로 가득 차 있습니다.
수식이 텍스트로 저장되어 있습니다. 위 섹션을 참고하세요. HasFormula는 실제 수식만 보고합니다.
수식을 원했는데 숫자가 나오거나 그 반대입니다.
잘못된 속성을 읽었습니다. Formula는 규칙을, FormulaNumberValue는 계산된 숫자를 반환합니다.
반복문이 느리거나 빈 항목이 수백 개 생깁니다.
시트의 할당된 영역 대신 고정된 사각형 범위를 순회하고 있습니다. 반복 대상으로 AllocatedRange를 사용하세요.
두 번째 시트의 셀이 누락되었습니다. 반복문이 워크시트 하나에서만 실행됩니다. 각 워크시트마다 반복하고, 각 항목에 시트 정보를 함께 기록하세요.
실행 후 원본 통합 문서가 변경되었습니다.
변경되지 않아야 합니다. 예제는 한 통합 문서를 읽고 다른 통합 문서에 씁니다. 위 코드처럼 출력이 다른 Workbook 객체에 저장되는지 확인하세요.
자주 묻는 질문
통합 문서에서 수식을 읽으려면 Excel이 설치되어 있어야 하나요?
아니요. 엔진은 패키지에 포함되어 있으며 브라우저 내에서 WebAssembly로 실행됩니다. 원래의 스프레드시트 애플리케이션은 어느 시점에도 관여하지 않습니다.
수식 대신 계산된 값을 읽을 수 있나요?
네. 같은 셀에서 Formula 대신 FormulaNumberValue를 읽으세요. 먼저 HasFormula를 사용해 의미가 있는 셀에만 그 질문을 던지세요.
통합 문서를 읽으면 파일이 수정되나요?
읽기는 수정하지 않습니다. 예제는 입력 파일을 열고 결과를 위한 별도의 출력 통합 문서를 만든 뒤 그것만 저장하므로, 검사 대상 파일은 원래대로 남습니다.
어떤 Excel 형식을 읽을 수 있나요?
레거시 .xls 형식과 최신 .xlsx 파일 모두 같은 API에서 지원되므로, 통합 문서를 검사하기 전에 변환할 필요가 없습니다.
서버에 저장된 통합 문서에도 사용할 수 있나요?
네, 바이트를 브라우저로 가져올 수 있다면 가능합니다. VFS에 기록하고 그곳에서 로드하세요. 읽기 자체는 전적으로 클라이언트 측에서 이루어지며, 통합 문서는 여러분의 애플리케이션이 업로드하기로 선택한 경우에만 업로드됩니다.