Visual Basic .NET中将OneDrive文件转为路径供OLEDB读取的实现问题

方案1：本地OneDrive同步（推荐，改动量最小） #

该方案无需修改核心读取逻辑，仅需配置运行环境即可：

1. 在程序运行的设备上登录OneDrive账号，将共享Excel所在的文件夹设置为始终保留在此设备上
2. 直接将原代码中的本地文件路径替换为OneDrive同步后的本地固定路径即可，OneDrive会自动同步云端最新的修改内容到本地
3. 该场景下原有OLEDB读取逻辑完全复用，无需额外开发

方案2：云端直接拉取文件（无本地同步场景） #

该方案适用于无法在运行设备上登录OneDrive做本地同步的场景，调用Microsoft Graph接口拉取最新文件到本地临时目录后再用原有逻辑读取：

前置准备 #

1. 到Azure应用注册页面创建应用，获取ClientID，授予Files.Read.All接口权限
2. 提前获取目标OneDrive文件的资源ID，或者通过共享链接解析出文件路径

完整实现代码 #

Imports System.Data.OleDb
Imports System.Net.Http
Imports System.IO

' 配置参数，根据实际情况替换
Private Const ONEDRIVE_FILE_ID As String = "你的OneDrive文件ID"
Private Const ACCESS_TOKEN As String = "你的Graph接口访问令牌"
Private Const LOCAL_FILE_DIRECTORY As String = "C:\Temp\Report.xlsx" ' 本地临时文件路径
Private Const strApplicationName As String = "Excel读取工具"
Private ReadOnly strComputerName As String = Environment.MachineName
Private ReadOnly strUsername As String = Environment.UserName

Sub Main()
    ' 补全伪代码中的OneDrive文件下载逻辑
    If Not String.IsNullOrEmpty(ACCESS_TOKEN) AndAlso Not String.IsNullOrEmpty(ONEDRIVE_FILE_ID) Then
        Using httpClient As New HttpClient()
            httpClient.DefaultRequestHeaders.Authorization = New System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", ACCESS_TOKEN)
            Dim fileBytes As Byte() = httpClient.GetByteArrayAsync($"https://graph.microsoft.com/v1.0/me/drive/items/{ONEDRIVE_FILE_ID}/content").Result
            ' 确保目录存在
            Directory.CreateDirectory(Path.GetDirectoryName(LOCAL_FILE_DIRECTORY))
            File.WriteAllBytes(LOCAL_FILE_DIRECTORY, fileBytes)
        End Using
    End If

    ' 修正原连接字符串语法错误，xlsx格式请替换Extended Properties为Excel 12.0 Xml
    Dim strConnectionString As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={LOCAL_FILE_DIRECTORY};Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    Dim dt As New DataTable
    dt.TableName = "Results"
    dt.Columns.AddRange(New DataColumn() {New DataColumn("LOC_ID", GetType(String)),
                                        New DataColumn("EMAIL", GetType(String)),
                                        New DataColumn("INTERVAL", GetType(String)),
                                        New DataColumn("PASSWORD", GetType(String))
                                        })

    Dim connection As OleDbConnection = New OleDbConnection(strConnectionString)
    Dim strSQL As String = "SELECT [LOC_ID], [EMAIL], [INTERVAL], [PASSWORD] FROM [Report$] WHERE [LOC_ID] <> ''"

    Try
        connection.Open()

        Using Adp As New OleDbDataAdapter(strSQL, connection)
            Adp.Fill(dt)
        End Using
        ' 后续dt数据处理逻辑保持不变
    Catch ex As Exception
        LogMessage(ex.ToString, $"Error message in ""{strApplicationName}"" job on {strComputerName} by: {strUsername}")
        SendEmailErrorMessage(ex.ToString)
    Finally
        If connection.State = ConnectionState.Open Then
            connection.Close()
        End If
    End Try
End Sub

' 原有日志、发邮件方法保持不变
Sub LogMessage(msg As String, title As String)
    ' 原有实现
End Sub

Sub SendEmailErrorMessage(msg As String)
    ' 原有实现
End Sub

注意事项 #

• 若使用xls格式的Excel文件，将连接字符串中的Excel 12.0 Xml替换为Excel 8.0即可
• 每次执行读取前先拉取最新的云端文件覆盖本地临时文件，确保读取的是其他用户最新修改的内容
• 临时文件建议放在系统临时目录或者当前用户有权限读写的目录，避免权限报错
• 如果需要将程序修改后的内容同步回OneDrive，在本地文件修改完成后调用Graph的上传接口替换云端文件即可

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