Cells

Cells class

Encapsulates a collection of cell relevant objects, such as Cell, Row, …etc.

public class Cells : IDisposable, IEnumerable

Properties

Name Description
Columns { get; } Gets the collection of Column objects that represents the individual columns in this worksheet.
Count { get; } Gets the total count of instantiated Cell objects.
CountLarge { get; } Gets the total count of instantiated Cell objects.
FirstCell { get; } Gets the first cell in this worksheet.
IsDefaultRowHeightMatched { get; set; } Indicates that row height and default font height matches
IsDefaultRowHidden { get; set; } Indicates whether the row is default hidden.
Item { get; } Gets Cell item within the worksheet (3 indexers)
LastCell { get; } Gets the last cell in this worksheet.
MaxColumn { get; } Minimum column index of those cells that have been instantiated in the collection(does not include the column where style is defined for the whole column but no cell has been instantiated in it).
MaxDataColumn { get; } Maximum column index of cell which contains data.
MaxDataRow { get; } Maximum row index of cell which contains data.
MaxDisplayRange { get; } Gets the max range which includes data, merged cells and shapes.
MaxRow { get; } Maximum row index of cell which contains data or style.
MemorySetting { get; set; } Gets or sets the memory usage option for this cells.
MergedCells { get; } Gets the collection of merged cells.
MinColumn { get; } Minimum column index of those cells that have been instantiated in the collection(does not include the column where style is defined for the whole column but no cell has been instantiated in it).
MinDataColumn { get; } Minimum column index of cell which contains data.
MinDataRow { get; } Minimum row index of cell which contains data.
MinRow { get; } Minimum row index of cell which contains data or style.
MultiThreadReading { get; set; } Gets or sets whether the cells data model should support Multi-Thread reading. Default value of this property is false.
OdsCellFields { get; } Gets the list of fields of ods.
PreserveString { get; set; } Gets or sets a value indicating whether all worksheet values are preserved as strings. Default is false.
Ranges { get; } Gets the collection of Range objects created at run time.
Rows { get; } Gets the collection of Row objects that represents the individual rows in this worksheet.
StandardHeight { get; set; } Gets or sets the default row height in this worksheet, in unit of points.
StandardHeightInch { get; set; } Gets or sets the default row height in this worksheet, in unit of inches.
StandardHeightPixels { get; set; } Gets or sets the default row height in this worksheet, in unit of pixels.
StandardWidth { get; set; } Gets or sets the default column width in the worksheet, in unit of characters.
StandardWidthInch { get; set; } Gets or sets the default column width in the worksheet, in unit of inches.
StandardWidthPixels { get; set; } Gets or sets the default column width in the worksheet, in unit of pixels.
Style { get; set; } Gets and sets the default style.

Methods

Name Description
AddRange(Range) Adds a range object reference to cells
ApplyColumnStyle(int, Style, StyleFlag) Applies formats for a whole column.
ApplyRowStyle(int, Style, StyleFlag) Applies formats for a whole row.
ApplyStyle(Style, StyleFlag) Applies formats for a whole worksheet.
CheckCell(int, int) Gets the Cell element or null at the specified cell row index and column index.
CheckColumn(int) Gets the Column element or null at the specified column index.
CheckRow(int) Gets the Row element or at the specified cell row index.
Clear() Clears all cell and row objects.
ClearContents(CellArea) Clears contents of a range.
ClearContents(int, int, int, int) Clears contents of a range.
ClearFormats(CellArea) Clears formatting of a range.
ClearFormats(int, int, int, int) Clears formatting of a range.
ClearMergedCells() Clears all merged ranges.
ClearRange(CellArea) Clears contents and formatting of a range.
ClearRange(int, int, int, int) Clears contents and formatting of a range.
ConvertStringToNumericValue() Converts string data in cells to numeric value if possible.
CopyColumn(Cells, int, int) Copies data and formats of a whole column.
CopyColumns(Cells, int, int, int) Copies data and formats of a whole column.
CopyColumns(Cells, int, int, int, int) Copies data and formats of the whole columns.
CopyColumns(Cells, int, int, int, PasteOptions) Copies data and formats of a whole column.
CopyRow(Cells, int, int) Copies data and formats of a whole row.
CopyRows(Cells, int, int, int) Copies data and formats of some whole rows.
CopyRows(Cells, int, int, int, CopyOptions) Copies data and formats of some whole rows.
CopyRows(Cells, int, int, int, CopyOptions, PasteOptions) Copies data and formats of some whole rows.
CreateRange(string) Creates a Range object from an address of the range.
CreateRange(string, string) Creates a Range object from a range of cells.
CreateRange(int, int, bool) Creates a Range object from rows of cells or columns of cells.
CreateRange(int, int, int, int) Creates a Range object from a range of cells.
DeleteBlankColumns() Delete all blank columns which do not contain any data.
DeleteBlankColumns(DeleteOptions) Delete all blank columns which do not contain any data.
DeleteBlankRows() Delete all blank rows which do not contain any data.
DeleteBlankRows(DeleteOptions) Delete all blank rows which do not contain any data.
DeleteColumn(int) Deletes a column.
DeleteColumn(int, bool) Deletes a column.
DeleteColumns(int, int, bool) Deletes several columns.
DeleteRange(int, int, int, int, ShiftType) Deletes a range of cells and shift cells according to the shift option.
DeleteRow(int) Deletes a row.
DeleteRows(int, int) Deletes several rows.
DeleteRows(int, int, bool) Deletes multiple rows in the worksheet.
Dispose() Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
EndCellInColumn(short) Gets the last cell in this column.
EndCellInColumn(int, int, short, short) Gets the last cell with maximum column index in this range.
EndCellInRow(int) Gets the last cell in this row.
EndCellInRow(int, int, int, int) Gets the last cell with maximum row index in this range.
ExportArray(int, int, int, int) Exports data in the Cells collection to a two-dimension array object.
ExportDataTable(int, int, int, int) Exports data in the Cells collection to a DataTable object.
ExportDataTable(int, int, int, int, bool) Exports data in the Cells collection to a DataTable object.
ExportDataTable(int, int, int, int, ExportTableOptions) Exports data in the Cells collection to a DataTable object.
ExportDataTableAsString(int, int, int, int) Exports data in the Cells collection to a DataTable object.
ExportDataTableAsString(int, int, int, int, bool) Exports data in the Cells collection to a DataTable object.
ExportTypeArray(int, int, int, int) Exports cell value type in the Cells collection to a two-dimension array object.
Find(object, Cell) Finds the cell containing with the input object.
Find(object, Cell, FindOptions) Finds the cell containing with the input object.
FindFormula(string, Cell) Finds the cell with the input string.
FindFormulaContains(string, Cell) Finds the cell with formula which contains the input string.
GetCell(int, int) Gets the Cell element or null at the specified cell row index and column index.
GetCellStyle(int, int) Get the style of given cell.
GetColumnWidth(int) Gets the width of the specified column in normal view
GetColumnWidthInch(int) Gets the width of the specified column in normal view, in units of inches.
GetColumnWidthPixel(int) Gets the width of the specified column in normal view, in units of pixel.
GetDependents(bool, int, int) Get all cells which refer to the specific cell.
GetEnumerator() Gets the cells enumerator.
GetGroupedColumnOutlineLevel(int) Gets the outline level (zero-based) of the column.
GetGroupedRowOutlineLevel(int) Gets the outline level (zero-based) of the row.
GetLastDataRow(int) Gets the last row index of cell which contains data in the specified column.
GetMaxGroupedColumnOutlineLevel() Gets the max grouped column outline level (zero-based).
GetMaxGroupedRowOutlineLevel() Gets the max grouped row outline level (zero-based).
GetRow(int) Gets the Row element at the specified cell row index.
GetRowEnumerator() Gets the rows enumerator.
GetRowHeight(int) Gets the height of a specified row.
GetRowHeightInch(int) Gets the height of a specified row in unit of inches.
GetRowHeightPixel(int) Gets the height of a specified row in unit of pixel.
GetRowOriginalHeightPoint(int) Gets original row’s height in unit of point if the row is hidden
GetViewColumnWidthPixel(int) Get the width in different view type.
GetViewRowHeight(int) Gets the height of a specified row.
GetViewRowHeightInch(int) Gets the height of a specified row in unit of inches.
GroupColumns(int, int) Groups columns.
GroupColumns(int, int, bool) Groups columns.
GroupRows(int, int) Groups rows.
GroupRows(int, int, bool) Groups rows.
HideColumn(int) Hides a column.
HideColumns(int, int) Hide multiple columns.
HideGroupDetail(bool, int) Collapses the grouped rows/columns.
HideRow(int) Hides a row.
HideRows(int, int) Hides multiple rows.
Import(IDataReader, int, int, ImportTableOptions) Import data from data view.
ImportArray(double[], int, int)
ImportArray(int[], int, int)
ImportArray(string[], int, int)
ImportArray(double[], int, int, bool) Imports an array of double into a worksheet.
ImportArray(int[], int, int, bool) Imports an array of integer into a worksheet.
ImportArray(string[], int, int, bool) Imports an array of string into a worksheet.
ImportArrayList(ArrayList, int, int, bool) Imports an arraylist of data into a worksheet.
ImportCSV(Stream, TxtLoadOptions, int, int) Import a CSV file to the cells.
ImportCSV(string, TxtLoadOptions, int, int) Import a CSV file to the cells.
ImportCSV(Stream, string, bool, int, int) Import a CSV file to the cells.
ImportCSV(string, string, bool, int, int) Import a CSV file to the cells.
ImportCustomObjects(ICollection, int, int, ImportTableOptions) Imports custom objects.
ImportCustomObjects(ICollection, string[], bool, int, int, int, bool, string, bool) Imports custom objects.
ImportData(IDataReader, int, int) Imports data from a IDataReader object.
ImportData(DataTable, int, int, ImportTableOptions) Import data from custom data table.
ImportData(DataView, int, int, ImportTableOptions) Import data from data view.
ImportData(ICellsDataTable, int, int, ImportTableOptions) Import data from custom data table.
ImportData(IDataReader, int, int, ImportTableOptions) Imports data from a IDataReader object.
ImportDataGrid(DataGrid, int, int, bool) Imports a DataGrid into a worksheet.
ImportDataGrid(DataGrid, int, int, int, int, bool) Imports a DataGrid into a worksheet.
ImportDataGrid(DataGrid, int, int, int, int, bool, bool) Imports a DataGrid into a worksheet.
ImportDataGridAsString(DataGrid, int, int, bool) Imports a DataGrid into a worksheet. This method doesn’t try to convert text into numeric values.
ImportDataReader(OleDbDataReader, int, int, bool) Imports data from a OleDbDataReader object.
ImportDataReader(SqlDataReader, int, int, bool) Imports data from a SqlDataReader object.
ImportDataReader(OleDbDataReader, bool, int, int, bool) Imports data from a OleDbDataReader object.
ImportDataReader(SqlDataReader, bool, int, int, bool) Imports data from a SqlDataReader object.
ImportDataReader(IDataReader, bool, int, int, bool, string, bool) Imports data from a IDataReader object.
ImportDataReader(OleDbDataReader, bool, int, int, bool, string, bool) Imports data from a OleDbDataReader object.
ImportDataReader(SqlDataReader, bool, int, int, bool, string, bool) Imports data from a SqlDataReader object.
ImportDataRow(DataRow, int, int) Imports a DataRow into the Excel file.
ImportDataTable(DataTable, bool, string) Imports a DataTable object into a worksheet.
ImportDataTable(DataTable, bool, int, int) Imports a DataTable object into a worksheet.
ImportDataTable(DataTable, bool, int, int, bool) Imports a DataTable object into a worksheet.
ImportDataTable(DataTable, bool, int, int, bool, bool) Imports a DataTable object into a worksheet.
ImportDataTable(DataTable, bool, int, int, int, int) Imports a DataTable into a worksheet.
ImportDataTable(DataTable, bool, int, int, int, int, bool) Imports a DataTable into a worksheet.
ImportDataTable(DataTable, bool, int, int, int, int, bool, string) Imports a DataTable into a worksheet.
ImportDataTable(DataTable, bool, int, int, int, int, bool, string, bool) Imports a DataTable into a worksheet.
ImportDataView(DataView, int, int) Imports a DataView into a worksheet.
ImportDataView(DataView, int, int, bool)
ImportDataView(DataView, bool, int, int, bool) Imports a DataView into a worksheet.
ImportDataView(DataView, int, int, int, int) Imports a DataView into a worksheet.
ImportDataView(DataView, int, int, int, int, bool) Imports a DataView into a worksheet.
ImportDataView(DataView, bool, int, int, int, int, bool) Imports a DataView into a worksheet.
ImportDataView(DataView, bool, int, int, int, int, bool, string) Imports a DataView into a worksheet.
ImportFormulaArray(string[], int, int, bool) Imports an array of formula into a worksheet.
ImportFromDataReader(OleDbDataReader, int, int, bool) Imports data from a OleDbDataReader object.
ImportFromDataReader(SqlDataReader, int, int, bool) Imports data from a SqlDataReader object.
ImportFromDataReader(OleDbDataReader, bool, int, int, bool) Imports data from a OleDbDataReader object.
ImportFromDataReader(SqlDataReader, bool, int, int, bool) Imports data from a SqlDataReader object.
ImportFromDataReader(OleDbDataReader, bool, int, int, bool, string, bool) Imports data from a OleDbDataReader object.
ImportFromDataReader(SqlDataReader, bool, int, int, bool, string, bool) Imports data from a SqlDataReader object.
ImportGridView(GridView, int, int, ImportTableOptions) Imports a grid view to this cells.
ImportObjectArray(object[], int, int, bool) Imports an array of data into a worksheet.
ImportObjectArray(object[], int, int, bool, int) Imports an array of data into a worksheet.
ImportTwoDimensionArray(object[], int, int)
ImportTwoDimensionArray(object[], int, int, bool)
ImportTwoDimensionArray(object[], object[], int, int, bool)
ImportTwoDimensionArray(object[], object[], int, int, TxtLoadOptions)
InsertColumn(int) Inserts a new column into the worksheet.
InsertColumn(int, bool) Inserts a new column into the worksheet.
InsertColumns(int, int) Inserts some columns into the worksheet.
InsertColumns(int, int, bool) Inserts some columns into the worksheet.
InsertCutCells(Range, int, int, ShiftType) Insert cut range.
InsertRange(CellArea, ShiftType) Inserts a range of cells and shift cells according to the shift option.
InsertRange(CellArea, int, ShiftType) Inserts a range of cells and shift cells according to the shift option.
InsertRange(CellArea, int, ShiftType, bool) Inserts a range of cells and shift cells according to the shift option.
InsertRow(int) Inserts a new row into the worksheet.
InsertRows(int, int) Inserts multiple rows into the worksheet.
InsertRows(int, int, bool) Inserts multiple rows into the worksheet.
InsertRows(int, int, InsertOptions) Inserts multiple rows into the worksheet.
IsBlankColumn(int) Checks whether given column is blank(does not contain any data).
IsColumnHidden(int) Checks whether a column at given index is hidden.
IsDeletingRangeEnabled(int, int, int, int) Check whether the range could be deleted.
IsRowHidden(int) Checks whether a row at given index is hidden.
LinkToXmlMap(string, int, int, string) Link to a xml map.
Merge(int, int, int, int) Merges a specified range of cells into a single cell.
Merge(int, int, int, int, bool) Merges a specified range of cells into a single cell.
Merge(int, int, int, int, bool, bool) Merges a specified range of cells into a single cell.
MoveRange(CellArea, int, int) Moves the range.
RemoveDuplicates() Removes duplicate rows in the sheet.
RemoveDuplicates(int, int, int, int) Removes duplicate values in the range.
RemoveDuplicates(int, int, int, int, bool, int[]) Removes duplicate data of the range.
RemoveFormulas() Removes all formula and replaces with the value of the formula.
RetrieveSubtotalSetting(CellArea) Retrieves subtotals setting of the range.
SetColumnWidth(int, double) Sets the width of the specified column in normal view.
SetColumnWidthInch(int, double) Sets column width in unit of inches in normal view.
SetColumnWidthPixel(int, int) Sets column width in unit of pixels in normal view.
SetRowHeight(int, double) Sets the height of the specified row.
SetRowHeightInch(int, double) Sets row height in unit of inches.
SetRowHeightPixel(int, int) Sets row height in unit of pixels.
SetViewColumnWidthPixel(int, int) Sets the width of the column in different view.
ShowGroupDetail(bool, int) Expands the grouped rows/columns.
Subtotal(CellArea, int, ConsolidationFunction, int[]) Creates subtotals for the range.
Subtotal(CellArea, int, ConsolidationFunction, int[], bool, bool, bool) Creates subtotals for the range.
TextToColumns(int, int, int, TxtLoadOptions) Splits the text in the column to columns.
UngroupColumns(int, int) Ungroups columns.
UngroupRows(int, int) Ungroups rows.
UngroupRows(int, int, bool) Ungroups rows.
UnhideColumn(int, double) Unhides a column
UnhideColumns(int, int, double) Unhide multiple columns.
UnhideRow(int, double) Unhides a row.
UnhideRows(int, int, double) Unhides the hidden rows.
UnMerge(int, int, int, int) Unmerges a specified range of merged cells.

Examples


[C#]

Workbook excel = new Workbook();
Cells cells = excel.Worksheets[0].Cells;

//Set default row height
cells.StandardHeight = 20;
//Set row height
cells.SetRowHeight(2, 20.5);

//Set default colum width
cells.StandardWidth = 15;
//Set column width
cells.SetColumnWidth(3, 12.57);

//Merge cells
cells.Merge(5, 4, 2, 2);

//Put values to cells
cells[0, 0].PutValue(true);
cells[0, 1].PutValue(1);
cells[0, 2].PutValue("abc");

//Export data
object[,] arr = cells.ExportArray(0, 0, 10, 10);

[Visual Basic]

Dim excel as Workbook = new Workbook()
Dim cells as Cells = excel.Worksheets(0).Cells

'Set default row height
cells.StandardHeight = 20
'Set row height
cells.SetRowHeight(2, 20.5)

'Set default colum width
cells.StandardWidth = 15
'Set column width
cells.SetColumnWidth(3, 12.57)

'Merge cells
cells.Merge(5, 4, 2, 2)

'Export data
Dim outDataTable as DataTable = cells.ExportDataTable(12, 12, 10, 10)

See Also