如何使用ASP.NET页面中的存储过程将xlsx 2016上传到MSSQL 2017？ [英] How to upload xlsx 2016 into MSSQL 2017 using stored procedure in ASP.NET page?

问题描述

嗨



我有一个问题，如何将excel 2016 xlsx文件上传到mssql 2017服务器

以前我使用excel 2010和Mssql 2012.代码工作正常（下面给出）



现在我们的客户端升级Office 2016和MSSql服务器2017和VS 2017

< br $>
请给我建议



谢谢



Maideen



我的尝试：



这是Web.Config

Hi

I have an issue that how to upload excel 2016 xlsx file into mssql 2017 server
Previously I use excel 2010 and Mssql 2012. The code is working fine (Given Below)

Now our client upgrade Office 2016 and MSSql server 2017 and VS 2017

Pls Advice Me

Thank you

Maideen

What I have tried:

This is Web.Config

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

存储过程

Stored Procedure

ALTER PROCEDURE [dbo].[ZR_usp_ImportFromExcel07]
   @SheetName varchar(20),
   @FilePath varchar(100),
   @HDR varchar(3),
   @TableName varchar(50)
AS
BEGIN
    
	DECLARE @SQL nvarchar(1000)
	SET @SQL = 'TRUNCATE TABLE ' + @TABLENAME
	EXEC(@SQL)

    IF OBJECT_ID (@TableName,'U') IS NOT NULL
      SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
 
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.16.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 16.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL

另外

Also

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

推荐答案

这是一场灾难。任何使用sp_executesql的东西都会变坏。你是截断动态表吗？



只需将你的excel文件读成二进制数据并将其写成blob。

This is a total disaster. Anything that uses sp_executesql is going to be bad. You're truncating dynamic tables?

Just read your excel file as binary data and write it as a blob.

这篇关于如何使用ASP.NET页面中的存储过程将xlsx 2016上传到MSSQL 2017？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！