none
Pool Values in Memory RRS feed

  • Question

  • Hi,

    I have recently created an Excel UDF (User Defined Function) with .NET. (For details: http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx)

    Whenever it is triggered it logs/appends the input text in a text file.

    Like:

    And the output is:

    But whenever i trigger this function, it opens the textfile and closes it which is a performance hit over 500 records. So i thought to pool the values in memory. But i have absolutely no idea...

    Btw simple code is below:

    using System;
    using System.Runtime.InteropServices;
    using Microsoft.Win32;
    
    namespace AutomationAddin
    {
    
     // Replace the Guid below with your own guid that
     // you generate using Create GUID from the Tools menu
     [Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")] 
     [ClassInterface(ClassInterfaceType.AutoDual)]
     [ComVisible(true)]
     public class MyFunctions
     {
      public MyFunctions()
      {
      }
    
      public bool MyDOTNETUDF(string TextToWrite)
      {
    		// 1: Append single line to new file
    		using (StreamWriter writer = new StreamWriter("C:\\deneme.txt", true))
    		{
    			writer.WriteLine(TextToWrite);
    		}
    
       return true;
      }
    
      [ComRegisterFunctionAttribute]
      public static void RegisterFunction(Type type)
      {
    
       Registry.ClassesRoot.CreateSubKey(
        GetSubKeyName(type, "Programmable"));
       RegistryKey key = Registry.ClassesRoot.OpenSubKey(
        GetSubKeyName(type, "InprocServer32"), true);
       key.SetValue("",
        System.Environment.SystemDirectory + @"\mscoree.dll",
        RegistryValueKind.String);
      }
      [ComUnregisterFunctionAttribute]
      public static void UnregisterFunction(Type type)
      {
    
       Registry.ClassesRoot.DeleteSubKey(
        GetSubKeyName(type, "Programmable"), false);
      }
      private static string GetSubKeyName(Type type,
       string subKeyName)
      {
       System.Text.StringBuilder s =
        new System.Text.StringBuilder();
       s.Append(@"CLSID\{");
       s.Append(type.GUID.ToString().ToUpper());
       s.Append(@"}\");
       s.Append(subKeyName);
       return s.ToString();
      } 
     }
    }
    


     


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me
    • Moved by CoolDadTx Thursday, August 18, 2011 1:26 PM Office related (From:Visual C# General)
    Thursday, August 18, 2011 11:02 AM

Answers

  • Hello,

    Instead of writing TextToWrite to the file, you can add it to a string defined on the class level (also add @"\n\r" or @"\n"). The resulting string can be writen to the file on a condition such as "the number of calls exceeds a predefined value" or "the length of the resulting string exceeds...".


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Friday, August 19, 2011 6:50 AM