Styjun

I am working on some code in which I have to open excels, change some cells values and then re-calculate the formulas of the whole file in order to get two specific values and inform them.

The problem is that I have to do this with a high number of files, all with different names and in different paths, and is taking too much time to create the workbook each time.

Even working with only one excel takes a few seconds to process, and the big stop is in the creation of the workbook object.

Is there some way to speed up this process?

This is my code:

class Program

 private class MyWorkbook : IDisposable
 
 public Workbook Wb get; set; 
 public MyWorkbook(Workbook wb)
 
 Wb = wb;
 

 public void Dispose()
 

 
 

 static void Main(string[] args)
 
 Console.WriteLine("Started: " + DateTime.Now.ToString());

 int i = 0;
 while (i <= 10000)
 
 using (MyWorkbook workbook = new MyWorkbook(new Workbook(@"C:AppDataMyExcelsabcdefg.xlsm")))
 
 workbook.Wb.Settings.CreateCalcChain = false;
 workbook.Wb.Worksheets[1].Cells["M21"].Value = 5;
 workbook.Wb.Worksheets[1].Cells["M22"].Value = 5;
 workbook.Wb.Worksheets[1].Cells["M23"].Value = 5;
 workbook.Wb.CalculateFormula();
 Console.WriteLine(workbook.Wb.Worksheets[1].Cells["L81"].Value);
 
 Console.WriteLine("Index: " + i);
 i++;
 
 Console.WriteLine("Finished: " + DateTime.Now.ToString());
 Console.ReadLine();
 

NOTE: I am using a custom class because the Aspose version is old and Workbook doesn't have Dispose() method.

Thank you.

First of all we recommend you to kindly try using our latest version as it has more enhancements and fixes regarding memory management and time consumption when reading or writing files.
Do you find Aspose.Cells takes more time on instantiation of the Workbook or it also uses more time on calculating formulas in the workbook? I guess you may try some measures to minimize the memory cost and to speed up the processes.

1. 
   

   If the process is taking more time and consuming more memory when opening the file via the API, you may try to set the MemorySetting.MemoryPreference option to optimize memory use for cells data and decrease the overall memory cost. Consequently it may enhance the speed of the process. See the sample code segment for your reference:
   e.g
   Sample code:

   
   
   

   
   

   LoadOptions opt = new LoadOptions();
   // Set the memory preferences
   opt.MemorySetting = MemorySetting.MemoryPreference;
   
   // Instantiate the Workbook
   // Load the Big Excel file having large Data set in it
   Workbook wb = new Workbook(dataDir+ "Book1.xlsx", opt);
   

   
   

   
   



2. Try to use some data filtering options when loading the workbooks. For example, see the document on how to filter data/contents or objects when loading the workbook.



3. 
   

   Try to load your desired sheet(s) and do not load unnecessary worksheets in the workbook. This can improve performance and consume less memory. This approach is useful when working with large workbook made up of many worksheets.
   e.g
   Sample code:

   
   
   

   
   

   // Define a new Workbook.

   
   
   

   Workbook workbook;
   
   // Load the workbook with the spcified worksheet only.
   LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
   loadOptions.LoadFilter = new CustomLoad();
   
   // Creat the workbook.
   workbook = new Workbook(dataDir+ "TestData.xlsx", loadOptions);
   
   // Perform your desired task.
   
   // Save the workbook.
   workbook.Save(dataDir+ "outputFile.out.xlsx");
   ....
   Here is the implementation of the CustomLoad class.
   ........
   class CustomLoad : LoadFilter
   
    public override void StartSheet(Worksheet sheet)
    
    if (sheet.Name == "Sheet2")
    
    // Load everything from worksheet "Sheet2"
    this.LoadDataFilterOptions = LoadDataFilterOptions.All;
    
    else
    
    // Load nothing
    this.LoadDataFilterOptions = LoadDataFilterOptions.None;
    
    
   
   

   
   

   
   

PS. I am working as Support developer/ Evangelist at Aspose.