SpreadsheetGear 2023
CopyFromDataTable Method (IRange)

SpreadsheetGear Namespace > IRange Interface : CopyFromDataTable Method
Specifies the source DataTable.
Specifies options including All Text values, No Column Headers and Insert Cells.
Copies the specified DataTable to this range.
Sub CopyFromDataTable( _
   ByVal dataTable As System.Data.DataTable, _
   ByVal flags As SetDataFlags _
Dim instance As IRange
Dim dataTable As System.Data.DataTable
Dim flags As SetDataFlags
instance.CopyFromDataTable(dataTable, flags)
void CopyFromDataTable( 
   System.Data.DataTable dataTable,
   SetDataFlags flags


Specifies the source DataTable.
Specifies options including All Text values, No Column Headers and Insert Cells.

If this range is a single column, the destination range will be expanded to the number of columns in the DataTable.

If this range is a single row, the destination will be expanded to the number of rows in the DataTable, plus one row for the column headers if needed.

Note that you must specify a single data row and allow the destination size to be determined automatically if you use SpreadsheetGear.Data.SetDataFlags.WrapToNewWorksheet to enable wrapping rows to the next worksheet if they do not fit on the current worksheet.

Using Formats and Formulas
To preformat a range and have formulas refer to columns in the range, specify the SpreadsheetGear.Data.SetDataFlags.InsertCells flag. When specifying this flag, you must provide a range with exactly two rows for data, preceded by a row for column headers if SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders is not specified.

Specifying this flag instructs SpreadsheetGear to insert or delete rows as needed to accommodate the number of rows in the source DataTable, and to copy down values or formulas in extra columns which enables "calculated columns". This allows the cells to be preformatted and allows formulas to reference cells within the destination range. If the DataTable contains no rows, both data rows will be deleted and any formula references to these cells will be changed to #REF!. Otherwise, formulas referring to cells in the second initial row will be fixed up to refer to the last row of data inserted into the worksheet.

DataTable Column Names to Range Column Headers
By default, column names are copied to the first row in the specified range. To disable this functionality, specify SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders.

Forcing Some or All Values to Text
Normally, an attempt is made to parse values as they are placed into cells. To force all values to be entered into cells as text, specify the SpreadsheetGear.Data.SetDataFlags.AllText flag. To force only specific columns to be placed into cells as text, use the SpreadsheetGear.Data.SetDataFlags.InsertCells flag which enables you to preformat these columns as text cells (IRange.NumberFormat = "@").

See DataSet to Excel Workbook with Formats and Formulas in the Excel Reporting Samples and DataTable to Excel Workbook with Formats and Formulas in the Excel to DataGrid Samples for examples of using CopyFromDataTable.

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also


IRange Interface
IRange Members
SetDataFlags Enumeration
GetDataTable Method