DataGridView定时刷新/导出Excel,加载Json格式配置文件
DataGridView定时刷新
- 新建DBHelper帮助其连接数据库
1 public class Dbhelp 2 { 3 public static DataTable Query(string sqlConString) 4 { 5 DataTable dataTable = new DataTable(); 6 string sqlCmd = "select top 20 * from [Table_refresh testing] order by Id desc;"; 7 using (SqlConnection conn = new SqlConnection(sqlConString)) 8 { 9 conn.Open(); 10 SqlCommand sqlCommand = new SqlCommand(sqlCmd, conn); 11 SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); 12 dataTable.Load(sqlDataReader); 13 } 14 return dataTable; 15 } 16 }
- 窗口Load事件处理器
private void Form1_Load(object sender, EventArgs e) { CyclicRefreshing(); //启动DataGridView循环刷新任务 }
- CycliReferhing方法如下:
1 private void CyclicRefreshing() 2 { 3 //开启一个任务线程 4 Task task = Task.Run(async () => 5 { 6 while (!token.IsCancellationRequested)//触发token取消线程内部循环并且取消Task 7 { 8 resetEvent.WaitOne();//暂停线程 9 //此委托用于跨线程执行UI线程中的控件 10 Action action = () => 11 { 12 13 DataTable dataTable = Dbhelp.Query(this.sqlConString);//执行查询 14 this.guna2DataGridView1.DataSource = dataTable; 15 }; 16 this.Invoke(action);//窗体执行委托 17 await Task.Delay(2000);//间隔2s 18 } 19 }, this.token); 20 21 }
- 循环任务中的等待功能(ManualResetEvent)
CancellationToken token;//声明变量 resetEvent = new ManualResetEvent(true);//初始赋值为true为不暂停
暂停、取消暂停
1 resetEvent.Set();
2 resetEvent.Reset();
- 线程令牌声明,触发“取消”
1 CancellationTokenSource cancellationSource; 2 CancellationToken token; 3 cancellationSource = new CancellationTokenSource();//实例化 4 token = cancellationSource.Token;//获取令牌 5 cancellationSource.Cancel();//取消任务
导出Excel
使用EPPlus第三方库(商用收费),说明EPPlus不用于商业
1 ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
实例化文件浏览器
1 FolderBrowserDialog folderBrowserDialog1 = new FolderBrowserDialog();
导出Excel代码如下:
1 private void guna2ImageButton1_Click(object sender, EventArgs e) 2 { 3 ExcelPackage.LicenseContext = LicenseContext.NonCommercial; 4 string CurrentPath;//Excel保存的路径完全限定名 5 string excelPathStr;//文件浏览器选择的路径 6 if (folderBrowserDialog1.ShowDialog() == DialogResult.OK) 7 { 8 //判断选中路径是否为空 9 if (folderBrowserDialog1.SelectedPath != null) 10 { 11 excelPathStr = folderBrowserDialog1.SelectedPath.ToString(); 12 //得到的路径加上文件限定名(当前时间作为Excel名) 13 CurrentPath = excelPathStr + $"\\{System.DateTime.Now.ToString("yyyy mm dd HH mm ss")}.xlsx"; 14 //创建一个Excel工作表并打开Excel包 15 using (ExcelPackage package = new ExcelPackage(CurrentPath)) 16 { 17 var sheet = package.Workbook.Worksheets.Add("TestSheet");//创建一个表单 18 //加载DataGridView到Excel表单中 19 sheet.Cells["A1"].LoadFromDataTable((DataTable)this.guna2DataGridView1.DataSource, true, TableStyles.Dark1); 20 package.Save(); 21 } 22 if (File.Exists(CurrentPath))//判断另存为的Excel是否导出成功 23 { 24 MessageBox.Show("表格导出成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 25 } 26 else 27 { 28 MessageBox.Show("表格导出失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 29 } 30 } 31 } 32 }
加载Json格式配置文件
声明Json映射类
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 }
初始加载Json配置文件,并把加载的数据显示到相应的TextBox上:
1 private void LoadConfigJson() 2 { 3 //Environment.CurrentDirectory获取应用程序所在文件夹 4 initialize_Model = JsonConvert.DeserializeObject<Initialize_Model>(File.ReadAllText(Environment.CurrentDirectory + "\\Initialize.json"));//反序列化到对象类上 5 this.guna2TextBox1.Text = initialize_Model.RefreshCycle.ToString(); 6 this.guna2TextBox2.Text = initialize_Model.DailyOutput.ToString(); 7 this.guna2TextBox3.Text = initialize_Model.MonthlyOutPut.ToString(); 8 this.guna2TextBox4.Text = initialize_Model.QuarterlyOutPut.ToString(); 9 sqlConString = initialize_Model.ConnectionString; 10 }
保存值到Json配置文件中:
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 File.WriteAllText(Environment.CurrentDirectory + "\\SaveConfig.json", JsonConvert.SerializeObject(initialize_Model, Formatting.Indented)); 9 }