officeaddin:: excel context input

 Excel JavaScript API 1.7 이상에서 지원

 

 

기본코드

// React 컴포넌트에서 사용할 경우
import { useEffect, useState } from 'react';

export function ExcelTaskPaneComponent() {
  const [selectedCellValue, setSelectedCellValue] = useState<string>('');
  const [selectedCellAddress, setSelectedCellAddress] = useState<string>('');

  useEffect(() => {
    // Office API 초기화 확인
    if (typeof Office !== 'undefined') {
      Office.onReady(() => {
        setupSelectionChangeHandler();
      });
    }
  }, []);

  const setupSelectionChangeHandler = async () => {
    try {
      await Excel.run(async (context) => {
        // 활성 워크시트 가져오기
        const worksheet = context.workbook.worksheets.getActiveWorksheet();
        
        // 선택 변경 이벤트 핸들러 등록
        worksheet.onSelectionChanged.add(handleSelectionChange);
        
        await context.sync();
        console.log('Selection change event handler registered');
      });
    } catch (error) {
      console.error('Failed to setup selection change handler:', error);
    }
  };

  const handleSelectionChange = async (event: Excel.WorksheetSelectionChangedEventArgs) => {
    try {
      await Excel.run(async (context) => {
        // 선택된 범위 가져오기
        const selectedRange = context.workbook.getSelectedRange();
        
        // 선택된 범위의 주소와 값 로드
        selectedRange.load(['address', 'values']);
        
        await context.sync();
        
        // 첫 번째 셀의 값 가져오기 (단일 셀인 경우)
        const cellValue = selectedRange.values[0][0]?.toString() || '';
        
        // 상태 업데이트
        setSelectedCellAddress(selectedRange.address);
        setSelectedCellValue(cellValue);
      });
    } catch (error) {
      console.error('Error handling selection change:', error);
    }
  };

  return (
    <div style={{ padding: '20px' }}>
      <h3>Selected Cell Information</h3>
      
      <div style={{ marginBottom: '10px' }}>
        <label>Cell Address:</label>
        <input 
          type="text" 
          value={selectedCellAddress} 
          readOnly 
          style={{ marginLeft: '10px', width: '200px' }}
        />
      </div>
      
      <div style={{ marginBottom: '10px' }}>
        <label>Cell Value:</label>
        <input 
          type="text" 
          value={selectedCellValue} 
          onChange={(e) => setSelectedCellValue(e.target.value)}
          style={{ marginLeft: '10px', width: '200px' }}
        />
      </div>
      
      <button onClick={updateCellValue}>
        Update Cell Value
      </button>
    </div>
  );

  // 선택된 셀 값 업데이트 함수
  async function updateCellValue() {
    try {
      await Excel.run(async (context) => {
        const selectedRange = context.workbook.getSelectedRange();
        selectedRange.values = [[selectedCellValue]];
        
        await context.sync();
        console.log('Cell value updated');
      });
    } catch (error) {
      console.error('Error updating cell value:', error);
    }
  }
}

 

 

 

 

다중셀지원

export function AdvancedExcelTaskPane() {
  const [cellData, setCellData] = useState<{
    address: string;
    values: any[][];
    isMultiCell: boolean;
  }>({
    address: '',
    values: [],
    isMultiCell: false
  });

  useEffect(() => {
    if (typeof Office !== 'undefined') {
      Office.onReady(() => {
        setupAdvancedSelectionHandler();
      });
    }
  }, []);

  const setupAdvancedSelectionHandler = async () => {
    try {
      await Excel.run(async (context) => {
        // 모든 워크시트에서 선택 변경 감지
        const worksheets = context.workbook.worksheets;
        worksheets.onSelectionChanged.add(handleAdvancedSelectionChange);
        
        await context.sync();
        console.log('Advanced selection change handler registered');
      });
    } catch (error) {
      console.error('Failed to setup advanced selection handler:', error);
    }
  };

  const handleAdvancedSelectionChange = async (event: Excel.WorksheetSelectionChangedEventArgs) => {
    try {
      await Excel.run(async (context) => {
        const selectedRange = context.workbook.getSelectedRange();
        selectedRange.load(['address', 'values', 'rowCount', 'columnCount']);
        
        await context.sync();
        
        const isMultiCell = selectedRange.rowCount > 1 || selectedRange.columnCount > 1;
        
        setCellData({
          address: selectedRange.address,
          values: selectedRange.values,
          isMultiCell: isMultiCell
        });
      });
    } catch (error) {
      console.error('Error in advanced selection handler:', error);
    }
  };

  return (
    <div style={{ padding: '20px' }}>
      <h3>Cell Selection Details</h3>
      
      <div style={{ marginBottom: '10px' }}>
        <strong>Selected Range:</strong> {cellData.address}
      </div>
      
      {cellData.isMultiCell ? (
        <div>
          <strong>Multiple cells selected:</strong>
          <table border={1} style={{ marginTop: '10px' }}>
            <tbody>
              {cellData.values.map((row, rowIndex) => (
                <tr key={rowIndex}>
                  {row.map((cell, colIndex) => (
                    <td key={colIndex} style={{ padding: '5px' }}>
                      <input 
                        type="text" 
                        value={cell?.toString() || ''} 
                        onChange={(e) => updateCellInRange(rowIndex, colIndex, e.target.value)}
                        style={{ width: '80px' }}
                      />
                    </td>
                  ))}
                </tr>
              ))}
            </tbody>
          </table>
        </div>
      ) : (
        <div>
          <label>Single Cell Value:</label>
          <input 
            type="text" 
            value={cellData.values[0]?.[0]?.toString() || ''} 
            onChange={(e) => updateSingleCell(e.target.value)}
            style={{ marginLeft: '10px', width: '200px' }}
          />
        </div>
      )}
    </div>
  );

  function updateCellInRange(row: number, col: number, value: string) {
    const newValues = [...cellData.values];
    if (!newValues[row]) newValues[row] = [];
    newValues[row][col] = value;
    
    setCellData(prev => ({
      ...prev,
      values: newValues
    }));
  }

  function updateSingleCell(value: string) {
    setCellData(prev => ({
      ...prev,
      values: [[value]]
    }));
  }
}

 

 

이벤트정리(메모리 누수방지)

 

useEffect(() => {
  let eventHandler: Excel.EventHandlerResult<Excel.WorksheetSelectionChangedEventArgs>;

  const setupHandler = async () => {
    await Excel.run(async (context) => {
      const worksheet = context.workbook.worksheets.getActiveWorksheet();
      eventHandler = worksheet.onSelectionChanged.add(handleSelectionChange);
      await context.sync();
    });
  };

  if (typeof Office !== 'undefined') {
    Office.onReady(setupHandler);
  }

  // 컴포넌트 언마운트 시 이벤트 핸들러 제거
  return () => {
    if (eventHandler) {
      Excel.run(async (context) => {
        eventHandler.remove();
        await context.sync();
      });
    }
  };
}, []);

 

_