零基础学习上位机——Excel表格数据的录入与读取

开发环境

        开发环境是Visual Stdio 2022

编程思想

     用c#代码创建一个自定义路径下一个Excel表格，本文中主要是采用泛型集合的思想，运用MniniExcel库中的SaveAs方法，将集合中的数据存到Excel表格中。

具体实现

首先创建一个学生类，类中包含学生姓名/性别/年龄/学号/家庭住址信息：

      public class Student
    {

        public string SName { get; set; }

        public string Sex { get; set; }

        public int Age { get; set; }

        public string Id { get; set; }
        public string Address { get; set; }
    }

创建一个Winform程序，界面如下所示：

       using MiniExcelLibs;
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;

namespace Excel_Operate
{
    public partial class FrmMain : Form
    {
        public FrmMain()
        {
            InitializeComponent();
            this.cmb_Sex.DataSource = new string[] { "男","女"};
            this.dgv_Main.AutoGenerateColumns = false;
            TotalStudents=GetAllStudents();
            Rfesh();
        }

        private string StudentPath = Application.StartupPath + "\\Config\\Student.xlsx";
        private List<Student> TotalStudents=new List<Student>();
        private List<Student> GetAllStudents()
        {
            try
            {
                return MiniExcel.Query<Student>(StudentPath).ToList();
            }
            catch (Exception)
            {

                return new List<Student> { new Student() };
            }
          
        
        }
        private bool IsNameExits(String SN)
        { 
           return TotalStudents.FindAll(c=>c.SName==SN).ToList().Count>0;
        
        }
        private void Rfesh()
        {
            if (TotalStudents != null && TotalStudents.Count > 0)
            {
                this.dgv_Main.DataSource = null;
                this.dgv_Main.DataSource= TotalStudents;           
            
            }
        
        }
        private void btn_Add_Click(object sender, EventArgs e)
        { 
            var sn=this.txt_Name.Text.Trim();
            if (IsNameExits(sn))
            {
                MessageBox.Show("该同学已经录入","信息提示");
                return;
            }
            TotalStudents.Add(new Student()
            { 
               SName= sn,
               Sex=this.cmb_Sex.Text.Trim(),
               Age=Convert.ToInt32( this.txt_Age.Text.Trim()),
               Id= this.txt_Id.Text.Trim(),
               Address=this.txt_Address.Text.Trim()      
         
            });
            try
            {
                MiniExcel.SaveAs(StudentPath, TotalStudents, overwriteFile: true);
                MessageBox.Show("录入成功","信息录入");
                Rfesh();
            }
            catch (Exception ex)
            {
                MessageBox.Show("录入失败："+ex.Message, "信息录入");

            }

            
          
        }

        private void btn_Delete_Click(object sender, EventArgs e)
        {
            var sn = this.txt_Name.Text.Trim();
            if (!IsNameExits(sn))
            {
                MessageBox.Show("该同学不存在", "信息提示");
                return;
            }

            TotalStudents.RemoveAll(c=>c.SName==sn);

            try
            {
                MiniExcel.SaveAs(StudentPath, TotalStudents, overwriteFile: true);
                MessageBox.Show("删除成功", "信息删除");
                Rfesh();
            }
            catch (Exception ex)
            {

                MessageBox.Show("删除失败：" + ex.Message, "信息删除");

            }


        }

        private void dgv_Main_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                Update(TotalStudents[e.RowIndex]);
            }
        }

        private void Update(Student student)
        { 
            if(student!=null) 
            {
                this.txt_Name.Text = student.SName;
                this.cmb_Sex.Text = student.Sex;
                this.txt_Address.Text = student.Address;
                this.txt_Age.Text =Convert.ToString (student.Age);
                this.txt_Id.Text = student.Id;      
            
            
            }
        
        }

        private void btn_Modify_Click(object sender, EventArgs e)
        {
            var sn = this.txt_Name.Text.Trim();
            if (!IsNameExits(sn))
            {
                MessageBox.Show("该同学不存在", "信息提示");
                return;
            }

            var student = TotalStudents.Find(c => c.SName == sn);

            //student.SName = sn;
            student.Sex = this.cmb_Sex.Text.Trim();
            student.Age=Convert.ToInt32( this.txt_Age.Text.Trim());
            student.Id=this.txt_Id.Text.Trim();
            student.Address=this.txt_Address.Text.Trim();

            try
            {
                MiniExcel.SaveAs(StudentPath, TotalStudents, overwriteFile: true);
                MessageBox.Show("修改成功", "信息修改");
                Rfesh();
            }
            catch (Exception ex)
            {

                MessageBox.Show("修改失败：" + ex.Message, "信息修改");
            }

        }
    }
}

效果展示

’

总结

代码中主要用到了泛型集合来存储数据，然后将集合与DataGriview 控件相绑定，用于数据的直观显示。代码中也用到了匿名函数（Lambda)表达式，MiniExcel库中的SaveAs,Query方法实现对Excel表格的创建与操作。