How to transfer data from Excel to a DataGridView in C#?

user_19195383 0
Viewed 73

I've tried numerous methods I've seen online to copy a Excel file to a DataGridView, but none worked. My main problem is that with those methods there's a problem about fully understanding how they work without a proper explanation or they just give me strange errors, like one that told me that the Excel sheet I wanted to copy was a null instance of an object. I want to specify that I can easily work on the Excel file with other functions and I get no errors at all, I also don't need any kind of manipulation on the Excel file for this, just a plain way to copy it. Btw I started using C# just 2 days ago for a stage-thing for school, that's why I don't know much.

datagridview visual-studio excel c#
2 Answers

You should convert your Excel-File to a C#-Object, like a List, DataTable, or something else that you can bind to a DataGridView.

Check out CsvHelper, this might get you started.

Then, simply bind to your Object to your DataGridView, e. g. how it was shown here.

Good Luck!

user_173750170

  1. Right-click on your project's name or References and select Manage NuGet Packages. enter image description here

  2. Under the Browse tab, search for free spire.xls and install it. enter image description here

  3. Use the code below to transfer data from Excel to DataGridView.

     private void button1_Click(object sender, EventArgs e)
 {
     Workbook workbook = new Workbook();
     workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Data.xlsx");
     Worksheet worksheet = workbook.Worksheets[0];

     //Export data in the worksheet to a DataTable 
     DataTable dt = worksheet.ExportDataTable();

     //This overload enables you to specify the range to be exported along with whether to export column names and the actual values of formulas
     //DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);

     dataGridView1.DataSource = dt;
 }
user_61710930
Related