public static Table GetRangeTable(this CellRange range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var sheet = range.Worksheet;
Table rangeTable = null;
foreach (var table in sheet.Tables)
{
if (range.IsIntersecting(table.Range))
{
rangeTable = table;
break;
}
}
if (rangeTable == null)
{
return(null);
}
return(rangeTable);
}
private void buttonRemoveRecord_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
CloseInplaceEditor();
Worksheet sheet = spreadsheetControl1.ActiveWorksheet;
CellRange selectedRange = spreadsheetControl1.Selection;
CellRange boundRange = sheet.DataBindings[0].Range;
// Verify that the selected cell range belongs to the data-bound range.
if (!boundRange.IsIntersecting(selectedRange) || selectedRange.TopRowIndex < boundRange.TopRowIndex)
{
MessageBox.Show("Select a record first!", "Remove Record", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Remove the topmost row of the selected cell range.
sheet.Rows.Remove(selectedRange.TopRowIndex);
}
private void spreadsheetControl1_RowsRemoving(object sender, RowsChangingEventArgs e)
{
Worksheet sheet = spreadsheetControl1.ActiveWorksheet;
CellRange rowRange = sheet.Range.FromLTRB(0, e.StartIndex, 16383, e.StartIndex + e.Count - 1);
CellRange boundRange = sheet.DataBindings[0].Range;
// If the rows to be removed belong to the data-bound range,
// display a dialog requesting the user to confirm the deletion of records.
if (boundRange.IsIntersecting(rowRange))
{
DialogResult result = MessageBox.Show("Want to delete the selected supplier(s)?", "Delete",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
applyChangesOnRowsRemoved = result == DialogResult.Yes;
e.Cancel = result == DialogResult.No;
return;
}
}
static void FreezePanes(SpreadsheetControl control)
{
//Access the active worksheet.
Worksheet worksheet = control.Document.Worksheets.ActiveWorksheet;
// Access the cell range that is currently visible.
CellRange visibleRange = control.VisibleRange;
// Access the active cell.
Cell activeCell = control.ActiveCell;
int rowOffset = activeCell.RowIndex - visibleRange.TopRowIndex - 1;
int columnOffset = activeCell.ColumnIndex - visibleRange.LeftColumnIndex - 1;
// If the active cell is outside the visible range of cells, no rows and columns are frozen.
if (!visibleRange.IsIntersecting(activeCell))
{
return;
}
if (activeCell.ColumnIndex == visibleRange.LeftColumnIndex)
{
// If the active cell matches the top left visible cell, no rows and columns are frozen.
if (activeCell.RowIndex == visibleRange.TopRowIndex)
{
return;
}
else
{
// Freeze visible rows above the active cell if it is located in the leftmost visible column.
worksheet.FreezeRows(rowOffset, visibleRange);
}
}
else if (activeCell.RowIndex == visibleRange.TopRowIndex)
{
// Freeze visible columns to the left of the active cell if it is located in the topmost visible row.
worksheet.FreezeColumns(columnOffset, visibleRange);
}
else
{
// Freeze both rows and columns above and to the left of the active cell.
worksheet.FreezePanes(rowOffset, columnOffset, visibleRange);
}
}
bool CheckCondition()
{
return(worksheet == workbook.Worksheets.ActiveWorksheet ? categoryColumn.IsIntersecting(activeCell) : false);
}
bool CanShowDateEdit(Cell activeCell)
{
return(worksheet == workbook.Worksheets.ActiveWorksheet ? dateColumn.IsIntersecting(activeCell) : false);
}
bool CanShowCheckBox(Cell activeCell)
{
return(worksheet == workbook.Worksheets.ActiveWorksheet ? discountColumn.IsIntersecting(activeCell) : false);
}
bool CanShowLookUp(Cell activeCell)
{
return(worksheet == workbook.Worksheets.ActiveWorksheet ? categoryColumn.IsIntersecting(activeCell) : false);
}
