大数据量导出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两种方式，但是我没有试过，希望有哪位高手能把代码贴出来分享