winform读取Excel和选择字段展示信息内容

winform读取Excel和选择字段展示信息内容

winform读取Excel选择收拾并展示内容

需求：读取Excel进行选择某一个字段进行查询和展示

代码实现

控件说明：

btn_read：读取Excel文件按钮
checkedListBox1：选择显示的列名
comboBoxColumns：下拉框选择列名查询
dataGridViewExcelData：展示Excel数据的视图
btn_select：查询按钮
textBoxSearch：输入关键字进行查询文本框
textBoxResults：RichTextBox控件的查询显示信息
btn_reset：重置按钮

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Windows.Forms;
using OfficeOpenXml; // 导入EPPlus命名空间
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace WindowsFormsApp1
{
    public partial class 读取Excel和选择字段展示信息内容 : Form
    {
        private DataTable dt;
        public 读取Excel和选择字段展示信息内容()
        {
            InitializeComponent();
            //禁用编辑
            comboBoxColumns.DropDownStyle = ComboBoxStyle.DropDownList;
        }

        private void btn_read_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "Excel文件 (*.xlsx)|*.xlsx|所有文件 (*.*)|*.*";
                openFileDialog.FilterIndex = 1;
                openFileDialog.RestoreDirectory = true;

                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string filePath = openFileDialog.FileName;
                    ReadExcelFile(filePath);
                }
            }
        }

        private void ReadExcelFile(string filePath)
        {
            try
            {
                // 设置EPPlus的LicenseContext
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                // 读取Excel文件
                FileInfo fileInfo = new FileInfo(filePath);
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // 获取第一个工作表
                    if (worksheet != null)
                    {
                        int rowCount = worksheet.Dimension.Rows;
                        int colCount = worksheet.Dimension.Columns;

                        dt = new DataTable();

                        // 清空CheckedListBox和ComboBox中的内容
                        checkedListBox1.Items.Clear();
                        comboBoxColumns.Items.Clear();

                        // 读取列名并检查是否有列名
                        for (int col = 1; col <= colCount; col++)
                        {
                            string columnName = worksheet.Cells[1, col].Text;
                            if (!string.IsNullOrEmpty(columnName))
                            {
                                // 检查列是否有数据
                                bool hasData = false;
                                for (int row = 2; row <= rowCount; row++)
                                {
                                    if (!string.IsNullOrEmpty(worksheet.Cells[row, col].Text))
                                    {
                                        hasData = true;
                                        break;
                                    }
                                }

                                if (hasData)
                                {
                                    dt.Columns.Add(columnName);
                                    checkedListBox1.Items.Add(columnName); // 将列名添加到CheckedListBox中
                                    comboBoxColumns.Items.Add(columnName); // 将列名添加到ComboBox中
                                }
                            }
                        }

                        // 读取数据
                        for (int row = 2; row <= rowCount; row++) // 从第二行开始读取数据
                        {
                            DataRow dr = dt.NewRow();
                            for (int col = 1; col <= colCount; col++)
                            {
                                string columnName = worksheet.Cells[1, col].Text;
                                if (dt.Columns.Contains(columnName))
                                {
                                    dr[columnName] = worksheet.Cells[row, col].Text;
                                }
                            }
                            dt.Rows.Add(dr);
                        }

                        // 绑定数据到DataGridView
                        dataGridViewExcelData.DataSource = dt;
                    }
                    else
                    {
                        MessageBox.Show("Excel文件中没有工作表。");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("读取Excel文件时发生错误: " + ex.Message);
            }
        }

        private void btn_select_Click(object sender, EventArgs e)
        {
            // 清空RichTextBox
            textBoxResults.Clear();

            // 获取用户在ComboBox中选择的列
            string searchColumn = comboBoxColumns.SelectedItem?.ToString();
            if (string.IsNullOrEmpty(searchColumn))
            {
                MessageBox.Show("请选择一个搜索列。", "提示");
                return;
            }

            // 获取用户在CheckedListBox中选择的列
            var selectedColumns = new List<string>();
            foreach (var item in checkedListBox1.CheckedItems)
            {
                selectedColumns.Add(item.ToString());
            }

            // 检查是否有列被选中
            if (selectedColumns.Count == 0)
            {
                MessageBox.Show("请选择至少一个列显示。", "提示");
                return;
            }

            // 获取搜索关键字
            string keyword = textBoxSearch.Text.Trim();
            if (string.IsNullOrEmpty(keyword))
            {
                MessageBox.Show("请输入搜索关键字。", "提示");
                return;
            }

            // 根据选中的列和关键字筛选数据
            foreach (DataRow row in dt.Rows)
            {
                if (row[searchColumn].ToString().Contains(keyword))
                {
                    textBoxResults.WordWrap = true;
                    //textBoxResults.AppendText("匹配的记录:\n");
                    foreach (var column in selectedColumns)
                    {
                        textBoxResults.AppendText($"{column}: {row[column]}\n");
                    }
                    textBoxResults.AppendText("\n"); // 分隔不同行的数据
                    //Console.WriteLine(textBoxResults.Text);
                }
            }
        }

        /// <summary>
        /// 设置重置按钮：重置查询框内容设置为空
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_reset_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(textBoxSearch.Text.Trim())) {
                MessageBox.Show("没有需要清空的信息", "提示");
            }
            else
            {
                textBoxSearch.Text = "";
            }
        }
    }
}

效果展示