Interface IWorksheet

Represents a worksheet in a workbook.

Inherited Members

ITabSheet.Activate()

ITabSheet.Select()

ITabSheet.Unselect()

ITabSheet.Protect(String)

ITabSheet.Protect(String, ExcelSheetProtection)

ITabSheet.Unprotect(String)

ITabSheet.TabColor

ITabSheet.TabColorRGB

ITabSheet.Charts

ITabSheet.Pictures

ITabSheet.Workbook

ITabSheet.Shapes

ITabSheet.IsRightToLeft

ITabSheet.IsSelected

ITabSheet.TabIndex

ITabSheet.Name

ITabSheet.Visibility

ITabSheet.TextBoxes

ITabSheet.CheckBoxes

ITabSheet.OptionButtons

ITabSheet.ComboBoxes

ITabSheet.CodeName

ITabSheet.ProtectContents

ITabSheet.ProtectDrawingObjects

ITabSheet.ProtectScenarios

ITabSheet.Protection

ITabSheet.IsPasswordProtected

IParentApplication.Application

IParentApplication.Parent

ICalcData.GetValueRowCol(Int32, Int32)

ICalcData.SetValueRowCol(Object, Int32, Int32)

ICalcData.WireParentObject()

ICalcData.ValueChanged

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Base.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	Description
System.Int32

AutoFilters

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

Declaration

IAutoFilters AutoFilters { get; }

Property Value

Type	Description
IAutoFilters

CalcEngine

Gets or sets the a CalcEngine object associated with ICalcData implementation.

Declaration

CalcEngine CalcEngine { get; set; }

Property Value

Type	Description
CalcEngine

Cells

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

Declaration

IRange[] Cells { get; }

Property Value

Type	Description
IRange[]

Columns

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

Declaration

IRange[] Columns { get; }

Property Value

Type	Description
IRange[]

Comments

Gets the IComments collection in the worksheet.

Declaration

IComments Comments { get; }

Property Value

Type	Description
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	Description
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	Description
System.Boolean

FirstVisibleColumn

Gets or sets the first visible column index.

Declaration

int FirstVisibleColumn { get; set; }

Property Value

Type	Description
System.Int32

FirstVisibleRow

Gets or sets the first visible row index.

Declaration

int FirstVisibleRow { get; set; }

Property Value

Type	Description
System.Int32

GridLineColor

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

Declaration

ExcelKnownColors GridLineColor { get; set; }

Property Value

Type	Description
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	Description
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	Description
IHPageBreaks

HyperLinks

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

Declaration

IHyperLinks HyperLinks { get; }

Property Value

Type	Description
IHyperLinks

Index

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

Declaration

int Index { get; }

Property Value

Type	Description
System.Int32

IsDisplayZeros

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

Declaration

bool IsDisplayZeros { get; set; }

Property Value

Type	Description
System.Boolean

IsFreezePanes

Defines whether freezed panes are applied.

Declaration

bool IsFreezePanes { get; }

Property Value

Type	Description
System.Boolean

IsGridLinesVisible

True if grid lines are visible. otherwise, False.

Declaration

bool IsGridLinesVisible { get; set; }

Property Value

Type	Description
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	Description
System.Boolean

IsStringsPreserved

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

Declaration

bool IsStringsPreserved { get; set; }

Property Value

Type	Description
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	Description
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	Description
IRange

Item[String]

Gets cell Range. Read-only.

Declaration

IRange this[string name] { get; }

Parameters

Type	Name	Description
System.String	name

Property Value

Type	Description
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	Description
IRange

LeftVisibleColumn

Gets or sets the left visible column of the worksheet.

Declaration

int LeftVisibleColumn { get; set; }

Property Value

Type	Description
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	Description
IListObjects

MergedCells

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

Declaration

IRange[] MergedCells { get; }

Property Value

Type	Description
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	Description
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	Description
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	Description
IPageSetup

PivotTables

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

Declaration

IPivotTables PivotTables { get; }

Property Value

Type	Description
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	Description
IRange

Rows

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

Declaration

IRange[] Rows { get; }

Property Value

Type	Description
IRange[]

Scenarios

Gets the scenarios in the current worksheet.

Declaration

IScenarios Scenarios { get; }

Property Value

Type	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
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	Description
ExcelSheetType

UsedCells

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

Declaration

IRange[] UsedCells { get; }

Property Value

Type	Description
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	Description
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	Description
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	Description
System.Boolean

VerticalSplit

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

Declaration

int VerticalSplit { get; set; }

Property Value

Type	Description
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	Description
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	Description
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	Description
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

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(Int32, Int32, Int32, Int32)

Converts the specified range into image. Default image type is Bitmap.

Declaration

Image ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColumn)

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.

Returns

Type	Description
System.Drawing.Image	Converted Image for the specified range.

Remarks

Subscript/Superscript,RTF,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

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
// Converts the Range(R1C1 to R10C20) in worksheet to image.
System.Drawing.Image img = sheet.ConvertToImage(1, 1, 10, 20);
img.Save("Sample.png", System.Drawing.Imaging.ImageFormat.Png);

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

Converts the specified range into image with the specified type.

Declaration

Image ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColumn, ImageType imageType, Stream stream)

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.
ImageType	imageType	Type of the image to create.
System.IO.Stream	stream	Stream to be converted to an image. It is ignored if null.

Returns

Type	Description
System.Drawing.Image	Converted Image for the specified range.

Remarks

Subscript/Superscript,RTF,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

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
MemoryStream stream = new MemoryStream();
// Converts the Range(R1C1 to R10C20) in worksheet to image.
sheet.ConvertToImage(1, 1, 10, 20, ImageType.Metafile, stream);

ConvertToImage(Int32, Int32, Int32, Int32, ImageType, Stream, EmfType)

Converts the specified range into image along with Metafile.

Declaration

Image ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColumn, ImageType imageType, Stream outputStream, EmfType emfType)

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.
ImageType	imageType	Type of the image to create.
System.IO.Stream	outputStream	Stream to be converted to an image. It is ignored if null.
System.Drawing.Imaging.EmfType	emfType	Enhanced MetaFile, to render the image in meta file format.

Returns

Type	Description
System.Drawing.Image	Converted Image for the specified range.

Remarks

Subscript/Superscript,RTF,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

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
MemoryStream stream = new MemoryStream();
// Converts the Range(R1C1 to R10C20) in worksheet to image.
sheet.ConvertToImage(1, 1, 10, 20, ImageType.Metafile, stream, System.Drawing.Imaging.EmfType.EmfOnly);

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

Converts the specified range into Metafile.

Declaration

Image ConvertToImage(int firstRow, int firstColumn, int lastRow, int lastColumn, EmfType emfType, 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.
System.Drawing.Imaging.EmfType	emfType	Enhanced MetaFile, to render the image in meta file format.
System.IO.Stream	outputStream	Stream to be converted to an image. It is ignored if null.

Returns

Type	Description
System.Drawing.Image	Converted Image for the specified range.

Remarks

Subscript/Superscript,RTF,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

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
MemoryStream stream = new MemoryStream();
// Converts the Range(R1C1 to R10C20) in worksheet to image.
sheet.ConvertToImage(1, 1, 10, 20, System.Drawing.Imaging.EmfType.EmfOnly,  stream);

CopyToClipboard()

Copies worksheet data to the clipboard.

Declaration

void CopyToClipboard()

Remarks

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

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<T> 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<T> 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<T> 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<T> 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;
  }

ImportDataGrid(DataGrid, Int32, Int32, Boolean, Boolean)

Imports data from Microsoft System.Web.UI.WebControls.DataGrid into worksheet.

Declaration

void ImportDataGrid(DataGrid dataGrid, int firstRow, int firstColumn, bool isImportHeader, bool isImportStyle)

Parameters

Type	Name	Description
System.Web.UI.WebControls.DataGrid	dataGrid	DataGrid with datasource.
System.Int32	firstRow	Represents the first row of the worksheet to import.
System.Int32	firstColumn	Represents the first column of the worksheet to import.
System.Boolean	isImportHeader	TRUE if header must be imported. FALSE otherwise.
System.Boolean	isImportStyle	TRUE if row style must be imported. FALSE otherwise.

Remarks

This method is supported in ASP.NET Web Forms only. To know more about ImportDataGrid refer this link

Examples

The following code illustrates how to Imports data from MS System.Web.UI.WebControls.DataGrid 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];

    //Set value to the DataSource of DataGrid web control
    System.Windows.Forms.DataGrid dataGrid = new System.Windows.Forms.DataGrid();
    dataGrid.DataSource = GetDataTable();
    dataGrid.DataBind();

    //Import data from DataGrid control
    worksheet.ImportDataGrid(dataGrid, 1, 1, true, true);

    workbook.SaveAs("ImportDataGrid.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
    workbook.Close();
    excelEngine.Dispose();
    }

  System.Data.DataTable GetDataTable()
  {
    // 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;
  }

ImportDataGrid(DataGrid, Int32, Int32, Boolean, Boolean)

Imports data from Microsoft System.Windows.Forms.DataGrid into worksheet.

Declaration

void ImportDataGrid(DataGrid dataGrid, int firstRow, int firstColumn, bool isImportHeader, bool isImportStyle)

Parameters

Type	Name	Description
System.Windows.Forms.DataGrid	dataGrid	DataGrid with datasource.
System.Int32	firstRow	Represents the first row of the worksheet to import.
System.Int32	firstColumn	Represents the first column of the worksheet to import.
System.Boolean	isImportHeader	TRUE if header must be imported. FALSE otherwise.
System.Boolean	isImportStyle	TRUE if row style must be imported. FALSE otherwise.

Remarks

This method is only supported in Windows Form platform. To know more about ImportDataGrid refer this link

Examples

The following code illustrates how to Imports data from Microsoft System.Windows.Forms.DataGrid 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 DataGrid control
     System.Windows.Forms.DataGrid dataGrid = new System.Windows.Forms.DataGrid();
     dataGrid.DataSource = GetDataTable();

     //Import data from DataGrid control
     worksheet.ImportDataGrid(dataGrid, 1, 1, true, true);

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

  System.Data.DataTable GetDataTable()
  {
    // 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;
  }

ImportDataGridView(DataGridView, Int32, Int32, Boolean, Boolean)

Imports data from Microsoft System.Windows.Forms.DataGridView into worksheet.

Declaration

void ImportDataGridView(DataGridView dataGridView, int firstRow, int firstColumn, bool isImportHeader, bool isImportStyle)

Parameters

Type	Name	Description
System.Windows.Forms.DataGridView	dataGridView	DataGridView with datasource.
System.Int32	firstRow	Represents the first row of the worksheet to import.
System.Int32	firstColumn	Represents the first column of the worksheet to import.
System.Boolean	isImportHeader	TRUE if header must be imported. FALSE otherwise.
System.Boolean	isImportStyle	TRUE if row style must be imported. FALSE otherwise.

Remarks

This method is only supported in Windows Form platform. To know more about ImportDataGridView refer this link

Examples

The following code illustrates how to Imports data from Microsoft System.Windows.Forms.DataGridView 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 DataGridView control
     System.Windows.Forms.DataGridView dataGridView = new System.Windows.Forms.DataGridView();

     //Get the data from GetDataTable method.
     dataGridView.DataSource = GetDataTable();

     //Import data from DataGridView control
     worksheet.ImportDataGridView(dataGridView, 1, 1, true, true);

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

  System.Data.DataTable GetDataTable()
  {
    // 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 System.Data.SqlClient.SqlDataReader or System.Data.OleDb.OleDbDataReader 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 System.Data.SqlClient.SqlDataReader or System.Data.OleDb.OleDbDataReader 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 System.Data.SqlClient.SqlDataReader or System.Data.OleDb.OleDbDataReader 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 System.Data.SqlClient.SqlDataReader or System.Data.OleDb.OleDbDataReader 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;
  }

ImportGridView(GridView, Int32, Int32, Boolean, Boolean)

Imports data from Microsoft System.Web.UI.WebControls.GridView into worksheet.

Declaration

void ImportGridView(GridView gridView, int firstRow, int firstColumn, bool isImportHeader, bool isImportStyle)

Parameters

Type	Name	Description
System.Web.UI.WebControls.GridView	gridView	GridView with datasource.
System.Int32	firstRow	Represents the first row of the worksheet to import.
System.Int32	firstColumn	Represents the first column of the worksheet to import.
System.Boolean	isImportHeader	TRUE if header must be imported. FALSE otherwise.
System.Boolean	isImportStyle	TRUE if row style must be imported. FALSE otherwise.

Remarks

This method is supported in ASP.NET Web Forms only. To know more about ImportDataGrid refer this link

Examples

The following code illustrates how to Imports data from MS System.Web.UI.WebControls.GridView into worksheet with the specified row and column.

using Syncfusion.XlsIO;
using System;
using System.Data;

public partial class Sample : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
   GridView();
  }

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

    //Set value to the DataSource of GridView web control
    gridView.DataSource = GetDataTable();
    gridView.DataBind();

    //Import data from DataGrid control
    worksheet.ImportGridView(gridView, 1, 1, true,true);

    workbook.SaveAs("ImportGridView.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
    workbook.Close();
    excelEngine.Dispose();
  }

  static DataTable GetDataTable()
  {
    // Here we create a three columns
    DataTable table = new 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;
  }
}

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();

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()

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

SaveAs(String, String)

Saves worksheet with specified file name using separator. Used only for CSV files.

Declaration

void SaveAs(string fileName, string separator)

Parameters

Type	Name	Description
System.String	fileName	File to save.
System.String	separator	Denotes separator for the CSV file types.

Remarks

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

Examples

The following code illustrates how to saves the worksheet 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];
sheet.SaveAs("newFile.csv", ",");

SaveAs(String, String, Encoding)

Saves worksheet using separator with specified file name and encoding. Used only for CSV files.

Declaration

void SaveAs(string fileName, string separator, Encoding encoding)

Parameters

Type	Name	Description
System.String	fileName	File to save.
System.String	separator	Denotes separator for the CSV file types.
System.Text.Encoding	encoding	Encoding to use.

Remarks

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

Examples

The following code illustrates how to saves the worksheet 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];
sheet.SaveAs("newFile.csv", ",",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);

SaveAsHtml(String)

Saves worksheet with specified filename.

Declaration

void SaveAsHtml(string filename)

Parameters

Type	Name	Description
System.String	filename

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 to the specified file name.

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.SaveAsHtml("Output.html");

SaveAsHtml(String, HtmlSaveOptions)

Saves worksheet with specified filename and HtmlSaveOptions.

Declaration

void SaveAsHtml(string filename, HtmlSaveOptions saveOptions)

Parameters

Type	Name	Description
System.String	filename	File 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 to the specified file name and 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];
sheet.SaveAsHtml("Output.html",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	Description
RangeImpl.CellValueChangedEventHandler

ExportDataTableEvent

Event to choose an action while exporting data from Excel to data table.

Declaration

event WorksheetImpl.ExportDataTableEventHandler ExportDataTableEvent

Event Type

Type	Description
WorksheetImpl.ExportDataTableEventHandler

MissingFunction

Event raised when an unknown function is encountered.

Declaration

event MissingFunctionEventHandler MissingFunction

Event Type

Type	Description
MissingFunctionEventHandler