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

The following C# and VB.NET code examples show how to use GemBox.Spreadsheet to export an Excel file to the DataGridView control and how to import the DataGridView control into an Excel file.

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|" +
            "SVG (*.svg)|*.svg";

        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|" &
            "SVG (*.svg)|*.svg"
        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
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

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, additional information is imported or exported, such as hyperlinks, formatting, and images.

See also


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