关于提高从数据库导出到execl文件性能的问题
我的程序,运行过程中总是在打开完文件对话框后,程序就卡在那里了,只有当文件保存完后,文件对话框才关闭。请教下,如何才能提高保存execl文件的速度?我的程序代码如下:
程序代码:
private void button1_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); string fpath; sfd.Filter = "xls files(*.xls)|*.xls"; sfd.FilterIndex = 2; sfd.RestoreDirectory = true; BarProessForm fm = new BarProessForm(); if (checkBox1.Checked == true && checkBox2.Checked == true && checkBox3.Checked == true) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel3(fpath); fm.piTime.Stop(); fm.Close(); } } else { if (checkBox1.Checked == true && checkBox2.Checked == true) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel2("preYCTable", "wdYCTable", fpath); } } if (checkBox1.Checked == true && checkBox3.Checked == true) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel2("preYCTable", "llYCTable", fpath); } } if (checkBox2.Checked == true && checkBox3.Checked == true) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel2("wdYCTable", "llYCTable", fpath); } } if (checkBox1.Checked == true && checkBox2.Checked == false && checkBox3.Checked == false) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 this.Close(); SaveTableToExcel1("preYCTable", fpath); fm.piTime.Stop(); fm.Close(); } } if (checkBox1.Checked == false && checkBox2.Checked == true && checkBox3.Checked == false) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel1("wdYCTable", fpath); } } if (checkBox1.Checked == false && checkBox2.Checked == false && checkBox3.Checked == true) { this.Close(); if (sfd.ShowDialog() == DialogResult.OK) { fpath = sfd.FileName; //保存的文件路径 SaveTableToExcel1("llYCTable", fpath); } } } } private void SaveTableToExcel1(string tableName, string filepath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Excel.Workbook wBook = app.Application.Workbooks.Add(true); Excel.Worksheet wSheet = wBook.Worksheets[1] as Excel.Worksheet; SqlConnection myConn = new SqlConnection("Data Source=" + serverHostName + ",1433;Initial Catalog=moniData;User Id=sa;Password=123456;Integrated Security=false"); string sql; if (tableName.Contains("pre")) sql = "SELECT * FROM preYCTable WHERE 时间 BETWEEN '" + dateTimePicker1.Value + "' AND '" + dateTimePicker2.Value + "'"; else sql = "SELECT * FROM wdYCTable WHERE 时间 BETWEEN '" + dateTimePicker3.Value + "' AND '" + dateTimePicker4.Value + "'"; SqlCommand cmd = new SqlCommand(sql, myConn); DataSet ds = new DataSet(); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); myConn.Open(); dataAdapter.Fill(ds, "srcdt"); myConn.Close(); int rowNum = ds.Tables["srcdt"].Rows.Count; int columnNum = ds.Tables["srcdt"].Columns.Count; int col = 0; int row = 1; //将dataTable的标题导入到EXECL的标题(第一行) foreach (DataColumn dc in ds.Tables["srcdt"].Columns) { col++; wSheet.Cells[1, col] = dc.ColumnName; } //将DataTable中的数据导入Excel中 for (int i = 0; i < rowNum; i++) { row++; col = 0; for (int j = 0; j < columnNum; j++) { col++; wSheet.Cells[row, col] = ds.Tables["srcdt"].Rows[i] [j].ToString(); } } app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filepath); app.SaveWorkspace(filepath); app.Quit(); app = null; } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信 息", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { } }