XlTableColumnInfo.DataFormatting Property
Gets or sets format characteristics for the data area of the table column.
Namespace: DevExpress.Export.Xl
Assembly: DevExpress.Printing.v24.2.Core.dll
Declaration
Property Value
| Type | Description |
|---|---|
| DevExpress.Export.Xl.XlDifferentialFormatting | An XlDifferentialFormatting object that specifies format characteristics applied to the column’s data area. |
Remarks
Use the DataFormatting property to apply custom formatting to the data area of the table column. In particular, you can color the cell background, adjust font settings, align the cell content, add borders and specify number format options. To do this, use properties of the XlDifferentialFormatting object inherited from the XlFormatting class: XlFormatting.Fill, XlFormatting.Font, XlFormatting.Alignment, XlFormatting.Border and XlFormatting.NumberFormat. Moreover, the XlDifferentialFormatting object implements implicit conversion from the XlFill, XlFont, XlCellAlignment, XlBorder and XlNumberFormat objects, so that you can directly assign an object containing the required format settings to the DataFormatting property without using unnecessary cast operators.
To apply custom formatting to the header row cell or total row cell of the table column, use the XlTableColumnInfo.HeaderRowFormatting or XlTableColumnInfo.TotalRowFormatting property, respectively.
For more information on how to set custom formatting for different table regions and individual table columns, refer to the How to: Apply Custom Formatting to a Table example.
Example
Note
A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
// Create objects containing information about table columns (their names and formatting).
List<XlTableColumnInfo> columns = new List<XlTableColumnInfo>();
columns.Add(new XlTableColumnInfo("Product"));
columns.Add(new XlTableColumnInfo("Category"));
columns.Add(new XlTableColumnInfo("Amount"));
// Specify formatting settings for the last column of the table.
columns[2].HeaderRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, -0.3));
columns[2].DataFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
columns[2].DataFormatting.NumberFormat = accounting;
columns[2].TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.8));
columns[2].TotalRowFormatting.NumberFormat = accounting;
// Specify formatting settings for the header row of the table.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, 0.0));
headerRowFormatting.Border = new XlBorder();
headerRowFormatting.Border.BottomColor = XlColor.FromArgb(0, 0, 0);
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Dashed;
// Start generating the table with a header row displayed.
IXlTable table = row.BeginTable(columns, true, headerRowFormatting);
// Apply the table style.
table.Style.Name = XlBuiltInTableStyleId.Medium16;
// Disable banded row formatting for the table.
table.Style.ShowRowStripes = false;
// Disable the filtering functionality for the table.
table.HasAutoFilter = false;
// Specify formatting settings for the total row of the table.
table.TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
table.TotalRowFormatting.Border = new XlBorder()
{
BottomColor = XlColor.FromTheme(XlThemeColor.Accent6, 0.0),
BottomLineStyle = XlBorderLineStyle.Thick,
TopColor = XlColor.FromArgb(0, 0, 0),
TopLineStyle = XlBorderLineStyle.Dashed
};
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(sheet.Tables[0], true);