数据表有千万条数据,优化查询速度达到 2秒 (简单的查询),问题是导出Excel相当慢,大概2分钟左右,查询记录只有1000条也要这个时间,问问大家有没有好的解决方法,个人基本没有涉及过 winform开发!导出excel方法(摘自网络):protected void ExportExcel(SqlDataReader dr)
{
if (dr == null) return;
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "Sheet1";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value; if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
Workbooks workbooks = xlApp.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1 int iRow = 0;
while (dr.Read())
{
for (int iCount = 0; iCount < dr.FieldCount; iCount++)
{
worksheet.Cells[iRow + 1, iCount + 1] = dr[iCount];
} iRow++;
} worksheet.SaveAs(saveFileName, XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit(); GC.Collect(); //回收资源
KillProcess("Excel"); //关闭进程
}
C#ExcelWinForm性能优化
{
if (dr == null) return;
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "Sheet1";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消 Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value; if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
return;
}
Workbooks workbooks = xlApp.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1 int iRow = 0;
while (dr.Read())
{
for (int iCount = 0; iCount < dr.FieldCount; iCount++)
{
worksheet.Cells[iRow + 1, iCount + 1] = dr[iCount];
} iRow++;
} worksheet.SaveAs(saveFileName, XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit(); GC.Collect(); //回收资源
KillProcess("Excel"); //关闭进程
}
C#ExcelWinForm性能优化
1、读一行,之后直接COPY到EXCEL中
2、读N行,再COPY到EXCEL中其中第2种法子,要看你一行有多少列,20列左右的话,读50行,再COPY到EXCEL中效率最高
r.value2=new object[3,5];//object[3,5]是一个二维数据,从SqlDataReader dr读值
下面的导出非常不错:
http://bbs.csdn.net/topics/370085437