ExcelDataReader插件

参考：https://github.com/ExcelDataReader

安装

右键项目的“引用”，选择“管理NuGet包”，搜索“ExcelDataReader”。

包：ExcelDataReader和ExcelDataReader.DataSet

常用方法

• Read()从当前表中读取一行
• NextResult()将光标指向下一行
• ResultsCount返回当前工作簿中的表数
• Name返回当前表的名称
• FieldCount返回当前表中的列数
• RowCount返回当前表中的行数。这包括由 AsDataSet （） 排除的终端空行
• HeaderFooter返回带有有关头和脚的信息的对象，或者如果没有
• MergeCells返回当前表中的合并单元格范围阵列
• RowHeight返回当前行的点的视觉高度。如果行被隐藏，则可能是 0
• GetColumnWidth()返回字符单元中列的宽度。如果列被隐藏，则可能是 0
• GetFieldType()返回当前行中的值类型
• IsDBNull()检查当前行中的值是否为空
• GetValue()从当前行中返回值
• GetDouble() 将当前行的值返回到各自的类型，如GetInt32() GetBoolean() GetDateTime() GetString()
• GetNumberFormatString()返回包含格式代码的字符串，以获得当前行中值
• GetNumberFormatIndex()返回当前行中值的数字格式索引。低于 164 的索引值是指内置数字格式，否则表示自定义数字格式。
• GetCellStyle()返回包含当前行中单元格的样式信息的对象

TestCode

     private static IList<string> GetTablenames(DataTableCollection tables)
        {
            var tableList = new List<string>();
            foreach (var table in tables)
            {
                tableList.Add(table.ToString());
            }

            return tableList;
        }
        //打开文件对话框
        private void Button1Click(object sender, EventArgs e)
        {
            var result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }
        //读取文件
        private void Button2Click(object sender, EventArgs e)
        {
            var extension = Path.GetExtension(textBox1.Text).ToLower(); //返回指定的路径字符串的扩展名
            using (var stream = new FileStream(textBox1.Text, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var sw = new Stopwatch();
                sw.Start();
                IExcelDataReader reader = null;
                if (extension == ".xls")
                {
                   reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (extension == ".xlsx")
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else if (extension == ".csv")
                {
                    reader = ExcelReaderFactory.CreateCsvReader(stream);
                }

                if (reader == null)
                    return;

                var openTiming = sw.ElapsedMilliseconds; //获取当前实例测量得出的总运行时间
                // reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
                using (reader)
                {
                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        UseColumnDataType = false,
                        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = firstRowNamesCheckBox.Checked
                        }
                    });
                }

                toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";

                var tablenames = GetTablenames(ds.Tables);
                sheetCombo.DataSource = tablenames; //choose sheet下拉框绑定数据

                if (tablenames.Count > 0)
                    sheetCombo.SelectedIndex = 0;

                // dataGridView1.DataSource = ds;
                // dataGridView1.DataMember

                //得到ExcelFile文件的表Sheet
	            //var sheet = ds.Tables[Sheet];
            }
        }

        private void SelectTable()
        {
            var tablename = sheetCombo.SelectedItem.ToString();

            dataGridView1.AutoGenerateColumns = true;
            dataGridView1.DataSource = ds; // dataset
            dataGridView1.DataMember = tablename;

            // GetValues(ds, tablename);
        }

        private void SheetComboSelectedIndexChanged(object sender, EventArgs e)
        {
            SelectTable();
        }

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 }
                        });
                        DataTableCollection tableCollection = result.Tables;
                        cboSheet.Items.Clear();
                        foreach (DataTable item in tableCollection)
                        {
                            cboSheet.Items.Add(item.TableName);
                        }
                    }
                }

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//Choose one of either 1 or 2
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

//3.Reading from a CSV file
IExcelDataReader excelReader = ExcelReaderFactory.CreateCsvReader(stream);

//Choose one of either 3, 4, or 5
//4. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();

//5. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//6. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

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