Interface IWorkbook

Represents an MS Excel Workbook.

Inherited Members

IParentApplication.Application

IParentApplication.Parent

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Portable.dll

Syntax

public interface IWorkbook : IParentApplication

Properties

ActiveSheet

Gets an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active. Read-only.

Declaration

IWorksheet ActiveSheet { get; }

Property Value

Type	Description
IWorksheet

ActiveSheetIndex

Gets or sets index of the active sheet.

Declaration

int ActiveSheetIndex { get; set; }

Property Value

Type	Description
System.Int32

AddInFunctions

Gets collection of all workbook's add-in functions. Read-only.

Declaration

IAddInFunctions AddInFunctions { get; }

Property Value

Type	Description
IAddInFunctions

Allow3DRangesInDataValidation

Indicates whether to allow usage of 3D ranges in DataValidation list property (MS Excel doesn't allow).

Declaration

bool Allow3DRangesInDataValidation { get; set; }

Property Value

Type	Description
System.Boolean

ArgumentsSeparator

Gets the formula arguments separator.

Declaration

string ArgumentsSeparator { get; }

Property Value

Type	Description
System.String

Examples

The following code snippet illustrates how to get the argument separator.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the chart argumentSeparator.
string argumentSeparator = workbook.ArgumentsSeparator;

Author

Gets or sets the author of the comment.

Declaration

string Author { get; set; }

Property Value

Type	Description
System.String

BuiltInDocumentProperties

Gets collection that represents all the built-in document properties for the specified workbook. Read-only.

Declaration

IBuiltInDocumentProperties BuiltInDocumentProperties { get; }

Property Value

Type	Description
IBuiltInDocumentProperties

Examples

The following code snippet illustrates how to get the built in document properties.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the built in document properties.
IBuiltInDocumentProperties builtInDocumentProperties = workbook.BuiltInDocumentProperties;

CalculationOptions

Gets calculation options. Read-only.

Declaration

ICalculationOptions CalculationOptions { get; }

Property Value

Type	Description
ICalculationOptions

Examples

The following code snippet illustrates how to get the calculation options.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the calculation options.
ICalculationOptions calculationOptions = workbook.CalculationOptions;

Charts

Gets the collection of the chart objects.

Declaration

ICharts Charts { get; }

Property Value

Type	Description
ICharts

CodeName

Name which is used by macros to access the workbook items.

Declaration

string CodeName { get; set; }

Property Value

Type	Description
System.String

Connections

Gets the connection. Read Only.

Declaration

IConnections Connections { get; }

Property Value

Type	Description
IConnections

Examples

The following code snippet illustrates how to get the connection string.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the connection string.
IConnections connections = workbook.Connections;

ContentTypeProperties

Gets a MetaProperties collection that describes the meta data stored in the workbook. Read-only.

Declaration

IMetaProperties ContentTypeProperties { get; }

Property Value

Type	Description
IMetaProperties

CustomDocumentProperties

Gets collection that represents all the custom document properties for the specified workbook. Read-only.

Declaration

ICustomDocumentProperties CustomDocumentProperties { get; }

Property Value

Type	Description
ICustomDocumentProperties

Examples

The following code snippet illustrates how to get the custom document properties.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the document properties.
ICustomDocumentProperties documentProperties = workbook.CustomDocumentProperties;

CustomXmlparts

Gets collection that represents all the Custom Xml parts for the specified workbook. Read-only.

Declaration

ICustomXmlPartCollection CustomXmlparts { get; }

Property Value

Type	Description
ICustomXmlPartCollection

Examples

The following code snippet illustrates how to get the custom Xml parts.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Get the customXml parts.
ICustomXmlPartCollection CustomXmlparts = workbook.CustomXmlparts;

Date1904

True if the workbook uses the 1904 date system.

Declaration

bool Date1904 { get; set; }

Property Value

Type	Description
System.Boolean

DetectDateTimeInValue

Indicates whether detect string value passed to Value (and Value2) property as DateTime. Default value is true.

Declaration

bool DetectDateTimeInValue { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Setting this property to false can increase performance.

DisableMacrosStart

Disables loading of macros from document.

Declaration

bool DisableMacrosStart { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Excel on file open will simply skip macros and will work as if document does not contain them. This options works only when file contains macros (HasMacros property is True).

DisplayedTab

Gets or sets index of tab which will be displayed on document open.

Declaration

int DisplayedTab { get; set; }

Property Value

Type	Description
System.Int32

DisplayWorkbookTabs

True if the tabs are visible. otherwise False.

Declaration

bool DisplayWorkbookTabs { get; set; }

Property Value

Type	Description
System.Boolean

HasMacros

True if workbook contains VBA macros. Read-only.

Declaration

bool HasMacros { get; }

Property Value

Type	Description
System.Boolean

IsCellProtection

True if cell is protected. Read-only.

Declaration

bool IsCellProtection { get; }

Property Value

Type	Description
System.Boolean

IsHScrollBarVisible

Gets or sets a value indicating whether to display horizontal scroll bar.

Declaration

bool IsHScrollBarVisible { get; set; }

Property Value

Type	Description
System.Boolean

Examples

This sample shows how to hide horizontal scroll bar.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.IsHScrollBarVisible = false;

IsRightToLeft

Indicates whether worksheet is displayed right to left.

Declaration

bool IsRightToLeft { get; set; }

Property Value

Type	Description
System.Boolean

IsVScrollBarVisible

Gets or sets a value indicating whether to display vertical scroll bar.

Declaration

bool IsVScrollBarVisible { get; set; }

Property Value

Type	Description
System.Boolean

Examples

This sample shows how to hide vertical scroll bar.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.IsVScrollBarVisible = false;

IsWindowProtection

True if window is protected. Read-only.

Declaration

bool IsWindowProtection { get; }

Property Value

Type	Description
System.Boolean

MaxColumnCount

Gets maximum column count for each worksheet in the workbook. Read-only.

Declaration

int MaxColumnCount { get; }

Property Value

Type	Description
System.Int32

MaxRowCount

Gets maximum row count for each worksheet in the workbook. Read-only.

Declaration

int MaxRowCount { get; }

Property Value

Type	Description
System.Int32

Names

Gets Names collection.

Declaration

INames Names { get; }

Property Value

Type	Description
INames

Remarks

For an Application object, returns a Names collection that represents all the names in the active workbook. For a Workbook object, returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names).

Examples

The following code snippet illustrates how to get names.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
INames names = workbook.Names;

Palette

Get Palette colors.

Declaration

Color[] Palette { get; }

Property Value

Type	Description
Color[]

Remarks

Gets Palette of colors which an Excel document can have. Here is a table of color indexes to places in the color tool box provided by Excel application:

| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ---+---------------------------------------- |1 | 00 | 51 | 50 | 49 | 47 | 10 | 53 | 54 | |2 | 08 | 45 | 11 | 09 | 13 | 04 | 46 | 15 | |3 | 02 | 44 | 42 | 48 | 41 | 40 | 12 | 55 | |4 | 06 | 43 | 05 | 03 | 07 | 32 | 52 | 14 | |5 | 37 | 39 | 35 | 34 | 33 | 36 | 38 | 01 | ---+---------------------------------------- |6 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |7 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |

Examples

The following code illustrates how to access the default colors of excel color palette.

        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];

            //Get colors
            System.Drawing.Color[] colors = workbook.Palette;

            //Get color
            System.Drawing.Color color = colors[2];

            //Set color
            worksheet["B2"].CellStyle.Color = color;

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

Palettte

Gets Palette colors.

Declaration

Color[] Palettte { get; }

Property Value

Type	Description
Color[]

Remarks

Get Palette of colors which an Excel document can have. Here is a table of color indexes to places in the color tool box provided by Excel application:

| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ---+---------------------------------------- |1 | 00 | 51 | 50 | 49 | 47 | 10 | 53 | 54 | |2 | 08 | 45 | 11 | 09 | 13 | 04 | 46 | 15 | |3 | 02 | 44 | 42 | 48 | 41 | 40 | 12 | 55 | |4 | 06 | 43 | 05 | 03 | 07 | 32 | 52 | 14 | |5 | 37 | 39 | 35 | 34 | 33 | 36 | 38 | 01 | ---+---------------------------------------- |6 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |7 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |

Examples

The following code snippet illustrates how to get palette color.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
System.Drawing.Color[] palette = workbook.Palette; 

PasswordToOpen

Gets or sets password to encrypt or decrypt document.

Declaration

string PasswordToOpen { get; set; }

Property Value

Type	Description
System.String

PivotCaches

Gets pivot caches collection. Read-only.

Declaration

IPivotCaches PivotCaches { get; }

Property Value

Type	Description
IPivotCaches

Examples

The following code snippet illustrates how to get pivot caches.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IPivotCaches pivotCaches = workbook.PivotCaches;

PrecisionAsDisplayed

True if the precision to be used as displayed. Read-only.

Declaration

bool PrecisionAsDisplayed { get; set; }

Property Value

Type	Description
System.Boolean

ReadOnly

True if the workbook has been opened as Read-only. Read-only Boolean.

Declaration

bool ReadOnly { get; }

Property Value

Type	Description
System.Boolean

ReadOnlyRecommended

True to display a message when the file is opened, recommending that the file be opened as read-only.

Declaration

bool ReadOnlyRecommended { get; set; }

Property Value

Type	Description
System.Boolean

RowSeparator

Gets or sets row separator for array parsing.

Declaration

string RowSeparator { get; }

Property Value

Type	Description
System.String

Examples

The following code snippet illustrates how to get row separator.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string rowSeparator = workbook.RowSeparator;

Saved

True if no changes have been made to the specified workbook since it was last saved. If current value is false then setting it to true cause Save() method call.

Declaration

bool Saved { get; set; }

Property Value

Type	Description
System.Boolean

StandardFont

Gets or sets the name of the standard font.

Declaration

string StandardFont { get; set; }

Property Value

Type	Description
System.String

Examples

The following code illustrates how to set the standard font for the workbook.

        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["B2"].Text = "Text";

            //Set standard font
            workbook.StandardFont = "Arial";

            //Set standard font size
            workbook.StandardFontSize = 18;

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

StandardFontSize

Gets or sets the standard font size, in points.

Declaration

double StandardFontSize { get; set; }

Property Value

Type	Description
System.Double

Examples

The following code illustrates how to set the standard font size for the workbook.

        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["B2"].Text = "Text";

            //Set standard font
            workbook.StandardFont = "Arial";

            //Set standard font size
            workbook.StandardFontSize = 18;

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

Styles

Gets a Styles collection that represents all the styles in the specified workbook. Read-only.

Declaration

IStyles Styles { get; }

Property Value

Type	Description
IStyles

Examples

The following code snippet illustrates how to get the Styles.

        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["B2"].Text = "Text";

            //Set styles
            IStyles styles = workbook.Styles;

            //Set style
            IStyle style = styles[0];

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

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

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

TableStyles

Gets the table styles object.

Declaration

ITableStyles TableStyles { get; }

Property Value

Type	Description
ITableStyles

TabSheets

Gets collection of tab sheets. Read-only.

Declaration

ITabSheets TabSheets { get; }

Property Value

Type	Description
ITabSheets

Examples

The following code snippet illustrates how to get the tabsheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
ITabSheets tabsheets = workbook.TabSheets;

ThrowOnUnknownNames

Indicates whether exception should be thrown when unknown name was found in a formula.

Declaration

bool ThrowOnUnknownNames { get; set; }

Property Value

Type	Description
System.Boolean

Examples

The following code snippet illustrates how to gets or sets the ThrowOnUnknownNames.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.ThrowOnUnknownNames = true;
bool isThrowOnUnknownNames = workbook.ThrowOnUnknownNames;

UseFastStringSearching

Toggles string searching algorithm.Default value is true.

Declaration

bool UseFastStringSearching { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

If true then Dictionary will be used to locate string inside strings dictionary. This mode is faster but uses more memory. If false then each time string is added to strings dictionary we will have to iterate through it and compare new strings with existing ones.

VbaProject

Gets Vba Project in the workbook.

Declaration

IVbaProject VbaProject { get; }

Property Value

Type	Description
IVbaProject

Examples

The following code snippet illustrates how to get Vba Project from the workbook.

ExcelEngine engine = new ExcelEngine();
IWorkbook workbook = engine.Excel.Workbooks.Create();
IWorksheet sheet = workbook.Worksheets[0];

sheet["A1"].Value = "Test";

IShape shape = sheet.Shapes.AddAutoShapes(AutoShapeType.Rectangle, 2, 2, 40, 40);

Syncfusion.Office.IVbaProject project = workbook.VbaProject;

Syncfusion.Office.IVbaModule module = project.Modules.Add("Test", Syncfusion.Office.VbaModuleType.StdModule);

module.Code = "Sub ShowMessage()" + "\r\n" +
   "    MsgBox \"Shape Clicked!\"" + "\r\n" +
   "End Sub";

shape.OnAction = "Test.ShowMessage";

workbook.SaveAs("Output.xlsm");

workbook.Close();
engine.Dispose();

Version

Gets or sets excel version.

Declaration

ExcelVersion Version { get; set; }

Property Value

Type	Description
ExcelVersion

WorksheetGroup

Gets grouped worksheets. Read-only.

Declaration

IWorksheetGroup WorksheetGroup { get; }

Property Value

Type	Description
IWorksheetGroup

Examples

The following code snippet illustrates how to get the grouped sheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
 IWorksheetGroup worksheetGroup = workbook.WorksheetGroup;

Worksheets

Gets a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

Declaration

IWorksheets Worksheets { get; }

Property Value

Type	Description
IWorksheets

Examples

The following code snippet illustrates how to get the worksheets.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheets worksheets = workbook.Worksheets;

Methods

Activate()

Activates the first window associated with the workbook.

Declaration

void Activate()

add_OnReadOnlyFile(ReadOnlyFileEventHandler)

Declaration

void add_OnReadOnlyFile(ReadOnlyFileEventHandler value)

Parameters

Type	Name	Description
ReadOnlyFileEventHandler	value

AddFont(IFont)

Adds font to the inner fonts collection. Read-only.

Declaration

IFont AddFont(IFont fontToAdd)

Parameters

Type	Name	Description
IFont	fontToAdd	Font to add.

Returns

Type	Description
IFont	Added IFont.

Examples

The following code illustrates how to add IFont to the IWorkbook.

        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];

            //Create font
            IFont font = workbook.CreateFont();

            //Add font
            workbook.AddFont(font);

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

Clone()

Creates copy of the current instance.

Declaration

IWorkbook Clone()

Returns

Type	Description
IWorkbook	Copy of the current instance.

Close()

Closes the object without saving.

Declaration

void Close()

Examples

The following code illustrate how to closes the object without saving.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
 workbook.Close();

Close(Boolean)

Closes the object. If True, all changes will be saved.

Declaration

void Close(bool saveChanges)

Parameters

Type	Name	Description
System.Boolean	saveChanges	If True, all changes will be saved.

Examples

The following code illustrate how to closes the object with specified bool value.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
 workbook.Close(true);

Close(Boolean, String)

Closes the object and saves workbook to the specified file name.If True, all changes will be saved.

Declaration

void Close(bool SaveChanges, string Filename)

Parameters

Type	Name	Description
System.Boolean	SaveChanges	If True, all changes will be saved.
System.String	Filename	Name of the file.

Examples

The following code illustrates how to Closes the object and saves workbook to the specified file name.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
 workbook.Close(true,"Output.xlsx");

CreateDataSorter()

Creates the Data sorter to sort the data.

Declaration

IDataSort CreateDataSorter()

Returns

Type	Description
IDataSort	Returns the Data Sorter.

CreateFont()

Creates a IFont object.

Declaration

IFont CreateFont()

Returns

Type	Description
IFont	Returns the newly created IFont.

Examples

The following code illustrates how to create IFont 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
            IRichTextString richText = worksheet["B2"].RichText;

            //Create font
            IFont font = workbook.CreateFont();

            //Set color
            font.Color = ExcelKnownColors.Red;

            //Set text
            richText.Text = "Sample";

            //Set font
            richText.SetFont(0, 5, font);

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

CreateFont(IFont)

Creates font object based on another font object.

Declaration

IFont CreateFont(IFont baseFont)

Parameters

Type	Name	Description
IFont	baseFont	Base font for the new one.

Returns

Type	Description
IFont	Returns a newly created font.

Examples

The following code illustrates how to create a IFont using existing IFont.

        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
            IRichTextString richText = worksheet["B2"].RichText;

            //Get font
            IFont defaultFont = worksheet["B2"].CellStyle.Font;

            //Create font
            IFont font = workbook.CreateFont(defaultFont);

            //Set color
            font.Color = ExcelKnownColors.Red;

            //Set text
            richText.Text = "Sample";

            //Set font
            richText.SetFont(0, 5, font);

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

CreateTemplateMarkersProcessor()

Create an instance that can be used for template markers processing.

Declaration

ITemplateMarkersProcessor CreateTemplateMarkersProcessor()

Returns

Type	Description
ITemplateMarkersProcessor	Returns the Object that can be used for template markers processing.

FindAll(Boolean)

Returns the cells of the specified bool value.

Declaration

IRange[] FindAll(bool findValue)

Parameters

Type	Name	Description
System.Boolean	findValue	Value to search.

Returns

Type	Description
IRange[]	Returns all found cells, or Null if value was not found

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange[] range = workbook.FindAll(true);

FindAll(DateTime)

Returns the cells of the specified DateTime value.

Declaration

IRange[] FindAll(DateTime findValue)

Parameters

Type	Name	Description
System.DateTime	findValue	Value to search.

Returns

Type	Description
IRange[]	Returns all found cells, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange[] results = workbook.FindAll(DateTime.Now);

FindAll(Double, ExcelFindType)

Returns the cells of the specified double value with the specified ExcelFindType.

Declaration

IRange[] FindAll(double findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.Double	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange[]	Returns all found cells, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange[] results = workbook.FindAll(100.32, ExcelFindType.Number);

FindAll(String, ExcelFindType)

Returns the cells with specified string value.

Declaration

IRange[] FindAll(string findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange[]	Returns all found cells, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange[] results = workbook.FindAll("Hello World", ExcelFindType.Text);

FindAll(String, ExcelFindType, ExcelFindOptions)

Returns the cells of the specified string value with the specified ExcelFindType.

Declaration

IRange[] FindAll(string findValue, ExcelFindType flags, ExcelFindOptions findOptions)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.
ExcelFindOptions	findOptions	Way to search.

Returns

Type	Description
IRange[]	Returns all found cells, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange[] results = workbook.FindAll("Hello World", ExcelFindType.Text, ExcelFindOptions.MatchCase);

FindAll(TimeSpan)

Returns the cells of the specified TimeSpan value.

Declaration

IRange[] FindAll(TimeSpan findValue)

Parameters

Type	Name	Description
System.TimeSpan	findValue	Value to search.

Returns

Type	Description
IRange[]	Return all found cells, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
TimeSpan value = new TimeSpan(2, 30, 30);
IRange[] results = workbook.FindAll(value);

FindFirst(Boolean)

Returns the first occurrence of the specified bool value.

Declaration

IRange FindFirst(bool findValue)

Parameters

Type	Name	Description
System.Boolean	findValue	Value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IRange result = workbook.FindFirst(true);

FindFirst(DateTime)

Returns the first occurrence of the specified DateTime value.

Declaration

IRange FindFirst(DateTime findValue)

Parameters

Type	Name	Description
System.DateTime	findValue	Value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
DateTime dateTime = DateTime.Now;
IRange result = workbook.FindFirst(dateTime);

FindFirst(Double, ExcelFindType)

Returns the first occurrence of the specified double value with the specified ExcelFindType.

Declaration

IRange FindFirst(double findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.Double	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
double value = 9.00;
IRange result = workbook.FindFirst(value, ExcelFindType.Number);

FindFirst(String, ExcelFindType)

Returns the first occurrence of the specified string value with the specified ExcelFindType.

Declaration

IRange FindFirst(string findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string value = "value";
IRange result = workbook.FindFirst(value, ExcelFindType.Text);

FindFirst(String, ExcelFindType, ExcelFindOptions)

Returns the first occurrence of the specified string value with the specified ExcelFindType and ExcelFindOptions.

Declaration

IRange FindFirst(string findValue, ExcelFindType flags, ExcelFindOptions findOptions)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.
ExcelFindOptions	findOptions	Way to search the value.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string findvalue = "value";
IRange result = workbook.FindFirst(findvalue, ExcelFindType.Text, ExcelFindOptions.None);

FindFirst(TimeSpan)

Returns the first occurrence of the specified TimeSpan value.

Declaration

IRange FindFirst(TimeSpan findValue)

Parameters

Type	Name	Description
System.TimeSpan	findValue	Value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
TimeSpan timeSpan = new TimeSpan(2, 30, 30);
IRange result = workbook.FindFirst(timeSpan);

FindStringEndsWith(String, ExcelFindType)

Returns the first occurrence that ends with the specified string value.

Declaration

IRange FindStringEndsWith(string findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string value = "value";
IRange result =  workbook.FindStringEndsWith(value, ExcelFindType.Text);

FindStringEndsWith(String, ExcelFindType, Boolean)

Returns the first occurrence that ends with the specified string value which ignores the case.

Declaration

IRange FindStringEndsWith(string findValue, ExcelFindType flags, bool ignoreCase)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.
System.Boolean	ignoreCase	true to ignore case wen comparing this string to the value;otherwise,false

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string value = "value";
IRange result =  workbook.FindStringEndsWith(value, ExcelFindType.Text, true);

FindStringStartsWith(String, ExcelFindType)

Returns the first occurrence that starts with the specified string value.

Declaration

IRange FindStringStartsWith(string findValue, ExcelFindType flags)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string value = "value";
IRange result =  workbook.FindStringStartsWith(value, ExcelFindType.Text);

FindStringStartsWith(String, ExcelFindType, Boolean)

Returns the first occurrence that starts with the specified string value which ignores the case.

Declaration

IRange FindStringStartsWith(string findValue, ExcelFindType flags, bool ignoreCase)

Parameters

Type	Name	Description
System.String	findValue	Value to search.
ExcelFindType	flags	Type of value to search.
System.Boolean	ignoreCase	true to ignore case wen comparing this string to the value;otherwise,false

Returns

Type	Description
IRange	First found cell, or Null if value was not found.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string value = "value";
IRange result =  workbook.FindStringStartsWith(value, ExcelFindType.Text, true);

GetNearestColor(Color)

Gets the nearest color with the specified Color structure from Workbook palette.

Declaration

ExcelKnownColors GetNearestColor(Color color)

Parameters

Type	Name	Description
Color	color	system color.

Returns

Type	Description
ExcelKnownColors	Returns Color index from workbook palette.

Examples

The following code illustrates how to get the indexed color from ExcelKnownColors for the given color from structure.

        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];

            //Get color
            ExcelKnownColors color = workbook.GetNearestColor(System.Drawing.Color.Red);

            //Set color
            worksheet["B2"].CellStyle.ColorIndex = color;

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

GetNearestColor(Int32, Int32, Int32)

Gets the nearest color with the specified red, green, and blue color values from Workbook palette.

Declaration

ExcelKnownColors GetNearestColor(int r, int g, int b)

Parameters

Type	Name	Description
System.Int32	r	Red component of the color.
System.Int32	g	Green component of the color.
System.Int32	b	Blue component of the color.

Returns

Type	Description
ExcelKnownColors	Returns Color index from workbook palette.

Examples

The following code illustrates how to get the indexed color from ExcelKnownColors for the given color from structure.

        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];

            //Get color
            ExcelKnownColors color = workbook.GetNearestColor(255, 0, 0);

            //Set color
            worksheet["B2"].CellStyle.ColorIndex = color;

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

GetPaletteColor(ExcelKnownColors)

Returns Color object from predefined colors by its index.

Declaration

Color GetPaletteColor(ExcelKnownColors color)

Parameters

Type	Name	Description
ExcelKnownColors	color	Index from palette array.

Returns

Type	Description
Color	RGB Color.

Examples

The following code illustrates how to get the RGB color value for the specified color from ExcelKnownColors enumeration.

        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];

            //Get color
            System.Drawing.Color color = workbook.GetPaletteColor(ExcelKnownColors.Red);

            //Set color
            worksheet["B2"].CellStyle.Color = color;

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

MarkAsFinal()

Marks workbook as final. Read-Only.

Declaration

void MarkAsFinal()

Protect(Boolean, Boolean)

Sets protection for workbook.

Declaration

void Protect(bool bIsProtectWindow, bool bIsProtectContent)

Parameters

Type	Name	Description
System.Boolean	bIsProtectWindow	Indicates if protect workbook window.
System.Boolean	bIsProtectContent	Indicates if protect workbook content.

Examples

The following code snippet illustrates how to protect the workbook.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.Protect(true, true);

Protect(Boolean, Boolean, String)

Sets protection for workbook with specified password.

Declaration

void Protect(bool bIsProtectWindow, bool bIsProtectContent, string password)

Parameters

Type	Name	Description
System.Boolean	bIsProtectWindow	Indicates if protect workbook window.
System.Boolean	bIsProtectContent	Indicates if protect workbook content.
System.String	password	Password to protect with.

Examples

The following code snippet illustrates how to protect the workbook using password.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.Protect(true, true, "Password");

remove_OnReadOnlyFile(ReadOnlyFileEventHandler)

Declaration

void remove_OnReadOnlyFile(ReadOnlyFileEventHandler value)

Parameters

Type	Name	Description
ReadOnlyFileEventHandler	value

Replace(String, DateTime)

Replaces string with the specified DateTime value.

Declaration

void Replace(string oldValue, DateTime newValue)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.DateTime	newValue	The DateTime to replace all occurrences of oldValue.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
DateTime dateTime = DateTime.Now;
workbook.Replace(oldValue, dateTime);

Replace(String, Double)

Replaces string with the specified double value.

Declaration

void Replace(string oldValue, double newValue)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.Double	newValue	The double value to replace all occurrences of oldValue.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
double newValue = 9.00;
workbook.Replace(oldValue, newValue);

Replace(String, Double[], Boolean)

Replaces specified string with the specified array of double values.

Declaration

void Replace(string oldValue, double[] newValues, bool isVertical)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.Double[]	newValues	Array of new values.
System.Boolean	isVertical	Indicates whether array should be inserted vertically.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
double[] newValues = { 1.00, 3.00 };
workbook.Replace(oldValue, newValues, true);

Replace(String, Int32[], Boolean)

Replaces specified string with the specified array of int values.

Declaration

void Replace(string oldValue, int[] newValues, bool isVertical)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.Int32[]	newValues	Array of new values.
System.Boolean	isVertical	Indicates whether array should be inserted vertically.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
int[] newValues = { 1, 2 };
workbook.Replace(oldValue, newValues, true);

Replace(String, String)

Replaces string with the specified another string value.

Declaration

void Replace(string oldValue, string newValue)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.String	newValue	The string to replace all occurrences of oldValue.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
string newValue = "NewValue";
workbook.Replace(oldValue, newValue);

Replace(String, String, ExcelFindOptions)

Replaces string with the specified string value based on the given ExcelFindOptions.

Declaration

void Replace(string oldValue, string newValue, ExcelFindOptions findOptions)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.String	newValue	The string to replace all occurrences of oldValue.
ExcelFindOptions	findOptions	Specifies the find options for the oldValue.

Examples

The following code snippet illustrates how to replace the string with another string.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
string oldValue = "Find";
string newValue = "NewValue";
sheet.Replace(oldValue, newValue, ExcelFindOptions.MatchCase);

Replace(String, String[], Boolean)

Replaces specified string with the specified array of string values.

Declaration

void Replace(string oldValue, string[] newValues, bool isVertical)

Parameters

Type	Name	Description
System.String	oldValue	The string to be replaced.
System.String[]	newValues	Array of new values.
System.Boolean	isVertical	Indicates whether array should be inserted vertically.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
string oldValue = "Find";
string[] newValues = { "X values", "Y values" };
workbook.Replace(oldValue, newValues, true);

ResetPalette()

Recover palette to default values.

Declaration

void ResetPalette()

Examples

The following code snippets illustrates how to reset the palette.

        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];

            //Get colors
            System.Drawing.Color[] colors = workbook.Palette;

            //Check color
            Console.WriteLine(colors[2].Name);

            //Set color
            colors[2] = System.Drawing.Color.Yellow;

            //Reset palette
            workbook.ResetPalette();

            //Check color
            Console.WriteLine(workbook.Palette[2].Name);

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

SaveAs(Stream)

Saves workbook as stream.

Declaration

void SaveAs(Stream stream)

Parameters

Type	Name	Description
System.IO.Stream	stream	Stream that will receive workbook data.

Remarks

This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, ASP.NET Core, Blazor, and Xamarin platforms.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
Stream stream = new MemoryStream();
workbook.SaveAs(stream);

SaveAs(Stream, ExcelSaveType)

Saves workbook as stream with specified ExcelSaveType.

Declaration

void SaveAs(Stream stream, ExcelSaveType saveType)

Parameters

Type	Name	Description
System.IO.Stream	stream	Stream that will receive workbook data.
ExcelSaveType	saveType	Type of the Excel file.

Remarks

This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, ASP.NET Core, Blazor, and Xamarin platforms.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
Stream stream = new MemoryStream();
workbook.SaveAs(stream, ExcelSaveType.SaveAsXLS);

SaveAs(Stream, String)

Saves workbook as stream using separator. Used only for CSV files.

Declaration

void SaveAs(Stream stream, string separator)

Parameters

Type	Name	Description
System.IO.Stream	stream	Stream to save.
System.String	separator	Denotes separator for the CSV file types.

Remarks

This method is supported on Windows Forms, WPF, ASP.NET, ASP.NET MVC, ASP.NET Core, Blazor, and Xamarin platforms.

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
Stream stream = new MemoryStream();
workbook.SaveAs(stream, ",");

SaveAs(Stream, String, Encoding)

Saves workbook as stream with given separator and encoding. Used only for CSV files.

Declaration

void SaveAs(Stream stream, string separator, Encoding encoding)

Parameters

Type	Name	Description
System.IO.Stream	stream	Stream to save the CSV file.
System.String	separator	Denotes separator for the CSV file types.
System.Text.Encoding	encoding	The name of a character encoding that is supported by the .NET Framework.

Remarks

This overloaded method can be used to apply encoding to the CSV file in particular. The default encoding type is UTF-8. Other encoding types include ASCII, UTF-7, and UTF-32.

Examples

    using(ExcelEngine engine = new ExcelEngine())
    {
      IApplication application = engine.Excel;
      IWorkbook workbook = application.Workbooks.Create(1);
      IWorksheet worksheet = workbook.Worksheets[0];
      worksheet["A1"].Text = "This";
      worksheet["B1"].Text = "is";
      worksheet["C1"].Text = "a";
      worksheet["D1"].Text = "sample";
      worksheet["E1"].Text = "to";
      worksheet["F1"].Text = "set";
      worksheet["G1"].Text = "encoding";
      MemoryStream stream = new MemoryStream();
      workbook.SaveAs(stream, ",",Encoding.Unicode);
    }

SetColorOrGetNearest(Color)

Sets the color or Gets nearest color.

Declaration

ExcelKnownColors SetColorOrGetNearest(Color color)

Parameters

Type	Name	Description
Color	color	System color

Returns

Type	Description
ExcelKnownColors	Color index from workbook palette.

Remarks

If there is at least one free color, define a new color; if not, search for the closest one to the specified Color structure from Workbook palette.

Examples

The following code illustrates how to get the indexed color from ExcelKnownColors for the given color from structure.

        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];

            //Get color
            ExcelKnownColors color = workbook.SetColorOrGetNearest(System.Drawing.Color.Red);

            //Set color
            worksheet["B2"].CellStyle.ColorIndex = color;

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

SetColorOrGetNearest(Int32, Int32, Int32)

Sets the color or Gets nearest color with the specified red, green and blue color value.

Declaration

ExcelKnownColors SetColorOrGetNearest(int r, int g, int b)

Parameters

Type	Name	Description
System.Int32	r	Red component of the color.
System.Int32	g	Green component of the color.
System.Int32	b	Blue component of the color.

Returns

Type	Description
ExcelKnownColors	Color index from workbook palette.

Remarks

If there is at least one free color, define a new color; if not, search for the closest one to the specified by red, green, and blue values color from Workbook palette.

Examples

The following code snippet illustrates how to set color or get nearest color by red,green and blue.

workbook.SetColorOrGetNearest(123,45,56);

The following code illustrates how to get the indexed color from ExcelKnownColors for the given color from structure.

        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];

            //Get color
            ExcelKnownColors color = workbook.SetColorOrGetNearest(255, 0, 0);

            //Set color
            worksheet["B2"].CellStyle.ColorIndex = color;

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

SetPaletteColor(Int32, Color)

Sets the palette color for the given index.

Declaration

void SetPaletteColor(int index, Color color)

Parameters

Type	Name	Description
System.Int32	index	Index of Color in array.
Color	color	New color which must be set.

Examples

The following code snippet illustrates how to set palette color.

        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 palette color
            workbook.SetPaletteColor(10, System.Drawing.Color.Red);

            //Set color
            worksheet["B2"].CellStyle.Color = workbook.Palette[10];

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

SetSeparators(Char, Char)

Sets separators for formula parsing.

Declaration

void SetSeparators(char argumentsSeparator, char arrayRowsSeparator)

Parameters

Type	Name	Description
System.Char	argumentsSeparator	Arguments separator to set.
System.Char	arrayRowsSeparator	Array rows separator to set.

Examples

The following code illustrates how to set separators.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
workbook.SetSeparators(',', ';');

SetWriteProtectionPassword(String)

Sets write protection for workbook using password.

Declaration

void SetWriteProtectionPassword(string password)

Parameters

Type	Name	Description
System.String	password	Password to set.

Examples

The following code snippet illustrates how to set write protection password.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
 workbook.SetWriteProtectionPassword("Password");

Unprotect()

Unprotects workbook.

Declaration

void Unprotect()

Examples

The following code illustrates how to unprotect a workbook.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Unprotects the password.
workbook.Unprotect();

Unprotect(String)

Unprotects workbook using password.

Declaration

void Unprotect(string password)

Parameters

Type	Name	Description
System.String	password	Password to unprotect workbook.

Remarks

Throws ArgumentOutOfRangeException when password is wrong.

Examples

The following code illustrates how to unprotect a workbook using password.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
//Unprotects the password using password.
workbook.Unprotect("Password");

Events

OnFileSaved

This event is fired after workbook is successfully saved.

Declaration

event EventHandler OnFileSaved

Event Type

Type	Description
System.EventHandler

OnReadOnlyFile

This event is fired when trying to save to a Read-only file.

Declaration

event ReadOnlyFileEventHandler OnReadOnlyFile

Event Type

Type	Description
ReadOnlyFileEventHandler