如何在C#中实现Excel刷新Web查询时请求用户凭据

AI 小北

2026-7-28

在OpenXML生成的Excel Web查询中添加用户认证功能

你可以通过配置WebQueryProperties的关键属性，实现Excel刷新时弹出用户名密码输入框的需求，具体修改如下：

修改Web查询配置

在你的WebQueryProperties实例中添加以下核心属性：

CredentialsMethod：设置为3，对应Excel的「提示输入凭据」模式
PromptCredentials：设为true，强制Excel在刷新时触发认证窗口
可选SavePassword：若允许用户保存密码则设为true，若需每次刷新都提示则设为false

修改后的完整代码片段

ConnectionsPart connPart = workbook.WorkbookPart.AddNewPart<ConnectionsPart>();
connPart.Connections = new Connections();
var connection1 = new Connection()
{
    Id = 1,
    Name = "Connection1",
    Type = 4, // Web Query
    WebQueryProperties = new WebQueryProperties
    {
        Url = "myurlfordatarefresh",
        CredentialsMethod = 3, // 指定提示用户输入凭据
        PromptCredentials = true,
        SavePassword = false // 不保存密码，每次刷新都触发提示
    }
};

connPart.Connections.Append(connection1);
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "mysheet" };
QueryTablePart qt = sheetPart.AddNewPart<QueryTablePart>();

qt.QueryTable = new QueryTable() { Name = "Connection", ConnectionId = connection1.Id, RefreshOnLoad = true };

DefinedNames definedNames = new DefinedNames();
definedNames.Append(new DefinedName()
{
    Name = "Connection",
    Text = "mysheet!$A$2:$A$2"
});