This example illustrates how you can export a cell range to a System.Data.DataTable object.
Use the Worksheet.CreateDataTableExporter method to create a DataTableExporter instance and call the DataTableExporter's Export method.
You can use the Worksheet.CreateDataTable method to create an empty DataTable from a cell range. This method obtains column names from the range headings, and determines the column data types based on the first row of the specified range.
Note: Add a reference to the DevExpress.Docs assembly to your Spreadsheet project. The distribution of this assembly requires a license to the DevExpress Office File API or DevExpress Universal Subscription.
Files to Look At
Documentation
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
ExportToDataTableExample/Form1.cs(vb)
C##region #usings
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using DevExpress.XtraEditors;
#endregion #usings
using System;
using System.Data;
using System.Windows.Forms;
namespace ExportToDataTableExample
{
public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm
{
public Form1()
{
InitializeComponent();
spreadsheetControl1.LoadDocument("TopTradingPartners.xlsx");
ribbonControl1.SelectedPage = exportDataExampleRibbonPage;
}
private void barButtonItemRangeToDataTable_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (barCheckItemStopEmptyRow.Checked) {
ExportSelectionStopOnEmptyRow();
return;
}
#region #SimpleDataExport
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
CellRange range = worksheet.Selection;
bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked;
// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);
//Validate cell value types. If cell value types in a column are different, the column values are exported as text.
int firstDataRowIndex = rangeHasHeaders ? 1 : 0;
int rowCount = range.RowCount;
if (firstDataRowIndex < rowCount) {
for (int col = 0; col < range.ColumnCount; col++) {
CellValueType cellType = range[firstDataRowIndex, col].Value.Type;
for (int r = firstDataRowIndex + 1; r < rowCount; r++) {
if (cellType != range[r, col].Value.Type) {
dataTable.Columns[col].DataType = typeof(string);
break;
}
}
}
}
// Create the exporter that obtains data from the specified range,
// skips the header row (if required) and populates the previously created data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;
// Perform the export.
exporter.Export();
#endregion #SimpleDataExport
// A custom method that displays the resulting data table.
ShowResult(dataTable);
}
private void ExportSelectionStopOnEmptyRow() {
#region #StopExportOnEmptyRow
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
CellRange range = worksheet.Selection;
// Determine whether the first row in a range contains headers.
bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked;
// Determine whether an empty row must stop conversion.
bool stopOnEmptyRow = barCheckItemStopEmptyRow.Checked;
// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);
// Create the exporter that obtains data from the specified range,
// skips the header row (if required) and populates the previously created data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += (sender,args)=> {args.Action = DataTableExporterAction.Continue;};
if (stopOnEmptyRow) {
exporter.Options.SkipEmptyRows = false;
// Handle empty row.
exporter.ProcessEmptyRow += (sender, args) => { args.Action = DataTableExporterAction.Stop; };
}
// Perform the export.
exporter.Export();
#endregion #StopExportOnEmptyRow
// A custom method that displays the resulting data table.
ShowResult(dataTable);
}
private void barButtonItemUseExporterOptions_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
#region #DataExportWithOptions
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
CellRange range = worksheet.Tables[0].Range;
// Create a data table with column names obtained from the first row in a range.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, true);
// Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;
// Specify exporter options.
exporter.Options.ConvertEmptyCells = true;
exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0;
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = barCheckItemSkipErrors.Checked;
// Perform the export.
exporter.Export();
#endregion #DataExportWithOptions
// A custom method that displays the resulting data table.
ShowResult(dataTable);
}
#region #DataExportWithCustomConverter
private void barButtonItemUseCustomConverter_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
CellRange range = worksheet.Tables[0].Range;
// Create a data table with column names obtained from the first row in a range.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, true);
// Change the data type of the "As Of" column to text.
dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");
// Create the exporter that obtains data from the specified range and populates the specified data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;
// Specify a custom converter for the "As Of" column.
DateTimeToStringConverter toDateStringConverter = new DateTimeToStringConverter();
exporter.Options.CustomConverters.Add("As Of", toDateStringConverter);
// Set the export value for empty cell.
toDateStringConverter.EmptyCellValue = "N/A";
// Specify that empty cells and cells with errors should be processed.
exporter.Options.ConvertEmptyCells = true;
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;
// Perform the export.
exporter.Export();
// A custom method that displays the resulting data table.
ShowResult(dataTable);
}
// A custom converter that converts DateTime values to "Month-Year" text strings.
class DateTimeToStringConverter : ICellValueToColumnTypeConverter
{
public bool SkipErrorValues { get; set; }
public CellValue EmptyCellValue { get; set; }
public ConversionResult Convert(Cell readOnlyCell, CellValue cellValue, Type dataColumnType, out object result)
{
result = DBNull.Value;
ConversionResult converted = ConversionResult.Success;
if (cellValue.IsEmpty) {
result = EmptyCellValue;
return converted;
}
if (cellValue.IsError) {
// You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
//return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
result = "N/A";
return ConversionResult.Success;
}
result = String.Format("{0:MMMM-yyyy}",cellValue.DateTimeValue);
return converted;
}
}
#endregion #DataExportWithCustomConverter
#region #CellValueConversionErrorHandler
void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e)
{
MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
e.DataTableValue = null;
e.Action = DataTableExporterAction.Continue;
}
#endregion #CellValueConversionErrorHandler
#region #ShowResultForm
Form ShowResult(DataTable result)
{
using (XtraForm newForm = new XtraForm())
{
newForm.Width = 600;
newForm.Height = 300;
DevExpress.XtraGrid.GridControl grid = new DevExpress.XtraGrid.GridControl();
grid.Dock = DockStyle.Fill;
grid.DataSource = result;
newForm.Controls.Add(grid);
grid.ForceInitialize();
((DevExpress.XtraGrid.Views.Grid.GridView)grid.FocusedView).OptionsView.ShowGroupPanel = false;
newForm.ShowDialog(this);
return newForm;
}
}
#endregion #ShowResultForm
}
}