数据库查询自动导出发送工具代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】数据库查询自动导出发送工具
作者:v / 发布于2015/4/14/ 783
该工具可以根据执行文件中的SQL查询SQL Server数据库中的信息，并将结果自动保存为Excel 2007格式的文件，并立即发送给指定邮箱，省去等待查询结果的时间。

	?using NPOI.SS.UserModel;
	using NPOI.XSSF.UserModel;
	using System;
	using System.ComponentModel;
	using System.Data;
	using System.Data.SqlClient;
	using System.IO;
	using System.Net.Mail;
	using System.Windows.Forms;
	
	namespace QueryExporterAndPoster
	{
	 public partial class frmMain : Form
	 {
	 private string DatabaseName;
	 private DateTime dtSpending;
	 private string attachmentPath;
	 private string sqlContent;
	 private int CommandTimeout;
	 private string connectionString;
	 private bool isBusy = false;
	 private string sqlFilePath;
	
	 public frmMain(string ConnectionString, string DatabaseName)
	 {
	 InitializeComponent();
	 this.connectionString = ConnectionString;
	 this.DatabaseName = DatabaseName;
	 }
	
	 private Stream RenderDataTableToExcel(DataTable SourceTable, string sheetName, bool dateWithTime)
	 {
	 XSSFWorkbook workbook = null;
	 MemoryStream ms = null;
	 ISheet sheet = null;
	 XSSFRow headerRow = null;
	 try
	 {
	 workbook = new XSSFWorkbook();
	 IDataFormat dateFormat = workbook.CreateDataFormat();
	 ICellStyle dateStyle = workbook.CreateCellStyle();
	 dateStyle.DataFormat = dateFormat.GetFormat("yyyy/m/d" + (dateWithTime ? " h:mm;@" : string.Empty));
	 IDataFormat decimalFormat1 = workbook.CreateDataFormat();
	 ICellStyle decimalStyle1 = workbook.CreateCellStyle();
	 decimalStyle1.DataFormat = decimalFormat1.GetFormat("#0.0");
	 IDataFormat decimalFormat2 = workbook.CreateDataFormat();
	 ICellStyle decimalStyle2 = workbook.CreateCellStyle();
	 decimalStyle2.DataFormat = decimalFormat2.GetFormat("#,##0.00");
	 ms = new MemoryStream();
	 sheet = workbook.CreateSheet(sheetName);
	 headerRow = (XSSFRow)sheet.CreateRow(0);
	 foreach (DataColumn column in SourceTable.Columns)
	 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
	 int rowIndex = 1;
	 foreach (DataRow row in SourceTable.Rows)
	 {
	 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
	 foreach (DataColumn column in SourceTable.Columns)
	 {
	 if (row[column] is int)
	 dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]);
	 else if (row[column] is decimal)
	 {
	 ICell cell = dataRow.CreateCell(column.Ordinal);
	 cell.SetCellValue((double)(decimal)row[column]);
	 cell.CellStyle = column.Ordinal == 10 ? decimalStyle2 : decimalStyle1;
	 }
	 else if (row[column] is float)
	 {
	 ICell cell = dataRow.CreateCell(column.Ordinal);
	 cell.SetCellValue((double)(float)row[column]);
	 }
	 else if (row[column] is double)
	 {
	 ICell cell = dataRow.CreateCell(column.Ordinal);
	 cell.SetCellValue((double)row[column]);
	 }
	 else if (row[column] is DateTime)
	 {
	 ICell cell = dataRow.CreateCell(column.Ordinal);
	 cell.SetCellValue((DateTime)row[column]);
	 cell.CellStyle = dateStyle;
	 }
	 else
	 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
	 }
	 ++rowIndex;
	 }
	 //列宽自适应，只对英文和数字有效
	 for (int i = 0; i <= SourceTable.Columns.Count; ++i)
	 sheet.AutoSizeColumn(i);
	 workbook.Write(ms);
	 ms.Flush();
	 }
	 catch (System.Exception ex)
	 {
	 throw ex;
	 }
	 finally
	 {
	 ms.Close();
	 sheet = null;
	 headerRow = null;
	 workbook = null;
	 }
	 return ms;
	 }
	
	 private void ConvertToExcel(string xlsSaveFileName, DataTable SourceTable, string sheetName, bool dateWithTime = true)
	 {
	 FileStream fs = null;
	 try
	 {
	 using (fs = new FileStream(xlsSaveFileName, FileMode.Create, FileAccess.Write))
	 {
	 using (BinaryWriter bw = new BinaryWriter(fs))
	 {
	 MemoryStream ms = RenderDataTableToExcel(SourceTable, sheetName, dateWithTime) as MemoryStream;
	 bw.Write(ms.ToArray());
	 bw.Flush();
	 bw.Close();
	 }
	 }
	 }
	 catch (System.Exception ex)
	 {
	 throw new System.Exception("转换数据到Excel失败：" + ex.Message);
	 }
	 finally
	 {
	 if (fs != null)
	 fs.Close();
	 }
	 }
	
	 private void SendEmail(string attachmentPath)
	 {
	 try
	 {
	 MailMessage message = new MailMessage();
	 message.To.Add("AppSupport@company.com");
	 message.Subject = "导出数据库&ldquo;" + DatabaseName + "&rdquo;查询成功完成";
	 message.From = new MailAddress("AppSupport@company.com");
	 message.Body = "已执行时间: " + lblSpendTime.Text;
	 if (chkSendSqlFile.Checked)
	 message.Attachments.Add(new Attachment(sqlFilePath));
	 message.Attachments.Add(new Attachment(attachmentPath));
	 SmtpClient client = new SmtpClient("MailServer.company.com")
	 {
	 EnableSsl = false
	 };
	
	 client.Send(message);
	 }
	 catch (Exception ex)
	 {
	 throw ex;
	 }
	 }
	
	 /// <summary>
	 /// 查询数据库返回DataTable对象的方法
	 /// </summary>
	 /// <param name="sql">要执行的SQL语句</param>
	 /// <param name="param">参数数组（可选）</param>
	 /// <returns></returns>
	 private DataTable GetDataTable(string sql, params SqlParameter[] param)
	 {
	 SqlConnection cn = null;
	 SqlDataAdapter dapt = null;
	 DataTable dt = new DataTable();
	 try
	 {
	 cn = new SqlConnection(connectionString);
	 dapt = new SqlDataAdapter(sql, cn);
	 dapt.SelectCommand.CommandTimeout = CommandTimeout;
	
	 if (param.Length > 0)
	 {
	 foreach (SqlParameter p in param)
	 {
	 if (p != null)
	 dapt.SelectCommand.Parameters.Add(p);
	 }
	 }
	 dapt.Fill(dt);
	 }
	 catch (System.Exception ex)
	 {
	 throw ex;
	 }
	 finally
	 {
	 if (dapt != null)
	 dapt.Dispose();
	 if (cn != null)
	 {
	 cn.Close();
	 cn.Dispose();
	 }
	 }
	 return dt;
	 }
	
	 private void btnBrowse_Click(object sender, EventArgs e)
	 {
	 if (openFileDialog1.ShowDialog() == DialogResult.OK)
	 cmbSqlFileList.Text = openFileDialog1.FileName;
	 }
	
	 private void btnRefresh_Click(object sender, EventArgs e)
	 {
	 foreach (string sqlFile in Directory.EnumerateFiles(Application.StartupPath, "*.sql"))
	 cmbSqlFileList.Items.Add(sqlFile);
	 }
	
	 private void btnSaveExcelFile_Click(object sender, EventArgs e)
	 {
	 if (saveFileDialog1.ShowDialog() == DialogResult.OK)
	 txtSaveExcelFile.Text = saveFileDialog1.FileName;
	 }
	
	 private void btnResetFile_Click(object sender, EventArgs e)
	 {
	 txtSaveExcelFile.Text = Application.StartupPath + @"\" + DatabaseName + DateTime.Today.ToString("yyyyMMdd") + ".xlsx";
	 }
	
	 private void btnExportExcelSheet_Click(object sender, EventArgs e)
	 {
	 try
	 {
	 if (cmbSqlFileList.Text.Trim() == string.Empty)
	 {
	 btnBrowse_Click(sender, e);
	 return;
	 }
	 else if (!File.Exists(cmbSqlFileList.Text.Trim()))
	 {
	 MessageBox.Show("SQL查询文件不存在！");
	 btnBrowse_Click(sender, e);
	 return;
	 }
	 if (Int32.TryParse(txtConnectionTimeout.Text.Trim(), out CommandTimeout) && CommandTimeout > 0)
	 {
	 btnRefresh.Enabled = false;
	 btnBrowse.Enabled = false;
	 btnSaveExcelFile.Enabled = false;
	 btnResetFile.Enabled = false;
	 btnExportExcelSheet.Enabled = false;
	 cmbSqlFileList.Enabled = false;
	 txtConnectionTimeout.ReadOnly = true;
	 dtSpending = new DateTime(1900, 1, 1, 0, 0, 0);
	 lblSpendTime.Text = dtSpending.Hour.ToString().PadLeft(2, '0') + ":" + dtSpending.ToString("mm:ss");
	 timer2.Enabled = true;
	 attachmentPath = txtSaveExcelFile.Text.Trim();
	 sqlFilePath = cmbSqlFileList.Text.Trim();
	 sqlContent = File.ReadAllText(sqlFilePath);
	 exportExcelSheetWorker.RunWorkerAsync();
	 }
	 else
	 MessageBox.Show("估计执行时间必须为正整数！");
	 }
	 catch (Exception ex)
	 {
	 MessageBox.Show(ex.Message);
	 }
	 }
	
	 private void seachSqlFileWorker_DoWork(object sender, DoWorkEventArgs e)
	 {
	 foreach (string sqlFile in Directory.EnumerateFiles(Application.StartupPath, "*.sql"))
	 cmbSqlFileList.Items.Add(sqlFile);
	 }
	
	 private void seachSqlFileWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
	 {
	 btnRefresh.Enabled = true;
	 }
	
	 private void exportExcelSheetWorker_DoWork(object sender, DoWorkEventArgs e)
	 {
	 isBusy = true;
	 ConvertToExcel(attachmentPath, GetDataTable(sqlContent), DatabaseName);
	 SendEmail(attachmentPath);
	 }
	
	 private void exportExcelSheetWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
	 {
	 isBusy = false;
	 if (chkAutoExit.Checked)
	 Application.Exit();
	 else
	 {
	 timer2.Enabled = false;
	 btnExportExcelSheet.Enabled = true;
	 btnBrowse.Enabled = true;
	 btnSaveExcelFile.Enabled = true;
	 btnResetFile.Enabled = true;
	 btnRefresh.Enabled = true;
	 cmbSqlFileList.Enabled = true;
	 txtConnectionTimeout.ReadOnly = false;
	 }
	 }
	
	 private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
	 {
	 if (this.WindowState == FormWindowState.Minimized)
	 {
	 this.Show();
	 this.WindowState = FormWindowState.Normal;
	 notifyIcon1.Visible = false;
	 this.ShowInTaskbar = true;
	 }
	 }
	
	 private void frmMain_Load(object sender, EventArgs e)
	 {
	 txtSaveExcelFile.Text = Application.StartupPath + @"\" + DatabaseName + DateTime.Today.ToString("yyyyMMdd") + ".xlsx";
	 foreach (string sqlFile in Directory.EnumerateFiles(Application.StartupPath, "*.sql"))
	 cmbSqlFileList.Items.Add(sqlFile);
	 if (cmbSqlFileList.Items.Count > 0)
	 cmbSqlFileList.SelectedIndex = 0;
	 }
	
	 private void frmMain_Resize(object sender, EventArgs e)
	 {
	 if (this.WindowState == FormWindowState.Minimized) //判断是否最小化
	 {
	 this.ShowInTaskbar = false; //不显示在系统任务栏
	 notifyIcon1.Visible = true; //托盘图标可见
	 if (isBusy)
	 notifyIcon1.ShowBalloonTip(1000);
	 else
	 notifyIcon1.ShowBalloonTip(1000, notifyIcon1.BalloonTipTitle, "双击还原搜索工具！", notifyIcon1.BalloonTipIcon);
	 }
	 }
	
	 private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
	 {
	 Application.Exit();
	 }
	
	 private void timer2_Tick(object sender, EventArgs e)
	 {
	 dtSpending = dtSpending.AddSeconds(1);
	 lblSpendTime.Text = dtSpending.Hour.ToString().PadLeft(2, '0') + ":" + dtSpending.ToString("mm:ss");
	 }
	 }
	}
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　自动导出　
同语言下
可能有用的
v贡献的其它代码(3)