如何使用ASP.NET将数据从MS-Excel导入Sql Server？

I need to import data from Excel to Sql Server using ASP.NET. How can I do this?

我需要使用ASP.NET将数据从Excel导入到SQL Server。我怎样才能做到这一点?

4 个解决方案

#1

You can use ADO.net OLEDB data source. You can fetch records as you normally do for MS Access. Have a look at the example..

您可以使用ADO.net OLEDB数据源。您可以像平常一样为MS Access获取记录。看看这个例子..

public static DataTable SelectAll()
{
    string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @"\YourExcellfile.xls;Extended Properties=""Excel 8.0;HDR=Yes"";";
    OleDbConnection oleConnection = new OleDbConnection(conString);

    OleDbCommand oleCommand = new OleDbCommand("select * from [YourSheet1$]", oleConnection);
    OleDbDataAdapter adapter = new OleDbDataAdapter(oleCommand);

    oleConnection.Open();

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    oleConnection.Close();

    return dt;
}

After the import you can pick the data from the data table and perform the insert operation using ADO.net Sql operation

导入后,您可以从数据表中选择数据并使用ADO.net Sql操作执行插入操作

#2

Have a look at

看一下

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the Database

使用Typed DataSet和TableAdapter导入Excel电子表格:构建数据库

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

使用SqlBulkCopy将Excel电子表格数据导入SQL Server数据库表

Asp.Net Excel File Import and Transfer to Sql Server

Asp.Net Excel文件导入并传输到Sql Server

#3

I assume you want your users to upload an Excel document, which then has to be imported into SQL server. If so, you can either try some third-party library to open xls file and read data on a row-by-row basis, inserting it into an appropriate table or install Excel itself on a web server (not a good idea, though) and use it as an ODBC data source.

我假设您希望您的用户上传Excel文档,然后必须将其导入SQL Server。如果是这样,您可以尝试使用某些第三方库来打开xls文件并逐行读取数据,将其插入到适当的表中或在Web服务器上安装Excel本身(虽然不是一个好主意)并将其用作ODBC数据源。

#4

Besides using an ODBC data source, you can also to ask your user to export that Excel file to CSV and to import it manually.

除了使用ODBC数据源之外,您还可以要求用户将该Excel文件导出为CSV并手动导入。

#1