Dunk

●●●學習筆記及問題處理過程●●●

2015-10-01

[C#]呼叫Excel VBA Macro

[C#]呼叫Excel VBA Macro

紀錄一下過程。

問題描述

之前開發的報表是用Excel VBA將資料庫查詢結果呈現；預計使用C#去呼叫相關VBA函式，產生跑批資料。

建置環境

Visual Studio 2010。

Office 2007。

步驟

1. 建立相關C#函式

參考相關網址

C#调用Excel VBA宏

內容如下


using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
namespace utility.excelmacro
{
    /// <summary>
    /// 執行Excel VBA
    /// </summary>
    public class ExcelMacroHelper
    {
        /// <summary>
        /// 執行Excel中的module
        /// </summary>
        /// <param name="excelFilePath">Excel文件路徑</param>
        /// <param name="macroName">module名稱</param>
        /// <param name="parameters">module參數組</param>
        /// <param name="rtnValue">module返回值</param>
        /// <param name="isShowExcel">執行時是否顯示Excel</param>
        public void RunExcelMacro(
                                            string excelFilePath,
                                            string macroName,
                                            object[] parameters,
                                            out object rtnValue,
                                            bool isShowExcel
                                        )
        {
            try
            {
                #region 檢查入參

                // 檢查文件是否存在
                if (!File.Exists(excelFilePath))
                {
                    throw new System.Exception(excelFilePath + " 文件不存在");
                }

                // 檢查是否輸入module名稱
                if (string.IsNullOrEmpty(macroName))
                {
                    throw new System.Exception("請輸入module的名稱");
                }

                #endregion

                #region 調用module處理

                // 準備打開Excel文件時的缺省參數對象
                object oMissing = System.Reflection.Missing.Value;

                // 根據參數組是否為空，準備參數組對象
                object[] paraObjects;

                if (parameters == null)
                {
                    paraObjects = new object[] { macroName };
                }
                else
                {
                    // module參數組長度
                    int paraLength = parameters.Length;

                    paraObjects = new object[paraLength + 1];

                    paraObjects[0] = macroName;
                    for (int i = 0; i < paraLength; i++)
                    {
                        paraObjects[i + 1] = parameters[i];
                    }
                }

                // 創建Excel對象示例
                Excel.ApplicationClass oExcel = new Excel.ApplicationClass();

                // 判斷是否要求執行時Excel可見
                if (isShowExcel)
                {
                    // 使創建的對象可見
                    oExcel.Visible = true;
                }

                // 創建Workbooks對象
                Excel.Workbooks oBooks = oExcel.Workbooks;

                // 創建Workbook對象
                Excel._Workbook oBook = null;

                // 打開指定的Excel文件
                oBook = oBooks.Open(
                                        excelFilePath,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                        oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing,
                                       oMissing
                                  );

                // 執行Excel中的module
                rtnValue = this.RunMacro(oExcel, paraObjects);

                // 保存更改
                oBook.Save();

                // 退出Workbook
                oBook.Close(false, oMissing, oMissing);

                #endregion

                #region 釋放對象

                // 釋放Workbook對象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                oBook = null;

                // 釋放Workbooks對象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                oBooks = null;

                // 關閉Excel
                oExcel.Quit();

                // 釋放Excel對象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                oExcel = null;

                // 調用垃圾回收
                GC.Collect();

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 執行module
        /// </summary>
        /// <param name="oApp">Excel對象</param>
        /// <param name="oRunArgs">參數（第一個參數為指定module名稱，後面為指定module的參數值）</param>
        /// <returns>module返回值</returns>
        private object RunMacro(object oApp, object[] oRunArgs)
        {
            try
            {
                // 聲明一個返回對象
                object objRtn;

                // 反射方式執行module
                objRtn = oApp.GetType().InvokeMember(
                                                        "Run",
                                                        System.Reflection.BindingFlags.Default |
                                                        System.Reflection.BindingFlags.InvokeMethod,
                                                        null,
                                                        oApp,
                                                        oRunArgs
                                                     );

                // 返回值
                return objRtn;

            }
            catch (Exception ex)
            {
                // 如果有底層異常，拋出底層異常
                if (ex.InnerException.Message.ToString().Length > 0)
                {
                    throw ex.InnerException;
                }
                else
                {
                    throw ex;
                }
            }
        }
    }
}

引用點

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using utility.excelmacro;
using System.IO;
using log4net;
using System.Diagnostics;

namespace batch.basic
{
    /// <summary>
    /// 判斷是否產生報表及相關函式
    /// </summary>
    public class GenReport
    {
        protected ILog log = LogManager.GetLogger(typeof(GenReport));
        /// <summary>
        /// 報表週期
        /// </summary>
        [Flags]
        public enum ReportPeriodEnum
        {
            None = 0,
            Monthly = 2,
            Quarterly = 4,
            Annual = 8,
        };
        #region 參數區
        /// <summary>
        /// 資料日期
        /// </summary>
        private string dataDate = string.Empty;
        /// <summary>
        /// 年報的excel檔案(vba)
        /// </summary>
        private string aFile = string.Empty;
        /// <summary>
        /// 季報的excel檔案(vba)
        /// </summary>
        private string qFile = string.Empty;
        /// <summary>
        /// 月報的excel檔案(vba)
        /// </summary>
        private string mFile = string.Empty;
        /// <summary>
        /// 執行excel vba工具
        /// </summary>
        ExcelMacroHelper excelHelper = null;
        /// <summary>
        /// 報表檔存放的路徑
        /// </summary>
        private string reportFolderPath = string.Empty;
        /// <summary>
        /// 月報預設檔案名稱
        /// </summary>
        private string _monthReportFileName = "M.xls";
        /// <summary>
        /// 季報預設檔案名稱
        /// </summary>
        private string _quarterReportFileName = "Q.xls";
        /// <summary>
        /// 年報預設檔案名稱
        /// </summary>
        private string _annualReportFileName = "A.xls";
        #endregion

        public GenReport()
        {
            if (excelHelper == null)
            {
                excelHelper = new ExcelMacroHelper();
            }
        }

        /// <summary>
        /// 設定報表檔存放的路徑
        /// </summary>
        /// <param name="reportPath"></param>
        public void SetReportPath(string reportPath)
        {
            reportFolderPath = string.Copy(reportPath);
        }
        /// <summary>
        /// 設定年報VBA檔案位置
        /// </summary>
        /// <param name="filename"></param>
        public void SetAnnelBaseFile(string filename)
        {
            aFile = string.Copy(filename);
        }
        /// <summary>
        /// 設定年報VBA檔案位置
        /// </summary>
        /// <param name="filename"></param>
        public void SetQuqrterBaseFile(string filename)
        {
            qFile = string.Copy(filename);
        }
        /// <summary>
        /// 設定年報VBA檔案位置
        /// </summary>
        /// <param name="filename"></param>
        public void SetMonthBaseFile(string filename)
        {
            mFile = string.Copy(filename);
        }

        /// <summary>
        /// 是否產生月報(預設為是)
        /// </summary>
        /// <returns></returns>
        public bool GenMonthReport()
        {
            return true;
        }
        /// <summary>
        /// 是否產生年報
        /// </summary>
        public bool GenAnnelReport()
        {
            return (dataDate.Substring(4, 2) == "06" || dataDate.Substring(4, 2) == "12") ? true : false;
        }

        /// <summary>
        /// 是否產生季報
        /// </summary>
        public bool GenQuarterReport()
        {
            return (dataDate.Substring(4, 2) == "03" || dataDate.Substring(4, 2) == "06"
            || dataDate.Substring(4, 2) == "09" || dataDate.Substring(4, 2) == "12") ? true : false;
        }

        /// <summary>
        /// Gets the name of the month report file.
        /// </summary>
        /// <param name="date">The date(YYYYMMDD).</param>
        /// <returns></returns>
        public string GetMonthReportFileName(string date)
        {
            return date.Substring(0, 6) + "_" + _monthReportFileName;
        }
        /// <summary>
        /// Gets the name of the quarter report file.
        /// </summary>
        /// <param name="date">The date(YYYYMMDD).</param>
        /// <returns></returns>
        public string GetQuarterReportFileName(string date)
        {
            if (date.Substring(4, 2) == "01" || date.Substring(4, 2) == "02" || date.Substring(4, 2) == "03")
            {
                return date.Substring(0, 4) + "Q1" + "_" + _quarterReportFileName;
            }
            else if (date.Substring(4, 2) == "04" || date.Substring(4, 2) == "05" || date.Substring(4, 2) == "06")
            {
                return date.Substring(0, 4) + "Q2" + "_" + _quarterReportFileName;
            }
            else if (date.Substring(4, 2) == "07" || date.Substring(4, 2) == "08" || date.Substring(4, 2) == "09")
            {
                return date.Substring(0, 4) + "Q3" + "_" + _quarterReportFileName;
            }
            else
            {
                return date.Substring(0, 4) + "Q4" + "_" + _quarterReportFileName;
            }
        }

        /// <summary>
        /// Gets the name of the annual report file.
        /// </summary>
        /// <param name="date">The date.</param>
        /// <returns></returns>
        public string GetAnnualReportFileName(string date)
        {
            return date.Substring(0, 4) + "_" + _annualReportFileName;
        }
        /// <summary>
        /// Gets the name of the report file.
        /// </summary>
        /// <returns></returns>
        public string GetReportFileName(ReportPeriodEnum selectedReportPeriod)
        {
            string fileName = string.Empty;
            string pathReport = Path.Combine(reportFolderPath, selectedReportPeriod.ToString());
            if (Directory.Exists(pathReport) == false)
            {
                Directory.CreateDirectory(pathReport);
            }
            switch (selectedReportPeriod)
            {
                case ReportPeriodEnum.Monthly:
                    fileName = Path.Combine(pathReport, GetMonthReportFileName(dataDate));
                    break;
                case ReportPeriodEnum.Quarterly:
                    fileName = Path.Combine(pathReport, GetQuarterReportFileName(dataDate));
                    break;
                case ReportPeriodEnum.Annual:
                    fileName = Path.Combine(pathReport, GetAnnualReportFileName(dataDate));
                    break;
                default:
                    break;
            }
            return fileName;
        }
        /// <summary>
        /// 產生報表
        /// </summary>
        /// <param name="inDataDate"></param>
        public void Generate(string inDataDate)
        {
            dataDate = string.Copy(inDataDate);
            object objRtn = new object();
            log.Info("Start Generate");
            string resultFileName = string.Empty;
            try
            {
                //月報
                if (GenMonthReport() == true)
                {
                    log.Info("Start GenMonthReport");
                    resultFileName = GetReportFileName(ReportPeriodEnum.Monthly);
                    log.Info("filename : " + resultFileName);


                    excelHelper.RunExcelMacro(mFile,
                    "GenReport", new object[] { dataDate }, out objRtn, false);
                    log.Info("GenReport Result : " + objRtn.ToString());


                    excelHelper.RunExcelMacro(mFile,
                   "SaveReportToNewFile", new object[] { resultFileName }, out objRtn, false);
                    log.Info("SaveReportToNewFile Result : " + objRtn.ToString());


                    log.Info("END GenMonthReport");
                }
                //季報
                if (GenQuarterReport() == true)
                {
                    log.Info("Start GenQuarterReport");
                    resultFileName = GetReportFileName(ReportPeriodEnum.Quarterly);
                    log.Info("filename : " + resultFileName);


                    excelHelper.RunExcelMacro(qFile,
                    "GenReport", new object[] { dataDate }, out objRtn, false);
                    log.Info("GenReport Result : " + objRtn.ToString());

                    excelHelper.RunExcelMacro(qFile,
                   "SaveReportToNewFile", new object[] { resultFileName }, out objRtn, false);
                    log.Info("SaveReportToNewFile Result : " + objRtn.ToString());


                    log.Info("END GenQuarterReport");
                }
                //年報，半年報
                if (GenAnnelReport() == true)
                {
                    log.Info("Start GenAnnelReport");
                    resultFileName = GetReportFileName(ReportPeriodEnum.Annual);
                    log.Info("filename : " + resultFileName);


                    excelHelper.RunExcelMacro(aFile,
                    "GenReport", new object[] { dataDate }, out objRtn, false);
                    log.Info("GenReport Result : " + objRtn.ToString());

                    excelHelper.RunExcelMacro(aFile,
                   "SaveReportToNewFile", new object[] { resultFileName }, out objRtn, false);
                    log.Info("SaveReportToNewFile Result : " + objRtn.ToString());

                    log.Info("END GenAnnelReport");
                }
                log.Info("END Generate");
            }
            catch (Exception ex)
            {
                //把啟動的Excel關閉
                foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcesses())
                {
                    if (proc.ProcessName == "EXCEL")
                    {
                        proc.Kill();
                    }
                }
                log.Error(ex.Message);
                throw ex;
            }
        }



    }
}

2. 準備Excel VBA Macro

內容如下，VBA Macro要放在模組底下，寫在ThisWorkbook沒辦法被識別：

image



'產生報表
Public Function GenReport(ByVal dataDate As String) As String
 '設定資料日期
 On Error GoTo ERRORHANDLE1
 Call MenuGenMrpt.SetDataDate(dataDate)
 
 '重新設定畫面
 On Error GoTo ERRORHANDLE2
 Call MenuGenMrpt.CmdReset_Click
 
 '讀取報表資料
 On Error GoTo ERRORHANDLE3
 Call MenuGenMrpt.CmdGenMP_Click
 
 '針對金額欄位進行格式調整
 On Error GoTo ERRORHANDLE4
 Call MenuGenMrpt.CmdRound_Click
   
 '關閉資料庫連線及畫面
 On Error GoTo ERRORHANDLE5
 Call MenuGenMrpt.CmdExit_Click
 GoTo ALLOK

ERRORHANDLE1:
    GenReport = "設定資料日期發生錯誤!"
ERRORHANDLE2:
    GenReport = "重新設定畫面發生錯誤!"
ERRORHANDLE3:
    GenReport = "讀取報表資料發生錯誤!"
ERRORHANDLE4:
    GenReport = "針對金額欄位進行格式調整發生錯誤!"
ERRORHANDLE5:
    GenReport = "關閉資料庫連線及畫面發生錯誤!"
ALLOK:
    GenReport = "Finish!"
End Function

'將產生結果另存新檔
Public Function SaveReportToNewFile(ByVal filename As String)

'另存新檔
Application.DisplayAlerts = False
 On Error GoTo ERRORHANDLE6
 Call FileSave.SaveAllSheetToNewFile(filename)
Application.DisplayAlerts = True
 
GoTo ALLOK
 
ERRORHANDLE6:
    SaveReportToNewFile = "另存新檔發生錯誤!"
ALLOK:
    SaveReportToNewFile = "Finish!"
End Function

3. 關閉安全性警告

開啟excel。

clip_image002

Excel選項。

clip_image003

信任中心→信任中心設定。

clip_image005

新增Excel報表底稿存放的位置。

clip_image006

clip_image008

開啟時就不會發出警告安全性。

clip_image010

clip_image012

結果畫面

將相關跑批結果用log紀錄，有正常產生。

image

注意事項

參考Excel物件版本為12.0(office 2007)，視實際使用的Office版本調整。

clip_image003

參考資料

C#调用Excel VBA宏

在 Excel 2007 中的預設為停用的 Visual Basic for Applications

建立、移除或變更檔案的信任位置