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

Screenshot

Windows.Forms Screenshot

See the full code below. 

1using System;
2using System.Windows.Forms;
3using GemBox.Spreadsheet;
4using GemBox.Spreadsheet.WinFormsUtilities;
5
6namespace WinFormsUtilitiesCs
7{
8    public partial class Form1 : Form
9    {
10        public Form1()
11        {
12            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
13
14            InitializeComponent();
15        }
16
17        private void btnLoadFile_Click(object sender, EventArgs e)
18        {
19            OpenFileDialog openFileDialog = new OpenFileDialog();
20            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";
21            openFileDialog.FilterIndex = 2;
22
23            if (openFileDialog.ShowDialog() == DialogResult.OK)
24            {
25                ExcelFile ef = ExcelFile.Load(openFileDialog.FileName);
26
27                // Export Excel worksheet to DataGridView control.
28                DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, this.dataGridView1, new ExportToDataGridViewOptions() { ColumnHeaders = true });                
29            }
30        }
31
32        private void btnSave_Click(object sender, EventArgs e)
33        {
34            SaveFileDialog saveFileDialog = new SaveFileDialog();
35            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";
36            saveFileDialog.FilterIndex = 3;
37
38            if (saveFileDialog.ShowDialog() == DialogResult.OK)
39            {
40                ExcelFile ef = new ExcelFile();
41                ExcelWorksheet ws = ef.Worksheets.Add("Sheet1");
42
43                // Import DataGridView control to Excel worksheet.
44                DataGridViewConverter.ImportFromDataGridView(ws, this.dataGridView1, new ImportFromDataGridViewOptions() { ColumnHeaders = true });
45
46                ef.Save(saveFileDialog.FileName);
47            }
48        }
49    }
50}

1Imports GemBox.Spreadsheet
2Imports GemBox.Spreadsheet.WinFormsUtilities
3
4Public Class Form1
5
6    Public Sub New()
7        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
8
9        InitializeComponent()
10    End Sub
11
12    Private Sub btnLoadFile_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadFile.Click
13
14        Dim openFileDialog = New OpenFileDialog()
15        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"
16        openFileDialog.FilterIndex = 2
17
18        If (openFileDialog.ShowDialog() = DialogResult.OK) Then
19            Dim ef = ExcelFile.Load(openFileDialog.FileName)
20
21            ' Export Excel worksheet to DataGridView control.
22            DataGridViewConverter.ExportToDataGridView(ef.Worksheets.ActiveWorksheet, Me.dataGridView1, New ExportToDataGridViewOptions() With {.ColumnHeaders = True})
23        End If
24
25    End Sub
26
27    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
28
29        Dim saveFileDialog = New SaveFileDialog()
30        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"
31        saveFileDialog.FilterIndex = 3
32
33        If (saveFileDialog.ShowDialog() = DialogResult.OK) Then
34            Dim ef = New ExcelFile()
35            Dim ws = ef.Worksheets.Add("Sheet1")
36
37            ' Import DataGridView control to Excel worksheet.
38            DataGridViewConverter.ImportFromDataGridView(ws, Me.dataGridView1, New ImportFromDataGridViewOptions() With {.ColumnHeaders = True})
39
40            ef.Save(saveFileDialog.FileName)
41        End If
42
43    End Sub
44End Class

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.