asp.net 呼叫 excel 元件

Asp.net 如何呼叫 Excel ?

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

2.         程式碼：

///

    /// 生成 excel 報表

    ///

    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 Missing = 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