private void FillDefaultData(ExtendedDataGrid exGrid)
{
DataTable dataTable = new DataTable("DataTable" + this.NumberOfSheets);
int rowsNum = 17;
int colsNum = 13;
for (int i = 0; i < colsNum; i++)
{
dataTable.Columns.Add(new DataColumn(SSColumns.ToString(i), typeof(SpreadsheetCell)));
}
for (int i = 0; i < rowsNum; i++)
{
var row = dataTable.NewRow();
dataTable.Rows.Add(row);
for (int j = 0; j < colsNum; j++)
{
string cellName = SSColumns.ToString(j) + i;
row[j] = new SpreadsheetCell(string.Empty, dataTable, new SpreadsheetCellCustomInfo(new CellName(cellName, i, j)));
}
}
this.DataTables.Add(dataTable);
exGrid.ItemsSource = dataTable.DefaultView;
}
public static void FillAttributesForSpreedsheetView(this ExtendedDataGrid dataGrid)
{
// MUST set EnableRowVirtualization to false for correct row numbers showing
// and EnableColumnVirtualization to false for correct work
dataGrid.EnableRowVirtualization = false;
dataGrid.EnableColumnVirtualization = false;
// Prevent unnecessary user actions
dataGrid.CanUserReorderColumns = false;
dataGrid.CanUserReorderRows = false;
dataGrid.CanUserAddRows = false;
dataGrid.CanUserDeleteRows = false;
dataGrid.AllowUserToCopy = false;
// Some dark theming
dataGrid.Theme = ExtendedDataGrid.Themes.LiveExplorer;
// Select only one cell per click, or row if clicked on row header
dataGrid.SelectionUnit = DataGridSelectionUnit.CellOrRowHeader;
//dataGrid.CanUserSortColumns = false;
// Columns and rows default size
dataGrid.MinColumnWidth = 50;
dataGrid.RowHeaderWidth = 30;
dataGrid.MinRowHeight = 25;
// Hide annoying label
dataGrid.GroupByControlVisibility = Visibility.Collapsed;
}
private void FillDefaultData(ExtendedDataGrid exGrid)
{
DataTable dataTable = new DataTable("DataTable" + this.NumberOfSheets);
int rowsNum = 17;
int colsNum = 13;
for (int i = 0; i < colsNum; i++)
{
dataTable.Columns.Add(new DataColumn(SSColumns.ToString(i), typeof(SpreadsheetCell)));
}
for (int i = 0; i < rowsNum; i++)
{
var row = dataTable.NewRow();
dataTable.Rows.Add(row);
for (int j = 0; j < colsNum; j++)
{
string cellName = SSColumns.ToString(j) + i;
row[j] = new SpreadsheetCell(string.Empty, dataTable, new SpreadsheetCellCustomInfo(new CellName(cellName, i, j)));
}
}
this.DataTables.Add(dataTable);
exGrid.ItemsSource = dataTable.DefaultView;
}
/// <summary>
/// Generates tabitem, datagrid, and binds table to datagrid
/// </summary>
/// <param name="bindTable"></param>
private void GenerateSheet(DataTable bindTable = null)
{
TabItem sheetTabItem = new TabItem {
Header = "Sheet" + this.NumberOfSheets, Style = (Style)FindResource("BlueAndOrange")
};
this.WorkspaceTabControl.Items.Add(sheetTabItem);
ExtendedDataGrid dataGrid = new ExtendedDataGrid {
Name = "WorkDataTable" + this.NumberOfSheets
};
this.CreateSheet(dataGrid, sheetTabItem, bindTable);
}
public static DataGridCell TryToFindGridCell(this ExtendedDataGrid dataGrid, DataGridCellInfo cellInfo)
{
DataGridCell result = null;
DataGridRow row = (DataGridRow)dataGrid.ItemContainerGenerator.ContainerFromItem(cellInfo.Item);
if (row != null)
{
int columnIndex = dataGrid.Columns.IndexOf(cellInfo.Column);
if (columnIndex > -1)
{
DataGridCellsPresenter presenter = GetVisualChild <DataGridCellsPresenter>(row);
result = presenter.ItemContainerGenerator.ContainerFromIndex(columnIndex) as DataGridCell;
}
}
return(result);
}
/// <summary>
/// Resize table by creating new and copying old values
/// </summary>
/// <param name="exGrid"></param>
/// <param name="oldDataTable"></param>
/// <param name="rowsNum"></param>
/// <param name="colsNum"></param>
private void ResizeTable(ExtendedDataGrid exGrid, DataTable oldDataTable, int rowsNum, int colsNum)
{
int boundRows = oldDataTable.Rows.Count < rowsNum ? oldDataTable.Rows.Count : rowsNum;
int boundCols = oldDataTable.Columns.Count < colsNum ? oldDataTable.Columns.Count : colsNum;
DataTable newDataTable = new DataTable(oldDataTable.TableName);
for (int i = 0; i < colsNum; i++)
{
newDataTable.Columns.Add(new DataColumn(SSColumns.ToString(i), typeof(SpreadsheetCell)));
}
for (int i = 0; i < rowsNum; i++)
{
var row = newDataTable.NewRow();
newDataTable.Rows.Add(row);
for (int j = 0; j < colsNum; j++)
{
string cellName = SSColumns.ToString(j) + i;
row[j] = new SpreadsheetCell(string.Empty, newDataTable, new SpreadsheetCellCustomInfo(new CellName(cellName, i, j)));
}
}
// copy data
for (int i = 0; i < boundRows; i++)
{
for (int j = 0; j < boundCols; j++)
{
SpreadsheetCell cell = newDataTable.GetSpreadsheetCell(i, j);
SpreadsheetCell oldCell = oldDataTable.GetSpreadsheetCell(i, j);
cell.Content = oldCell.Content;
cell.Tag = oldCell.Tag;
}
}
this.DataTables[this.CurrentSheetNumber] = newDataTable;
// VERY VERY DIRTY HACK because nulling throws exception, dunno why
try
{
exGrid.ItemsSource = null;
}
catch { }
exGrid.ItemsSource = newDataTable.DefaultView;
}
/// <summary>
/// Displays additional info: current cell name and current cell formula
/// </summary>
/// <param name="dataGrid"></param>
private void DisplayAdditionalInfo(ExtendedDataGrid dataGrid)
{
var currentCellInfo = dataGrid.CurrentCell;
var currentCell = dataGrid.TryToFindGridCell(currentCellInfo);
var contentPresenter = currentCell.Content as ContentPresenter;
if (contentPresenter != null)
{
var spreadsheetCell = contentPresenter.Content as SpreadsheetCell;
if (spreadsheetCell != null)
{
var cellInfo = spreadsheetCell.Tag;
this.CurrentCellFormulaTextBox.Text = cellInfo.Formula;
this.CurrentCellCoordinatesTextBox.Text = cellInfo.CellName.FullName;
}
}
}
/// <summary>
/// Adds event handlers for datagrid and binds table to datagrid
/// Also increases number of sheets
/// </summary>
/// <param name="dataGrid"></param>
/// <param name="sheetTabItem"></param>
/// <param name="bindTable"></param>
private void CreateSheet(ExtendedDataGrid dataGrid, TabItem sheetTabItem, DataTable bindTable = null)
{
dataGrid.FillAttributesForSpreedsheetView();
dataGrid.SelectedCellsChanged += CoordinatesOfSelectedCells_OnSelectedCellsChanged;
dataGrid.LoadingRow += NumerateRows_OnLoadRows;
dataGrid.AutoGeneratingColumn += SpreadsheetCell_AutoGeneratingColumn;
if (bindTable == null)
{
FillDefaultData(dataGrid);
}
else
{
dataGrid.ItemsSource = bindTable.DefaultView;
}
sheetTabItem.Content = dataGrid;
this.NumberOfSheets++;
}
/// <summary>
/// Adds event handlers for datagrid and binds table to datagrid
/// Also increases number of sheets
/// </summary>
/// <param name="dataGrid"></param>
/// <param name="sheetTabItem"></param>
/// <param name="bindTable"></param>
private void CreateSheet(ExtendedDataGrid dataGrid, TabItem sheetTabItem, DataTable bindTable = null)
{
dataGrid.FillAttributesForSpreedsheetView();
dataGrid.SelectedCellsChanged += CoordinatesOfSelectedCells_OnSelectedCellsChanged;
dataGrid.LoadingRow += NumerateRows_OnLoadRows;
dataGrid.AutoGeneratingColumn += SpreadsheetCell_AutoGeneratingColumn;
if (bindTable == null)
{
FillDefaultData(dataGrid);
}
else
{
dataGrid.ItemsSource = bindTable.DefaultView;
}
sheetTabItem.Content = dataGrid;
this.NumberOfSheets++;
}
private void ComputeAllColumnSummaries(ExtendedDataGrid extendedDataGrrid)
{
var dataView = extendedDataGrrid.ItemsSource as DataView;
if (dataView != null)
{
DataTable itemSourceDataTable = dataView.ToTable();
for (int i = 0; i < itemSourceDataTable.Columns.Count; i++)
{
string currentColumnName = itemSourceDataTable.Columns[i].ColumnName;
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Count][currentColumnName])))
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Count][currentColumnName] =
itemSourceDataTable.Rows.Count;
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Sum][currentColumnName])))
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Sum][currentColumnName] =
Convert.ToDouble(itemSourceDataTable.Compute("Sum(" + currentColumnName + ")", ""));
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Average][currentColumnName])))
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Average][currentColumnName] =
itemSourceDataTable.AsEnumerable().Average((row) => Convert.ToDouble(row[currentColumnName]));
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Min][currentColumnName])))
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Min][currentColumnName] =
Convert.ToDouble(itemSourceDataTable.Compute("Min(" + currentColumnName + ")", ""));
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Max][currentColumnName])))
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Max][currentColumnName] =
Convert.ToDouble(itemSourceDataTable.Compute("Max(" + currentColumnName + ")", ""));
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Smallest][currentColumnName])))
{
var lengthOfCells =
(from DataRow row in itemSourceDataTable.Rows
select Convert.ToString(row[currentColumnName]).Length)
.ToList();
var minValue = "";
foreach (
var row in
itemSourceDataTable.Rows.Cast<DataRow>().Where(
row => Convert.ToString(row[currentColumnName]).Length == lengthOfCells.Min()))
{
minValue = Convert.ToString(row[currentColumnName]);
break;
}
if (lengthOfCells.Count > 0)
{
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Smallest][currentColumnName] =
minValue;
}
}
if (
!string.IsNullOrEmpty(
Convert.ToString(
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Largest][currentColumnName])))
{
var lengthOfCells1 =
(from DataRow row in itemSourceDataTable.Rows
select Convert.ToString(row[currentColumnName]).Length)
.ToList();
var maxValue = "";
foreach (
var row in
itemSourceDataTable.Rows.Cast<DataRow>().Where(
row => Convert.ToString(row[currentColumnName]).Length == lengthOfCells1.Max()))
{
maxValue = Convert.ToString(row[currentColumnName]);
break;
}
if (lengthOfCells1.Count > 0)
{
extendedDataGrrid.RowSummariesTable.Rows[ExtendedDataGrid.Largest][currentColumnName] =
maxValue;
}
}
}
}
}
public void ResetDragDrop(ExtendedDataGrid grid)
{
if (!grid.CanUserReorderRows)
return;
var popupDrag = FindControls.FindChild<Popup>(grid, "popupDrag");
IsDragging = false;
if(popupDrag!=null)
popupDrag.IsOpen = false;
grid.IsReadOnly = false;
grid.DraggedItem = null;
if (_canUserResesizeRows != null)
{
grid.CanUserResizeRows = (bool)_canUserResesizeRows;
_canUserResesizeRows = null;
}
if (_dataGridSelectionMode != null)
{
grid.SelectionMode = _dataGridSelectionMode.Value;
_dataGridSelectionMode = null;
}
}
private void DataGridLoaded(object sender, RoutedEventArgs e)
{
try
{
Loading = true;
if (grid != null)
{
var contextMenu = FindParent <ContextMenu>(grid) ?? (ContextMenu)
((MenuItem)(((ColumnChooserControl)(((DockPanel)(grid.Parent)).Parent)).Parent)).Parent;
// contextMenu.Visibility = Visibility.Collapsed;
ExtendedDataGrid mainGrid = null;
if (contextMenu.PlacementTarget != null)
{
mainGrid =
FindParent <ExtendedDataGrid>(contextMenu.PlacementTarget);
}
else
{
mainGrid = contextMenu.Tag as ExtendedDataGrid;
}
if (mainGrid != null)
{
MainGrid = mainGrid;
if (mainGrid.HideColumnChooser)
{
contextMenu.Visibility = Visibility.Collapsed;
Visibility = Visibility.Collapsed;
return;
}
else
{
contextMenu.Visibility = Visibility.Visible;
Visibility = Visibility.Visible;
}
var dt = new DataTable();
dt.Columns.Add("chkBox", typeof(bool));
dt.Columns.Add("columnHeader");
dt.Columns.Add("columnField");
var row1 = dt.NewRow();
row1["columnField"] = row1["columnHeader"] = "(Select All)";
row1["chkBox"] = false;
_checkedCount = 0;
var actualCount = 0;
dt.Rows.Add(row1);
var columnCollection = mainGrid.Columns;
var columns = columnCollection.ToList();
columns.Sort(CompareColumnPosition);
foreach (var column in columns)
{
if (column != null)
{
var row = dt.NewRow();
row["chkBox"] = ConvertVisibiltyToBool(column.Visibility);
row["columnHeader"] = column.Header;
row["columnField"] = column.SortMemberPath;
dt.Rows.Add(row);
actualCount++;
if (column.Visibility == Visibility.Visible)
{
_checkedCount++;
}
}
}
if (_checkedCount == actualCount)
{
dt.AsEnumerable().First(r => Convert.ToString(r["columnHeader"]) == "(Select All)")["chkBox"
] = true;
}
else
{
var row = dt.AsEnumerable().First(r => Convert.ToString(r["columnHeader"]) == "(Select All)");
row["chkBox"
] = false;
}
ColumnChooserDataTable = dt;
NotifyPropertyChanged("ColumnChooserDataTable");
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally
{
Loading = false;
}
}
public AutoFilterHelper(ExtendedDataGrid grid)
{
CurrentGrid = grid;
}
/// <summary>
/// Displays additional info: current cell name and current cell formula
/// </summary>
/// <param name="dataGrid"></param>
private void DisplayAdditionalInfo(ExtendedDataGrid dataGrid)
{
var currentCellInfo = dataGrid.CurrentCell;
var currentCell = dataGrid.TryToFindGridCell(currentCellInfo);
var contentPresenter = currentCell.Content as ContentPresenter;
if (contentPresenter != null)
{
var spreadsheetCell = contentPresenter.Content as SpreadsheetCell;
if (spreadsheetCell != null)
{
var cellInfo = spreadsheetCell.Tag;
this.CurrentCellFormulaTextBox.Text = cellInfo.Formula;
this.CurrentCellCoordinatesTextBox.Text = cellInfo.CellName.FullName;
}
}
}
/// <summary>
/// Resize table by creating new and copying old values
/// </summary>
/// <param name="exGrid"></param>
/// <param name="oldDataTable"></param>
/// <param name="rowsNum"></param>
/// <param name="colsNum"></param>
private void ResizeTable(ExtendedDataGrid exGrid, DataTable oldDataTable, int rowsNum, int colsNum)
{
int boundRows = oldDataTable.Rows.Count < rowsNum ? oldDataTable.Rows.Count : rowsNum;
int boundCols = oldDataTable.Columns.Count < colsNum ? oldDataTable.Columns.Count : colsNum;
DataTable newDataTable = new DataTable(oldDataTable.TableName);
for (int i = 0; i < colsNum; i++)
{
newDataTable.Columns.Add(new DataColumn(SSColumns.ToString(i), typeof(SpreadsheetCell)));
}
for (int i = 0; i < rowsNum; i++)
{
var row = newDataTable.NewRow();
newDataTable.Rows.Add(row);
for (int j = 0; j < colsNum; j++)
{
string cellName = SSColumns.ToString(j) + i;
row[j] = new SpreadsheetCell(string.Empty, newDataTable, new SpreadsheetCellCustomInfo(new CellName(cellName, i, j)));
}
}
// copy data
for (int i = 0; i < boundRows; i++)
{
for (int j = 0; j < boundCols; j++)
{
SpreadsheetCell cell = newDataTable.GetSpreadsheetCell(i, j);
SpreadsheetCell oldCell = oldDataTable.GetSpreadsheetCell(i, j);
cell.Content = oldCell.Content;
cell.Tag = oldCell.Tag;
}
}
this.DataTables[this.CurrentSheetNumber] = newDataTable;
// VERY VERY DIRTY HACK because nulling throws exception, dunno why
try
{
exGrid.ItemsSource = null;
}
catch { }
exGrid.ItemsSource = newDataTable.DefaultView;
}
/// <summary>
/// Generates tabitem, datagrid, and binds table to datagrid
/// </summary>
/// <param name="bindTable"></param>
private void GenerateSheet(DataTable bindTable = null)
{
TabItem sheetTabItem = new TabItem { Header = "Sheet" + this.NumberOfSheets, Style = (Style)FindResource("BlueAndOrange") };
this.WorkspaceTabControl.Items.Add(sheetTabItem);
ExtendedDataGrid dataGrid = new ExtendedDataGrid { Name = "WorkDataTable" + this.NumberOfSheets };
this.CreateSheet(dataGrid, sheetTabItem, bindTable);
}
