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: WinFormsUtilitiesSample.xlsx

See the full code below.

C#
VB.NET

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

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

            InitializeComponent();
        }

        private void btnLoadFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog 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)
            {
                ExcelFile ef = ExcelFile.Load(openFileDialog.FileName);

                // Export Excel worksheet to DataGridView control.
                DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions() { ColumnHeaders = true });                
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveFileDialog 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)
            {
                ExcelFile ef = new ExcelFile();
                ExcelWorksheet ws = ef.Worksheets.Add("Sheet1");

                // Import DataGridView control to Excel worksheet.
                DataGridViewConverter.ImportFromDataGridView(ws, this.dataGridView1, new ImportFromDataGridViewOptions() { ColumnHeaders = true });

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

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 System.Object, e As System.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 ef = ExcelFile.Load(openFileDialog.FileName)

            ' Export Excel worksheet to DataGridView control.
            DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, Me.dataGridView1, New ExportToDataGridViewOptions() With {.ColumnHeaders = True})
        End If

    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.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 ef = New ExcelFile()
            Dim ws = ef.Worksheets.Add("Sheet1")

            ' Import DataGridView control to Excel worksheet.
            DataGridViewConverter.ImportFromDataGridView(ws, Me.dataGridView1, New ImportFromDataGridViewOptions() With {.ColumnHeaders = True})

            ef.Save(saveFileDialog.FileName)
        End If

    End Sub
End Class

Check next sample.