Use the Excel Export API to Apply Predefined Formatting to a Cell
- 8 minutes to read
Using the Excel Export API you can apply a set of predefined format characteristics to a cell in a single step. Predefined formatting corresponds to one of the built-in Microsoft® Excel® styles and includes font settings, alignment options, fill color and cell borders. By default, all cells in a worksheet are formatted in accordance with the Normal Excel style.
To change the cell appearance using the predefined format options corresponding to one of the MS Excel styles, use the appropriate static property of the XlCellFormatting class.
| XlCellFormatting Property | Excel Built-In Style | Formatted Cell |
|---|---|---|
| XlCellFormatting.Bad | Bad |  |
| XlCellFormatting.Good | Good |  |
| XlCellFormatting.Neutral | Neutral |  |
| XlCellFormatting.Calculation | Calculation |  |
| XlCellFormatting.CheckCell | Check Cell |  |
| XlCellFormatting.Explanatory | Explanatory Text |  |
| XlCellFormatting.Input | Input |  |
| XlCellFormatting.LinkedCell | Linked Cell |  |
| XlCellFormatting.Note | Note |  |
| XlCellFormatting.Output | Output |  |
| XlCellFormatting.WarningText | Warning Text |  |
| XlCellFormatting.Heading1 | Heading 1 |  |
| XlCellFormatting.Heading2 | Heading 2 |  |
| XlCellFormatting.Heading3 | Heading 3 |  |
| XlCellFormatting.Heading4 | Heading 4 |  |
| XlCellFormatting.Title | Title |  |
| XlCellFormatting.Total | Total |  |
To apply predefined formatting settings to a cell, pass the XlCellFormatting class instance, which is returned by the static property utilized, to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
Tip
To share formatting settings with multiple cells in a row at once, use the IXlRow.BlankCells and IXlRow.BulkCells methods.
To specify formatting settings for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
You can also change the cell appearance using the predefined formatting settings that are based on the document theme. For an example, refer to the How to: Apply Themed Formatting to a Cell document.
// Create a new worksheet.
using(IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for(int i = 0; i < 6; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
}
// Specify the "Good, Bad and Neutral" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Good, Bad and Neutral";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell with the default "Normal" formatting.
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Normal";
}
// Create a cell and apply the "Bad" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Bad";
cell.Formatting = XlCellFormatting.Bad;
}
// Create a cell and apply the "Good" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Good";
cell.Formatting = XlCellFormatting.Good;
}
// Create a cell and apply the "Neutral" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Neutral";
cell.Formatting = XlCellFormatting.Neutral;
}
}
sheet.SkipRows(1);
// Specify the "Data and Model" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Data and Model";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Calculation" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Calculation";
cell.Formatting = XlCellFormatting.Calculation;
}
// Create a cell and apply the "Check Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Check Cell";
cell.Formatting = XlCellFormatting.CheckCell;
}
// Create a cell and apply the "Explanatory..." predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Explanatory";
cell.Formatting = XlCellFormatting.Explanatory;
}
// Create a cell and apply the "Input" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Input";
cell.Formatting = XlCellFormatting.Input;
}
// Create a cell and apply the "Linked Cell" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Linked Cell";
cell.Formatting = XlCellFormatting.LinkedCell;
}
// Create a cell and apply the "Note" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Note";
cell.Formatting = XlCellFormatting.Note;
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Output" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Output";
cell.Formatting = XlCellFormatting.Output;
}
// Create a cell and apply the "Warning Text" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Warning Text";
cell.Formatting = XlCellFormatting.WarningText;
}
}
sheet.SkipRows(1);
// Specify the "Titles and Headings" formatting category.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Titles and Headings";
}
}
using(IXlRow row = sheet.CreateRow()) {
// Create a cell and apply the "Heading 1" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 1";
cell.Formatting = XlCellFormatting.Heading1;
}
// Create a cell and apply the "Heading 2" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 2";
cell.Formatting = XlCellFormatting.Heading2;
}
// Create a cell and apply the "Heading 3" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 3";
cell.Formatting = XlCellFormatting.Heading3;
}
// Create a cell and apply the "Heading 4" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Heading 4";
cell.Formatting = XlCellFormatting.Heading4;
}
// Create a cell and apply the "Title" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Title";
cell.Formatting = XlCellFormatting.Title;
}
// Create a cell and apply the "Total" predefined formatting to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Total";
cell.Formatting = XlCellFormatting.Total;
}
}
}