Error reading Excel document in forms C#

Question

I'm trying to take XLSX data and putting it into an DataGridView using the ExcelDataReader NuGet package. But i get a Object reference not set to an instance of an object error on line 35

foreach (DataTable dt in result.Tables)

Here is the full code.

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using Excel;

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

        DataSet result;

        private void butOpen_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(fs);
                    reader.IsFirstRowAsColumnNames = true;
                    result = reader.AsDataSet();
                    cboSheet.Items.Clear();
                    foreach (DataTable dt in result.Tables)
                    cboSheet.Items.Add(dt.TableName);
                    reader.Close();

                }
            }

        }

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            dataGridView.DataSource = result.Tables[cboSheet.SelectedIndex];

        }
    }
}

Here is the error message: Error

You get the error because reader.AsDataSet() returns null and you're trying to access the property result.Tables. Do you have an example of a .xlsx file to test this with? — Thomas Eggemann
– Thomas Eggemann, Commented Jul 1, 2017 at 15:58
Probably your dataset result is taking null content. Try to debug and see what is the content of result. Also try to instantiate as DataSet result = new DataSet() — dim mik
– dim mik, Commented Jul 1, 2017 at 16:01
This seems to be an issue with newer .xlsx files. The IExcelDataREader object contains an error message indicating problems with the file signature. When i updated the package to the latest prerelease version (v3.0.0-develop00086) and added the package ExcelDataREader.DataSet (now required to use the AsDataSet() extension method) i was able to read a xlsx file and access the tables in the dataset. — Thomas Eggemann
– Thomas Eggemann, Commented Jul 1, 2017 at 16:38
The latest version is a prerelease. If you're willing to use a package explicitly marked as prerelease in a production environment, yes, that would be an option. Otherwise you'll either have to wait for a "finished" version of the package or find another solution that completely avoids the nuget package. — Thomas Eggemann
– Thomas Eggemann, Commented Jul 1, 2017 at 16:44

Thomas Eggemann · Accepted Answer · 2017-07-01 18:29:56Z

2

In response to the comments:

The property IsFirstRowAsColumnNames is no longer available on newer versions of the package.

From the GitHub page LINK:

AsDataSet configuration options

The AsDataSet() function accepts an optional configuration object to modify the behavior of the DataSet conversion:

var result = reader.AsDataSet(new ExcelDataSetConfiguration() {

    // Gets or sets a value indicating whether to set the DataColumn.DataType 
    // property in a second pass.
    UseColumnDataType = true,

    // Gets or sets a callback to obtain configuration options for a DataTable. 
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() {

        // Gets or sets a value indicating the prefix of generated column names.
        EmptyColumnNamePrefix = "Column",

        // Gets or sets a value indicating whether to use a row from the 
        // data as column names.
        UseHeaderRow = false,

        // Gets or sets a callback to determine which row is the header row. 
        // Only called when UseHeaderRow = true.
        ReadHeaderRow = (rowReader) => {
            // F.ex skip the first row and use the 2nd row as column headers:
            rowReader.Read();
        }
    }
});

I'm guessing that the line UseHeaderRow = true; in the ExcelDataTAbleConfiguration would result in the wanted behavior.

EDIT: Add working example

This example works for me with a new .xlsx file created in Excel 2016. The file contains two sheets: Sheet 1 and Sheet 2. Both contain two columns with two rows of text.

List<string> tblNames = new List<string>();
OpenFileDialog ofd = new OpenFileDialog();

if (ofd.ShowDialog() ?? false)
{
    using (FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(fs))
        {
            var result = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                UseColumnDataType = true,

                ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                {                                
                    UseHeaderRow = true
                }
            });

            foreach (DataTable dt in result.Tables)
                tblNames.Add(dt.TableName);
        }
    }
}

This is a WPF application, so the usage of OpenFileDialog looks a bit different.

edited Jul 1, 2017 at 18:29

answered Jul 1, 2017 at 17:06

Thomas Eggemann

865 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Adam Over a year ago

Okay, I'm now getting a invalid file signature error caused by line 31, IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(fs);

Thomas Eggemann Over a year ago

Which software (and version) did you use to create the .xlsx file?

Adam Over a year ago

Microsoft Excel 2016

Thomas Eggemann Over a year ago

Can you create a new .xlsx file with Excel Online and try it with the new file? I'm curious if the files behave differently. I'm currently not able to reproduce this.

Adam Over a year ago

Using Excel online, i downloaded a copy of the book and it crashed in the same way.

Collectives™ on Stack Overflow

Error reading Excel document in forms C#

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related