Introduction

In many time you will find yourself with some Excel file need to be saved or Export to SQL Server,I know it possible by using SQL Server Import and Export Wizard.But this depend on business Scenario itself ,so one of the scenario is creating web application or windows application that read Excel file and Move it to SQL SERVER Database,so this sample assume that you have one of those scenarios.

Sample Demo

As you can see from the above image the sample contain the page which byself contain three controls

FileUpload Control to allow you browse and upload excel file
Button to Import data from excel and export it to SQL Server
Label to show the message about uploading status

Building the Sample

Before run and test the sample you need to follow the next steps.

Note: before start reading the steps I want let you to know that I haven’t test this sample either on ASP.NET 3.5 or 32-bit MS Office,so may be you do not need the step 1 and step 2.

Create an IIS web site
Change .Net Framework to 4.0 for Application Pool of this site
Create Excel file and make sure that file contain the column name as following image

4.Create Table in SQL Server and make sure has the same Columns name with appropriate columns data type.

Description

This sample work as I mentioned above by ask user to upload Excel file (*.xsl,*.xslx) and then check the content type of that file then open excel file and save it's data to SQL Server Database.

Visual BasicC#

Edit|Remove

'if you have Excel 2007 uncomment this line of code  
        '  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
        'Define the content type
        Dim ExcelContentType As String = "application/vnd.ms-excel"
        Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        If FileUpload1.HasFile Then
            If FileUpload1.PostedFile.ContentType = ExcelContentType Or FileUpload1.PostedFile.ContentType = Excel2010ContentType Then
                Try
                    'Save file path
                    Dim path As String = String.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName)
                    'Save File as Temp then you can delete it if you want
                    FileUpload1.SaveAs(path)
                    'For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
                    Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)

                    ' Create Connection to Excel Workbook
                    Using connection As New OleDbConnection(excelConnectionString)

                        Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)

                        connection.Open()

                        'Create DbDataReader to Data Worksheet
                        Using reader As DbDataReader = Command.ExecuteReader()


                            ' SQL Server Connection String
                            Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"

                            ' Bulk Copy to SQL Server
                            Using bulkCopy As New SqlBulkCopy(sqlConnectionString)

                                bulkCopy.DestinationTableName = "Employee"
                                bulkCopy.WriteToServer(reader)
                                Label1.Text = "The data has been exported succefuly from Excel to SQL"
                            End Using
                        End Using
                    End Using
                Catch ex As Exception
                    Label1.Text = ex.Message
                End Try
            End If
        End If

// if you have Excel 2007 uncomment this line of code
        //  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
      
        string ExcelContentType = "application/vnd.ms-excel";
        string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        if (FileUpload1.HasFile)
        {
            //Check the Content Type of the file
            if(FileUpload1.PostedFile.ContentType==ExcelContentType || FileUpload1.PostedFile.ContentType==Excel2010ContentType)
            {
                try
                {
                    //Save file path
                    string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
                    //Save File as Temp then you can delete it if you want
                    FileUpload1.SaveAs(path);
                    //string path = @"C:\Users\Johnney\Desktop\ExcelData.xls";
                    //For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
                    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";
                            }
                        }
                    }
                }

                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }

'if you have Excel 2007 uncomment this line of code   
        '  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path); 
        'Define the content type 
        Dim ExcelContentType As String = "application/vnd.ms-excel" 
        Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
        If FileUpload1.HasFile Then 
            If FileUpload1.PostedFile.ContentType = ExcelContentType Or FileUpload1.PostedFile.ContentType = Excel2010ContentType Then 
                Try 
                    'Save file path 
                    Dim path As String = String.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName) 
                    'Save File as Temp then you can delete it if you want 
                    FileUpload1.SaveAs(path) 
                    'For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e 
                    Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path) 
 
                    ' Create Connection to Excel Workbook 
                    Using connection As New OleDbConnection(excelConnectionString) 
 
                        Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection) 
 
                        connection.Open() 
 
                        'Create DbDataReader to Data Worksheet 
                        Using reader As DbDataReader = Command.ExecuteReader() 
 
 
                            ' SQL Server Connection String 
                            Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True" 
 
                            ' Bulk Copy to SQL Server 
                            Using bulkCopy As New SqlBulkCopy(sqlConnectionString) 
 
                                bulkCopy.DestinationTableName = "Employee" 
                                bulkCopy.WriteToServer(reader) 
                                Label1.Text = "The data has been exported succefuly from Excel to SQL" 
                            End Using 
                        End Using 
                    End Using 
                Catch ex As Exception 
                    Label1.Text = ex.Message 
                End Try 
            End If 
        End If

Source Code Files

Default.aspx sample page written by C#
defaultVB.aspx sample page written in VB.NET
DBScript contain SQL script to create database with one table
TempFiles allow you to save the uploaded excel on it as temp file then read from this file and export it's data to sql server
Excel file contain two excel file first one excel 2007 and other is excel 2010

More Information

For more information about this topic you can ask me here