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 GemBox.Spreadsheet;
using GemBox.Spreadsheet.WinFormsUtilities;
using System;
using System.Windows.Forms;
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 GemBox.Spreadsheet
Imports GemBox.Spreadsheet.WinFormsUtilities
Imports System
Imports System.Windows.Forms
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
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.
