In this article I will explain with an example, how to read (import) Excel file (sheet) data using OpenXml in Windows (WinForms) Application with C# and VB.Net.
 
 

Install DocumentFormat.OpenXml package

In order to install DocumentFormat.OpenXml library using Nuget, please refer my article Install DocumentFormat.OpenXml Nuget Package.
 
 

Form Design

The Form consists of following controls:
Button – For opening OpenFileDialog window.
OpenFileDialog – For selecting Excel file.
Button – For importing the selected file.
The Buttons have been assigned with OnClick event handler.
Read (Import) Excel file using OpenXml using C# and VB.Net
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
 
 

Reading and Importing Excel file using OpenXml in C# and VB.Net

Selecting Excel File using OpenFileDialog Box

When the Select Button is clicked, the OpenFileDialog Box is opened for selecting an Excel file.
Note: For more details on how to use OpenFileDialog, please refer my article Using OpenFileDialog in C# and VB.Net.
 

Importing the Excel file

When the Import Button is clicked, the selected Excel file is opened and read using OpenXml SpreadsheetDocument class object.
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, a nested FOR EACH loop is executed over the fetched rows and GetValue method (explained later) is called and columns are added to the DataTable.
 

GetValue

The GetValue function accepts SpreadsheetDocument and Cell class objects as a parameter where a cell value is determined and initialized.
Then, a check is performed if cell datatype NOT equal to NULL and cell value is string then, it returns the cell value.
 
Now, a connection is established with the database and the SqlBulkCopy object is initialized and the name of the Table is specified using the DestinationTableName property.
Finally, the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
Note: The mapping of columns of the DataTable and the SQL Server table is optional and you need to do only in case where your DataTable and/or the SQL Server Table do not have same number of columns or the names of columns are different.
 
C#
private void btnSelect_Click(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}
 
private void ImportExcel(object sender, EventArgs e)
{
    //Create a new DataTable.
    DataTable dt = new DataTable();
 
    //Open the Excel file in Read Mode using OpenXml.
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(openFileDialog1.FileName, false))
    {
        //Read the first Sheets from Excel file.
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
 
        //Get the Worksheet instance.
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
 
        //Fetch all the rows present in the Worksheet.
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
 
        //Loop through the Worksheet rows.
        foreach (Row row in rows)
        {
            //Use the first row to add columns to DataTable
            if (row.RowIndex.Value == 1)
            {
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Columns.Add(this.GetValue(doc, cell));
                }
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = this.GetValue(doc, cell);
                    i++;
                }
            }
        }
    }
 
    //Insert the Data read from the Excel file to Database Table.
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.Customers";
 
            //[OPTIONAL]: Map the Excel columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId");
            sqlBulkCopy.ColumnMappings.Add("Name", "Name");
            sqlBulkCopy.ColumnMappings.Add("Country", "Country");
 
            con.Open();
            sqlBulkCopy.WriteToServer(dt);
            con.Close();
        }
    }
 
}
 
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
}
 
VB.Net
Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
    openFileDialog1.ShowDialog()
End Sub
 
Private Sub ImportExcel(sender As Object, e As EventArgs) Handles btnImport.Click
    'Create a new DataTable.
    Dim dt As DataTable = New DataTable()
 
    'Open the Excel file in Read Mode using OpenXml.
    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(openFileDialog1.FileName, False)
        'Read the first Sheets from Excel file.
        Dim sheet As Sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild(Of Sheet)()
 
        'Get the Worksheet instance.
        Dim worksheet As Worksheet = TryCast(doc.WorkbookPart.GetPartById(sheet.Id.Value), WorksheetPart).Worksheet
 
       'Fetch all the rows present in the Worksheet.
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
 
       'Loop through the Worksheet rows.
        For Each row As Row In rows
           'Use the first row to add columns to DataTable
            If row.RowIndex.Value = 1 Then
                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Columns.Add(Me.GetValue(doc, cell))
                Next
            Else
               'Add rows to DataTable.
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Rows(dt.Rows.Count - 1)(i) = Me.GetValue(doc, cell)
                    i += 1
                Next
            End If
        Next
    End Using
 
   'Insert the Data read from the Excel file to Database Table.
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
 
           'Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.Customers"
 
           '[OPTIONAL]: Map the Excel columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId")
            sqlBulkCopy.ColumnMappings.Add("Name", "Name")
            sqlBulkCopy.ColumnMappings.Add("Country", "Country")
            con.Open()
            sqlBulkCopy.WriteToServer(dt)
            con.Close()
        End Using
    End Using
End Sub
 
Private Function GetValue(doc As SpreadsheetDocument, cell As Cell) As String
    Dim value As String = cell.CellValue.InnerText
    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
        Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
    End If
    Return value
End Function
 
 

Screenshots

Read (Import) Excel file using OpenXml using C# and VB.Net
 

The Excel File

Read (Import) Excel file using OpenXml using C# and VB.Net
 

Table containing the data from the Excel file

Read (Import) Excel file using OpenXml using C# and VB.Net
 
 

Downloads

Download Code