Table of Contents

Interface IWorksheet

Namespace
Spire.Xls.Core
Assembly
Spire.XLS.dll
public interface IWorksheet : ITabSheet, IExcelApplication
Inherited Members

Properties

ActivePane

Identifier of pane with active cell cursor.

int ActivePane { get; set; }

Property Value

int

AllocatedRange

Returns a Range object that represents a cell or a range of cells.

IXLSRange AllocatedRange { get; }

Property Value

IXLSRange

AutoFilters

Returns collection of worksheet's autofilters. Read-only.

IAutoFilters AutoFilters { get; }

Property Value

IAutoFilters

Cells

Returns all used cells in the worksheet. Read-only.

IXLSRange[] Cells { get; }

Property Value

IXLSRange[]

CodeName

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

string CodeName { get; set; }

Property Value

string

Columns

For a Worksheet object, returns an array of Range objects that represents all the columns on the specified worksheet. Read-only Range object.

IXLSRange[] Columns { get; }

Property Value

IXLSRange[]

Comments

Comments collection.
The following code illustrates how to access the comments collection in the worksheet:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

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

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

//Save to file
workbook.SaveToFile("Comments.xlsx");
IComments Comments { get; }

Property Value

IComments

DefaultColumnWidth

Returns or sets the standard (default) width of all the columns in the worksheet. Read/write Double. The following code illustrates how to get the default column width:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get column width
Console.Write(worksheet.DefaultColumnWidth);

//Set default width
worksheet.DefaultColumnWidth = 40;

//Save to file
workbook.SaveToFile("DefaultColumnWidth.xlsx");
double DefaultColumnWidth { get; set; }

Property Value

double

DefaultRowHeight

Returns the standard (default) height of all the rows in the worksheet, in points. Read/write Double. The following code illustrates how to get the default row height:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Get row height
Console.Write(worksheet.DefaultRowHeight);

//Set default height
worksheet.DefaultRowHeight = 40;

//Save to file
workbook.SaveToFile("DefaultRowHeight.xlsx");
double DefaultRowHeight { get; set; }

Property Value

double

DisplayPageBreaks

True if page breaks (both automatic and manual) on the specified worksheet are displayed. Read / write Boolean.

bool DisplayPageBreaks { get; set; }

Property Value

bool

FirstVisibleColumn

Index to first visible column in right pane(s).

int FirstVisibleColumn { get; set; }

Property Value

int

FirstVisibleRow

Index to first visible row in bottom pane(s).

int FirstVisibleRow { get; set; }

Property Value

int

FormulasVisible 

bool FormulasVisible { get; set; }

Property Value

bool

GridLineColor

Gets / sets Grid line color. The following code illustrates how to set the grid line color:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Save to file
workbook.SaveToFile("GridLineColor.xlsx");
ExcelColors GridLineColor { get; set; }

Property Value

ExcelColors

GridLinesVisible

True if gridlines are visible; False otherwise. The following code illustrates how to set visibility for grid lines:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set grid line visibility
worksheet.GridLinesVisible = false;

//Save to file
workbook.SaveToFile("GridLinesVisible.xlsx");
bool GridLinesVisible { get; set; }

Property Value

bool

HPageBreaks

Returns an HPageBreaks collection that represents the horizontal page breaks on the sheet. Read-only.

IHPageBreaks HPageBreaks { get; }

Property Value

IHPageBreaks

HasOleObjects

Gets or sets a value indicating whether this instance is OLE object. The following code illustrates how to access the IListObjects collection in the worksheet to add a new IOleObject and check Ole Object:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet 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.Output will be true.
Console.Write(worksheet.HasOleObjects);

//Save to file
workbook.SaveToFile("HasOleObjects.xlsx");
bool HasOleObjects { get; }

Property Value

bool

true if this instance is OLE object; otherwise, false.

HorizontalSplit

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

int HorizontalSplit { get; set; }

Property Value

int

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)

Collection of all worksheet's hyperlinks.

IHyperLinks HyperLinks { get; }

Property Value

IHyperLinks

Index

Returns the index number of the object within the collection of similar objects. Read-only.

int Index { get; }

Property Value

int

IsDisplayZeros

True if zero values to be displayed False otherwise.

bool IsDisplayZeros { get; set; }

Property Value

bool

IsStringsPreserved

Indicates if all values in the workbook are preserved as strings.

bool IsStringsPreserved { get; set; }

Property Value

bool

this[int, int]

Gets / sets cell by row and index.

IXLSRange this[int row, int column] { get; }

Parameters

row int
column int

Property Value

IXLSRange

this[int, int, int, int]

Get cells range.

IXLSRange this[int row, int column, int lastRow, int lastColumn] { get; }

Parameters

row int
column int
lastRow int
lastColumn int

Property Value

IXLSRange

this[string]

Get cell range.

IXLSRange this[string name] { get; }

Parameters

name string

Property Value

IXLSRange

LeftVisibleColumn

Gets/sets left visible column of the worksheet. The following code illustrates how to set the left visible column:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set left visible column
worksheet.LeftVisibleColumn = 3;

//Get left visible column
Console.Write(worksheet.LeftVisibleColumn);

//Save to file
workbook.SaveToFile("LeftVisibleColumn.xlsx");
int LeftVisibleColumn { get; set; }

Property Value

int

ListObjects

Gets collection of all list objects in the worksheet.

IListObjects ListObjects { get; }

Property Value

IListObjects

MergedCells

Returns all merged ranges. Read-only. The following code illustrates how to get the merged ranges:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

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

//Get merged range count . Output will be 2
Console.Write(mergedRanges.Length);

//Save to file
workbook.SaveToFile("MergedCells.xlsx");
IXLSRange[] MergedCells { get; }

Property Value

IXLSRange[]

Names

For a Worksheet object, returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix). Read-only Names object.

INameRanges Names { get; }

Property Value

INameRanges

OleObjects

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet 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 to file
workbook.SaveToFile("OLEObjects.xlsx");
IOleObjects OleObjects { get; }

Property Value

IOleObjects

The OLE objects.

PageSetup

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

IPageSetup PageSetup { get; }

Property Value

IPageSetup

PivotTables

Returns pivot table collection containing all pivot tables in the worksheet. Read-only.

PivotTablesCollection PivotTables { get; }

Property Value

PivotTablesCollection

Range

Returns a Range object that represents the used range on the specified worksheet. Read-only.
The following code illustrates how to get used range on the specified worksheet:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
worksheet["J3"].Style.Color = Color.Red;

//Get used range . Output will be B2:J3
Console.Write(worksheet.Range.RangeAddressLocal);

//Save to file
workbook.SaveToFile("UsedRange.xlsx");
XlsRange Range { get; }

Property Value

XlsRange

RowColumnHeadersVisible

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

bool RowColumnHeadersVisible { get; set; }

Property Value

bool

Rows

For a Worksheet object, returns an array of Range objects that represents all the rows on the specified worksheet. Read-only Range object.

IXLSRange[] Rows { get; }

Property Value

IXLSRange[]

TopVisibleRow

Gets/sets top visible row of the worksheet. The following code illustrates how to set the top visible row:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set top visible row
worksheet.TopVisibleRow = 5;

//Get top visible row
Console.Write(worksheet.TopVisibleRow);

//Save to file
workbook.SaveToFile("TopVisibleRow.xlsx");
int TopVisibleRow { get; set; }

Property Value

int

Type

Returns or sets the worksheet type. Read-only ExcelSheetType.

ExcelSheetType Type { get; }

Property Value

ExcelSheetType

UseRangesCache

Indicates whether all created range objects should be cached. Default value is false.

bool UseRangesCache { get; set; }

Property Value

bool

VPageBreaks

Returns a VPageBreaks collection that represents the vertical page breaks on the sheet. Read-only.

IVPageBreaks VPageBreaks { get; }

Property Value

IVPageBreaks

VerticalSplit

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

int VerticalSplit { get; set; }

Property Value

int

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)

Zoom

Zoom factor of document. Value must be in range from 10 till 400. The following code illustrates how to set zoom level of the sheet:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set zoom
worksheet.Zoom = 200;

//Save to file
workbook.SaveToFile("Zoom.xlsx");
int Zoom { get; set; }

Property Value

int

Methods

AutoFitColumn(int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["A1"].Text = "Sample text in cell";

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

//Save to file
workbook.SaveToFile("AutoFitColumn.xlsx");
void AutoFitColumn(int columnIndex)

Parameters

columnIndex int

One-based column index.

AutoFitRow(int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Style
CellStyle style = workbook.Styles.Add("CustomStyle");
IFont font = style.Font;
font.Size = 18;
worksheet["C2"].Style = style;

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

//Save to file
workbook.SaveToFile("AutoFitRow.xlsx");
void AutoFitRow(int rowIndex)

Parameters

rowIndex int

One-based row index.

CheckExistence(int, int)

Indicates whether a cell was initialized or accessed by the user. The following code illustrates if the cells was initialized or accessed by the user:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet.Range["A1"].Text = "Hello";

//Check the cell.Output will be true.
Console.Write(worksheet.CheckExistence(1, 1));

//Save to file
workbook.SaveToFile("CheckExistence.xlsx");
bool CheckExistence(int iRow, int iColumn)

Parameters

iRow int

One-based row index of the cell.

iColumn int

One-based column index of the cell.

Returns

bool

Value indicating whether the cell was initialized or accessed by the user.

Clear()

Clears worksheet data. Removes all formatting and merges.

void Clear()

ClearData()

Clears worksheet. Only the data is removed from each cell.

void ClearData()

ColumnWidthToPixels(double)

Converts column width into pixels.

int ColumnWidthToPixels(double widthInChars)

Parameters

widthInChars double

Width in characters.

Returns

int

Width in pixels

CreateNamedRanges(string, string, bool) 

void CreateNamedRanges(string namedRange, string referRange, bool vertical)

Parameters

namedRange string
referRange string
vertical bool

DeleteColumn(int)

Removes specified column (with formulas update).

void DeleteColumn(int index)

Parameters

index int

One-based column index to remove.

DeleteRow(int)

Removes specified row (with formulas update).

void DeleteRow(int index)

Parameters

index int

One-based row index to remove.

GetBoolean(int, int)

Gets bool value from cell.

bool GetBoolean(int row, int column)

Parameters

row int

Represents row index.

column int

Represents column index.

Returns

bool

Returns found bool value. If cannot found returns false.

GetColumnWidthPixels(int)

Returns width in pixels from ColumnInfoRecord if there is corresponding ColumnInfoRecord or StandardWidth if not. The following code illustrates how to get the column width for a particular column:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet 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.GetColumnWidthPixels(1));

//Save to file
workbook.SaveToFile("UsedRange.xlsx");
int GetColumnWidthPixels(int Column)

Parameters

Column int

One-based index of the column.

Returns

int

Width in pixels of the specified column.

GetDefaultColumnStyle(int)

Returns default column style. The following code illustrates how to get default column style:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Get default style
CellStyle defaultStyle = worksheet.GetDefaultColumnStyle(2);

//Set color
defaultStyle.Color = Color.Blue;
worksheet.SetDefaultColumnStyle(3, defaultStyle);

//Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx");
IStyle GetDefaultColumnStyle(int iColumnIndex)

Parameters

iColumnIndex int

Column index.

Returns

IStyle

Default column style or null if style wasn't set.

GetDefaultRowStyle(int)

Returns default row style. The following code illustrates how to get default row style:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Get default style
CellStyle defaultStyle = worksheet.GetDefaultRowStyle(2);

//Set color
defaultStyle.Color = Color.Blue;
worksheet.SetDefaultRowStyle(3, defaultStyle);

//Save to file
workbook.SaveToFile("GetDefaultColumnStyle.xlsx");
IStyle GetDefaultRowStyle(int rowIndex)

Parameters

rowIndex int

Row index.

Returns

IStyle

Default row style or null if style wasn't set.

GetError(int, int)

Gets error value from cell.

string GetError(int row, int column)

Parameters

row int

Row index.

column int

Column index.

Returns

string

Returns error value or null.

GetFormula(int, int, bool)

Returns formula value corresponding to the cell.

string GetFormula(int row, int column, bool bR1C1)

Parameters

row int

One-based row index of the cell to get value from.

column int

One-based column index of the cell to get value from.

bR1C1 bool

Indicates whether R1C1 notation should be used.

Returns

string

Formula contained by the cell.

GetFormulaBoolValue(int, int)

Gets formula bool value from cell.

bool GetFormulaBoolValue(int row, int column)

Parameters

row int

Represents row index.

column int

Represents column index.

Returns

bool

Returns found bool value. If cannot found returns false.

GetFormulaErrorValue(int, int)

Gets formula error value from cell.

string GetFormulaErrorValue(int row, int column)

Parameters

row int

Row index.

column int

Column index.

Returns

string

Returns error value or null.

GetFormulaNumberValue(int, int)

Returns formula number value corresponding to the cell.

double GetFormulaNumberValue(int row, int column)

Parameters

row int

One-based row index of the cell to get value from.

column int

One-based column index of the cell to get value from.

Returns

double

Number contained by the cell.

GetFormulaStringValue(int, int)

Returns formula string value corresponding to the cell.

string GetFormulaStringValue(int row, int column)

Parameters

row int

One-based row index of the cell to get value from.

column int

One-based column index of the cell to get value from.

Returns

string

String contained by the cell.

GetNumber(int, int)

Returns number value corresponding to the cell.

double GetNumber(int row, int column)

Parameters

row int

One-based row index of the cell to get value from.

column int

One-based column index of the cell to get value from.

Returns

double

Number contained by the cell.

GetRowHeightPixels(int)

Returns height from RowRecord if there is a corresponding RowRecord. Otherwise returns StandardHeight. The following code illustrates how to get the row height for a particular row:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set text
worksheet["C2"].Text = "Sample text";
worksheet["C2"].Style.Font.Size = 18;

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

//Get row height
Console.WriteLine(worksheet.GetRowHeightPixels(2));

//Save to file
workbook.SaveToFile("UsedRange.xlsx");
int GetRowHeightPixels(int Row)

Parameters

Row int

One-bazed index of the row.

Returns

int

Height in pixels from RowRecord if there is corresponding RowRecord. Otherwise returns StandardHeight.

GetText(int, int)

Returns string value corresponding to the cell.

string GetText(int row, int column)

Parameters

row int

One-based row index of the cell to get value from.

column int

One-based column index of the cell to get value from.

Returns

string

String contained by the cell.

InsertArray(DateTime[], int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet 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") };

//Insert the DateTime Array to Sheet
worksheet.InsertArray(arrayDate, 1, 1, true);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(DateTime[] arrDateTime, int firstRow, int firstColumn, bool isVertical)

Parameters

arrDateTime DateTime[]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

isVertical bool

True if array should be imported vertically; False - horizontally.

Returns

int

Number of imported elements.

InsertArray(double[], int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Insert the double Array to Sheet
worksheet.InsertArray(arrayDouble, 1, 1, true);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(double[] arrDouble, int firstRow, int firstColumn, bool isVertical)

Parameters

arrDouble double[]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

isVertical bool

True if array should be imported vertically; False - horizontally.

Returns

int

Number of imported elements.

InsertArray(int[], int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Insert the integer Array to Sheet
worksheet.InsertArray(arrayInt, 1, 1, true);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(int[] arrInt, int firstRow, int firstColumn, bool isVertical)

Parameters

arrInt int[]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

isVertical bool

True if array should be imported vertically; False - horizontally.

Returns

int

Number of imported elements.

InsertArray(object[,], int, int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Insert the Object Array to Sheet
worksheet.InsertArray(arrayTwoDimen, 1, 1);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(object[,] arrObject, int firstRow, int firstColumn)

Parameters

arrObject object[,]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

Returns

int

Number of imported rows.

InsertArray(object[], int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Insert the Object Array to Sheet
worksheet.InsertArray(array, 1, 1, true);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(object[] arrObject, int firstRow, int firstColumn, bool isVertical)

Parameters

arrObject object[]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

isVertical bool

True if array should be imported vertically; False - horizontally.

Returns

int

Number of imported elements.

InsertArray(string[], int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Insert the string Array to Sheet
worksheet.InsertArray(arrayString, 1, 1, true);

//Save to file
workbook.SaveToFile(InsertArray.xlsx");
int InsertArray(string[] arrString, int firstRow, int firstColumn, bool isVertical)

Parameters

arrString string[]

Array to import.

firstRow int

Row of the first cell where array should be imported.

firstColumn int

Column of the first cell where array should be imported.

isVertical bool

True if array should be imported vertically; False - horizontally.

Returns

int

Number of imported elements.

InsertDataColumn(DataColumn, bool, int, int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Insert the DataColumn to worksheet
System.Data.DataColumn column = table.Columns[2];
worksheet.InsertDataColumn(column, true, 1, 1);

//Save to file
workbook.SaveToFile(InsertDataColumn.xlsx");
int InsertDataColumn(DataColumn dataColumn, bool isFieldNameShown, int firstRow, int firstColumn)

Parameters

dataColumn DataColumn

DataColumn with desired data.

isFieldNameShown bool

True if column name must also be imported.

firstRow int

Row of the first cell where DataTable should be imported.

firstColumn int

Column of the first cell where DataTable should be imported.

Returns

int

Number of imported rows.

InsertDataTable(DataTable, bool, int, int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Insert the DataTable to worksheet
worksheet.InsertDataTable(table, true, 1, 1);

//Save to file
workbook.SaveToFile(InsertDataTable.xlsx");
int InsertDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn)

Parameters

dataTable DataTable

DataTable with desired data.

isFieldNameShown bool

True if column names must also be imported.

firstRow int

Row of the first cell where DataTable should be imported.

firstColumn int

Column of the first cell where DataTable should be imported.

Returns

int

Number of imported rows.

InsertDataTable(DataTable, bool, int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Insert the DataTable to worksheet
worksheet.InsertDataTable(table, true, 1, 1 , true);

//Save to file
workbook.SaveToFile(InsertDataTable.xlsx");
int InsertDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, bool preserveTypes)

Parameters

dataTable DataTable

DataTable with desired data.

isFieldNameShown bool

True if column names must also be imported.

firstRow int

Row of the first cell where DataTable should be imported.

firstColumn int

Column of the first cell where DataTable should be imported.

preserveTypes bool

Indicates whether XlsIO should try to preserve types in DataTable, i.e. if it is set to False (default) and in DataTable we have in string column value that contains only numbers, it would be converted to number.

Returns

int

Number of imported rows.

InsertDataTable(DataTable, bool, int, int, int, int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Insert the DataTable to worksheet
worksheet.InsertDataTable(table, true, 1 , 1 , 2 , 2);

//Save to file
workbook.SaveToFile(InsertDataTable.xlsx");
int InsertDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns)

Parameters

dataTable DataTable

DataTable with desired data.

isFieldNameShown bool

True if column names must also be imported.

firstRow int

Row of the first cell where DataTable should be imported.

firstColumn int

Column of the first cell where DataTable should be imported.

maxRows int

Maximum number of rows to import.

maxColumns int

Maximum number of columns to import.

Returns

int

Number of imported rows.

InsertDataTable(DataTable, bool, int, int, int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Insert the DataTable to worksheet
worksheet.InsertDataTable(table, true, 1 , 1 , 2 , 2 , true);

//Save to file
workbook.SaveToFile(InsertDataTable.xlsx");
int InsertDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns, bool preserveTypes)

Parameters

dataTable DataTable

DataTable with desired data.

isFieldNameShown bool

True if column names must also be imported.

firstRow int

Row of the first cell where DataTable should be imported.

firstColumn int

Column of the first cell where DataTable should be imported.

maxRows int

Maximum number of rows to import.

maxColumns int

Maximum number of columns to import.

preserveTypes bool

Indicates whether XlsIO should try to preserve types in DataTable, i.e. if it is set to False (default) and in DataTable we have in string column value that contains only numbers, it would be converted to number.

Returns

int

Number of imported rows.

InsertDataView(DataView, bool, int, int)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Initialize dataview of datatable
System.Data.DataView view = table.DefaultView;

//Import data from DataView
worksheet.InsertDataView(view, true, 1, 1);

//Save to file
workbook.SaveToFile(InsertDataView.xlsx");
int InsertDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn)

Parameters

dataView DataView

DataView with desired data.

isFieldNameShown bool

TRUE if column names must also be imported.

firstRow int

Row of the first cell where DataView should be imported.

firstColumn int

Column of the first cell where DataView should be imported.

Returns

int

Number of imported rows.

InsertDataView(DataView, bool, int, int, bool)

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

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Initialize dataview of datatable
System.Data.DataView view = table.DefaultView;

//Import data from DataView
worksheet.InsertDataView(view, true, 1, 1 , true);

//Save to file
workbook.SaveToFile(InsertDataView.xlsx");
int InsertDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, bool bPreserveTypes)

Parameters

dataView DataView

DataView with desired data.

isFieldNameShown bool

TRUE if column names must also be imported.

firstRow int

Row of the first cell where DataView should be imported.

firstColumn int

Column of the first cell where DataView should be imported.

bPreserveTypes bool

Indicates whether to preserve column types.

Returns

int

Number of imported rows.

InsertDataView(DataView, bool, int, int, int, int)

Imports data from a DataView into worksheet. The following code illustrates how to Imports data from a DataView into a worksheet with the specified range:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Initialize dataview of datatable
System.Data.DataView view = table.DefaultView;

//Import data from DataView
worksheet.InsertDataView(view, true, 1, 1 , 2 , 2);

//Save to file
workbook.SaveToFile(InsertDataView.xlsx");
int InsertDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns)

Parameters

dataView DataView

DataView with desired data.

isFieldNameShown bool

TRUE if column names must also be imported.

firstRow int

Row of the first cell where DataView should be imported.

firstColumn int

Column of the first cell where DataView should be imported.

maxRows int

Maximum number of rows to import.

maxColumns int

Maximum number of columns to import.

Returns

int

Number of imported rows.

InsertDataView(DataView, bool, int, int, int, int, bool)

Imports data from a DataView into worksheet. The following code illustrates how to Imports data from a DataView into a worksheet with the specified range along with preserve type:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Create a DataTable
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));
table.Rows.Add(1, "Soap", "David");
table.Rows.Add(2, "Paste", "Sam");
table.Rows.Add(3, "Cream", "Christoff");

//Initialize dataview of datatable
System.Data.DataView view = table.DefaultView;

//Import data from DataView
worksheet.InsertDataView(view, true, 1, 1 , 2 , 2 , true);

//Save to file
workbook.SaveToFile(InsertDataView.xlsx");
int InsertDataView(DataView dataView, bool isFieldNameShown, int firstRow, int firstColumn, int maxRows, int maxColumns, bool bPreserveTypes)

Parameters

dataView DataView

DataView with desired data.

isFieldNameShown bool

TRUE if column names must also be imported.

firstRow int

Row of the first cell where DataView should be imported.

firstColumn int

Column of the first cell where DataView should be imported.

maxRows int

Maximum number of rows to import.

maxColumns int

Maximum number of columns to import.

bPreserveTypes bool

Indicates whether to preserve column types.

Returns

int

Number of imported rows

IsColumnVisible(int)

Method check is Column with specifed index visible to end user or not.

bool IsColumnVisible(int columnIndex)

Parameters

columnIndex int

Index of column.

Returns

bool

True - column is visible; otherwise False.

IsRowVisible(int)

Method check is Row with specifed index visible to user or not.

bool IsRowVisible(int rowIndex)

Parameters

rowIndex int

Index of row visibility of each must be checked.

Returns

bool

True - row is visible to user, otherwise False.

MoveWorksheet(int)

Moves worksheet.

void MoveWorksheet(int iNewIndex)

Parameters

iNewIndex int

New index of the worksheet.

PixelsToColumnWidth(double)

Converts pixels into column width (in characters).

double PixelsToColumnWidth(double pixels)

Parameters

pixels double

Width in pixels

Returns

double

Widht in characters.

Protect(string)

Protects worksheet's content with password. The following code illustrates how to protect the sheet except select lock/unlock cells:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Protects the first worksheet's content with password
worksheet.Protect("123456");

//Save to file
workbook.SaveToFile("Protect.xlsx");
void Protect(string password)

Parameters

password string

Password to protect with.

Remove()

Removes worksheet from parent worksheets collection.

void Remove()

RemovePanes()

Removes panes from a worksheet.

void RemovePanes()

Replace(string, DataColumn, bool)

Replaces specified string by data column values. The following code snippet illustrates how to replace the string value with data column:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by data column
string oldValue = "Find";
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];
worksheet.Replace(oldValue, dataColumn, true);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, DataColumn newValues, bool isFieldNamesShown)

Parameters

oldValue string

String value to replace.

newValues DataColumn

Data table with new data.

isFieldNamesShown bool

Indicates whether field name must be shown.

Replace(string, DataTable, bool)

Replaces specified string by data table values. The following code snippet illustrates how to replace the string value with data table:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by data table
string oldValue = "Find";
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Rows.Add(1);
worksheet.Replace(oldValue, table, true);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, DataTable newValues, bool isFieldNamesShown)

Parameters

oldValue string

String value to replace.

newValues DataTable

Data table with new data.

isFieldNamesShown bool

Indicates wheter field name must be shown.

Replace(string, DateTime)

Replaces specified string by specified value. The following code illustrates how to replace the string value with datetime:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by dateTime
string oldValue = "Find";
DateTime dateTime = DateTime.Now;
worksheet.Replace(oldValue, dateTime);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, DateTime newValue)

Parameters

oldValue string

String value to replace.

newValue DateTime

New value for the range with specified string.

Replace(string, double)

Replaces specified string by specified value. The following code snippet illustrates how to replace the string with double:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by double
string oldValue = "Ten";
worksheet.Replace(oldValue, 10.0);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, double newValue)

Parameters

oldValue string

String value to replace.

newValue double

New value for the range with specified string.

Replace(string, double[], bool)

Replaces specified string by data from array. The following code snippet illustrates how to replace the string with array of double values:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by array of double values
string oldValue = "Find";
double[] newValues = { 1.0, 2.0 };
worksheet.Replace(oldValue, newValues, true);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, double[] newValues, bool isVertical)

Parameters

oldValue string

String value to replace.

newValues double[]

Array of new values.

isVertical bool

Indicates whether array should be inserted vertically.

Replace(string, int[], bool)

Replaces specified string by data from array. The following code snippet illustrates how to replace the string with array of int values:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by array of int values
string oldValue = "Find";
int[] newValues = { 1, 2 };
worksheet.Replace(oldValue, newValues, true);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, int[] newValues, bool isVertical)

Parameters

oldValue string

String value to replace.

newValues int[]

Array of new values.

isVertical bool

Indicates whether array should be inserted vertically.

Replace(string, string)

Replaces specified string by specified value. The following code snippet illustrates how to replace the string with another string:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by newValue
string oldValue = "Find";
string newValue = "NewValue";
worksheet.Replace(oldValue, newValue);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, string newValue)

Parameters

oldValue string

String value to replace.

newValue string

New value for the range with specified string.

Replace(string, string[], bool)

Replaces specified string by data from array. The following code snippet illustrates how to replace the string with array of string values:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Replace the oldValue by array of string values
string oldValue = "Find";
string[] newValues = { "X values", "Y values" };
worksheet.Replace(oldValue, newValues , true);

//Save to file
workbook.SaveToFile("Replace.xlsx");
void Replace(string oldValue, string[] newValues, bool isVertical)

Parameters

oldValue string

String value to replace.

newValues string[]

Array of new values.

isVertical bool

Indicates whether array should be inserted vertically.

SaveToFile(string, string)

Save tabsheet using separator.

void SaveToFile(string fileName, string separator)

Parameters

fileName string

File to save.

separator string

Current seperator.

Examples

The following code illustrates how to saves the worksheet in a different file with separator: 

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

        //Save to file
        worksheet.SaveToFile("SaveToFile.csv" , ",");</code></pre>

SaveToHtml(Stream)

Save to HTML stream. The following code snippets illustrates how to save as html as stream:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Creat stream
Stream stream = new MemoryStream();

//Save to HTML stream
worksheet.SaveToHtml(stream);
void SaveToHtml(Stream stream)

Parameters

stream Stream

Stream object.

SaveToHtml(Stream, HTMLOptions)

Saves work sheet to HTML. The following code snippets illustrates how to save as html as stream with Save option:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Creat stream
Stream stream = new MemoryStream();

//Save to HTML stream
worksheet.SaveToHtml(stream, Spire.Xls.Core.Spreadsheet.HTMLOptions.Default);
void SaveToHtml(Stream stream, HTMLOptions saveOptions)

Parameters

stream Stream

Stream to save.

saveOptions HTMLOptions

Save Options.

SaveToHtml(string)

Saves worksheet with specified filename. The following code snippets illustrates how to save as html to the specified file name:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Save to HTML file
worksheet.SaveToHtml("Output.html");
void SaveToHtml(string filename)

Parameters

filename string

File to save.

SaveToHtml(string, HTMLOptions)

Saves as HTML. The following code snippets illustrates how to save as html to the specified file name and save option:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Save to HTML file
worksheet.SaveToHtml("Sample.html" , Spire.Xls.Core.Spreadsheet.HTMLOptions.Default);
void SaveToHtml(string filename, HTMLOptions saveOptions)

Parameters

filename string

The filename.

saveOptions HTMLOptions

The option.

SaveToStream(Stream, string)

Save tabsheet using separator. The following code illustrates how to saves the worksheet as stream with separator:

//Create worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

//Create stream
Stream stream = new MemoryStream();

//Save to stream
worksheet.SaveToStream(stream , ",");
void SaveToStream(Stream stream, string separator)

Parameters

stream Stream

Stream to save.

separator string

Current seperator.

SetBlank(int, int)

Sets blank in specified cell.

void SetBlank(int iRow, int iColumn)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

SetBoolean(int, int, bool)

Sets value in the specified cell.

void SetBoolean(int iRow, int iColumn, bool value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value bool

Value to set.

SetColumnWidthInPixels(int, int)

Sets column width in pixels. The following code illustrates how to set width for a column:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set column width
worksheet.SetColumnWidthInPixels(2, 160);

//Save to file
workbook.SaveToFile("SetColumnWidthInPixels.xlsx");
void SetColumnWidthInPixels(int columnIndex, int value)

Parameters

columnIndex int

One-based column index.

value int

Width to set.

SetDefaultColumnStyle(int, IStyle)

Sets by column index default style for column. The following code illustrates how to set the default style for a column:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Save to file
workbook.SaveToFile("SetDefaultColumnStyle.xlsx");
void SetDefaultColumnStyle(int iColumnIndex, IStyle defaultStyle)

Parameters

iColumnIndex int

Column index.

defaultStyle IStyle

Default style.

SetDefaultColumnStyle(int, int, IStyle)

Sets by column index default style for column. The following code illustrates how to set the default style for columns:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Save to file
workbook.SaveToFile("SetDefaultColumnStyle.xlsx");
void SetDefaultColumnStyle(int iStartColumnIndex, int iEndColumnIndex, IStyle defaultStyle)

Parameters

iStartColumnIndex int

Start column index.

iEndColumnIndex int

End column index.

defaultStyle IStyle

Default style.

SetDefaultRowStyle(int, IStyle)

Sets by column index default style for row. The following code illustrates how to set the default style for a row:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Save to file
workbook.SaveToFile("SetDefaultRowStyle.xlsx");
void SetDefaultRowStyle(int rowIndex, IStyle defaultStyle)

Parameters

rowIndex int

Row index.

defaultStyle IStyle

Default style.

SetDefaultRowStyle(int, int, IStyle)

Sets by column index default style for row. The following code illustrates how to set the default style for rows:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

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

//Set Color
style.Color = Color.Red;

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

//Save to file
workbook.SaveToFile("SetDefaultRowStyle.xlsx");
void SetDefaultRowStyle(int iStartRowIndex, int iEndRowIndex, IStyle defaultStyle)

Parameters

iStartRowIndex int

Start row index.

iEndRowIndex int

End row index.

defaultStyle IStyle

Default style.

SetError(int, int, string)

Sets error in the specified cell.

void SetError(int iRow, int iColumn, string value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value string

Error to set.

SetFormula(int, int, string)

Sets formula in the specified cell.

void SetFormula(int iRow, int iColumn, string value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value string

Formula to set.

SetFormulaBoolValue(int, int, bool)

Sets formula bool value.

void SetFormulaBoolValue(int iRow, int iColumn, bool value)

Parameters

iRow int

One based row index.

iColumn int

One based column index.

value bool

Represents formula bool value for set.

SetFormulaErrorValue(int, int, string)

Sets formula error value.

void SetFormulaErrorValue(int iRow, int iColumn, string value)

Parameters

iRow int

One based row index.

iColumn int

One based column index.

value string

Represents formula error value for set.

SetFormulaNumberValue(int, int, double)

Sets formula number value.

void SetFormulaNumberValue(int iRow, int iColumn, double value)

Parameters

iRow int

One based row index.

iColumn int

One based column index.

value double

Represents formula number value for set.

SetFormulaStringValue(int, int, string)

Sets formula string value.

void SetFormulaStringValue(int iRow, int iColumn, string value)

Parameters

iRow int

One based row index.

iColumn int

One based column index.

value string

Represents formula string value for set.

SetNumber(int, int, double)

Sets value in the specified cell.

void SetNumber(int iRow, int iColumn, double value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value double

Value to set.

SetRowHeightPixels(int, double)

Sets row height in pixels. The following code illustrates how to set height for a row:

//Create worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

//Set row height
worksheet.SetRowHeightPixels(3, 150);

//Save to file
workbook.SaveToFile("SetRowHeightPixels.xlsx");
void SetRowHeightPixels(int Row, double value)

Parameters

Row int

One-based row index to set height.

value double

Value in pixels to set.

SetText(int, int, string)

Sets text in the specified cell.

void SetText(int iRow, int iColumn, string value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value string

Text to set.

SetValue(int, int, string)

Sets value in the specified cell.

void SetValue(int iRow, int iColumn, string value)

Parameters

iRow int

One-based row index of the cell to set value.

iColumn int

One-based column index of the cell to set value.

value string

Value to set.

Unprotect(string)

Unprotects worksheet's content with password.

void Unprotect(string password)

Parameters

password string

Password to unprotect.

Events

CellValueChanged

Occurs when the value of a cell changes.

event XlsRange.CellValueChangedEventHandler CellValueChanged

Event Type

XlsRange.CellValueChangedEventHandler