Skip to main content
BuyLog In
Cross-Platform Class Library
Search in…
Docs > DevExpress Data Library > Data Sources > Use ExcelDataSource
All docs
V23.2
Cross-Platform Core Libraries
Core Library Assemblies
DevExpress Data Library
Data Sources
Use SqlDataSource
Use EFDataSource
Use ObjectDataSource
Use JsonDataSource
Use ExcelDataSource
Use FederationDataSource
Criteria Language Syntax
Criteria Operators
LINQ-Like Criteria Syntax
Simplified Criteria Syntax
Limitations of CriteriaOperator.Parse
Build Criteria - Cheat Sheet
Custom Aggregate Functions
DevExpress.Drawing Graphics Library
DevExpress Pivot Grid Core Library
Miscellaneous
API Reference
Download CHM

Use ExcelDataSource

  • 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:

// 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.

// 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.