Skip to content

Instantly share code, notes, and snippets.

@govert
Last active July 2, 2020 20:45
Show Gist options
  • Save govert/8fc38cf335462a1d9baef8a61f2bba6d to your computer and use it in GitHub Desktop.
Save govert/8fc38cf335462a1d9baef8a61f2bba6d to your computer and use it in GitHub Desktop.
Excel-DNA: Test ExcelReference.SetValue performance
using System;
using System.Diagnostics;
using ExcelDna.Integration;
using static ExcelDna.Integration.XlCall;
namespace SetValuePerf
{
public static class Macros
{
[ExcelCommand(ShortCut = "^D")] // Ctrl + Shift + D
public static void DumpValues()
{
var numWritesInput = Excel(xlfInput, "Number of writes:", 1, "SetValuePerf Test", 20000);
if (numWritesInput is bool)
return;
var numWrites = Convert.ToInt32(numWritesInput);
var sw = Stopwatch.StartNew();
for (int i = 0; i < numWrites; i++)
{
var r = new ExcelReference(i, i, 0, 1);
r.SetValue(new object[,] { { $"Info - {i}", DateTime.Now } });
}
sw.Stop();
new ExcelReference(0, 4).SetValue("Writes / ElapsedMs / Writes per ms:");
new ExcelReference(0, 0, 5, 7).SetValue(new object[,] { { numWrites, sw.ElapsedMilliseconds, (double)numWrites / sw.ElapsedMilliseconds } });
}
[ExcelCommand(ShortCut = "^E")] // Ctrl + Shift + E
public static void DumpValuesEverywhere()
{
var numWritesInput = Excel(xlfInput, "Number of writes:", 1, "SetValuePerf Test", 20000);
if (numWritesInput is bool)
return;
var numWrites = Convert.ToInt32(numWritesInput);
// Add 9 new sheets, named Sheet2 - Sheet10
for (int s = 0; s < 9; s++)
{
Excel(xlcWorkbookInsert, 1);
}
// Scatter some values around the various sheets
var sw = Stopwatch.StartNew();
for (int i = 0; i < numWrites; i++)
{
var r = new ExcelReference(i/10, i/10, 0, 1, $"Sheet{i%10 + 1}");
r.SetValue(new object[,] { { $"Info - {i}", DateTime.Now } });
}
sw.Stop();
Excel(xlcWorkbookActivate, "Sheet1");
new ExcelReference(0, 4).SetValue("Writes / ElapsedMs / Writes per ms:");
new ExcelReference(0, 0, 5, 7).SetValue(new object[,] { { numWrites, sw.ElapsedMilliseconds, (double)numWrites / sw.ElapsedMilliseconds } });
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment