If you want to copy a set of data from a DataSet to cells in a worksheet, the
best way to do this is to copy the data to an array, and then to copy the
array to the Value2 property of a range that has the same size as the array.
This is much faster than looping through the data and setting cell values
individually. Eric Carter blogs about this too:
http://blogs.msdn.com/eric_carter/archive/2005/03/16/396886.aspx
The following code demonstrates a simple example. The code creates a simple
DataSet, copies the contents of the DataTable in the DataSet to a
two-dimensional array (the example does this by looping through the DataTable
- there might be an easier way to do this, as I'm not an expert in this
area), and then assigns the values in the array to a range in one simple
operation. This code just copies the data to a range beginning at cell "A1",
but you can of course specify any range in the workbook, as long as the range
has the same size as the array.
I hope this helps!
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
#region VSTO generated code
this.Application =
(Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application);
#endregion
// Get the sample DataSet. For simplicity, assume we're just
using the first DataTable.
DataSet ds = CreateSampleDataSet();
DataTable dt = ds.Tables[0];
int numberOfRows = dt.Rows.Count;
int numberOfColumns = dt.Columns.Count;
object[,] dataArray = new object[numberOfRows, numberOfColumns];
for (int i = 0; i < numberOfRows; i++)
{
for (int j = 0; j < numberOfColumns; j++)
{
dataArray[i, j] = dt.Rows[i][j];
}
}
// Import the data into a range with the same size as the array.
Excel.Range theRange =
((Excel.Worksheet)this.Application.ActiveSheet).get_Range("A1", "B2");
theRange.Value2 = dataArray;
}
private DataSet CreateSampleDataSet()
{
// Create and populate a simple DataSet with 2 rows.
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Customers");
dt.Columns.Add(new DataColumn("LastName"));
dt.Columns.Add(new DataColumn("FirstName"));
DataRow dr = dt.NewRow();
dr["LastName"] = "Chan";
dr["FirstName"] = "Gareth";
dt.Rows.Add(dr);
DataRow dr2 = dt.NewRow();
dr2["LastName"] = "Applewood";
dr2["FirstName"] = "Gary";
dt.Rows.Add(dr2);
return ds;
}
Post by ADHi Harry,
Thanks for the post, looks quite interesting. Never thought C# and VB
would be that far apart... I was actually hoping that the latest VSTO
would be more aligned with the .Net than with Office (which I thought
was the purpose of VSTO :))
I've also found ((Range)Globals.ThisAddIn.Application.Cells[x,
y]).Value2 to work quite well as I am looping, do you know which way
is the best to copy a DataSet to a worksheet? At the moment I'm
looping through every column in every row, which I do not like...