How to get Values from Excel using getValues?

Question

I am trying to read the values of a column from an Excel file using this code:

FileStream stream = File.Open("excelfile.xlsx", FileMode.Open, FileAccess.Read);

            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            //5. Data Reader methods
            Console.WriteLine("Results: " +excelReader.ResultsCount);
            while (excelReader.Read())
            {
                object[] values = new object[excelReader.FieldCount];
                excelReader.GetValues(values);

            }

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();

Howver it trows an exception:

"System.NotSupportedException was unhandled Message="O método especificado não é suportado." Source="Excel" StackTrace: in Excel.ExcelOpenXmlReader.GetValues(Object[] values)

I was using another way to read the values, but i wanted to clean up the code a bit... This was the previous read cycle:

FileStream stream = File.Open("excelfile.xlsx", FileMode.Open, FileAccess.Read);

        //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        //...
        //4. DataSet - Create column names from first row
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        //5. Data Reader methods
        Console.WriteLine("Results: " +excelReader.ResultsCount);
        while (excelReader.Read())
        {
            Console.WriteLine("Row:"+excelReader.GetValues())
            for (int i = 0; i < excelReader.FieldCount; i++ )
            {
                Console.Write(excelReader.GetValue(i)+ "|");
            }
            Console.WriteLine("");
        }

        //6. Free resources (IExcelDataReader is IDisposable)
        excelReader.Close();

Am I doing something wrong with getValues? I have used that before to read SQL Records... Perhaps it's a limitation of the library that i am using.

http://stackoverflow.com/questions/2624333/how-do-i-read-data-from-a-spreadsheet-using-the-openxml-format-sdk — emd, May 20 '13 at 18:44
@emd Thanks those methods seem like a huge overkill. My only need is to get the values and then use the record values to put them on a json file. Also I can have XLS files not just XLSX If i could just use getValues that would be enough for me... if not ill use the getValue — Astronaut, May 20 '13 at 21:22
You can use OLEDB to get Excel values. I can provide an example if you like. — ron tornambe, May 20 '13 at 23:24
I want to use the excel file, since I can have this program run in a computer that does not have Excel installed. I can get the values already, I want to get them in a simpler way if I can with getvalues — Astronaut, May 21 '13 at 10:08

Astronaut · Accepted Answer · 2013-05-27T10:17:45.053

It is indeed an implementation issue. Hopefully enough people will find this feature useful for Ian to implement it in a future release.

this particular method of IDataReader is not currently supported by ExcelDataReader. I'll leave this issue open though so we can see if other people want it as well

--by Ian1971 developer of Excel Reader

There seems to be an implementation problem so i will use the first method.

score 1 · Answer 2 · answered Feb 12 '18 at 07:23

This code work form me: please consider:I tried ExcelDataReader V2.1.2.3 version, using later version like V3.4.0. got problem with .IsFirstRowAsColumnNames

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 Excel;
using System.IO;

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

        DataSet result;

        private void btnOpen_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Wprkbook 97-2003|*.xls| Excel Workbook|*.xlsx", ValidateNames = true })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    IExcelDataReader reader;
                    if (ofd.FilterIndex == 1)
                        reader = ExcelReaderFactory.CreateBinaryReader(fs);
                    else
                        reader = ExcelReaderFactory.CreateOpenXmlReader(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 dataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }


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

How to get Values from Excel using getValues?

2 Answers2