As many of you know, I pledged to write a blog post on Excel import in the last blog post and then I seemed to have lost the code somewhere between WindowsApplication45 and WindowsApplication75. With a hectic travel schedule, some upcoming releases and strategies needing to be managed, and a large number of miscellaneous tasks, I didn’t get a chance to write that post. Interestingly enough, there have been a lot of questions and a lot of demand for how to do some sort of import into the grid; so I decided to go ahead and write a quick how-to.
Excel Export (From UltraGrid)
This is fairly simple, simply drop the UltraGridExcelExporter component onto the form and call the export method and you’re done.
private void btnExport_Click(object sender, EventArgs e)
{
//Use the UltraGridExcelExporter to Export Grid Data to a File
this.ugExcelExport.Export(this.ugGrid, fileLocation);
}
Excel Import (To UltraGrid)
This is a little bit more involved, as there is no helper method to just import an Excel file to the Grid. The reason there is no helper method is that Excel is a freeform structure and we didn’t want to take the responsibility of taking a freeform or cell-based structure and build a row/column based datatable out of it. If we did that, we’d be forced to make some assumptions (like this sample that I’ve written will do) and that may lose some data. For instance, if you have cells (with data) that are just randomly scattered throughout the excel worksheet.
The following code will walk you through creating a workbook, getting a column count and row count, building a DataTable and binding that table to the grid.
First, we’ll declare the variables that we need:
string fileLocation;
Infragistics.Excel.Workbook internalWorkBook;
//These will be used to build a DataTable from an Excel File. If we know the mapping of the DataStructure, we could add Rows to the existing table. i.e.
//AdventureWorksDataSet awDataSet = new AdventureWorksDataSet();
//awDataSet.vSalesPerson.AddvSalesPersonRow(myWorkBook.Worksheets[0].Rows[ i ].Cells[0].Value, ..., ..., ..., );
//However, in this example, we will just build the DataTable as if we had limited knowledge of the datastructure.
DataTable myDataTable = new DataTable();
DataColumn myDataColumn;
DataRow myDataRow;
//MIN/MAX Values used to frame the working size of the Excel data to be imported.
int minCellRow = Int32.MaxValue;
int maxCellRow = Int32.MinValue;
int minCellColumn = Int32.MaxValue;
int maxCellColumn = Int32.MinValue;
Then, we’ll use the file location to populate the Infragistics Excel Libraries:
try
{
fileLocation = FileLocation;
internalWorkBook = Infragistics.Excel.Workbook.Load(fileLocation);
}
catch (FileNotFoundException ex)
{
MessageBox.Show(ex.Message);
}
Next, we’ll determine the bounds for the rectangle that will translate the cell-based excel datasource into a column-based structure and we’ll also check to make sure the excel datasource has values in it.
foreach (Infragistics.Excel.WorksheetRow row in internalWorkBook.Worksheets[0].Rows)
{
foreach (Infragistics.Excel.WorksheetCell cell in row.Cells)
{
if (cell.Value != null)
{
//Logic For Determining the Range of Rows/Columns in the Excel File.
minCellRow = Math.Min(minCellRow, cell.RowIndex);
maxCellRow = Math.Max(maxCellRow, cell.RowIndex);
minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex);
maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex);
}
}
}
//Insert a Check to ensure data has been exported
if (maxCellRow < 0 && maxCellColumn < 0)
{
return null;
}
Next, we’ll create the columns for the datasource and in the case of this sample assign the first row as the value for the column header.
for (int i = minCellColumn; i <= maxCellColumn; i++)
{
//The export that was demonstrated earlier utilizes the first row
//for the column header. We can now use that to give column names.
myDataColumn = new DataColumn(internalWorkBook.Worksheets[0].Rows[minCellRow].Cells[ i ].Value.ToString());
//Add the columns to the datatable.
myDataTable.Columns.Add(myDataColumn);
}
Finally, we’ll populate the cells with data.
//Start at row 1 to ignore the header information that was used in the previous section.
for (int rowIndex = minCellRow + 1; rowIndex <= maxCellRow; rowIndex++)
{
//Create a new DataRow
myDataRow = myDataTable.NewRow();
//Loop through the columns and associate the value to each cell
for (int columnIndex = minCellColumn; columnIndex <= maxCellColumn; columnIndex++)
{
myDataRow[columnIndex] = internalWorkBook.Worksheets[0].Rows[rowIndex].Cells[columnIndex].Value;
}
//Add The Row to a DataTable
myDataTable.Rows.Add(myDataRow);
}
Lastly, we’ll associate the datatable that we created to the grid and our import is complete!
this.ugGrid.DataSource = myDataTable;
Most of the above code is comments to describe what I’m doing, so doing a basic import is really not that challenging. These Excel Libraries are extremely powerful and doing what I’m showing here is only scratching the surface of their capabilities.
You can download the .cs file (saved as txt) with all of the code here.