Imports Infragistics.Documents.Excel Imports Infragistics.Win.UltraWinGrid Imports System.IO
Before You Begin
Sometimes you may want to import an Excel file into WinGrid™. Consider an Excel Worksheet that contains some data as well as a column that is calculated by Excel Formulas. This topic will show you how to get the calculated values of Excel Formulas into a WinGrid. Thanks to the Excel Formula Deserialization feature, we are able to get the resulting formula values.
What You Will Accomplish
In this walkthrough, you will import data from an excel Worksheet into a dynamically created DataTable and then bind it to WinGrid.
Follow These Steps
Before you start writing any code, you should place using/Imports directives in your code-behind so you don’t need to type out a member’s fully qualified name. A reference to Infragistics.Documents.Excel and Infragistics.Win.UltraWinGrid dlls are required for this example code.
In Visual Basic:
Imports Infragistics.Documents.Excel Imports Infragistics.Win.UltraWinGrid Imports System.IO
In C#:
using Infragistics.Documents.Excel; using Infragistics.Win.UltraWinGrid; using System.IO;
Add the UltraGrid control from your Visual Studio® Toolbox to your Form.
Add an Excel file named EMPLOYEE_METRICS.xls to the project. The GRAND_TOTAL column contains a Formula that multiplies the SALES and FREQUENCY columns. This is what the Excel file schema and data looks like:
Add a Button control to your Form. This will be used to initiate the import action. Double click the Button control and place the following code inside its Click event handler:
In Visual Basic:
Dim theFile As String = Application.StartupPath + "\EMPLOYEE_METRICS.xls" If File.Exists(theFile) Then 'Load the Excel File into the Workbook Object Dim theWorkbook As Workbook = Workbook.Load(theFile) 'We will only work with the first Worksheet in the Workbook Dim theWorksheet As Worksheet = theWorkbook.Worksheets(0) 'We will place the Excel Data into this DataTable Dim theEmployeeData As New DataTable("Employee_Data") Dim theRowCounter As Integer = 0 Dim theCellCounter As Integer = 0 'Iterate through all Worksheet rows For Each theWorksheetRow As WorksheetRow In theWorksheet.Rows If theRowCounter = 0 Then 'This is the Header Row. We are assuming that the Excel Worksheet's 'first row contains the schema of our soon to be data model. 'We will use this information to build our DataTable's schema For Each theWorksheetCell As WorksheetCell In theWorksheetRow.Cells Dim theCellValue As String = theWorksheetCell.Value.ToString().Trim() If theCellValue <> String.Empty Then 'This is the "Header Row" 'Create a DataColumn for each Column taken from the first Worksheet row Dim theDataColumn As DataColumn = theEmployeeData.Columns.Add() 'Since this is the Header Row, we use the cell value 'as the Column Name theDataColumn.ColumnName = theCellValue 'Here we skip to the actual data row (the row below the header row) and 'set the data column's data type to the type that exists in the corresponding 'cell in the actual data row of the Worksheet theDataColumn.DataType = theWorksheet.Rows(theRowCounter + 1).Cells(theCellCounter).Value.[GetType]() Else 'Exit the loop so that we do not 'traverse all empty Worksheet Cells. Exit For End If theCellCounter += 1 Next Else 'This is the actual data that will populate the data model theCellCounter = 0 'Add a new empty data row to our data model Dim theDataRow As DataRow = theEmployeeData.NewRow() 'iterate through each current Worksheet cell and populate the new data row. For Each theWorksheetCell As WorksheetCell In theWorksheetRow.Cells Dim theValue As Object = theWorksheet.Rows(theRowCounter).Cells(theCellCounter).Value If theValue IsNot Nothing Then theDataRow(theCellCounter) = theValue Else 'Exit the loop so that we do not 'traverse all empty Worksheet Cells. Exit For End If theCellCounter += 1 Next 'Add the Data Row to the DataTable theEmployeeData.Rows.Add(theDataRow) End If theRowCounter += 1 Next 'AcceptChanges so that these do not appear to be NEW rows theEmployeeData.AcceptChanges() 'Finally, bind the WinGrid to the DataTable Me.UltraGrid1.DataSource = theEmployeeData End If
In C#:
string theFile = Application.StartupPath + @"\EMPLOYEE_METRICS.xls"; if (File.Exists(theFile)) { //Load the Excel File into the Workbook Object Workbook theWorkbook = Workbook.Load(theFile); //We will only work with the first Worksheet in the Workbook Worksheet theWorksheet = theWorkbook.Worksheets[0]; //We will place the Excel Data into this DataTable DataTable theEmployeeData = new DataTable("Employee_Data"); int theRowCounter = 0; int theCellCounter = 0; //Iterate through all Worksheet rows foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows) { if (theRowCounter == 0) { //This is the Header Row. We are assuming that the Excel Worksheet's //first row contains the schema of our soon to be data model. //We will use this information to build our DataTable's schema foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells) { string theCellValue = theWorksheetCell.Value.ToString().Trim(); if (theCellValue != string.Empty) { //This is the "Header Row" //Create a DataColumn for each Column taken from the first Worksheet row DataColumn theDataColumn = theEmployeeData.Columns.Add(); //Since this is the Header Row, we use the cell value //as the Column Name theDataColumn.ColumnName = theCellValue; //Here we skip to the actual data row (the row below the header row) and //set the data column's data type to the type that exists in the corresponding //cell in the actual data row of the Worksheet theDataColumn.DataType = theWorksheet.Rows[theRowCounter + 1].Cells[theCellCounter].Value.GetType(); } else { break; //Exit the loop so that we do not //traverse all empty Worksheet Cells. } theCellCounter++; } } else //This is the actual data that will populate the data model { theCellCounter = 0; //Add a new empty data row to our data model DataRow theDataRow = theEmployeeData.NewRow(); //iterate through each current Worksheet cell and populate the new data row. foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells) { object theValue = theWorksheet.Rows[theRowCounter].Cells[theCellCounter].Value; if (theValue != null) { theDataRow[theCellCounter] = theValue; } else { break; //Exit the loop so that we do not //traverse all empty Worksheet Cells. } theCellCounter++; } //Add the Data Row to the DataTable theEmployeeData.Rows.Add(theDataRow); } theRowCounter++; } //AcceptChanges so that these do not appear to be NEW rows theEmployeeData.AcceptChanges(); //Finally, bind the WinGrid to the DataTable this.ultraGrid1.DataSource = theEmployeeData; }
Run the application. Click the button that initiates the import action. Following is an image of the WinGrid control that has been bound to the data that was loaded from the Excel Worksheet. The colored column represents the Worksheet’s calculated column whose actual values have been extracted.