How to read excel file using c#

Today I am going to show you how read excel file using c# and create folders based on excel file. Sometimes we may want to create many folders/sub folders based on business requirement and copy files into respective directory. So I am going to show how to do this using c# .net language. For this project i am using

  • Microsoft Visual Studio 2013
  • .net framework 4.5
  • ExcelDataReader library ( You can find it here)
  • Sample excel file which contain sample data set

Here is my project structure.

structure
Project Structure

Sample output

sample_output_parent
Parent folders
sample_output_child
Child folders

I have created sample console application to perform this task. I haven’t add exception handling for this code and feel free to optimize code and use it.

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FolderProject
{
    /// <summary>
    /// Author : Damith Wanninayake
    /// Excel 4.5 library url:https://github.com/ExcelDataReader/ExcelDataReader
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            FileStream stream = File.Open(ConfigurationManager.AppSettings["sourceFile"], FileMode.Open, FileAccess.Read);
            string basePath = ConfigurationManager.AppSettings["basePath"];

            // Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            // DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            // Getting distinct values
            var distinctValues = result.Tables[0].AsEnumerable()
                        .Select(row => new
                        {
                            Mem = row.Field<double>("Mem#"),

You can find the completed project which contains excel file in here. Please note that i am not the author of the excel file.

Create Responsive + Bootstrap + SignalR + ASP.NET MVC c# + AngularJS Real time dashboard

Hi all. Let’s create responsive dashboard using
– AngularJS
– Bootstrap
– SignalR
– Highcharts

This sample project based on tons of resources on internet.I have listed few of them below. You can download this project on GitHub (https://github.com/uwudamith/Dashboard). Feel free to download and modify the dashboard project.
Entire prototype has developed using SignalR services. Server will push data into the client without refreshing the browser. That is the beauty of Real Time applications. All charts are based on Highcharts. Layout based on Bootstrap css, so it is responsive by default.

Runtime

ServerPerformance

customer

Charts

SiganalR hub proxy factory

var signalRHubProxy = function ($rootScope, signalRServer) {
    
    function signalRHubProxyFactory(serverUrl, hubName, startOptions) {
        var connection = $.hubConnection(signalRServer);
        var proxy = connection.createHubProxy(hubName);
        connection.start(startOptions).done(function () { });

        return {
            on: function (eventName, callback) {
                proxy.on(eventName, function (result) {
                    $rootScope.$apply(function () {
                        if (callback) {
                            callback(result);
                        }
                    });
                });
            },
            off: function (eventName, callback) {
                proxy.off(eventName, function (result) {
                    $rootScope.$apply(function () {
                        if (callback) {
                            callback(result);
                        }
                    });
                });
            },
            invoke: function (methodName, callback) {
                proxy.invoke(methodName)
                    .done(function (result) {
                        $rootScope.$apply(function () {
                            if (callback) {
                                callback(result);
                            }
                        });
                    });
            },
            connection: connection
        };
    };

    return signalRHubProxyFactory;
};

Calling the factory from angular controller

var serverPerformanceController = function ($scope, signalRHubProxy) {

    $scope.RamCpu = {
        RamUsage: '0',
        CpuUsage: '0',
        Total:'0'
    };

    var clientPushHubProxy = signalRHubProxy(
       signalRHubProxy.defaultServer, 'MyHub',
           { logging: true });

    clientPushHubProxy.on('serverPerformanceDetails', function (data) {
        $scope.RamCpu = data;
        var x = clientPushHubProxy.connection.id;
    });
};

Injecting service into chart controller

// Declare angular application
var dashboardApp = angular.module('dashboardApp', ['ui.bootstrap', 'highcharts-ng']);

// Service declaration
dashboardApp.service('chartdataService', ['$rootScope', '$http', chartdataService]);

// Factory declaration
dashboardApp.factory('signalRHubProxy', ['$rootScope', signalRHubProxy]);

// Controllers declaration
dashboardApp.controller('chartController', ['$scope', '$log', '$timeout', '$interval', 'chartdataService', 'signalRHubProxy', chartController]);

Binding data into html pages

<div class="row" ng-controller="serverPerformanceController">
    <div class="col-md-3">
        <div class="panel panel-warning text-center">
            <div class="panel-body">
                <h1 class="text-info">{{RamCpu.RamUsage}} MB</h1>
            </div>
            <div class="panel-footer panel-info">Available RAM</div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="panel panel-warning text-center">
            <div class="panel-body">
                <h1 class="text-info">{{RamCpu.CpuUsage}} %</h1>
            </div>
            <div class="panel-footer panel-info">Total RAM</div>
        </div>
    </div>
</div>

Background timer application

public class BackgroundServerTimeTimer : IRegisteredObject
    {
        private readonly Timer _timer;
        private readonly Timer _timer1;
        private readonly IHubContext _hubContext;

        public BackgroundServerTimeTimer()
        {
            HostingEnvironment.RegisterObject(this);
            _hubContext = GlobalHost.ConnectionManager.GetHubContext();
            _timer = new Timer(OnTimerElapsed, null,TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(5));
            _timer1 = new Timer(OnTimerElapsed1, null, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(1));
        }

        private void OnTimerElapsed(object sender)
        {
            _hubContext.Clients.All.monthlyRainFall(DashboardData.GetChartDataForDashboard());
            _hubContext.Clients.All.customerInformations(DashboardData.GetCustomerInformations());
        }
        private void OnTimerElapsed1(object sender)
        {
            var server = new ServerPerformanceUtility();

           _hubContext.Clients.All.serverPerformanceDetails(server.GetServerPerformance());

        }

        public void Stop(bool immediate)
        {
            _timer.Dispose();
            _timer1.Dispose();
            HostingEnvironment.UnregisterObject(this);
        }
    }

Simple way to understand or explain dependency injection

Recently, I have been trying to learn about anjular.js and mean while i am reading this tutorial (http://learn-angular.org/#!/lessons/a-simple-app) i saw a great answer about describing what is dependency injection. This was the great and simple answer i have ever read. Some one has asked

How to explain dependency injection to a 5-year old?

and simple answer given by John Munsch was

When you go and get things out of the refrigerator for yourself, you can cause problems. You might leave the door open, you might get something Mommy or Daddy doesn’t want you to have. You might even be looking for something we don’t even have or which has expired.

What you should be doing is stating a need, “I need something to drink with lunch,” and then we will make sure you have something when you sit down to eat.

Wow that is great answer isn’t it?

What is jsPlumb

jsPlumb is a data visualization library available for most popular JavaScript libraries like jQuery, MooTools,Vanilla JS, YUI3. There are lot of features available on jsPlumb and has lots of examples over internet and they have a good documentation.

jsPlumb provides a means for a developer to visually connect elements on their web pages. It uses SVG or Canvas in modern browsers, and VML on IE 8 and below. The latest version is 1.6.4.

 

The project started out life on Google Code and was hosted there up until April 30, 2013. From May 1st, 2013, jsPlumb lives on GitHub only.

 

If you’re new to jsPlumb, please do take the time to read the documentation. There are a few integration issues that you should be aware of: z-index needs special attention, for example.

You can find running demos in here (http://jsplumb.org/demo/home/jquery.html) and licensed under the MIT license.

Browser Compatibility

jsPlumb runs on everything from IE6 up. There are some caveats, though, because of various browser/library bugs:

  • jQuery 1.6.x and 1.7.x have a bug in their SVG implementation for IE9 that causes hover events to not get fired.
  • jQuery 2.0 does not support IE 6,7 or 8.
  • Safari 5.1 has an SVG bug that prevents mouse events from being passed through the transparent area of an SVG element (Safari 6.x does not seem to have the same problem)
  • MooTools has a bug when using SVG in Firefox 11

 

Go ahead and try this awesome library for one of your projects

 

References

 

 

Develop high quality software using NDepend

I just tried this tool and it looks pretty powerful, I still have to dig further in it.It has lots of features like

  • Code Rule and Code Query over LINQ (CQLinq)
  • Trend Monitoring
  • Harness Test Coverage Data
  • Code Quality, 82 Code Metrics
  • Explore Existing Architecture
  • Detect Dependency Cycles
  • Compare Builds and Code Diff
  • Compare Builds and Code Diff
  • Complexity and Diagrams
  • Continuous Integration Reporting
  • Warnings on Build Process Health
  • NDepend.API and Power Tools
  • Real-World Proof

If you are really interested to increase the quality of your product, you better to try this tool. NDepend offers a wide range of features to let the user analyze a code base.
It is often described as a Swiss Army Knife for .NET developers.

http://www.ndepend.com/Default.aspx

 

Reveal js

Reveal js is very good framework for build web based presentations. It is very pretty and works in most of popular applications. It has lot of features like you don’t want any programming skills to design the presentation. The site says

reveal.js is a framework for easily creating beautiful presentations using HTML. You’ll need a browser with support for CSS 3D transforms to see it in its full glory.

Slides can be nested inside of other slides and there is a zoom feature also in the framework. It support themes and background colors. Go to Reveal js website

2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 57,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 21 sold-out performances for that many people to see it.

Click here to see the complete report.