ExcelDataReader读取excel-C#

1、获取包

使用nuget搜索ExcelDATaReader和ExcelDataReader.DataSet
都需要进行安装操作
使用命名空间
using System.IO;
using ExcelDataReader;
using System.Data;

2、使用

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.Reflection;

namespace ReadExcel
{
    /// <summary>
    ///     C#读取Excel文件（.xls .xlsx）
    ///     LDH @ 2021-7-28
    ///     Nuget: ExcelDataReader ExcelDataReader.DataSet
    /// </summary>
    public partial class Form1 : Form
    {
        private DataTableCollection tableCollection;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|.xls"})
            {
                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 }
                            });
                            DataTable dt = result.Tables[0];
							// 关键就在这里去获取到excel中的单个数据
                            Value.Text = dt.Rows[1][2].ToString();
                            tableCollection = result.Tables;
                            cboSheet.Items.Clear();
                            foreach (DataTable table in tableCollection)
                            {
                                cboSheet.Items.Add(table.TableName);

                            }
                            
                        }
                    }
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];
            
            dataGridView1.DataSource = dt;
        }
    }
}

3、快速使用

using System.IO;
using ExcelDataReader;
using System.Reflection;
using System.Data;

...
	
public static DataTable getExcelData(String path, int site = 0)
        {
            DataTable dt;
            using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                {

                    DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                    });
                    dt = result.Tables[site];
                }
            }
            return dt;
        }

                /// <summary>
        /// 转换带符号浮点数
        /// </summary>
        /// <param name="s">可能会带有符号的浮点数</param>
        /// <returns>转换好的浮点数</returns>
        public static double transformDouble(string s)
        {
            if (s[0] != '-')
            {
                return double.Parse(s);
            }
            string[] ss = s.Split('-');
            return (-1) * double.Parse(ss[1]);
        }