c#執(zhí)行excel宏模版的方法

更新時(shí)間：2014年01月20日 16:30:46 作者：

這篇文章主要介紹了c#執(zhí)行excel宏模版的方法，導(dǎo)出Excel模版的代碼如下，大家參考使用吧

在項(xiàng)目中如果涉及到用Excel開發(fā)的報(bào)表模版來導(dǎo)出報(bào)表數(shù)據(jù)的話，一般都是在Excel報(bào)表中使用VBA做成宏來進(jìn)行調(diào)用。即先使用Excel自帶的VBA開發(fā)好報(bào)表模版，然后再將模版程序放在固定目錄下，在管理軟件中對這些報(bào)表模版進(jìn)行調(diào)用。這樣就不需要使用其它報(bào)表工具來進(jìn)行開發(fā)了，因?yàn)镋xcel功能比較強(qiáng)大，一般的應(yīng)用的報(bào)表用它來開發(fā)綽綽有余了。而且這樣開發(fā)的成本也很低，不需要再購買其它專業(yè)的報(bào)表開發(fā)軟件直接使用Office開發(fā)即可。使用Excel開發(fā)的宏模版當(dāng)然要使用上層程序來調(diào)用宏模版了。我以前在工作中就開發(fā)過Excel模版，并使用C#程序調(diào)用過這些模版程序。規(guī)則是首先將報(bào)表需要的數(shù)據(jù)填沖到DataTable或者DataSet中，然后將DataTable或者DataSet中數(shù)據(jù)導(dǎo)出Excel中。
現(xiàn)提供導(dǎo)出Excel模版的代碼如下：

復(fù)制代碼代碼如下:

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
       //將報(bào)表所需的主數(shù)據(jù)放入dtHeader，明細(xì)數(shù)據(jù)放入dtDetail中，調(diào)用的模版名稱為strTemplateFileName，導(dǎo)出的報(bào)表名稱為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分別對應(yīng)模版文件中的Header和Detail兩個(gè)Sheet頁，這2個(gè)Sheet是專門用來存放明主數(shù)據(jù)或者明細(xì)數(shù)據(jù)的。
                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;
        }
        //將報(bào)表所需的主數(shù)據(jù)放入dtHeader，明細(xì)數(shù)據(jù)放入dtDetail中，調(diào)用的模版名稱為strTemplateFileName，導(dǎo)出的報(bào)表名稱為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.注意這里的沒有寫明對應(yīng)模版文件中Sheet頁，但是指定了dsdata.Tables[i].TableName為Sheet頁的名字，這樣方便靈活設(shè)置，而且這樣可以有多個(gè)Sheet是專門用來存放明主數(shù)據(jù)或者明細(xì)數(shù)據(jù)的。
                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();