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

The following example shows how you can 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

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

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: January 27, 2020 | Author: Josip Kremenić