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

GemBox.Spreadsheet.WindowsFormUtilities is a utility library that enables interoperability between GemBox.Spreadsheet and DataGridView control from a Windows Forms application.

To use it, add a reference to GemBox.Spreadsheet.WindowsFormUtilities.dll or install it with NuGet.

The utility 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, note that 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

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.

Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.

Published: January 22, 2019 | Modified: December 2, 2021 | Author: Josip Kremenić