关于提高从数据库导出到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 { }
}
