JavaScript(React)에서 Excel 수식과 함수 삽입하는 방법

미리 계산된 숫자로 가득 찬 생성된 스프레드시트는 스냅샷에 불과합니다. 생성되는 순간에는 올바르게 보이지만 즉시 낡아가기 시작합니다. 그 뒤의 데이터는 계속 변하는데 그 안의 숫자는 변하지 않으며, 파일이 애플리케이션을 떠난 뒤에는 어떤 셀을 변경해도 되는지 아무도 알 수 없습니다. 대신 수식을 함께 담은 통합 문서는 살아 있는 문서로 남습니다. 입력값을 편집하면 합계가 따라옵니다.

Spire.XLS for JavaScript는 WebAssembly로 컴파일된 스프레드시트 엔진이므로 React 앱이 서버 없이 브라우저에서 통합 문서를 만들 수 있습니다. 파일은 가상 파일 시스템(VFS)을 통해 읽고 쓰며, 수식은 값과 같은 방식으로, 즉 셀의 Range 객체를 통해 작성됩니다. 달라지는 것은 속성 이름뿐입니다.

마지막 요점이 바로 핵심입니다. 흥미로운 질문은 수식을 작성하는 방법이 아니라 사용 가능한 네 가지 속성 중 어느 것으로 작성할지입니다. 그중 세 가지는 여러분의 수식을 조용히 일반 텍스트로 저장하기 때문입니다.

프로젝트 설정은 React 프로젝트에 Spire.XLS for JavaScript 통합하기를 참조하세요. 아래 예제는 패키지가 설치되어 있고 WebAssembly 모듈이 초기화되었다고 가정합니다.

생성된 통합 문서에 수식이 포함되어야 하는 이유

정답이 이미 채워진 파일을 생성하는 것이 작성하기는 더 쉽지만, 받는 입장에서는 더 나쁩니다. 실제로 문제가 되는 경우는 다음과 같습니다.

• 자리 표시자가 있는 템플릿. 받는 사람이 입력값을 교체할 것으로 예상됩니다. 합계가 하드코딩되어 있으면 입력값을 교체해도 합계는 잘못된 상태로 남고, 아무런 경고도 표시되지 않습니다.
• 분석가에게 전달되는 모델. 그들은 다른 가정을 테스트하고 싶어 할 것입니다. 다시 도출할 수 없는 시트는 다시 만들어야 하는 시트입니다.
• 추적 가능해야 하는 보고서. 뒤에 보이는 규칙이 없는 숫자는 검증할 수 없습니다. 수식은 검증할 수 있습니다.
• 다른 워크시트에 데이터를 공급하는 워크시트. 다른 셀들이 이를 참조합니다. 값이 다시 계산되지 않으면 다운스트림의 모든 것이 그 오래된 값을 물려받습니다.

네 가지 경우 모두에서 수식이 파일의 핵심입니다. 값은 부산물일 뿐입니다.

사전 요구 사항

Spire.XLS for JavaScript가 설치되어 있고 WebAssembly 모듈이 초기화되어 window.wasmModule.spirexls에서 접근 가능한 React 프로젝트가 필요합니다. 아래 샘플은 텍스트 서식을 지정하기 전에 VFS에 글꼴을 로드하고, 최신 Excel과 이전 버전 모두에서 문제없이 열리도록 Excel 2010 버전 플래그로 저장합니다.

수식을 작성하는 속성 선택

작성하는 모든 셀은 Range 객체이며, 값을 받는 네 가지 속성을 노출합니다. 이들은 서로 호환되지 않습니다.

Text는 주의해야 할 속성이며, 아래 코드를 보기 전에 그 이유를 이해할 가치가 있습니다. =SUM(B1:F1)을 Text에 할당하면 셀은 그 문자들을 저장합니다. 아무것도 이를 평가하지 않으므로 수식이 영원히 표시됩니다.

이 동작은 결함이 아닙니다. 샘플이 의도적으로 사용하는 바로 그 동작으로, 각 행이 왼쪽에 수식을, 오른쪽에 결과를 표시할 수 있게 합니다. 왼쪽 셀은 규칙을 표시하기 위한 것이므로 Text를 사용하고, 오른쪽 셀은 규칙을 적용하기 위한 것이므로 Formula를 사용합니다.

셀에 수식 작성하기

흐름은 짧습니다.

1. Workbook 객체를 생성합니다.
2. Workbook.Worksheets.get() 메서드로 워크시트를 가져옵니다.
3. 입력 데이터를 셀에 쓰고 셀 서식을 설정합니다.
4. 계산해야 하는 셀에 Range.Formula 속성을 통해 수식을 할당합니다.
5. Workbook.SaveToFile()로 통합 문서를 저장합니다.

이 예제는 입력 숫자 행이 있는 작은 시트를 만들고 그 아래에 다섯 개의 수식(산술 식, 날짜 함수, 삼각 함수, 평균, 합계)을 작성합니다.

function App() {
  const insertFormulasAndFunctions = 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 into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a Workbook object
    const workbook = new xlsModule.Workbook();

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Declare two variables: currentRow and currentFormula
    let currentRow = 1;
    let currentFormula = "";

    // Set the column width
    sheet.SetColumnWidth(1, 32);
    sheet.SetColumnWidth(2, 16);

    // Write data into cells
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Test Data";
    sheet.Range.get({ row: currentRow, column: 2 }).NumberValue = 1;
    sheet.Range.get({ row: currentRow, column: 3 }).NumberValue = 2;
    sheet.Range.get({ row: currentRow, column: 4 }).NumberValue = 3;
    sheet.Range.get({ row: currentRow, column: 5 }).NumberValue = 4;
    sheet.Range.get({ row: currentRow, column: 6 }).NumberValue = 5;
    currentRow += 2;
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Formula or Function";
    sheet.Range.get({ row: currentRow, column: 2 }).Value = "Result";

    // Set the cell formatting
    let range = sheet.Range.get({ row: currentRow, column: 1, lastRow: currentRow, lastColumn: 2 });
    range.Style.Font.FontName = "Arial";
    range.Style.KnownColor = xlsModule.ExcelColors.LightGreen;
    range.Style.FillPattern = xlsModule.ExcelPatternType.Solid;
    range.Style.Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Medium;
    range.Style.Font.IsBold = true;

    // Mathematical operation
    currentFormula = "=1/2+3*4";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Date function
    currentFormula = "=TODAY()";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Style.NumberFormat = "YYYY/MM/DD";

    // Trigonometric function
    currentFormula = "=SIN(PI()/6)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Average function
    currentFormula = "=AVERAGE(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Sum function
    currentFormula = "=SUM(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Save the workbook
    const outputFileName = 'InsertFormulasAndFunctions_output.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release resources
    workbook.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>Insert Formulas and Functions</h1>
      <button onClick={insertFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Excel 워크시트에 수식과 함수 결과 삽입

수식 앞의 서식 지정 호출에 주목하세요. Range.get()은 lastRow와 lastColumn을 받으므로, 헤더 블록을 셀 단위가 아니라 한 번의 호출로 스타일링할 수 있습니다. 수식을 작성하는 데 사용하는 동일한 객체가 스타일도 함께 갖습니다.

범주별 함수

샘플의 다섯 수식은 다섯 가지 기법이 아닙니다. 하나의 기법을 다섯 종류의 식에 적용한 것입니다.

"함수"를 위한 별도의 API는 없습니다. 함수는 곧 수식입니다. Range.Formula가 문자열을 받고 엔진이 이를 어떻게 처리할지 결정합니다. 그래서 작성할 수 있는 것들의 목록은 스프레드시트 엔진의 함수 목록만큼 방대하며, 함수마다 유지 관리할 래퍼가 없습니다.

결과 옆에 수식 텍스트 표시하기

생성된 워크시트에서 유용한 습관 중 하나는 규칙을 그 결과 옆에 보이게 유지하는 것입니다. 샘플은 수식 문자열을 A열에 리터럴 텍스트로, 평가된 값을 B열에 넣는 방식으로 이를 수행합니다.

// Column A displays the rule; column B applies it
sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

먼저 숫자 서식으로 "@"를 할당하는 것이 레이블 열이 문자열을 해석하려 들지 않게 하는 방법입니다. 즉, 아무것도 쓰기 전에 셀이 텍스트로 선언됩니다. 결과 열에는 그런 주의가 필요 없지만, 자체 표시 서식이 필요할 수 있습니다. 날짜 행은 .Style.NumberFormat = "YYYY/MM/DD"를 설정하는데, 이것이 없으면 값이 날짜가 아닌 일련 번호로 표시됩니다.

이렇게 자체 규칙을 포함한 시트는 레이블이 어떤 엔진도 건드리지 않는 일반 텍스트이므로 모든 왕복 과정을 견뎌냅니다.

범위 전체에 하나의 수식 적용하기

실제 워크시트에는 하나의 수식이 필요한 경우가 드물고, 열을 따라 내려가는 동일한 규칙이 필요합니다. 문자열을 직접 만들기 때문에 참조를 명시적으로 제어할 수 있습니다.

// One rule, many rows: the row number in the reference shifts with each cell
for (let row = 2; row <= 11; row += 1) {
  sheet.Range.get({ row: row, column: 3 }).Formula = `=A${row}*B${row}`;
}

이는 Excel에서 수식을 아래로 끌어 복사할 때 얻는 것과 동일한 상대 참조 동작을 풀어 쓴 것입니다. 규칙이 항상 하나의 고정된 입력을 가리켜야 한다면 고정하세요. 수식이 이동해도 $A$1은 변하지 않지만 A1은 변합니다.

사람들이 자주 실수하는 수식 구문

• 선행 등호. 수식 문자열에 =가 없으면 수식이 아닙니다. 텍스트로 저장되고 절대 평가되지 않습니다.
• 상대 참조와 절대 참조. A1은 이동하고 $A$1은 이동하지 않습니다. 루프에서 수식을 생성할 때는 의도적으로 선택하세요.
• 시트 간 참조. 문자열 안에 시트 이름을 지정하세요 — Sheet2!A1. 시트 이름에 공백이 있으면 따옴표로 묶으세요: 'Q1 Sales'!A1.
• 로캘별 인수 구분 기호. 문자열은 작성한 그대로 저장됩니다. 일부 로캘에서 세미콜론으로 표시되는 경우가 있으므로, 파일이 여러 로캘에서 열릴 가능성이 있다면 위에서 사용한 쉼표 구분 형식을 유지하세요.
• 휘발성 함수. TODAY()와 NOW()는 통합 문서가 재계산될 때마다 변경되므로, 나중에 다시 읽은 값이 앞서 본 값과 일치하지 않습니다. 규칙과 마지막 계산 값 사이의 이 간극은 그 자체로 알아둘 가치가 있습니다. 바로 JavaScript(React)에서 Excel 수식 읽기 및 추출에서 다루는 내용입니다.

일반적인 문제

셀에 결과 대신 수식이 표시됩니다. Formula가 아닌 Text를 통해 작성된 것입니다. Formula로 다시 할당하세요. 셀에는 문자가 아니라 규칙이 필요합니다.

날짜가 다섯 자리 숫자로 표시됩니다. 날짜 서식이 적용되지 않은 일련 번호 값입니다. 샘플이 TODAY() 행에 하는 것처럼 셀에 .Style.NumberFormat을 설정하세요.

서식이 의도하지 않은 셀에 적용됩니다. Range.get()에 전달한 범위를 확인하세요. lastRow와 lastColumn을 지정하면 변경 사항이 블록 전체에 적용되는데, 헤더에는 편리하지만 범위를 잘못 잡기 쉽습니다.

수식은 저장되었지만 다시 읽을 때 셀이 비어 있는 것처럼 보입니다. 결과는 통합 문서가 계산된 후에 나타납니다. 계산된 값이 파일과 함께 저장되도록 수식을 작성한 뒤 저장하세요.

자주 묻는 질문

수식을 작성하려면 Excel이나 Office가 설치되어 있어야 하나요?

아니요. 스프레드시트 엔진은 패키지에 포함되어 있으며 브라우저에서 WebAssembly로 실행됩니다. 자동화되는 것도 없고 사용자 컴퓨터에 필요한 것도 없습니다.

수식이 같은 통합 문서의 다른 워크시트를 참조할 수 있나요?

예, Excel에서 하는 것과 정확히 동일하게 작성하면 됩니다. 수식 문자열에 시트 이름을 포함하세요.

하나의 시트에서 수식과 일반 값을 섞어 쓸 수 있나요?

예, 보통 그렇게 하게 됩니다. 속성들은 서로 독립적입니다. 일부 셀은 NumberValue나 Value를 통해 데이터를 받고, 다른 셀은 Formula를 통해 규칙을 받습니다.

받는 사람이 파일을 열면 결과는 어떻게 되나요?

수식이 저장되어 있고, 통합 문서를 열 때 Excel이 다시 계산합니다. 결과가 아닌 규칙을 작성하는 이유가 바로 이것입니다. 나중에 입력값을 편집해도 파일은 올바른 상태로 유지됩니다.

수식을 작성하려면 백엔드가 필요한가요?

아니요. 통합 문서는 브라우저에서 만들어지고, 다운로드용 Blob으로 변환하는 바이트로 반환됩니다. 아무것도 업로드되지 않습니다.

참고 항목

• JavaScript(React)에서 Excel 수식 읽기 및 추출
• React 프로젝트에 Spire.XLS for JavaScript 통합하기
• JavaScript로 React에서 명명된 범위 만들기
• Spire.XLS for JavaScript 제품 페이지