ASP.NET-【Excel】-將Excel中的資料批量載入到SQLserver資料庫

標籤：

用到了一個SqlBulkCopy的類

核心程式碼分析

代碼我還沒有測試過

                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);                    // Create Connection to Excel Workbook                    using (OleDbConnection connection =                                 new OleDbConnection(excelConnectionString))                    {                        OleDbCommand command = new OleDbCommand                                ("Select * FROM [Sheet1$]", connection);                        connection.Open();                        // Create DbDataReader to Data Worksheet                        using (DbDataReader dr = command.ExecuteReader())                        {                            // SQL Server Connection String                            string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True";                            // Bulk Copy to SQL Server                            using (SqlBulkCopy bulkCopy =                                       new SqlBulkCopy(sqlConnectionString))                            {                                bulkCopy.DestinationTableName = "Employee";                                bulkCopy.WriteToServer(dr);                                Label1.Text = "The data has been exported succefuly from Excel to SQL";                            }                        }                    }

 

ASP.NET-【Excel】-將Excel中的資料批量載入到SQLserver資料庫