Import and Export Excel to DataGridView in C# and VB.NET
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, some additional information is imported or exported as well, such as hyperlinks, formatting, and images.


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.
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
See also
Next steps
Published: January 22, 2019 | Modified: February 1, 2022 | Author: Josip Kremenić