BITE SIZE C# – LINQ

October 20, 2018October 20, 2018 KillerRoboticsC#, Development, Programming1 Comment

LINQ or Language Integrated Query is part of the Microsoft .NET Framework and it adds native data querying capabilities to .NET languages.

LINQ allows the user to query:

Objects in Memory (i.e. collections such as lists) using LINQ to Objects
Databases using LINQ to Entities
XML using LINQ to XML
ADO.Net Datasets using LINQ to Datasets

LINQ can either be implemented using predefined extension methods or alternatively using LINQ Query Operators.

Below are three examples utilising LINQ Extension methods:

In this example a list of Book objects is filtered to return only the Objects where the price is less than 10.

List<Book> cheapBooks = books.Where(b=>b.Price < 10);

In this example a list of Book objects is filtered to return only the Objects where the price is less than 10 and additionally the newly created list of Book objects is sorted alphabetically based on the Title field.

List<Book> cheapBooks = books.Where(b=>b.Price < 10).OrderBy(b.Title);

In this example the cheapBooks list is filtered to only return the Titles of the books therein and these Titles are then inserted into a new list of strings.

List<string> cheapBooksTitles = cheapBooks.Select(b=>b.Title);

Multiple extension methods can be combined to ascertain the desired results, for example:

List<string> cheapBooksTitles = books.Where(b=>b.Price).OrderBy(b.Title);
                                   .Select(b=>b.Title);

LINQ query operators tend to be slightly more verbose, and the above example can be implemented with query operators as follows:

List<string> cheapBooksTitles = from b in books
                         where b.Price < 10
                         orderby b.Title
                         select b.Title;

Some common extension methods are:

Single

var book = books.Single(b=>b.Title == ”Building Robots”);

Returns a single object that matches the defined criteria. However note that in the event that none or more than one book matches the criteria specified an exception will be thrown.

SingleOrDefault

var book = books.SingleOrDefault(b=>b.Title == ”Building Robots”);

Returns a single object that matches the defined criteria, if more than one book matches the criteria specified an exception will be thrown, however if no books match the criteria the default value defined will be returned.

First

Returns the first object that matches the criteria, however if no matches are found an exception is thrown.

FirstOrDefault

Returns the first object that matches the criteria and if no match is found the default value will be returned.

Last

Returns the last object that matches the criteria, however if no matches are found an exception is thrown.

LastOrDefault

Returns the last object that matches the criteria and if no match is found the default value will be returned.

Max

var maxValue = books.Max(b=>b.Price);

Max is used with numeric values and will return the highest value that is contained in the Price field in the Book objects.

Min

var minValue = books.Min(b=>b.Price);

Min is another operation used with numeric values and will return the smallest value that is contained in the Price field in the Book objects.

Sum

var totalPrice = books.Sum(b=>b.Price);

Sum is used to add up all the values in a numeric field.

There are many other methods available to filter and manipulate data in LINQ and the possibilities for the utilisation of LINQ are nearly endless, for example

var bookSelection = books.Skip(2).Take(3);

The above example will skip the first two books in the books list and take the next three placing them into the newly created bookSelection list.

The best option to gain a better insight of what is possible with LINQ is to give it a try.

BITE SIZE C# – LAMBDA EXPRESSIONS

October 13, 2018 KillerRoboticsC#, Development, ProgrammingLeave a comment

A lambda expression is a function definition with no name, no access modifier and no return statement. Lambda expressions are also known as Anonymous methods or functions.

The syntax for the definition of a lambda expressions is as follows:

(arguments) => expression

The below C# code does not use lambda expressions and is given as a comparative reference point:

static int SquareNumber(int number)
{
   return number*number;
}

static void Main(string[] args)
{
   Int y = SquareNumber(10);
   Console.WriteLine(y); // This will display 100
}

The above code can be refactored using a lambda expression as follows:

static void Main(string[] args)
{

   Func<int,int> square = number=>number*number;
   Console.WriteLine(square(10)); // This will display 100

}

The result of this refactor is fewer lines of code in order to achieve the same result.

Lambda expressions can also make use of Delegates as shows here:

delegate int squareDelegate (int number);

static void Main(string[] args)
{
   squareDelegate square = number=>number*number;
   Console.WriteLine(square(10)); // This will display 100
}

The syntax for a lambda expression that takes no arguments is as follows:

()=>expression

For one argument:

X=>expression

For more than one argument:

(x,y,z)=>expression

Lambda expressions can also be very effectively used with objects that implement the IEnumerable and IQueryable interfaces to filter or search based on defined criteria, for example:

var highPriceList = PriceList.FindAll(a=>a.Price>10);

This will return all items with a price larger than 10, and place these items in the highPriceList.

var nyCustomers = Customers.Where(a=>a.City==”New York”);

This will place all customers with the city property set to “New York” into the nyCustomers collections.

Lambda expressions are an extremely useful tool with many other uses.

Bite Size C# – Eventing\Observer Pattern

June 9, 2018 KillerRobotics.Net, C#, Development, ProgrammingLeave a comment

Let us have a look at the eventing or observer pattern. This pattern is based on the raising and handling of events. Events are a mechanism for the communication between object that allow us to build loosely coupled applications that can be easily extended.

At a high level this pattern functions as follows:
An object (known as the Publisher) defines a contract (delegate method) to which other objects (knows as Subscribers) must comply in order to be notified of a certain condition occurring in the Publisher object. This is achieved as follows, when a certain state is reached in the Publisher object an event will be raised, which will then trigger all the Subscriber objects to react by executing the method defined within the individual Subscriber objects that matches the delegate method as defined in the Publisher.

Now let us have a look at an example:

First let us create the Publisher, here we have to do three things:
1. Define a delegate which will act as the contract between the Publisher and Subscribers.
2. Define an event based on the delegate.
3. Raise the defined event.

public class KillerRobotPublisher
{
	public delegate void KillerRobotEventHandler(object source, EventArgs args);

	public event KillerRobotEventHandler KillerRobotAction;

	protected virtual void OnKillerRobotAction()
	 {
		if(KillerRobotAction!=null) //check if there are any subscribers
			{
				KillerRobotAction(this,EventArgs.Empty);
			}
	 }

	public void DoKillerRobotAction(string action)
	 {
		Console.WriteLine(“Killer Robot is doing “ +action);
		//add additional action logic here

		OnKillerRobotAction();
	}
}

Next let us create a Subscriber to subscribe to the KillerRobotAction event on the Publisher.

public class KillerRobotSubscriber
{
	public void OnKillerRobotAction(object source, EventArgs args)
     //this method conforms to the delegate defined in the Publisher
	 {
		Console.WriteLine(“The Killer Robot did something!”);
	 }
}

Now lastly let us create a basic application to instantiate the objects and to subscribe the Subscriber to the Publisher:

class Program
{
	static void Main(string[] args)
	{
		var killerRobotPub = new KillerRobotPublisher(); //Instantiate Publisher Object
		var killerRobotSub = new KillerRobotSubscriber(); //Instantiate Subscriber Object 

		killerRobotPub.KillerRobotAction += killerRobotSub.OnKillerRobotAction;
		//Subscribe the KillerRobotSubscriber to the KillerRobotAction event on the KillerRobotPublisher.

		killerRobotPub.DoKillerRobotAction(“A Dance”);
	}
}

The console output of this application will look like this:

console_Output

Bite Size C# – Extension Methods

February 3, 2018February 2, 2018 KillerRobotics.Net, C#, Development, ProgrammingLeave a comment

Extension methods allow us to add methods to existing classes without changing the class’ source code, nor by inheriting from the class. Extension methods are more relevant when wanting to add a method to a class from which one cannot inherit, such as sealed classes.
Just note, you cannot use extension methods with a static class as extension methods require an instance variable for an object in order to be utilised.
Let us look at an example, here we will add an extension method to the string class, which is a sealed class (i.e. you cannot inherit from this class).

class Program
{
	static void Main(string[] args)
	{
		string sentence=  “This is something a person would say.”;

		var robotSentence = sentence.ToRobot();
		Console.WriteLine(robotSentence);
	}
}

public static class StringExtensions
{
	public static string ToRobot(this string str)
	{
		if(String.IsNullOrEmpty(str)) return str;
		var words = str.Split(‘ ‘);
		var robotStr = String.Empty;

		foreach(var word in words)
		{
			if(word.Length > 4)
			{
				robotStr+=“BEEP “;
			} else {
				robotStr+=“BOOP ”;
			}
		}

		return robotStr.Trim();
	}
}

Also note that extension methods must be non-generic static methods defined in a static class.

Bite Size C# – Delegates

January 27, 2018 KillerRobotics.Net, C#, Development, ProgrammingLeave a comment

A delegate is a form of a type-safe function pointer. More simply put, it is an object that knows how to call a method, i.e. a reference to a method. Delegates are useful as they assist us in writing flexible and extendable applications.

Delegates are useful when using the eventing design pattern also known as the observer pattern. The eventing or observer pattern consists of an object, called the subject or publisher, which maintains a list of dependent objects, called observers or subscribers, and notifies them automatically of a state change in the subject-object by raising an event and then calling a method on the observer objects by using a delegate. We will cover this pattern in more detail when I cover events in a separate post.

For now, let us simply focus on delegates.

So, let us look at an example:


public class KillerRobot
{
	public string Name { get; set; }
}

public class KillerRobotActuator
{
	public delegate void KillerRobotActionHandler(KillerRobot robot); 	//Declare delegate which will act as a
															//signature for methods that can be
															//referenced.

	public void DoAction(string robotName, KillerRobotActionHandler actionHandler)
	{
		var robot = new KillerRobot {Name = robotName	};
		actionHandler(robot);
	}
}

public class RobotActions
{
	public void RobotTalk(KillerRobot robot)
	{
		Console.WriteLine(“{0} is Talking.”, robot.Name);
	}

	public void RobotDance(KillerRobot robot)
	{
		Console.WriteLine(“{0} is Dancing.”, robot.Name);
	}
}

class Program
{
	static void Main(string[] args)
	{
		var robotActuator = new KillerRobotActuator();
		var robotActions = new RobotActions();
		KillerRobotActuator.KillerRobotActionHandler actionHandler = robotActions.RobotTalk;
		actionHandler += robotActions.RobotDance;
		actionHandler += RobotPowerOff;

		robotActuator.DoAction(“The Geek”,actionHandler);
	}

	static void RobotPowerOff(KillerRobot robot)
	{
		Console.WriteLine(“{0} has turned off.”, robot.Name);
	}
}

So, in this basic example we have a class KillerRobotActuator which declares a delegate:

public delegate void KillerRobotActionHandler(KillerRobot robot);

Any method that complies to this signature can then be added to this delegate and in the DoAction method where the following is executed:

actionHandler(robot);

All the methods that have been added to the actionHandler will then be executed.
We can see in the Main method of the Program class that a new KillerRobotActionHandler is declared and three method references are added to it as below:

KillerRobotActuator.KillerRobotActionHandler actionHandler = robotActions.RobotTalk;
actionHandler += robotActions.RobotDance;
actionHandler += RobotPowerOff;

And then finally the DoAction method on KillerRobotActuator is executed, passing in the above declared actionHandler containing references to the three methods:

robotActuator.DoAction(“The Geek”,actionHandler);

All three the methods that are referenced by the actionHandler comply to the signature defined in the delegate declaration in the KillerRobotActuator class. i.e. a void return type and an input parameter of type KillerRobot.
It is also worth mentioning that of the methods referenced, two are contained in a separate class RobotActions and the third is a static method declared in the Program class, so methods from multiple different class locations can be added as long as they comply to the signature of the delegate declares.

Bite Size C# – Generics

January 20, 2018January 20, 2018 KillerRobotics.Net, C#, Development, Programming3 Comments

Generics refer to the method of creating classes and methods in a way that defers the specification of the type or types associated with the class or method until it is declared and instantiated.

What this means in plain english is that you can define a single class or method that can be utilised with multiple types, thus resulting in less and tidier code.

So let us have a look at an example. Firstly let us look at some code that is not generic. Here we have a class which consist of a list of integers, it has a default constructor as well as two methods, one to add an integer to the list and the other to return the sum of all the integers in the list.

public class KillerRoboticsIntList
{
   public List<int> IntList { get; set; }

    public KillerRoboticsIntList()
    {
        IntList = new List<int>();
    }

    public void Add(int item)
    {
        IntList.Add(item);
    }

    public int Sum()
   {
	int sum;
	foreach(int i in IntList)
	{
	 sum += i;
	}
	return sum;
   }

}

This is a very basic example and can be utilised as follows:

class Program
        {
            public static void Main()
            {
                KillerRoboticsIntList iList = new KillerRoboticsIntList();
		        iList.Add(1);
		        iList.Add(2);
		        iList.Add(3);
		        iList.Add(4);
		        int sum = iList.Sum();
                Console.WriteLine(“Sum = {0}.", sum);

            }
        }

This is very straight forward, however if I would like to use this class with a data type other than int (for example a float or a double) I would need to define a new class for each data type. This can be overcome by utilising Generics. Below is an example of how this can be implemented:

public class KillerRoboticsGenericList<T>
{
   public List GenList<T> { get; set; }

    public KillerRoboticsGenericList()
    {
        GenList = new List<T>();
    }

    public void Add(T item)
    {
        GenList.Add(item);
    }

    public T Sum()
   {
	T sum;
	foreach(T i in GenList)
	{
	 sum += i;
	}
	return sum;
   }

}

The above class can now be defined for various types as seen below:

class Program
        {
            public static void Main()
            {
              KillerRoboticsIntList<int> intList = new KillerRoboticsIntList<int>();
		        intList.Add(1);
		        intList.Add(2);
		        intList.Add(3);
		        intList.Add(4);
		        int sum = intList.Sum();
                Console.WriteLine(“Int Sum = {0}.", sum);

		        KillerRoboticsIntList<double> = new KillerRoboticsIntList<double>();
		        doubleList.Add(0.1);
		        doubleList.Add(2.2);
		        doubleList.Add(3.0);
		        doubleList.Add(1.4);
		        double dSum = doubleList.Sum();
                Console.WriteLine(“Double Sum = {0}.", dSum);

            }
        }

In some cases you might want to put a restriction on the types that can be utilised to implement a generic class of method, this can be achieved by utilising constraints. For example the class we defined above can be restricted to types that implement the IComparable interface by simple changing the first line as follows:

public class KillerRoboticsGenericList<T> where T : IComparable

Some additional examples of constraints are:

where T : Product

This restricts the type of T to an implementation of the class Product or any of its child classes.

where T : struct

This restricts the type of T to the value type struct.

where T : new()

This restricts the type of T to an object with a default constructor.

Multiple Constraints can also be appled at the same time, for example:

 
public class KillerRoboticsGenericList<T> where T : IComparable, new()

A Generic class can be defined to utilise multiple types, for example:

 
public class KillerRoboticsGenericList<T,U,V>

It is also worth mentioning that the default c# List class used in the above examples also utilises generics, along with all the other predefined c# collections contained in System.Collections.Generic.

I hope this post has been useful and I will be posting on some additional C# topics, such as Delegates, Lamda Expressions, LINQ, Extension Methods, etc. over the next few months.

Bite Size C# – DataTable to RecordSet Converter

August 5, 2015August 5, 2015 KillerRobotics.Net, C#, Database, Development, ProgrammingLeave a comment

When working on legacy systems you may sometimes find that a wide variety of technologies have been used, and short of rewriting a huge amount of code the only thing that can be done is to create some functionality to map between the different technologies utilised.

I had a scenario where I had to integrate 2 legacy applications utilising different technologies for database access, one utilised ADODB and the other ADO.NET. Due to time constraints I could not change the underlying code of the systems and rather decided to create a helper class to map an ADO.NET DataTable to an ADODB RecordSet in order to facilitate the integration.

My DTtoRSconvert is a static class that resides in the Core.Data.Help namespace. It contains a static method ConvertToRecordSet that takes a DataTable as a parameter and returns a RecordSet. It also has one static private method TranslateType which is used to map between the DataTable and RecordSet Data types.

Here is the code:

using System.Data;
using System.Reflection;
using ADODB;

namespace Core.Data.Helper
{
    //Helper class that convert ADO.Net DataTables to ADODB RecordSets
    public static class DTtoRSconvert
    {
        public static Recordset ConvertToRecordSet(DataTable inTable)
        {
            var recordSet = new Recordset { CursorLocation = CursorLocationEnum.adUseClient };

            var recordSetFields = recordSet.Fields;
            var inColumns = inTable.Columns;

            foreach (DataColumn column in inColumns)
            {
                recordSetFields.Append(column.ColumnName
                                    , TranslateType(column.DataType)
                                    , column.MaxLength
                                    , column.AllowDBNull
                                          ? FieldAttributeEnum.adFldIsNullable
                                          : FieldAttributeEnum.adFldUnspecified
                                    , null);
            }

            recordSet.Open(Missing.Value
                        , Missing.Value
                        , CursorTypeEnum.adOpenStatic
                        , LockTypeEnum.adLockOptimistic, 0);

            foreach (DataRow row in inTable.Rows)
            {
                recordSet.AddNew(Missing.Value,
                              Missing.Value);

                for (var columnIndex = 0; columnIndex &lt; inColumns.Count; columnIndex++)
                {
                    recordSetFields[columnIndex].Value = row[columnIndex];
                }
            }

            return recordSet;
        }

        private static DataTypeEnum TranslateType(IReflect columnDataType)
        {
            switch (columnDataType.UnderlyingSystemType.ToString())
            {
                case "System.Boolean":
                    return DataTypeEnum.adBoolean;

                case "System.Byte":
                    return DataTypeEnum.adUnsignedTinyInt;

                case "System.Char":
                    return DataTypeEnum.adChar;

                case "System.DateTime":
                    return DataTypeEnum.adDate;

                case "System.Decimal":
                    return DataTypeEnum.adCurrency;

                case "System.Double":
                    return DataTypeEnum.adDouble;

                case "System.Int16":
                    return DataTypeEnum.adSmallInt;

                case "System.Int32":
                    return DataTypeEnum.adInteger;

                case "System.Int64":
                    return DataTypeEnum.adBigInt;

                case "System.SByte":
                    return DataTypeEnum.adTinyInt;

                case "System.Single":
                    return DataTypeEnum.adSingle;

                case "System.UInt16":
                    return DataTypeEnum.adUnsignedSmallInt;

                case "System.UInt32":
                    return DataTypeEnum.adUnsignedInt;

                case "System.UInt64":
                    return DataTypeEnum.adUnsignedBigInt;

                //System.String will also be handled by default
                default: 
                    return DataTypeEnum.adVarChar;
            }
        }
    }
}

Bite Size C# – XLS File Merger

July 30, 2015July 30, 2015 KillerRobotics.Net, C#, Development, ProgrammingLeave a comment

This will be the last Bite Size C# post revolving around CSV and XLS files (for a while at least). The next one will be on a different and probably a bit more exciting topic.

Today we will look at my XLS File Merger, which resides in the Core.XLS namespace of my Core.dll library. It is a static class with one static method MergeFiles. The purpose of this method is to merge 2 or more XLS files into a single file, i.e. taking the worksheets of 2 or more files and combing them into a single file. The MergeFiles method takes 2 arguments: a list of locations of files to be merged (filePathList) and the destination file that will be created (outputFile).

Here is the code:

using System;
using Microsoft.Office.Interop.Excel;

namespace Core.XLS
{
    public static class XlsFileMerge
    {
        public static void MergeFiles(string[] filePathList, string outputFile)
        {
            var app = new Application {Visible = false};

            app.Workbooks.Add("");
            foreach (var file in filePathList)
            {
                app.Workbooks.Add(file);
            }


            for (var i = 2; i &lt;= app.Workbooks.Count; i++)
            {
                var count = app.Workbooks[i].Worksheets.Count;

                app.Workbooks[i].Activate();
                for (var j = 1; j &lt;= count; j++)
                {
                    var ws = (_Worksheet) app.Workbooks[i].Worksheets[j];
                    if (ws.UsedRange.Rows.Count &lt;= 1) continue;
                    ws.Select(Type.Missing);
                    ws.Cells.Select();


                    var sel = (Range) app.Selection;
                    sel.Copy(Type.Missing);

                    var sheet = (_Worksheet) app.Workbooks[1].Worksheets.Add(
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                                 );
                    sheet.Name = ws.Name;
                    sheet.Paste(Type.Missing, Type.Missing);
                }
            }
            app.Workbooks[1].SaveAs(@outputFile);
            app.Quit();
        }
    }
}

Bite Size C# – XLS Creator

July 21, 2015July 27, 2015 KillerRobotics.Net, C#, Development, Programming3 Comments

Today we will look at my XLS Creator Class. It is very similar to my CSV Creator class discussed in an earlier post (BITE SIZE C# – CSV FILE CREATOR), except that it creates a well formed XLS file instead of a CSV file.

It is a static class contained in the Core.XLS namespace and contains 1 static method ExportToExcel. Like the CSV Creator class it uses a generic list of objects to construct a XLS file. The method also takes 2 string values xlsNameWithExt and sheetName.

xlsNameWithExt which is used to define the name and location to save the created XLS file and sheetName which is used to set the sheet name the data will be inserted on.

Here is the code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Core.XLS
{
  public static class XlsCreator
  {
  public static void ExportToExcel<T>(List<T> list, string xlsNameWithExt, string sheetName)
  {
  var columnCount = 0;

  var StartTime = DateTime.Now;

  var rowData = new StringBuilder();

  var properties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

  rowData.Append("<Row ss:StyleID=\"s62\">");
  foreach (var p in properties)
  {
  if (p.Name.ToUpper() == "ENTITYSTATE" || p.Name.ToUpper() == "ENTITYKEY")
    continue;

  if (p.PropertyType.Name != "EntityCollection`1" && p.PropertyType.Name != "EntityReference`1")
  {
    var type = "String";
    columnCount++;
    rowData.Append("<Cell><Data ss:Type=\"" + type + "\">" + p.Name + "</Data></Cell>");
  }
  else
    break;
  }
  rowData.Append("</Row>");

  foreach (var item in list)
  {
  rowData.Append("<Row>");
  for (var x = 0; x < columnCount; x++) //each (PropertyInfo p in properties)
  {
    var o = properties[x].GetValue(item, null);
    var value = o == null ? "" : o.ToString();
    rowData.Append("<Cell><Data ss:Type=\"String\">" + value + "</Data></Cell>");
  }
  rowData.Append("</Row>");
  }

  var sheet = @"<?xml version=""1.0""?>
    <?mso-application progid=""Excel.Sheet""?>
    <Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
    xmlns:o=""urn:schemas-microsoft-com:office:office""
    xmlns:x=""urn:schemas-microsoft-com:office:excel""
    xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
    xmlns:html=""http://www.w3.org/TR/REC-html40"">
    <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
    <Author>MSADMIN</Author>
    <LastAuthor>MSADMIN</LastAuthor>
    <Created>2011-07-12T23:40:11Z</Created>
    <Company>Microsoft</Company>
    <Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
    <WindowHeight>6600</WindowHeight>
    <WindowWidth>12255</WindowWidth>
    <WindowTopX>0</WindowTopX>
    <WindowTopY>60</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID=""Default"" ss:Name=""Normal"">
    <Alignment ss:Vertical=""Bottom""/>
    <Borders/>
    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    <Style ss:ID=""s62"">
    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""
    ss:Bold=""1""/>
    </Style>
    </Styles>
    <Worksheet ss:Name=""" + sheetName + @""">
    <Table ss:ExpandedColumnCount=""" + (properties.Count() + 1) + @""" ss:ExpandedRowCount=""" +
    (list.Count() + 1) + @""" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    " + rowData + @"
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
    </Print>
    <Selected/>
    <Panes>
    <Pane>
      <Number>3</Number>
      <ActiveCol>2</ActiveCol>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name=""Sheet2"">
    <Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name=""Sheet3"">
    <Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>";

  using (var sw = new StreamWriter(xlsNameWithExt))
  {
  sw.Write(sheet);
  }
  }
  }
}

Bite Size C# – XLS to CSV Converter

July 16, 2015July 27, 2015 KillerRobotics.Net, C#, Development, ProgrammingLeave a comment

Let us have a look at my XLS to CSV Converter class today.

It is a static class, which is located in the Core.CSV namespace. It contains one static method CovertExcelToCsv which takes 3 parameters excelFilePath, csvOutputFile and worksheetNumber. The excelFilePath parameter is used to input the source Excel file, csvOutputFile is used to input the destination CSV file and lastly worksheetNumber is used to define which worksheet to convert, as CSV files do not have worksheets only one worksheet can be converted to CSV file at a time. The worksheetNumber parameter has a default value of 1 in the event that no value is passed in.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Core.CSV
{
 public static class XlsToCsvConverter
 {
  public static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
  {
   if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
   if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

   // Connection string
   var connectionStr =
    String.Format(
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"",
     excelFilePath);
   var cnn = new OleDbConnection(connectionStr);

   // Get schema information and then data in spreadsheet
   var dt = new DataTable();
   try
   {
    cnn.Open();
    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (schemaTable != null && schemaTable.Rows.Count < worksheetNumber)
     throw new ArgumentException("The worksheet number requested does not exist.");
    if (schemaTable != null)
    {
     var worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
     var sql = String.Format("select * from [{0}]", worksheet);
     var da = new OleDbDataAdapter(sql, cnn);
     da.Fill(dt);
    }
   }
   catch (Exception ex)
   {
    //Insert logging of exception here
    throw;
   }
   finally
   {
    // Close connection to the spreadsheet
    cnn.Close();
   }

   // Write out CSV data
   using (var wtr = new StreamWriter(csvOutputFile))
   {
    foreach (DataRow row in dt.Rows)
    {
     var firstLine = true;
     foreach (DataColumn col in dt.Columns)
     {
      if (!firstLine)
      {
       wtr.Write(",");
      }
      else
      {
       firstLine = false;
      }
      var data = row[col.ColumnName].ToString().Replace("\"", "");
      wtr.Write(String.Format("{0}", data));
     }
     wtr.WriteLine();
    }
   }
  }
 }
}

Killer Robotics

Adventures in Robotics, Development and Electronics.

Menu

C#

BITE SIZE C# – LINQ

BITE SIZE C# – LAMBDA EXPRESSIONS

Bite Size C# – Eventing\Observer Pattern

Bite Size C# – Extension Methods

Bite Size C# – Delegates

Bite Size C# – Generics

Bite Size C# – DataTable to RecordSet Converter

Bite Size C# – XLS File Merger

Bite Size C# – XLS Creator

Bite Size C# – XLS to CSV Converter