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.Text.RegularExpressions;
using Yogesh.Extensions;
using System.Globalization;

namespace Yogesh.ExcelXml
{
	internal static class FormulaParser
	{
		#region Patterns
		const string RangePattern =
			@"^((\[(?<File>[\w\.]+)\])?('?(?<Sheet>.+)'?!))?R(?<RowStart>\d+)?C(?<ColStart>\d+)?(:R(?<RowEnd>\d+)?C(?<ColEnd>\d+)?)?$";

		const string AbsoluteRangePattern =
			@"^((\[(?<File>[\w\.]+)\])?('?(?<Sheet>.+)'?!))?R(\[(?<RowStart>[\-0-9]+)\])?C(\[(?<ColStart>[\-0-9]+)\])?(:R(\[(?<RowEnd>[\-0-9]+)\])?C(\[(?<ColEnd>[\-0-9]+)\])?)?$";

		const string FunctionPattern =
			@"^(?<FunctionName>[\w\+\-]+)\((?<Parameters>.*)\)$";

		const string PrintRowPattern =
			@"^('?(?<Sheet>.+)'?!)?R(?<RowStart>\d+)(:R(?<RowEnd>\d+))?$";

		const string PrintColumnsPattern =
			@"^('?(?<Sheet>.+)'?!)?C(?<ColStart>\d+)(:C(?<ColEnd>\d+))?$";

		static Regex RangeRegex = new Regex(RangePattern);
		static Regex AbsoluteRangeRegex = new Regex(AbsoluteRangePattern);
		static Regex FunctionRegex = new Regex(FunctionPattern);

		static Regex PrintRowRegex = new Regex(PrintRowPattern);
		static Regex PrintColumnsRegex = new Regex(PrintColumnsPattern);
		#endregion

		#region Private and Internal methods
		private static Cell GetCell(Worksheet ws, Cell cell, bool absolute, int row, int col)
		{
			if (absolute)
			{
				col = cell.CellIndex + col;
				row = cell.ParentRow.RowIndex + row;
			}

			return ws[col, row];
		}

		private static void ParseFormula(Cell cell, Formula formula, string formulaText)
		{
			Match match;
			ParseArgumentType pat = GetArgumentType(formulaText, out match);

			switch (pat)
			{
				case ParseArgumentType.Function:
					{
						string function = match.Groups["FunctionName"].Value;
						Formula subFormula = new Formula();

						subFormula.Add(function).StartGroup();

						string[] parameters = match.Groups["Parameters"].Value.Split(new [] { ',' });

						foreach (string parameter in parameters)
							ParseFormula(cell, subFormula, parameter);

						subFormula.EndGroup();

						formula.Add(subFormula);

						break;
					}

				case ParseArgumentType.Range:
				case ParseArgumentType.AbsoluteRange:
					{
						Range range = new Range(formulaText);
						formula.Add(range);

						break;
					}

				case ParseArgumentType.None:
					{
						formula.Add(formulaText);

						break;
					}
			}
		}

		internal static ParseArgumentType GetArgumentType(string argument, out Match match)
		{
			Match matchFunction = FunctionRegex.Match(argument);
			if (matchFunction.Success)
			{
				match = matchFunction;

				return ParseArgumentType.Function;
			}

			Match matchRange = RangeRegex.Match(argument);
			if (matchRange.Success)
			{
				match = matchRange;

				return ParseArgumentType.Range;
			}

			Match matchAbsoluteRange = AbsoluteRangeRegex.Match(argument);
			if (matchAbsoluteRange.Success)
			{
				match = matchAbsoluteRange;

				return ParseArgumentType.AbsoluteRange;
			}

			match = null;

			return ParseArgumentType.None;
		}

		internal static void Parse(Cell cell, string formulaText)
		{
			if (formulaText[0] != '=')
			{
				cell.Value = formulaText;
				cell.Content = ContentType.UnresolvedValue;
			}

			formulaText = formulaText.Substring(1);

			Formula formula = new Formula();
			ParseFormula(cell, formula, formulaText);

			if (formula.parameters[0].ParameterType == ParameterType.Formula)
			{
				cell.Value = formula.parameters[0].Value as Formula;
				cell.Content = ContentType.Formula;
			}
			else
			{
				cell.Value = formula;
				cell.Content = ContentType.Formula;
			}
		}

		internal static bool ParseRange(Cell cell, Match match, out Range range, bool absolute)
		{
			range = null;

			// If a file name exists, we assume that the file is not
			// the same one as this one, as neither excel nor this
			// library saves filenames for same file. And as external 
			// file references are not supported, return false...
			if (match.Groups["File"].Success)
				return false;

			Worksheet ws;
			if (match.Groups["Sheet"].Success)
			{
				string sheet = match.Groups["Sheet"].Value;
				if (sheet.Right(1) == "'")
					sheet = sheet.Left(sheet.Length - 1);

				ws = cell.GetParentBook()[sheet];
			}
			else
				ws = cell.ParentRow.ParentSheet;

			if (ws == null)
				return false;

			int cellFromRow = 0;
			int cellFromCol = 0;

			if (match.Groups["RowStart"].Success)
			{
				if (int.TryParse(match.Groups["RowStart"].Value, NumberStyles.Integer,
					CultureInfo.InvariantCulture, out cellFromRow))
				{
					if (!absolute)
						cellFromRow--;
				}
			}

			if (match.Groups["ColStart"].Success)
			{
				if (int.TryParse(match.Groups["ColStart"].Value, NumberStyles.Integer,
					CultureInfo.InvariantCulture, out cellFromCol))
				{
					if (!absolute)
						cellFromCol--;
				}
			}

			Cell cellTo = null;
			Cell cellFrom = GetCell(ws, cell, absolute, cellFromRow, cellFromCol);

			if (match.Groups["RowEnd"].Success)
			{
				int cellToRow = 0;
				int cellToCol = 0;

				if (match.Groups["RowEnd"].Success)
				{
					if (int.TryParse(match.Groups["RowEnd"].Value, NumberStyles.Integer,
						CultureInfo.InvariantCulture, out cellToRow))
					{
						if (!absolute)
							cellToRow--;
					}
				}

				if (match.Groups["ColEnd"].Success)
				{
					if (int.TryParse(match.Groups["ColEnd"].Value, NumberStyles.Integer,
						CultureInfo.InvariantCulture, out cellToCol))
					{
						if (!absolute)
							cellToCol--;
					}
				}

				cellTo = GetCell(ws, cell, absolute, cellToRow, cellToCol);
			}

			range = new Range(cellFrom, cellTo);

			return true;
		}

		internal static void ParsePrintHeaders(Worksheet ws, string range)
		{
			string[] printOptions = range.Split(new [] { ',' });

			foreach (string po in printOptions)
			{
				Match match = PrintRowRegex.Match(po);

				if (match.Success)
				{
					int start;

					if (int.TryParse(match.Groups["RowStart"].Value, NumberStyles.Integer,
						CultureInfo.InvariantCulture, out start))
					{
						int end = 0;

						if (!(match.Groups["RowEnd"].Success &&
							int.TryParse(match.Groups["RowEnd"].Value, NumberStyles.Integer,
								CultureInfo.InvariantCulture, out end)))
						{
							end = start;
						}

						ws.PrintOptions.SetTitleRows(start, end);
					}
				}
				else
				{
					match = PrintColumnsRegex.Match(po);

					if (match.Success)
					{
						int start;

						if (int.TryParse(match.Groups["ColStart"].Value, NumberStyles.Integer,
							CultureInfo.InvariantCulture, out start))
						{
							int end = 0;

							if (!(match.Groups["ColEnd"].Success &&
								int.TryParse(match.Groups["ColEnd"].Value, NumberStyles.Integer,
									CultureInfo.InvariantCulture, out end)))
							{
								end = start;
							}

							ws.PrintOptions.SetTitleColumns(start, end);
						}
					}
				}
			}
		}
		#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
Web02 | 2.8.150414.1 | Last Updated 26 Nov 2008
Article Copyright 2008 by Yogesh Jagota
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid