We often use MS Excel to save data because it is powerful to managing data and convenient for printing. But sometimes, we need to export data from Excel to DataTable for realizing some special requirements. About DataTable, it is a virtual table to save data temporarily. It is can be applied on VB and ASP. Also, it can bind to database without coding.
How to export data to datatable? In this post, I will introduce a method about how to export data from Excel to DataTable via DataGridView by using C#.
In this example, I uses a .NET Excel component, Spire.XLS for .NET to realize this function quickly. So, I have add its dll file as reference in my project.
- This is a WinForm project. So I create a form and design it at the beginning. This form includes a label to shows what I want to do, a datagridview to save data temporarily and a Run button.
- Double click button to write code.
- Load file which I want to export data to datatable.
- Declare sheet as the current worksheet.
- Define data source is that data in worksheet and export to gridview.
Using the code:
public partial class Form1 : Form
private void button1_Click(object sender, EventArgs e)
//Load Excel File
Workbook workbook = new Workbook();
workbook.LoadFromFile(@”D:\Work\My Documents\Sales Report.xlsx”);
Worksheet sheet = workbook.Worksheets;
this.DataGridView.DataSource = sheet.ExportDataTable();