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.IO;
using System.Xml;
using System.Security;
using Yogesh.Extensions;

namespace Yogesh.ExcelXml
{
	partial class ExcelXmlWorkbook
	{
		#region Import Work book
		/// <summary>
		/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
		/// </summary>
		/// <param name="importFile">File to import</param>
		/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
		public static ExcelXmlWorkbook Import(string importFile)
		{
			if (!File.Exists(importFile))
				return null;

			Stream stream;

			try
			{
				stream = new FileStream(importFile, FileMode.Open, FileAccess.Read);
			}
			catch (IOException)
			{
				return null;
			}
			catch (SecurityException)
			{
				return null;
			}
			catch (UnauthorizedAccessException)
			{
				return null;
			}

			ExcelXmlWorkbook book = Import(stream);

			stream.Close();
			stream.Dispose();

			return book;
		}

		/// <summary>
		/// Imports a excel xml workbook into a ExcelXmlWorkbook instance
		/// </summary>
		/// <param name="stream">Stream to import</param>
		/// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
		public static ExcelXmlWorkbook Import(Stream stream)
		{
			XmlReaderSettings settings = new XmlReaderSettings();
			settings.CloseInput = false;
			settings.IgnoreComments = true;
			settings.IgnoreProcessingInstructions = true;
			settings.IgnoreWhitespace = true;

			if (!stream.CanRead)
				return null;

			XmlReader reader = XmlReader.Create(stream, settings);

			ExcelXmlWorkbook book = new ExcelXmlWorkbook();
			book.Styles.Clear();

			int workSheet = 0;

			while (reader.Read())
			{
				if (reader.NodeType == XmlNodeType.Element)
				{
					switch (reader.Name)
					{
						// Document Properties
						case "DocumentProperties":
							{
								if (!reader.IsEmptyElement)
									book.Properties.Import(reader);
								break;
							}

						case "Styles":
							{
								if (!reader.IsEmptyElement)
									book.ImportStyles(reader);

								break;
							}

						case "Names":
							{
								ImportNamedRanges(reader, book, null);

								break;
							}

						case "Worksheet":
							{
								if (!reader.IsEmptyElement)
									book[workSheet++].Import(reader);

								break;
							}
					}
				}
			}

			book.ResolveNamedRangeReferences();

			book.ResolveCellReferences();

			reader.Close();

			stream.Close();
			stream.Dispose();

			return book;
		}
		#endregion

		#region Import Named Ranges
		internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
		{
			if (!reader.IsEmptyElement)
			{
				while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
				{
					if (reader.NodeType == XmlNodeType.Element)
					{
						if (reader.Name == "NamedRange")
						{
							Range range = null;
							string name = "";

							foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
							{
								if (xa.LocalName == "Name" && xa.HasValue)
									name = xa.Value;

								if (xa.LocalName == "RefersTo" && xa.HasValue)
									range = new Range(xa.Value);
							}

							NamedRange nr = new NamedRange(range, name, ws);
							book.NamedRanges.Add(nr);
						}
					}
				}
			}
		}
		#endregion

		#region Import Styles
		private void ImportStyles(XmlReader reader)
		{
			while (reader.Read() && !(reader.Name == "Styles" && reader.NodeType == XmlNodeType.EndElement))
			{
				XmlStyle style = XmlStyle.Import(reader);

				if (style != null)
					Styles.Add(style);
			}
		}
		#endregion

		#region Resolve Ranges
		private void ResolveNamedRangeReferences()
		{
			int printTitleIndex = -1;
			int i = -1;

			foreach (NamedRange nr in NamedRanges)
			{
				i++;

				if (nr.Name == "Print_Titles")
				{
					FormulaParser.ParsePrintHeaders(nr.Worksheet, nr.Range.UnresolvedRangeReference);

					printTitleIndex = i;
				}
				else
				{
					Worksheet ws = nr.Worksheet ?? this[0];

					nr.Range.ParseUnresolvedReference(ws[0, 0]);

					if (nr.Name == "_FilterDatabase")
						ws.AutoFilter = true;

					if (nr.Name == "Print_Area")
						ws.PrintArea = true;
				}
			}

			if (printTitleIndex != -1)
				NamedRanges.RemoveAt(printTitleIndex);
		}

		private void ResolveCellReferences()
		{
			for (int sheetIndex = 0; sheetIndex < _Worksheets.Count; sheetIndex++)
			{
				Worksheet sheet = this[sheetIndex];

				foreach (Row row in sheet._Rows)
				{
					foreach (Cell cell in row._Cells)
					{
						cell.ResolveReferences();
					}
				}
			}
		}
		#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
Web01 | 2.8.150327.1 | Last Updated 26 Nov 2008
Article Copyright 2008 by Yogesh Jagota
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid