C# | How to display Excel sheets on DataGrindView?

Question

I'm trying to display the Excel sheet on DataGrindView. I found a solution on internet but I'm getting an error when I tried to browse the Excel file. Would anyone help me on this topic?

Browse button codes are stated below:

private void btnBrowse_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
            {
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    txtFilename.Text = openFileDialog.FileName;
                    using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                    {
                        using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                            });
                            tableCollection = result.Tables;
                            cboxSheet.Items.Clear();
                            foreach (DataTable table in tableCollection)
                                cboxSheet.Items.Add(table.TableName);//add sheet to combobox
                        }
                    }
                }
            }
        }

EDIT:

Hello again,

This's my first time posting something on Stackoverflow so sorry for my mistakes.

I'm going to explain more detailed about the project and the error.

This's my Form Application format

And here's the Form code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using ExcelDataReader;
using System.Data.OleDb;

namespace emailsender
{
    public partial class Form2 : Form
    {

        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {

        }



        private void cboxSheet_SelectedIndexChanged(object sender, EventArgs e)
        {

            dataGridView1.DataMember = cboxSheet.SelectedItem.ToString();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
            {
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    txtFilename.Text = openFileDialog.FileName;
                    using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                    {
                        using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                            });
                            //tableCollection = result.Tables;
                            cboxSheet.Items.Clear();
                            foreach (DataTable table in result.Tables)
                                cboxSheet.Items.Add(table.TableName);//add sheet to combobox
                            dataGridView1.DataSource = result;
                            cboxSheet.SelectedIndex = 0;
                        }
                    } 
                }
            }
        }
    }
}

I tried @JohnG's sample but when I select the file with the file explorer and press the open button I get the same error.

System.NotSupportedException: 'No data is available for encoding 1252. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.

I cannot quite grasp the point I missed...

Answer 1

Your question is somewhat unclear... The title states you want to ”display Excel sheets on DataGridView” … and this is certainly doable, however, I do not see anywhere in the code that attempts to set a DataGridView to one of the Excel worksheets. The posted code does indeed successfully read a selected Excel file into a DataSet. Then the code adds each worksheet name as an item into a combo box cboxSheet.

This all appears to work. So given your question, I will assume there is a DataGridView on the same Form that contains the ComboBox filled by the posted code. I assume you want the user to be able to select a particular worksheet from the combo box, then have that worksheet displayed in the DataGridView. If this is what you want, then fortunately, you have most of the hard work done… i.e., creating a DataSet where each DataTable in the DataSet is one of the worksheets from the selected Excel file.

Assuming there is a DataGridView on the Form, the code needs to set it’s DataSource after the code has read the Excel file. Fortunately, the code is creating a DataSet called result that we can use as a DataSource to the grid. Something like…

dataGridView1.DataSource = result;

This will set the grid’s DataSource as a DataSet. In order to display a particular DataTable from the DataSet, the code needs to set the grids DataMember property to the name of the DataTable we want to display... and fortunately, we have all those names conveniently stored in the combo box.

Therefore, after the code sets the grid’s DataSource, we could also set the combo box selection to the first table in the data set and then set the grid’s DisplayMamber to display the selected worksheet in the gird… OR… since we know we want the grid to display a different table when the user changes a selection in the combo box, we are going to have to “wire up/subscribe" to the ComboBoxes SelectedIndexChanged event anyway.

So, after the grids data source is set, if we simply set the combo boxes selected item to the first item in the combo box, the SelectedIndexChanged event should take care of the rest. The combo boxes SelectedIndexChanged event would only need one line of code to change the grids displayed table. It may look something like…

private void cboxSheet_SelectedIndexChanged(object sender, EventArgs e) {
  dataGridView1.DataMember = cboxSheet.SelectedItem.ToString();
}

With the combo boxes SelectedIndexChanged event wired up, then your current code only needs to add the two lines as shown below. Note I removed the tableCollection as it is not needed.

private void ReadExcelFileFillCombo() {
  using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" }) {
    if (openFileDialog.ShowDialog() == DialogResult.OK) {
      txtFilename.Text = openFileDialog.FileName;
      using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read)) {
        using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) {
          DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration() {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
          });
          //tableCollection = result.Tables;
          cboxSheet.Items.Clear();
          foreach (DataTable table in result.Tables)
            cboxSheet.Items.Add(table.TableName);//add sheet to combobox
          dataGridView1.DataSource = result;
          cboxSheet.SelectedIndex = 0;
        }
      }
    }
  }
}

I hope this makes sense.

Edit per OP update and comments

I can only guess the reason you are getting the error you are showing is because you are “creating” the wrong “type” of application. I will assume that your current application was created using the .Net “Core” application. From my understanding, the .Net Core uses a different “encoding” which is causing the error you see. I suggest you take a look at the SO question….

System.NotSupportedException: No data is available for encoding 1252

If you want to use a .Net Core application then you may want to follow the suggestions in the above answer. However, if you create a “.Net Framework” instead of a “.Net” application, I am confident this will solve the problem. Below is a picture of the proper application you need to get the code to work. Note… Red is not what you want, the green… “.Net Framework” is what you want to use.