# Settings

The `DataTableFormatProvider` exposes import and export settings that allow you to control how data is converted between `DataTable` and spreadsheet formats.

## Import Settings

* `PreferredDateTimeFormat`: Gets or sets the default format string when importing DateTime columns. When null, the current culture's short date pattern is used.
* `ShouldImportColumnHeaders`: Controls whether the column headers are imported.
* `StartCellIndex`: Gets or sets the index where the table starts in the Worksheet.

```C#
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();

DataTableFormatProvider dataTableFormatProvider = new DataTableFormatProvider();
dataTableFormatProvider.ImportSettings.PreferredDateTimeFormat = "dd.MM.yyyy";
dataTableFormatProvider.ImportSettings.ShouldImportColumnHeaders = true;
dataTableFormatProvider.Import(dataTable, worksheet, TimeSpan.FromSeconds(10));
```

### The CellImported Event

The `CellImported` event is fired for each cell and allows you to change the cell properties.

The `CellImportedEventArgs` contains information about the current cell:

* `dataTableRowIndex`: The index of the row in the DataTable containing the cell for which the event occurs.
* `dataTableColumnIndex`: The index of the column in the DataTable containing the cell for which the event occurs.
* `worksheetRowIndex`: The index of the row in the Worksheet containing the cell for which the event occurs.
* `worksheetColumnIndex`: The index of the column in the Worksheet containing the cell for which the event occurs.
* `worksheet`: The worksheet where the data is imported.

**Using the CellImported event to color imported cells in the second column**

```C#
DataTable table = GetTable();
DataTableFormatProvider provider = new DataTableFormatProvider();
provider.ImportSettings.CellImported += (s, e) =>
{
    if (e.DataTableColumnIndex == 1 && e.WorksheetRowIndex > 1)
    {
        e.Worksheet.Cells[e.WorksheetRowIndex, e.WorksheetColumnIndex].SetForeColor(new ThemableColor(Colors.Red));
    }
};

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();

provider.Import(table, worksheet, TimeSpan.FromSeconds(10));

radSpreadsheet.Workbook = workbook;
```

## Export Settings

* `HasHeaderRow`: Gets or sets whether the header row of the worksheet is exported.
* `ShouldSetDataTypes`: Gets or sets whether the exporter tries to parse the data types from the spreadsheet. If false, only objects are exported.
* `DataTableCulture`: Gets or sets the DataTable culture. By default, the culture of the workbook is used.
* `RangeToExport`: Gets or sets the cell range for which the data is exported.

### The ColumnExporting Event

The `ColumnExporting` event is fired for each column before the column is added to the table and allows you to change its properties.

The `ColumnExportingEventArgs` object contains the current column instance and its index:

* `DataColumn`: Gets the [DataColumn](https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn?view=net-6.0) that is being exported.
* `ColumnIndex`: Gets the index of the exported column.

**Using the ColumnExporting event to allow null values in the fourth exported column**

```C#
DataTableFormatProvider provider = new DataTableFormatProvider();
provider.ExportSettings.ColumnExporting += (s, e) =>
{
    if (e.ColumnIndex == 3)
    {
        e.DataColumn.AllowDBNull = true;
    }
};
var table = provider.Export(radSpreadsheet.Workbook.ActiveWorksheet, TimeSpan.FromSeconds(10));
```

## See Also

* [Using DataTableFormatProvider](https://www.telerik.com/document-processing-libraries/documentation/libraries/radspreadprocessing/formats-and-conversion/data-table/using-data-table-format-provider)
* [Keeping Numeric Values When Importing DataTable to Excel Using DataTableFormatProvider](https://www.telerik.com/document-processing-libraries/documentation/knowledge-base/retain-numeric-values-datatable-excel-datatableformatprovider)