(原創)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
