This project is read-only.

Introduction

Every developer who already has 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 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 
http://wpfextendeddatagrid.codeplex.com/

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 GetCells() 
	{ 
		return Enumerable.SelectMany(Enumerable.OfType(this.Sheets), w => w.GetCells()); 
	} 
	// Get the collection of sheets in the workbook. In most cases, sheet are worksheets. 
	public ObservableCollection Sheets 
	{ 
		get 
		{
			if (_sheets == null) 
			{ 
				_sheets = new ObservableCollection(); 
				_sheets.CollectionChanged += (sender, e) => 
				{ 
					if (AreSheetsProtected) throw new InvalidOperationException("Sheets are protected"); 
				}; 
			} 
			return _sheets;
		}
	}

	public List Connections 
        { 
		get 
		{ 
			return _connections; 
		} 
		set 
		{ 
			_connections = value; 
		} 
	}

	public ObservableCollection 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 dependencies = EnsureExtraData.Dependencies; 
		if (dependencies.Contains(n)) return; 
		dependencies.Add(n); 
	}

Cell value change notification

The Cell object contains the Formula, Comment, Adress (used in formula) objects. In a partial class, it implements IDependencyNode, which defines the spreadsheet objects dependency tree.

interface IDependencyNode 
{ 
	IEnumerable 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 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, formating) 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.

<!-------------------------------    That's it!   --------------------------->

Last edited Jan 16, 2014 at 3:25 PM by SebastienJehan, version 1