C#Winform导入Excel数据到datagridview控件

当前位置：网站建设 > 技术支持

C#Winform导入Excel数据到datagridview控件

资料来源：网络整理时间：2023/2/14 1:09:18 共计：3610 浏览

此次导入Excel数据借助了ExcelDataReader插件。（由于我需要的是打开工作簿时能够选择工作表绑定于datagridview控件，通过网上资源的浏览与借鉴，ExcelDataReader能满足我的需求。）
一、插件安装

（1）安装ExcelDataReader插件（如图）

（2）查看是否安装成功（如图表明已安装成功）

二、代码编写

（1）为了方便使用，打开文件的过程构造为一个方法

        DataTableCollection tableCollection;
        public void OpenFile()
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = file.FileName;//对话框中选择的文件名
                //1：打开文件，得到文件流stream
                FileStream stream = File.Open(file.FileName, FileMode.Open, FileAccess.Read);//FileStream:用于文件中任何位置的读写。
                var sw = new Stopwatch();//定时器
                sw.Start();
                //2：得到文件reader
                var reader = ExcelReaderFactory.CreateReader(stream);
                //3：通过reader得到数据
                var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        //标题行的内容是否显示
                        UseHeaderRow = true
                    }
                });
                var openTiming = sw.ElapsedMilliseconds;
                toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
                //4：得到ExcelFile文件的表Sheet
                tableCollection = result.Tables;
                cboSheet.Items.Clear();
                foreach (DataTable item in tableCollection)
                {
                    cboSheet.Items.Add(item.TableName);
                    Console.WriteLine(item.TableName);
                }

            }
        }

（2）效果展示（图上为控件name）

（3）数据绑定方法

       public static void BindDataGridView(DataTable dt, DataGridView dgv)
        {

            /*dgv.ClearSelection();
            //不像用户显示添加行
            dgv.AllowUserToAddRows = false;
            dgv.DataSource = null;*/
            //DataTable存储数据
              dgv.DataSource = dt;

        }

（4）选择工作表，绑定相应数据

         private void dgv_CellContentClick()
        {

            //datagridview组件的颜色
            dgvData.RowsDefaultCellStyle.BackColor = Color.White;
            Console.WriteLine(cboSheet.SelectedItem.ToString());
            var dt = tableCollection[cboSheet.SelectedItem.ToString()];

            BindDataGridView(dt, dgvData);
            dgvData.DataSource = dt;
            // DataGridView取消选中第一行第一列方法(绑定数据源后)
            dgvData.Rows[0].Cells[0].Selected = false;
            dgvData.Rows[0].Selected = false;
        }

（5）在相应控件调用方法即可

        private void btnFilenpath_Click(object sender, EventArgs e)
        {
            OpenFile();
        }

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            dgv_CellContentClick();
        }
这就完成将Excel数据导入到Excel的过程啦！

下面是全部源码：

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinSampling
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /*
        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog()
            { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" })
            {
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = openFileDialog.FileName;//文件路径
                }
                else
                {
                    label2.Text = "文件框无法打开！";
                }
            }
        }
        private DataTable ReadExcelToTable(string path, int x) //excel存放的路径
        {
            try
            {
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
                string conStr =
                    string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;",
                        path);
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                DataSet ds = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(conStr))
                {
                    conn.Open();
                    DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    string sheetName = dtSheet.Rows[x]["Table_Name"].ToString();
                    string sql = string.Format("select * from [{0}]", sheetName);
                    oda.SelectCommand = new OleDbCommand(sql, conn);
                    oda.Fill(ds);
                    dt = ds.Tables[0];
                    return dt;
                }
            }
            catch (Exception ex)
            {
                label2.Text = ex.Message;
                return null;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = ReadExcelToTable(textBox1.Text, 0);
            dataTableToListview(listView1, dt);
        }
        //fill the listview
        public void dataTableToListview(ListView lv, DataTable dt)
        {
            if (dt != null)
            {
                lv.Items.Clear();
                lv.Columns.Clear();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    lv.Columns.Add(dt.Columns[i].Caption.ToString());
                }
                foreach (DataRow dr in dt.Rows)
                {
                    ListViewItem lvi = new ListViewItem();
                    lvi.SubItems[0].Text = dr[0].ToString();

                    for (int i = 1; i < dt.Columns.Count; i++)
                    {
                        lvi.SubItems.Add(dr[i].ToString());
                    }

                    lv.Items.Add(lvi);
                }
                lv.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
            }
        }
        public void listViewToDataTable(ListView lv, DataTable dt)
        {
            int i, j;
            DataRow dr;
            dt.Clear();
            dt.Columns.Clear();
            //生成DataTable列头
            for (i = 0; i < lv.Columns.Count; i++)
            {
                dt.Columns.Add(lv.Columns[i].Text.Trim(), typeof(String));
            }
            //每行内容
            for (i = 0; i < lv.Items.Count; i++)
            {
                dr = dt.NewRow();
                for (j = 0; j < lv.Columns.Count; j++)
                {
                    dr[j] = lv.Items[i].SubItems[j].Text.Trim();
                }
                dt.Rows.Add(dr);
            }
        }
        */
        DataTableCollection tableCollection;
        public void OpenFile()
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = file.FileName;//对话框中选择的文件名
                //1：打开文件，得到文件流stream
                FileStream stream = File.Open(file.FileName, FileMode.Open, FileAccess.Read);//FileStream:用于文件中任何位置的读写。
                var sw = new Stopwatch();//定时器
                sw.Start();
                //2：得到文件reader
                var reader = ExcelReaderFactory.CreateReader(stream);
                //3：通过reader得到数据
                var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        //标题行的内容是否显示
                        UseHeaderRow = true
                    }
                });
                var openTiming = sw.ElapsedMilliseconds;
                //toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
                //4：得到ExcelFile文件的表Sheet
                tableCollection = result.Tables;
                cboSheet.Items.Clear();
                foreach (DataTable item in tableCollection)
                {
                    cboSheet.Items.Add(item.TableName);
                    Console.WriteLine(item.TableName);
                }

            }
        }
        private void dgv_CellContentClick()
        {

            //datagridview组件的颜色
            dgvData.RowsDefaultCellStyle.BackColor = Color.White;
            Console.WriteLine(cboSheet.SelectedItem.ToString());
            var dt = tableCollection[cboSheet.SelectedItem.ToString()];

            BindDataGridView(dt, dgvData);
            dgvData.DataSource = dt;
            // DataGridView取消选中第一行第一列方法(绑定数据源后)
            dgvData.Rows[0].Cells[0].Selected = false;
            dgvData.Rows[0].Selected = false;
        }
        public static void BindDataGridView(DataTable dt, DataGridView dgv)
        {

            /*dgv.ClearSelection();
            //不像用户显示添加行
            dgv.AllowUserToAddRows = false;
            dgv.DataSource = null;*/
            //DataTable存储数据
            dgv.DataSource = dt;

        }

        private void btnFilenpath_Click(object sender, EventArgs e)
        {
            OpenFile();
        }

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            dgv_CellContentClick();
        }
    }
}

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：用C#多线程在winform DataGridView简单实现像ajax更新效果 | ·下一条：怎么查看端口是否被占用