我有n +个excel文件我想将excel数据插入到SQL数据表中如何使用C＃实现这一点 [英] I have n+ number of excel files I want to insert the excel data to SQL datatable how can I achieve this using C#

问题描述

我有n +个excel文件放在一个文件夹中，所有文件的格式都是一样的。我想只选择文件夹，所有文件都应该在sql表中插入记录。



我浏览了很多，但所有例子都与单个文件上传有关。 br $>


请帮助。



我尝试过的事情：

Hi,

I have n+ number of excel files which are placed in a folder, the format of all files are same. i want to select the folder only and all files should insert the records in the sql table.

I browsed a lot but all examples are related to single file upload.

Kindly assist.

What I have tried:

protected void btnImport_Click(object sender, EventArgs e)
    {
        using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["ExcelCon"].ConnectionString))
        {
            con.Open();
            OleDbCommand com = new OleDbCommand("Select * from [EmployeeInfo$]", con);
            OleDbDataReader dr = com.ExecuteReader();
            using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString))
            {
                sqlcon.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
                {
                    bulkCopy.ColumnMappings.Add("[Employee Name]", "EmpName");
                    bulkCopy.ColumnMappings.Add("Department", "Department");
                    bulkCopy.ColumnMappings.Add("Address", "Address");
                    bulkCopy.ColumnMappings.Add("Age", "Age");
                    bulkCopy.ColumnMappings.Add("Sex", "Sex");
                    bulkCopy.DestinationTableName = "Employees";
                    bulkCopy.WriteToServer(dr);
                }
            }
            dr.Close();
            dr.Dispose();
        }
        Response.Write("Upload Successfull!");
    }
}

推荐答案

，con）;
OleDbDataReader dr = com.ExecuteReader（）;
使用（SqlConnection sqlcon = new SqlConnection（ConfigurationManager.ConnectionStrings [ Sql]。ConnectionString））
{
sqlcon.Open（） ;
使用（SqlBulkCopy bulkCopy = new SqlBulkCopy（sqlcon））
{
bulkCopy.ColumnMappings.Add（ [Employee Name]， EmpName）;
bulkCopy.ColumnMappings.Add（ 部门， 部门）;
bulkCopy.ColumnMappings.Add（ Address， 地址）;
bulkCopy.ColumnMappings.Add（ 年龄， 年龄）;
bulkCopy.ColumnMappings.Add（ Sex， 性别）;
bulkCopy.DestinationTableName = 员工;
bulkCopy.WriteToServer（dr）;
}
}
dr.Close（）;
dr.Dispose（）;
}
Response.Write（ Upload Successfull！）;
}
}

", con); OleDbDataReader dr = com.ExecuteReader(); using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString)) { sqlcon.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon)) { bulkCopy.ColumnMappings.Add("[Employee Name]", "EmpName"); bulkCopy.ColumnMappings.Add("Department", "Department"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("Age", "Age"); bulkCopy.ColumnMappings.Add("Sex", "Sex"); bulkCopy.DestinationTableName = "Employees"; bulkCopy.WriteToServer(dr); } } dr.Close(); dr.Dispose(); } Response.Write("Upload Successfull!"); } }

看看我们在CP上的一个提示（欢呼adriancs）使用OLEDB读取和写入Excel文档 [ ^ ] - 它显示了如何构建动态连接字符串



因此，您可以更改所选文件夹中任何文件的连接字符串，您可以编写一个程序给



遍历文件列表（使用Directory.GetFiles（）获得）

使用文件名构建连接字符串

阅读使用OleDB的Excel文件

将文件存储在MySQL中

（循环直到列表中的所有元素完成）



是吗？

Have a look here at this tip from one of our own here on CP (cheers adriancs) Read and Write Excel Documents Using OLEDB[^] - it shows how to build the connection string dynamically

So given you can change the connection string for any of the files in the chosen folder, you could write a procedure to

loop through the list of files (obtained with Directory.GetFiles())
build the connection string using the filename
read the Excel file using OleDB
store the file in MySQL
(loop until all elements in list done)

yes ?

这篇关于我有n +个excel文件我想将excel数据插入到SQL数据表中如何使用C＃实现这一点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！