如何用C#通过服务主体读取SharePoint Online Excel并加密写入Snowflake数据库

一、前置准备 #

• 在Azure AD注册应用程序，记录客户端ID、租户ID、客户端密钥
• 给应用授予SharePoint Online应用权限（而非委托权限）：
  • 全局读取可选Sites.Read.All，更安全的方式是给服务主体分配目标文档库的直接访问权限
• 通过SharePoint管理员界面，将服务主体添加到目标文档库的权限组中，确保具备读取权限

二、服务主体认证获取AccessToken #

使用Microsoft.Identity.Client（MSAL）库实现客户端凭证流认证：

using Microsoft.Identity.Client;

public static async Task<string> GetSharePointAccessToken(string tenantId, string clientId, string clientSecret)
{
    var authority = $"https://login.microsoftonline.com/{tenantId}";
    var scopes = new[] { "https://{your-sharepoint-domain}.sharepoint.com/.default" };

    var app = ConfidentialClientApplicationBuilder
        .Create(clientId)
        .WithClientSecret(clientSecret)
        .WithAuthority(new Uri(authority))
        .Build();

    var result = await app.AcquireTokenForClient(scopes).ExecuteAsync();
    return result.AccessToken;
}

替换{your-sharepoint-domain}为你的SharePoint站点域名（例如contoso对应contoso.sharepoint.com）

三、读取SharePoint文档库中的Excel文件 #

1. 获取文件二进制内容 #

通过SharePoint REST API拉取文件流：

using System.Net.Http;
using System.Net.Http.Headers;

public static async Task<byte[]> GetExcelFileBytes(string siteUrl, string relativeFilePath, string accessToken)
{
    var fileApiUrl = $"{siteUrl}/_api/web/GetFileByServerRelativeUrl('{relativeFilePath}')/$value";
    
    using var client = new HttpClient();
    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
    
    var response = await client.GetAsync(fileApiUrl);
    response.EnsureSuccessStatusCode();
    
    return await response.Content.ReadAsByteArrayAsync();
}

relativeFilePath是文件相对于站点的路径，例如/Shared%20Documents/敏感数据.xlsx

2. 解析Excel内容（使用EPPlus） #

先安装NuGet包EPPlus，再解析文件内容：

using OfficeOpenXml;
using System.Data;
using System.IO;

public static DataTable ReadExcelData(byte[] excelBytes, string sheetName = null)
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 商用场景需切换为Commercial
    using var package = new ExcelPackage(new MemoryStream(excelBytes));
    var worksheet = string.IsNullOrEmpty(sheetName) 
        ? package.Workbook.Worksheets.First() 
        : package.Workbook.Worksheets[sheetName];
    
    var dt = new DataTable();
    // 读取表头
    foreach (var cell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
    {
        dt.Columns.Add(cell.Text);
    }
    // 读取数据行
    for (int rowNum = 2; rowNum <= worksheet.Dimension.End.Row; rowNum++)
    {
        var rowCells = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column];
        var dr = dt.NewRow();
        foreach (var cell in rowCells)
        {
            dr[cell.Start.Column - 1] = cell.Text;
        }
        dt.Rows.Add(dr);
    }
    return dt;
}

四、加密敏感列 #

以AES对称加密为例实现敏感字段加密：

using System.Security.Cryptography;
using System.Text;

public static string EncryptSensitiveData(string plainText, byte[] key, byte[] iv)
{
    using var aes = Aes.Create();
    aes.Key = key;
    aes.IV = iv;
    
    var encryptor = aes.CreateEncryptor(aes.Key, aes.IV);
    using var ms = new MemoryStream();
    using var cs = new CryptoStream(ms, encryptor, CryptoStreamMode.Write);
    using var sw = new StreamWriter(cs);
    sw.Write(plainText);
    
    return Convert.ToBase64String(ms.ToArray());
}

// 加密示例（密钥和IV需安全存储，禁止硬编码）
var aesKey = Aes.Create().Key;
var aesIV = Aes.Create().IV;
foreach (DataRow row in dataTable.Rows)
{
    row["敏感列名称"] = EncryptSensitiveData(row["敏感列名称"].ToString(), aesKey, aesIV);
}

五、写入Snowflake数据库 #

安装NuGet包Snowflake.Data.Client，实现数据写入：

using Snowflake.Data.Client;
using System.Data;

public static async Task WriteToSnowflake(DataTable dt, string snowflakeConnStr)
{
    using var conn = new SnowflakeDbConnection();
    conn.ConnectionString = snowflakeConnStr;
    await conn.OpenAsync();
    
    using var cmd = conn.CreateCommand();
    // 替换为你的目标表字段
    cmd.CommandText = "INSERT INTO YOUR_TARGET_TABLE (列1, 列2, 加密敏感列) VALUES (@列1, @列2, @加密敏感列)";
    
    foreach (DataRow row in dt.Rows)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@列1", row["列1"]);
        cmd.Parameters.AddWithValue("@列2", row["列2"]);
        cmd.Parameters.AddWithValue("@加密敏感列", row["敏感列名称"]);
        
        await cmd.ExecuteNonQueryAsync();
    }
    
    await conn.CloseAsync();
}

Snowflake连接字符串格式：account=<account-url>;user=<user>;password=<password>;db=<database>;schema=<schema>;warehouse=<warehouse>;role=<role>

六、完整流程整合 #

public static async Task Main()
{
    // 替换为你的实际配置参数
    var tenantId = "你的租户ID";
    var clientId = "你的客户端ID";
    var clientSecret = "你的客户端密钥";
    var siteUrl = "https://your-sharepoint-site.sharepoint.com";
    var relativeFilePath = "/Shared Documents/数据文件.xlsx";
    var snowflakeConnStr = "你的Snowflake连接字符串";
    
    // 1. 获取认证Token
    var accessToken = await GetSharePointAccessToken(tenantId, clientId, clientSecret);
    
    // 2. 读取Excel文件
    var excelBytes = await GetExcelFileBytes(siteUrl, relativeFilePath, accessToken);
    var dataTable = ReadExcelData(excelBytes);
    
    // 3. 加密敏感列
    var aesKey = Aes.Create().Key;
    var aesIV = Aes.Create().IV;
    foreach (DataRow row in dataTable.Rows)
    {
        row["敏感列名称"] = EncryptSensitiveData(row["敏感列名称"].ToString(), aesKey, aesIV);
    }
    
    // 4. 写入Snowflake
    await WriteToSnowflake(dataTable, snowflakeConnStr);
}

内容的提问来源于stack exchange，提问作者Bilal Shafqat