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

A Very Easy to Use Excel XML Import-Export Library

, 25 Nov 2008
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;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Globalization;
using System.Xml;
using Yogesh.Extensions;

namespace Yogesh.ExcelXml
{
	/// <summary>
	/// Row class represents a single row in a worksheet
	/// </summary>
	/// <remarks>
	/// Row class represents a single row in a worksheet.
	/// <para>You cannot directly declare a instance of a row from your code by using
	/// <c>new</c> keyword. The only way to access a row is to retrieve it from
	/// a worksheet.</para>
	/// </remarks>
	[SuppressMessage("Microsoft.Naming", "CA1710:IdentifiersShouldHaveCorrectSuffix")]
	public class Row : Styles, IEnumerable<Cell>
	{
		#region Private and Internal fields
		internal List<Cell> _Cells;
		internal Worksheet ParentSheet;
		internal int RowIndex;
		#endregion

		#region Public Properties
		/// <summary>
		/// Gets or sets the row height
		/// </summary>
		public double Height { get; set; }

		/// <summary>
		/// Row is hidden?
		/// </summary>
		public bool Hidden { get; set; }
		#endregion

		#region Constructor
		internal Row(Worksheet parent, int row)
		{
			if (parent == null)
				throw new ArgumentNullException("parent");

			_Cells = new List<Cell>();

			ParentSheet = parent;
			Height = 0;
			RowIndex = row;

			if (parent.Style != null)
				Style = parent.Style;
		}
		#endregion

		#region Private and Internal methods
		private Cell Cells(int colIndex)
		{
			if (colIndex < 0)
				throw new ArgumentOutOfRangeException("colIndex");

			if (colIndex + 1 > _Cells.Count)
				for (int i = _Cells.Count; i <= colIndex; i++)
					_Cells.Add(new Cell(this, i));

			ParentSheet.maxColumnAddressed = Math.Max(colIndex, ParentSheet.maxColumnAddressed);

			return _Cells[colIndex];
		}

		internal override ExcelXmlWorkbook GetParentBook()
		{
			return ParentSheet.ParentBook;
		}

		internal override void IterateAndApply(IterateFunction ifFunc)
		{
		}

		internal override Cell FirstCell()
		{
			return null;
		}

		internal void ResetCellNumbersFrom(int index)
		{
			for (int i = index; i < _Cells.Count; i++)
				_Cells[i].CellIndex = i;
		}

		internal void Empty()
		{
			ParentSheet = null;

			_Cells.Clear();

			_Cells = null;
		}
		#endregion

		#region Public Row Information methods
		/// <summary>
		/// Returns the cell at a given position
		/// </summary>
		/// <param name="colIndex">Index of the <see cref="Yogesh.ExcelXml.Cell"/> starting from 0</param>
		/// <returns><see cref="Yogesh.ExcelXml.Cell"/> reference to the requested cell</returns>
		public Cell this[int colIndex]
		{
			get
			{
				return Cells(colIndex);
			}
		}

		/// <summary>
		/// Returns the number of cell in a row
		/// </summary>
		public int CellCount
		{
			get
			{
				return _Cells.Count;
			}
		}

		/// <summary>
		/// Deletes the row from the parent sheet
		/// </summary>
		public void Delete()
		{
			ParentSheet.DeleteRow(this);
		}
		#endregion

		#region Collection Methods
		/// <summary>
		/// Get a cell enumerator
		/// </summary>
		/// <returns>returns IEnumerator&gt;Cell&lt;</returns>
		public IEnumerator<Cell> GetEnumerator()
		{
			for (int i = 0; i <= ParentSheet.maxColumnAddressed; i++)
			{
				yield return this[i];
			}
		}

		/// <summary>
		/// Get a object enumerator
		/// </summary>
		/// <returns>returns IEnumerator&gt;Cell&lt;</returns>
		IEnumerator IEnumerable.GetEnumerator()
		{
			throw new NotImplementedException();
		}
		#endregion

		#region Public Cell Addition, Insertion & Deletion methods
		/// <summary>
		/// Delete a specific number of cells starting from a cell index
		/// </summary>
		/// <param name="index">Index of cell from which the cells are deleted</param>
		/// <param name="numberOfCells">Number of cells to delete</param>
		/// <param name="cascade">if true, the cells are removed and cells to the right
		/// are cascaded leftwards. if false, the cells are only emptied</param>
		public void DeleteCells(int index, int numberOfCells, bool cascade)
		{
			if (numberOfCells < 0)
				return;

			if (index < 0 || index >= _Cells.Count)
				return;

			if (index + numberOfCells > _Cells.Count)
			{
				numberOfCells = _Cells.Count - index;
			}

			for (int i = index; i < (index + numberOfCells); i++)
			{
				_Cells[index].Empty(!cascade);

				if (cascade)
					_Cells.RemoveAt(index);
			}

			if (cascade)
				ResetCellNumbersFrom(index);
		}

		/// <summary>
		/// Delete a specific number of cells starting from a cell instance
		/// </summary>
		/// <param name="cell">Instance of cell from which the cells are deleted</param>
		/// <param name="numberOfCells">Number of cells to delete</param>
		/// <param name="cascade">if true, the cells are removed and cells to the right
		/// are cascaded leftwards. if false, the cells are only emptied</param>
		public void DeleteCells(Cell cell, int numberOfCells, bool cascade)
		{
			if (cell != null)
				DeleteCells(_Cells.FindIndex(r => r == cell), numberOfCells, cascade);
		}

		/// <summary>
		/// Delete a specific number of cells starting from a cell index
		/// </summary>
		/// <param name="index">Index of cell from which the cells are deleted</param>
		/// <param name="numberOfCells">Number of cells to delete</param>
		/// <remarks>The cells are removed and cells to the right are cascaded
		/// leftwards.</remarks>
		public void DeleteCells(int index, int numberOfCells)
		{
			DeleteCells(index, numberOfCells, true);
		}

		/// <summary>
		/// Delete a specific number of cells starting from a cell instance
		/// </summary>
		/// <param name="cell">Instance of cell from which the cells are deleted</param>
		/// <param name="numberOfCells">Number of cells to delete</param>
		/// <remarks>The cells are removed and cells to the right are cascaded
		/// leftwards.</remarks>
		public void DeleteCells(Cell cell, int numberOfCells)
		{
			if (cell != null)
				DeleteCells(_Cells.FindIndex(r => r == cell), numberOfCells, true);
		}

		/// <summary>
		/// Deletes a cell
		/// </summary>
		/// <param name="index">Index of cell to delete</param>
		public void DeleteCell(int index)
		{
			DeleteCells(index, 1, true);
		}

		/// <summary>
		/// Deletes a cell
		/// </summary>
		/// <param name="cell">Instance of cell to delete</param>
		public void DeleteCell(Cell cell)
		{
			if (cell != null)
				DeleteCells(_Cells.FindIndex(r => r == cell), 1, true);
		}

		/// <summary>
		/// Deletes a cell
		/// </summary>
		/// <param name="index">Index of cell to delete</param>
		/// <param name="cascade">if true, the cell is removed and cells to the right
		/// are cascaded leftwards. if false, the cell is only emptied</param>
		public void DeleteCell(int index, bool cascade)
		{
			DeleteCells(index, 1, cascade);
		}

		/// <summary>
		/// Deletes a cell
		/// </summary>
		/// <param name="cell">Instance of cell to delete</param>
		/// <param name="cascade">if true, the cell is removed and cells to the right
		/// are cascaded leftwards. if false, the cell is only emptied</param>
		public void DeleteCell(Cell cell, bool cascade)
		{
			if (cell != null)
				DeleteCells(_Cells.FindIndex(r => r == cell), 1, cascade);
		}

		/// <summary>
		/// Inserts a specific number of cells before a cell
		/// </summary>
		/// <param name="index">Index of cell before which the cells are to be inserted</param>
		/// <param name="cells">Number of cells to insert</param>
		public void InsertCellsBefore(int index, int cells)
		{
			if (cells < 0)
				return;

			if (index < 0)
				return;

			if (index >= _Cells.Count)
				return;

			for (int i = index; i < (index + cells); i++)
			{
				Cell newCell = new Cell(this, index);
				_Cells.Insert(index, newCell);
			}

			ResetCellNumbersFrom(index);
		}

		/// <summary>
		/// Inserts a specific number of cells before a cell
		/// </summary>
		/// <param name="cell">Instance of cell before which the cells are to be inserted</param>
		/// <param name="cells">Number of cells to insert</param>
		public void InsertCellsBefore(Cell cell, int cells)
		{
			InsertCellsBefore(_Cells.FindIndex(r => r == cell), cells);
		}

		/// <summary>
		/// Inserts a cell before another cell
		/// </summary>
		/// <param name="index">Index of cell before which the cell is to be inserted</param>
		public Cell InsertCellBefore(int index)
		{
			if (index < 0)
				return AddCell();

			if (index >= _Cells.Count)
				return this[index];

			InsertCellsBefore(index, 1);

			return _Cells[index];
		}

		/// <summary>
		/// Inserts a cell before another cell
		/// </summary>
		/// <param name="cell">Instance of cell before which the cell is to be inserted</param>
		public Cell InsertCellBefore(Cell cell)
		{
			return InsertCellBefore(_Cells.FindIndex(r => r == cell));
		}

		/// <summary>
		/// Inserts a specific number of cells after a cell
		/// </summary>
		/// <param name="index">Index of cell after which the cells are to be inserted</param>
		/// <param name="cells">Number of cells to insert</param>
		public void InsertCellsAfter(int index, int cells)
		{
			if (cells < 0)
				return;

			if (index < 0)
				return;

			if (index >= (_Cells.Count - 1))
				return;

			for (int i = index; i < (index + cells); i++)
			{
				Cell newCell = new Cell(this, index);

				_Cells.Insert(index + 1, newCell);
			}

			ResetCellNumbersFrom(index);
		}

		/// <summary>
		/// Inserts a specific number of cells after a cell
		/// </summary>
		/// <param name="cell">Instance of cell after which the cells are to be inserted</param>
		/// <param name="cells">Number of cells to insert</param>
		public void InsertCellsAfter(Cell cell, int cells)
		{
			if (cell != null)
				InsertCellsAfter(_Cells.FindIndex(r => r == cell), cells);
		}

		/// <summary>
		/// Inserts a cell after another cell
		/// </summary>
		/// <param name="index">Index of cell after which the cell is to be inserted</param>
		public Cell InsertCellAfter(int index)
		{
			if (index < 0)
				return AddCell();

			if (index >= (_Cells.Count - 1))
				return this[index + 1];

			InsertCellsAfter(index, 1);

			return _Cells[index];
		}

		/// <summary>
		/// Inserts a cell after another cell
		/// </summary>
		/// <param name="cell">Instance of cell after which the cell is to be inserted</param>
		public Cell InsertCellAfter(Cell cell)
		{
			return InsertCellAfter(_Cells.FindIndex(r => r == cell));
		}

		/// <summary>
		/// Adds a cells to the end of the row
		/// </summary>
		/// <returns>Instance of the newly created cell</returns>
		public Cell AddCell()
		{
			return this[_Cells.Count];
		}

		#endregion

		#region Export
		internal void Export(XmlWriter writer)
		{
			// Row
			writer.WriteStartElement("Row");

			if (!StyleID.IsNullOrEmpty() && ParentSheet.StyleID != StyleID && StyleID != "Default")
				writer.WriteAttributeString("ss", "StyleID", null, StyleID);

			if (Height != 0)
			{
				writer.WriteAttributeString("ss", "AutoFitHeight", null, "0");
				writer.WriteAttributeString("ss", "Height", null, Height.ToString(CultureInfo.InvariantCulture));
			}

			if (Hidden)
				writer.WriteAttributeString("ss", "Hidden", null, "1");

			bool printIndex = false;

			// Start Cells
			foreach (Cell cell in _Cells)
			{
				if (cell.IsEmpty() && !cell.MergeStart)
				{
					printIndex = true;
				}
				else
				{
					cell.Export(writer, printIndex);

					printIndex = false;
				}
			}
			// End Cells

			// End Row
			writer.WriteEndElement();
		}
		#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 | Mobile
Web04 | 2.8.140916.1 | Last Updated 26 Nov 2008
Article Copyright 2008 by Yogesh Jagota
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid