核心代码部分
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace ExportAlarmDataToExcel
{
public partial class Form1 : Form
{
private static readonly SqlConnection sqlConnection = new SqlConnection();
private DataTable tBSystemAlarm = null;
private bool isExporting = false;
public Form1()
{
InitializeComponent();
this.FormClosed += (s, e) =>
{
sqlConnection.Close();
};
}
/// <summary>
/// 登陆数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLoginDB_Click(object sender, EventArgs e)
{
if (sqlConnection.State == ConnectionState.Open)
{
return;
}
string dataSource = txtIP.Text.Trim();
string userId = txtUserName.Text.Trim();
string password = txtPassword.Text.Trim();
if (string.IsNullOrEmpty(dataSource))
{
return;
}
if (string.IsNullOrEmpty(userId))
{
return;
}
if (string.IsNullOrEmpty(password))
{
return;
}
if (Open(dataSource, userId, password))
{
tBSystemAlarm = new DataTable();
try
{
FillData(tBSystemAlarm);
int count = tBSystemAlarm.Rows.Count;
txtLog.Text += $"时间:{DateTime.Now} => 获取数据成功,总共包含到 {count} 条记录!\r\n";
if (count > 0)
this.btnExport.Visible = true;
}
catch (Exception ex)
{
txtLog.Text += $"时间:{DateTime.Now} => 获取数据异常,请联系管理员!\r\n";
}
}
}
/// <summary>
/// 获取数据表 TB_System_Alarm 的全部信息
/// </summary>
/// <returns></returns>
private void FillData(DataTable tBSystemAlarm)
{
SqlCommand sqlCommand = new SqlCommand("SELECT CreateTime,DeviceId,ChannelId,AlarmLevel,AlarmTypeName,Possibility,Center,Longitude,Latitude,Width,CreateDate,Stadus,Timestamp,Remark,Display,Discribe FROM TB_System_Alarm");
sqlCommand.Connection = sqlConnection;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(tBSystemAlarm);
}
private void ExportToExcel()
{
Thread thread = new Thread(new ThreadStart(() =>
{
using (StreamWriter sw = new StreamWriter(File.Create(this.saveFileDialog1.FileName), Encoding.UTF8))
{
StringBuilder csvHeader = new StringBuilder();
for (int h = 0; h < this.tBSystemAlarm.Columns.Count; h++)
{
csvHeader.Append(',');
csvHeader.Append(this.tBSystemAlarm.Columns[h].Caption);
}
sw.WriteLine(csvHeader);
for (int r = 0; r < this.tBSystemAlarm.Rows.Count; r++)
{
StringBuilder csvBody = new StringBuilder();
DataRow row = this.tBSystemAlarm.Rows[r];
for (int c = 0; c < this.tBSystemAlarm.Columns.Count; c++)
{
csvBody.Append(',');
csvBody.Append(row[c]);
}
sw.WriteLine(csvBody);
SetProcess($"已导出 {r + 1} 条警报数据!");
}
}
SetProcess("已全部导出!");
}));
thread.Start();
}
private void SetProcess(string messageText)
{
if (this.InvokeRequired)
{
this.Invoke(new Action(() =>
{
this.btnExport.Text = messageText;
}));
}
else
{
this.btnExport.Text = messageText;
}
}
private bool Open(string dataSource, string userId, string password)
{
sqlConnection.ConnectionString = $"Data Source={dataSource};Initial Catalog=monitoring;User Id={userId};Password={password}";
try
{
sqlConnection.Open();
return true;
}
catch (Exception ex)
{
MessageBox.Show("数据库链接错误,请确认您输入的信息是否正确,并且检查网络是否配置正常,数据库远程服务是否启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
private void btnExport_Click(object sender, EventArgs e)
{
this.btnExport.Enabled = false;
//this.saveFileDialog1.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx";
this.saveFileDialog1.Filter = "(*.csv)|*.csv";
string fileName = this.saveFileDialog1.FileName;
this.saveFileDialog1.ShowDialog();
ExportToExcel();
}
}
}
WindowsForm UI设计器代码
namespace ExportAlarmDataToExcel
{
partial class Form1
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing">如果应释放托管资源,为 true;否则为 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要修改
/// 使用代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnLoginDB = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.txtIP = new System.Windows.Forms.TextBox();
this.txtUserName = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.txtPassword = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.txtLog = new System.Windows.Forms.TextBox();
this.btnExport = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.SuspendLayout();
//
// btnLoginDB
//
this.btnLoginDB.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.btnLoginDB.Location = new System.Drawing.Point(13, 193);
this.btnLoginDB.Name = "btnLoginDB";
this.btnLoginDB.Size = new System.Drawing.Size(525, 54);
this.btnLoginDB.TabIndex = 0;
this.btnLoginDB.Text = "登陆";
this.btnLoginDB.UseVisualStyleBackColor = true;
this.btnLoginDB.Click += new System.EventHandler(this.btnLoginDB_Click);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.label1.Location = new System.Drawing.Point(5, 17);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(230, 46);
this.label1.TabIndex = 1;
this.label1.Text = "服务器地址:";
//
// txtIP
//
this.txtIP.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.txtIP.Location = new System.Drawing.Point(235, 13);
this.txtIP.MaxLength = 64;
this.txtIP.Name = "txtIP";
this.txtIP.Size = new System.Drawing.Size(303, 54);
this.txtIP.TabIndex = 2;
this.txtIP.Text = ".\\SQLEXPRESS";
//
// txtUserName
//
this.txtUserName.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.txtUserName.Location = new System.Drawing.Point(235, 73);
this.txtUserName.MaxLength = 32;
this.txtUserName.Name = "txtUserName";
this.txtUserName.Size = new System.Drawing.Size(303, 54);
this.txtUserName.TabIndex = 4;
this.txtUserName.Text = "sa";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.label2.Location = new System.Drawing.Point(75, 77);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(160, 46);
this.label2.TabIndex = 3;
this.label2.Text = "用户名:";
//
// txtPassword
//
this.txtPassword.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.txtPassword.Location = new System.Drawing.Point(235, 133);
this.txtPassword.MaxLength = 32;
this.txtPassword.Name = "txtPassword";
this.txtPassword.PasswordChar = '$';
this.txtPassword.Size = new System.Drawing.Size(303, 54);
this.txtPassword.TabIndex = 6;
//
// label3
//
this.label3.AutoSize = true;
this.label3.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.label3.Location = new System.Drawing.Point(110, 137);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(125, 46);
this.label3.TabIndex = 5;
this.label3.Text = "密码:";
//
// txtLog
//
this.txtLog.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.txtLog.Font = new System.Drawing.Font("微软雅黑", 15.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.txtLog.ForeColor = System.Drawing.SystemColors.MenuHighlight;
this.txtLog.Location = new System.Drawing.Point(13, 253);
this.txtLog.Multiline = true;
this.txtLog.Name = "txtLog";
this.txtLog.ReadOnly = true;
this.txtLog.Size = new System.Drawing.Size(525, 227);
this.txtLog.TabIndex = 7;
//
// btnExport
//
this.btnExport.Font = new System.Drawing.Font("微软雅黑", 26.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
this.btnExport.Location = new System.Drawing.Point(12, 486);
this.btnExport.Name = "btnExport";
this.btnExport.Size = new System.Drawing.Size(526, 54);
this.btnExport.TabIndex = 8;
this.btnExport.Text = "导出数据到EXCEL";
this.btnExport.UseVisualStyleBackColor = true;
this.btnExport.Visible = false;
this.btnExport.Click += new System.EventHandler(this.btnExport_Click);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(554, 548);
this.Controls.Add(this.btnExport);
this.Controls.Add(this.txtLog);
this.Controls.Add(this.txtPassword);
this.Controls.Add(this.label3);
this.Controls.Add(this.txtUserName);
this.Controls.Add(this.label2);
this.Controls.Add(this.txtIP);
this.Controls.Add(this.label1);
this.Controls.Add(this.btnLoginDB);
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "将警报数据导出到EXCEL";
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.Button btnLoginDB;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox txtIP;
private System.Windows.Forms.TextBox txtUserName;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox txtPassword;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox txtLog;
private System.Windows.Forms.Button btnExport;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
}
}