a亚洲精品_精品国产91乱码一区二区三区_亚洲精品在线免费观看视频_欧美日韩亚洲国产综合_久久久久久久久久久成人_在线区

首頁 > 編程 > C# > 正文

c#執行excel宏模版的方法

2020-01-24 02:51:46
字體:
來源:轉載
供稿:網友

在項目中如果涉及到用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;
            }
        }
}

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板: 国产一级片在线播放 | 亚洲欧美日韩天堂 | 激情av| 四虎最新紧急更新地址 | 一区二区三区免费看 | 午夜视频网站 | 日韩av在线导航 | 精品日韩视频 | 91在线资源 | 久久99精品久久久久久园产越南 | 成人在线免费观看视频 | 欧美日韩国产一区二区三区不卡 | 视频一区 中文字幕 | 国产九九精品 | 亚洲免费在线视频 | 日韩视频一区 | 日韩一区二区在线观看视频 | 日本视频免费高清一本18 | 日韩欧美在线观看 | 亚洲一区在线免费观看 | 国产精品视频导航 | 中文字幕在线免费视频 | 日韩经典一区 | 国产精品久久久久久久 | 免费在线亚洲 | 欧美日韩国产高清 | 日韩福利在线 | 国产精品久久国产精品 | 国产免费黄网站 | 午夜精品导航 | 成人深夜小视频 | 国产精品www| 国产 日韩 欧美 在线 | 中文字幕日韩高清 | 精品国产一区二区三区电影小说 | 天天天天天天天天干 | 国产在线观看免费av | 黄色片在线免费播放 | 91国内外精品自在线播放 | 国产日韩视频在线播放 | 日韩国产一区二区 |