The main use for a Microsoft® Excel® worksheet is to display data. You can easily transfer data from an existing DataSet to a workbook so you can view it in Excel. A DataSet consists of one or more DataTable objects. Each DataTable can be mapped to a worksheet.
This walkthrough will show you how to create a DataSet from some tables in a database. Then it will demonstrate how to copy the data from that DataSet to a workbook, creating a Worksheet object to display the data for each DataTable in the DataSet.
Create a DataTable and populate it with data from an existing database.
Connect to an existing Access database and populate a DataSet with data from some tables in the database:
In Visual Basic:
Dim northWindDbConnection As New System.Data.SqlClient.SqlConnection( _
"Data Source=.SQLEXPRESS;AttachDbFilename=""C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNorthwind.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True")
Dim dataSet As New DataSet()
northWindDbConnection.Open()
Try
Dim customersSelectCommand As New System.Data.SqlClient.SqlCommand("SELECT * FROM Customers", northWindDbConnection)
Dim customersReader As System.Data.SqlClient.SqlDataReader = customersSelectCommand.ExecuteReader()
' Load all data from the customers table in the database
Dim customersTable As New DataTable("Customers")
customersTable.Load(customersReader)
' Add the customers data table to the data set
dataSet.Tables.Add(customersTable)
Dim ordersSelectCommand As New System.Data.SqlClient.SqlCommand("SELECT * FROM Orders", northWindDbConnection)
Dim ordersReader As System.Data.SqlClient.SqlDataReader = ordersSelectCommand.ExecuteReader()
' Load all data from the customers orders in the database
Dim ordersTable As New DataTable("Orders")
ordersTable.Load(ordersReader)
' Add the orders data table to the data set
dataSet.Tables.Add(ordersTable)
Finally
northWindDbConnection.Close()
End Try
In C#:
System.Data.SqlClient.SqlConnection northWindDbConnection = new System.Data.SqlClient.SqlConnection(
@"Data Source=.SQLEXPRESS;AttachDbFilename=""C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataNorthwind.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True");
DataSet dataSet = new DataSet();
northWindDbConnection.Open();
try
{
System.Data.SqlClient.SqlCommand customersSelectCommand = new System.Data.SqlClient.SqlCommand(
"SELECT * FROM Customers", northWindDbConnection);
System.Data.SqlClient.SqlDataReader customersReader = customersSelectCommand.ExecuteReader();
// Load all data from the customers table in the database
DataTable customersTable = new DataTable("Customers");
customersTable.Load(customersReader);
// Add the customers data table to the data set
dataSet.Tables.Add(customersTable);
System.Data.SqlClient.SqlCommand ordersSelectCommand = new System.Data.SqlClient.SqlCommand(
"SELECT * FROM Orders", northWindDbConnection);
System.Data.SqlClient.SqlDataReader ordersReader = ordersSelectCommand.ExecuteReader();
// Load all data from the customers orders in the database
DataTable ordersTable = new DataTable("Orders");
ordersTable.Load(ordersReader);
// Add the orders data table to the data set
dataSet.Tables.Add(ordersTable);
}
finally
{
northWindDbConnection.Close();
}
Load the data into a workbook.
Create a workbook to hold the data from the DataSet.
In Visual Basic:
Dim workbook As New Infragistics.Documents.Excel.Workbook()
In C#:
Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
Iterate the data tables in the data set and create a worksheet for each one. Also, populate the worksheet with the data from the data table:
In Visual Basic:
For Each table As DataTable In dataSet.Tables
' Create the worksheet to represent this data table
Dim worksheet As Infragistics.Documents.Excel.Worksheet = workbook.Worksheets.Add(table.TableName)
' Create column headers for each column
For columnIndex As Integer = 0 To table.Columns.Count – 1
worksheet.Rows.Item(0).Cells.Item(columnIndex).Value = table.Columns.Item(columnIndex).ColumnName
Next
' Starting at row index 1, copy all data rows in
' the data table to the worksheet
Dim rowIndex As Integer = 1
For Each dataRow As DataRow In table.Rows
Dim row As Infragistics.Documents.Excel.WorksheetRow = _
worksheet.Rows.Item(rowIndex)
rowIndex = rowIndex + 1
For columnIndex As Integer = 0 To dataRow.ItemArray.Length – 1
row.Cells.Item(columnIndex).Value = dataRow.ItemArray(columnIndex)
Next
Next
Next
In C#:
foreach (DataTable table in dataSet.Tables)
{
// Create the worksheet to represent this data table
Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add(table.TableName);
// Create column headers for each column
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
worksheet.Rows[0].Cells[columnIndex].value = table.Columns[columnIndex].ColumnName;
}
// Starting at row index 1, copy all data rows in
// the data table to the worksheet
int rowIndex = 1;
foreach (DataRow dataRow in table.Rows)
{
Infragistics.Documents.Excel.WorksheetRow row = worksheet.Rows[rowIndex++];
for (int columnIndex = 0; columnIndex < dataRow.ItemArray.Length; columnIndex++)
{
row.Cells[columnIndex].value = dataRow.ItemArray[columnIndex];
}
}
}
Save the workbook.
Write the workbook to a file:
In Visual Basic:
workbook.Save("C:Data.xls")
In C#:
workbook.Save( "C:Data.xls" );
View on GitHub