Import Data from Excel

Importing data from Excel into a FlexGrid control offers several advantages. It provides a familiar interface for users, enhancing navigation and interaction. FlexGrid supports real-time editing, making data manipulation intuitive. Moreover, FlexGrid integrates seamlessly with other application components and allows users to export modified data back to Excel or other formats, streamlining reporting and sharing processes.

You can import data from an Excel file in both synchronous and asynchronous manner using the LoadExcel and LoadExcelAsync methods respectively available in the C1.WPF.Grid.Extensions class.

type=note

To import data from an Excel file, you don't need to have Microsoft Excel installed on your computer.

Import Excel Data Synchronously

The LoadExcel method allows you to load the data from an Excel file synchronously. It is best suited for small Excel files because the loading operation blocks the calling thread until the data is fully loaded into the FlexGrid control.

//Set Excel file path
string filePath = @"..\..\empdata.xlsx";
//Load data from Excel file
gridexcel.LoadExcel(filePath);

The LoadExcel method can only load data from first worksheet of a workbook by default. However, you can also load the data from a specific worksheet using the following code snippet:

//Provide your own path!
string filePath = @"..\..\empdata.xlsx";
//Loading data from Sheet2 synchronously
gridexcel.LoadExcel(filePath, "Sheet2");

type=note

The grid loads the values in excel cells but cannot load the underlying formulas. Other features such as frozen and merged cells, images, data maps, and cell borders are not translated either.

You can also load data synchronously from file and non-file based resources using the Stream class. Loading data directly from a stream is very useful when the data is already available in-memory for quick access and manipulation. Moreover, it is ideal for scenarios where the Excel file is retrieved from the network or dynamically generated in-memory. You can use the following code snippet to load data from an existing Excel file using the Stream class:

//Providing a file path
string filepath = @"..\..\empdata.xlsx";
//Initialize a file stream
Stream stream = InitializeExcelStream();
//Load data from Excel file
gridexcel.LoadExcel(stream);
//Method to load data from Excel file
public Stream InitializeExcelStream()
{
   FileStream fs = new FileStream(filepath, FileMode.Open);
   return fs;
}

Import Excel Data Asynchronously

The LoadExcelAsync method helps you in importing the data asynchronously. It is particularly useful when dealing with large files, preventing the UI from freezing. Asynchronous loading also ensures the application remains responsive while data is being loaded. Use the code below to import data asynchronously in the FlexGrid control from an Excel file.

//Set Excel file path
string filePath = @"..\..\empdata.xlsx"; 
//Load data from Excel file
gridexcel.LoadExcelAsync(filePath);

Similar to LoadExcel method, the LoadExcelAsync also loads data from the first worksheet of a workbook by default. However, you can also load the data from a specific worksheet using the following code snippet:

//Provide your own path!
string filePath = @"..\..\empdata.xlsx";
//Loading data from Sheet2 asynchronously
gridexcel.LoadExcelAsync(filePath,"Sheet2");

Both the LoadExcel and LoadExcelAsync methods can load only one table from a sheet. If the selected sheet has multiple tables of different sizes, the data will not render correctly. Moreover, importing data from Excel file would only work for unbound settings, and therefore, features like sorting and filtering would not be available.

Customize Importing

The C1.WPF.Grid.LoadExcelOptions class provides various properties to customize the process of importing data from an Excel file. The descriptions of these properties are as follows:

For example, you can use the following code snippet to transpose data while importing it from an existing Excel file using the Stream class:

LoadExcelOptions op = new LoadExcelOptions();
op.Transpose = true;
gridexcel.LoadExcel(stream, op);                         

type=note

This feature is available in .NET only.