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 XLExport_Formatting_Bad
XlCellFormatting.Good Good XLExport_Formatting_Good
XlCellFormatting.Neutral Neutral XLExport_Formatting_Neutral
XlCellFormatting.Calculation Calculation XLExport_Formatting_Calculation
XlCellFormatting.CheckCell Check Cell XLExport_Formatting_CheckCell
XlCellFormatting.Explanatory Explanatory Text XLExport_Formatting_Explanatory
XlCellFormatting.Input Input XLExport_Formatting_Input
XlCellFormatting.LinkedCell Linked Cell XLExport_Formatting_LinkedCell
XlCellFormatting.Note Note XLExport_Formatting_Note
XlCellFormatting.Output Output XLExport_Formatting_Output
XlCellFormatting.WarningText Warning Text XLExport_Formatting_WarningText
XlCellFormatting.Heading1 Heading 1 XLExport_Formatting_Heading1
XlCellFormatting.Heading2 Heading 2 XLExport_Formatting_Heading2
XlCellFormatting.Heading3 Heading 3 XLExport_Formatting_Heading3
XlCellFormatting.Heading4 Heading 4 XLExport_Formatting_Heading4
XlCellFormatting.Title Title XLExport_Formatting_Title
XlCellFormatting.Total Total XLExport_Formatting_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;
        }
    }
}
See Also