C#利用NPOI在Dgv控件中编辑已有的Excel并保存到Excel文件，以及NPOI 帮助类分享

1、Nuget包管理器添加NPOI 2.1.1.0

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

若用HSSF 时是对xls格式的文件进行操作。
若用XSSF时则是对xlsx格式的文件进行操作。

2、实例举例说明

2.1、加载Excel文件到DGV

private void btnSelectExcel_Click(object sender, EventArgs e)
{
   
    string path = "";
    if (SelectDirEx("扩展打开文件", ref path))
    {
   
        UIMessageTip.ShowOk(path);
        lbExcelPath.Text = path;
        //将Excel转为DataTable
        DataTable dt = new DataTable();
        dt = NpoiHelper.ExcelToDataTable("映射表", true, true, path);
        dgv1.DataSource = dt;
    }


}
/// <summary>
/// 选择文件
/// </summary>
/// <param name="desc">弹窗说明</param>
/// <param name="FilePath">返回true则path为选择文件路径</param>
/// <returns>是否选择文件</returns>
public bool SelectDirEx(string desc, ref string FilePath)
{
   
    bool bOk = false;
    using (OpenFileDialog fd = new OpenFileDialog())
    {
   
        fd.DefaultExt = ".xlsx";
        fd.Filter = "XLSX文件（*.xlsx)|*.xlsx|XLS文件（*.xls)|*.xls|所有文件（*.*)|*.*";
        fd.FilterIndex = 1;
        fd.Title = "打开Excel文件";
        if (fd.ShowDialog(null) == DialogResult.OK)
        {
   
            FilePath = fd.FileName;
            bOk = true;
        }
    }

    return bOk;
}

2.2、将修改的DGV内容更新到源文件

/// <summary>
/// 更新在界面修改的Dgv单元格值到Excel文件
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void btnSaveDgvToExcel_Click(object sender, EventArgs e)
 {
   
     bool iOk = NpoiHelper.ReplaceExcel(lbExcelPath.Text, "映射表", dgv1);
     if (iOk)
     {
   
         UIMessageTip.ShowOk(this, "映射表更新成功");

     }
     else
     {
   
         UIMessageTip.ShowError(this, $"映射表更新失败");
     }
 }

3、项目源码

在线修改的DGV单元格值已经写入到源文件的Excel表格中

using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.ComTypes;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
   
    public partial class Form1 : UIForm
    {
   
        public Form1()
        {
   
            InitializeComponent();
        }
        #region TabPage2
        private void btnSelectExcel_Click(object sender, EventArgs e)
        {
   

            string path = "";
            if (SelectDirEx("扩展打开文件", ref path))
            {
   
                UIMessageTip.ShowOk(path);
                lbExcelPath.Text = path;
                //将Excel转为DataTable
                DataTable dt = new DataTable();
                dt = NpoiHelper.ExcelToDataTable("映射表", true, true, path);
                dgv1.DataSource = dt;
            }
        }
        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="desc">弹窗说明</param>
        /// <param name="FilePath">返回true则path为选择文件路径</param>
        /// <returns>是否选择文件</returns>
        public bool SelectDirEx(string desc, ref string FilePath)
        {
   
            bool bOk = false;
            using (OpenFileDialog fd = new OpenFileDialog())
            {
   
                fd.DefaultExt = ".xlsx";
                fd.Filter = "XLSX文件（*.xlsx)|*.xlsx|XLS文件（*.xls)|*.xls|所有文件（*.*)|*.*";
                fd.FilterIndex = 1;
                fd.Title = "打开Excel文件";
                if (fd.