大数据量导出Excel数据
用普通导出EXCEL的方法时，如果数据量过大会导致进程挂起，经过试验有两种方式可以解决这个问题。
一：BCP导出
代码如下：
exec master..xp_cmdshell 'bcp "select * from OpenDataSource(''SQLOLEDB.1'',''Password=密码;User ID=sa;Server=IP').databaseName.dbo.tableName " queryout c:\currency.xls -c'
测试过可以执行，但是文件是在数据库服务器上生成的，如果程序跟数据库不在同一台机子上就不要用了。
二：wordsheet.querytable导出
代码如下：
/// <summary>
/// 使用QueryTable从一个外部数据源创建Worksheet
/// </summary>
/// <param name="strSql"></param>
public void ExortToExcel(string strSql,string fileName)
{
string ExportPath =HttpContext.Current.Server.MapPath("~/TempFiles/");
string strFileName = fileName+DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
//新建一Microsoft.Office.Interop.Excel
System.Reflection.Missing missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass objExcel = null;
Microsoft.Office.Interop.Excel.Workbooks objBooks = null;
Microsoft.Office.Interop.Excel.Workbook objBook = null;
Microsoft.Office.Interop.Excel.Worksheet objSheet = null;
try
{
objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
objExcel.Visible = false;
objBooks = (Microsoft.Office.Interop.Excel.Workbooks)objExcel.Workbooks;
objBook = (Microsoft.Office.Interop.Excel.Workbook)(objBooks.Add(missing));
objSheet = (Microsoft.Office.Interop.Excel.Worksheet)objBook.ActiveSheet;
//数据连接，可以是OLEDB或者ODBC
string conn = System.Configuration.ConfigurationSettings.AppSettings["OLEDB"].ToString();
//参数依次为：数据连接，填充起始单元格，查询SQL语句
Microsoft.Office.Interop.Excel.QueryTable tb = objSheet.QueryTables.Add(conn, objSheet.get_Range("A1", missing), strSql);
//设置QueryTable的属性
tb.FieldNames = true;
tb.RowNumbers = false;
tb.FillAdjacentFormulas = false;
tb.PreserveFormatting = true;
tb.RefreshOnFileOpen = false;
tb.BackgroundQuery = false;
tb.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
tb.SavePassword = false;
tb.SaveData = true;
tb.AdjustColumnWidth = true;
tb.RefreshPeriod = 0;
tb.PreserveColumnInfo = true;
//只适用QueryTable基于SQL查询的结果时
//true：异步查询
//false：所有查询结果存入Sheet后返回
tb.Refresh(tb.BackgroundQuery);
//try
//{
//设置Sheet样式等属性
//objSheet.PageSetup.LeftMargin = 20;
//objSheet.PageSetup.RightMargin = 20;
//objSheet.PageSetup.TopMargin = 35;
//objSheet.PageSetup.BottomMargin = 15;
//objSheet.PageSetup.HeaderMargin = 7;
//objSheet.PageSetup.FooterMargin = 10;
//objSheet.PageSetup.CenterHorizontally = true;
//objSheet.PageSetup.CenterVertically = false;
//objSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
//objSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
//objSheet.PageSetup.Zoom = false;
//objSheet.PageSetup.FitToPagesWide = 1;
//objSheet.PageSetup.FitToPagesTall = false;
//}
//catch
//{
//}
//保存excel文件在服务器
//关闭Microsoft.Office.Interop.Excel
objBook.SaveAs(ExportPath + strFileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
objBook.Close(false, missing, missing);
objBooks.Close();
objExcel.Quit();
}
catch (Exception es)
{
}
finally
{
//释放资源
if (!objSheet.Equals(null))
System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet);
if (objBook != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook);
if (objBooks != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objBooks);
if (objExcel != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
GC.Collect();
}
//下载或打开Excel在浏览器端
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-Excel";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.AppendHeader("content-disposition", "attachment;filename=" + strFileName);
this.EnableViewState = false;
HttpContext.Current.Response.WriteFile(ExportPath + strFileName);
HttpContext.Current.Response.End();
}
这种方法不能用存储过程，除了这里用到的直接执行SQL语句还有Table和list两种方式，但是我没有试过，希望有哪位高手能把代码贴出来分享