c#：winform读取excel，并显示在griddataview

柔光的暖阳◎ 2022-02-22 11:12 508阅读 0赞

using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.OleDb;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace ExcelWord
    { 
        public partial class Form1 : Form
        { 
            public Form1()
            { 
                InitializeComponent();
            }
    
    
            public DataSet getData()
    
            { 
    
                //打开文件
    
                OpenFileDialog file = new OpenFileDialog();
    
                //file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
                file.Filter = "Excel文件 |*.xlsx;*.xls";
                file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    
                file.Multiselect = false;
    
                if (file.ShowDialog() == DialogResult.Cancel)
    
                    return null;
    
                //判断文件后缀
    
                var path = file.FileName;
    
                string fileSuffix = System.IO.Path.GetExtension(path);
    
                if (string.IsNullOrEmpty(fileSuffix))
    
                    return null;
    
                using (DataSet ds = new DataSet())
    
                { 
    
                    //判断Excel文件是2003版本还是2007版本
    
                    string connString = "";
    
                    if (fileSuffix == ".xls")
    
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    
                    else
    
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
    
                    //读取文件
    
                    string sql_select = " SELECT * FROM [Sheet1$]";
    
                    using (OleDbConnection conn = new OleDbConnection(connString))
    
                    using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
    
                    { 
    
                        conn.Open();
    
                        cmd.Fill(ds);
    
                    }
    
                    if (ds == null || ds.Tables.Count <= 0) return null;
    
                    return ds;
    
                }
    
            }
    
            private void button2_Click(object sender, EventArgs e)
            { 
                dataGridView2.DataSource = null; //每次打开清空内容
    
                DataSet dataSet = getData();
                if (dataSet != null)
                { 
                    DataTable dt = dataSet.Tables[0];
                    RemoveEmpty(dt);
                    dataGridView2.DataSource = dt;
                }
            }
    
            //删除空行
            protected void RemoveEmpty(DataTable dt)
            { 
                List<DataRow> removelist = new List<DataRow>();
                for (int i = 0; i < dt.Rows.Count; i++)
                { 
                    bool IsNull = true;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    { 
                        if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                        { 
                            IsNull = false;
                        }
                    }
                    if (IsNull)
                    { 
                        removelist.Add(dt.Rows[i]);
                    }
                }
                for (int i = 0; i < removelist.Count; i++)
                { 
                    dt.Rows.Remove(removelist[i]);
                }
            }
    
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1b3lpa3Vu_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1b3lpa3Vu_size_16_color_FFFFFF_t_70]: /images/20220222/5a47c5b0aa1b4a96b0f0f4652419ed81.png