Going through each cell and updating its data is a takes time.
Excel allows data in an object[,] array to be inserted directly into a range object.
The following C# function inserts data from a dataset quickly.
/// <summary>
/// Add data to the sheets, with the columns names at the top
/// </summary>
/// <param name="dataTable">The data to be added to the sheet</param>
/// <param name="sheetToAddTo">The worksheet to add the data to</param>
/// <returns>The range of the data that has been added</returns>
public static Range AddData(
System.Data.DataTable dataTable, Worksheet sheetToAddTo)
{
//create the object to store the column names
object[,] columnNames;
columnNames = new object[1, dataTable.Columns.Count];
//add the columns names from the datatable
for (int i = 0; i < dataTable.Columns.Count; i++)
{
columnNames[0, i] = dataTable.Columns[i].ColumnName;
}
//get a range object that the columns will be added to
Range columnsNamesRange = sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1]
, sheetToAddTo.Cells[1, dataTable.Columns.Count]);
//a simple assignement allows the data to be transferred quickly
columnsNamesRange.Value2 = columnNames;
//release the columsn range object now it is finished with
System.Runtime.InteropServices.Marshal.ReleaseComObject(columnsNamesRange);
columnsNamesRange = null;
//create the object to store the dataTable data
object[,] rowData;
rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];
//insert the data into the object[,]
for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)
{
rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];
}
}
//get a range to add the table data into
//it is one row down to avoid the previously added columns
Range dataCells = sheetToAddTo.get_Range(sheetToAddTo.Cells[2, 1],
sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);
//assign data to worksheet
dataCells.Value2 = rowData;
//release range
System.Runtime.InteropServices.Marshal.ReleaseComObject(dataCells);
dataCells = null;
//return the range to the new data
return sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1],
sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);
}
Once you have the data in the table, call the following procedure to make a nice table.
/// <summary>
/// This function adds a new Excel table
/// (with nice formatting and filtering etc)
/// to the tableRange passed in. It names the range tableName.
/// This will error if tableName is already used.
/// This function uses XlYesNoGuess.xlYes to
/// automatically make the first row the headings.
/// </summary>
/// <param name="tableRange">Range to convert to a table</param>
/// <param name="tableName">Name of new table</param>
public static void Addtable(Range tableRange, string tableName)
{
Worksheet activeSheet =
(Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
ListObject newList = tableRange.Worksheet.ListObjects.Add(
XlListObjectSourceType.xlSrcRange, tableRange,
null, XlYesNoGuess.xlYes, tableRange);
newList.Name = tableName;
}