Skip to content

Instantly share code, notes, and snippets.

@govert
Created June 28, 2012 16:47
Show Gist options
  • Save govert/3012438 to your computer and use it in GitHub Desktop.
Save govert/3012438 to your computer and use it in GitHub Desktop.
Excel-DNA - Range processing via COM, with .NET 4 'dynamic'
<DnaLibrary Name="RangeConvert" Language="C#" RuntimeVersion="v4.0">
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;
public static class TestRangeMacros
{
[ExcelCommand(MenuName="Test Range Macros", MenuText="Double the Range")]
public static void RangeDouble()
{
dynamic xlApp;
dynamic inRange;
dynamic outRange;
object[,] inValues;
object[,] outValues;
try
{
xlApp = ExcelDnaUtil.Application;
inRange = xlApp.InputBox("Range to read: ", Type: 8);
outRange = xlApp.InputBox("Range to write: ", Type: 8);
if (inRange != null && outRange != null)
{
inValues = inRange.Value;
MessageBox.Show("InValues: " + inValues.GetType() + " " + inValues.GetLength(0).ToString() + ", " + inValues.GetLength(1).ToString());
outValues = new object[ inValues.GetLength(0) , inValues.GetLength(1) ];
for (int i = inValues.GetLowerBound(0); i <= inValues.GetUpperBound(0); i++)
{
int ii = i - inValues.GetLowerBound(0);
for (int j = inValues.GetLowerBound(1); j <= inValues.GetUpperBound(1); j++)
{
int jj = j - inValues.GetLowerBound(1);
if (inValues[i,j] is double)
outValues[ii,jj] = (double)inValues[i,j] * 2.0;
else
outValues[ii,jj] = "!ERROR";
}
}
MessageBox.Show("OutValues: " + outValues.GetType() + " " + outValues.GetLength(0).ToString() + ", " + outValues.GetLength(1).ToString());
MessageBox.Show("OutValues[0,0]: " + outValues[0,0].ToString());
outRange.Value2 = outValues;
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
}
]]>
</DnaLibrary>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment