none
Passing dictionary colection data dari C# ke Macro RRS feed

  • Pertanyaan

  • Please untuk Master C# , Minta bantuan nya

    saya ingin memasukkan dictionary data dari C# kode ke excel macro.

    kesulitan yang saya alami adalah, ketika variable object yang di masukkan berbentuk dictionary, hasilnya selalu eror, bagaimanakah cara yang paling efisien dan memungkinkan?

    Berikut adalah contoh simple kode macro yg ada dalam modul excel yang saya pake :

    Public Sub dictionaryTest1(dataku As Variant)
    'Contoh dengan Dictionary dataku
    'Dim dataku
    'Set dataku = CreateObject("Scripting.Dictionary")
    Dim I As Integer
    For Each v In dataku.Keys
            MsgBox "Name: " & v & " Age: " & dataku.Item(v)
    Next
    End Sub


    Dan berikut adalah kode C# untuk memanggil macro dan mengirim object data:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Core;
    using System.Reflection;
    using System.IO;
    namespace WindowsMacro
    {
        public partial class Form1 : Form
        {
    	public Form1()
            {
                InitializeComponent();
            }
    		
    	private void RunMacro(object oApp, object[] oRunArgs)
            {
                oApp.GetType().InvokeMember("Run",
                    System.Reflection.BindingFlags.Default |
                    System.Reflection.BindingFlags.InvokeMethod,
                    null, oApp, oRunArgs);
            }
    		
    	private void button1_Click(object sender, EventArgs e)
            {
                Dictionary<string, int> d = new Dictionary<string, int>()
    	        {
    	            {"cat", 2},
    	            {"dog", 1},
    	            {"llama", 0},
    	            {"iguana", -1}
    	        };
                // Simpan dictionary ke dalam List object
                List<string> list = new List<string>(d.Keys);
              
                // Object for missing (or optional) arguments.
                object oMissing = System.Reflection.Missing.Value;
                // Buat instan Excel, Visible dan buka File.xls.
                Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
                oExcel.Visible = true;
                Excel.Workbooks oBooks = oExcel.Workbooks;
                Excel._Workbook oBook = null;
               
    		    //path ke excel file di bin folder
                string executableLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
                string xslLocation = Path.Combine(executableLocation, "File.xls");
    			
                oBook = oBooks.Open(xslLocation, oMissing, oMissing,
                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                //Run macros.
    			RunMacro(oExcel, new Object[] { "dictionaryTest1", list });
                // Quit Excel and clean up.
                oBook.Close(false, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                oBook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                oBooks = null;
                oExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                oExcel = null;
                GC.Collect();   //Garbage collection.
            }
    	}
    }

    Atas bantuan rekan-2, saya ucapkan banyak terima kasih.

    Salam,

    Hadi

    Senin, 27 Agustus 2012 09.49

Jawaban