(原創)C#使用QueryTables導出到Excel

 

private void ExortToExcel1(string strSql)

                     {

                                string ExportPath = Request.PhysicalApplicationPath + "Temp\\";

                                string strFileName = Session.SessionID+DateTime.Now.ToString("yyyyMMdd hhmmss")+".xls";

                                //新建一Excel應用程式

                                Missing missing = Missing.Value;

                                Excel.ApplicationClass objExcel = null;

                                Excel.Workbooks objBooks = null;

                                Excel.Workbook objBook = null;

                                Excel.Worksheet objSheet = null;

                                try

                                {

                                           objExcel = new Excel.ApplicationClass();

                                           objExcel.Visible = false;

                                           objBooks = (Excel.Workbooks)objExcel.Workbooks;

                                           objBook = (Excel.Workbook)(objBooks.Add(missing));

                                           objSheet = (Excel.Worksheet)objBook.ActiveSheet;

 

                                           string conn = "ODBC;DRIVER={Oracle in OraHome92};SERVER=MCM;UID=SFC;PWD=SFC;DBQ=MCM;";

                                           Excel.QueryTable tb = objSheet.QueryTables.Add(conn,objSheet.get_Range("A1", missing),strSql);

                                           //tb.Name = "來自 FCNP 的查詢";

                                           tb.FieldNames = true;

                                           tb.RowNumbers = false;

                                           tb.FillAdjacentFormulas = false;

                                           tb.PreserveFormatting = true;

                                           tb.RefreshOnFileOpen = false;

                                           tb.BackgroundQuery = true;

                                           tb.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;

                                           tb.SavePassword = false;

                                           tb.SaveData = true;

                                           tb.AdjustColumnWidth = true;

                                           tb.RefreshPeriod = 0;

                                           tb.PreserveColumnInfo = true;

                                           tb.BackgroundQuery = false;

                                           tb.Refresh(tb.BackgroundQuery);

                                                    

 

                                           try

                                           {

                                                     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 = Excel.XlPageOrientation.xlPortrait;

                                                     objSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;

                                                     objSheet.PageSetup.Zoom = false;

                                                     objSheet.PageSetup.FitToPagesWide = 1;

                                                     objSheet.PageSetup.FitToPagesTall = false;

                                           }

                                           catch

                                           {

                                           }

                                           //關閉Excel

                                           objBook.SaveAs(ExportPath+strFileName, missing, missing, missing, missing, missing,Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);

                                           objBook.Close(false,missing,missing);

                                           objBooks.Close();

                                           objExcel.Quit();

                                }

                                finally

                                {                                        

                                           //System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);

                                           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();

                                }

                                //保存或打開報表

                                Response.Clear();

                                Response.Buffer= true;

                                Response.ContentType = "application/vnd.ms-excel";

                                Response.ContentEncoding = System.Text.Encoding.UTF8;                               

                                Response.AppendHeader("content-disposition","attachment;filename="+strFileName+".xls");

                                Response.Charset = "";

                                this.EnableViewState = false;

                                Response.WriteFile(ExportPath+strFileName);

                                Response.End();     

                     }

註：必須安裝odbc for oracle