using ExcelDataReader;
using System;
using System.IO;
using System.Windows.Forms;

namespace ExcelFastRead
{
    public partial class FrmMain : Form
    {
        public FrmMain()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "(Excel 97-03)|*.xls|(Excel 2007)|*.xlsx|ALL|*.*";
            if (DialogResult.OK != dialog.ShowDialog())
            {
                return;
            }

            string strFileName = dialog.FileName;
            if (string.IsNullOrWhiteSpace(strFileName))
            {
                return;
            }

            using (var stream = File.Open(strFileName, FileMode.Open, FileAccess.Read))
            {

                // Auto-detect format, supports:
                //  - Binary Excel files (2.0-2003 format; *.xls)
                //  - OpenXml Excel files (2007 format; *.xlsx)
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {

                    // Choose one of either 1 or 2:

                    // 1. Use the reader methods
                    do
                    {
                        while (reader.Read())
                        {
                            // reader.GetDouble(0);
                        }
                    } while (reader.NextResult());

                    ExcelDataSetConfiguration configuration = 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 = true,

                            // 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();
                            //},

                            // Gets or sets a callback to determine whether to include the 
                            // current row in the DataTable.
                            //FilterRow = (rowReader) => {
                            //    return true;
                            //},

                            // Gets or sets a callback to determine whether to include the specific
                            // column in the DataTable. Called once per column after reading the 
                            // headers.
                            //FilterColumn = (rowReader, columnIndex) => {
                            //    return true;
                            //}
                        }
                    };
                    var result = reader.AsDataSet(configuration);

                    // 2. Use the AsDataSet extension method
                    //var result = reader.AsDataSet();

                    dgvList.DataSource = result.Tables[0];

                    // The result of each spreadsheet is in result.Tables
                }
            }

        }
    }
}