从Access OLE对象提取文件并保存到磁盘的问题求助

我太懂你这个困扰了：把SQL Server里存的Access OLE对象直接存成文件后，除了ZIP全打不开——核心原因是Access的OLE对象会给原始文件套一层OLE包装头部，你之前读取的是带包装的完整二进制，不是纯原始文件内容。下面分VBA和.NET两种方案给你落地解决方法：

一、VBA修正方案（基于你的现有代码改造） #

你的原代码用GetChunk读取了整个OLE字段，但没剥离那层OLE头部。我们需要先通过文件签名定位原始数据的起始位置，再从该点开始写入文件。

修正后的完整代码 #

Option Compare Database
Const BlockSize = 32768

Sub ExtractOLEFromSQL()
    Dim dok() As Byte
    Dim sql As String
    sql = "select top 1 idCaseDetail, idCase, Dokument from dbo_law_tbl_CaseHistory where idCaseDetail = '1'"
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(sql)
    
    If Not rst.EOF Then
        Do While Not rst.EOF
            Debug.Print "idcasehistory: " & rst.Fields(0)
            Debug.Print "idcase: " & rst.Fields(1)
            
            If Not IsNull(rst.Fields(2).Value) Then
                Dim bytesWritten As Long
                bytesWritten = WriteBLOBWithOLEStrip(rst, "Dokument", "c:\temp\extracted.doc")
                Debug.Print "写入有效字节数: " & bytesWritten
            End If
            rst.MoveNext
        Loop
    End If
End Sub

' 带OLE头部剥离的文件写入函数
Function WriteBLOBWithOLEStrip(T As DAO.Recordset, sField As String, Destination As String) As Long
    Dim fullOLEData() As Byte
    Dim rawFileStart As Long
    Dim rawFileData() As Byte
    Dim i As Long
    On Error GoTo Err_WriteBLOB
    
    ' 读取完整的OLE二进制数据
    fullOLEData = T(sField).GetChunk(0, T(sField).FieldSize())
    
    ' 找到原始文件在OLE数据中的起始位置
    rawFileStart = FindRawFileStart(fullOLEData)
    If rawFileStart = -1 Then
        MsgBox "无法识别文件类型，无法剥离OLE头"
        WriteBLOBWithOLEStrip = 0
        Exit Function
    End If
    
    ' 提取原始文件数据
    Dim rawFileLength As Long
    rawFileLength = UBound(fullOLEData) - rawFileStart + 1
    ReDim rawFileData(0 To rawFileLength - 1)
    For i = 0 To rawFileLength - 1
        rawFileData(i) = fullOLEData(rawFileStart + i)
    Next i
    
    ' 写入磁盘
    Dim DestFile As Integer
    DestFile = FreeFile
    Open Destination For Binary As DestFile
    Put DestFile, , rawFileData
    Close DestFile
    
    WriteBLOBWithOLEStrip = rawFileLength
    Exit Function
    
Err_WriteBLOB:
    WriteBLOBWithOLEStrip = -Err.Number
    MsgBox "错误: " & Err.Description
    Exit Function
End Function

' 识别常见文件签名，定位原始数据起始点
Function FindRawFileStart(oleData() As Byte) As Long
    ' 常见文件的十六进制签名转字节数组
    Dim signatures As Variant
    signatures = Array( _
        Array(&HFF, &HD8), ' JPG
        Array(&H89, &H50, &H4E, &H47), ' PNG
        Array(&HD0, &HCF, &H11, &HE0), ' DOC/XLS 旧版
        Array(&H50, &H4B, &H03, &H04), ' DOCX/XLSX/ZIP
        Array(&H25, &H50, &H44, &H46) ' PDF
    )
    
    Dim sig As Variant, i As Long, j As Long
    For Each sig In signatures
        For i = 0 To UBound(oleData) - UBound(sig)
            Dim match As Boolean
            match = True
            For j = 0 To UBound(sig)
                If oleData(i + j) <> sig(j) Then
                    match = False
                    Exit For
                End If
            Next j
            If match Then
                FindRawFileStart = i
                Exit Function
            End If
        Next i
    Next sig
    
    ' 未找到已知签名时返回-1
    FindRawFileStart = -1
End Function

核心改进点 #

1. 新增OLE头剥离逻辑：通过FindRawFileStart识别文件签名，精准定位原始数据在OLE包中的起始位置
2. 只写入有效数据：跳过Access的OLE包装头部，仅将原始文件二进制写入磁盘

二、.NET解决方案（C#示例） #

如果用.NET处理，推荐通过COM接口解析OLE复合文档，比签名匹配更可靠，能覆盖更多文件类型。

C#代码示例 #

using System;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices.ComTypes;

public class OleObjectExtractor
{
    public static void ExtractRawFileFromSql(string connectionString, string outputPath)
    {
        string sqlQuery = "SELECT TOP 1 Dokument FROM dbo_law_tbl_CaseHistory WHERE idCaseDetail = '1'";
        
        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            using (OleDbCommand cmd = new OleDbCommand(sqlQuery, conn))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read() && !reader.IsDBNull(0))
                    {
                        byte[] oleData = (byte[])reader[0];
                        byte[] rawFileData = ParseOleStorage(oleData);
                        File.WriteAllBytes(outputPath, rawFileData);
                        Console.WriteLine("文件提取成功！");
                    }
                }
            }
        }
    }
    
    private static byte[] ParseOleStorage(byte[] oleData)
    {
        using (MemoryStream ms = new MemoryStream(oleData))
        {
            IStorage storage;
            int result = StgOpenStorageOnILockBytes(
                new StreamToILockBytes(ms),
                null,
                STGM.READ | STGM.SHARE_DENY_WRITE,
                IntPtr.Zero,
                0,
                out storage);
            
            if (result != 0)
                throw new System.Runtime.InteropServices.COMException("无法解析OLE存储", result);
            
            // 读取OLE中存储的原始数据流（通常名为"CONTENTS"）
            IStream contentStream;
            storage.OpenStream("CONTENTS", IntPtr.Zero, STGM.READ | STGM.SHARE_DENY_WRITE, 0, out contentStream);
            
            using (MemoryStream rawMs = new MemoryStream())
            {
                byte[] buffer = new byte[4096];
                uint bytesRead;
                do
                {
                    contentStream.Read(buffer, (uint)buffer.Length, out bytesRead);
                    rawMs.Write(buffer, 0, (int)bytesRead);
                } while (bytesRead > 0);
                
                return rawMs.ToArray();
            }
        }
    }
    
    // 导入COM API
    [DllImport("ole32.dll")]
    private static extern int StgOpenStorageOnILockBytes(
        ILockBytes plkbyt,
        Guid? pclsid,
        STGM grfMode,
        IntPtr snbExclude,
        uint reserved,
        out IStorage ppstgOpen);
    
    private enum STGM : uint
    {
        READ = 0x00000000,
        SHARE_DENY_WRITE = 0x00000020
    }
    
    // 实现ILockBytes接口，将Stream转换为COM可识别的字节锁
    private class StreamToILockBytes : ILockBytes
    {
        private readonly Stream _stream;
        public StreamToILockBytes(Stream stream) => _stream = stream;
        
        public void ReadAt(long ulOffset, byte[] pv, int cb, out int pcbRead)
        {
            _stream.Seek(ulOffset, SeekOrigin.Begin);
            pcbRead = _stream.Read(pv, 0, cb);
        }
        
        public void WriteAt(long ulOffset, byte[] pv, int cb, out int pcbWritten)
        {
            _stream.Seek(ulOffset, SeekOrigin.Begin);
            _stream.Write(pv, 0, cb);
            pcbWritten = cb;
        }
        
        public void Flush() => _stream.Flush();
        public void LockRegion(long libOffset, long cb, int dwLockType) => throw new NotImplementedException();
        public void SetSize(long cb) => _stream.SetLength(cb);
        public void Stat(out STATSTG pstatstg, int grfStatFlag)
        {
            pstatstg = new STATSTG();
            pstatstg.cbSize = _stream.Length;
            pstatstg.type = 2; // STGTY_LOCKBYTES
        }
        public void UnlockRegion(long libOffset, long cb, int dwLockType) => throw new NotImplementedException();
    }
}

使用说明 #

1. 项目需引用System.Data.OleDb
2. SQL Server的OleDb连接字符串示例：Provider=SQLOLEDB;Data Source=你的服务器名;Initial Catalog=你的数据库;User ID=账号;Password=密码;
3. 该方法直接解析OLE复合文档结构，能处理所有Access存储的OLE对象类型，比签名匹配更稳定

为什么之前的代码失效？ #

Access存入SQL Server的OLE对象不是原始文件的纯二进制，而是包含了OLE复合文档的头部元数据（比如对象类型标识、存储信息等）。ZIP能打开是巧合——它的签名刚好出现在OLE头部之后，而图片、Word的签名被头部覆盖，导致文件结构损坏。

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