如何通过Web API获取Excel文件并导入SQL Server为可读格式？

你的当前代码使用responseText获取响应，这会导致二进制Excel文件损坏——因为responseText仅适用于文本类响应。必须改用responseBody获取二进制内容，再通过ADODB.Stream写入本地文件（需确保SQL Server服务账号拥有目标文件夹的读写权限）：

DECLARE @Object AS INT;
DECLARE @ResponseBody AS VARBINARY(MAX); -- 用VARBINARY存储二进制Excel内容
DECLARE @Body AS VARCHAR(8000) = 
'{
"StartDt": "2024-08-01",
"EndDt":"2024-08-02"
}'

-- 初始化XMLHTTP对象并发送请求
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'POST','http://10.116.34.8:8129/runreport/ad17bc11-67ea-4f3e-b210-333e78ec9e9c', 'false';
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json';
EXEC sp_OAMethod @Object, 'send', null, @body;

-- 获取二进制响应体（关键：替换responseText为responseBody）
EXEC sp_OAMethod @Object, 'responseBody', @ResponseBody OUTPUT;

-- 用ADODB.Stream写入Excel文件
DECLARE @Stream INT;
EXEC sp_OACreate 'ADODB.Stream', @Stream OUT;
EXEC sp_OAMethod @Stream, 'Open';
EXEC sp_OAMethod @Stream, 'Type', NULL, 1; -- 1代表二进制类型
EXEC sp_OAMethod @Stream, 'Write', NULL, @ResponseBody;
EXEC sp_OAMethod @Stream, 'SaveToFile', NULL, 'D:\Temp\Report.xlsx', 2; -- 2表示覆盖现有文件
EXEC sp_OAMethod @Stream, 'Close';

-- 释放所有COM对象
EXEC sp_OADestroy @Stream;
EXEC sp_OADestroy @Object;

替换D:\Temp\Report.xlsx为实际可写入的路径，若使用远程服务器文件需用UNC路径（如\\ServerName\Share\Report.xlsx）。

使用OPENROWSET直接读取Excel文件并导入到数据库表，前提是已安装Microsoft Access Database Engine（ACE驱动），且SQL Server启用了Ad Hoc Distributed Queries：

先启用Ad Hoc Distributed Queries（仅需执行一次） #

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

导入数据到新表 #

SELECT *
INTO dbo.ReportData -- 自定义新表名
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\Temp\Report.xlsx', -- HDR=YES表示Excel第一行是表头
    'SELECT * FROM [Sheet1$]' -- 替换Sheet1为实际工作表名称
);

导入数据到现有表 #

INSERT INTO dbo.ExistingReportTable (列名1, 列名2, 列名3) -- 替换为目标表的列名
SELECT 对应Excel列1, 对应Excel列2, 对应Excel列3 -- 替换为Excel中的对应列
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=D:\Temp\Report.xlsx',
    'SELECT * FROM [Sheet1$]'
);

注意事项：

• 64位SQL Server需安装64位ACE驱动，32位则安装32位驱动
• 若遇到权限问题，可在SQL Server配置管理器中，将服务启动账号改为本地系统或具备足够权限的域账号

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