NovelEssay.com Programming Blog

Exploration of Big Data, Machine Learning, Natural Language Processing, and other fun problems.

Creating an Excel report with C# using a MySql data source

This blog article will share how to create a multi-worksheet Excel workbook file using C# that fetches report data from a MySql data source.


First, create a Visual Studio C# project and install the EPPlus and MySql.Data Nuget packages.




Next, get yourself a MySqlConnection object connected to your MySql server. If you have a specific database, you'll want to add that to your connection string. My case didn't need to do that. Obviously, change your user, password, and server values to whatever makes sense for your environment. 

                string connstring = string.Format("Server={0}; UID={1}; password={2};default command timeout={3};", serverName, userName, Password, commandTimeout);
                MySqlConnection connection = new MySqlConnection(connstring);
                connection.Open();

Next, you set up an ExcelPackage object and create worksheets as the code below shows. Notice how the column headers are set and styled.

Then, I fetch my Sql query from a helper class and execute that using a MySqlCommand. The results of my query are fetched using the data reader. As it iterates through the data reader, the code sets the query result values in to specific Excel cell locations using the row and column index values.


                string eppOutput = @"C:\myEppOutput.xlsx";
                FileInfo fi = new FileInfo(eppOutput);
                using (ExcelPackage package = new ExcelPackage(fi))
                {
                    // I two queries that run. Results are put on separate worksheets.
                    for (int queryIdx = 1; queryIdx <= 2; queryIdx++)
                    {
                        string worksheetName = "Worksheet" + queryIdx;
                        ExcelWorksheet ws = package.Workbook.Worksheets.Add(worksheetName);
                        // Each page has this four column headers:
                        // Set worksheet's column headers
                        ws.Cells["A1"].Value = "ColumnA";
                        ws.Cells["B1"].Value = "ColumnB";
                        ws.Cells["C1"].Value = "ColumnC";
                        ws.Cells["D1"].Value = "ColumnD";
                        // Make column headers bold
                        ws.Cells["A1:D1"].Style.Font.Bold = true;
                        query = Utils.GetDatabaseQueryString(queryIdx);
                        cmd = new MySqlCommand(query, dbCon.Connection);
                        int row = 2;
                        int col = 1;
                        using (var dataReader = cmd.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                col = 1;
                                ws.SetValue(row, col++, dataReader.GetString(0));
// Column B and C are always integer values:
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(1)));
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(2)));
                                ws.SetValue(row, col++, dataReader.GetString(3));
                                row++;
                            }
                        }
                    }
                    package.Save();
                }


Note that the col index resets each pass through the dataReader.Read, and the row index resets each time I create a new worksheet.

Finally, we can call package.Save to write out my ExcelPackage object to a file specified in the argument string that we passed to its constructor.



Writing a Windows Service with TopShelf

This article will show the C# code to use the TopShelf framework to write a Windows Service.


First, use Visual Studio and create a new C# console application and install the TopShelf nuget package.


Next, change your Main function to look roughly like this:

    public class Program
    {
        public static void Main()
        {
            TownCrier tc = new TownCrier();
            tc.Start();
           
            HostFactory.Run(x =>                                 //1
            {
                x.Service<TownCrier>(s =>                        //2
                {
                    s.ConstructUsing(name => new TownCrier());     //3
                    s.WhenStarted(tc => tc.Start());              //4
                    s.WhenStopped(tc => tc.Stop());               //5
                });
                x.RunAsLocalSystem();                            //6
                x.SetDescription("NovelEssayAgent v" + Assembly.GetExecutingAssembly().GetName().Version);        //7
                x.SetDisplayName("NovelEssayAgent");                       //8
                x.SetServiceName("NovelEssayAgent");                       //9
                x.AfterInstall(() => NotificationHelper.DoAfterInstall());
                x.BeforeUninstall(() => NotificationHelper.DoBeforeUninstall());
            });                                                  //10
             
            return;
        }
    }

Now, you should be asking:

  1. What is the NotificationHelper?
  2. What is the TownCrier?

The NotificationHelper is a class that lets you handle before and after install events. I'm simple writing out some log messages on those events, but you might want to automate additional deployment tasks in those events.

    static public class NotificationHelper
    {
        static public void DoAfterInstall()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was installed.", EventLogEntryType.Information);
        }
        static public void DoBeforeUninstall()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was uninstalled.", EventLogEntryType.Information);
        }
    }


The TownCrier is a class that I setup a never ending timer that polls looking for work. It also handles the OnStart and OnStop service events, so I can log or do other tasks when those events happen.


The EssayMgr class encapsulates work being done. EssayMgr's RunLifeCycle function is called every hbSetting seconds. You can make that amount of time configurable in many different ways (database, config file, etc...).

    public class TownCrier
    {
        private static EssayMgr mMgr = new EssayMgr();
        readonly System.Timers.Timer _timer;
        /// <summary>
        /// The constructor sets up a timer that will call RunLifeCycle when it fires.
        /// </summary>
        public TownCrier()
        {
            int hbSetting = 1;
            _timer = new System.Timers.Timer(hbSetting * 1000) { AutoReset = true };
            _timer.Elapsed += (sender, eventArgs) => mMgr.RunLifeCycle();
        }
        public void Start()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was started.", EventLogEntryType.Information);
            _timer.Start();
        }
        public void Stop()
        {
            _timer.Stop();
            mMgr.StopAll();
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was stopped.", EventLogEntryType.Information);
        }
    }


Add some code to do some work in the EssayMgr.RunLifeCycle function, and that's you've created your very own C# Windows Service!


Your code should build in to an exe, and you can installer and start your service using topshelf's commands on your executable via the command line:

http://docs.topshelf-project.com/en/latest/overview/commandline.html




Mvc.Net with AngularJS and Pagination

My personal current "goto" for web based pagination uses angular-paginate-anything plugin with a Mvc.Net backend. As with most web development tools, there's many ways to use something like this. This blog will show one full example of how to do Mvc.Net with AngularJS and Pagination using angular-paginate-anything.


I'm going to try and keep this very simple, so excuse some of the nuance being skipped.

0) Create a MVC.Net project and a controller/view. I'll leave that outside the scope of this article.


1) Create an AngularJS App & Controller. Nothing fancy. Just simple stuff:

Link the AngularJS:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.3.15/angular.min.js"></script>
Create a JS file with the AngularJS code:
var app = angular.module('MyPersonalApp', ['bgf.paginateAnything']);
app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
...
}]);
In your cshtml view, link your new javascript file, and make some divs that hold your app:
<script src="~/Scripts/Search/MyPersonalApp.js"></script>
<div id="MyPersonalAppId" ng-app="MyPersonalApp">
    <div ng-controller="MyPersonalCtrl" ng-cloak>
...


2) Download and link the javascript in your view (cshtml).

        <script src="~/Scripts/paginate-anything-tpls.js"></script>


3) In side your app divs, add a table to hold the paginated results and bgf-pagination markup like this:

                            <table>
                                <tr>
                                    <th>Name</th>
                                </tr>
                                <tr ng-repeat="result in searchResults">
                                    <td>{{result.Name}}</td>
                                </tr>
                            </table>
                            <bgf-pagination collection="searchResults"
                                            url="'/api/GetMySearchResults'"
                                            url-params="searchParams"
                                            num-items="searchResultsTotalItems">
                            </bgf-pagination>


4) You'll notice that the bgf-pagination references several things. First, lets look at the searchParams. Ideally, you'll want to hook that up to a searchbox of some sort. For now, we'll hard code a query string to the searchParams in our angular controller like this:

app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
    $scope.searchParams = {};
    $scope.searchParams.queryName= "Jo";


5) Next, notice that /api/GetFreeSearchResults URL points at a web API endpoint. We'll set that up by installing the WebAPI Nuget project in our MVC.Net project.

    


6) Create new WebAPI Controller in your project, and make it look roughly like this:

    [RoutePrefix("api")]
    public class SearchApiController : ApiController
    {
        MyDbEntities _db;

        public SearchApiController()
        {
            _db = new MyDbEntities();
_db.Configuration.ProxyCreationEnabled = false; } [Route("GetMySearchResults"), HttpGet()]
[WithPaging] public IHttpActionResult Search([FromUri] PagingModeFreeSearch pagingModel , [FromUri] string queryName = null
) { string localQueryName = string.Empty; if(!string.IsNullOrEmpty(queryName ))
{ localQueryName = queryName;
} var peopleResultsTotal= _db.People.Where(t => t.Name.ToLower().Contains(localQueryName));
var peopleResultsSubset = peopleResultsTotal.OrderBy(o => o.Id).Skip(pagingModel.From).Take(pagingModel.Size);
PagingModeMySearch result = new PagingModeMySearch();
result.From = pagingModel.From; result.To = pagingModel.To; result.TotalRecordCount = peopleResultsTotal.Count();
result.People = peopleResultsSubset.ToArray();
return this.PagedPartialContent(result.People , result.From, result.To, (int)result.TotalRecordCount);
} }

Notice the routing annotations are setup. This also assumes you have a DB with a table named People as your MyDbEntities data source.


7) Where does the [WithPaging] annotation come from? Make a file named NgPaginateModels.cs, and use this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Filters;
using System.Web.Http.Results;
namespace MyProject.MVC.Models
{
    // Found this awesome sauce here:
    // https://github.com/begriffs/angular-paginate-anything/wiki/How-To-Configure-ASP.NET-Web-API
    public class WithPagingAttribute : ActionFilterAttribute
    {
        private readonly string _paramName;
        private static readonly Regex RangeHeaderRegex = new Regex(@"(?<from>\d+)-(?<to>\d*)", RegexOptions.Compiled);
        public WithPagingAttribute(string paramName = "pagingModel")
        {
            _paramName = paramName;
        }
        public override void OnActionExecuting(HttpActionContext actionContext)
        {
            object data;
            if (actionContext.ActionArguments.TryGetValue(_paramName, out data))
            {
                //manipulate/inject paging info from headers
                var pagingModel = data as PagingModel;
                pagingModel = IncomingPagingModel(actionContext.Request.Headers, pagingModel);
                actionContext.ActionArguments[_paramName] = pagingModel;
            }
        }
        private static PagingModel IncomingPagingModel(HttpRequestHeaders headers, PagingModel pagingModel)
        {
            if (pagingModel == null)
                pagingModel = new PagingModel();
            IEnumerable<string> rangeUnitValues;
            IEnumerable<string> rangeValues;
            if (headers.TryGetValues("Range-Unit", out rangeUnitValues)
                && rangeUnitValues.First().Equals("items", StringComparison.OrdinalIgnoreCase)
                && headers.TryGetValues("Range", out rangeValues))
            {
                var rangeHeaderMatch = RangeHeaderRegex.Match(rangeValues.First());
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["from"].Value))
                    pagingModel.From = int.Parse(rangeHeaderMatch.Groups["from"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["to"].Value))
                    pagingModel.To = int.Parse(rangeHeaderMatch.Groups["to"].Value);
            }
            return pagingModel;
        }
    }
    public class PartialNegotiatedContentResult<T> : OkNegotiatedContentResult<T>
    {
        readonly int _requestedFrom;
        readonly int _requestedTo;
        readonly int _totalCount;
        public PartialNegotiatedContentResult(T content, ApiController controller, int requestedFrom, int requestedTo, int totalCount)
            : base(content, controller)
        {
            _requestedFrom = requestedFrom;
            _requestedTo = requestedTo;
            _totalCount = totalCount;
            if (_requestedTo > _totalCount && _totalCount > 0)
                _requestedTo = _totalCount;
        }
        public override async Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
        {
            var response = await base.ExecuteAsync(cancellationToken);
            const string unit = "items";
            response.Headers.Add("Accept-Ranges", unit);
            response.Headers.Add("Range-Unit", unit);
            if (_totalCount > 0)
            {
                response.StatusCode = HttpStatusCode.PartialContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(_requestedFrom, _requestedTo, _totalCount)
                {
                    Unit = unit
                };
            }
            else
            {
                response.StatusCode = HttpStatusCode.NoContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(0);
            }
            return response;
        }
    }
    public static class ApiControllerExtensions
    {
        public static IHttpActionResult PagedPartialContent<T>(
            this ApiController controller,
            IEnumerable<T> content, int from, int to, int count) where T : class
        {
            return new PartialNegotiatedContentResult<IEnumerable<T>>(content, controller, from, to, count);
        }
    }
}


8) What about the PagingModel class? Create a PagingModel base class, and then inherit from it like this:

    public class PagingModeMySearch: PagingModel
    {
        public PagingModeMySearch()
        {
        }
        public Person[] People { get; set; }
    }
    public class PagingModel
    {
        public PagingModel()
        {
            From = 0;
            To = 4;
        }
        public int From { get; set; }
        public int To { get; set; }
        public long TotalRecordCount { get; set; }
        public int Page { get { return (To / Size) + 1; } }
        public int Size { get { return To - From + 1; } }
    }


If you spin this up, your web page should load and the table should show Names like "Joe", "Joseph", "John", etc... with the hard coded "Jo" search string we put in our AngularJS controller.


That's really about all there is to making this work. The rest is search controls and styling up the results.


You can get angular-paginate-anything on Github here:

https://github.com/begriffs/angular-paginate-anything