asp.net 調用 excel 組件

Asp.net 如何調用 Excel ?

1.         引用 Microsoft.Office.Interop.Excel.dll，自動封裝成Interop.Microsoft.Office.Interop.Excel.dll

2.         代碼：

/// <summary>

    /// 產生 excel 報表

    /// </summary>

    private void CreateExcelReport()

    {

        string xlTemplateFullPath = Server.MapPath("~/Function/Business/ExcelTemplate/OTOCFPY.xls");

        string xlSavePath = Server.MapPath("~/Function/Business/ExcelReport");

        _Application xlApp = null;

 

        _Workbook xlWorkbook = null;

 

        _Worksheet xlWorksheet = null;

 

 

 

        System.Reflection.Missing oMissing = System.Reflection.Missing.Value;

 

        try

        {

          

            xlApp = new ApplicationClass();

            xlWorkbook = xlApp.Workbooks.Open(xlTemplateFullPath ,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing,

                oMissing);

            xlWorksheet = (Worksheet)xlWorkbook.Worksheets[1];

 

            xlSavePath = Path.Combine(xlSavePath, DateTime.Now.Ticks.ToString());

 

          //寫入excel資料

            WriteData(xlWorksheet );

 

            xlWorkbook.SaveAs(xlSavePath, oMissing, oMissing, oMissing, oMissing,

 

              oMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, oMissing, oMissing, oMissing,

 

            oMissing, oMissing);

            xlApp.Visible = true;

            xlApp.Quit();

              //發送excel 檔案到用戶端

            Response.Clear();

            Response.Buffer = true;

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

            Response.AppendHeader("Content-Disposition", "attachment;filename=todayDownList.xls");

            System.IO.FileInfo fileInfo = new FileInfo(xlSavePath+".xls" );

            Response.AddHeader("Content-Length", fileInfo.Length.ToString());

         

            Response.WriteFile(fileInfo.FullName);

            Response.End();

        }

        catch (Exception ex)

        {

            Response.Write(ex.Message);

        }

 

        finally

        {

 

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

 

            xlApp = null;

 

            GC.Collect();

 

        }

    }

注意點

用完excel後一定要釋放excel