GridEditor

public sealed class GridEditor

Namespace SharpCell.Grid · package SharpCell.Grid

Changes a workbook as people change it in Excel: typing into cells, Delete, pasting text copied from Excel, copying and moving cells, the fill handle, inserting and deleting rows and columns, column widths and row heights. Each change is one step to undo and redo, and the workbook is recalculated after it. What is typed is read as Excel reads it, through Cell.Input.

Constructors

GridEditor(Workbook)

public GridEditor(Workbook workbook)

An editor for a workbook, with nothing to undo.

Properties

CanRedo

public bool CanRedo { get; }

Whether there is an undone change to redo.

CanUndo

public bool CanUndo { get; }

Whether there is a change to undo.

Workbook

public Workbook Workbook { get; }

The workbook changed.

Methods

Clear(Worksheet, CellArea)

public GridEdit Clear(Worksheet sheet, CellArea area)

Clears the content of the cells in an area, as Delete does: values and formulas go, number formats and styles stay.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.

ClearHistory()

public void ClearHistory()

Forgets every change, so that none can be undone, as after a workbook is replaced.

Copy(Worksheet, CellArea, CellArea)

public GridEdit Copy(Worksheet sheet, CellArea from, CellArea to)

Copies cells, as copying and pasting them in Excel does: values, formulas, number formats and styles, with the formulas' relative references following the cells (see CellRange.CopyTo). An area that is a whole number of times as tall and as wide as the cells copied is filled by repeating them; any other gets one copy at its top-left cell. What lies beyond the sheet's edge is left out, and an area of more than a million cells, such as the whole sheet, gets one copy.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.
• NotSupportedException: The cells copied hold part of an array formula, or a formula SharpCell cannot read; no cell changes.

DeleteCells(Worksheet, CellArea, DeleteShift)

public GridEdit DeleteCells(Worksheet sheet, CellArea area, DeleteShift shift)

Deletes the cells of an area, as Excel's Delete with "Shift cells up" or "Shift cells left" does: the cells below (or on the right) move into their place, and references to the deleted cells become #REF! (see CellRange.Delete). Undoing gives back the cells and every formula and name as it was. An area of whole rows or columns deletes them.

Exceptions

• InvalidOperationException: The cells that move would split a merged area, a table or an array formula; nothing changes.
• NotSupportedException: A formula SharpCell cannot read would have to follow; nothing changes.

DeleteColumns(Worksheet, int, int)

public GridEdit DeleteColumns(Worksheet sheet, int column, int count)

Deletes columns; see GridEditor.DeleteRows.

Exceptions

• ArgumentOutOfRangeException: The columns are not all on the sheet.
• InvalidOperationException: The columns hold part of an array formula; nothing changes.
• NotSupportedException: The columns are part of a table, or a formula SharpCell cannot read would have to follow; nothing changes.

DeleteRows(Worksheet, int, int)

public GridEdit DeleteRows(Worksheet sheet, int row, int count)

Deletes rows, as Excel's Delete Sheet Rows does: the rows below move up, and references to the deleted cells become #REF! (see Worksheet.DeleteRows). Undoing gives back the rows with their cells, styles and heights, and every formula and name as it was.

Exceptions

• ArgumentOutOfRangeException: The rows are not all on the sheet.
• InvalidOperationException: The rows hold part of an array formula, or part of a table that cannot go; nothing changes.
• NotSupportedException: A formula SharpCell cannot read would have to follow; nothing changes.

Enter(Worksheet, CellArea, string)

public GridEdit Enter(Worksheet sheet, CellArea area, string input)

Types the same text into every cell of an area, as Enter does for one cell and Ctrl+Enter for a selection. Inside a merged area only its top-left cell takes the text.

Exceptions

• FormulaParseException: The text starts with = but is not a formula; no cell changes.
• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.

Fill(Worksheet, CellArea, CellArea)

public GridEdit Fill(Worksheet sheet, CellArea from, CellArea to)

Fills cells from others, as dragging Excel's fill handle does: to holds from and reaches past it on one side. Formulas are copied, their relative references following; numbers, dates, times, text ending in a number and the names of days and months continue as a series; anything else repeats. Number formats and styles repeat with the cells. When to is smaller than from, sharing its top-left cell, the cells left out are cleared, as dragging the handle back over them does.

Exceptions

• ArgumentException: to neither holds from and reaches past it on one side, nor lies inside it from its top-left cell.
• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.
• NotSupportedException: The cells hold part of an array formula, or a formula SharpCell cannot read; no cell changes.

FillArea(CellArea, CellPosition)

public static CellArea FillArea(CellArea from, CellPosition cell)

The cells that dragging the fill handle of from to a cell fills, as Excel picks them: past from, whole rows or columns of it up to the cell, on the side the cell is furthest beyond; back inside it, the cells up to the cell, the rest to be cleared. GridEditor.Fill takes the result.

FillDown(Worksheet, CellArea)

public GridEdit? FillDown(Worksheet sheet, CellArea area)

Copies the top row of an area into the rows below it, formulas following, as Ctrl+D does in Excel; an area of one row takes the row above it. Whole columns are filled down to the last row the sheet uses. Null, with nothing changed, for one row at the top of the sheet.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.
• NotSupportedException: The row copied holds part of an array formula, or a formula SharpCell cannot read; no cell changes.

FillRight(Worksheet, CellArea)

public GridEdit? FillRight(Worksheet sheet, CellArea area)

Copies the left column of an area into the columns right of it, as Ctrl+R does; see GridEditor.FillDown.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.
• NotSupportedException: The column copied holds part of an array formula, or a formula SharpCell cannot read; no cell changes.

FitColumnWidth(Worksheet, int, int, GridPainter)

public GridEdit FitColumnWidth(Worksheet sheet, int firstColumn, int lastColumn, GridPainter painter)

Fits columns to their content, as double-clicking a column header's edge does in Excel: each takes the width the painter finds for its widest text (see GridPainter.FitColumnWidth); a column with nothing to fit keeps its width.

Exceptions

• ArgumentOutOfRangeException: A column is outside the sheet.

FitRowHeight(Worksheet, int, int, GridPainter)

public GridEdit FitRowHeight(Worksheet sheet, int firstRow, int lastRow, GridPainter painter)

Fits rows to their content, as double-clicking a row header's edge does in Excel: each takes the height the painter finds for its tallest text (see GridPainter.FitRowHeight), and a row with nothing in it the sheet's default height.

Exceptions

• ArgumentOutOfRangeException: A row is outside the sheet.

InsertCells(Worksheet, CellArea, InsertShift)

public GridEdit InsertCells(Worksheet sheet, CellArea area, InsertShift shift)

Inserts empty cells in place of an area, as Excel's Insert with "Shift cells down" or "Shift cells right" does: only the cells beside the area move, and formulas follow them (see CellRange.Insert). The new cells take the styles and number formats of the cells above (or on the left), as in Excel. An area of whole rows or columns inserts them.

Exceptions

• InvalidOperationException: Content would be pushed off the sheet, or the cells that move would split a merged area, a table or an array formula; nothing changes.
• NotSupportedException: A formula SharpCell cannot read would have to follow; nothing changes.

InsertColumns(Worksheet, int, int)

public GridEdit InsertColumns(Worksheet sheet, int column, int count)

Inserts empty columns before column, which take the styles of the column on their left; see GridEditor.InsertRows.

Exceptions

• ArgumentOutOfRangeException: The columns would not fit on the sheet.
• InvalidOperationException: Content would be pushed past the last column, or the columns would split an array formula; nothing changes.
• NotSupportedException: The columns would go inside a table, or a formula SharpCell cannot read would have to follow; nothing changes.

InsertRows(Worksheet, int, int)

public GridEdit InsertRows(Worksheet sheet, int row, int count)

Inserts empty rows before row, as Excel's Insert Sheet Rows does: the rows below move down and formulas follow them (see Worksheet.InsertRows). The new rows take the styles and number formats of the row above, as in Excel.

Exceptions

• ArgumentOutOfRangeException: The rows would not fit on the sheet.
• InvalidOperationException: Content would be pushed past the last row, or the rows would split an array formula; nothing changes.
• NotSupportedException: A formula SharpCell cannot read would have to follow; nothing changes.

Move(Worksheet, CellArea, CellPosition)

public GridEdit Move(Worksheet sheet, CellArea from, CellPosition to)

Moves cells, as cutting and pasting them in Excel does: their values or formulas and number formats go to the area with its top-left cell at to, and the cells left behind are cleared. Formulas move as they are written; what lies beyond the sheet's edge is left out. Styles stay where they were.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.

Paste(Worksheet, CellPosition, string)

public GridEdit Paste(Worksheet sheet, CellPosition at, string text)

Pastes text copied from Excel or another program with its top-left cell at a cell: tabs between cells, line breaks between rows, cells with line breaks in double quotes. Each cell is read as typed; an empty one clears its cell. What lies beyond the sheet's edge is left out.

Exceptions

• InvalidOperationException: A cell is part of an array formula other than its top-left cell; no cell changes.

Redo()

public GridEdit? Redo()

Redoes the last undone change; null when there is none.

SetBorders(Worksheet, CellArea, BorderEdges, CellBorder)

public GridEdit SetBorders(Worksheet sheet, CellArea area, BorderEdges edges, CellBorder? border)

Puts a border on edges of an area, or takes it off with null, as Excel's Borders menu does: BorderEdges.Outside for Outside Borders, BorderEdges.All for All Borders, BorderEdges.All with null for No Border. The neighbouring cell's edge on the other side of a line goes, so the line looks as chosen.

Parameters

• sheet: The sheet.
• area: The cells.
• edges: Which edges.
• border: The line, such as new CellBorder(CellBorderStyle.Thin); null for none.

SetColumnHidden(Worksheet, int, int, bool)

public GridEdit SetColumnHidden(Worksheet sheet, int firstColumn, int lastColumn, bool hidden)

Hides or shows columns, as Excel's Hide and Unhide do; a hidden column keeps its width for when it shows again.

Exceptions

• ArgumentOutOfRangeException: A column is outside the sheet.

SetColumnWidth(Worksheet, int, int, double?)

public GridEdit SetColumnWidth(Worksheet sheet, int firstColumn, int lastColumn, double? width)

Gives columns a width, as dragging a column header's edge does in Excel: in the units of Worksheet.SetColumnWidth, or null for the sheet's default.

Exceptions

• ArgumentOutOfRangeException: A column is outside the sheet, or the width is outside 0 to 255.

SetNumberFormat(Worksheet, CellArea, string)

public GridEdit SetNumberFormat(Worksheet sheet, CellArea area, string format)

Gives every cell of an area a number format, as Excel's Number Format box does; see Cell.NumberFormat. Whole rows or columns change their cells up to the sheet's used range.

Exceptions

• ArgumentException: The format is not a number format Excel reads.

SetRowHeight(Worksheet, int, int, double?)

public GridEdit SetRowHeight(Worksheet sheet, int firstRow, int lastRow, double? height)

Gives rows a height in points, or null for the sheet's default; see GridEditor.SetColumnWidth.

Exceptions

• ArgumentOutOfRangeException: A row is outside the sheet, or the height is outside 0 to 409.

SetRowHidden(Worksheet, int, int, bool)

public GridEdit SetRowHidden(Worksheet sheet, int firstRow, int lastRow, bool hidden)

Hides or shows rows; see GridEditor.SetColumnHidden.

Exceptions

• ArgumentOutOfRangeException: A row is outside the sheet.

SetStyle(Worksheet, CellArea, Func<CellStyle, CellStyle>)

public GridEdit SetStyle(Worksheet sheet, CellArea area, Func<CellStyle, CellStyle> change)

Changes the style of every cell of an area, as a button of Excel's Home tab does: the function gets each cell's style and gives the new one, such as style => style with { Fill = color }. Whole rows or columns change their cells up to the sheet's used range.

Parameters

• sheet: The sheet.
• area: The cells.
• change: The new style of a cell from its old one.

ShortcutFormat(char, CultureInfo)

public static string? ShortcutFormat(char symbol, CultureInfo culture)

The number formats of Excel's Ctrl+Shift shortcuts, for a workbook's culture: ~ General, ! two decimals with thousands separators, $ currency, % percent, ^ scientific, # a date with day, month and year, @ a time with hours and minutes.

Parameters

• symbol: The shortcut's character.
• culture: The workbook's culture, for the currency symbol.

Returns: The format code; null for another character.

Undo()

public GridEdit? Undo()

Undoes the last change; null when there is none.