officefileapi-114394-excel-export-library-formatting-how-to-apply-predefined-formatting-to-a-cell.md
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;
}
}
}
' Create a new worksheet.
Using sheet As IXlSheet = document.CreateSheet()
' Create six successive columns and set their widths.
For i As Integer = 0 To 5
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 100
End Using
Next i
' Specify the "Good, Bad and Neutral" formatting category.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Good, Bad and Neutral"
End Using
End Using
Using row As IXlRow = sheet.CreateRow()
' Create a cell with the default "Normal" formatting.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Normal"
End Using
' Create a cell and apply the "Bad" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Bad"
cell.Formatting = XlCellFormatting.Bad
End Using
' Create a cell and apply the "Good" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Good"
cell.Formatting = XlCellFormatting.Good
End Using
' Create a cell and apply the "Neutral" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Neutral"
cell.Formatting = XlCellFormatting.Neutral
End Using
End Using
sheet.SkipRows(1)
' Specify the "Data and Model" formatting category.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Data and Model"
End Using
End Using
Using row As IXlRow = sheet.CreateRow()
' Create a cell and apply the "Calculation" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Calculation"
cell.Formatting = XlCellFormatting.Calculation
End Using
' Create a cell and apply the "Check Cell" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Check Cell"
cell.Formatting = XlCellFormatting.CheckCell
End Using
' Create a cell and apply the "Explanatory..." predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Explanatory"
cell.Formatting = XlCellFormatting.Explanatory
End Using
' Create a cell and apply the "Input" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Input"
cell.Formatting = XlCellFormatting.Input
End Using
' Create a cell and apply the "Linked Cell" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Linked Cell"
cell.Formatting = XlCellFormatting.LinkedCell
End Using
' Create a cell and apply the "Note" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Note"
cell.Formatting = XlCellFormatting.Note
End Using
End Using
Using row As IXlRow = sheet.CreateRow()
' Create a cell and apply the "Output" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Output"
cell.Formatting = XlCellFormatting.Output
End Using
' Create a cell and apply the "Warning Text" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Warning Text"
cell.Formatting = XlCellFormatting.WarningText
End Using
End Using
sheet.SkipRows(1)
' Specify the "Titles and Headings" formatting category.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Titles and Headings"
End Using
End Using
Using row As IXlRow = sheet.CreateRow()
' Create a cell and apply the "Heading 1" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Heading 1"
cell.Formatting = XlCellFormatting.Heading1
End Using
' Create a cell and apply the "Heading 2" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Heading 2"
cell.Formatting = XlCellFormatting.Heading2
End Using
' Create a cell and apply the "Heading 3" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Heading 3"
cell.Formatting = XlCellFormatting.Heading3
End Using
' Create a cell and apply the "Heading 4" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Heading 4"
cell.Formatting = XlCellFormatting.Heading4
End Using
' Create a cell and apply the "Title" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Title"
cell.Formatting = XlCellFormatting.Title
End Using
' Create a cell and apply the "Total" predefined formatting to it.
Using cell As IXlCell = row.CreateCell()
cell.Value = "Total"
cell.Formatting = XlCellFormatting.Total
End Using
End Using
End Using
See Also
Use the Excel Export API to Format a Cell
Use the Excel Export API to Apply Themed Formatting to a Cell