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

The following example shows how to use GemBox.Spreadsheet.WindowsFormUtilities.dll to import or export ExcelFile to the Windows.Forms DataGridView control in C# and VB.NET.

Note that not only data is imported or exported from ExcelWorksheet into a DataGridView control but also hyperlinks, formatting, images, and more.

Input file: WinFormsUtilitiesExample.xlsx

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

A Windows Forms application that imports data with GemBox.Spreadsheet — Screenshot of a Windows Forms application that imports data with GemBox.Spreadsheet

C#
VB.NET

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);

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

    private void btnSave_Click(object sender, EventArgs e)
    {
        var saveFileDialog = new SaveFileDialog();
        saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files (*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.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 = 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)

            ' From ExcelFile to DataGridView.
            DataGridViewConverter.ExportToDataGridView(workbook.Worksheets.ActiveWorksheet, 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 = New SaveFileDialog()
        saveFileDialog.Filter = "XLS files (*.xls)|*.xls|XLT files (*.xlt)|*.xlt|XLSX files (*.xlsx)|*.xlsx|XLSM files (*.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

Check next example or download examples from GitHub.