Interface IExtendedFormat

Contains the font attributes (font name, size, color, and so on) for an object.

Inherited Members

IParentApplication.Application

IParentApplication.Parent

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IExtendedFormat : IParentApplication

Properties

Borders

Returns IBorders object for this extended format.

Declaration

IBorders Borders { get; }

Property Value

Type
IBorders

Examples

The following code illustrates how to access Borders property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set border
            style.Borders.Color = ExcelKnownColors.Blue;
            style.Borders.LineStyle = ExcelLineStyle.Thick;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set border
            format.Borders.Color = ExcelKnownColors.Red;
            format.Borders.LineStyle = ExcelLineStyle.Thick;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Color

Returns or sets the RGB color to cell.

Declaration

Color Color { get; set; }

Property Value

Type
Color

Examples

The following code illustrates how to set RGB color to Color property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set color
            style.Color = System.Drawing.Color.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set color
            format.Color = System.Drawing.Color.Red;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

ColorIndex

Returns or sets the color from ExcelKnownColors enumeration to the IStyle.

Declaration

ExcelKnownColors ColorIndex { get; set; }

Property Value

Type
ExcelKnownColors

Examples

The following code illsutrates how to set a color from ExcelKnownColors enumeration to the ColorIndex property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set color
            format.ColorIndex = ExcelKnownColors.Red;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

FillBackground

Gets / Sets index of fill background color. Obsolete, will be removed in next release.

Declaration

ExcelKnownColors FillBackground { get; set; }

Property Value

Type
ExcelKnownColors

Remarks

Use ColorIndex instead of this property.

FillBackgroundRGB

Gets / Sets fill background color. Obsolete, will be removed in next release.

Declaration

Color FillBackgroundRGB { get; set; }

Property Value

Type
Color

Remarks

Use Color instead of this property.

FillForeground

Gets / Sets index of fill foreground color. Obsolete, will be removed in next release.

Declaration

ExcelKnownColors FillForeground { get; set; }

Property Value

Type
ExcelKnownColors

Remarks

Use PatternColorIndex instead of this property.

FillForegroundRGB

Gets / Sets fill foreground color. Obsolete, will be removed in next release.

Declaration

Color FillForegroundRGB { get; set; }

Property Value

Type
Color

Remarks

Use PatternColor instead of this property.

FillPattern

Gets / Sets fill pattern.

Declaration

ExcelPattern FillPattern { get; set; }

Property Value

Type
ExcelPattern

Examples

By default fill pattern will be set to None so patterns will not be visbile. Here for example, we set VerticalStripe to fill pattern.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set pattern
            style.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            style.PatternColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set pattern
            format.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            format.PatternColorIndex = ExcelKnownColors.Red;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Font

Gets IFont object for this extended format. Read-only.

Declaration

IFont Font { get; }

Property Value

Type
IFont

Examples

The following code illustrates how to access Font property and set Bold to the IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set font
            IFont font = style.Font;

            //Set bold
            font.Bold = true;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set font
            IFont formatFont = format.Font;

            //Set bold
            formatFont.Bold = true;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

FormulaHidden

True if formula is hidden.

Declaration

bool FormulaHidden { get; set; }

Property Value

Type
System.Boolean

Remarks

This property will work only if the sheet proetction is enabled.

Examples

The following code illustrates how to set visibility for the formula applied to a cell.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load values
            worksheet["B2"].Value = worksheet["C2"].Value = "10";

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["D2"].CellStyle = style;

            //Set formula
            worksheet["D2"].Formula = "B2+C2";

            //Set FormulaHidden
            style.FormulaHidden = true;

            //Set locked
            style.Locked = true;

            //Get extended format
            IExtendedFormat format = worksheet["D3"].CellStyle;

            //Set formula
            worksheet["D3"].Formula = "B2+C2";

            //Set FormulaHidden
            format.FormulaHidden = true;

            //Set locked
            format.Locked = true;

            //Enable sheet protection
            worksheet.Protect("", ExcelSheetProtection.All);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

HasBorder

Returns true if the Cell has IBorder.

Declaration

bool HasBorder { get; }

Property Value

Type
System.Boolean

Examples

The following code ilustrats how to access HasBorder property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set border
            style.Borders.Color = ExcelKnownColors.Blue;
            style.Borders.LineStyle = ExcelLineStyle.Thick;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set border
            format.Borders.Color = ExcelKnownColors.Blue;
            format.Borders.LineStyle = ExcelLineStyle.Thick;

            //Check border
            Console.WriteLine(style.HasBorder);
            Console.WriteLine(format.HasBorder);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//True
//True

HorizontalAlignment

Horizontal alignment.

Declaration

ExcelHAlign HorizontalAlignment { get; set; }

Property Value

Type
ExcelHAlign

Examples

The following code illustrates how to set horizontal alignment to the data in the cell.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set alignment
            style.HorizontalAlignment = ExcelHAlign.HAlignRight;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set alignment
            format.HorizontalAlignment = ExcelHAlign.HAlignRight;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludeAlignment

True if the style includes the AddIndent, HorizontalAlignment, VerticalAlignment, WrapText, and Orientation properties. Read / write Boolean.

Declaration

bool IncludeAlignment { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to exclude alignment related settings for a specific IStyle object.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set alignment
            style.HorizontalAlignment = ExcelHAlign.HAlignRight;

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set include alignment
            style.IncludeAlignment = false;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set alignment
            format.HorizontalAlignment = ExcelHAlign.HAlignRight;

            //Set color
            format.ColorIndex = ExcelKnownColors.Red;

            //Set include alignment
            format.IncludeAlignment = false;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludeBorder

True if the IStyle includes the Color, ColorIndex, LineStyle, and Weight border properties. Read / write Boolean.

Declaration

bool IncludeBorder { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to exclude IBorders related settings for a specific IStyle object.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set border
            style.Borders.Color = ExcelKnownColors.Blue;
            style.Borders.LineStyle = ExcelLineStyle.Thick;

            //Set color
            style.ColorIndex = ExcelKnownColors.Red;

            //Set include border
            style.IncludeBorder = false;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set border
            format.Borders.Color = ExcelKnownColors.Red;
            format.Borders.LineStyle = ExcelLineStyle.Thick;

            //Set color
            format.ColorIndex = ExcelKnownColors.Blue;

            //Set include border
            format.IncludeBorder = false;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludeFont

True if the style includes the Background, Bold, Color, ColorIndex, FontStyle, Italic, Name, OutlineFont, Shadow, Size, Strikethrough, Subscript, Superscript, and Underline font properties. Read / write Boolean.

Declaration

bool IncludeFont { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to exclude IFont related settings for a specific IStyle object.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set font
            IFont font = style.Font;

            //Set bold
            font.Bold = true;

            //Set include font
            style.IncludeFont = false;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set font
            IFont formatFont = format.Font;

            //Set bold
            formatFont.Bold = true;

            //Set include font
            format.IncludeFont = false;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludeNumberFormat

True if the IStyle includes the NumberFormat property. Read / write Boolean.

Declaration

bool IncludeNumberFormat { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to exclude NumberFormat for a specific IStyle object.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set values
            worksheet["C2"].Value = "1200.40";
            worksheet["E2"].Value = "1200.40";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set number format index
            style.NumberFormat = "# ?/?";

            //Set include number format
            style.IncludeNumberFormat = false;

            //Set modified style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set number format index
            format.NumberFormat = "# ?/?";

            //Set include number format
            format.IncludeNumberFormat = false;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludePatterns

True if the IStyle includes the Color, ColorIndex, InvertIfNegative, Pattern, PatternColor, and PatternColorIndex interior properties. Read / write Boolean.

Declaration

bool IncludePatterns { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to exclude pattern related settings for a specific IStyle object.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set pattern and color
            style.FillPattern = ExcelPattern.ThinHorizontalCrosshatch;

            //Set pattern color
            style.PatternColorIndex = ExcelKnownColors.Blue;

            //Set include pattern
            style.IncludePatterns = false;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set pattern and color
            format.FillPattern = ExcelPattern.ThinHorizontalCrosshatch;

            //Set pattern color
            format.PatternColorIndex = ExcelKnownColors.Blue;

            //Set include pattern
            format.IncludePatterns = false;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IncludeProtection

True if the style includes the FormulaHidden and Locked protection properties. Read / write Boolean.

Declaration

bool IncludeProtection { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to set IncludeProtection property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load values
            worksheet["B2"].Value = "10";
            worksheet["C2"].Value = "20";

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["B2"].CellStyle = style;

            //Set locked
            style.Locked = false;

            //Set IncludeProtection
            style.IncludeProtection = false;

            //Get extended format
            IExtendedFormat format = worksheet["C2"].CellStyle;

            //Set locked
            format.Locked = false;

            //Set IncludeProtection
            format.IncludeProtection = false;

            //Enable sheet protection
            worksheet.Protect("", ExcelSheetProtection.All);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IndentLevel

Gets or set Indent level.

Declaration

int IndentLevel { get; set; }

Property Value

Type
System.Int32

Examples

The following code illustrates how to set indent level for a IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set indent level
            style.IndentLevel = 2;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set indent level
            format.IndentLevel = 2;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

IsFirstSymbolApostrophe

If true then first symbol in cell is apostrophe.

Declaration

bool IsFirstSymbolApostrophe { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to access IsFirstSymbolApostrophe property of a IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set text
            worksheet["C2"].Text = @"'12345";

            //Access extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set text
            worksheet["E2"].Text = @"'12345";

            //Save
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();

            //Open worksheet
            IWorkbook book = application.Workbooks.Open("CellFormats.xls");
            IWorksheet sheet = book.Worksheets[0];

            //Get style
            IStyle customStyle = sheet["C2"].CellStyle;

            //Get extended format
            IExtendedFormat extendedFormat = sheet["E2"].CellStyle;

            //Check first symbol is apostrophe or not
            Console.Write(customStyle.IsFirstSymbolApostrophe);
            Console.Write(extendedFormat.IsFirstSymbolApostrophe);

            //Save and dispose
            book.SaveAs("CellFormats.xlsx");
            book.Close();
            Console.ReadKey();
        }
//Output will be
//True
//True

IsModified

Gets value indicating whether format was modified, compared to parent format.

Declaration

bool IsModified { get; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to set IsModified property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set style
            IStyle style = worksheet["C2"].CellStyle;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Check IsModified
            Console.WriteLine(style.IsModified);
            Console.WriteLine(format.IsModified);

            //Save and Dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//False
//False

JustifyLast

For far east languages. Supported only for format. Always 0 for US.

Declaration

bool JustifyLast { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to set JustifyLast property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set style
            IStyle style = worksheet["C2"].CellStyle;

            //Set justify last
            style.JustifyLast = true;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set justify last
            format.JustifyLast = true;

            //Save and Dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Locked

True if cell is locked.

Declaration

bool Locked { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to set Locked for a IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Load values
            worksheet["B2"].Value = worksheet["C2"].Value = "10";

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["D2"].CellStyle = style;

            //Set formula
            worksheet["D2"].Formula = "B2+C2";

            //Set FormulaHidden
            style.FormulaHidden = true;

            //Set locked
            style.Locked = true;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set formula
            worksheet["E2"].Formula = "B2+C2";

            //Set FormulaHidden
            format.FormulaHidden = true;

            //Set locked
            format.Locked = true;

            //Enable sheet protection
            worksheet.Protect("", ExcelSheetProtection.All);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

NumberFormat

Returns or sets the number format code for the IStyle object. Read / write String.

Declaration

string NumberFormat { get; set; }

Property Value

Type
System.String

Remarks

To know more about number formats refer Apply Number Formats.

Examples

The following code illustrates how to set number format for the IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Value = "1200.40";
            worksheet["E2"].Value = "1340.40";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set number format
            style.NumberFormat = "#,##0.00_);[Red](#,##0.00)";

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set number format
            format.NumberFormat = "#,##0.00_);[Red](#,##0.00)";

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

NumberFormatIndex

Gets / Sets format index.

Declaration

int NumberFormatIndex { get; set; }

Property Value

Type
System.Int32

Remarks

To know more about number formats refer Apply Number Formats.

Examples

By default, XlsIO holds 36 number format types that can be applied by just setting NumberFormatIndex. Here for example, we set number format in the 5th index to apply. To get more customization we can use NumberFormat to set number formatting string.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Value = "1200.40";
            worksheet["E2"].Value = "1234.40";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set number format index
            style.NumberFormatIndex = 5;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set number format index
            format.NumberFormatIndex = 5;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

NumberFormatLocal

Returns or sets the format code for the object as a string in the language of the user. Read / write String.

Declaration

string NumberFormatLocal { get; set; }

Property Value

Type
System.String

Examples

The following code illustrates how to set NumberFormatLocal property of a IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Value = "1200.40";
            worksheet["E2"].Value = "1234.40";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set number format
            style.NumberFormatLocal = "$###.##";

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set number format
            format.NumberFormatLocal = "$###.##";

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

NumberFormatSettings

Returns INumberFormat object that describes number format. Read-only.

Declaration

INumberFormat NumberFormatSettings { get; }

Property Value

Type
INumberFormat

Examples

The following code illustrates how to access INumberFormat using NumberFormatSettings property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Value = "1200.40";
            worksheet["E2"].Value = "1200.45";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set number format index
            style.NumberFormat = "#,##0.00";

            //Set number format settings
            INumberFormat numberFormat = style.NumberFormatSettings;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set number format index
            format.NumberFormat = "#,##0.00";

            //Set number format settings
            INumberFormat extendedNumberFormat = format.NumberFormatSettings;

            //Check number format decimal places
            Console.WriteLine(numberFormat.DecimalPlaces);
            Console.WriteLine(extendedNumberFormat.DecimalPlaces);

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
            Console.ReadKey();
        }
//Output will be
//2
//2

PatternColor

Returns or sets the color of the interior pattern as a RGB Color value.

Declaration

Color PatternColor { get; set; }

Property Value

Type
Color

Examples

The following code illustrates how to set a RGB color to the pattern of the IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set pattern
            style.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            style.PatternColor = System.Drawing.Color.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set pattern
            format.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            format.PatternColor = System.Drawing.Color.Red;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

PatternColorIndex

Returns or sets the color of the interior pattern as an index into the current color palette.

Declaration

ExcelKnownColors PatternColorIndex { get; set; }

Property Value

Type
ExcelKnownColors

Examples

The following code illustrates how to set a color from ExcelKnownColors enumeration to the pattern of the IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set pattern
            style.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            style.PatternColorIndex = ExcelKnownColors.Red;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set pattern
            format.FillPattern = ExcelPattern.VerticalStripe;

            //Set pattern color
            format.PatternColorIndex = ExcelKnownColors.Red;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

ReadingOrder

Text direction, the reading order for far east versions.

Declaration

ExcelReadingOrderType ReadingOrder { get; set; }

Property Value

Type
ExcelReadingOrderType

Examples

The following code illustrates how to set ReadingOrder property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set style
            IStyle style = worksheet["C2"].CellStyle;

            //Set justify last
            style.JustifyLast = true;

            //Set reading order
            style.ReadingOrder = ExcelReadingOrderType.RightToLeft;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set justify last
            format.JustifyLast = true;

            //Set reading order
            format.ReadingOrder = ExcelReadingOrderType.RightToLeft;

            //Save and Dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Rotation

Text rotation angle: 0 Not rotated 1-90 1 to 90 degrees counterclockwise 91-180 1 to 90 degrees clockwise 255 Letters are stacked top-to-bottom, but not rotated.

Declaration

int Rotation { get; set; }

Property Value

Type
System.Int32

Examples

The following code illustrates how to set Rotation for IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["E2"].Text = "Sample";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set rotation
            style.Rotation = 60;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set rotation
            format.Rotation = 40;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

Exceptions

Type	Condition
System.ArgumentOutOfRangeException	Thrown when value is more than 0xFF.

ShrinkToFit

True - shrink content to fit into cell.

Declaration

bool ShrinkToFit { get; set; }

Property Value

Type
System.Boolean

Examples

The following code illustrates how to set ShrinkToFit property for IStyle.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Add text
            worksheet["C2"].Text = "This is a sample text";
            worksheet["E2"].Text = "This is a sample text";

            //Create style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set style
            worksheet["C2"].CellStyle = style;

            //Set shrink to fit
            style.ShrinkToFit = true;

            //Get extended format
            IExtendedFormat format = worksheet["E2"].CellStyle;

            //Set shrink to fit
            format.ShrinkToFit = true;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

VerticalAlignment

Vertical alignment.

Declaration

ExcelVAlign VerticalAlignment { get; set; }

Property Value

Type
ExcelVAlign

Examples

By default VAlignBottom is set to VerticalAlignment. Here for example, we set VAlignCenter to VerticalAlignment.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "Sample";
            worksheet["C5"].Text = "Sample";

            //Set row height
            worksheet["C2"].RowHeight = 25;
            worksheet["C5"].RowHeight = 25;

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set alignment
            style.VerticalAlignment = ExcelVAlign.VAlignCenter;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["C5"].CellStyle;

            //Set alignment
            format.VerticalAlignment = ExcelVAlign.VAlignCenter;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }

WrapText

True - Text is wrapped at right border.

Declaration

bool WrapText { get; set; }

Property Value

Type
System.Boolean

Remarks

To know more about text wrapping refer Apply Wrap Text.

Examples

The following code illustrates how to set WrapText property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
            //Create worksheet
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2013;
            IWorkbook workbook = application.Workbooks.Create(1);
            IWorksheet worksheet = workbook.Worksheets[0];

            //Set text
            worksheet["C2"].Text = "This is a Sample text";
            worksheet["C5"].Text = "This is a Sample text";

            //Add style
            IStyle style = workbook.Styles.Add("CustomStyle");

            //Set wrap text
            style.WrapText = true;

            //Set style
            worksheet["C2"].CellStyle = style;

            //Get extended format
            IExtendedFormat format = worksheet["C5"].CellStyle;

            //Set wrap text
            format.WrapText = true;

            //Save and dispose
            workbook.SaveAs("CellFormats.xlsx");
            workbook.Close();
        }