Wednesday 26 November 2014

Create Xml from Excel

        private const string FileName = @"C:\myFile.xls";
        private const string XmlFileName = @"c:\\myFile.xml";
private static void CreateXmlFile()
        {           

            DataTable data = new ExcelReader().ReadFirstSheet(FileName);

            var xdoc = new XDocument();

            var root = new XElement("ReportEntitlementMappings");

            foreach (DataRow row in data.Rows)
            {
                var mapping = new XElement("Mapping");
                mapping.SetAttributeValue("From", row[0]);
                mapping.SetAttributeValue("To", row[1]);
                root.Add(mapping);
            }

            xdoc.Add(root);

            xdoc.Save(XmlFileName);
        }

 public interface IExcelReader
    {
        DataTable ReadFirstSheet(string fileName);
        DataTableCollection ReadAllSheets(string fileName);
    }

    public class ExcelReader : IExcelReader
    {
        const string ConnectionStringFormat = "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;";

        public DataTable ReadFirstSheet(string fileName)
        {
           return ReadAllSheets(fileName)[0];
        }

        public DataTableCollection ReadAllSheets(string fileName)
        {
            var adapter = new OleDbDataAdapter("SELECT * FROM [Mappings$]", ConnectionStringFormat.FormatWith(fileName));

            var ds = new DataSet();

            adapter.Fill(ds);

            return ds.Tables;
        }
    }

EPPLUS tool supports reading from Excel 2007 and 2010 files: http://epplus.codeplex.com/

Friday 21 November 2014

Why you should use ConcurrentBag with Parallel Foreach?

namespace ConsoleApp
{
    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;

    class Program
    {
        static void Main(string[] args)
        {
            var targetCount = 10000;
 
            // Shows 7456!
            RunThreadUnsafeDemo(targetCount);

            // Shows 10000
            RunThreadSafeDemo(targetCount);

            Console.ReadKey();
        }

        // The target count is less than actual.
        static void RunThreadUnsafeDemo(int count)
        {
            var sourceList = Enumerable.Range(1, count);
            var targetList = new List();          // Bad idea
            Parallel.ForEach(sourceList, i =>
            {
                targetList.Add(i);
            });

            Console.WriteLine("Thread Unsafe Count: {0}.", targetList.Count);
        }

        // The target count equals to actual.
        static void RunThreadSafeDemo(int count)
        {
            var sourceList = Enumerable.Range(1, count);
            var targetList = new ConcurrentBag(); // Good one
            Parallel.ForEach(sourceList, i =>
            {
                targetList.Add(i);
            });

            Console.WriteLine("Thread Safe Count: {0}.", targetList.Count);
        }
    }
}