Introduction
Every developer who has already worked with Excel Interop knows that the spreadsheet cell object typing is a nightmare. Excel cell typing (performed with Excel Interop with Excel.Range objects) is not easy either. An Open Source tool to build beautiful Excel compliant spreadsheet reports, using a clean object modelisation, was indeed a need.
Background
Anyone with some WPF knowledge can add a spreadsheet grid in their WPF application. In order to extend this spreadsheet grid with new features, such as graphs, a decent WPF understanding is required.
Using the Code
The source code is hosted on CodePlex at https://wpfsheet.codeplex.com/.
The code is using the View / ViewModel separation paradigm. The main View in the application is the SpreadsheetWindow.xaml Window class in OpenLS.Spreadsheet.UI.40, containing the ribbon, the docking panel and the workbook view.
The SpreadsheetWindow.xaml class references the DevXPress WPF Docking Framework; for those interested in simpler but free docking frameworks, they might use AvalonDock https://avalondock.codeplex.com/ . It also uses DevXpress DataGrid in its "Big data table" concepts, which are type-sensitive sortable groupable grids linked to a spreadsheet selection area. In order to keep using open source software, this DataGrid is "in process" of being replaced by the open source WPF grid:
The spreadsheet is designed to support multiple languages (as for now, French, English are fully supported, but for the core functions the resources libraries for up to 30 languages are included).
Because the code is vast (it contains the WPF spreadsheet view, the graphs, the document serialization class, the computation engine, the command classes, the data connectors, the styling classes, the function add-in libraries), this article will mainly focus on:
- The Workbook UI (to be referenced on a WPF Window if a multi-sheet workbook is required in the application) is
SpreadSheetElement.
- The ViewModel associated to this
SpreadSheetElement is the Workbook class.
public partial class Workbook : ViewModelBase,
IOfficeXmlSerializable, IThemedElement, IReferenceFactory, IWorkbook, IFormatValueConverter
{
internal IEnumerable<Cell> GetCells()
{
return Enumerable.SelectMany(Enumerable.OfType<Worksheet>(this.Sheets), w => w.GetCells());
}
public ObservableCollection<Sheet> Sheets
{
get
{
if (_sheets == null)
{
_sheets = new ObservableCollection<Sheet>();
_sheets.CollectionChanged += (sender, e) =>
{
if (AreSheetsProtected)
throw new InvalidOperationException("Sheets are protected");
};
}
return _sheets;
}
}
public List<DataConnection> Connections
{
get
{
return _connections;
}
set
{
_connections = value;
}
}
public ObservableCollection<DefinedName> DefinedNames
{
get
{
return NameManager.definedNames;
}
}
The core Spreadsheet ViewModel, the Workbook object, implements the ViewModelBase for inheriting from INotifyPropertyChanged, from IOfficeXmlSerializable to be saved in an XML format (compliant with XSLX), from IThemedElement because a Theme can be applied on the object, from IReferenceFactory because the worbook object can be referenced as an object in a spreadsheet formula.
public partial class Cell : INotifyPropertyChanged, ICellEnumerator {
{
internal Cell(Row row, Column column) : this(row)
{
Contract.Requires(row != null);
Contract.Requires(column != null);
_column = column;
}
private Cell(Row row, Column column, object value) : this(row, column)
{
Value = value;
}
public CellAddress Address
{
get
{
return new CellAddress(Row.Index, Column.Index);
}
}
internal Cell GetNeighbor(NavigationDirection k)
{
...
}
public Comment Comment
{
...
}
private object getFormatedValue(Format format, object value, int maxCount)
{
...
}
public string Formula
{
get
{
try
{
if (HasExtraData && ExtraData.FormulaWithoutEqual != null)
return "=" + ExtraData.FormulaWithoutEqual;
if (Expression != null) return Expression.GetTextWithEqual
(new TextContext(new CellEvaluationContext(this)));
return null;
}
catch (Exception e)
{
Debug.WriteLine("Invalid formula " + e);
return "Invalid formula " + e.Message;
}
}
set
{
SetFormulaImp(value, LocalizationMode.NonLocalized, null);
}
}
...
partial class Cell: IDependencyNode
{
IRange ICellEnumerator.ToRange()
{
return this.ToRange();
}
internal void AddDependent(IDependencyNode n)
{
promote();
HashListNoDuplicate<IDependencyNode> dependencies = EnsureExtraData.Dependencies;
if (dependencies.Contains(n)) return;
dependencies.Add(n);
}
Cell Value Change Notification
The Cell object contains the Formula, Comment, Address (used in formula) objects. In a partial class, it implements IDependencyNode, which defines the spreadsheet objects dependency tree.
interface IDependencyNode
{
IEnumerable<IDependencyNode> Dependents();
bool ProcessChange();
void ChainRecalc();
void AddDependent(IDependencyNode node);
void RemoveDependent(IDependencyNode node);
}
The ChainRecalc() method in the Cell object will basically trigger the Workbook.NotifyValueChanged(cell, oldValue, cell.Value) command.
Cell Ranges
Ranges in formula (such as for SUM(C1, C2) use the notion of "cell cluster key" (in the previous case, (C1, C2)).
They are defined using a CellCLusterKey class.
struct CellClusterKey
{
public override string ToString()
{
return RangeAddress.ToString();
}
public CellClusterKey(CellAddress cell) : this(GetRounded(cell.Row ), GetRounded(cell.Column))
{
...
}
public RangeAddress RangeAddress
{
get
{
return new RangeAddress(new CellAddress(Row, Column),
new CellAddress(Row + Size - 1, Column + Size - 1));
}
}
...
Spreadsheet Functions Add-In
Spreadsheet functions are added using the FunctionManager Functions.FunctionManager.AddAddIn(typeof(TextFunctions).Assembly) in the SpreadsheetApplication.xaml.cs file. In order to be added as an AddIn function list, a class must have the [AddInFunctions] class attribute. Functions can deal with spreadsheet objects define using parameters attributes, such as in the example below.
[AddInFunctions]
public static class LookupFunctions
{
public static object Address([WorksheetContext]IWorksheet worksheet,
int row_num, int column_num, [Optional(1)] int abs_num,
[Optional(true)] bool A1, [Optional(null)] string sheet_text)
{
return worksheet.Address(row_num, column_num, abs_num, A1, sheet_text);
}
public static int Areas([AllowCellToRangeConversion] params IRange[] range)
{
int result = 0;
foreach (var r in range)
result += r.Areas;
return result;
}
public static object Choose(double index_num, [NoConversion]params object[] values)
{
var actualIndex = (int) Math.Floor(index_num);
if (actualIndex < 1 || actualIndex > values.Length)
return Errors.ValueError; return values[actualIndex - 1];
}
[NoValueAccess]
public static int Column([CellContext]ICell context,
[AcceptsRangeFirstCell, Optional(null)] ICell cr)
{
ICell cell = cr ?? context; return cell.ColumnIndex + 1;
}
[NoValueAccess]
public static object Columns(IArray array)
{
return array.ColumnCount;
}
Several conversion attributes used in function add-ins will inherit from the IParameterConverter interface; they will therefore provide a way to convert data using the ConversionData context object.
Points of Interest
The interesting part of the project is the "complex" object structure and object dependencies of a spreadsheet application. WPF revealed itself well architectured enough to define objects behavior and interaction (update, grouping, styling, formatting) without any extension required.
History
This project started in 2011 but is only published now; the goal is to provide a foundation for better charting and data entry in a spreadsheet software.
