C# – Working with Excel

Working with Excel

Library

Include

using Excel = Microsoft.Office.Interop.Excel

Instantiating the excel application class

Excel.Application excelApp = new Excel.Application();

To make the object visible

excelApp.Visible = true; //false to turnoff visibility

Add a workbook

Excel.Workbook openExcel = excelApp.Workbooks.Add(Type.Missing);

or to add an existing excel file

var path = @"C:\\excel.xlsx";
Excel.Workbook openExcel = excelApp.Workbooks.Open(path);

To add blank sheets

Excel.Sheets sheets = openExcel.Worksheets;
Excel._Worksheet worksheet = (Excel.Worksheet)sheets.Add(Type.Missing);

To select active sheet

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

or To select sheet by name

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetname];

Writing to the cell

workSheet.Cells[1, "A"] = "Fund";

Set the column width

workSheet.Columns.ColumnWidth = 18;

Using get_Range and format header

Excel.Range headerColumnRange = workSheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
headerColumnRange.WrapText = true;
headerColumnRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

Name of the worksheet

workSheet.Name = name.Substring(0, len);

Format cells

workSheet.Cells[startCell, strAlpha].NumberFormat = "0.00";

Autofit columns

workSheet.Columns.AutoFit();

Save

openExcel.Save();

SaveAs .xlsx

openExcel.SaveAs(strFullFilePathNoExt, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
    Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, 
    Excel.XlSaveConflictResolution.xlUserResolution, true, 
    Missing.Value, Missing.Value, Missing.Value);

Close excel

openExcel.Close();

Copy or Cloning

foreach(Worksheet sheet in workBook.Worksheets)
{
    var newbook = app.Workbooks.Add(1);
    sheet.Copy(newbook.Sheets[1]);

    newbook.SaveAs(FileDropLocation + "\\" + sheet.Name);
    newbook.Close();
}

workBook.Close();

Delete worksheet

workSheet.Worksheets[2].Delete();

Border

Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;  
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
border.Weight = 2d; 

C# – LINQ to read XML

LINQ to read XML

Loading the XML File

XElement and XDocument derive from XContainer.

1. XElement class

Reads the XML elements (attributes and children)

Implements IXmlSerializable.

XElement xelement = XElement.Load(file);
IEnumerable employees = xelement.Elements();
//Read the entire XML
foreach(var employee in employees)
{
	Console.WriteLine("Reading Employee: == " + employee);
}

2. XDocument class

Reads the entire XMl document.

Reads from the single root element.

XDocument xdocument = XDocument.Load(file);
IEnumerable employees = xdocument.Elements();
foreach(var employee in employees)
{
     Console.WriteLine("Reading Employee: == " + employee);
}

Reading the Single Element

foreach(var employee in employees)
{
     Console.WriteLine("Reading Employee: == " + employee.Element("Name").Value);
}

Reading Multiple Elements

foreach(var employee in employees)
{
Console.WriteLine("Reading Employee: {0} == {1}", employee.Element("Name").Value, employee.Element("EmpId").Value);
}

Filter Elements

XElement xelement = XElement.Load(file);
var name = from nm in xelement.Elements("Employee")
    where (string)nm.Element("Sex") == "Female"
    select nm;

    Console.WriteLine("Information about Female Employees");
    foreach(XElement x in name)
    {
	Console.WriteLine(x);
    }

Filter Element within another Element

XElement xelement = XElement.Load(file);
var addresses = from address in xelement.Elements("Employee")
	where (string)address.Element("Address").Element("City") == "Alta"
	select address;

Console.WriteLine("Information about Employees in Alta City");
foreach(XElement x in addresses)
{
	Console.WriteLine(x);
}

Find Nested Elements using Descendants

XElement xelement = XElement.Load(file);
Console.WriteLine("List of all Zip codes");
foreach(XElement x in xelement.Descendants("Zip"))
{
    Console.WriteLine((string)x);
}

Sorting Elements

XElement xelement = XElement.Load(file);
IEnumerable codes = from code in xelement.Elements("Employee")
	let zip = (string)code.Element("Address").Element("Zip")
        orderby zip
	select zip;
Console.WriteLine("List and Sort all Zip Codes");
			 
foreach (string zp in codes)
	Console.WriteLine(zp);

C# – LinkedList

LinkedList

LinkedList<T> is a generic doubly linked list.

Doubly linked list is a chain of nodes in which each references the node before, the node after and the actual element.

Benefit – element can always be inserted efficiently anywhere in the list, since it just involves creating a new node and updating a few references.

Implements IEnumerable<T> and ICollection<T>.

LinkedList<T> supports copying to an array for indexed processing and obtaining an enumerator to support foreach statement.

public sealed class LinkedListNode
{
	public LinkedList List { get; }
	public LinkedListNode Next { get; }
	public LinkedListNode Previous { get; }
	public T Value { get; set; }
}
var tune = new LinkedList();
tune.AddFirst ("do");                           tune.Dump(); // do
tune.AddLast ("so");                            tune.Dump(); // do - so

tune.AddAfter (tune.First, "re");               tune.Dump(); // do - re- so
tune.AddAfter (tune.First.Next, "mi");          tune.Dump(); // do - re - mi- so
tune.AddBefore (tune.Last, "fa");               tune.Dump(); // do - re - mi - fa- so

tune.RemoveFirst();                             tune.Dump(); // re - mi - fa - so
tune.RemoveLast();                              tune.Dump(); // re - mi - fa

LinkedListNode miNode = tune.Find ("mi");
tune.Remove (miNode);                           tune.Dump(); // re - fa
tune.AddFirst (miNode);                         tune.Dump(); // mi- re - fa

C# – IReadOnlyList

IReadOnlyList<T>

Low version of IList<T> which provides only readonly view of list.

public interface IReadOnlyList<out T> : IEnumerable<T>, IEnumerable
{
	int Count { get; }
	T this[int index] { get; }
}

C# – IList

IList<T> and IList

IList<T>

Standard interface for collection indexable by position.

Functionality inherited from ICollection<T> and IEnumerable<T>.

Provides the ability to read or write an element by position.

public interface IList : ICollection, IEnumerable, IEnumerable
{
	T this [int index] { get; set; }
	int IndexOf (T item);
	void Insert (int index, T item);
	void RemoveAt (int index);
}

IndexOf – performs liner search, returns -1 if item not found.

Insert – inserts at specified index.

RemoveAt – removes at specified index.

IList

public interface IList : ICollection, IEnumerable
{
object this [int index] { get; set }
bool IsFixedSize { get; }
bool IsReadOnly { get; }
int Add (object value);
void Clear();
bool Contains (object value);
int IndexOf (object value);
void Insert (int index, object value);
void Remove (object value);
void RemoveAt (int index);
}

Add – returns an integer index of the newly added item.

C# – ICollection

ICollection<T> and ICollection

ICollection<T> is the standard interface for countable collections of objects.

It provides the ability to determine the size of a collection (Count).

If the collection is writable you can also Add, Remove and Clear items from the collection.

Collection can be traversed via foreach statement since it extends IEnumerable.

Methods and Properties

Contains – determine whether an item exists in the collection.

ToArray – copy the collection into an array.

IsReadOnly – determine whether the collection is read-only.

Count-size of the collection.

Add– add item to the collection.

Remove– remove item from collection.

Clear– clear collection.

Generic Collection

public interface ICollection<T> : IEnumerable<T>, IEnumerable
{
	int Count { get; }
	
	bool Contains (T item);
	void CopyTo (T[] array, int arrayIndex);
	bool IsReadOnly { get; }

	void Add(T item);
	bool Remove (T item);
	void Clear();
}

Non Generic Collection

public interface ICollection : IEnumerable
{
	int Count { get; }
	bool IsSynchronized { get; }
	object SyncRoot { get; }
	void CopyTo (Array array, int index);
}

The interface is implemented in conjunction with either IList or IDictionary.

C# – IEnumerable and IEnumerator

IEnumerable and IEnumerator

Related Article

IEnumerator Interface

Elements in a collection are enumerated in a forward manner only.

public interface IEnumerator
{
	bool MoveNext();
	object Current { get; }
	void Reset();
}

MoveNext – advances the current element to the next position or returns false if there are no more elements in the collection.

Current – returns the element at the current position.

Reset – moves back to the start of the collection.

Collections do not implement enumerators, instead they provide enumerators via the interface IEnumerable.

public interface IEnumerable
{
	IEnumerable GetEnumerator();
}

IEnumerable

Provides flexibility in that the iteration logic can be farmed off to another class.

Several consumers can enumerate the collection at once without interfering with each other.

IEnumerable can be thought of as IEnumeratorProvider.

string s = "Hello";

// Because string implements IEnumerable, we can call GetEnumerator():
IEnumerator rator = s.GetEnumerator();

while (rator.MoveNext())
{
	char c = (char) rator.Current;
	Console.Write (c + ".");
}

Console.WriteLine();

// Equivalent to:

foreach (char c in s)
	Console.Write (c + ".");

IEnumerable<T> and IEnumerator<T>

IEnumerator and IEnumerable are nearly always implemented in conjunction with their extended generic versions.

public interface IEnumerator : IEnumerator, IDisposable
{
	T Current { get; }
}

public interface IEnumerable : IEnumerable
{
	IEnumerator GetEnumerator();
}

By defining typed version of Current and GetEnumerator, these interfaces strengthen static type safety and avoid the boxing overhead.

Arrays automatically implement IEnumerable<T>.

IEnumerable<T> and IDisposable

IEnumerator<T> inherits from IDisposable.

This allows enumerators to hold references to resources such as database connections to ensure that those resources are released when enumeration is complete.

foreach (var element in somethingEnumerable) { ... }

translates into:

using ( var rator = somethingEnumerable.GetEnumerator())
	while (rator.MoveNext())
	{
		var element = rator.Current;
		...
	}

The using block ensures disposal.

IEnumerable s = "Hello";

using (var rator = s.GetEnumerator())
	while (rator.MoveNext())
	{
		char c = (char) rator.Current;
		Console.Write (c + ".");
	}

Non Generic Interface

void Main()
{
   Count("the quick brown fox".Split());
}

public static int Count(IEnumerable e)
{
   int count = 0;
   foreach (object element in e)
   {
      var subCollection = element as IEnumerable;
      if (subCollection != null)
          count += Count (subCollection);
      else
          count++;
   }
   return count;
}

IEnumerable or IEnumerable<T> can be implemented for one of the reasons:

  • To support the foreach statement.
  • To inter-operate with anything expecting a standard collections.
  • To meet the requirements of a more sophisticated collection interface.
  • To support collection initializers.

To implement IEnumerable/IEnumerable<T>:

  • If class is wrapping another collection, return the wrapped collection’s enumerator
  • Using the iterator yield return
  • By instantiating your own implementation

Using Iterator

void Main()
{
	foreach (int element in new MyCollection())
		Console.WriteLine (element);
}

public class MyCollection : IEnumerable
{
	int[] data = { 1, 2, 3 };
	
	public IEnumerator GetEnumerator()
	{
		foreach (int i in data)
			yield return i;
	}
}

Using Iterator using Generic

void Main()
{
	foreach (int element in new MyGenCollection())
		Console.WriteLine (element);
}

public class MyGenCollection : IEnumerable<int>
{
	int[] data = { 1, 2, 3 };

	public IEnumerator GetEnumerator()
	{
		foreach (int i in data)
			yield return i;
	}

	IEnumerator IEnumerable.GetEnumerator()     // Explicit implementation
	{                                           // keeps it hidden.
		return GetEnumerator();
	}
}

Using Iterator Method

The yield statement allows for an easier variation. Instead of writing a class the iteration logic can be moved into a method returning a generic IEnumerable<T> and let the compiler take care of the rest.

void Main()
{
	foreach (int i in Test.GetSomeIntegers())
		Console.WriteLine (i);
}

public class Test
{
	public static IEnumerable  GetSomeIntegers()
	{
		yield return 1;
		yield return 2;
		yield return 3;
	}
}

C# – Order Comparison

Order Comparison

Protocols are:

1. The IComparable Interfaces

2. The < and > Operators

IComparable Interfaces

Used for general-purpose sorting algorithms.

System.String implements IComparable interfaces.

string[] colors = { "Green", "Red", "Blue" };
Array.Sort (colors);
foreach (string c in colors) Console.Write (c + " ");   // Blue Green Red

// The IComparable interfaces are defined as follows:
//   public interface IComparable       { int CompareTo (object other); }
//   public interface IComparable { int CompareTo (T other);      }

Console.WriteLine ("Beck".CompareTo ("Anne"));       // 1
Console.WriteLine ("Beck".CompareTo ("Beck"));       // 0
Console.WriteLine ("Beck".CompareTo ("Chris"));      // -1

If a comes after b, a.CompareTo(b) returns a positive number.

If a is the same as b, a.CompareTo(b) returns 0.

If a comes before b, a.CompareTo(b) returns a negative number.

< and > Operators

The < and > operators are more specialized and mostly used for numeric types. They are efficient for bytecode translation and used for intensive algorithms.

// Some types define < and > operators:
bool after2010 = DateTime.Now > new DateTime (2010, 1, 1);

// The string type doesn't overload these operators (for good reason):
bool error = "Beck" > "Anne";       // Compile-time error
public struct Note : IComparable, IEquatable, IComparable
{
	int _semitonesFromA;
	public int SemitonesFromA => _semitonesFromA;
	
	public Note (int semitonesFromA)
	{
		_semitonesFromA = semitonesFromA;
	}
	
	public int CompareTo (Note other)            // Generic IComparable
	{
		if (Equals (other)) return 0;    // Fail-safe check
		return _semitonesFromA.CompareTo (other._semitonesFromA);
	}
	
	int IComparable.CompareTo (object other)     // Nongeneric IComparable
	{
		if (!(other is Note))
			throw new InvalidOperationException ("CompareTo: Not a note");
		return CompareTo ((Note) other);
	}
	
	public static bool operator < (Note n1, Note n2)
		=> n1.CompareTo (n2) < 0;
	
	public static bool operator > (Note n1, Note n2)
		=> n1.CompareTo (n2) > 0;
	
	public bool Equals (Note other)    // for IEquatable
		=> _semitonesFromA == other._semitonesFromA;
	
	public override bool Equals (object other)
	{
		if (!(other is Note)) return false;
		return Equals ((Note) other);
	}
	
	public override int GetHashCode()
		=> _semitonesFromA.GetHashCode();
	
	public static bool operator == (Note n1, Note n2)
		=> n1.Equals (n2);
	
	public static bool operator != (Note n1, Note n2)
		=> !(n1 == n2);
}

static void Main()
{
	Note n1 = new Note (1);	
	Note n2 = new Note (2);
	(n2 > n1).Dump();
}

C# – Equality Comparison

Equality Comparison

Value Versus Referential Equality (== or !=)

Value Equality – two values are equivalent in some sense.

Referential Equality – two references refer to exactly the same object.

By default the value types use value equality, reference types use referential equality.

Value Equality

// Simple value equality:
int x = 5, y = 5;
Console.WriteLine (x == y);   // True (by virtue of value equality)

DateTimeOffset

The two DateTimeOffsets struct refer to the same point in time.

// A more elaborate demonstration of value equality:
var dt1 = new DateTimeOffset (2010, 1, 1, 1, 1, 1, TimeSpan.FromHours(8));
var dt2 = new DateTimeOffset (2010, 1, 1, 2, 1, 1, TimeSpan.FromHours(9));
Console.WriteLine (dt1 == dt2);   // True (same point in time)

Reference Equality

// Referential equality:
Foo f1 = new Foo { X = 5 };
Foo f2 = new Foo { X = 5 };
Console.WriteLine (f1 == f2);   // False (different objects)

//Are equal because they refer to the same object
Foo f3 = f1;
Console.WriteLine (f1 == f3);   // True (same objects)

Equality Uri Class

// Customizing classes to exhibit value equality:
Uri uri1 = new Uri ("http://www.linqpad.net");
Uri uri2 = new Uri ("http://www.linqpad.net");
Console.WriteLine (uri1 == uri2);              // True

Standard Equality Protocols

1. The == and != operators

2. The virtual Equals method in object

3. The IEquatable<T> interface

== and !=

int x = 5;
int y = 5;
Console.WriteLine (x == y);      // True

//Object is reference type thus uses referential equality to compare x and y.
//Returns false because they refer to two different boxed objects on the heap.
object x = 5;
object y = 5;
Console.WriteLine (x == y);      // False

The virtual Object.Equals method

Object.Equals method is defined in System.Object and available to all types and resolved at runtime.

void Main()
{
	object x = 5;
	object y = 5;
	Console.WriteLine (x.Equals (y));      // True
	
	Console.WriteLine (AreEqual (x, y));		// True
	Console.WriteLine (AreEqual (null, null));	// True
}

// Here's an example of how we can leverage the virtual Equals method:
public static bool AreEqual (object obj1, object obj2)
{
	if (obj1 == null) return obj2 == null;
	return obj1.Equals (obj2);
	// What we've written is in fact equivalent to the static object.Equals method!
}

The static object.Equals method

The static helper method does the work of AreEqual accepting two arguments.

static void Main()
{
	object x = 3, y = 3;
	Console.WriteLine (object.Equals (x, y));   // True
	x = null;
	Console.WriteLine (object.Equals (x, y));   // False
	y = null;
	Console.WriteLine (object.Equals (x, y));   // True
}

// Here's how we can use object.Equals:
class Test 
{
	T _value;
	public void SetValue (T newValue)
	{
		if (!object.Equals (newValue, _value))
		{
			_value = newValue;
			OnValueChanged();
		}
	}

	protected virtual void OnValueChanged() { /*...*/ }
}

The static object.ReferenceEquals method

class Widget
{
	// Let's suppose Widget overrides its Equals method and overloads its == operator such
	// that w1.Equals (w2) would return true if w1 and w2 were different objects.
	/*...*/
}

static void Main()
{
	Widget w1 = new Widget();
	Widget w2 = new Widget();
	Console.WriteLine (object.ReferenceEquals (w1, w2));     // False
}

The IEquatable interface

IEquatable<T> implementation gives the same result as calling object’s virtual Equals method.

class Test where T : IEquatable
{
	public bool IsEqual (T a, T b) =>  a.Equals (b);     // No boxing with generic T
}

static void Main()
{
	new Test().IsEqual (3, 3).Dump();
}

Removing the generic constraint, the class will still compile but will bind to the slower object.Equals.

When Equals and == are not equal

The double type’s == operator ensures that double.NaN cannot equal to anything else. It will always result as false. But Equals method will always apply reflexive equality.

Collections and dictionaries always rely on Equals behaving this way.

// With value types, it's quite rare:
double x = double.NaN;
Console.WriteLine (x == x);            // False
Console.WriteLine (x.Equals (x));      // True

// With reference types, it's more common:
var sb1 = new StringBuilder ("foo");
var sb2 = new StringBuilder ("foo");
Console.WriteLine (sb1 == sb2);          // False (referential equality)
Console.WriteLine (sb1.Equals (sb2));    // True  (value equality)

Equality and Custom Types

Value types use value equality.

Reference types use referential equality.

Struct’s Equals method applies structural value equality by default.

When to override the equality method

When there is a need to change the meaning of equality.

When there is need to speed up equality comparisons for structs.

public struct Area : IEquatable 
{
	public readonly int Measure1;
	public readonly int Measure2;
	
	public Area (int m1, int m2)
	{
		Measure1 = Math.Min (m1, m2);
		Measure2 = Math.Max (m1, m2);
	}
	
	public override bool Equals (object other)
	{
		if (!(other is Area)) return false;
		return Equals ((Area) other);        // Calls method below
	}
	
	public bool Equals (Area other)        // Implements IEquatable
		=> Measure1 == other.Measure1 && Measure2 == other.Measure2;
	
	public override int GetHashCode()
		=> Measure2 * 31 + Measure1;    // 31 = some prime number
	
	public static bool operator == (Area a1, Area a2)
		=> a1.Equals (a2);
	
	public static bool operator != (Area a1, Area a2)
		=> !a1.Equals (a2);
}

static void Main()
{
	Area a1 = new Area (5, 10);
	Area a2 = new Area (10, 5);
	Console.WriteLine (a1.Equals (a2));    // True
	Console.WriteLine (a1 == a2);          // True
}

C# – Tuples and Guid Struct

Tuples

Tuples are set of generic classes for holding a set of differently typed elements. Where each has read-only properties called Item1, Item2.

Creating Tuples

1. Using Constructor

var t = new Tuple (123, "Hello");

2. Static Helper method

Tuple t = Tuple.Create(123, "Hello");

3. Using Implicit Type

var t = Tuple.Create(123, "Hello");

Accessing the Tuple Properties

Console.WriteLine (t1.Item1 * 2);         // 246
Console.WriteLine (t1.Item2.ToUpper());   // HELLO

// The alternative sacrafices static type safety and causes boxing with value types:

object[] items = { 123, "Hello" };
Console.WriteLine ( ((int)    items[0]) * 2       );   // 246
Console.WriteLine ( ((string) items[1]).ToUpper() );   // HELLO

Tuples are convenient in returning more than one value from a method or creating collections of value pairs. Alternative to tuples is to use an object array but with limitations such as boxing and unboxing.

Comparing Tuples

Tuples are classes so reference types. The equality operator returns false where as the Equals method is overridden to compare each individual element.

var t1 = Tuple.Create (123, "Hello");
var t2 = Tuple.Create (123, "Hello");

Console.WriteLine (t1 == t2);           // False
Console.WriteLine (t1.Equals (t2));     // True

Guid Struct

The Guid struct represents a globally unique identifier: a 16-byte value when generated is almost unique in every way.

To instantiate an existing value, you use the constructors.

public Guid (byte[] b); //Accepts a 16-byte array
public Guid (string g); //Accepts a formatted string
//generate new unique Guid
Guid g = Guid.NewGuid ();
g.ToString().Dump ("Guid.NewGuid.ToString()");

Guid g1 = new Guid ("{0d57629c-7d6e-4847-97cb-9e2fc25083fe}");
Guid g2 = new Guid ("0d57629c7d6e484797cb9e2fc25083fe");
Console.WriteLine (g1 == g2);  // True

byte[] bytes = g.ToByteArray();
Guid g3 = new Guid (bytes);
g3.Dump();

Guid.Empty.Dump ("Guid.Empty");
default(Guid).Dump ("default(Guid)");
Guid.Empty.ToByteArray().Dump ("Guid.Empty - bytes");