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


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?
Like it?
Published: January 22, 2019 | Modified: November 20, 2020 | Author: Josip Kremenić