Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

A Very Easy to Use Excel XML Import-Export Library

, 25 Nov 2008 CPOL
Import export library for the Excel XML format, which reduces a programmer's work to the bare minimum.
Documentation.zip
Documentation.chm
ExcelXml.zip
ExcelXml
ExcelXmlTest
bin
Debug
ExcelXmlTest.exe
ExcelXmlTest.vshost.exe
Yogesh.ExcelXml.dll
Yogesh.Extensions.dll
obj
Debug
TempPE
Properties
Yogesh.ExcelXml
bin
Debug
Yogesh.ExcelXml.dll
Yogesh.Extensions.dll
Help
Documentation.chm
LastBuild.log
obj
Debug
Refactor
TempPE
Release
Properties
Extensions.zip
Extensions
Yogesh.Extensions
bin
Debug
Yogesh.Extensions.dll
Properties
Yogesh.Extensions.zip
Yogesh.Extensions.dll
Yogesh.ExcelXml.dll
using System;
using System.Collections.Generic;
using System.Text;

namespace Yogesh.ExcelXml
{
	#region Parameter class
	/// <summary>
	/// Parameter denotes a single parameter in a formula 
	/// </summary>
	public class Parameter
	{
		/// <summary>
		/// Parameter type
		/// </summary>
		public ParameterType ParameterType { get; private set; }

		/// <summary>
		/// Value of the parameter
		/// </summary>
		public object Value { get; private set; }

		internal Parameter(char p)
		{
			ParameterType = ParameterType.String;
			Value = p;
		}

		internal Parameter(string p)
		{
			ParameterType = ParameterType.String;
			Value = p;
		}

		internal Parameter(Range p)
		{
			ParameterType = ParameterType.Range;
			Value = p;
		}

		internal Parameter(Formula p)
		{
			ParameterType = ParameterType.Formula;
			Value = p;
		}
	}
	#endregion

	/// <summary>
	/// Formula is a formula builder class which can be stored directly in a cell
	/// </summary>
	public class Formula
	{
		#region Private and Internal fields
		internal List<Parameter> parameters;
		#endregion

		#region Public Properties
		/// <summary>
		/// Readonly list of formula paramters
		/// </summary>
		public IList<Parameter> Parameters
		{
			get
			{
				return parameters;
			}
		}

		/// <summary>
		/// Check to force parameters in function
		/// </summary>
		/// <remarks>In case if this flag is set and formula does not contain one or more parameters
		/// then when the formula is assigned to a cell, the cell is left empty.</remarks>
		/// <example><code>
		/// sheet[0, 0].Value = 2;
		/// sheet[1, 0].Value = 12;
		/// sheet[2, 0].Value = 9;
		/// sheet[3, 0].Value = 7;
		/// 
		/// Formula formula1 = new Formula("Sum", new Range(sheet[0, 0], sheet[3, 0]),
		///							delegate (Cell cell) { return cell.GetValue() > 10; });
		///						
		/// formula1.MustHaveParameters = false; // default value
		/// sheet[4, 0].Value = formula; // cell value will be '=SUM()')
		/// 
		/// Formula formula2 = new Formula("Sum", new Range(sheet[0, 0], sheet[3, 0]),
		///							delegate (Cell cell) { return cell.GetValue() > 10; });
		///						
		/// formula2.MustHaveParameters = true;
		/// sheet[5, 0].Value = formula; // cell will be empty
		/// </code></example>
		public bool MustHaveParameters { get; set; }
		#endregion

		#region Constructor
		/// <summary>
		/// Formula constructor
		/// </summary>
		public Formula()
		{
			parameters = new List<Parameter>();
		}
		#endregion

		#region Private and Internal methods
		internal string ToString(Cell cell)
		{
			StringBuilder parameterList = new StringBuilder();

			foreach (Parameter p in Parameters)
			{
				switch (p.ParameterType)
				{
					case ParameterType.Range:
						Range range = p.Value as Range;

						if (range != null)
							parameterList.Append(range.RangeReference(cell));

						break;

					case ParameterType.Formula:
						Formula formula = p.Value as Formula;

						if (formula != null)
							parameterList.Append(formula.ToString(cell));

						break;
					
					case ParameterType.String:
						parameterList.Append(p.Value.ToString());
						break;
				}
			}

			if (MustHaveParameters && (Parameters.Count == 0 || parameterList.Length == 0))
				return "";

			return parameterList.ToString();
		}
		#endregion

		#region Public methods
		/// <summary>
		/// Adds a operator as a parameter in a formula
		/// </summary>
		/// <param name="op">Operator to add as parameter</param>
		public Formula Add(char op)
		{
			Parameter p = new Parameter(op);

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a operator as a parameter in a formula
		/// </summary>
		/// <param name="op">Operator to add as parameter</param>
		public Formula Operator(char op)
		{
			Parameter p = new Parameter(op);

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Starts a new group
		/// </summary>
		public Formula StartGroup()
		{
			Parameter p = new Parameter('(');

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Ends a group
		/// </summary>
		public Formula EndGroup()
		{
			Parameter p = new Parameter(')');

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a empty group to the formula
		/// </summary>
		/// <returns></returns>
		public Formula EmptyGroup()
		{
			Parameter p = new Parameter("()");

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a cell as a parameter in a formula
		/// </summary>
		/// <param name="cell">Cell to add as parameter</param>
		public Formula Add(Cell cell)
		{
			Parameter p = new Parameter(new Range(cell));

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a range as a parameter in a formula
		/// </summary>
		/// <param name="range">Range to add as parameter</param>
		public Formula Add(Range range)
		{
			Parameter p = new Parameter(range);

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a string as a parameter in a formula
		/// </summary>
		/// <param name="parameter">String to add as parameter</param>
		public Formula Add(string parameter)
		{
			Parameter p = new Parameter(parameter);

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds another formula as a parameter in a formula
		/// </summary>
		/// <param name="formula">Another formula to add to this formula's parameter list</param>
		public Formula Add(Formula formula)
		{
			Parameter p = new Parameter(formula);

			Parameters.Add(p);

			return this;
		}

		/// <summary>
		/// Adds a filtered range as a parameter
		/// </summary>
		/// <param name="range">Range to add as parameter</param>
		/// <param name="cellCompare">A custom defined cell to compare the values of the range</param>
		public Formula Add(Range range, Predicate<Cell> cellCompare)
		{
			if (range.CellFrom == null)
				return this;

			if (range.CellTo == null)
			{
				if (cellCompare(range.CellFrom))
					Add(range);

				return this;
			}

			Worksheet ws = range.CellFrom.ParentRow.ParentSheet;

			int rowFrom = range.CellFrom.ParentRow.RowIndex;
			int rowTo = range.CellTo.ParentRow.RowIndex;

			int cellIndexFrom = range.CellFrom.CellIndex;
			int cellIndexTo = range.CellTo.CellIndex;

			for (int j = cellIndexFrom; j <= cellIndexTo; j++)
			{
				// Find the first row in column which matches the style
				int rangeRowIndex = rowFrom;

				do
				{
					if (cellCompare(ws[j, rangeRowIndex]))
					{
						for (int i = rangeRowIndex + 1; i <= rowTo; i++)
						{
							if (!cellCompare(ws[j, i]))
							{
								Add(new Range(ws[j, rangeRowIndex], ws[j, i - 1]));

								rangeRowIndex = i;

								break;
							}
						}
					}
					else
						rangeRowIndex++;
				}
				while (rangeRowIndex <= rowTo);
			}

			return this;
		}
		#endregion
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 26 Nov 2008
Article Copyright 2008 by Yogesh Jagota
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid