波斯马BOSSMA Information Technology

在使用Com对象或者PIA导出Excel时，对于大数据量的情况，如果一行行的创建，然后添加到Worksheet，速度会比较慢。

还好有别的方法，使用QueryTable查询数据填充Worksheet，速度将有很大的提升，适合数据较多情况的导出。

直接看代码吧，添加了一些注释：

    /// <summary>
    /// 使用QueryTable从一个外部数据源创建Worksheet
    /// </summary>
    /// <param name="strSql"></param>
    private void ExortToExcelQueryTable(string strSql)
    {
        string ExportPath = Server.MapPath("~/");
        string strFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";

        //新建一Microsoft.Office.Interop.Excel
        Missing missing = 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 = "OLEDB;Provider=SQLOLEDB.1;server=192.168.100.155;uid=sa;
password=Asdf1234;database=WebToolsDB;";
            //参数依次为：数据连接，填充起始单元格，查询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 = true;
            tb.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells;
            tb.SavePassword = false;
            tb.SaveData = true;
            tb.AdjustColumnWidth = true;
            tb.RefreshPeriod = 0;
            tb.PreserveColumnInfo = true;
            tb.BackgroundQuery = false;

            //只适用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();
        }
        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在浏览器端
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.ms-Excel";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("content-disposition", "attachment;filename=" + strFileName);

        this.EnableViewState = false;

        Response.WriteFile(ExportPath + strFileName);
        Response.End();
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        ExortToExcelQueryTable("select * from t_apptask");
    }

?

查看QueryTables.Add的官方说明：
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.querytables.add(office.11).aspx

关键字: excel QueryTable 导出excel