c#执行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;
            }
        }
}