Interface IWorksheet
Represents a worksheet in a workbook.
Inherited Members
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 |
---|
System.Int32 |
AutoFilters
Gets the auto filters collection in the worksheet. Read-only.
Declaration
IAutoFilters AutoFilters { get; }
Property Value
Type |
---|
IAutoFilters |
CalcEngine
Gets or sets the a CalcEngine object associated with ICalcData implementation.
Declaration
CalcEngine CalcEngine { get; set; }
Property Value
Type |
---|
CalcEngine |
Cells
Gets the used cells in the worksheet. Read-only.
Declaration
IRange[] Cells { get; }
Property Value
Type |
---|
IRange[] |
Columns
Gets a Range object that represents all the columns in the specified worksheet. Read-only.
Declaration
IRange[] Columns { get; }
Property Value
Type |
---|
IRange[] |
Comments
Gets the IComments collection in the worksheet.
Declaration
IComments Comments { get; }
Property Value
Type |
---|
IComments |
Examples
The following code illustrates how to access the IComments collection in the worksheet.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
// Adding comments to a cell.
ICommentShape comment1 = sheet.Range["A1"].AddComment();
ICommentShape comment2 = sheet.Range["B1"].AddComment();
//Set comment text
comment1.Text= "Comment1";
comment2.Text= "Comment2";
//Check count
Console.Write(sheet.Comments.Count);
//Save and dispose
workbook.SaveAs("Comments.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//2
CustomProperties
Gets a collection of custom properties of the worksheet. Read-only.
Declaration
IWorksheetCustomProperties CustomProperties { get; }
Property Value
Type |
---|
IWorksheetCustomProperties |
DataSorter
Gets the DataSorter.
Declaration
IDataSort DataSorter { get; }
Property Value
Type | Description |
---|---|
IDataSort | The sparkline groups. |
Examples
The following code illustrates how to get the DataSorter from the sheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sorting.xlsx");
//Create DataSorter for worksheet.
IDataSort sheetSort = workbook.Worksheets[0].DataSorter;
//Adding Sort range for worksheet
sheetSort.SortRange = workbook.Worksheets[0].UsedRange;
//Adding Sorting fields for DataSorter.
sheetSort.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Perform Sorting for worksheet range.
sheetSort.Sort();
workbook.SaveAs("SortedExcel.xlsx");
workbook.Close();
excelEngine.Dispose();
DisplayPageBreaks
Gets or sets a value that indicates whether page breaks (both automatic and manual) on the worksheet are displayed.
Declaration
bool DisplayPageBreaks { get; set; }
Property Value
Type |
---|
System.Boolean |
FirstVisibleColumn
Gets or sets the first visible column index.
Declaration
int FirstVisibleColumn { get; set; }
Property Value
Type |
---|
System.Int32 |
FirstVisibleRow
Gets or sets the first visible row index.
Declaration
int FirstVisibleRow { get; set; }
Property Value
Type |
---|
System.Int32 |
GridLineColor
Gets or sets the color of the Grid line in the worksheet.
Declaration
ExcelKnownColors GridLineColor { get; set; }
Property Value
Type |
---|
ExcelKnownColors |
Examples
The following code illustrates how to set the grid line color.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet worksheet = workbook.Worksheets[0];
//Set grid lines color
worksheet.GridLineColor = ExcelKnownColors.Red;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}
HasOleObject
Gets whether the OLE object is present in the worksheet. Read-only.
Declaration
bool HasOleObject { get; }
Property Value
Type | Description |
---|---|
System.Boolean | True if this instance is OLE object; otherwise, False. |
Remarks
This property is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.
Examples
The following code illustrates how to access the IListObjects collection in the worksheet to add a new IOleObject.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create image stream
System.Drawing.Image image = System.Drawing.Image.FromFile("image.png");
//Add ole object
IOleObject oleObject = worksheet.OleObjects.Add("Shapes.xlsx", image, OleLinkType.Embed);
//Check HasOleObject
Console.Write(worksheet.HasOleObject);
//Save and dispose
workbook.SaveAs("OLEObjects.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//True
HorizontalSplit
Gets or sets the position of horizontal split in the worksheet.
Declaration
int HorizontalSplit { get; set; }
Property Value
Type |
---|
System.Int32 |
Remarks
Position of the horizontal split (by, 0 = No horizontal split): Unfrozen pane: Height of the top pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible rows in top pane(s)
HPageBreaks
Gets a HPageBreaksCollection that represents the horizontal page breaks in the worksheet. Read-only.
Declaration
IHPageBreaks HPageBreaks { get; }
Property Value
Type |
---|
IHPageBreaks |
HyperLinks
Gets a hyperlink collections in the worksheet. Read-only.
Declaration
IHyperLinks HyperLinks { get; }
Property Value
Type |
---|
IHyperLinks |
Index
Gets the index number of the worksheet within the collection of worksheet. Read-only.
Declaration
int Index { get; }
Property Value
Type |
---|
System.Int32 |
IsDisplayZeros
True if zero values to be displayed. otherwise, False.
Declaration
bool IsDisplayZeros { get; set; }
Property Value
Type |
---|
System.Boolean |
IsFreezePanes
Defines whether freezed panes are applied.
Declaration
bool IsFreezePanes { get; }
Property Value
Type |
---|
System.Boolean |
IsGridLinesVisible
True if grid lines are visible. otherwise, False.
Declaration
bool IsGridLinesVisible { get; set; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates how to set visibility for grid lines.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set grid line visibility
worksheet.IsGridLinesVisible = false;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}
IsRowColumnHeadersVisible
True if row and column headers are visible. otherwise, False.
Declaration
bool IsRowColumnHeadersVisible { get; set; }
Property Value
Type |
---|
System.Boolean |
IsStringsPreserved
True if all values in the worksheet are preserved as strings. otherwise, False.
Declaration
bool IsStringsPreserved { get; set; }
Property Value
Type |
---|
System.Boolean |
Examples
The following code illustrates if the values in the worksheet are preserved as strings.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.IsStringsPreserved = true;
worksheet["A1"].Value = "10";
worksheet["A2"].Value = "Test";
worksheet["A3"].Value = "=SUM(1+1)";
Item[Int32, Int32]
Gets or sets cell range by row and column index. Row and column indexes are one-based.
Declaration
IRange this[int row, int column] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | row | One-based row index. |
System.Int32 | column | One-based column index. |
Property Value
Type |
---|
IRange |
Item[Int32, Int32, Int32, Int32]
Get cell Range. Row and column indexes are one-based. Read-only.
Declaration
IRange this[int row, int column, int lastRow, int lastColumn] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | row | First row index. One-based. |
System.Int32 | column | First column index. One-based. |
System.Int32 | lastRow | Last row index. One-based. |
System.Int32 | lastColumn | Last column index. One-based. |
Property Value
Type |
---|
IRange |
Item[String]
Gets cell Range. Read-only.
Declaration
IRange this[string name] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.String | name |
Property Value
Type |
---|
IRange |
Item[String, Boolean]
Gets cell Range with R1C1Notation flag. Read-only.
Declaration
IRange this[string name, bool IsR1C1Notation] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.String | name | |
System.Boolean | IsR1C1Notation |
Property Value
Type |
---|
IRange |
LeftVisibleColumn
Gets or sets the left visible column of the worksheet.
Declaration
int LeftVisibleColumn { get; set; }
Property Value
Type |
---|
System.Int32 |
Examples
The following code illustrates how to get the left visible column.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
int leftVisibleColumn = worksheet.LeftVisibleColumn;
ListObjects
Gets a collection of list objects in the worksheet. Read-only.
Declaration
IListObjects ListObjects { get; }
Property Value
Type |
---|
IListObjects |
MergedCells
Gets all the merged ranges in the worksheet. Read-only.
Declaration
IRange[] MergedCells { get; }
Property Value
Type |
---|
IRange[] |
Examples
The following code illustrates how to get the merged ranges.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Merge cells
worksheet["C2:D2"].Merge();
worksheet["F3:G3"].Merge();
//Get merged ranges
IRange[] mergedRanges = worksheet.MergedCells;
//Get merged range count
Console.Write(mergedRanges.Length);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//2
MigrantRange
Declaration
IMigrantRange MigrantRange { get; }
Property Value
Type |
---|
IMigrantRange |
Names
Gets a Names collection that represents the worksheet-specific names (names defined with the "WorksheetName!" prefix) in the worksheet. Read-only.
Declaration
INames Names { get; }
Property Value
Type |
---|
INames |
OleObjects
Gets IOleObjects collection in the worksheet.
Declaration
IOleObjects OleObjects { get; }
Property Value
Type | Description |
---|---|
IOleObjects | The OLE objects. |
Remarks
This property is supported on Windows Forms, WPF, ASP.NET and ASP.NET MVC platforms only.
Examples
The following code illustrates how to access the IListObjects collection in the worksheet to add a new IOleObject.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create image stream
System.Drawing.Image image = System.Drawing.Image.FromFile("image.png");
//Add ole object
IOleObject oleObject = worksheet.OleObjects.Add("Shapes.xlsx", image, OleLinkType.Embed);
//Save and dispose
workbook.SaveAs("OLEObjects.xlsx");
workbook.Close();
}
PageSetup
Gets a PageSetup object that contains all the page setup settings for the specified object. Read-only.
Declaration
IPageSetup PageSetup { get; }
Property Value
Type |
---|
IPageSetup |
PivotTables
Gets a collection of pivot tables in the worksheet. Read-only.
Declaration
IPivotTables PivotTables { get; }
Property Value
Type |
---|
IPivotTables |
Range
Gets a Range object that represents a cell or a range of cells in the worksheet.
Declaration
IRange Range { get; }
Property Value
Type |
---|
IRange |
Rows
Gets a Range object that represents the rows in the specified worksheet. Read-only.
Declaration
IRange[] Rows { get; }
Property Value
Type |
---|
IRange[] |
Scenarios
Gets the scenarios in the current worksheet.
Declaration
IScenarios Scenarios { get; }
Property Value
Type |
---|
IScenarios |
Examples
The following code illustrates how to get the scenarios for the current worksheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
IScenarios scenarios = sheet.Scenarios;
Slicers
Gets the collection of slicers present in the worksheet.
Declaration
ISlicers Slicers { get; }
Property Value
Type |
---|
ISlicers |
SparklineGroups
Gets the sparkline groups.
Declaration
ISparklineGroups SparklineGroups { get; }
Property Value
Type | Description |
---|---|
ISparklineGroups | The sparkline groups. |
Examples
The following code illustrates how to get the Spark line groups from the sheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//A new Sparkline group is added to the sheet sparklinegroups
ISparklineGroup sparklineGroup = sheet.SparklineGroups.Add();
//Set the Sparkline group type as line
sparklineGroup.SparklineType = SparklineType.Line;
//Set to display the empty cell as line
sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Line;
//Sparkline group style properties
sparklineGroup.ShowFirstPoint = true;
sparklineGroup.FirstPointColor = System.Drawing.Color.Green;
sparklineGroup.ShowLastPoint = true;
sparklineGroup.LastPointColor = System.Drawing.Color.DarkOrange;
sparklineGroup.ShowHighPoint = true;
sparklineGroup.HighPointColor = System.Drawing.Color.DarkBlue;
sparklineGroup.ShowLowPoint = true;
sparklineGroup.LowPointColor = System.Drawing.Color.DarkViolet;
sparklineGroup.ShowMarkers = true;
sparklineGroup.MarkersColor = System.Drawing.Color.Black;
sparklineGroup.ShowNegativePoint = true;
sparklineGroup.NegativePointColor = System.Drawing.Color.Red;
//set the line weight
sparklineGroup.LineWeight = 0.3;
//The sparklines are added to the sparklinegroup.
ISparklines sparklines = sparklineGroup.Add();
//Set the Sparkline Datarange
IRange dataRange = sheet.Range["D6:G17"];
//Set the Sparkline Reference range
IRange referenceRange = sheet.Range["H6:H17"];
//Create a sparkline with the datarange and reference range
sparklines.Add(dataRange, referenceRange);
ISparklineGroups sparkline = sheet.SparklineGroups;
SplitCell
Gets split cell range.
Declaration
IRange SplitCell { get; }
Property Value
Type |
---|
IRange |
StandardHeight
Gets or sets the standard (default) height of all the rows in the worksheet, in points.
Declaration
double StandardHeight { get; set; }
Property Value
Type |
---|
System.Double |
Examples
The following code illustrates how to get the standard height.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Text";
//Set standard height
worksheet.StandardHeight = 40;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}
StandardHeightFlag
Gets or sets the standard (default) height option flag.
Declaration
bool StandardHeightFlag { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
Which defines that standard (default) row height and book default font height do not match.
StandardWidth
Gets or sets the standard (default) width of all the columns in the worksheet.
Declaration
double StandardWidth { get; set; }
Property Value
Type |
---|
System.Double |
Examples
The following code illustrates how to get the standard width.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Text";
//Set standard width
worksheet.StandardWidth = 80;
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
}
ThreadedComments
Gets the threaded comments collection for a current worksheet.
Declaration
IThreadedComments ThreadedComments { get; }
Property Value
Type | Description |
---|---|
IThreadedComments | The IThreadedComments collection. |
Examples
The following code illustrates how to access ThreadedComments
property of IWorksheet
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Add threaded comment
IThreadedComment threadedComment = worksheet["C2"].AddThreadedComment("Hello","User");
//Get the threaded comments collection
IThreadedComments threadedComments = worksheet.ThreadedComments.
//Get the threaded comments count
int count = threadedComments.Count;
//Save and dispose
workbook.SaveAs("ThreadedComments.xlsx");
workbook.Close();
}
TopVisibleRow
Gets or sets the top visible row of the worksheet.
Declaration
int TopVisibleRow { get; set; }
Property Value
Type |
---|
System.Int32 |
Examples
The following code illustrates how to get the top visible row.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
int topVisibleRow = sheet.TopVisibleRow;
Type
Gets the worksheet type. Read-only ExcelSheetType.
Declaration
ExcelSheetType Type { get; }
Property Value
Type |
---|
ExcelSheetType |
UsedCells
Gets all not empty or accessed cells. Read-only.
Declaration
IRange[] UsedCells { get; }
Property Value
Type |
---|
IRange[] |
Remarks
WARNING: This property creates Range object for each cell in the worksheet and creates new array each time user calls to it. It can cause huge memory usage especially if called frequently.
Examples
The following code illustrates how to get the used cells range of a sheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IRange[] usedCellsRange = sheet.UsedCells;
UsedRange
Gets a Range object that represents the used range on the specified worksheet. Read-only.
Declaration
IRange UsedRange { get; }
Property Value
Type |
---|
IRange |
Examples
The following code illustrates how to get used range on the specified worksheet.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["B2"].Text = "Text";
worksheet["J5"].Text = "Text";
//Set Color
worksheet["J3"].CellStyle.ColorIndex = ExcelKnownColors.Red;
//Get used range
Console.Write(worksheet.UsedRange.AddressLocal);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//B2:J5
UsedRangeIncludesFormatting
Gets or sets whether used range should include cells with formatting.
Declaration
bool UsedRangeIncludesFormatting { get; set; }
Property Value
Type |
---|
System.Boolean |
Remarks
There are two different algorithms to create UsedRange object:
- 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).
- This property = false. In this case XlsIO tries to remove empty rows and columns from all sides to make UsedRange smaller.
Examples
The following code illustrates how to set UsedRangeIncludesFormatting
property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set text
worksheet["C2"].Text = "Text";
//Add format
worksheet["D3"].CellStyle.ColorIndex = ExcelKnownColors.Red;
//Set used range includes formatting
worksheet.UsedRangeIncludesFormatting = false;
//Get used range
Console.Write(worksheet.UsedRange.AddressLocal);
//Save and dispose
workbook.SaveAs("CellFormats.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//C2
UseRangesCache
Indicates whether all created range objects should be cached or not.
Declaration
bool UseRangesCache { get; set; }
Property Value
Type |
---|
System.Boolean |
VerticalSplit
Gets or sets the position of vertical split in the worksheet.
Declaration
int VerticalSplit { get; set; }
Property Value
Type |
---|
System.Int32 |
Remarks
Position of the vertical split (px, 0 = No vertical split): Unfrozen pane: Width of the left pane(s) (in twips = 1/20 of a point) Frozen pane: Number of visible columns in left pane(s)
View
Gets or sets the view setting of the worksheet.
Declaration
SheetView View { get; set; }
Property Value
Type |
---|
SheetView |
Examples
The following code illustrates how to set the view of the sheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.View = SheetView.PageLayout;
VPageBreaks
Gets a VPageBreaksCollection that represents the vertical page breaks on the sheet. Read-only.
Declaration
IVPageBreaks VPageBreaks { get; }
Property Value
Type |
---|
IVPageBreaks |
Zoom
Gets or sets the value that represents zoom factor of document. Value must be in range from 10 till 400.
Declaration
int Zoom { get; set; }
Property Value
Type |
---|
System.Int32 |
Examples
The following code illustrates how to set zoom level of the sheet.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Zoom = 200;
Methods
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();
ImportXml(Stream, Int32, Int32)
Imports XML data into a worksheet at the specified row and column from a given XML data stream.
Declaration
void ImportXml(Stream stream, int row, int column)
Parameters
Type | Name | Description |
---|---|---|
System.IO.Stream | stream | Stream data of the xml file. |
System.Int32 | row | Row where the data to be imported. |
System.Int32 | column | Column where the data to be imported. |
Remarks
The stream should be passed as file stream to bind the xml
ImportXml(String, Int32, Int32)
Import XML document with specified cell position to the worksheet using file path.
Declaration
void ImportXml(string filename, int row, int column)
Parameters
Type | Name | Description |
---|---|---|
System.String | filename | FilePath of the speciifed xml file. |
System.Int32 | row | Row where the data to be imported. |
System.Int32 | column | Column where the data to be imported. |
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 |
---|
RangeImpl.CellValueChangedEventHandler |
ExportDataTableEvent
Event to choose an action while exporting data from Excel to data table.
Declaration
event WorksheetImpl.ExportDataTableEventHandler ExportDataTableEvent
Event Type
Type |
---|
WorksheetImpl.ExportDataTableEventHandler |
MissingFunction
Event raised when an unknown function is encountered.
Declaration
event MissingFunctionEventHandler MissingFunction
Event Type
Type |
---|
MissingFunctionEventHandler |