# Import Data
DsExcel is a Document Solutions product that offers a comprehensive library to create, manipulate, convert, and share Microsoft Excel-compatible spreadsheets.
## Content
Sometimes, you may need to import data from an object collection or a data table to a range to create a new spreadsheet or report. DsExcel enables you to import data from various data sources using [ImportData](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IRange.ImportData.html) method of [IRange](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IRange.html) interface. With ImportData method, you can import the following types of data sources:
* Element Types
* Primitive types: SByte, Byte, Int16, UInt16, Int32, UInt32, Int64, UInt64, Single, Double, Decimal, Boolean, DateTime, TimeSpan, or CalcError.
* Nullable primitives: string or Nullable{T} where T is a primitive type.
* Custom objects: objects that have properties to be mapped to cell values.
* IReadOnlyDictionary{TKey, TValue} or IDictionary{TKey, TValue} or IDictionary where the key is a string: It will be treated as a collection of property names and values.
* DataRow or DataRowView.
* Collection Types
* Flat (non-jagged, 1D in total) IEnumerable. For example, DataView.
* Jagged (2D in total) IEnumerable or IEnumerable{t} where the element type of the inner collection is primitive or nullable primitive.
* 2D array where the element type is primitive or nullable primitive.
DsExcel provides the ImportData method with the following overloads to import data from an object collection or a data table to a range:
| **Overload** | **Parameter** | **Description** |
| -------- | --------- | ----------- |
| @rows=2:ImportData(IEnumerable items, DataImportOptions options = null) | items | The items to import, which can be:
Element Types
Primitive types: SByte, Byte, Int16, UInt16, Int32, UInt32, Int64, UInt64, Single, Double, Decimal, Boolean, DateTime, TimeSpan, or CalcError.
Nullable primitives: string or Nullable{T} where T is a primitive type.
Custom objects: objects that have properties to be mapped to cell values.
IReadOnlyDictionary{TKey, TValue} or IDictionary{TKey, TValue} or IDictionary where the key is a string: It will be treated as a collection of property names and values.
DataRow or DataRowView.
Collection Types
Flat (non-jagged, 1D in total) IEnumerable. For example, DataView.
Jagged (2D in total) IEnumerable or IEnumerable{t} where the element type of the inner collection is primitive or nullable primitive.
2D array where the element type is primitive or nullable primitive.
|
| options | The import options. |
| @rows=2:ImportData(DataTable table, DataImportOptions options = null) | table | The table to import, which can be:
Column Types
Primitive types: SByte, Byte, Int16, UInt16, Int32, UInt32, Int64, UInt64, Single, Double, Decimal, Boolean, DateTime, TimeSpan, or CalcError.
Nullable primitives: string or Nullable{T} where T is a primitive type.
|
| options | The import options. |
**options** parameter of ImportData method accepts the following import options that are accessible through [DataImportOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.DataImportOptions.html) class, which enable you to choose how to import data from object collections or data tables to a range:
| **Import Option** | **Description** |
| ------------- | ----------- |
| InsufficientSpaceHandling Property | This property determines how to handle insufficient space when importing data. |
| IncludeColumnHeader Property | This property determines whether to include the column header as the first row when importing data. |
| ColumnsSelector Property | This property selects columns to import. The default behavior is to import all columns. |
| ItemTypeProvider Property | This property gets item type from the specified **System.Collections.IEnumerable**. The type is used to auto-generate columns. The default behavior is to try to get the item type from the generic type parameter, then try to get the type of the first item. |
> !type=note
> **Note:** DsExcel recommends adhering to the following when importing data to the range:
>
> * Use generic type where possible.
> * Use the delegate-based columns selector for custom objects in AOT projects.
> * Use a 2D array instead of a jagged array where possible.
> * Cache a LINQ result into a list or array if the result is not very long.
> * Keep the length of jagged arrays the same where possible.
> * Make sure to not use duck typing unless you have no choice.
> * Make sure to not attempt to import an asynchronous data source unless async continuations run on the original thread.
> * Make sure to not use covariance in array types unless you have no choice.
> !type=note
> **Note:** DsExcel recommends writing an option builder to keep the code clean. For more information, see [Builder pattern](https://en.wikipedia.org/wiki/Builder_pattern).
## Import Simple Enumerable Vertically
Refer to the following example code to import simple enumerable vertically to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Access first worksheet.
var worksheet = workbook.Worksheets[0];
// Add 1-D array of doubles.
var doubleData = new double[] { 1, 2, 3, 4, 5 };
// Import array vertically.
worksheet.Range["A1:A5"].ImportData(doubleData);
// Save the workbook.
workbook.Save("ImportSimpleEnumerableVertically.xlsx");
```
## Import Simple Enumerable Horizontally
Refer to the following example code to import simple enumerable horizontally to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Access first worksheet.
var worksheet = workbook.Worksheets[0];
// Add 1-D array of doubles.
var doubleData = new double[] { 1, 2, 3, 4, 5 };
// Import array horizontally.
worksheet.Range["A1:E1"].ImportData(doubleData);
// Save the workbook.
workbook.Save("ImportSimpleEnumerableHorizontally.xlsx");
```
## Import 1D Data
Refer to the following example code to import 1D data to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Set workbook culture to English (US).
workbook.Culture = CultureInfo.GetCultureInfo("en-US");
var sheet1 = workbook.Worksheets["Sheet1"];
sheet1.Name = "Import1DData";
// Set values to the range.
sheet1.Range["A1"].Value = "Tour Budget Table";
// Import 1D array horizontally when the range is not vertical.
var titles = new[] { "Employee ID", "Adults", "Children", "Rooms", "Spa", "Hotpot", "Budget" };
sheet1.Range["A2:G2"].ImportData(titles);
// Generate test data. You can set the seed value of the random number generator to get the same data.
var rnd = new Random(1234);
const int rowCount = 20;
var uids = Enumerable.Repeat(0, rowCount).Select(i => rnd.Next(10000, 99999)).ToArray();
var adults = Enumerable.Repeat(0, rowCount).Select(i => rnd.Next(1, 4)).ToArray();
var children = Enumerable.Repeat(0, rowCount).Select(i => rnd.Next(0, 3)).ToArray();
var rooms = adults.Zip(children, (adult, child) => (adult + child) / 2).ToList();
var spa = adults.Zip(children, (adult, child) => Math.Ceiling((adult + child) * rnd.NextDouble())).ToArray();
var hotpot = adults.Zip(children, (adult, child) => adult + child).ToArray();
const int budgetBase = 750;
var budget =
from i in Enumerable.Range(0, rowCount)
let adult = adults[i]
let child = children[i]
let room = rooms[i]
let sp = spa[i]
let hot = hotpot[i]
select adult * 200 + child * 100 + room * 400 + sp * 188 + hot * 233 + budgetBase;
// Import data by columns.
/* "rooms" is a list. It can also be imported like 1D arrays.
"budget" is a LINQ result. It can also be imported like 1D arrays. */
var columns = new IEnumerable[] { uids, adults, children, rooms, spa, hotpot, budget };
for (int i = 0; i < columns.Length; i++)
{
// 1D array/list will be imported vertically if the range is vertical.
sheet1.Range[2, i, 2, 1].ImportData(columns[i]);
}
// Set range style.
sheet1.Range[1, 0, 1, 7].Style = workbook.Styles["Heading 3"];
sheet1.Range[2, 0, rowCount, 7].Style = workbook.Styles["20% - Accent1"];
sheet1.Range[1, 0, 1, 7].EntireColumn.AutoFit();
sheet1.Range["A1"].Style = workbook.Styles["Heading 1"];
sheet1.Range["1:2"].AutoFit();
// Save the workbook.
workbook.Save("Import1DData.xlsx");
```
## Import 2D Data
Refer to the following example code to import 2D data to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Set workbook culture to English (US).
workbook.Culture = CultureInfo.GetCultureInfo("en-US");
var sheet1 = workbook.Worksheets["Sheet1"];
sheet1.Name = "Import2DData";
sheet1.Range["A1"].Value = "Tour Budget Table";
// Import titles with 2D array.
var titles = new string[,] { { "Employee ID", "Adults", "Children", "Rooms", "Spa", "Hotpot", "Budget" } };
sheet1.Range["A2"].ImportData(titles);
// Generate test data. You can set the seed value of the random number generator to get the same data.
var rnd = new Random(1234);
const int rowCount = 20;
// Import data from 2D arrays. This code is similar to using Range.Value.
var numbers = new double[rowCount, 7];
for (int i = 0; i < rowCount; i++)
{
int employeeId = rnd.Next(10000, 99999);
int adults = rnd.Next(1, 4);
int children = rnd.Next(0, 3);
int rooms = (adults + children) / 2;
double spa = Math.Ceiling((adults + children) * rnd.NextDouble());
int hotpot = adults + children;
double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;
numbers[i, 0] = employeeId;
numbers[i, 1] = adults;
numbers[i, 2] = children;
numbers[i, 3] = rooms;
numbers[i, 4] = spa;
numbers[i, 5] = hotpot;
numbers[i, 6] = budget;
}
var result = sheet1.Range["A3"].ImportData(numbers);
// Set range style.
sheet1.Range[1, 0, 1, result.ColumnsImported].Style = workbook.Styles["Heading 3"];
sheet1.Range[2, 0, result.RowsImported, result.ColumnsImported].Style = workbook.Styles["20% - Accent1"];
sheet1.Range[1, 0, 1, result.ColumnsImported].EntireColumn.AutoFit();
sheet1.Range["A1"].Style = workbook.Styles["Heading 1"];
sheet1.Range["1:2"].AutoFit();
sheet1.Range[2, 6, result.RowsImported, 1].NumberFormat = "$#,##0.00";
sheet1.Range[2, 6, result.RowsImported, 1].EntireColumn.ColumnWidth = 10;
// Save the workbook.
workbook.Save("Import2DData.xlsx");
```
## Import Multidimensional Array
Refer to the following example code to import multidimensional array to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Access first worksheet.
var worksheet = workbook.Worksheets[0];
// Add multidimensional array.
var doubleDataMt = new double[][,]
{
new double [,] {
{ 1, 2, 3 },
{ 4, 5, 6 },
{ 7, 8, 9 }
},
new double [,] {
{ 10, 11, 12 },
{ 13, 14, 15 },
{ 16, 17, 18 }
},
new double [,] {
{ 19, 20, 21 },
{ 22, 23, 24 },
{ 25, 26, 27 }
}
};
// Import multidimensional array.
int startRow = 0;
foreach (var item in doubleDataMt)
{
// A worksheet does not support multi-dimensional arrays.
// But you can import inner arrays one by one using the import result to perform layout.
var imported = worksheet.Range[startRow, 0].ImportData(item);
startRow += imported.RowsImported;
}
// Save the workbook.
workbook.Save("ImportMultidimensionalArray.xlsx");
```
## Import Jagged Arrays
Refer to the following example code to import jagged array to the worksheet:
```csharp
// Create a new workbook.
var workbook = new Workbook();
// Access first worksheet.
var worksheet = workbook.Worksheets[0];
// Add jagged array.
var doubleDataJagged = new double[][]
{
new double[] { 1, 2, 3 },
new double[] { 4, 5 },
new double[] { 7, 8, 9 }
};
// Inner arrays can have different lengths.
// But it's recommended to keep the length the same and use 2D arrays for better performance.
worksheet.Range["A1"].ImportData(doubleDataJagged);
// Save the workbook.
workbook.Save("ImportJaggedArrays.xlsx");
```
## Import Entity Data
Refer to the following example code to import data from entities to the worksheet:
```csharp
class Program
{
static void Main(string[] args)
{
// Create a new workbook.
var workbook = new Workbook();
// Set workbook culture to English (US).
workbook.Culture = CultureInfo.GetCultureInfo("en-US");
var sheet1 = workbook.Worksheets["Sheet1"];
sheet1.Name = "ImportEntities";
sheet1.Range["A1"].Value = "Tour Budget Table";
// Generate test data. You can set the seed value of the random number generator to get the same data.
var rnd = new Random(1234);
const int rowCount = 20;
// Create a list of TourBudget entities.
var tourBudgets = new List();
for (int i = 0; i < rowCount; i++)
{
int employeeId = rnd.Next(10000, 99999);
int adults = rnd.Next(1, 4);
int children = rnd.Next(0, 3);
int rooms = (adults + children) / 2;
double spa = Math.Ceiling((adults + children) * rnd.NextDouble());
int hotpot = adults + children;
double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;
// Add entity to the list.
tourBudgets.Add(new TourBudget
{
EmployeeID = employeeId,
Adults = adults,
Children = children,
Rooms = rooms,
Spa = spa,
Hotpot = hotpot,
Budget = budget
});
}
// Import list into the worksheet.
var result = sheet1.Range["A2"].ImportData(tourBudgets);
// Set range style.
sheet1.Range[1, 0, 1, result.ColumnsImported].Style = workbook.Styles["Heading 3"];
sheet1.Range[2, 0, result.RowsImported - 1, result.ColumnsImported].Style = workbook.Styles["20% - Accent1"];
sheet1.Range[1, 0, result.RowsImported, result.ColumnsImported].EntireColumn.AutoFit();
sheet1.Range["A1"].Style = workbook.Styles["Heading 1"];
sheet1.Range["1:2"].AutoFit();
sheet1.Range[2, 6, result.RowsImported, 1].NumberFormat = "$#,##0.00";
sheet1.Range[2, 6, result.RowsImported, 1].EntireColumn.ColumnWidth = 10;
// Save the workbook.
workbook.Save("ImportDataFromEntities.xlsx");
}
}
// Create a class for the entities.
public class TourBudget
{
public int EmployeeID { get; set; }
public int Adults { get; set; }
public int Children { get; set; }
public int Rooms { get; set; }
public double Spa { get; set; }
public int Hotpot { get; set; }
public double Budget { get; set; }
}
```
## Import Entity Data with Filter
Refer to the following example code to import data from entities with selected rows and columns to the worksheet:
```csharp
class Program
{
static void Main(string[] args)
{
// Create a new workbook.
var workbook = new Workbook();
// Set workbook culture to English (US).
workbook.Culture = CultureInfo.GetCultureInfo("en-US");
var sheet1 = workbook.Worksheets["Sheet1"];
sheet1.Name = "ImportEntities";
sheet1.Range["A1"].Value = "Tour Budget Table";
// Generate test data. You can set the seed value of the random number generator to get the same data.
var rnd = new Random(1234);
const int rowCount = 20;
// Create a list of TourBudget entities.
var tourBudgets = new List();
for (int i = 0; i < rowCount; i++)
{
int employeeId = rnd.Next(10000, 99999);
int adults = rnd.Next(1, 4);
int children = rnd.Next(0, 3);
int rooms = (adults + children) / 2;
double spa = Math.Ceiling((adults + children) * rnd.NextDouble());
int hotpot = adults + children;
double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750;
// Add entity to the list.
tourBudgets.Add(new TourBudget
{
EmployeeID = employeeId,
Adults = adults,
Children = children,
Rooms = rooms,
Spa = spa,
Hotpot = hotpot,
Budget = budget
});
}
// Order by budget from high to low and take top five rows.
var top5 = tourBudgets.OrderByDescending(t => t.Budget).Take(5);
// Import the query result into the worksheet.
// Only show the employee ID, adults, children, and budget with custom column headers.
var result = sheet1.Range["A2"].ImportData(top5, new DataImportOptions
{
ColumnsSelector = items =>
{
if (!(items is IEnumerable)) return null;
return new (string, Delegate)[]
{
("Employee ID", new Func(item => item.EmployeeID)),
("Adults", new Func(item => item.Adults)),
("Children", new Func(item => item.Children)),
("Budget", new Func(item => item.Budget))
};
}
});
// Set range style.
sheet1.Range[1, 0, 1, result.ColumnsImported].Style = workbook.Styles["Heading 3"];
sheet1.Range[2, 0, result.RowsImported - 1, result.ColumnsImported].Style = workbook.Styles["20% - Accent1"];
sheet1.Range[1, 0, result.RowsImported, result.ColumnsImported].EntireColumn.AutoFit();
sheet1.Range["A1"].Style = workbook.Styles["Heading 1"];
sheet1.Range["1:2"].AutoFit();
sheet1.Range[2, 3, result.RowsImported, 1].NumberFormat = "$#,##0.00";
sheet1.Range[2, 3, result.RowsImported, 1].EntireColumn.ColumnWidth = 10;
// Save the workbook.
workbook.Save("ImportDataFromEntitiesSelectRowsColumns.xlsx");
}
}
// Create a class for the entities.
public class TourBudget
{
public int EmployeeID { get; set; }
public int Adults { get; set; }
public int Children { get; set; }
public int Rooms { get; set; }
public double Spa { get; set; }
public int Hotpot { get; set; }
public double Budget { get; set; }
}
```
## Import Entity Data with Option Builder
Refer to the following example code to import data from entities with selected rows and columns with a "Builder" design pattern to the worksheet:
```csharp
class Program
{
static void Main(string[] args)
{
// Create a new workbook.
var workbook = new Workbook();
// Set workbook culture to English (US).
workbook.Culture = CultureInfo.GetCultureInfo("en-US");
var sheet1 = workbook.Worksheets["Sheet1"];
sheet1.Name = "DataImportOptionsBuilder";
sheet1.Range["A1"].Value = "GPU Performance Test Result";
sheet1.Range["A1:B1"].Merge();
sheet1.Range["A1:B1"].Style = workbook.Styles["Heading 1"];
// Set test data.
var frameRateResults = new FrameRateTestResult[]
{
new FrameRateTestResult { GpuName = "GeForce RTX 4090", FrameRate = 154.1, Workload = "1080p Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XTX", FrameRate = 149.0, Workload = "1080p Ultra" },
new FrameRateTestResult { GpuName = "GeForce RTX 4080 Super", FrameRate = 148.3, Workload = "1080p Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XT", FrameRate = 143.9, Workload = "1080p Ultra" },
new FrameRateTestResult { GpuName = "GeForce RTX 4090", FrameRate = 146.1, Workload = "1440p Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XTX", FrameRate = 135.3, Workload = "1440p Ultra" },
new FrameRateTestResult { GpuName = "GeForce RTX 4080 Super", FrameRate = 133.0, Workload = "1440p Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XT", FrameRate = 125.9, Workload = "1440p Ultra" },
new FrameRateTestResult { GpuName = "GeForce RTX 4090", FrameRate = 114.5, Workload = "4K Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XTX", FrameRate = 95.1, Workload = "4K Ultra" },
new FrameRateTestResult { GpuName = "GeForce RTX 4080 Super", FrameRate = 91.9, Workload = "4K Ultra" },
new FrameRateTestResult { GpuName = "Radeon RX 7900 XT", FrameRate = 81.2, Workload = "4K Ultra" },
};
// Build options with DataImportOptionsBuilder. The code is easier to read and maintain.
// Note that DataImportOptionsBuilder is a custom class in this example code. It is not part of the public API.
double maxFrameRateOfGroup = 0;
var importOptions = new DataImportOptionsBuilder()
.HasColumnsSelector(it => it
.Bind("Graphics Card", item => item.GpuName)
.Bind("Frame Rate", item => $"{item.FrameRate / maxFrameRateOfGroup:P1}({item.FrameRate:N1}fps)"))
.Build();
// Import grouped data and apply styles.
int curRow = 2;
// Group the frame rate results by workload.
var groupedResults = frameRateResults.GroupBy(it => it.Workload);
foreach (var group in groupedResults)
{
// Set group header.
sheet1.Range[curRow, 0].Value = group.Key;
sheet1.Range[curRow, 0].Style = workbook.Styles["Heading 2"];
curRow++;
// Sort data by frame rate from high to low and update the max frame rate.
var sortedData = group.OrderByDescending(it => it.FrameRate);
maxFrameRateOfGroup = group.Max(it => it.FrameRate);
// Import data with options.
DataImportResult result = sheet1.Range[curRow, 0].ImportData(sortedData, importOptions);
// Use the import result to apply styles.
sheet1.Range[curRow, 0, 1, result.ColumnsImported].Style = workbook.Styles["Accent1"];
curRow += result.RowsImported + 1;
}
// Set range layout.
var usedRange = sheet1.UsedRange;
usedRange.EntireRow.AutoFit();
usedRange.EntireColumn.AutoFit();
// Save the workbook.
workbook.Save("ImportDataFromEntitiesSelectRowsColumnsBuilder.xlsx");
}
}
// Create a class for the entities.
public class FrameRateTestResult
{
public string GpuName { get; set; }
public double FrameRate { get; set; }
public string Workload { get; set; }
}
// Create DataImportOptionsBuilder class for custom complex options.
public class DataImportOptionsBuilder
{
private readonly List> _columnsSelectors = new List>();
private bool _includeColumnsHeader = true;
private InsufficientSpaceHandling _insufficientSpaceHandling;
private readonly List> _itemTypeProviders = new List>();
public DataImportOptionsBuilder HasColumnsSelector(Action> configureSelector)
{
var builder = new ObjectColumnSelectorBuilder();
configureSelector(builder);
_columnsSelectors.Add(builder.Build());
return this;
}
public DataImportOptionsBuilder HasColumnsSelector(Predicate