DataGridView循环刷新、导出为xlsx文件，加载保存json配置文件

Winform中的DataGridView控件DataSource属性绑定到数据库，实现循环更新，并且可导出xsxl文件、暂停等功能。

注：使用第三方库有EPPlus（操作Eecel）、Newtonsoft.net（Json序列化/反序列化）、Guna2（控件库）

• winform布局如下：

• 创建LoadConFigJson方法：启动应用程序时，加载读取配置文件

 1         private void LoadConfigJson()
 2         {
 3             //从文件直接反序列化为Initialize_Model对象
 4             initialize_Model = JsonConvert.DeserializeObject<Initialize_Model>(File.ReadAllText(Environment.CurrentDirectory + "\\Initialize.json"));
 5             //给对象赋值
 6             this.guna2TextBox1.Text = initialize_Model.RefreshCycle.ToString();
 7             this.guna2TextBox2.Text = initialize_Model.DailyOutput.ToString();
 8             this.guna2TextBox3.Text = initialize_Model.MonthlyOutPut.ToString();
 9             this.guna2TextBox4.Text = initialize_Model.QuarterlyOutPut.ToString();
10             //赋值连接字符串
11             sqlConString = initialize_Model.ConnectionString;
12         }

Initialize_Model类如下：

 1     public class Initialize_Model
 2     {
 3         public string ConnectionString { get; set; }
 4 
 5         //如果为1加载初始化json，2加载保存json
 6         public int Status { get; set; }
 7         public int RefreshCycle { get; set; }
 8         public int DailyOutput { get; set; }
 9         public int MonthlyOutPut { get; set;}
10         public int QuarterlyOutPut { get; set; }
11     }

• DataGridView循环刷新方法

 1         private void CyclicRefreshing()
 2         {
 3             Task task = Task.Run(async () =>
 4             {
 5                 while (!token.IsCancellationRequested)
 6                 {
 7                     resetEvent.WaitOne();//事件如果被触发，程序暂停此位置
 8                     Action action = () =>
 9                     {
10 
11                         DataTable dataTable = Dbhelp.Query(this.sqlConString);
12                         this.guna2DataGridView1.DataSource = dataTable;
13                     };
14                     this.Invoke(action);
15                     //循环刷新周期为2s
16                     await Task.Delay(2000);
17                 }
18             }, this.token);
19 
20         }

• 创建SaveConfigJson方法：应用程序关闭时，保存修改的值到配置文件中

 1         private void SaveConfigJson()
 2         {
 3             initialize_Model.RefreshCycle = Convert.ToInt32(this.guna2TextBox1.Text.Trim());
 4             initialize_Model.DailyOutput = Convert.ToInt32(this.guna2TextBox2.Text.Trim());
 5             initialize_Model.MonthlyOutPut = Convert.ToInt32(this.guna2TextBox3.Text.Trim());
 6             initialize_Model.QuarterlyOutPut = Convert.ToInt32(this.guna2TextBox4.Text.Trim());
 7             initialize_Model.Status = 2;
 8             //序列化字符串直接写入到json文件中
 9             File.WriteAllText(Environment.CurrentDirectory + "\\SaveConfig.json", JsonConvert.SerializeObject(initialize_Model, Formatting.Indented));
10         }

• 声明字段，窗体构造函数、窗体Load处理函数、窗体关闭处理函数

 1     public partial class Form1 : Form
 2     {
 3         //初始化类
 4         Initialize_Model initialize_Model;
 5         //数据库连接字符串
 6         string sqlConString;
 7         //Excel保存路径
 8         string excelPathStr;
 9         //Task取消令牌
10         CancellationTokenSource cancellationSource;
11         CancellationToken token;
12         //初始化配置文件状态
13         bool button1Status = false;
14         //手动复位事件（用于暂停Task任务）
15         ManualResetEvent resetEvent;
16         public Form1()
17         {
18             InitializeComponent();
19             cancellationSource = new CancellationTokenSource();
20             token = cancellationSource.Token;
21             resetEvent = new ManualResetEvent(true);//首次传入为True，task任务不会暂停
22         }
23 
24         private void Form1_Load(object sender, EventArgs e)
25         {
26             LoadConfigJson();   //加载json配置文件
27             CyclicRefreshing(); //启动DataGridView循环刷新任务
28         }
29         private void Form1_FormClosing(object sender, FormClosingEventArgs e)
30         {
31             SaveConfigJson(); //调用保存配置文件代码
32         }

• 暂停DataGridView刷新

 1         private void guna2Button1_Click(object sender, EventArgs e)
 2         {
 3             if (button1Status == false)
 4             {
 5                 resetEvent.Reset(); //触发暂停
 6                 button1Status = true;
 7             }
 8             else
 9             {
10                 resetEvent.Set();　　//继续运行
11                 button1Status = false;
12             }
13 
14         }

• 导出DataGridView的DataSource到xlsx表格

 1         //导出DataGridView到Excel表格
 2         private void guna2ImageButton1_Click(object sender, EventArgs e)
 3         {
 4             //说明EPPlus不为商用
 5             ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
 6             //声明保存当前路径的字符串
 7             string CurrentPath;
 8             //打开folderBrowserDialog窗口
 9             if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
10             {
11                 //判断选择路径不为空
12                 if (folderBrowserDialog1.SelectedPath != null)
13                 {
14                     excelPathStr = folderBrowserDialog1.SelectedPath.ToString();//选择的文件夹路径赋值给excel保存的路径
15                     CurrentPath = excelPathStr + $"\\{System.DateTime.Now.ToString("yyyy mm dd HH mm ss")}.xlsx";
16                     //加载excel包，作为操作excel的入口
17                     using (ExcelPackage package = new ExcelPackage(CurrentPath))
18                     {
19                         var sheet = package.Workbook.Worksheets.Add("TestSheet");
20                         sheet.Cells["A1"].LoadFromDataTable((DataTable)this.guna2DataGridView1.DataSource, true, TableStyles.Dark1);
21                         package.Save();
22                     }
23                     if (File.Exists(CurrentPath))
24                     {
25                         MessageBox.Show("表格导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
26                     }
27                     else
28                     {
29                         MessageBox.Show("表格导出失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
30                     }
31                 }
32             }
33         }