通过C#将Excel数据传输到SQL [英] Transfer excel data to sql by C#

问题描述

大家好，有数据库和C#datagrid，它们的列与我的excel文件相同，我想将excel数据传输到数据库，什么是最佳方法?

Hi everyone, there are database and C# datagrid with columns same as my excel file that i want to transfer excel data to database, what''s best method?

推荐答案

您需要使用 Excel Interop [

You need to use the Excel Interop[^] assemblies to read the data from the excel spreadsheet, and then ADO.Net to save to the database.

尝试此操作

Try this

private void Import_Click(object sender, EventArgs e)
        {
           string excelcon = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
                SqlConnection sqlcon = new SqlConnection();
                sqlcon.ConnectionString = ConfigurationManager.AppSettings["con"];
                sqlcon.Open();
                OleDbConnection sSourceConnection = new OleDbConnection(excelcon);
                using (sSourceConnection)
                {
                    string sql = string.Format("Select [Name],[Age] FROM [{0}]", "Sheet1

" )； OleDbCommand命令= 新 OleDbCommand(sql，sSourceConnection); sSourceConnection.Open(); 使用(OleDbDataReader dr = command.ExecuteReader()) { while (dr.Read()) { 字符串查询= " ; SqlCommand cmd = 新 SqlCommand(query，sqlcon); cmd.Parameters.AddWithValue(" ，dr [ 0 ].ToString()); cmd.Parameters.AddWithValue(" ，dr [ 1 ].ToString()); cmd.ExecuteNonQuery(); } MessageBox.Show(" ); } } }

"); OleDbCommand command = new OleDbCommand(sql, sSourceConnection); sSourceConnection.Open(); using (OleDbDataReader dr = command.ExecuteReader()) { while (dr.Read()) { string query = "insert into details values (@name,@age)"; SqlCommand cmd = new SqlCommand(query, sqlcon); cmd.Parameters.AddWithValue("@name", dr[0].ToString()); cmd.Parameters.AddWithValue("@age", dr[1].ToString()); cmd.ExecuteNonQuery(); } MessageBox.Show("Done"); } } }

在app.config文件中添加连接字符串

Add connection string in app.config file

这篇关于通过C#将Excel数据传输到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！