如何将多个excel文件上传到sql表 [英] how to upload multiple excel files to sql table

问题描述

我有多个excel文件位于一个文件夹中，每个文件有2张。



我想要的是选择我所有excel文件的文件夹找到并将excel sheet1内容导入第二个表格中的第一个表格和第二个表格内容，只需点击一下即可。



我是C＃的新手需要紧急求助plz。

I have multiple excel files located in a folder, each file have 2 sheets.

what i want, is to select the folder in which all my excel files are located and import excel sheet1 content to 1st table and sheet 2 content in 2nd table at one click.

I'm new to C# need urgent help plz.

推荐答案

使用SQL Server BULK INSERT：



BULK INSERT（Transact-SQL） [ ^ ]



创建存储过程（SP）;将文件名或名称传递给存储过程。从C＃代码调用SP。

Use SQL Server BULK INSERT:

BULK INSERT (Transact-SQL)[^]

Create a stored procedure (SP); pass the file name or names to the stored procedure. Call the SP from your C# code.

首先，我建议使用EPPlus来管理这些Excel文件。你可以通过NuGet获得它。在using语句中添加对OfficeOpenXml的引用。



因此，您希望一次读取这些文件，从每个工作表中获取值，添加到对象列表，并对SQL数据库运行INSERT语句。有很多方法可以做到这一点，但使用ADO.NET和动态，这是快速和肮脏的。代码示例假设您正在使用控制台应用程序和Microsoft.NET 4.6。



以下代码未经过测试，只是您可以如何执行此操作的示例。

First, I'd suggest using EPPlus for managing those Excel files. You can get it via NuGet. Add a reference to OfficeOpenXml in your using statements.

So you'll want to read in those files one at a time, get the values from each worksheet, add to a list of objects, and run an INSERT statement against your SQL database. There are many ways to do this, but this is quick and dirty using ADO.NET and dynamics. Code sample assumes you're using a console application and Microsoft.NET 4.6.

Code below is not tested, just an example of how you might do this.

private static void ProcessFiles()
{
    // Get a reference to your directory
    var directory = new DirectoryInfo(@"C:\MyFiles");

    // Get all Excel files
    foreach (var file in directory.GetFiles("*.xslx"))
    {
        // Create a new package instance from the given file
        using (var package = new ExcelPackage(file))
        {
            // Get a local reference to the worksheet
            var workSheet1 = package.Workbook.Worksheets[0];

            // Create a list to hold your objects
            var objectList = new List<dynamic>();

            // Loop over the rows
            for (var x = workSheet1.Dimension.Start.Row; x <= workSheet1.Dimension.End.Row; x++)
            {
                // This is the sample value of the entry on sheet 1, row x. You can 
                // use this to create a dynamic object to insert into your database

                dynamic objectToInsert = new ExpandoObject();
                objectToInsert.Value1 = workSheet1.Cells[x, 0].Value;
                objectToInsert.Value2 = workSheet1.Cells[x, 1].Value;
                // .. Continue as needed

                // Once you have all your values in your object, add it to the list
                objectList.Add(objectToInsert);
            }

            // Insert into your database
            using (var conn = new SqlConnection("MyConnectionString"))
            {
                foreach (var row in objectList)
                {
                    // Better idea is to use a stored procedure, but this will get the                     
                    // job done
                    using (var cmd = new SqlCommand(

INSERT INTO dbo。 MyTable（Value1，Value2）VALUES（{row.Value1}，{row.Value2}），conn））
{
cmd.ExecuteNonQuery（）;
}
}
}

// 。 。重复下一个工作表
var workSheet2 = package.Workbook.Worksheets [ 1 ];
}
}
}

"INSERT INTO dbo.MyTable (Value1, Value2) VALUES ({row.Value1}, {row.Value2})", conn)) { cmd.ExecuteNonQuery(); } } } // .. Repeat for the next worksheet var workSheet2 = package.Workbook.Worksheets[1]; } } }

这篇关于如何将多个excel文件上传到sql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！