Interface IWorksheet

Represents a worksheet in a workbook.

Inherited Members
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IWorksheet : ITabSheet, IParentApplication, ICalcData

Properties

ActivePane

Gets or sets index of the active pane.

Declaration
int ActivePane { get; set; }
Property Value
Type
System.Int32

AutoFilters

Gets the auto filters collection in the worksheet. Read-only.

Declaration
IAutoFilters AutoFilters { get; }
Property Value
Type
IAutoFilters

CalcEngine

Gets or sets the a CalcEngine object associated with implementation.

Declaration
CalcEngine CalcEngine { get; set; }
Property Value
Type
CalcEngine

Cells

Gets the used cells in the worksheet. Read-only.

Declaration
IRange[] Cells { get; }
Property Value
Type
IRange[]

Columns

Gets a Range object that represents all the columns in the specified worksheet. Read-only.

Declaration
IRange[] Columns { get; }
Property Value
Type
IRange[]

Comments

Gets the IComments collection in the worksheet.

Declaration
IComments Comments { get; }
Property Value
Type
IComments
Examples

The following code illustrates how to access the IComments collection in the worksheet.

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

            // Adding comments to a cell.
            ICommentShape comment1 = sheet.Range["A1"].AddComment();
            ICommentShape comment2 = sheet.Range["B1"].AddComment();

            //Set comment text
            comment1.Text= "Comment1";
            comment2.Text= "Comment2";

            //Check count
            Console.Write(sheet.Comments.Count);

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

CustomProperties

Gets a collection of custom properties of the worksheet. Read-only.

Declaration
IWorksheetCustomProperties CustomProperties { get; }
Property Value
Type
IWorksheetCustomProperties

DataSorter

Gets the DataSorter.

Declaration
IDataSort DataSorter { get; }
Property Value
Type Description
IDataSort

The sparkline groups.
Examples

The following code illustrates how to get the DataSorter from the sheet.

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

//Create DataSorter for worksheet.
IDataSort sheetSort = workbook.Worksheets[0].DataSorter;

//Adding Sort range for worksheet
sheetSort.SortRange = workbook.Worksheets[0].UsedRange;

//Adding Sorting fields for DataSorter.
sheetSort.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);

//Perform Sorting for worksheet range.
sheetSort.Sort();

workbook.SaveAs("SortedExcel.xlsx");
workbook.Close();
excelEngine.Dispose();

DisplayPageBreaks

Gets or sets a value that indicates whether page breaks (both automatic and manual) on the worksheet are displayed.

Declaration
bool DisplayPageBreaks { get; set; }
Property Value
Type
System.Boolean

FirstVisibleColumn

Gets or sets the first visible column index.

Declaration
int FirstVisibleColumn { get; set; }
Property Value
Type
System.Int32

FirstVisibleRow

Gets or sets the first visible row index.

Declaration
int FirstVisibleRow { get; set; }
Property Value
Type
System.Int32

GridLineColor

Gets or sets the color of the Grid line in the worksheet.

Declaration
ExcelKnownColors GridLineColor { get; set; }
Property Value
Type
ExcelKnownColors
Examples

The following code illustrates how to set the grid line color.

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

            //Set grid lines color
            worksheet.GridLineColor = ExcelKnownColors.Red;

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

HasOleObject

Gets whether the OLE object is present in the worksheet. Read-only.

Declaration
bool HasOleObject { get; }
Property Value
Type Description
System.Boolean

True if this instance is OLE object; otherwise, False.
Remarks

This property is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.

Examples

The following code illustrates how to access the IListObjects collection in the worksheet to add a new IOleObject.

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

            //Create image stream
            System.Drawing.Image image = System.Drawing.Image.FromFile("image.png");

            //Add ole object
            IOleObject oleObject = worksheet.OleObjects.Add("Shapes.xlsx", image, OleLinkType.Embed);

            //Check HasOleObject
            Console.Write(worksheet.HasOleObject);

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

HorizontalSplit

Gets or sets the position of horizontal split in the worksheet.

Declaration
int HorizontalSplit { get; set; }
Property Value
Type
System.Int32
Remarks

Position of the horizontal split (by, 0 = No horizontal split): Unfrozen pane: Height of the top pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible rows in top pane(s)

HPageBreaks

Gets a HPageBreaksCollection that represents the horizontal page breaks in the worksheet. Read-only.

Declaration
IHPageBreaks HPageBreaks { get; }
Property Value
Type
IHPageBreaks

Gets a hyperlink collections in the worksheet. Read-only.

Declaration
IHyperLinks HyperLinks { get; }
Property Value
Type
IHyperLinks

Index

Gets the index number of the worksheet within the collection of worksheet. Read-only.

Declaration
int Index { get; }
Property Value
Type
System.Int32

IsDisplayZeros

True if zero values to be displayed. otherwise, False.

Declaration
bool IsDisplayZeros { get; set; }
Property Value
Type
System.Boolean

IsFreezePanes

Defines whether freezed panes are applied.

Declaration
bool IsFreezePanes { get; }
Property Value
Type
System.Boolean

IsGridLinesVisible

True if grid lines are visible. otherwise, False.

Declaration
bool IsGridLinesVisible { get; set; }
Property Value
Type
System.Boolean
Examples

The following code illustrates how to set visibility for grid lines.

        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 grid line visibility
            worksheet.IsGridLinesVisible = false;

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

IsRowColumnHeadersVisible

True if row and column headers are visible. otherwise, False.

Declaration
bool IsRowColumnHeadersVisible { get; set; }
Property Value
Type
System.Boolean

IsStringsPreserved

True if all values in the worksheet are preserved as strings. otherwise, False.

Declaration
bool IsStringsPreserved { get; set; }
Property Value
Type
System.Boolean
Examples

The following code illustrates if the values in the worksheet are preserved as strings.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.IsStringsPreserved = true;
worksheet["A1"].Value = "10";
worksheet["A2"].Value = "Test";
worksheet["A3"].Value = "=SUM(1+1)";

Item[Int32, Int32]

Gets or sets cell range by row and column index. Row and column indexes are one-based.

Declaration
IRange this[int row, int column] { get; }
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Property Value
Type
IRange

Item[Int32, Int32, Int32, Int32]

Get cell Range. Row and column indexes are one-based. Read-only.

Declaration
IRange this[int row, int column, int lastRow, int lastColumn] { get; }
Parameters
Type Name Description
System.Int32 row

First row index. One-based.
System.Int32 column

First column index. One-based.
System.Int32 lastRow

Last row index. One-based.
System.Int32 lastColumn

Last column index. One-based.
Property Value
Type
IRange

Item[String]

Gets cell Range. Read-only.

Declaration
IRange this[string name] { get; }
Parameters
Type Name Description
System.String name
Property Value
Type
IRange

Item[String, Boolean]

Gets cell Range with R1C1Notation flag. Read-only.

Declaration
IRange this[string name, bool IsR1C1Notation] { get; }
Parameters
Type Name Description
System.String name
System.Boolean IsR1C1Notation
Property Value
Type
IRange

LeftVisibleColumn

Gets or sets the left visible column of the worksheet.

Declaration
int LeftVisibleColumn { get; set; }
Property Value
Type
System.Int32
Examples

The following code illustrates how to get the left visible column.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
int leftVisibleColumn = worksheet.LeftVisibleColumn;

ListObjects

Gets a collection of list objects in the worksheet. Read-only.

Declaration
IListObjects ListObjects { get; }
Property Value
Type
IListObjects

MergedCells

Gets all the merged ranges in the worksheet. Read-only.

Declaration
IRange[] MergedCells { get; }
Property Value
Type
IRange[]
Examples

The following code illustrates how to get the merged ranges.

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

            //Merge cells
            worksheet["C2:D2"].Merge();
            worksheet["F3:G3"].Merge();

            //Get merged ranges
            IRange[] mergedRanges = worksheet.MergedCells;

            //Get merged range count
            Console.Write(mergedRanges.Length);

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

MigrantRange

Declaration
IMigrantRange MigrantRange { get; }
Property Value
Type
IMigrantRange

Names

Gets a Names collection that represents the worksheet-specific names (names defined with the "WorksheetName!" prefix) in the worksheet. Read-only.

Declaration
INames Names { get; }
Property Value
Type
INames

OleObjects

Gets IOleObjects collection in the worksheet.

Declaration
IOleObjects OleObjects { get; }
Property Value
Type Description
IOleObjects

The OLE objects.
Remarks

This property is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.

Examples

The following code illustrates how to access the IListObjects collection in the worksheet to add a new IOleObject.

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

            //Create image stream
            System.Drawing.Image image = System.Drawing.Image.FromFile("image.png");

            //Add ole object
            IOleObject oleObject = worksheet.OleObjects.Add("Shapes.xlsx", image, OleLinkType.Embed);

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

PageSetup

Gets a PageSetup object that contains all the page setup settings for the specified object. Read-only.

Declaration
IPageSetup PageSetup { get; }
Property Value
Type
IPageSetup

PivotTables

Gets a collection of pivot tables in the worksheet. Read-only.

Declaration
IPivotTables PivotTables { get; }
Property Value
Type
IPivotTables

Range

Gets a Range object that represents a cell or a range of cells in the worksheet.

Declaration
IRange Range { get; }
Property Value
Type
IRange

Rows

Gets a Range object that represents the rows in the specified worksheet. Read-only.

Declaration
IRange[] Rows { get; }
Property Value
Type
IRange[]

Scenarios

Gets the scenarios in the current worksheet.

Declaration
IScenarios Scenarios { get; }
Property Value
Type
IScenarios
Examples

The following code illustrates how to get the scenarios for the current worksheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
IScenarios scenarios = sheet.Scenarios;

Slicers

Gets the collection of slicers present in the worksheet.

Declaration
ISlicers Slicers { get; }
Property Value
Type
ISlicers

SparklineGroups

Gets the sparkline groups.

Declaration
ISparklineGroups SparklineGroups { get; }
Property Value
Type Description
ISparklineGroups

The sparkline groups.
Examples

The following code illustrates how to get the Spark line groups from the sheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//A new Sparkline group is added to the sheet sparklinegroups
ISparklineGroup sparklineGroup = sheet.SparklineGroups.Add();

//Set the Sparkline group type as line
sparklineGroup.SparklineType = SparklineType.Line;

//Set to display the empty cell as line
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Line;

//Sparkline group style properties
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = System.Drawing.Color.Green;
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = System.Drawing.Color.DarkOrange;
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = System.Drawing.Color.DarkBlue;
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = System.Drawing.Color.DarkViolet;
sparklineGroup.ShowMarkers = true;
sparklineGroup.MarkersColor = System.Drawing.Color.Black;
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = System.Drawing.Color.Red;

//set the line weight
sparklineGroup.LineWeight = 0.3;
//The sparklines are added to the sparklinegroup.
ISparklines sparklines = sparklineGroup.Add();

//Set the Sparkline Datarange 
IRange dataRange = sheet.Range["D6:G17"];
//Set the Sparkline Reference range
IRange referenceRange = sheet.Range["H6:H17"];

//Create a sparkline with the datarange and reference range
sparklines.Add(dataRange, referenceRange);

ISparklineGroups sparkline = sheet.SparklineGroups;

SplitCell

Gets split cell range.

Declaration
IRange SplitCell { get; }
Property Value
Type
IRange

StandardHeight

Gets or sets the standard (default) height of all the rows in the worksheet, in points.

Declaration
double StandardHeight { get; set; }
Property Value
Type
System.Double
Examples

The following code illustrates how to get the standard height.

        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 = "Text";

            //Set standard height
            worksheet.StandardHeight = 40;

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

StandardHeightFlag

Gets or sets the standard (default) height option flag.

Declaration
bool StandardHeightFlag { get; set; }
Property Value
Type
System.Boolean
Remarks

Which defines that standard (default) row height and book default font height do not match.

StandardWidth

Gets or sets the standard (default) width of all the columns in the worksheet.

Declaration
double StandardWidth { get; set; }
Property Value
Type
System.Double
Examples

The following code illustrates how to get the standard width.

        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 = "Text";

            //Set standard width
            worksheet.StandardWidth = 80;

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

ThreadedComments

Gets the threaded comments collection for a current worksheet.

Declaration
IThreadedComments ThreadedComments { get; }
Property Value
Type Description
IThreadedComments

The IThreadedComments collection.
Examples

The following code illustrates how to access ThreadedComments property of IWorksheet

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

            //Add threaded comment
            IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("Hello","User");

            //Get the threaded comments collection
            IThreadedComments threadedComments = worksheet.ThreadedComments.

            //Get the threaded comments count
            int count = threadedComments.Count;

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

TopVisibleRow

Gets or sets the top visible row of the worksheet.

Declaration
int TopVisibleRow { get; set; }
Property Value
Type
System.Int32
Examples

The following code illustrates how to get the top visible row.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
int topVisibleRow = sheet.TopVisibleRow;

Type

Gets the worksheet type. Read-only ExcelSheetType.

Declaration
ExcelSheetType Type { get; }
Property Value
Type
ExcelSheetType

UsedCells

Gets all not empty or accessed cells. Read-only.

Declaration
IRange[] UsedCells { get; }
Property Value
Type
IRange[]
Remarks

WARNING: This property creates Range object for each cell in the worksheet and creates new array each time user calls to it. It can cause huge memory usage especially if called frequently.

Examples

The following code illustrates how to get the used cells range of a sheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] usedCellsRange = sheet.UsedCells;

UsedRange

Gets a Range object that represents the used range on the specified worksheet. Read-only.

Declaration
IRange UsedRange { get; }
Property Value
Type
IRange
Examples

The following code illustrates how to get used range on the specified worksheet.

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

            //Set text
            worksheet["B2"].Text = "Text";
            worksheet["J5"].Text = "Text";

            //Set Color
            worksheet["J3"].CellStyle.ColorIndex = ExcelKnownColors.Red;

            //Get used range
            Console.Write(worksheet.UsedRange.AddressLocal);

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

UsedRangeIncludesFormatting

Gets or sets whether used range should include cells with formatting.

Declaration
bool UsedRangeIncludesFormatting { get; set; }
Property Value
Type
System.Boolean
Remarks

There are two different algorithms to create UsedRange object:

  1. Default. This property = true. The cell is included into UsedRange, even data is empty (maybe some formatting changed, maynot be - cell was accessed and record was created).
  2. This property = false. In this case XlsIO tries to remove empty rows and columns from all sides to make UsedRange smaller.
Examples

The following code illustrates how to set UsedRangeIncludesFormatting 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 = "Text";

            //Add format
            worksheet["D3"].CellStyle.ColorIndex = ExcelKnownColors.Red;

            //Set used range includes formatting
            worksheet.UsedRangeIncludesFormatting = false;

            //Get used range
            Console.Write(worksheet.UsedRange.AddressLocal);

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

UseRangesCache

Indicates whether all created range objects should be cached or not.

Declaration
bool UseRangesCache { get; set; }
Property Value
Type
System.Boolean

VerticalSplit

Gets or sets the position of vertical split in the worksheet.

Declaration
int VerticalSplit { get; set; }
Property Value
Type
System.Int32
Remarks

Position of the vertical split (px, 0 = No vertical split): Unfrozen pane: Width of the left pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible columns in left pane(s)

View

Gets or sets the view setting of the worksheet.

Declaration
SheetView View { get; set; }
Property Value
Type
SheetView
Examples

The following code illustrates how to set the view of the sheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.View = SheetView.PageLayout;

VPageBreaks

Gets a VPageBreaksCollection that represents the vertical page breaks on the sheet. Read-only.

Declaration
IVPageBreaks VPageBreaks { get; }
Property Value
Type
IVPageBreaks

Zoom

Gets or sets the value that represents zoom factor of document. Value must be in range from 10 till 400.

Declaration
int Zoom { get; set; }
Property Value
Type
System.Int32
Examples

The following code illustrates how to set zoom level of the sheet.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Zoom = 200;

Methods

add_CellValueChanged(RangeImpl.CellValueChangedEventHandler)

Declaration
void add_CellValueChanged(RangeImpl.CellValueChangedEventHandler value)
Parameters
Type Name Description
RangeImpl.CellValueChangedEventHandler value

add_ExportDataTableEvent(WorksheetImpl.ExportDataTableEventHandler)

Declaration
void add_ExportDataTableEvent(WorksheetImpl.ExportDataTableEventHandler value)
Parameters
Type Name Description
WorksheetImpl.ExportDataTableEventHandler value

add_MissingFunction(MissingFunctionEventHandler)

Declaration
void add_MissingFunction(MissingFunctionEventHandler value)
Parameters
Type Name Description
MissingFunctionEventHandler value

AdvancedFilter(ExcelFilterAction, IRange, IRange, IRange, Boolean)

Filters or copies data from a list based on a criteria range..

Declaration
void AdvancedFilter(ExcelFilterAction filterInPlace, IRange filterRange, IRange criteriaRange, IRange copyToRange, bool isUnique)
Parameters
Type Name Description
ExcelFilterAction filterInPlace

Whether filter in the place or copy to another place.
IRange filterRange

The filter range.
IRange criteriaRange

The criteria range.
IRange copyToRange

The destination range for the copied rows if ExcelFilterAction is FilterCopy. Otherwise, this argument is ignored.
System.Boolean isUnique

True to filter unique records; Otherwise filters all the records that meet the criteria. The default value is False.

AutofitColumn(Int32)

Changes the width of the specified column to achieve the best fit.

Declaration
void AutofitColumn(int colIndex)
Parameters
Type Name Description
System.Int32 colIndex

One-based column index.
Examples

The following code illustrates how to Auto-fit the column.

        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["A1"].Text = "Sample text in cell";

            //Set auto fit
            worksheet.AutofitColumn(1);

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

AutofitRow(Int32)

Changes the height of the specified row to achieve the best fit.

Declaration
void AutofitRow(int rowIndex)
Parameters
Type Name Description
System.Int32 rowIndex

One-based row index.
Examples

The following code illustrates how to Auto-fit the row.

        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 data
            worksheet["C2"].Value = "Sample text";

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

            //Set font
            IFont font = style.Font;

            //Set font size
            font.Size = 18;

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

            //Set auto fit
            worksheet.AutofitRow(2);

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

Calculate()

Calculate all the formulas in worksheet.

Declaration
void Calculate()
Examples

The following code illustrates how to calculate all the formulas in the worksheet.

    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Open("Formulas.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    // Calculate all the formulas in the worksheet.
    worksheet.Calculate();

    workbook.SaveAs("Calculation.xlsx");
    workbook.Close();
    excelEngine.Dispose();

Clear()

Clears the worksheet data, formats and merged cells.

Declaration
void Clear()

ClearData()

Clears the worksheet data.

Declaration
void ClearData()

ColumnWidthToPixels(Double)

Converts the specified column width from points to pixels.

Declaration
int ColumnWidthToPixels(double Width)
Parameters
Type Name Description
System.Double Width

Width in points.
Returns
Type Description
System.Int32

Column width in pixels.

Contains(Int32, Int32)

Checks whether the specified cell is initialized or accessed.

Declaration
bool Contains(int iRow, int iColumn)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
Returns
Type Description
System.Boolean

True if the cell is initialized or accessed by the user; otherwise, False.
Examples

The following code illustrates if the specified cells contains a value.

 ExcelEngine excelEngine = new ExcelEngine();
 IApplication application = excelEngine.Excel;
 IWorkbook workbook = application.Workbooks.Create(1);
 IWorksheet worksheet = workbook.Worksheets[0];
 worksheet.Range["A1"].Text = "Hello";
 worksheet.Contains(1, 1);
 workbook.Version = ExcelVersion.Excel2013;
 workbook.SaveAs("Output.xlsx");
 workbook.Close();
 excelEngine.Dispose();

ConvertToImage(IRange, ExportImageOptions, Stream)

Converts the specified range into image with the image settings given.

Declaration
void ConvertToImage(IRange range, ExportImageOptions imageOptions, Stream outputStream)
Parameters
Type Name Description
IRange range

The range which is to be converted to image.
ExportImageOptions imageOptions

object that defines Image quality and image format to be used for conversion.
System.IO.Stream outputStream

Stream to hold the converted image data.
Remarks

Subscript/Superscript,Shrink to fit,Shapes (except TextBox shape and Image),Charts and Chart Worksheet and Complex conditional formatting features are not supported in Worksheet to image conversion. Gradient fill is partially supported

Examples
// Converts the Range used in worksheet to image.
Stream image = new FileStream("Sample.png", FileMode.Create, FileAccess.ReadWrite);
ExportImageOptions imageOptions = new ExportImageOptions();
imageOptions.ImageFormat = ExportImageFormat.Jpeg;
sheet.ConvertToImage(sheet.UsedRange, imageOptions, image);

ConvertToImage(IRange, Stream)

Converts the specified range into image with default image settings.

Declaration
void ConvertToImage(IRange range, Stream outputStream)
Parameters
Type Name Description
IRange range

The range which is to be converted to image.
System.IO.Stream outputStream

Stream to hold the converted image data.
Remarks

Subscript/Superscript,Shrink to fit,Shapes (except TextBox shape and Image),Charts and Chart Worksheet and Complex conditional formatting features are not supported in Worksheet to image conversion. Gradient fill is partially supported

Examples
// Converts the Range used in worksheet to image.
Stream image = new FileStream("Sample.png", FileMode.Create, FileAccess.ReadWrite);
sheet.ConvertToImage(sheet.UsedRange, imageOptions, image);

ConvertToImage(Int32, Int32, Int32, Int32, ExportImageOptions, Stream)

Converts the specified range into image with the image options given. Default image format is PNG.

Declaration
void ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColumn, ExportImageOptions imageOptions, Stream outputStream)
Parameters
Type Name Description
System.Int32 firstRow

First row index. One-based.
System.Int32 firstColumn

First column index. One-based.
System.Int32 lastRow

Last row index. One-based.
System.Int32 lastColumn

Last column index. One-based.
ExportImageOptions imageOptions

object that defines Image quality and image format to be used for conversion.
System.IO.Stream outputStream

Stream to hold the converted image data.
Remarks

Subscript/Superscript,Shrink to fit,Shapes (except TextBox shape and Image),Charts and Chart Worksheet and Complex conditional formatting features are not supported in Worksheet to image conversion. Gradient fill is partially supported

Examples
// Converts the Range(R1C1 to R10C20) in worksheet to image.
Stream image = new FileStream("Sample.png", FileMode.Create, FileAccess.ReadWrite);
ExportImageOptions imageOptions = new ExportImageOptions();
imageOptions.ImageFormat = ExportImageFormat.Jpeg;
Image img = sheet.ConvertToImage(1, 1, 10, 20, imageOptions, image);

ConvertToImage(Int32, Int32, Int32, Int32, Stream)

Converts the specified range into image. Default image format is PNG.

Declaration
void ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColum, Stream outputStream)
Parameters
Type Name Description
System.Int32 firstRow

First row index. One-based.
System.Int32 firstColumn

First column index. One-based.
System.Int32 lastRow

Last row index. One-based.
System.Int32 lastColum
System.IO.Stream outputStream

Stream to hold the converted image data.
Remarks

Subscript/Superscript,Shrink to fit,Shapes (except TextBox shape and Image),Charts and Chart Worksheet and Complex conditional formatting features are not supported in Worksheet to image conversion. Gradient fill is partially supported

Examples
// Converts the Range(R1C1 to R10C20) in worksheet to image.
Stream image = new FileStream("Sample.png", FileMode.Create, FileAccess.ReadWrite);
Image img = sheet.ConvertToImage(1, 1, 10, 20, image);

CreateNamedRanges(String, String, Boolean)

Creates a named ranges with the specified named range's value as a name for the specified range.

Declaration
void CreateNamedRanges(string namedRange, string referRange, bool vertical)
Parameters
Type Name Description
System.String namedRange

Existing named ranged.
System.String referRange

Address of the named range to be created.
System.Boolean vertical

True if the named range values are vertically placed in the sheet.
Remarks

This method is used to access the discontinuous ranges.

Examples
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IName name = sheet.Names.Add("B1name",sheet["B1:D1"]);
sheet["B1"].Value = "B2name";
sheet["C1"].Value = "C2name";
sheet["D1"].Value = "D2name";
sheet.CreateNamedRanges("B1name", "B2:D2", true);

CreateRangesCollection()

Creates a new instance of the IRanges.

Declaration
IRanges CreateRangesCollection()
Returns
Type Description
IRanges

New instance of ranges collection.
Examples

The following code illustrates how to creates a RangeCollection.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["B3"].Number = 53.75;
worksheet.Range["B4"].Number = 52.85;
worksheet.Range["B5"].Number = 59.77;
worksheet.Range["B6"].Number = 96.15;
worksheet.Range["F1"].Number = 26.72;
worksheet.Range["F2"].Number = 33.71;
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range["B3:B6"]);
rangesOne.Add(worksheet.Range["F1:F2"]);

CreateTemplateMarkersProcessor()

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

Declaration
ITemplateMarkersProcessor CreateTemplateMarkersProcessor()
Returns
Type Description
ITemplateMarkersProcessor

Object that can be used for template markers processing.

DeleteColumn(Int32)

Removes the specified column.

Declaration
void DeleteColumn(int index)
Parameters
Type Name Description
System.Int32 index

One-based column index.

DeleteColumn(Int32, Int32)

Removes the specified number of columns from the given index.

Declaration
void DeleteColumn(int index, int count)
Parameters
Type Name Description
System.Int32 index

One-based column index.
System.Int32 count

Number of columns to remove.

DeleteRow(Int32)

Removes the specified row.

Declaration
void DeleteRow(int index)
Parameters
Type Name Description
System.Int32 index

One-based row index.

DeleteRow(Int32, Int32)

Removes the specified number of rows from the given index.

Declaration
void DeleteRow(int index, int count)
Parameters
Type Name Description
System.Int32 index

One-based row index.
System.Int32 count

Number of rows.

DisableSheetCalculations()

Disables the calculation support in this workbook and disposes of the associative CalcEngine objects.

Declaration
void DisableSheetCalculations()
Examples

The following code illustrates how to disable the sheet calculation.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
sheet.DisableSheetCalculations();

EnableSheetCalculations()

Enables the calculation support.

Declaration
void EnableSheetCalculations()
Remarks

Enabling this method will initialize CalcEngine objects and retrieves calculated values of formulas in a worksheet.

Examples

The following code illustrates how to enable the sheet calculation.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();

ExportData<T>(Int32, Int32, Int32, Int32)

Exports worksheet data into the System.Collections.Generic.List<> of CLR Objects.

Declaration
List<T> ExportData<T>(int firstRow, int firstColumn, int lastRow, int lastColumn)
    where T : new()
Parameters
Type Name Description
System.Int32 firstRow

Row of the first cell should be exported.
System.Int32 firstColumn

Column of the first cell should be exported.
System.Int32 lastRow

Upto lastRow of data want to be export.
System.Int32 lastColumn

Upto lastColumn of data want to be export.
Returns
Type Description
System.Collections.Generic.List<T>

List of CLRObjects with worksheet data.
Type Parameters
Name Description
T

T is a generic type argument. The type argument for this particular class can be any type recognized by the compiler and it must have a parameterless constructor.
Remarks

Class property name or System.ComponentModel.DisplayNameAttribute for a property must match with any one of the cell value in firstRow parameter to bind and export data. To know more about ExportData refer this link.

Examples

The following code illustrates how to exports worksheet data into a System.Collections.Generic.List<> of CLR objects.

using Syncfusion.XlsIO;
using System.Collections.Generic;

class Example
{
  static void Main()
  {
    ExcelEngine engine = new ExcelEngine();
    IApplication application = engine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    // Export the matching data and Get the list of CLR objects
    List<Report> clrObjects= worksheet.ExportData<Report>(1, 1, 40, 3);

    workbook.Close();
    engine.Dispose();
  }
}
public class Report   
{
  public string SalesPerson { get; set; }
  public string SalesJanJun { get; set; }
  public string SalesJulDec { get; set; }

  public Report()
  {

  }
}

ExportData<T>(Int32, Int32, Int32, Int32, Dictionary<String, String>)

Exports worksheet data into the System.Collections.Generic.List<> of CLR Objects.

Declaration
List<T> ExportData<T>(int firstRow, int firstColumn, int lastRow, int lastColumn, Dictionary<string, string> mappingProperties)
    where T : new()
Parameters
Type Name Description
System.Int32 firstRow

Row of the first cell should be exported.
System.Int32 firstColumn

Column of the first cell should be exported.
System.Int32 lastRow

Upto lastRow of data want to be export.
System.Int32 lastColumn

Upto lastColumn of data want to be export.
System.Collections.Generic.Dictionary<System.String, System.String> mappingProperties

Property names mapping collection. You should give headers as key and properties names as value to the dictionary.
Returns
Type Description
System.Collections.Generic.List<T>

List of CLRObjects with worksheet data.
Type Parameters
Name Description
T

T is a generic type argument. The type argument for this particular class can be any type recognized by the compiler and it must have a parameterless constructor.
Remarks

Class property name or System.ComponentModel.DisplayNameAttribute for a property must match with any one of the cell value in firstRow parameter to bind and export data. To know more about ExportData refer this link.

Examples

The following code illustrates how to exports worksheet data into a System.Collections.Generic.List<> of CLR objects.

using Syncfusion.XlsIO;
using System.Collections.Generic;

class Example
{
  static void Main()
  {
    ExcelEngine engine = new ExcelEngine();
    IApplication application = engine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    // Export the matching data and Get the list of CLR objects
    Dictionary<string, string> mappingProperties = new Dictionary<string, string>();
    mapping.Add("Sales Person", "SalesPerson");
    mapping.Add("Sales JanuaryJune", "SalesJanJun");
    mapping.Add("Sales JulyDecember", "SalesJulDec");

    List<Report> clrObjects= worksheet.ExportData<Report>(1, 1, 40, 3, mappingProperties);

    workbook.Close();
    engine.Dispose();
  }
}
public class Report   
{
  public string SalesPerson { get; set; }
  public string SalesJanJun { get; set; }
  public string SalesJulDec { get; set; }

  public Report()
  {

  }
}

ExportDataTable(IRange, ExcelExportDataTableOptions)

Exports worksheet data in the specified range into a System.Data.DataTable.

Declaration
DataTable ExportDataTable(IRange dataRange, ExcelExportDataTableOptions options)
Parameters
Type Name Description
IRange dataRange

Used range of worksheet to be export using IRange Interface.
ExcelExportDataTableOptions options

Export options using ExcelExportDataTableOptions enumeration.
Returns
Type Description
System.Data.DataTable

DataTable with worksheet data.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ExportDataTable refer this link.

Examples

The following code illustrates how to exports worksheet data into a System.Data.DataTable with the specified UsedRange.

    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Import Data From Excel to DataTable
    System.Data.DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames| ExcelExportDataTableOptions.PreserveOleDate);

    //Using dataGrid/gridControls you can view the exported DataTable's data
    System.Windows.Forms.DataGrid dataGrid1 = new System.Windows.Forms.DataGrid();
    this.dataGrid1.DataSource = customersTable;

    workbook.Close();
    excelEngine.Dispose();

ExportDataTable(IRange, ExcelExportDataTableOptions, ExcelExportDataOptions)

Exports worksheet data into a DataTable with the specified data range based on the export options.

Declaration
DataTable ExportDataTable(IRange dataRange, ExcelExportDataTableOptions options, ExcelExportDataOptions exportDataOptions)
Parameters
Type Name Description
IRange dataRange

Used range of worksheet to be export using IRange Interface.
ExcelExportDataTableOptions options

Export options using ExcelExportDataTableOptions enumeration.
ExcelExportDataOptions exportDataOptions

Export data options using ExcelExportDataOptions enumeration.
Returns
Type Description
System.Data.DataTable

DataTable with worksheet data.
Remarks

When exportDataOption is null or the value of the ColumnTypeDeductionRow property in a row is less than or equal to 0, then By default the second row from the export range is used for detecting the column types. And the default value will be exported in the data table if the cell value does not match with the column type.

Examples

The following code illustrates how to exports worksheet data into a System.Data.DataTable with the specified UsedRange.

    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Import Data From Excel to DataTable
    ExcelExportDataOptions exportDataOptions = new ExcelExportDataOptions();
    exportDataOptions.ColumnTypeDetectionRow = 2;
    System.Data.DataTable customersTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames| ExcelExportDataTableOptions.PreserveOleDate, exportDataOptions);

    //Using dataGrid/gridControls you can view the exported DataTable's data
    System.Windows.Forms.DataGrid dataGrid1 = new System.Windows.Forms.DataGrid();
    this.dataGrid1.DataSource = customersTable;

    workbook.Close();
    excelEngine.Dispose();

ExportDataTable(Int32, Int32, Int32, Int32, ExcelExportDataTableOptions)

Exports worksheet data in the specified row and column into a System.Data.DataTable.

Declaration
DataTable ExportDataTable(int firstRow, int firstColumn, int maxRows, int maxColumns, ExcelExportDataTableOptions options)
Parameters
Type Name Description
System.Int32 firstRow

Row of the first cell from where DataTable should be exported.
System.Int32 firstColumn

Column of the first cell from where DataTable should be exported.
System.Int32 maxRows

Maximum number of rows to export / upto number of rows.
System.Int32 maxColumns

Maximum number of columns to export / upto number of columns.
ExcelExportDataTableOptions options

Export options using ExcelExportDataTableOptions enumeration.
Returns
Type Description
System.Data.DataTable

DataTable with worksheet data.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ExportDataTable refer this link.

Examples

The following code illustrates how to exports worksheet data into a System.Data.DataTable with the specified row and column.

     ExcelEngine excelEngine = new ExcelEngine();
     IApplication application = excelEngine.Excel;
     IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
     IWorksheet worksheet = workbook.Worksheets[0];

     //Import Data From Excel to DataTable
     System.Data.DataTable dataTable = worksheet.ExportDataTable(1,1,40,4, ExcelExportDataTableOptions.ComputedFormulaValues);

     // Using dataGrid/gridControls you can view the exported DataTable's data
     System.Windows.Forms.DataGrid dataGrid1 = new System.Windows.Forms.DataGrid();
     dataGrid1.DataSource = dataTable;

     workbook.Close();
     excelEngine.Dispose();

ExportDataTable(Int32, Int32, Int32, Int32, ExcelExportDataTableOptions, ExcelExportDataOptions)

Exports worksheet data in the specified row and column into a DataTable based on the export options.

Declaration
DataTable ExportDataTable(int firstRow, int firstColumn, int maxRows, int maxColumns, ExcelExportDataTableOptions options, ExcelExportDataOptions exportDataOptions)
Parameters
Type Name Description
System.Int32 firstRow

Row of the first cell from where DataTable should be exported.
System.Int32 firstColumn

Column of the first cell from where DataTable should be exported.
System.Int32 maxRows

Maximum number of rows to export / upto number of rows.
System.Int32 maxColumns

Maximum number of columns to export / upto number of columns.
ExcelExportDataTableOptions options

Export options using ExcelExportDataTableOptions enumeration.
ExcelExportDataOptions exportDataOptions

Export data options using ExcelExportDataOptions enumeration.
Returns
Type Description
System.Data.DataTable

DataTable with worksheet data.
Remarks

When exportDataOption is null or the value of the ColumnTypeDeductionRow property in a row is less than or equal to 0, then By default the second row from the export range is used for detecting the column types. And the default value will be exported in the data table if the cell value does not match with the column type.

Examples

The following code illustrates how to exports worksheet data into a System.Data.DataTable with the specified row and column.

     ExcelEngine excelEngine = new ExcelEngine();
     IApplication application = excelEngine.Excel;
     IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
     IWorksheet worksheet = workbook.Worksheets[0];

     //Import Data From Excel to DataTable
     ExcelExportDataOptions exportDataOptions = new ExcelExportDataOptions();
     exportDataOptions.ColumnTypeDetectionRow = 2;
     System.Data.DataTable dataTable = worksheet.ExportDataTable(1,1,40,4, ExcelExportDataTableOptions.ComputedFormulaValues,exportDataOptions);

     // Using dataGrid/gridControls you can view the exported DataTable's data
     System.Windows.Forms.DataGrid dataGrid1 = new System.Windows.Forms.DataGrid();
     dataGrid1.DataSource = dataTable;

     workbook.Close();
     excelEngine.Dispose();

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 the cells with a specified bool value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified bool value.

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

//Find all with bool.
IRange[] results = sheet.FindAll(true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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 the cells with a specified DateTime value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified DateTime value.

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

//Find all with DateTime
IRange[] results = sheet.FindAll(DateTime.Now);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

All found cells, or Null if value was not found.
Examples

The following code illustrates how to find the cells with specified double value.

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

//Find All with number
IRange[] results = sheet.FindAll(100.32, ExcelFindType.Number);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(String, ExcelFindType)

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

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 the cells with a specified string value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified string value.

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

//Find all with string
IRange[] results = sheet.FindAll("Hello World", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FindAll(String, ExcelFindType, ExcelFindOptions)

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

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 the cells with a specified string value and specified find options , or null if value was not found.
Examples

The following code illustrates how to find the cells with specified string value with specified Excel find option.

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

//Find all with string and specified find option.
IRange[] results = sheet.FindAll("Hello World", ExcelFindType.Text, ExcelFindOptions.MatchCase);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the cells with a specified TimeSpan value, or null if value was not found.
Examples

The following code illustrates how to find the cells with specified TimeSpan value.

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

//Find All with Timespan
IRange[] results = sheet.FindAll(TimeSpan.MinValue);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified bool value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified bool value.

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

//Find First with bool
IRange result = sheet.FindFirst(true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified DateTime value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified DateTime value.

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

//Find First with DateTime
IRange result = sheet.FindFirst(DateTime.Now);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified double value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified double value.

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

//Find First with number
IRange result = sheet.FindFirst(100.32, ExcelFindType.Number);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified string value.

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

//Find First with string
IRange results = sheet.FindFirst("Hello World", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified string value and specified find options , or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified string value with specified Excel find option.

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

//Find First with string and specified find option.
IRange results = sheet.FindFirst("Hello World", ExcelFindType.Text,ExcelFindOptions.MatchCase);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first cell with a specified TimeSpan value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence of the specified TimeSpan value.

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

//Find First with Timespan
IRange result = sheet.FindFirst(TimeSpan.MinValue);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first occurrence that ends with the specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence that ends with the specified string value which ignores the case.

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

//Find String Ends with specified string
IRange result = sheet.FindStringEndsWith("world", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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 when comparing this string to the value; otherwise, False.
Returns
Type Description
IRange

Returns the first occurrence that ends with the specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence that ends with the specified string value which ignores the case.

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

//Find String Ends with specified string
IRange result = sheet.FindStringEndsWith("world", ExcelFindType.Text, true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first occurrence that starts with the specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence that starts with the specified string value.

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

//Find String Starts with specified string
IRange result = sheet.FindStringStartsWith("Hello", ExcelFindType.Text);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

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

Returns the first occurrence that starts with the specified string value, or null if value was not found.
Examples

The following code illustrates how to find the first occurrence that starts with the specified string value.

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

//Find String Starts with specified string
IRange result = sheet.FindStringStartsWith("Hello", ExcelFindType.Text, true);

string fileName = "Find.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();

FreeRange(IRange)

Frees range object.

Declaration
void FreeRange(IRange range)
Parameters
Type Name Description
IRange range

Range to remove from internal cache.

FreeRange(Int32, Int32)

Frees range object for the specified row and column.

Declaration
void FreeRange(int iRow, int iColumn)
Parameters
Type Name Description
System.Int32 iRow

One-based row index of the range object to remove from internal cache.
System.Int32 iColumn

One-based column index of the range object to remove from internal cache.

GetBoolean(Int32, Int32)

Returns bool value from the specified row and column.

Declaration
bool GetBoolean(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.Boolean

Returns found bool value. If cannot found returns false.

GetColumnWidth(Int32)

Returns the width of the specified column.

Declaration
double GetColumnWidth(int iColumnIndex)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
Returns
Type Description
System.Double

Width of the specified column.
Examples

The following code illustrates how to get the column width for a particular column.

        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["A1"].Text = "Sample text in cell";

            //Set auto fit
            worksheet.AutofitColumn(1);

            //Get column width
            Console.WriteLine(worksheet.GetColumnWidth(1));

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

GetColumnWidthInPixels(Int32)

Returns the width of the specified column in pixels.

Declaration
int GetColumnWidthInPixels(int iColumnIndex)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
Returns
Type Description
System.Int32

Width in pixels of the specified column.
Examples

The following code illustrates how to get the column width for a particular column.

        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["A1"].Text = "Sample text in cell";

            //Set auto fit
            worksheet.AutofitColumn(1);

            //Get column width
            Console.WriteLine(worksheet.GetColumnWidthInPixels(1));

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

GetDefaultColumnStyle(Int32)

Returns the default column style for the specified column.

Declaration
IStyle GetDefaultColumnStyle(int iColumnIndex)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
Returns
Type Description
IStyle

Default column style for the specified column or null if style wasn't set.
Examples

The following code illustrates how to get default column style.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultRowStyle(2, style);

            //Get default style
            IStyle defaultStyle = worksheet.GetDefaultColumnStyle(3);

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

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

GetDefaultRowStyle(Int32)

Returns default row style for the specified row.

Declaration
IStyle GetDefaultRowStyle(int iRowIndex)
Parameters
Type Name Description
System.Int32 iRowIndex

One-based row index.
Returns
Type Description
IStyle

Default row style for the specified row or null if style wasn't set.
Examples

The following code illustrates how to get default row style.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultColumnStyle(2, style);

            //Get default style
            IStyle defaultStyle = worksheet.GetDefaultRowStyle(3);

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

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

GetError(Int32, Int32)

Returns error value from the specified row and column.

Declaration
string GetError(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.String

Returns error value or null.

GetFormula(Int32, Int32, Boolean)

Returns formula value from specified row and column.

Declaration
string GetFormula(int row, int column, bool bR1C1)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
System.Boolean bR1C1

Indicates whether R1C1 notation should be used.
Returns
Type Description
System.String

Returns formula string.

GetFormulaBoolValue(Int32, Int32)

Returns formula bool value from the specified row and column.

Declaration
bool GetFormulaBoolValue(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.Boolean

True if bool value is found. otherwise False.

GetFormulaErrorValue(Int32, Int32)

Returns formula error value from the specified row and column.

Declaration
string GetFormulaErrorValue(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.String

Returns error value or null.

GetFormulaNumberValue(Int32, Int32)

Returns formula number value from the specified row and column.

Declaration
double GetFormulaNumberValue(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.Double

Number contained by the cell.

GetFormulaStringValue(Int32, Int32)

Returns formula string value from the specified row and column.

Declaration
string GetFormulaStringValue(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.String

String contained by the cell.

GetNumber(Int32, Int32)

Returns number value from the specified row and column.

Declaration
double GetNumber(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.Double

Number contained by the cell.

GetRowHeight(Int32)

Returns the height of the specified row.

Declaration
double GetRowHeight(int iRow)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
Returns
Type Description
System.Double

Returns height of the specified row. Otherwise returns StandardHeight.
Examples

The following code illustrates how to get the row height for a particular row.

        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 data
            worksheet["C2"].Value = "Sample text";

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

            //Set font
            IFont font = style.Font;

            //Set font size
            font.Size = 18;

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

            //Set auto fit
            worksheet.AutofitRow(2);

            //Get row width
            Console.WriteLine(worksheet.GetRowHeight(2));

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

GetRowHeightInPixels(Int32)

Returns the height of the specified row in pixels.

Declaration
int GetRowHeightInPixels(int iRowIndex)
Parameters
Type Name Description
System.Int32 iRowIndex

One-based row index.
Returns
Type Description
System.Int32

Returns height of the specified row in pixels. Otherwise returns StandardHeight.
Examples

The following code illustrates how to get the row height for a particular row.

        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 data
            worksheet["C2"].Value = "Sample text";

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

            //Set font
            IFont font = style.Font;

            //Set font size
            font.Size = 18;

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

            //Set auto fit
            worksheet.AutofitRow(2);

            //Get row width
            Console.WriteLine(worksheet.GetRowHeightInPixels(2));

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

GetText(Int32, Int32)

Returns string value from the specified row and column.

Declaration
string GetText(int row, int column)
Parameters
Type Name Description
System.Int32 row

One-based row index.
System.Int32 column

One-based column index.
Returns
Type Description
System.String

String contained by the cell.

HideColumn(Int32)

Hides the specified column.

Declaration
void HideColumn(int columnIndex)
Parameters
Type Name Description
System.Int32 columnIndex

One-based column index.

HideRow(Int32)

Hides the specified row.

Declaration
void HideRow(int rowIndex)
Parameters
Type Name Description
System.Int32 rowIndex

One-based row index.

ImportArray(DateTime[], Int32, Int32, Boolean)

Imports an array of System.DateTime values into a worksheet.

Declaration
int ImportArray(DateTime[] arrDateTime, int firstRow, int firstColumn, bool isVertical)
Parameters
Type Name Description
System.DateTime[] arrDateTime

Array of datetime value.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
System.Boolean isVertical

True if array should be imported vertically; False - horizontally.
Returns
Type Description
System.Int32

Number of imported elements.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports an array of System.DateTime values into a worksheet with the specified row and column.

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

    //Initialize the DateTime Array
    DateTime[] arrayDate = new DateTime[4] { DateTime.Parse("06:45"), DateTime.Parse("08:30"), DateTime.Parse("09:40"), DateTime.Parse("10:30") };

    //Import the DateTime Array to Sheet
    worksheet.ImportArray(arrayDate, 1, 1, true);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportArray(Double[], Int32, Int32, Boolean)

Imports an array of System.Double values into a worksheet.

Declaration
int ImportArray(double[] arrDouble, int firstRow, int firstColumn, bool isVertical)
Parameters
Type Name Description
System.Double[] arrDouble

Array of double value.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
System.Boolean isVertical

True if array should be imported vertically; False - horizontally.
Returns
Type Description
System.Int32

Number of imported elements.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports an array of System.Double values into a worksheet with the specified row and column.

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

    //Initialize the double Array
    double[] arrayDouble = new double[4] { 344.0045, 345.0045, 346.0045, 347.0045 };

    //Import the double Array to Sheet
    worksheet.ImportArray(arrayDouble, 1, 1, true);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportArray(Int32[], Int32, Int32, Boolean)

Imports an array of integer values into a worksheet.

Declaration
int ImportArray(int[] arrInt, int firstRow, int firstColumn, bool isVertical)
Parameters
Type Name Description
System.Int32[] arrInt

Array of int value.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
System.Boolean isVertical

True if array should be imported vertically; False - horizontally.
Returns
Type Description
System.Int32

Number of imported elements.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports an array of integer values into a worksheet with the specified row and column.

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

    //Initialize the integer Array
    int[] arrayInt = new int[4] {1000, 2000, 3000, 4000};

    //Import the integer Array to Sheet
    worksheet.ImportArray(arrayInt, 1, 1, true);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportArray(Object[], Int32, Int32, Boolean)

Imports an array of System.Object into a worksheet with specified alignment.

Declaration
int ImportArray(object[] arrObject, int firstRow, int firstColumn, bool isVertical)
Parameters
Type Name Description
System.Object[] arrObject

Array of object.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
System.Boolean isVertical

True if array should be imported vertically; False - horizontally.
Returns
Type Description
System.Int32

Number of imported elements.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports an array of System.Object into a worksheet with specified alignment.

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

    //Initialize the Object Array
    object[] array = new object[4] { "Total Income", "Actual Expense", "Expected Expenses", "Profit" };

    //Import the Object Array to Sheet
    worksheet.ImportArray(array, 1, 1, true);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportArray(Object[,], Int32, Int32)

Imports data from a two-dimensional array of System.Object into a worksheet.

Declaration
int ImportArray(object[, ] arrObject, int firstRow, int firstColumn)
Parameters
Type Name Description
System.Object[,] arrObject

Two-dimensional array of object.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports a two-dimensional array of System.Object into a worksheet with the specified row and column.

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

    //Initialize the Object Array
    object[,] arrayTwoDimen = new object[3, 2] { { "AND", "OR" }, { "NAND", "XOR" },{ "NOR", "NOT" } };

    //Import the Object Array to Sheet
    worksheet.ImportArray(arrayTwoDimen, 1, 1);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportArray(String[], Int32, Int32, Boolean)

Imports an array of System.String values into a worksheet.

Declaration
int ImportArray(string[] arrString, int firstRow, int firstColumn, bool isVertical)
Parameters
Type Name Description
System.String[] arrString

Array of string value.
System.Int32 firstRow

Row of the first cell where array should be imported.
System.Int32 firstColumn

Column of the first cell where array should be imported.
System.Boolean isVertical

True if array should be imported vertically; False - horizontally.
Returns
Type Description
System.Int32

Number of imported elements.
Remarks

To know more about ImportArray refer this link

Examples

The following code illustrates how to Imports an array of System.String values into a worksheet with the specified row and column.

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

    //Initialize the string Array
    string[] arrayString = new string[4] { "Total Income", "Actual Expense", "Expected Expenses", "Profit" };

    //Import the string Array to Sheet
    worksheet.ImportArray(arrayString, 1, 1, true);

    workbook.SaveAs("ImportArray.xlsx");
    workbook.Close();
    excelEngine.Dispose();

ImportData(IEnumerable, ExcelImportDataOptions)

Imports data from class objects into a worksheet with specified row and column along with import data options.

Declaration
int ImportData(IEnumerable arrObject, ExcelImportDataOptions importOptions)
Parameters
Type Name Description
System.Collections.IEnumerable arrObject

IEnumerable object with desired data.
ExcelImportDataOptions importOptions

Import data options for when importing nested collection data.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

To know more about ImportData refer this link

Examples

The following code illustrates how to Imports data from class objects into a worksheet with the specified row and column.

using Syncfusion.XlsIO;
using System.Collections.Generic;

class Example
{
  static void Main()
  {
    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //The list of data is get from GetFamilyDataList method. Here GetFamilyDataList and Family are sample method and sample class.
    IList<Family> families = GetFamilyDataList();

    //Import the data to worksheet
    ExcelImportDataOptions importDataOptions = new ExcelImportDataOptions();
    importDataOptions.FirstRow = 1;
    importDataOptions.FirstColumn = 1;
    importDataOptions.IncludeHeader = true;
    importDataOptions.IncludeHeaderParent = true;
    importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Merge;
    importDataOptions.NestedDataGroupOptions = ExcelNestedDataGroupOptions.Collapse;
    importDataOptions.CollapseLevel = 2;

    worksheet.ImportData(families, importDataOptions);

    workbook.SaveAs("ImportNestedData.xlsx");
    workbook.Close();
    excelEngine.Dispose();
  }

  private static IList<Family> GetFamilyDataList()
  {
      List<Family> list = new List<Family>();

      // Create an object for the Husband class
      Family f1 = new Family("Thomas Hardy", 50, "Catherine Dewey", 48);

      f1.Children = new List<Child>();
      f1.Children.Add(new Child("Thomas Anderson", 24));
      f1.Children.Add(new Child("Rachel Dawes", 18));
      f1.Children.Add(new Child("Dom Cobb", 25));

      // Create another object for the Husband class
      Family f2 = new Family("Philip Cramer", 54, "Maria Larsson", 50);

      f2.Children = new List<Child>();
      f2.Children.Add(new Child("Bernadette Maryann", 26));
      f2.Children.Add(new Child("Zack Johnson", 23));
      f2.Children.Add(new Child("Sheldon Cooper", 25));

      list.Add(f1);
      list.Add(f2);
      return list;
  }
}

public class Family
{
  private String m_HusbandName;
  public String HusbandName
  {
      get { return m_HusbandName; }
      set { m_HusbandName = value; }
  }
  private int m_HusbandAge;
  public int HusbandAge
  {
      get { return m_HusbandAge; }
      set { m_HusbandAge = value; }
  }
  private List<Child> m_Children;
  public List<Child> Children
  {
      get { return m_Children; }
      set { m_Children = value; }
  }

  private string m_WifeName;
  public string WifeName
  {
      get { return m_WifeName; }
      set { m_WifeName = value; }
  }

  private int m_WifeAge;
  public int WifeAge
  {
      get { return m_WifeAge; }
      set { m_WifeAge = value; }
  }
  public Family(string husbandName, int husbandAge, string wifeName, int wifeAge)
  {
      m_HusbandName = husbandName;
      m_HusbandAge = husbandAge;
      m_WifeName = wifeName;
      m_WifeAge = wifeAge;
  }
}
public class Child
{    
  private string m_name;
  public string Name
  {
      get { return m_name; }
      set { m_name = value; }
  }
  private int m_age;
  public int Age
  {
      get { return m_age; }
      set { m_age = value; }
  }
  public Child(string name, int age)
  {
      this.m_name = name;
      this.m_age = age;
  }
}

ImportData(IEnumerable, Int32, Int32, Boolean)

Imports data from class objects into a worksheet with specified row and column.

Declaration
int ImportData(IEnumerable arrObject, int firstRow, int firstColumn, bool includeHeader)
Parameters
Type Name Description
System.Collections.IEnumerable arrObject

IEnumerable objects with desired data.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean includeHeader

TRUE if class properties names must be imported. FALSE otherwise.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

To know more about ImportData refer this link

Examples

The following code illustrates how to Imports data from class objects into a worksheet with the specified row and column.

using Syncfusion.XlsIO;
using System.Collections.Generic;

class Example
{
  static void Main()
  {
    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //The list of data is get from GetSalesReports method. Here GetSalesReports and Report are sample method and sample class.
    IList<Report> reports = GetSalesReports();

    //Import the data to worksheet
    worksheet.ImportData(reports, 2, 1, false);

    workbook.SaveAs("ImportFromDT.xlsx");
    workbook.Close();
    excelEngine.Dispose();
  }

  public static List<Report> GetSalesReports()
  {
   List<Report> reports = new List<Report>();
   reports.Add(new Report("Andy Bernard", "45000", "58000"));
   reports.Add(new Report("Jim Halpert", "34000", "65000"));
   reports.Add(new Report("Karen Fillippelli", "75000", "64000"));
   reports.Add(new Report("Phyllis Lapin", "56500", "33600" ));
   reports.Add(new Report("Stanley Hudson", "46500", "52000"));
   return reports;
  }
}
public class Report   
{
  public string SalesPerson { get; set; }
  public string SalesJanJun { get; set; }
  public string SalesJulDec { get; set; }

  public Report(string name, string janToJun, string julToDec)
  {
    SalesPerson = name;
    SalesJanJun = janToJun;
    SalesJulDec = julToDec;
  }
}

ImportDataColumn(DataColumn, Boolean, Int32, Int32)

Imports data from a System.Data.DataColumn into a worksheet.

Declaration
int ImportDataColumn(DataColumn dataColumn, bool isFieldNameShown, int firstRow, int firstColumn)
Parameters
Type Name Description
System.Data.DataColumn dataColumn

DataColumn with desired data.
System.Boolean isFieldNameShown

TRUE if column name must be imported. FALSE - Not imported.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataColumn refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataColumn into a worksheet with the specified row and column.

     using(ExcelEngine excelEngine = new ExcelEngine())
     {
     IApplication application = excelEngine.Excel;
     application.DefaultVersion = ExcelVersion.Excel2013;
     IWorkbook workbook = application.Workbooks.Create(1);
     IWorksheet worksheet = workbook.Worksheets[0];
     System.Data.DataTable table = SampleDataTable();
     System.Data.DataColumn column = table.Columns[2];
     worksheet.ImportDataColumn(column, true, 1, 1);
     workbook.SaveAs("ImportFromDC.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }
  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataColumn(DataColumn, Boolean, Int32, Int32, Boolean)

Imports data from a System.Data.DataColumn into a worksheet.

Declaration
int ImportDataColumn(DataColumn dataColumn, bool isFieldNameShown, int firstRow, int firstColumn, bool preserveTypes)
Parameters
Type Name Description
System.Data.DataColumn dataColumn

DataColumn with desired data.
System.Boolean isFieldNameShown

TRUE if column name must be imported. FALSE - Not imported.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean preserveTypes

Indicates whether XlsIO should preserve column types from DataTable. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataColumn refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataColumn into a worksheet with the specified row and column.

     using(ExcelEngine excelEngine = new ExcelEngine())
     {
     IApplication application = excelEngine.Excel;
     application.DefaultVersion = ExcelVersion.Excel2013;
     IWorkbook workbook = application.Workbooks.Create(1);
     IWorksheet worksheet = workbook.Worksheets[0];
     System.Data.DataTable table = SampleDataTable();
     System.Data.DataColumn column = table.Columns[2];
     worksheet.ImportDataColumn(column, true, 1, 1);
     workbook.SaveAs("ImportFromDC.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }
  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataReader(IDataReader, IName, Boolean)

Imports data from System.Data.IDataReader into the specified named range of current worksheet.

Declaration
int ImportDataReader(IDataReader dataReader, IName namedRange, bool isFieldNameShown)
Parameters
Type Name Description
System.Data.IDataReader dataReader

The or object which contains data.
IName namedRange

Represents named range using IName enumeration. Bounds of data table should not greatfull than bounds of named range. i.e., Example NamedRange["A1:F6"] have a 6 Rows and 6 Columns. Also we should have DataTable rows and columns also same or lessthan named range.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataReader refer this link

Examples

The following code illustrates how to Imports data from System.Data.IDataReader into worksheet.

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

     //Initialize named range
     IName namedRange = worksheet.Names.Add("SampleInfo");
     namedRange.RefersToRange = worksheet.Range["A1:F6"];

     //Initialize DataTable and get data from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Create IDataReader using Datatable 
     System.Data.IDataReader reader = table.CreateDataReader();

     //Import data from DataReader
     worksheet.ImportDataReader(reader, namedRange, true);

     workbook.SaveAs("ImportDataReader.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataReader(IDataReader, Boolean, Int32, Int32)

Imports data from System.Data.IDataReader into worksheet from the specified row and column.

Declaration
int ImportDataReader(IDataReader dataReader, bool isFieldNameShown, int firstRow, int firstColumn)
Parameters
Type Name Description
System.Data.IDataReader dataReader

The or object which contains data.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataReader refer this link

Examples

The following code illustrates how to Imports data from System.Data.IDataReader into worksheet from the specified row and column.

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

     //Initialize DataTable and get data from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Create IDataReader using Datatable 
     System.Data.IDataReader reader = table.CreateDataReader();

     //Import data from DataReader
     worksheet.ImportDataReader(reader, true, 1, 1);

     workbook.SaveAs("ImportDataReader.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataReader(IDataReader, Boolean, Int32, Int32, Boolean)

Imports data from System.Data.IDataReader into worksheet from the specified row and column along with the preserve type.

Declaration
int ImportDataReader(IDataReader dataReader, bool isFieldNameShown, int firstRow, int firstColumn, bool preserveTypes)
Parameters
Type Name Description
System.Data.IDataReader dataReader

The or object which contains data.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean preserveTypes

Indicates whether XlsIO should preserve column types from DataReader. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataReader refer this link

Examples

The following code illustrates how to Imports data from System.Data.IDataReader into worksheet from the specified row and column.

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

     //Initialize DataTable and get data from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Create IDataReader using Datatable 
     System.Data.IDataReader reader = table.CreateDataReader();

     //Import data from DataReader
     worksheet.ImportDataReader(reader,true, 1, 1,true);

     workbook.SaveAs("ImportDataReader.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataReader(IDataReader, Int32, Int32, Boolean)

Imports data from System.Data.IDataReader into worksheet from the specified row and column along with save option.

Declaration
int ImportDataReader(IDataReader dataReader, int firstRow, int firstColumn, bool importOnSave)
Parameters
Type Name Description
System.Data.IDataReader dataReader

The or object which contains data.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean importOnSave

TRUE if data must be serialized directly on save.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataReader refer this link

Examples

The following code illustrates how to Imports data from System.Data.IDataReader into worksheet from the specified row and column.

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

     //Initialize DataTable and get data from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Create IDataReader using Datatable 
     System.Data.IDataReader reader = table.CreateDataReader();

     //Import data from DataReader
     worksheet.ImportDataReader(reader, 1, 1,true);

     workbook.SaveAs("ImportDataReader.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataTable(DataTable, IName, Boolean)

Imports data from System.Data.DataTable into the specified named range of current worksheet.

Declaration
int ImportDataTable(DataTable dataTable, IName namedRange, bool isFieldNameShown)
Parameters
Type Name Description
System.Data.DataTable dataTable

Data Table with desired data.
IName namedRange

Represents named range IName. Bounds of data table should not greatfull than bounds of named range. i.e., Example NamedRange["A1:F6"] have a 6 Rows and 6 Columns. Also we should have DataTable rows and columns also same or less than named range.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from System.Data.DataTable into a worksheet with specified named range.

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

     //Initialize named range
     IName namedRange = worksheet.Names.Add("SampleInfo");
     namedRange.RefersToRange = worksheet.Range["A1:F6"];

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, namedRange, true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataTable(DataTable, IName, Boolean, Int32, Int32)

Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset.

Declaration
int ImportDataTable(DataTable dataTable, IName namedRange, bool isFieldNameShown, int rowOffset, int columnOffset)
Parameters
Type Name Description
System.Data.DataTable dataTable

Data Table with desired data.
IName namedRange

Represents named range IName. Bounds of data table should not greatfull than bounds of named range. i.e., Example NamedRange["A1:F6"] have a 6 Rows and 6 Columns. Also we should have DataTable rows and columns also same or lessthan named range.
System.Boolean isFieldNameShown

TRUE if column names must also be imported. FALSE otherwise.
System.Int32 rowOffset

Represents row offset into named range to import.
System.Int32 columnOffset

Represents column offset into named range to import.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset.

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

     //Initialize named range
     IName namedRange = worksheet.Names.Add("SampleInfo");
     namedRange.RefersToRange = worksheet.Range["A1:F6"];

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, namedRange, true, 1, 3);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataTable(DataTable, IName, Boolean, Int32, Int32, Int32, Int32)

Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset.

Declaration
int ImportDataTable(DataTable dataTable, IName namedRange, bool isFieldNameShown, int rowOffset, int columnOffset, int iMaxRow, int iMaxCol)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
IName namedRange

Represents named range using IName enumeration. Bounds of data table should not greatfull than bounds of named range. i.e., Example NamedRange["A1:F6"] have a 6 Rows and 6 Columns. Also we should have DataTable rows and columns also same or lessthan named range.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 rowOffset

Represents row offset into named range to import.
System.Int32 columnOffset

Represents column offset into named range to import.
System.Int32 iMaxRow

Maximum number of rows to import.
System.Int32 iMaxCol

Maximum number of columns to import.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset also with maximum rows and columns.

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

     //Initialize named range
     IName namedRange = worksheet.Names.Add("SampleInfo");
     namedRange.RefersToRange = worksheet.Range["A1:F16"];

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, namedRange, true, 1, 1, 5, 2);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataTable(DataTable, IName, Boolean, Int32, Int32, Int32, Int32, Boolean)

Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset along with preserve type option.

Declaration
int ImportDataTable(DataTable dataTable, IName namedRange, bool isFieldNameShown, int rowOffset, int columnOffset, int iMaxRow, int iMaxCol, bool bPreserveTypes)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
IName namedRange

Represents named range using IName enumeration. Bounds of data table should not greatfull than bounds of named range. i.e., Example NamedRange["A1:F6"] have a 6 Rows and 6 Columns. Also we should have DataTable rows and columns also same or lessthan named range.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 rowOffset

Represents row offset into named range to import.
System.Int32 columnOffset

Represents column offset into named range to import.
System.Int32 iMaxRow

Maximum number of rows to import.
System.Int32 iMaxCol

Maximum number of columns to import.
System.Boolean bPreserveTypes

Indicates whether XlsIO should preserve column types from DataTable. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from System.Data.DataTable into the specified named range of current worksheet with row and column offset also with maximum rows and columns.

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

     //Initialize named range
     IName namedRange = worksheet.Names.Add("SampleInfo");
     namedRange.RefersToRange = worksheet.Range["A1:F16"];

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, namedRange, true, 1, 1, 5, 2, true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataTable(DataTable, Boolean, Int32, Int32)

Imports data from a System.Data.DataTable into a worksheet with specified row and column.

Declaration
int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward.. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with specified row and column.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, true, 1, 1);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }
  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataTable(DataTable, Boolean, Int32, Int32, Boolean)

Imports data from a System.Data.DataTable into a worksheet with specified row and column along with the preserve type.

Declaration
int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, bool preserveTypes)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
System.Boolean isFieldNameShown

True if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean preserveTypes

Indicates whether XlsIO should preserve column types from DataTable. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with specified row and column along with the preserve type.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, true, 1, 1, true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataTable(DataTable, Boolean, Int32, Int32, Int32, Int32)

Imports data from a System.Data.DataTable into a worksheet with the specified range.

Declaration
int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
System.Boolean isFieldNameShown

True if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Int32 maxRows

Maximum number of rows to import.
System.Int32 maxColumns

Maximum number of columns to import.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with the specified range.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, true, 1, 1, 5, 2);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows.
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataTable(DataTable, Boolean, Int32, Int32, Int32, Int32, Boolean)

Imports data from a System.Data.DataTable into a worksheet with specified range along with preserve type.

Declaration
int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns, bool preserveTypes)
Parameters
Type Name Description
System.Data.DataTable dataTable

DataTable with desired data.
System.Boolean isFieldNameShown

True if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Int32 maxRows

Maximum number of rows to import.
System.Int32 maxColumns

Maximum number of columns to import.
System.Boolean preserveTypes

Indicates whether XlsIO should preserve column types from DataTable. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataTable refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with specified range along with preserve type.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, true, 1, 1, 5, 2 , true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataTable(DataTable, Int32, Int32, Boolean)

Imports data from a System.Data.DataTable into a worksheet with the specified row and column along with save option.

Declaration
int ImportDataTable(DataTable dataTable, int firstRow, int firstColumn, bool importOnSave)
Parameters
Type Name Description
System.Data.DataTable dataTable

Data Table with desired data.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean importOnSave

TRUE if data table must be serialized directly on save.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

We request you to use this method to improve performance and reduce memory consumption while dealing with large data. To know more about ImportDataTable refer this link. This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward.

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with the specified row and column along with save option.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, 1, 1, true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataTable(DataTable, Int32, Int32, Boolean, Boolean)

Imports data from a System.Data.DataTable into a worksheet with the specified row and column along with save option.

Declaration
int ImportDataTable(DataTable dataTable, int firstRow, int firstColumn, bool importOnSave, bool includeHeader)
Parameters
Type Name Description
System.Data.DataTable dataTable

Data Table with desired data.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean importOnSave

TRUE if data table must be serialized directly on save.
System.Boolean includeHeader

TRUE if column names must be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

We request you to use this method to improve performance and reduce memory consumption while dealing with large data. To know more about ImportDataTable refer this link. This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward.

Examples

The following code illustrates how to Imports data from a System.Data.DataTable into a worksheet with the specified row and column along with save option.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Import data from DataTable
     worksheet.ImportDataTable(table, 1, 1, true, true);

     workbook.SaveAs("ImportDataTable.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataView(DataView, Boolean, Int32, Int32)

Imports data from a System.Data.DataView into worksheet with the specified row and column.

Declaration
int ImportDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn)
Parameters
Type Name Description
System.Data.DataView dataView

DataView with desired data.
System.Boolean isFieldNameShown

TRUE if column names must also be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataView refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataView into worksheet with the specified row and column.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Initialize dataview of datatable
     System.Data.DataView view = table.DefaultView;

     //Import data from DataView
     worksheet.ImportDataView(view, true, 1, 1);

     workbook.SaveAs("ImportDataView.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataView(DataView, Boolean, Int32, Int32, Boolean)

Imports data from a System.Data.DataView into a worksheet with the specified range and preserve type.

Declaration
int ImportDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, bool bPreserveTypes)
Parameters
Type Name Description
System.Data.DataView dataView

DataView with desired data.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Boolean bPreserveTypes

Indicates whether XlsIO should preserve column types from DataReader. By default, preserve type is FALSE. i.e., Setting it to True will import data based on column type, otherwise will import based on value type.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataView refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataView into worksheet with the specified row and column.

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

     //Initialize DataTable and data of DataTable get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Initialize dataview of datatable
     System.Data.DataView view = table.DefaultView;

     //Import data from DataView
     worksheet.ImportDataView(view, true, 1, 1, true);

     workbook.SaveAs("ImportDataView.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add four DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    return table;
  }

ImportDataView(DataView, Boolean, Int32, Int32, Int32, Int32)

Imports data from a System.Data.DataView into a worksheet with specified row, column and preserve type.

Declaration
int ImportDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns)
Parameters
Type Name Description
System.Data.DataView dataView

Data View with desired data.
System.Boolean isFieldNameShown

TRUE if column names must be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Int32 maxRows

Maximum number of rows to import.
System.Int32 maxColumns

Maximum number of columns to import.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataView refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataView into worksheet with the specified row and column.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Initialize dataview of datatable
     System.Data.DataView view = table.DefaultView;

     //Import data from DataView
     worksheet.ImportDataView(view, true, 1, 1, 5, 2);

     workbook.SaveAs("ImportDataView.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportDataView(DataView, Boolean, Int32, Int32, Int32, Int32, Boolean)

Imports data from a System.Data.DataView into a worksheet with specified range and preserve type.

Declaration
int ImportDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns, bool bPreserveTypes)
Parameters
Type Name Description
System.Data.DataView dataView

DataView with desired data.
System.Boolean isFieldNameShown

TRUE if column names must also be imported. FALSE otherwise.
System.Int32 firstRow

First row from where the data should be imported.
System.Int32 firstColumn

First column from where the data should be imported.
System.Int32 maxRows

Maximum number of rows to import.
System.Int32 maxColumns

Maximum number of columns to import.
System.Boolean bPreserveTypes

Indicates whether XlsIO should try to preserve types in Data Table, i.e. if it is set to False (default) and in Data Table we have in string column value that contains only numbers, it would be converted to number.
Returns
Type Description
System.Int32

Number of imported rows.
Remarks

This method is supported in ASP.NET Core, Xamarin, and UWP platforms from .NET Standard 2.0 onward. To know more about ImportDataView refer this link

Examples

The following code illustrates how to Imports data from a System.Data.DataView into worksheet with the specified row and column.

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

     //Initialize DataTable and data get from SampleDataTable method
     System.Data.DataTable table = SampleDataTable();

     //Initialize dataview of datatable
     System.Data.DataView view = table.DefaultView;

     //Import data from DataView.
     worksheet.ImportDataView(view, true, 1, 1, 5, 2, true);

     workbook.SaveAs("ImportDataView.xlsx");
     workbook.Close();
     excelEngine.Dispose();
     }

  System.Data.DataTable SampleDataTable()
  {
    // Here we create a three columns
    System.Data.DataTable table = new System.Data.DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Item", typeof(string));
    table.Columns.Add("Name", typeof(string));

    // Here we add Ten DataRows
    table.Rows.Add(1, "Soap", "David");
    table.Rows.Add(2, "Paste", "Sam");
    table.Rows.Add(3, "Cream", "Christoff");
    table.Rows.Add(4, "Powder", "Janet");
    table.Rows.Add(5, "Snacks", "Andrew");
    table.Rows.Add(6, "Perfume", "Thomos");
    table.Rows.Add(7, "Biscuit", "Stephen");
    table.Rows.Add(8, "Cake", "Jones");
    table.Rows.Add(9, "Fruit", "Yabes");
    table.Rows.Add(10, "vegetable", "Marsion");
    return table;
  }

ImportHtmlTable(Stream, Int32, Int32)

Imports HTML table of a file stream into worksheet from the specified row and column.

Declaration
void ImportHtmlTable(Stream fileStream, int row, int column)
Parameters
Type Name Description
System.IO.Stream fileStream

Specifies the HTML filestream.
System.Int32 row

Specifies the starting row index
System.Int32 column

Specifies the starting column index
Examples

The following code illustrates how to convert HTML table to Excel.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
FileStream html = new FileStream("HTMLtable.html", FileMode.Open, FileAccess.ReadWrite);

sheet.ImportHtmlTable(html, 1, 1);

workbook.SaveAs("HTMLToExcel.xlsx");
workbook.Close();
excelEngine.Dispose();

ImportHtmlTable(Stream, Int32, Int32, HtmlImportOptions)

Imports HTML table of a file stream into worksheet from the specified row and column.

Declaration
void ImportHtmlTable(Stream fileStream, int row, int column, HtmlImportOptions htmlImportOptions)
Parameters
Type Name Description
System.IO.Stream fileStream

Specifies the HTML filestream.
System.Int32 row

Specifies the starting row index
System.Int32 column

Specifies the starting column index
HtmlImportOptions htmlImportOptions

Specifies the html import options.
Examples

The following code illustrates how to convert HTML table to Excel.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];
FileStream html = new FileStream("HTMLtable.html", FileMode.Open, FileAccess.ReadWrite);

sheet.ImportHtmlTable(html, 1, 1, HtmlImportOptions.DetectFormulas);

workbook.SaveAs("HTMLToExcel.xlsx");
workbook.Close();
excelEngine.Dispose();

ImportHtmlTable(String, Int32, Int32)

Imports HTML table of a HTML file into worksheet from the specified row and column.

Declaration
void ImportHtmlTable(string fileName, int row, int column)
Parameters
Type Name Description
System.String fileName

Specifies the HTML file.
System.Int32 row

Specifies the starting row index.
System.Int32 column

Specifies the starting column index.
Examples

The following code illustrates how to convert HTML table to Excel.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

sheet.ImportHtmlTable("HTMLtable.html", 1, 1);

workbook.SaveAs("HTMLToExcel.xlsx");
workbook.Close();
excelEngine.Dispose();

ImportHtmlTable(String, Int32, Int32, HtmlImportOptions)

Imports tables from HTML document into Excel worksheet from the specified row and column with HTML import options.

Declaration
void ImportHtmlTable(string fileName, int row, int column, HtmlImportOptions htmlImportOptions)
Parameters
Type Name Description
System.String fileName

Specifies the HTML file.
System.Int32 row

Specifies the starting row index.
System.Int32 column

Specifies the starting column index.
HtmlImportOptions htmlImportOptions

Specifies the html import options.
Examples

The following code illustrates how to convert HTML table to Excel.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);

IWorksheet sheet = workbook.Worksheets[0];

sheet.ImportHtmlTable("HTMLtable.html", 1, 1, HtmlImportOptions.DetectFormulas);

workbook.SaveAs("HTMLToExcel.xlsx");
workbook.Close();
excelEngine.Dispose();

ImportXml(Stream, Int32, Int32)

Imports XML data into a worksheet at the specified row and column from a given XML data stream.

Declaration
void ImportXml(Stream stream, int row, int column)
Parameters
Type Name Description
System.IO.Stream stream

Stream data of the xml file.
System.Int32 row

Row where the data to be imported.
System.Int32 column

Column where the data to be imported.
Remarks

The stream should be passed as file stream to bind the xml

InsertColumn(Int32)

Inserts an empty column for the specified column index.

Declaration
void InsertColumn(int index)
Parameters
Type Name Description
System.Int32 index

Index at which new column should be inserted.

InsertColumn(Int32, Int32)

Inserts an empty column in the specified column index based on column count.

Declaration
void InsertColumn(int iColumnIndex, int iColumnCount)
Parameters
Type Name Description
System.Int32 iColumnIndex

Index at which new column should be inserted.
System.Int32 iColumnCount

Number of columns to insert.

InsertColumn(Int32, Int32, ExcelInsertOptions)

Inserts an empty column in the specified column index with specified ExcelInsertOptions based on column count.

Declaration
void InsertColumn(int iColumnIndex, int iColumnCount, ExcelInsertOptions insertOptions)
Parameters
Type Name Description
System.Int32 iColumnIndex

Index at which new column should be inserted.
System.Int32 iColumnCount

Number of columns to insert.
ExcelInsertOptions insertOptions

Insert options.

InsertRow(Int32)

Inserts an empty row in the specified row index.

Declaration
void InsertRow(int index)
Parameters
Type Name Description
System.Int32 index

Index at which new row should be inserted.

InsertRow(Int32, Int32)

Inserts an empty rows in the specified row index based on row count.

Declaration
void InsertRow(int iRowIndex, int iRowCount)
Parameters
Type Name Description
System.Int32 iRowIndex

Index at which new row should be inserted.
System.Int32 iRowCount

Number of rows to insert.

InsertRow(Int32, Int32, ExcelInsertOptions)

Inserts an empty row in the specified row index with specified ExcelInsertOptions based on row count.

Declaration
void InsertRow(int iRowIndex, int iRowCount, ExcelInsertOptions insertOptions)
Parameters
Type Name Description
System.Int32 iRowIndex

Index at which new row should be inserted.
System.Int32 iRowCount

Number of rows to insert.
ExcelInsertOptions insertOptions

Insert options.

IntersectRanges(IRange, IRange)

Intersects two ranges.

Declaration
IRange IntersectRanges(IRange range1, IRange range2)
Parameters
Type Name Description
IRange range1

First range to intersect.
IRange range2

Second range to intersect.
Returns
Type Description
IRange

Intersection of two ranges or NULL if there is no range intersection.
Exceptions
Type Condition
System.ArgumentNullException

When range1 or range2 is NULL.

IsColumnVisible(Int32)

Returns True if the specified column is visible to end user.

Declaration
bool IsColumnVisible(int columnIndex)
Parameters
Type Name Description
System.Int32 columnIndex

One-based column index.
Returns
Type Description
System.Boolean

True if column is visible; otherwise, False.

IsRowVisible(Int32)

Returns True if the specified row is visible to end user.

Declaration
bool IsRowVisible(int rowIndex)
Parameters
Type Name Description
System.Int32 rowIndex

One-based row index.
Returns
Type Description
System.Boolean

True if row is visible; otherwise, False.

MergeRanges(IRange, IRange)

Merges two ranges.

Declaration
IRange MergeRanges(IRange range1, IRange range2)
Parameters
Type Name Description
IRange range1

First range to merge.
IRange range2

Second range to merge.
Returns
Type Description
IRange

Merged ranges or NULL if is not able to merge ranges.
Exceptions
Type Condition
System.ArgumentNullException

When range1 or range2 is NULL.

Move(Int32)

Moves worksheet to the specified index.

Declaration
void Move(int iNewIndex)
Parameters
Type Name Description
System.Int32 iNewIndex

New index of the worksheet.

PixelsToColumnWidth(Int32)

Converts the specified column width from pixels to points.

Declaration
double PixelsToColumnWidth(int pixels)
Parameters
Type Name Description
System.Int32 pixels

Width in pixels.
Returns
Type Description
System.Double

Width in points.

Remove()

Removes worksheet from parent worksheet collection.

Declaration
void Remove()

remove_CellValueChanged(RangeImpl.CellValueChangedEventHandler)

Declaration
void remove_CellValueChanged(RangeImpl.CellValueChangedEventHandler value)
Parameters
Type Name Description
RangeImpl.CellValueChangedEventHandler value

remove_ExportDataTableEvent(WorksheetImpl.ExportDataTableEventHandler)

Declaration
void remove_ExportDataTableEvent(WorksheetImpl.ExportDataTableEventHandler value)
Parameters
Type Name Description
WorksheetImpl.ExportDataTableEventHandler value

remove_MissingFunction(MissingFunctionEventHandler)

Declaration
void remove_MissingFunction(MissingFunctionEventHandler value)
Parameters
Type Name Description
MissingFunctionEventHandler value

RemovePanes()

Removes panes from a worksheet.

Declaration
void RemovePanes()

Replace(String, DataColumn, Boolean)

Replaces string with the specified datacolumn value.

Declaration
void Replace(string oldValue, DataColumn newValues, bool isFieldNamesShown)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Data.DataColumn newValues

Data table with new data.
System.Boolean isFieldNamesShown

Indicates whether field name must be shown.
Examples

The following code snippet illustrates how to replace the string value with data column.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1); 
System.Data.DataColumn dataColumn = table.Columns[0];
// Replace the value with data column.
sheet.Replace("AB2", dataColumn, true);

Replace(String, DataTable, Boolean)

Replaces the string with the specified datatable value.

Declaration
void Replace(string oldValue, DataTable newValues, bool isFieldNamesShown)
Parameters
Type Name Description
System.String oldValue

The string to be replaced.
System.Data.DataTable newValues

Data table with new data.
System.Boolean isFieldNamesShown

Indicates whether field name must be shown.
Examples

The following code snippet illustrates how to replace the string value with data table.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Create the data table
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1);
// Replace the value with data table.
sheet.Replace("AB2", table, true);

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 value to replace all occurrences of oldValue.
Examples

The following code illustrates how to replace the string value with datetime.

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";
DateTime dateTime = DateTime.Now;
sheet.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

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

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 = "Ten";
sheet.Replace(oldValue,10.0);

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

The following code snippet illustrates how to replace the string with array of double values.

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";
double[] newValues = { 1.00, 3.00 };
sheet.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

The following code snippet illustrates how to replace the string with array of int values.

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";
int[] newValues = { 1, 2 };
sheet.Replace(oldValue, newValues, true);

Replace(String, String)

Replaces string with the specified 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

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

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

The following code snippet illustrates how to replace the string with array of string values.

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[] newValues = { "X values", "Y values" };
sheet.Replace(oldValue, newValues, true);

SaveAs(Stream, String)

Saves worksheet 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.
Examples

The following code illustrates how to saves the worksheet as stream with separator.

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

SaveAs(Stream, String, Encoding)

Saves worksheet as stream using separator with specified 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.
System.String separator

Denotes separator for the CSV file types.
System.Text.Encoding encoding

Encoding to use.
Examples

The following code illustrates how to saves the worksheet as stream with encoding.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
Stream stream = new MemoryStream();
sheet.SaveAs(stream, ",",Encoding.ASCII);

SaveAsHtml(Stream)

Saves worksheet as stream.

Declaration
void SaveAsHtml(Stream stream)
Parameters
Type Name Description
System.IO.Stream stream

Stream to save.
Remarks

This method is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.

Examples

The following code snippets illustrates how to save as html as stream.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
Stream stream = new MemoryStream();
sheet.SaveAsHtml(stream);

SaveAsHtml(Stream, HtmlSaveOptions)

Saves worksheet as stream with the specified HtmlSaveOptions.

Declaration
void SaveAsHtml(Stream stream, HtmlSaveOptions saveOptions)
Parameters
Type Name Description
System.IO.Stream stream

Stream to save.
HtmlSaveOptions saveOptions

Save Options
Remarks

This method is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.

Examples

The following code snippets illustrates how to save as html as stream with Save option.

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
Stream stream = new MemoryStream();
sheet.SaveAsHtml(stream,HtmlSaveOptions.Default);

SetBlank(Int32, Int32)

Blanks the specified cell.

Declaration
void SetBlank(int iRow, int iColumn)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.

SetBoolean(Int32, Int32, Boolean)

Sets boolean value for the specified cell.

Declaration
void SetBoolean(int iRow, int iColumn, bool value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.Boolean value

Value to set.

SetColumnWidth(Int32, Double)

Sets column width for the specified column.

Declaration
void SetColumnWidth(int iColumnIndex, double value)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
System.Double value

Width to set.
Examples

The following code illustrates how to set width for a column.

        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 column width
            worksheet.SetColumnWidth(2, 160);

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

SetColumnWidthInPixels(Int32, Int32)

Sets column width in pixels for the specified column.

Declaration
void SetColumnWidthInPixels(int iColumnIndex, int value)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
System.Int32 value

Width in pixels to set.
Examples

The following code illustrates how to set width for a column.

        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 column width
            worksheet.SetColumnWidthInPixels(2, 160);

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

SetColumnWidthInPixels(Int32, Int32, Int32)

Sets column width in pixels to the given number of columns from the specified column index.

Declaration
void SetColumnWidthInPixels(int iStartColumnIndex, int iCount, int value)
Parameters
Type Name Description
System.Int32 iStartColumnIndex

Start Column index
System.Int32 iCount

No of Column to be set width
System.Int32 value

Value in pixel to set
Examples

The following code illustrates how to set width for columns.

        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 column width
            worksheet.SetColumnWidthInPixels(2, 4, 160);

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

SetDefaultColumnStyle(Int32, IStyle)

Sets the default column style for the specified column.

Declaration
void SetDefaultColumnStyle(int iColumnIndex, IStyle defaultStyle)
Parameters
Type Name Description
System.Int32 iColumnIndex

One-based column index.
IStyle defaultStyle

Default style.
Examples

The following code illustrates how to set the default style for a column.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultColumnStyle(2, style);

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

SetDefaultColumnStyle(Int32, Int32, IStyle)

Sets the default column style for the specified starting and ending column.

Declaration
void SetDefaultColumnStyle(int iStartColumnIndex, int iEndColumnIndex, IStyle defaultStyle)
Parameters
Type Name Description
System.Int32 iStartColumnIndex

Starting column index.
System.Int32 iEndColumnIndex

Ending column index.
IStyle defaultStyle

Default style.
Examples

The following code illustrates how to set the default style for columns.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultColumnStyle(2, 5, style);

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

SetDefaultRowStyle(Int32, IStyle)

Sets the default row style for the specified row.

Declaration
void SetDefaultRowStyle(int iRowIndex, IStyle defaultStyle)
Parameters
Type Name Description
System.Int32 iRowIndex

One-based row index.
IStyle defaultStyle

Default style.
Examples

The following code illustrates how to set the default style for a row.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultRowStyle(2, style);

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

SetDefaultRowStyle(Int32, Int32, IStyle)

Sets the default row style for the specified starting and ending row.

Declaration
void SetDefaultRowStyle(int iStartRowIndex, int iEndRowIndex, IStyle defaultStyle)
Parameters
Type Name Description
System.Int32 iStartRowIndex

Starting row index.
System.Int32 iEndRowIndex

Ending row index.
IStyle defaultStyle

Default style.
Examples

The following code illustrates how to set the default style for rows.

        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 style
            IStyle style = workbook.Styles.Add("CustomStyle");

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

            //Set default style
            worksheet.SetDefaultRowStyle(2, 5, style);

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

SetError(Int32, Int32, String)

Sets error for the specified cell.

Declaration
void SetError(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Error to set.

SetFormula(Int32, Int32, String)

Sets formula for the specified cell.

Declaration
void SetFormula(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Formula to set.

SetFormulaBoolValue(Int32, Int32, Boolean)

Sets formula bool value for the specified cell.

Declaration
void SetFormulaBoolValue(int iRow, int iColumn, bool value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.Boolean value

Represents formula bool value.

SetFormulaErrorValue(Int32, Int32, String)

Sets formula error value for the specified cell.

Declaration
void SetFormulaErrorValue(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Represents formula error value.

SetFormulaNumberValue(Int32, Int32, Double)

Sets formula number value for the specified cell.

Declaration
void SetFormulaNumberValue(int iRow, int iColumn, double value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.Double value

Represents formula number value.

SetFormulaStringValue(Int32, Int32, String)

Sets formula string value for the specified cell.

Declaration
void SetFormulaStringValue(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Represents formula string value.

SetNumber(Int32, Int32, Double)

Sets number for the specified cell.

Declaration
void SetNumber(int iRow, int iColumn, double value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.Double value

Value to set.

SetRowHeight(Int32, Double)

Sets row height for the specified row.

Declaration
void SetRowHeight(int iRow, double value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Double value

Height to set.
Examples

The following code illustrates how to set height for a row.

        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 row height
            worksheet.SetRowHeight(3, 45);

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

SetRowHeightInPixels(Int32, Double)

Sets row height in pixels for the specified row.

Declaration
void SetRowHeightInPixels(int iRowIndex, double value)
Parameters
Type Name Description
System.Int32 iRowIndex

One-based row index.
System.Double value

Value in pixels to set.
Examples

The following code illustrates how to set height for a row.

        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 row height
            worksheet.SetRowHeightInPixels(3, 150);

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

SetRowHeightInPixels(Int32, Int32, Double)

Sets row height in pixels to the given number of rows from the specified row index.

Declaration
void SetRowHeightInPixels(int iStartRowIndex, int iCount, double value)
Parameters
Type Name Description
System.Int32 iStartRowIndex

Starting row index.
System.Int32 iCount

No of Row to be set width.
System.Double value

Value in pixels to set.
Examples

The following code illustrates how to set height for a row.

        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 row height
            worksheet.SetRowHeightInPixels(3, 4, 150);

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

SetText(Int32, Int32, String)

Sets text for the specified cell.

Declaration
void SetText(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Text to set.

SetValue(Int32, Int32, String)

Sets value for the specified cell.

Declaration
void SetValue(int iRow, int iColumn, string value)
Parameters
Type Name Description
System.Int32 iRow

One-based row index.
System.Int32 iColumn

One-based column index.
System.String value

Value to set.

ShowColumn(Int32, Boolean)

Shows the specified column.

Declaration
void ShowColumn(int columnIndex, bool isVisible)
Parameters
Type Name Description
System.Int32 columnIndex

Index at which the column should be hidden.
System.Boolean isVisible

True - Column is visible; False - hidden.

ShowRange(RangesCollection, Boolean)

Shows or Hides the collection of range.

Declaration
void ShowRange(RangesCollection ranges, bool isVisible)
Parameters
Type Name Description
RangesCollection ranges

Ranges specifies the range collection.
System.Boolean isVisible

True - Row is visible; False - hidden.

ShowRange(IRange, Boolean)

Shows or Hides the specified range.

Declaration
void ShowRange(IRange range, bool isVisible)
Parameters
Type Name Description
IRange range

Range specifies the particular range to show / hide.
System.Boolean isVisible

True - Row is visible; False - hidden.

ShowRange(IRange[], Boolean)

Shows or Hides an array of range.

Declaration
void ShowRange(IRange[] ranges, bool isVisible)
Parameters
Type Name Description
IRange[] ranges

Ranges specifies the range array.
System.Boolean isVisible

True - Row is visible; False - hidden.

ShowRow(Int32, Boolean)

Shows or Hides the specified row.

Declaration
void ShowRow(int rowIndex, bool isVisible)
Parameters
Type Name Description
System.Int32 rowIndex

Index at which the row should be hidden.
System.Boolean isVisible

True - Row is visible; False - hidden.

Events

CellValueChanged

Occurs when the value of a cell changes.

Declaration
event RangeImpl.CellValueChangedEventHandler CellValueChanged
Event Type
Type
RangeImpl.CellValueChangedEventHandler

ExportDataTableEvent

Event to choose an action while exporting data from Excel to data table.

Declaration
event WorksheetImpl.ExportDataTableEventHandler ExportDataTableEvent
Event Type
Type
WorksheetImpl.ExportDataTableEventHandler

MissingFunction

Event raised when an unknown function is encountered.

Declaration
event MissingFunctionEventHandler MissingFunction
Event Type
Type
MissingFunctionEventHandler