Click here to Skip to main content
11,576,993 members (58,766 online)
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 370.2K 15.6K 352
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.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.Xml;
using Yogesh.Extensions;

namespace Yogesh.ExcelXml
{
	public partial class Worksheet
	{
		#region Import Work sheet
		internal void Import(XmlReader reader)
		{
			foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
			{
				if (xa.LocalName == "Name" && xa.HasValue)
					Name = xa.Value;

				if (xa.LocalName == "StyleID" && xa.HasValue)
					Style = ParentBook.GetStyleByID(xa.Value);
			}

			while (reader.Read() && !(reader.Name == "Worksheet" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						case "Names":
							{
								ExcelXmlWorkbook.ImportNamedRanges(reader, GetParentBook(), this);

								break;
							}

						case "Table":
							{
								ImportTable(reader);

								break;
							}

						case "WorksheetOptions":
							{
								ImportOptions(reader);

								break;
							}
					}
				}
			}
		}
		#endregion

		#region Import Book Options
		private void ImportOptions(XmlReader reader)
		{
			if (reader.IsEmptyElement)
				return;

			while (reader.Read() && !(reader.Name == "WorksheetOptions" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						case "PageSetup":
							{
								ImportPageSetup(reader);

								break;
							}
						case "FitToPage":
							{
								PrintOptions.FitToPage = true;

								break;
							}
						case "TabColorIndex":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										TabColor = i;
								}

								break;
							}
						case "Print":
							{
								ImportPrintOptions(reader);

								break;
							}
						case "SplitHorizontal":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										FreezeTopRows = i;
								}
								break;
							}
						case "SplitVertical":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										FreezeLeftColumns = i;
								}
								break;
							}
					}
				}
			}
		}

		private void ImportPrintOptions(XmlReader reader)
		{
			if (reader.IsEmptyElement)
				return;

			while (reader.Read() && !(reader.Name == "Print" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						case "FitHeight":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										PrintOptions.FitHeight = i;
								}
								break;
							}
						case "FitWidth":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										PrintOptions.FitWidth = i;
								}
								break;
							}
						case "Scale":
							{
								if (reader.IsEmptyElement)
									continue;

								reader.Read();
								if (reader.NodeType == XmlNodeType.Text)
								{
									int i;
									if (reader.Value.ParseToInt(out i))
										PrintOptions.Scale = i;
								}
								break;
							}
					}
				}
			}
		}

		private void ImportPageSetup(XmlReader reader)
		{
			if (reader.IsEmptyElement)
				return;

			while (reader.Read() && !(reader.Name == "PageSetup" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						case "Layout":
							{
								XmlReaderAttributeItem xa = reader.GetSingleAttribute("Orientation", true);
								if (xa != null)
									PrintOptions.Orientation = ObjectExtensions.ParseEnum<PageOrientation>(xa.Value);

								break;
							}
						case "Header":
							{
								XmlReaderAttributeItem xa = reader.GetSingleAttribute("Margin", true);
								if (xa != null)
								{
									double d;
									if (xa.Value.ParseToInt(out d))
										PrintOptions.HeaderMargin = d;
								}

								break;
							}
						case "Footer":
							{
								XmlReaderAttributeItem xa = reader.GetSingleAttribute("Margin", true);
								if (xa != null)
								{
									double d;
									if (xa.Value.ParseToInt(out d))
										PrintOptions.FooterMargin = d;
								}

								break;
							}
						case "PageMargins":
							{
								foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
								{
									double d;
									if (xa.Value.ParseToInt(out d))
									{
										switch (xa.LocalName)
										{
											case "Bottom":
												PrintOptions.BottomMargin = d;
												break;

											case "Left":
												PrintOptions.LeftMargin = d;
												break;

											case "Right":
												PrintOptions.RightMargin = d;
												break;

											case "Top":
												PrintOptions.TopMargin = d;
												break;
										}
									}
								}

								break;
							}
					}
				}
			}
		}
		#endregion

		#region Import Table
		private void ImportTable(XmlReader reader)
		{
			if (reader.IsEmptyElement)
				return;

			int column = 0;

			while (reader.Read() && !(reader.Name == "Table" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						case "Column":
							{
								double width = 0;
								bool hidden = false;
								int span = 1;
								XmlStyle style = null;

								foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
								{
									if (xa.LocalName == "Width" && xa.HasValue)
									{
										double d;
										if (xa.Value.ParseToInt(out d))
											width = d;
									}

									if (xa.LocalName == "Hidden" && xa.HasValue)
										hidden = xa.Value == "1";

									if (xa.LocalName == "Index" && xa.HasValue)
										xa.Value.ParseToInt(out column);

									if (xa.LocalName == "Span" && xa.HasValue)
										xa.Value.ParseToInt(out span);

									if (xa.LocalName == "StyleID" && xa.HasValue)
										style = ParentBook.GetStyleByID(xa.Value);
								}

								for (int i = 1; i <= span; i++)
								{
									Columns(column).Width = width;
									Columns(column).Hidden = hidden;

									if (style != null)
										Columns(column).Style = style;

									column++;
								}

								break;
							}
						case "Row":
							{
								ImportRow(reader);

								break;
							}
					}
				}
			}
		}
		#endregion

		#region Import Row
		private void ImportRow(XmlReader reader)
		{
			bool isEmpty = reader.IsEmptyElement;

			int rowIndex = _Rows.Count;

			double height = -1;
			XmlStyle style = null;
			bool hidden = false;
			foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
			{
				if (xa.LocalName == "Height" && xa.HasValue)
					xa.Value.ParseToInt(out height);

				if (xa.LocalName == "Index" && xa.HasValue)
				{
					xa.Value.ParseToInt(out rowIndex);

					rowIndex--;
				}

				if (xa.LocalName == "StyleID" && xa.HasValue)
					style = ParentBook.GetStyleByID(xa.Value);

				if (xa.LocalName == "Hidden" && xa.HasValue)
					hidden = xa.Value == "1";
			}

			Row row = GetRowByIndex(rowIndex);
			row.Hidden = hidden;
			if (height != -1)
				row.Height = height;
			if (style != null)
				row.Style = style;

			if (isEmpty)
				return;

			while (reader.Read() && !(reader.Name == "Row" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					if (reader.Name == "Cell")
					{
						ImportCell(reader, row);
					}
				}
			}
		}
		#endregion

		#region Import Cell
		[SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]
		private void ImportCell(XmlReader reader, Row row)
		{
			bool isEmpty = reader.IsEmptyElement;

			int cellIndex = row._Cells.Count;

			int mergeDown = 0;
			int mergeAcross = 0;

			XmlStyle style = null;
			string formula = "";
			string reference = "";
			foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
			{
				if (xa.LocalName == "Index" && xa.HasValue)
				{
					xa.Value.ParseToInt(out cellIndex);

					cellIndex--;
				}

				if (xa.LocalName == "StyleID" && xa.HasValue)
					style = ParentBook.GetStyleByID(xa.Value);

				if (xa.LocalName == "HRef" && xa.HasValue)
					reference = xa.Value;

				if (xa.LocalName == "Formula" && xa.HasValue)
					formula = xa.Value;

				if (xa.LocalName == "MergeAcross" && xa.HasValue)
					xa.Value.ParseToInt(out mergeAcross);

				if (xa.LocalName == "MergeDown" && xa.HasValue)
					xa.Value.ParseToInt(out mergeDown);
			}

			Cell cell = Cells(cellIndex, row.RowIndex);
			if (style != null)
				cell.Style = style;

			if (!reference.IsNullOrEmpty())
				cell.HRef = reference;

			if (!formula.IsNullOrEmpty())
			{
				FormulaParser.Parse(cell, formula);

				return;
			}

			if (isEmpty)
				return;

			if (mergeDown > 0 || mergeAcross > 0)
			{
				cell.MergeStart = true;

				Range range = new Range(cell, Cells(cellIndex + mergeAcross, row.RowIndex + mergeDown));

				_MergedCells.Add(range);

				cell.ColumnSpan = range.ColumnCount;
				cell.RowSpan = range.RowCount;
			}

			while (reader.Read() && !(reader.Name == "Cell" && reader.NodeType == XmlNodeType.EndElement))
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					if (reader.Name == "Data")
					{
						ImportCellData(reader, cell);
					}
					else if (reader.Name == "Comment")
					{
						ImportCellComment(reader, cell);
					}
				}
			}
		}

		internal static void ImportCellData(XmlReader reader, Cell cell)
		{
			if (reader.IsEmptyElement)
			{
				cell.Value = "";

				return;
			}

			XmlReaderAttributeItem xa = reader.GetSingleAttribute("Type");
			if (xa != null)
			{
				reader.Read();

				if (reader.NodeType != XmlNodeType.Text)
				{
					cell.Value = "";

					return;
				}

				switch (xa.Value)
				{
					case "String":
						{
							cell.Value = reader.Value;

							break;
						}

					case "Number":
						{
							decimal d;
							if (reader.Value.ParseToInt(out d))
								cell.Value = d;
							else
								cell.Value = reader.Value;

							break;
						}

					case "DateTime":
						{
							DateTime date;
							if (DateTime.TryParseExact(reader.Value, "yyyy-MM-dd\\Thh:mm:ss.fff",
								CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
							{
								cell.Value = date;
							}
							else
								cell.Value = reader.Value;

							break;
						}

					case "Boolean":
						{
							cell.Value = reader.Value == "1";
							break;
						}
				}
			}
		}

		internal static void ImportCellComment(XmlReader reader, Cell cell)
		{
			reader.Read();

			if (reader.LocalName == "Data")
			{
				string comment = reader.ReadInnerXml();

				if (!comment.IsNullOrEmpty())
					cell.Comment = comment;
			}
		}
		#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


You may also be interested in...

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