如何用C# VSTO Excel Add-in实现按箭头键时始终滚动一行？

Got it, let's figure out how to build this exact feature for your Excel Add-in! I’ve tackled similar keyboard customization tasks before, so here’s a step-by-step breakdown that’ll get you there.

Core Idea #

Excel’s default behavior only scrolls the view when your selected cell is in the last visible row. We need to override that default, so pressing the Arrow Down key triggers a one-row scroll every time—even if your cursor is in the middle of the screen.

Step-by-Step Implementation #

1. Register the Keyboard Event Listener #

First, we need to listen for when the Arrow Down key is pressed. Set this up when your Add-in initializes (usually in the Office.initialize function):

Office.initialize = function(reason) {
    // Register our custom key press handler
    Office.context.document.addHandlerAsync(
        Office.EventType.DocumentKeyPress,
        handleArrowDownKey,
        { enableEvents: true },
        function(result) {
            if (result.status === Office.AsyncResultStatus.Failed) {
                console.error("Oops, failed to register the key handler: " + result.error.message);
            }
        }
    );
};

2. Build the Custom Arrow Down Logic #

Next, create the handleArrowDownKey function to intercept the Arrow Down key, block Excel’s default action, and run our custom scroll + selection logic:

function handleArrowDownKey(eventArgs) {
    // Check if the pressed key is Arrow Down (key code 40)
    if (eventArgs.keyCode === 40) {
        // Stop Excel from doing its normal Arrow Down thing
        eventArgs.preventDefault();

        // Run all Excel operations in a batch context (required for Office JS)
        Excel.run(async (context) => {
            const activeSheet = context.workbook.activeWorksheet;
            const currentSelection = activeSheet.getSelection();
            
            // Get the current visible part of the sheet
            const visibleRange = activeSheet.getVisibleRange();
            visibleRange.load("rowIndex, rowCount");
            
            await context.sync();

            // Calculate where we want the new visible range to be (scroll down 1 row)
            const newVisibleStartRow = visibleRange.rowIndex + 1;
            const newVisibleRange = activeSheet.getRangeByIndexes(
                newVisibleStartRow,
                visibleRange.columnIndex,
                visibleRange.rowCount,
                visibleRange.columnCount
            );
            
            // Trigger the scroll to the new range
            activeSheet.scrollTo(newVisibleRange);

            // Optional: Move the selected cell down 1 row to match the scroll (just like default behavior)
            const newSelection = activeSheet.getRangeByIndexes(
                currentSelection.rowIndex + 1,
                currentSelection.columnIndex,
                currentSelection.rowCount,
                currentSelection.columnCount
            );
            newSelection.select();

            await context.sync();
        }).catch((error) => {
            console.error("Error during custom scroll: " + error.message);
            if (error instanceof OfficeExtension.Error) {
                console.error("Debug details: " + JSON.stringify(error.debugInfo));
            }
        });
    }
}

3. Key Details to Note #

• Prevent Default: The eventArgs.preventDefault() line is non-negotiable—it stops Excel from running its normal Arrow Down behavior (which only scrolls when you’re at the bottom of the visible area).
• Scroll Precision: Using worksheet.scrollTo() with a shifted visible range ensures the view moves exactly one row down every time.
• Selection Sync: The optional step to move the selection down keeps the user’s cursor aligned with the scroll, making the experience feel natural (just like the default, but extended to all rows).
• Error Handling: Wrapping everything in Excel.run() and adding catch blocks ensures your Add-in handles edge cases (like reaching the very bottom of the worksheet) without breaking.

Edge Case: Reaching the Bottom of the Worksheet #

If you want to avoid trying to scroll beyond the last row of the sheet, add this check before setting the new visible range:

// Get the last used row of the sheet
const usedRange = activeSheet.getUsedRange();
usedRange.load("rowCount");
await context.sync();

const lastPossibleStartRow = usedRange.rowCount - visibleRange.rowCount;
if (newVisibleStartRow <= lastPossibleStartRow) {
    activeSheet.scrollTo(newVisibleRange);
} else {
    // Scroll to the bottom of the sheet instead
    activeSheet.scrollTo(activeSheet.getRangeByIndexes(lastPossibleStartRow, visibleRange.columnIndex, visibleRange.rowCount, visibleRange.columnCount));
}

内容的提问来源于stack exchange，提问作者Tuong Phan