There is a code that accesses the xml file, extracts data from it into the f01x array, and processes this array. The code is tied to Excel via DNA. A function has been created that contains a number of arguments. The function works fine, but very slowly. The problem is that if you need to use the function 1500 times (in different cells and with different periodicity), then a huge part of the time (cumulatively 30+ minutes) takes exactly the connection to the xml file (on the C drive).

The idea that came to my mind is to once read the xml file into an array and leave this f01x array in RAM, and later, when the same function is started, they are in another Excel cell (say, 5 minutes after the first launch) , check if the function's arguments are equal to some cells in the array, then we get the f01x array from the PC's RAM (if the cleaner does not overwrite it), bypassing reading from the C: \ 01.xml file into the array, and if the function arguments are not equal to some cells of the array , then the array f01x is filled from the xml file.

What I would like to implement should look like this:

  1. We get all the arguments to the function.
  2. Check if there is an array of f01x in RAM.
  3. If the argument of the function "Date" is equal to a certain cell of the array, then we take an array from RAM, otherwise, we fill the array from the xml file.

Initially, I want to deal with RAM, and in the case where the array is there, take it from RAM, if it is not there, read it from xml. Later, I will finish the comparison of arguments.

Tell me, please, how to do this? Maybe someone has similar examples, please share. It is necessary that the f01X array remains at the output (filled either from the file, if the first function call occurs, or from RAM, if the function was already called before (5-10-20 minutes ago)). Help me please.

After some discussions, the following simple code was born:

namespace C_Sharp { public class Ground { // Создаю приватное поле в классе Ground private string[,] f01x; // Создаю конструктор класса с параметрами и в конструкторе заполняю поле из файла public Ground(DateTime data, string ekv) { XmlDataDocument xmldoc = new XmlDataDocument(); XmlNodeList xmlnode; FileStream fs = new FileStream(@"C:\01.xml", FileMode.Open, FileAccess.Read); xmldoc.Load(fs); xmlnode = xmldoc.GetElementsByTagName("DATA"); string[,] f01x = new string[xmlnode.Count, 11]; f01x[2, 5] = xmlnode[2].ChildNodes.Item(6).InnerText.Trim(); } [ExcelFunction(Description = "Hello", IsVolatile = true)] public static string func01( [ExcelArgument(Name = "Дата", Description = "День месяца")] DateTime data, [ExcelArgument(Name = "Капитал", Description = "Эквити")] string ekv) { // !!!! Так ругается компилятор // !!!! Для нестатического поля, метода или свойства требуется ссылка на объект return f01x[2, 5]; } } }
  • 2
    Well, make f01X welcoming field of the class and check if it is not initialized - then read it from the file, if initialized - then do nothing. - tym32167
  • When the array is processed and the specified value "return x;" the code will complete its work. The function naturally completes the work as well. And here it would be desirable that when you call the function again, we no longer read the 01.xml file in the f01x array, but received the f01x array from the RAM. Is it even possible to do this in C #? - Cherry
  • Of course, the function can work, but the class from its field will remain in memory - tym32167 1:56 pm
  • Do I understand correctly that we need to bring all this into a separate class, pass some function arguments to this class and return an array return f01x in this separate class; ? - Cherry
  • Your method is already in some class. You need to output it somewhere or not. I cannot know, I am not aware of the organization of the code in your project - tym32167

1 answer 1

Your function must be in a class because it is C #.

Do the following:

In this class (as suggested by @ tym32167) create a private field. Create a constructor class with parameters and in the constructor fill in the field from the file.

Then you can call the class methods and use this variable. If the methods are not static, then you don’t even have to check the field for null; after creating the object, the field will already be filled in using the constructor.

As long as you have a link to a class instance, the GC will not touch either the class or the field (because there is a link).

If the function is in main, then I would advise to pull it all into a separate class.

Addition after topic update

As you have already been advised in the comments (@ tym32167) you have several options:

1) remove static from the function

 public string func01(...)

then the challenge will be

 Ground ground = new Ground(...); ... string temp = ground.func01(...);

2) change the class to static or only the field to static, but for this you will have to change the code of the constructor and correct the class itself

Judging by the task you described, I would prefer option 1.

  • Thank. Something is clear. With the designers as such, there was no practice. I'll try to figure it out, add code. - Cherry
  • write in lichku / telegram / here - I will try to prompt if something is unclear - Dejsving
  • Slightly corrected the topic, added the received code to it. Look here please. When compiling, the compiler generates the error "For a non-static field, method, or property, an object reference is required." It's already night, and I'm sitting with the code, trying to figure out how to refer to the array in the constructor. - Cherry