C#读取Excel⽂件（.xls.xlsx）
using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using ExcelDataReader;
namespace ReadExcel
{
///<summary>
///    C#读取Excel⽂件（.xls .xlsx）
///    LDH @ 2021-7-28
/
//    Nuget: ExcelDataReader ExcelDataReader.DataSet
///</summary>
datagridview数据源
public partial class FrmMain : Form
{
private DataTableCollection _tableCollection;
public FrmMain()
{
InitializeComponent();
}
///<summary>
///打开Excel表格，⽀持 .xlsx .xls 两种格式
/
//</summary>
///<param name="sender"></param>
///<param name="e"></param>
private void btnBrowse_Click(object sender, EventArgs e)
{
using (var openFileDialog = new OpenFileDialog {Filter = @"Excel表格|*.xlsx|Excel|*.xls"})
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
txtFileName.Text = openFileDialog.FileName;
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))                    {
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration {UseHeaderRow = true}                            });
_tableCollection = result.Tables;
cboSheet.Items.Clear();
foreach (DataTable item in _tableCollection) cboSheet.Items.Add(item.TableName);
}
}
}
}
}
private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
{
BindDataForDataGridView();
}
///<summary>
///给DataGridView绑定数据源
///</summary>
private void BindDataForDataGridView()
{
var dt = _tableCollection[cboSheet.SelectedItem.ToString()];
DataGridViewHelper.BindDataGridViewFillShow(dt, dataGridView1, Color.BlueViolet);
dataGridView1.DataSource = dt;
// DataGridView取消选中第⼀⾏第⼀列⽅法(绑定数据源后)
dataGridView1.Rows[0].Cells[0].Selected = false;
dataGridView1.Rows[0].Selected = false;
}
}
}
///<summary>
///铺满显⽰，绑定DataGridView数据源后
/
//    LDH @ 2021-7-28
///</summary>
///<param name="dt">DataTable</param>
///<param name="dgv">DataGridView</param>
///<param name="headerFontColor">表头字体颜⾊设置</param>
public static void BindDataGridViewFillShow(DataTable dt, DataGridView dgv, Color headerFontColor)        {
dgv.ClearSelection();
dgv.AllowUserToAddRows = false;
dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; // 平均分平铺显⽰
dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect; // 设置为整⾏被选中
// 给表头内容换颜⾊
dgv.EnableHeadersVisualStyles = false; // 这样就可以使⽤当前的主题的样式了，这句话⼗分关键！            dgv.ColumnHeadersDefaultCellStyle.Font = new Font("微软雅⿊", 12, FontStyle.Bold);
dgv.ColumnHeadersDefaultCellStyle.ForeColor = headerFontColor;
dgv.DataSource = null;
dgv.DataSource = dt;
}