无云服务环境下，基于网络共享Excel的实时数据展示方案咨询

Got it, let's break down how to solve this fully offline, on-prem only scenario—no cloud services required, just your internal network. I've helped teams implement similar setups, so here are the practical, workable approaches:

This is the simplest approach if you don't need enterprise-level scalability, and it uses only built-in Excel features.

Step 1: Set up the shared editable workbook #

• Save your Excel file to a network shared folder that all editing users have read/write access to.
• Open the file, go to Review > Share Workbook, check the box for "Allow changes by more than one user at the same time. This also allows workbook merging."
• Head to File > Options > Trust Center > Trust Center Settings > Privacy Options and uncheck "Remove personal information from file properties on save" (this prevents conflicts during multi-user edits).
• Save the file again to apply the shared settings—now multiple users can edit simultaneously.

Step 2: Configure auto-refresh on the display PC #

On the dedicated display computer, set up a macro to refresh the shared workbook at your desired interval (30-60 seconds):

1. Open the shared workbook on the display PC.
2. Press Alt + F11 to open the VBA Editor.
3. Find the workbook's ThisWorkbook module in the Project Explorer, then paste this code:

Private Sub Workbook_Open()
    ' Schedule first refresh 30 seconds after opening (adjust as needed)
    Application.OnTime Now + TimeValue("00:00:30"), "RefreshSharedData"
End Sub

Sub RefreshSharedData()
    On Error Resume Next ' Skip temporary lock conflicts
    ThisWorkbook.RefreshAll ' Pull latest edits from the shared file
    ThisWorkbook.Save ' Optional: Save refreshed state locally (not required)
    
    ' Reschedule next refresh (set to 60 seconds here)
    Application.OnTime Now + TimeValue("00:01:00"), "RefreshSharedData"
End Sub

4. Enable macros on the display PC: Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select "Enable all macros" (safe for your closed internal network).
5. Save the file as a .xlsm (macro-enabled workbook) and leave it open on the display PC—it will auto-refresh in the background.

Notes for this approach #

• Shared workbooks have minor limitations (e.g., no some advanced pivot table features, limited macro support), but this refresh macro works reliably.
• If a user is saving edits when the refresh runs, the macro will skip that cycle and try again on the next interval—delay stays within your 30-60 second window.

If you want to avoid macros entirely, Power Query is a great alternative for the display PC.

Step 1: Set up the shared workbook (same as Scheme 1) #

Get the multi-user editable workbook running on the network share first.

Step 2: Connect Power Query to the shared file on the display PC #

1. Open a new blank Excel file on the display PC.
2. Go to Data > Get Data > From File > From Workbook, then select the shared Excel file from the network path.
3. Select the worksheet(s) you want to display, then click Load To—choose "Only Create Connection" then check "Enable background refresh" and click OK.
4. Load the connection to a table on your display worksheet (this will be your live view).

Step 3: Set auto-refresh interval #

1. Right-click the data connection in the Connections pane (under Data > Connections) and select Properties.
2. Check "Refresh every X minutes" and set X to 1 (or 0.5 for 30 seconds).
3. Check "Refresh data when opening the file" to ensure it starts fresh on boot.

Notes for this approach #

• No macros needed, so it's more secure if you have strict macro policies (though your internal network is safe either way).
• Power Query handles temporary file locks gracefully—if the shared file is busy, it will retry the refresh shortly after, keeping delay minimal.

For larger teams with heavy editing traffic, this approach eliminates Excel's shared file limitations and provides more stable real-time sync.

Step 1: Deploy SQL Server Express (free, offline) #

• Download the SQL Server Express installer locally (you can get it from a physical media or internal file share—no internet needed).
• Install it on an internal server that all devices can access on the network.

Step 2: Sync shared Excel data to SQL Server #

1. Use the SQL Server Import and Export Wizard to set up a scheduled job that pulls data from the shared Excel file into a SQL Server table (set the interval to 30-60 seconds).
2. Alternatively, create a SQL Server Agent job to run the import script on a timer—this ensures the database always has the latest edits.

Step 3: Display PC connects to SQL Server #

1. On the display PC, open Excel and go to Data > Get Data > From Database > From SQL Server Database.
2. Enter your local SQL Server name and credentials, select the synced table, and load it to your display worksheet.
3. Set the auto-refresh interval in the connection properties (same as Scheme 2) to 30-60 seconds.

Notes for this approach #

• This handles high concurrency way better than Excel's native sharing—no more file lock delays or edit conflicts.
• SQL Server Express is free for up to 10GB of data, which is more than enough for most Excel datasets.

• Permissions: Ensure all editing users have read/write access to the shared folder, and the display PC has at least read access.
• Network Stability: Make sure your internal network has consistent speed—flaky connections can increase refresh delay.
• Test First: Run a small-scale test with 2-3 editors and the display PC to validate sync times before rolling out to the whole team.

内容的提问来源于stack exchange，提问作者George Kendros