Import and Export Excel to DataGridView in C# and VB.NET

GemBox.Spreadsheet.WinFormsUtilities namespace enables interoperability between GemBox.Spreadsheet and DataGridView control from a Windows Forms application.

The namespace provides a DataGridViewConverter class which you can use to import or export an Excel file to the DataGridView control from C# and VB.NET.

Besides the cell data, some additional information is imported or exported as well, such as hyperlinks, formatting, and images.

Excel file imported to a Windows Forms application with GemBox.Spreadsheet — Screenshot of an Excel file imported to a Windows Forms application

Windows Forms application that imports data from Excel file with GemBox.Spreadsheet — Screenshot of a Windows Forms application with imported Excel data

The following example shows how you can export an Excel file into the DataGridView control using the DataGridViewConverter.ExportToDataGridView method and how to import the DataGridView control to an Excel file using the DataGridViewConverter.ImportFromDataGridView method.

C#
VB.NET

View on GitHub

using System;
using System.Windows.Forms;
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.WinFormsUtilities;

public partial class Form1 : Form
{
    public Form1()
    {
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        InitializeComponent();
    }

    private void btnLoadFile_Click(object sender, EventArgs e)
    {
        var openFileDialog = new OpenFileDialog();
        openFileDialog.Filter =
            "XLS files (*.xls, *.xlt)|*.xls;*.xlt|" +
            "XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|" +
            "ODS files (*.ods, *.ots)|*.ods;*.ots|" +
            "CSV files (*.csv, *.tsv)|*.csv;*.tsv|" +
            "HTML files (*.html, *.htm)|*.html;*.htm";

        openFileDialog.FilterIndex = 2;

        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            var workbook = ExcelFile.Load(openFileDialog.FileName);
            var worksheet = workbook.Worksheets.ActiveWorksheet;

            // From ExcelFile to DataGridView.
            DataGridViewConverter.ExportToDataGridView(
                worksheet,
                this.dataGridView1,
                new ExportToDataGridViewOptions() { ColumnHeaders = true });
        }
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        var saveFileDialog = new SaveFileDialog();
        saveFileDialog.Filter =
            "XLS (*.xls)|*.xls|" +
            "XLT (*.xlt)|*.xlt|" +
            "XLSX (*.xlsx)|*.xlsx|" +
            "XLSM (*.xlsm)|*.xlsm|" +
            "XLTX (*.xltx)|*.xltx|" +
            "XLTM (*.xltm)|*.xltm|" +
            "ODS (*.ods)|*.ods|" +
            "OTS (*.ots)|*.ots|" +
            "CSV (*.csv)|*.csv|" +
            "TSV (*.tsv)|*.tsv|" +
            "HTML (*.html)|*.html|" +
            "MHTML (.mhtml)|*.mhtml|" +
            "PDF (*.pdf)|*.pdf|" +
            "XPS (*.xps)|*.xps|" +
            "BMP (*.bmp)|*.bmp|" +
            "GIF (*.gif)|*.gif|" +
            "JPEG (*.jpg)|*.jpg|" +
            "PNG (*.png)|*.png|" +
            "TIFF (*.tif)|*.tif|" +
            "WMP (*.wdp)|*.wdp";

        saveFileDialog.FilterIndex = 3;

        if (saveFileDialog.ShowDialog() == DialogResult.OK)
        {
            var workbook = new ExcelFile();
            var worksheet = workbook.Worksheets.Add("Sheet1");

            // From DataGridView to ExcelFile.
            DataGridViewConverter.ImportFromDataGridView(
                worksheet,
                this.dataGridView1,
                new ImportFromDataGridViewOptions() { ColumnHeaders = true });

            workbook.Save(saveFileDialog.FileName);
        }
    }
}

Imports System
Imports System.Windows.Forms
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.WinFormsUtilities

Public Class Form1

    Public Sub New()
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
        InitializeComponent()
    End Sub

    Private Sub btnLoadFile_Click(sender As Object, e As EventArgs) Handles btnLoadFile.Click

        Dim openFileDialog As New OpenFileDialog()
        openFileDialog.Filter =
            "XLS files (*.xls, *.xlt)|*.xls;*.xlt|" &
            "XLSX files (*.xlsx, *.xlsm, *.xltx, *.xltm)|*.xlsx;*.xlsm;*.xltx;*.xltm|" &
            "ODS files (*.ods, *.ots)|*.ods;*.ots|" &
            "CSV files (*.csv, *.tsv)|*.csv;*.tsv|" &
            "HTML files (*.html, *.htm)|*.html;*.htm"
        openFileDialog.FilterIndex = 2

        If (openFileDialog.ShowDialog() = DialogResult.OK) Then
            Dim workbook = ExcelFile.Load(openFileDialog.FileName)
            Dim worksheet = workbook.Worksheets.ActiveWorksheet

            ' From ExcelFile to DataGridView.
            DataGridViewConverter.ExportToDataGridView(
                worksheet,
                Me.dataGridView1,
                New ExportToDataGridViewOptions() With {.ColumnHeaders = True})
        End If

    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        Dim saveFileDialog As New SaveFileDialog()
        saveFileDialog.Filter =
            "XLS (*.xls)|*.xls|" &
            "XLT (*.xlt)|*.xlt|" &
            "XLSX (*.xlsx)|*.xlsx|" &
            "XLSM (*.xlsm)|*.xlsm|" &
            "XLTX (*.xltx)|*.xltx|" &
            "XLTM (*.xltm)|*.xltm|" &
            "ODS (*.ods)|*.ods|" &
            "OTS (*.ots)|*.ots|" &
            "CSV (*.csv)|*.csv|" &
            "TSV (*.tsv)|*.tsv|" &
            "HTML (*.html)|*.html|" &
            "MHTML (.mhtml)|*.mhtml|" &
            "PDF (*.pdf)|*.pdf|" &
            "XPS (*.xps)|*.xps|" &
            "BMP (*.bmp)|*.bmp|" &
            "GIF (*.gif)|*.gif|" &
            "JPEG (*.jpg)|*.jpg|" &
            "PNG (*.png)|*.png|" &
            "TIFF (*.tif)|*.tif|" &
            "WMP (*.wdp)|*.wdp"
        saveFileDialog.FilterIndex = 3

        If (saveFileDialog.ShowDialog() = DialogResult.OK) Then
            Dim workbook = New ExcelFile()
            Dim worksheet = workbook.Worksheets.Add("Sheet1")

            ' From DataGridView to ExcelFile.
            DataGridViewConverter.ImportFromDataGridView(
                worksheet,
                Me.dataGridView1,
                New ImportFromDataGridViewOptions() With {.ColumnHeaders = True})

            workbook.Save(saveFileDialog.FileName)
        End If

    End Sub
End Class

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: January 22, 2019 | Modified: February 1, 2022 | Author: Josip Kremenić

Import and Export Excel to DataGridView in C# and VB.NET

See also

Next steps