How to Export Data from Excel to DataTable with C#

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.

Detailed Steps: 

  1. 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. 
  2. Double click button to write code.
  3. Load file which I want to export data to datatable.
  4. Declare sheet as the current worksheet.
  5. Define data source is that data in worksheet and export to gridview.

Designed Form

Designed Form 

Using the code:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Spire.Xls;

namespace ExportData
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Load Excel File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\Work\My Documents\Sales Report.xlsx”);

            //Initialize worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Export Data
            this.DataGridView.DataSource = sheet.ExportDataTable();
        }
    }
}

 DOWNLOAD Spire.XLS for .NET

Advertisements

2 thoughts on “How to Export Data from Excel to DataTable with C#

  1. direct to consumer says:

    Wonderful publish, very informative. I wonder why the other experts of this sector do not realize this. You should continue your writing. I’m confident, you have a huge readers’ base already!|What’s Taking place i’m new to this, I stumbled upon this I’ve discovered It absolutely helpful and it has aided me out loads. I hope to give a contribution & help other customers like its aided me. Great job.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s