C#執行Excel宏模版的方法

在項目中如果涉及到用Excel開發的報表模版來導出報表數據的話，一般都是在Excel報表中使用VBA做成宏來進行調用。即先使用Excel自帶的VBA開發好報表模版，然後再將模版程序放在固定目錄下，在管理軟件中對這些報表模版進行調用。這樣就不需要使用其它報表工具來進行開發了，因為Excel功能比較強大，一般的應用的報表用它來開發綽綽有余了。而且這樣開發的成本也很低，不需要再購買其它專業的報表開發軟件直接使用Office開發即可。使用Excel開發的宏模版當然要使用上層程序來調用宏模版了。我以前在工作中就開發過Excel模版，並使用C#程序調用過這些模版程序。規則是首先將報表需要的數據填沖到DataTable或者DataSet中，然後將DataTable或者DataSet中數據導出Excel中。

現提供導出Excel模版的代碼如下：

namespace ExcelTest
{
    public class ExelTemplate
    {
        private static Excel.Application ExcelApp;//Define a Excel Application object
        private static Excel._Workbook ExcelWB;//define a Excel workbook object
        private static Excel._Worksheet ExcelWS;//define a Excel workbook worksheet

       //將報表所需的主數據放入dtHeader，明細數據放入dtDetail中，調用的模版名稱為strTemplateFileName，導出的報表名稱為strOutFileName

        public static bool FillContent(string strTemplateFileName, string strOutFileName, System.Data.DataTable dtHeader, System.Data.DataTable dtDetail)
        {

            bool flag = true;
            FileInfo fileInfo = new FileInfo(strTemplateFileName);

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            try
            {
                //Start Excel and get Application object.               
                ExcelApp = new Excel.Application();
                ExcelApp.Visible  = false;
             
                //Get a new workbook.
                ExcelWB = (Excel._Workbook)(ExcelApp.Workbooks.Add(strTemplateFileName));
               

                //Fill content.注意這裡的Header和Detail分別對應模版文件中的Header和Detail兩個Sheet頁，這2個Sheet是專門用來存放明主數據或者明細數據的。
                if (!FillWorksheet("Header", dtHeader)) return false;
                if (!FillWorksheet("Detail", dtDetail)) return false;

                //Run macro.
               
                ExcelApp.Run("SetData", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
              
                ExcelApp.DisplayAlerts = false;
                //=========================================================================================================
                //Delete Header and Detail.
                //((Excel._Worksheet)ExcelWB.Sheets["Header Information"]).Delete();               
                //((Excel._Worksheet)ExcelWB.Sheets["Detail Information"]).Delete();

 

                //=========================================================================================================
                ExcelApp.DisplayAlerts = true;

                //Delete old file.
                File.Delete(strOutFileName);
                //Save excel file.
                ExcelWB.SaveAs(strOutFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
               
                // Quit Excel.
                ExcelWB.Close(false, strOutFileName, Missing.Value);//*******************             

                MarshalReleaseComObject(ExcelWB);

                ExcelApp.Quit();
               
               
                //Kill excel application.
                //KillProcess("EXCEL");//******************
              
            }
            catch (Exception ex)
            {
                throw ex;  
                flag = false;
            }
            finally
            {
                MarshalReleaseComObject(ExcelApp);
                GC.Collect();
            }

            return flag;
        }

        //將報表所需的主數據放入dtHeader，明細數據放入dtDetail中，調用的模版名稱為strTemplateFileName，導出的報表名稱為strOutFileName

        public static bool FillContent(string strTemplateFileName, string strOutFileName, System.Data.DataSet dsdata)
        {

            bool flag = true;
            FileInfo fileInfo = new FileInfo(strTemplateFileName);

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            try
            {
                //Start Excel and get Application object.               
                ExcelApp = new Excel.Application();
                ExcelApp.Visible = false;

                //Get a new workbook.
                 ExcelWB = (Excel._Workbook)(ExcelApp.Workbooks.Add(strTemplateFileName));
                 //Fill content.
                //if (!FillWorksheet("Header", dtHeader)) return false;
                //if (!FillWorksheet("Detail", dtDetail)) return false;

                //Fill content.注意這裡的���有寫明對應模版文件中Sheet頁，但是指定了dsdata.Tables[i].TableName為Sheet頁的名字，這樣方便靈活設置，而且這樣可以有多個Sheet是專門用來存放明主數據或者明細數據的。
                for (int i = 0; i < dsdata.Tables.Count; i++)
                {
                    if (!FillWorksheet(dsdata.Tables[i].TableName, dsdata.Tables[i]))
                        return false;
                }

                //Run macro.
             
                ExcelApp.Run("SetData", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
               
                ExcelApp.DisplayAlerts = false;
                //=========================================================================================================
                //Delete Header and Detail.
                //((Excel._Worksheet)ExcelWB.Sheets["Header Information"]).Delete();               
                //((Excel._Worksheet)ExcelWB.Sheets["Detail Information"]).Delete();

 

                //=========================================================================================================
                ExcelApp.DisplayAlerts = true;

                //Delete old file.
                File.Delete(strOutFileName);
                //Save excel file.
               
                ExcelWB.SaveAs(strOutFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
               
                // Quit Excel.
                ExcelWB.Close(false, strOutFileName, Missing.Value);//*******************             

                MarshalReleaseComObject(ExcelWB);

                ExcelApp.Quit();
             

            }
            catch (Exception ex)
            {
                throw ex;
                flag = false;
            }
            finally
            {
                MarshalReleaseComObject(ExcelApp);
                GC.Collect();
            }

            return flag;
        }

        //使用Excel來導出報表時，在服務器上的Excel進程容易死掉，導致資源無法釋放，因此需要使用這個方法來釋放死掉的進程

        private static void MarshalReleaseComObject(object objCom)
        {
            try
            {
                int i = 1;
                if (objCom != null && System.Runtime.InteropServices.Marshal.IsComObject(objCom))
                {
                    do
                    {
                        i = System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom);
                    } while (i > 0);
                }
            }
            finally
            {
                objCom = null;
            }
        }

}