The MUD Connector

Mud Development and Administration => Coder's Forum => Topic started by: papafhill on November 27, 2017, 5:27 PM

Title: A non-"MUD related" coding question (Excel/C#)
Post by: papafhill on November 27, 2017, 5:27 PM
Hey guys,

So Google is being stupid and can't find this information for me.

I'm making a complex program in C# that will execute certain callbacks, but my problem is excel wears the pants and tells my program to wait after every change it makes so that excel can make adjustments.  So if my program is systematically changing data, any dependents of that range will get updated, causing my program to pause for like 250 milliseconds for each cell changed.

I'm trying to find a simple bit of code that will make excel and my program synchronous (preferably, rather than asynch).  I want my program to tell excel to chill out and wait until my callback executes fully before updating any of the conditional formatting or formulae with precedents in the effected range.

I was going to make a class that would store all conditional formatting presets, delete them in excel, run the callback, then reinstate the presets back into excel, but that is a band-aid on a gushing wound (not to mention it doesn't fix the issue of formulae updating mid-routine).  Making excel chill out would optimize my program by probably well over 9,000%.  At the moment, my callbacks are running slowly on any worksheet with formulae/conditional formatting.

Any help would be appreciated!  I'm just looking for synch code to be in the background that is called at the start and end of all my callbacks.

Thanks!
Phil
Title: Re: A non-"MUD related" coding question (Excel/C#)
Post by: blinkdog on November 27, 2017, 8:21 PM
Any help would be appreciated!  I'm just looking for synch code to be in the background that is called at the start and end of all my callbacks.

I haven't done any Windows programming in ~13 years, but I did find this:
https://www.extendoffice.com/documents/excel/1564-excel-stop-auto-calculation.html (https://www.extendoffice.com/documents/excel/1564-excel-stop-auto-calculation.html)

The page contains some VBA code:
Code: [Select]
Private Sub Workbook_Open()
'Updateby20140314
Application.Calculation = XlCalculation.xlCalculationManual
End Sub

This probably has a very close equivalent in C#, Visual Studio will probably help you find it.

So, do the above to turn off recalculation, update all your cells, then turn recalculation back on.

Sorry that I can't be more specific or provide a tested solution.



Title: Re: A non-"MUD related" coding question (Excel/C#)
Post by: papafhill on November 27, 2017, 10:15 PM
The page contains some VBA code:
Code: [Select]
Private Sub Workbook_Open()
'Updateby20140314
Application.Calculation = XlCalculation.xlCalculationManual
End Sub

This probably has a very close equivalent in C#, Visual Studio will probably help you find it.

This looks helpful!  I know there are a bunch of "XL" items in C# that I can play with, not to mention the Application keyword is very applicable (no pun intended) to what I've been doing.  I'll come back here with whether it worked or not, plus how I did it.

Thanks!
Phil
Title: Re: A non-"MUD related" coding question (Excel/C#)
Post by: papafhill on November 28, 2017, 1:20 AM
Code: [Select]
using Excel = Microsoft.Office.Interop.Excel;

// To turn off automatic excel calculations
Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationManual;

// To Reinstate automatic excel calculations
Globals.ThisAddIn.Application.Calculation = Excel.XlCalculation.xlCalculationAutomatic;


These are the lines needed and holy crap does it work SO WELL!  Literally takes something that takes about 30 seconds just to change 50 cell values, to taking about half a second to do that and calculate the formulae and conditional formatting of everything!!!

Thanks so much!
Phil
Title: Re: A non-"MUD related" coding question (Excel/C#)
Post by: blinkdog on November 29, 2017, 10:10 PM
These are the lines needed and holy crap does it work SO WELL!  Literally takes something that takes about 30 seconds just to change 50 cell values, to taking about half a second to do that and calculate the formulae and conditional formatting of everything!!!

I love it when a plan comes together. :cigar:
Title: Re: A non-"MUD related" coding question (Excel/C#)
Post by: Rhien on December 04, 2017, 1:38 PM
This would re-writing on your part, but I use Microsoft's Document.OpenXml package to create XLSX files.  The core benefit of this package is you don't have to have Excel on the computer/server where you're creating spreadsheets which makes it ideal if you ever plan on run your code from a server environment (this will also run with .Net Standard).

https://www.nuget.org/packages/DocumentFormat.OpenXml/