Importing Data from a DataTable to Grid

Since the release of the .NET Framework, Microsoft has provided an excellent way to store data in offline mode in the form of a DataTable object. Understanding the needs of developers, Aspose.Cells.GridDesktop also supports importing data from a data table. This topic discusses how to do this.

Example

To import the contents of a data table using Aspose.Cells.GridDesktop control:

1. Add Aspose.Cells.GridDesktop control to a form.
2. Create a DataTable object that contains the data to be imported.
3. Get the reference of a desired worksheet.
4. Import the data table contents to the worksheet.
5. Set the column headers of the worksheet according to column names of the data table.
6. Set the width of the columns, if desired/
7. Display the worksheet.

In the example given below, we have created a DataTable object and filled it with some data fetched from a database table named Products. Finally, we have imported data from that DataTable object to a desired worksheet using Aspose.Cells.GridDesktop.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

OleDbDataAdapter adapter;
DataTable dt = new DataTable();

// Creating connection string to connect with database
string conStr = @"Provider=microsoft.jet.oledb.4.0;Data Source=" + dataDir + "dbDatabase.mdb";

// Creating Select query to fetch data from database
string query = "SELECT * FROM Products ORDER BY ProductID";
adapter = new OleDbDataAdapter(query, conStr);

// Filling DataTable using an already created OleDbDataAdapter object
adapter.Fill(dt);

// Accessing the reference of a worksheet
Worksheet sheet = gridDesktop1.Worksheets[0];

// Importing data from DataTable to the worksheet. 0,0 specifies to start importing data from the cell with first row (0 index) and first column (0 index)
sheet.ImportDataTable(dt, false, 0, 0);

// Iterating through the number of columns contained in the DataTable
for (int i = 0; i < dt.Columns.Count; i++)
{
    // Setting the column headers of the worksheet according to column names of the DataTable
    sheet.Columns[i].Header = dt.Columns[i].Caption;
}

// Setting the widths of the columns of the worksheet
sheet.Columns[0].Width = 240;
sheet.Columns[1].Width = 160;
sheet.Columns[2].Width = 160;
sheet.Columns[3].Width = 100;

// Displaying the contents of the worksheet by making it active
gridDesktop1.ActiveSheetIndex = 0;