Console Logging and Reading Excel Files with .NET Core 2

Introduction

In this article, I've focused on only two things:

• Proper Logging: The primary goal is to have a console application in .NET Core 2 with a configurable and nice logging that, also, we want to have logging persistence using a file.
• Read Excel files: The second purpose of this Proof of Concept is, indeed, what led me to sit in front of the keyboard on a Saturday morning, that I need to read information from an Excel file using .NET Core.

Background

I write this article today because I’ve been trying to find useful information regarding this topic and I had to surf through the Internet many times going back and forth from a big group of related articles that none of them could help me achieve my goals completely.

Using the Code

The code is self-explanatory, just consider it as a POC I started to myself. It is going to be the starting point of a small utility that my team and I need to develop during the next week, but feel free to keep the concepts and use it as a starting point for your .NET core utilities.

Note: I don’t want you to get bored with the history, so I’ll just share the source code that gave me results and some explanations at the end. I hope it helps.

Proper Logging with .NET Core 2

This code snippets apply for .NET Core Console Applications but might also be slightly modified and used in ASP NET Core projects too. As stated before, the goal is to have nice console logging, but also we want to configure the log level and to have a separate mechanism to log to a file adding logging persistence to our applications.

Solution Structure and Dependencies

Program.cs

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Serilog;
using Serilog.Events;
using System;
 
namespace ExcelDataReaderPoc
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create service collection
            var serviceCollection = new ServiceCollection();
            ConfigureServices(serviceCollection);
 
            // Create service provider
            var serviceProvider = serviceCollection.BuildServiceProvider();
 
            // Run app
            serviceProvider.GetService<App>().Run();
        }
 
        private static void ConfigureServices(IServiceCollection serviceCollection)
        {
 
            // Build configuration
            var configuration = new ConfigurationBuilder()
                .SetBasePath(AppContext.BaseDirectory)
                .AddJsonFile("appsettings.json", false)
                .Build();
 
            // Add console logging
            serviceCollection.AddSingleton(new LoggerFactory()
                .AddConsole(configuration.GetSection("Logging"))
                .AddSerilog()
                .AddDebug());
            serviceCollection.AddLogging();
 
            // Add Serilog logging           
            Log.Logger = new LoggerConfiguration()
            .ReadFrom.Configuration(configuration)
            .MinimumLevel.Override("Microsoft", LogEventLevel.Information)
            .WriteTo.RollingFile(configuration["Serilog:LogFile"])
            .CreateLogger();
 
            // Add access to generic IConfigurationRoot
            serviceCollection.AddSingleton(configuration);
 
            // Add the App
            serviceCollection.AddTransient<App>();
        }
    }
}

App.cs

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;
using System;
using System.IO;
using System.Text;
 
namespace ExcelDataReaderPoc
{
    public class App
    {
        private readonly ILogger<App> _logger;
        private readonly IConfigurationRoot _config;
 
        public App(ILogger<App> logger, IConfigurationRoot config)
        {
            _logger = logger;
            _config = config;
        }
 
        public void Run()
        {
            // Let's test log levels:
            _logger.LogTrace("LogTrace");
            _logger.LogDebug("LogDebug");
            _logger.LogInformation("LogInformation");
            _logger.LogWarning("LogWarning");
            _logger.LogError("LogError");
            _logger.LogCritical("LogCritical");
        }
    }
}

appsettings.json

{
  "Configuration": {
    "Title": "Excel Data Reader POC Application"
  },
 
  "Logging": {
    "IncludeScopes": false,
 
    "Debug": {
      "LogLevel": {
        "Default": "Trace"
      }
    },
    "Console": {
      "LogLevel": {
        "Microsoft.AspNetCore.Mvc.Razor.Internal": "Warning",
        "Microsoft.AspNetCore.Mvc.Razor.Razor": "Debug",
        "Microsoft.AspNetCore.Mvc.Razor": "Error",
        "Default": "Trace"
      }
    },
    "LogLevel": {
      "Default": "Trace"
    }
  },
 
  "Serilog": {
    "LogFile": "C:/Logs/ExcelDataReaderPoc.log",
    "MinimumLevel": "Verbose"
  }
}

And only with this, we have a proper logging in a console, logging everything because the log level for debug was set to Trace in appsetttings.json file:

Reading Excel in .NET Core 2

Also, as we configured Serilog to write to a rolling file, we can see its result on the configured file. Note that the minimal level using Serilog nomenclature is Verbose.

The file adds a small date on its name:

And the content output has enough details for this POC.

Excel Reading in .NET Core

There are many articles on the Internet on this topic but none of them solved this issue and after many tries, I could get good results with EPPlus.Core library, and unnofficial port to .NET Core. To do so, I added the latest stable NuGet package and modified App.cs to try to read a modern Excel file (.xlsx =>2007 or later).

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using OfficeOpenXml;
using System;
using System.IO;
using System.Text;
 
namespace ExcelDataReaderPoc
{
    public class App
    {
        private readonly ILogger<App> _logger;
        private readonly IConfigurationRoot _config;
 
        public App(ILogger<App> logger, IConfigurationRoot config)
        {
            _logger = logger;
            _config = config;
        }
 
        public void Run()
        {
            // Let's test log levels:
            _logger.LogTrace("LogTrace");
            _logger.LogDebug("LogDebug");
            _logger.LogInformation("LogInformation");
            _logger.LogWarning("LogWarning");
            _logger.LogError("LogError");
            _logger.LogCritical("LogCritical");
 
 
            // TODO: Extract this to a service class
            // load from excel
            var filePath = @"D:/test.xlsx";
            FileInfo file = new FileInfo(filePath);
 
            using (ExcelPackage package = new ExcelPackage(file))
            {
                StringBuilder sb = new StringBuilder();
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int rowCount = worksheet.Dimension.Rows;
                int ColCount = worksheet.Dimension.Columns;
 
                var rawText = string.Empty;
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= ColCount; col++)
                    {
 
                        rawText += worksheet.Cells[row, col].Value.ToString() + "\t";
 
                    }
                    rawText+="\r\n";
                }
                _logger.LogInformation(rawText);
            }
            Console.ReadKey();
        }
    }
}

Let’s run it… and this is the Console Output:

Something that is exactly what I needed considering that the input Excel file was like this:

Points of Interest

Most of the information I've found regarding logging in .NET Core was focused on ASP NET Core applications and was annoying that, according to those, calling the proper method to set minimal level, in code, that didn't work for me. Not only that, it was difficult to find articles mixing LoggerFactory (with a proper DI usage injecting ILogger<T>) with logging configuration by using a settings file, so, I hope this article helps developers like me in the near future to save time and get fast results and nice outputs in their .NET core console applications and utilities.

For further reading (Serilog and EPPlus worth it), here is a list of some of the URLs that were helpful to me during this POC development:

• https://github.com/VahidN/EPPlus.Core
• https://github.com/aspnet/Logging/issues/437
• https://jonhilton.net/2016/09/21/log-different-levels-in-asp-net-core-app/
• https://docs.microsoft.com/en-us/aspnet/core/fundamentals/logging/?tabs=aspnetcore2x
• https://stackoverflow.com/questions/38114761/asp-net-core-configuration-for-net-core-console-application
• https://github.com/serilog/serilog-settings-configuration
• https://github.com/serilog/serilog

History

• 26-Nov-2017 -> Article creating