ContextQMD
Back to Devexpress

Use ExcelDataSource

corelibraries-403660-devexpress-data-library-data-sources-use-excel-data-source.md

latest5.5 KB
Original Source

Use ExcelDataSource

  • Apr 22, 2022
  • 3 minutes to read

The ExcelDataSource data component retrieves data from XLS, XLSX, and CSV files.

The main ExcelDataSource property is ExcelDataSource.FileName, which specifies a path to a CSV, XLS, or XLSX file.

When a file is specified, subsequent actions depend on the file format.

CSV File Format

Create a new CsvSourceOptions class instance and specify import settings. Enable the DetectEncoding, CsvSourceOptions.DetectNewlineType, and CsvSourceOptions.DetectValueSeparator options to automatically determine the character encoding, the line break type, and the character that separates values in the source CSV document. Assign the CsvSourceOptions object to the ExcelDataSource.SourceOptions property:

csharp
// Creates a new Excel data source.  
ExcelDataSource excelDataSource = new ExcelDataSource();  
excelDataSource.FileName = "Northwind.csv";  
 // Specifies import settings.  
CsvSourceOptions csvSourceOptions = new CsvSourceOptions();  
csvSourceOptions.DetectEncoding = true;  
csvSourceOptions.DetectNewlineType = true;  
csvSourceOptions.DetectValueSeparator = true;  
excelDataSource.SourceOptions = csvSourceOptions;

XLS / XLSX File Format

Create a new ExcelWorksheetSettings object and specify the worksheet with the WorksheetName property. Assign the ExcelWorksheetSettings object to the ExcelSourceOptions.ImportSettings property.

Create a new ExcelSourceOptions class instance and specify settings used to import data from the source workbook. Disable the ExcelSourceOptions.SkipHiddenRows and ExcelSourceOptions.SkipHiddenColumns options to include hidden rows and columns in the resulting data source. Assign the ExcelSourceOptions instance to the ExcelDataSource.SourceOptions property.

csharp
// Creates a new Excel data source.  
ExcelDataSource excelDataSource = new ExcelDataSource();  
excelDataSource.FileName = "Northwind.xlsx";  
// Selects a worksheet.  
ExcelWorksheetSettings excelWorksheetSettings = new ExcelWorksheetSettings();  
excelWorksheetSettings.WorksheetName = "Sheet_Categories";  
// Specifies import settings.  
ExcelSourceOptions excelSourceOptions = new ExcelSourceOptions();  
excelSourceOptions.ImportSettings = excelWorksheetSettings;  
excelSourceOptions.SkipHiddenRows = false;  
excelSourceOptions.SkipHiddenColumns = false;  
excelDataSource.SourceOptions = excelSourceOptions;

Schema Definition

The data source schema is generated automatically, but you can define it manually when necessary. For this, do the following:

  1. Create as many FieldInfo class instances as there are columns in the selected CSV file / XLS worksheet.
  2. For each data field, specify the name (FieldInfo.Name) and type (FieldInfo.Type).
  3. Specify whether the field should be included into the resulting data source (FieldInfo.Selected).
  4. Add the fields to the ExcelDataSource.Schema collection with the FieldInfoList.AddRange) method. The order of added fields should match the order of the columns in the source file.

Web Specifics

In web applications, the Resources.AccessSettings.DataResources option manages access to the source XLS, XLSX, and CSV files for the ExcelDataSource instance.

The DevExpress ASP.NET Project Wizard creates a Web Forms application that specifies the AccessSettings.DataResources option and allows you to use only the Content folder as the data source file location.