QuickTip

Working with Excel files using EPPlus and XLSX.JS

February 29, 2016 C#, JavaScript, Opinion, QuickTip, Tools 1 comment

Need to quickly generate an Excel file on the server (.NET) with no headache or need to import  another Excel file in your JS client?

I can recommend two libraries for their simplicity and ease of use.

XLSX.JS

To smooth transitioning from Excel files to electronic handling of data we offered our users possibility of importing data. As our application is web based it meant some JS library to work with Excel files. A bit of complication was that our users over time developed a habit of having all kinds of modifications in their “custom” Excel files. So something that would allow us easily work with different formats was a preference.

XLSX.JS library available on GitHub proved to be a good choice. I could only imagine how much better it is over some monsters that would only work in IE. I think starting documentation is fairly good, so I will just go through some bits and pieces from our use case.

Setting up XLSX.JS and reading files is straight forward: npm or bower, include of file and you are ready to write XLSX.readFile('test.xlsx') or App.XLSX.read(excelBinaryContents, {type: 'binary'}).

Reading as binary is probably a better bet as it will work in IE, though you will have to write some code to implement FileReader.prototype.readAsBinaryString() in IE. You can have a look at our implementation of file-select component on gist.

Using XLSX in your JavaScript is fairly easy, though there might be some hiccups with parsing dates. See this gist.

EPPlus

We also have two use cases where we need to generate Excel file on the server. One was to generate some documentation for business rules so we can have it up to date and share with our users at all times. It was implemented as part of CI that would save a file to a file system. The other use case was downloading of business related data via web interface. These two were super easy to do with open source library called EPPlus.

You just add EPPlus through NuGet and start using (var excelPackage = new ExcelPackage(newFileInfo)). See the gist below. First file demonstrates how to operate with cells and the other one just shows how you can use streams to make file downloadable.

These two libraries really helped me to efficiently implement some of the Excel file business use cases.

Next time I will have to generate Excel file on server or read it on client I will most certainly use these two again.


1 comment

Simple check for breaking Database changes using NUnit and T-SQL CHECKSUM_AGG

October 1, 2015 C#, HowTo, QuickTip, SQL No comments

Imagine that your database is being used by another system that directly reads data from few of the tables. I know this is not a good idea and I would not recommend anyone to do anything similar. Unfortunately developers often do what they don’t really want to do.

In this post I just want to document a very simple way of verifying that you are not removing or modifying tables and columns that are being used by someone else.

My solution is dumb unit tests that select table schema information for the list of tables and columns and then builds checksum that is finally compared to expected checksum.

I don’t pretend to sound like this somewhat dirty solution is a right way, but it is very quick to implement and effective way. Here is the implementation in C#:

Click here to see the gist.

 

In code above we verify that we are not removing or modifying few columns in tables Table1 and Table55, since they are used by SystemX. NUnit TestCase attributes are used to allow for multiple table checks. First parameter is a checksum that you would get for the first time by failing the unit test or by running query separately. Checksum calculation is simple but it has a bit of overburden with concatenating column names into one string and then parsing it again into a temp table for the “IN” condition. (Of course there are other ways, but I find this one to be a bit less code). Also it probably worth to mention that we apply database changes using FluentMigrator, so tracking what exactly was changed that caused particular Unit Test case to fail would be just to have a look at the latest commit.

I think this is acceptable solution for a small set of checks. Probably you need another solution if your database is heavily used by another systems.

Hopefully is helpful to someone. At least I write this post to have it handy for myself in case I need something similar done quickly.


No comments

IIS Logging hints: (Log Parser + Log Parser Studio + IIS Advanced Logging)

February 18, 2013 IIS, QuickTip, Tools 1 comment

Rather than starting this blog post with first excusing about me being not much into IIS. I will share very simple but extremely powerful toolset to be aware how your web application is living its life.

Of course any good application has exception handling and logging in place therefor you know when “shit happens” and possibly able to dig into it, find root cause and fix. Great! But what if you want to know more? What if you want to see some graphics showing distribution of users, their favourite features, pick hours, etc? To some extend Google Analytics could satisfy your needs. I use it for this blog and it is absolutely brilliant there.

You could have better tool in your hands? Plain simple. Plain power.

First. Enable your IIS Logging

To enable IIS logging use this boring step-by-step explanation. Or find this self-explanatory nice pictogram

image

in IIS manager. After enabling it in actions section your logs will be collected accordingly to configuration. (Say for application with high load you might want to change configuration to generate a new log file for each hour.)

Second. Analyse what’s inside

Well, first step could be considered as waste of keystrokes for some of you as you find it natural to have IIS logging enabled. But I also know that there are applications running for which no one ever collected any logs and have only preliminary idea how their applications are used and what is happening at all.

Here is bit of my story: We develop HTTP API used by various front-ends. Now we rewrite one of our heavily used endpoints. Therefor it was needed to know usage patterns (GET method parameters) and picks. I was able to quickly find everything out. The most time consuming was to download all those gigs of logs. But as soon as they are easily accessible, everything is piece of cake. Just continue reading…

You would need some tool to start analysing your logs.

Best fit would be Microsoft LogParser. Download it. It is console application which allows to query stuff with SQL-like queries. I said “stuff” because it is not only logs. Have a look on this LogParser architecture diagram:

logparser_architecture.gif

Output could be also charts (though there is dependency on MsOffice). For example this could be chart of non-200 status codes:

clip_image001

You can utilize this to to build nice reporting system with graphics to be sent to development team and/or managers.

Log Parser Studio

What if you want to quickly analyse something without bothering with console app?

There is Log Parser Studio – an excellent and handy tool.

image

When you just started it library of different queries is shown to you along with possibility to search among them. So you have a great starting point for your custom queries.

For example here is simple query I just used:

SELECT QUANTIZE(TO_TIMESTAMP(date, time), 60) AS Minute,
    sc-status,
    COUNT(*) AS Total
FROM *.log
WHERE (cs-uri-stem like '%SuspicionsEndpoint.svc%') and (sc-status = 500)
GROUP BY sc-status, Minute
ORDER BY Minute

I have found this page to be extremely useful as reference for functions in LogParser.

Be aware that LogParser doesn’t support everything available in SQL. When I tried to write something bit more complex like distinct count with grouping it complained. At least LogParser provided meaningful explanation so I knew that feature isn’t implemented. I had to use temporary file to achieve what I wanted.

Third (if you want more from IIS). Install Advanced Logging

You might be in situation when default logging fields are not enough for you:

image

For example, some token information is supplied in http header. No worries – Install IIS Advanced Logging extension. Now you look for this pictogram:

image

Setup and configuration is somewhat more complex.

Here is great step-by-step with pictures: http://www.iis.net/learn/extensions/advanced-logging-module/advanced-logging-for-iis-custom-logging

And here is mine shorter example. Say you want to include custom http request header:

  1. "Add Log Definition…”
  2. Configure it and include few common logging fields and save
  3. Navigate back to “Advanced Logging”, select your definition and hit “Edit Logging Fields…”
  4. Add logging field:
  5. image
  6. Use it in your definition
  7. Enable your definition for web site (if not yet enabled)

There you go. At this moment you can analyse even more information with Log Parser or nice Log Parser Studio.

Hope this post is of help for those who want to know more about their application!

Quick links:


1 comment

Working with FTP for the first time? Quick setup & quick C# code.

March 21, 2012 .NET, C#, QuickTip No comments

Recently I had some FTP work to do. Nothing special, but in case you need quick guide on setting up FTP and writing access code in .NET you might find this interesting. Also you know where to find it in case you need it later.

I will define simple task and we will solve it!

Task:

Imagine we have external FTP server, where some vendor puts many files. Of course they provided us with credentials. We want to connect to server and then parse some files from the whole list of files. Also for testing purposes we are going to mock external service with our own local.

Setup FTP:

1) Enable FPT in Windows features.

image

2) Go to IIS –> Sites –> “Add FPT Site…”. You would need to specify some folder.

3) As for our task we want to mock some system. Following setup might be good:

  • Binding with all assigned host names and port 21
  • No SSL
  • Allow for Anonymous and Basic Authentication
  • Add Read permissions for All Users and Anonymous

You should see something like this:

image

image

You will be able to access FTP locally without any issues and need to provide credentials.

4) Go to User Accounts –> Advanced –> Advanced –> New User… Create user you would like use when connecting to FTP.

image

5) Go to IIS -> your FTP site –> Basic Settings –> Connect as… –> Specific User. And enter same user again.

image

We added this user because we need to imitate situation in which our code and FTP have different credentials.

Access code:

To get list of files on server (using WebRequest):

public List<string> FetchFilesList()
{
    var request = WebRequest.Create(FtpServerUri);
    request.Method = WebRequestMethods.Ftp.ListDirectory;

    request.Credentials = new NetworkCredential(UserName, UserPassword);

    using (var response = (FtpWebResponse)request.GetResponse())
    {
        var responseStream = response.GetResponseStream();

        using (var reader = new StreamReader(responseStream))
        {
            var fileNamesString = reader.ReadToEnd();
            var fileNames = fileNamesString.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

            return fileNames.ToList();
        }
    }
}

To fetch some file contents as XDocument (using WebClient):

public XDocument FetchFile(string fileName)
{
    var client = new WebClient();
    client.Credentials = new NetworkCredential(UserName, UserPassword);

    var fileUri = new Uri(FtpServerUri, fileName);
    var downloadedXml = client.DownloadString(fileUri);
    return XDocument.Parse(downloadedXml);
}

I don’t think those two chucks of code need lot of explanations. As you can see with WebClient there is less code, but this way you cannot specify request ftp method.

Hope this overview is quick and not too much noisy.

NOTE: I’m not professional administrator, so my FTP setup may be somewhatwrong, but it satisfied needs of task described in the beginning of blog post.

In any case here are some links:


No comments

Custom configuration: collection without “add” plus CDATA inside

March 20, 2012 .NET, C#, QuickTip 2 comments

This blog post might look like any other standard blog posts, answering question, which can be googled and found on stackoverflow. But it isn’t. You see… it composes couple of interesting things you might need for you custom configuration. Also it is not congested with explanations. I’m adding this as quick reference for myself, so I don’t spend my time googling a lot to find answers. Also if you just starting with custom configuration and don’t want to read MSDN pages, please refer to my earlier blog post on basics here.

Let’s get back to topic:

We want section in our app/web.config with collection which will be able to contain elements without ugly “add” tag and also have CDATA inside. See configuration:

    <Feeds defaultPollingInterval="00:10:00">
      <Feed>
        <![CDATA[http://www.andriybuday.com/getXmlFeed.aspx?someParam=A&somethingElse=B]]>
      </Feed>
      <Feed pollingInterval="00:05:00">
        <![CDATA[http://www.andriybuday.com/getXmlFeed.aspx?someParam=C&somethingElse=D]]>
      </Feed>
    </Feeds>

So as you can see in collection of elements there is custom name “Feed”, which is awesome. Also notice that URL contains weird characters (not for us, but for XML), so we surround URL into CDATA. Those feeds are fake of course.

To make all this happen we need few things:

  1. Override CollectionType property for our collection, and set type to BasicMap
  2. Override ElementName property for our collection, and return preferred name
  3. Override DeserializeElement method for element inside collection. Here you need to manually fetch your attributes, like I do for poollingInterval and read contents of CDATA. Please refer to source code below to see how this is done as it is bit tricky. For example because of the nature of the XmlReader you need to read attributes first and then proceed to contents.

Source code below (interesting pieces are in bold):

[ConfigurationCollection(typeof(FeedConfigElement))]
public class FeedsConfigElementCollection : ConfigurationElementCollection
{
    [ConfigurationProperty("defaultPollingInterval", DefaultValue = "00:10:00")]
    public string DefaultPollingInterval
    {
        get
        {
            return (string)base["defaultPollingInterval"];
        }
    }
    protected override ConfigurationElement CreateNewElement()
    {
        return new FeedConfigElement();
    }
    protected override object GetElementKey(ConfigurationElement element)
    {
        return ((FeedConfigElement)(element)).Url;
    }

    // In order to avoid standard keyword "add"
    // we override ElementName and set CollectionType to BasicMap
    protected override string ElementName
    {
        get { return "Feed"; }
    }

    public override ConfigurationElementCollectionType CollectionType
    {
        get { return ConfigurationElementCollectionType.BasicMap; }
    }
    public FeedConfigElement this[int index]
    {
        get
        {
            return (FeedConfigElement)BaseGet(index);
        }
    }
}

public class FeedConfigElement : ConfigurationElement
{
    public string Url { get; private set; }

    public string PollingInterval { get; private set; }

    // To get value from the CDATA we need to overrride this method
    protected override void DeserializeElement(XmlReader reader, bool serializeCollectionKey)
    {
        PollingInterval = reader.GetAttribute("pollingInterval") ?? "00:00:00";

        // Also for some unknown reason for CDATA ReadElementContentAsString returns 
        // a lot of spaces before and after the actual string, so we Trim it
        Url = reader.ReadElementContentAsString().Trim();
    }
}

Hope this gives quick answers to some of you. It took me good portion of time to find all this things, because for some odd reason it wasn’t so much easy to find.

Some links:


2 comments

WP7 update error 80070026

March 19, 2012 QuickTip, WP7 No comments

I can assume this is of no use for most of you, my constant readers.

But in case you googled for this or just happened to have Windows Phone 7 and cannot update it because of the error 80070026, which you see on the image below,

WindowsPhone7_HTCMozart_UpdateError_80070026

please know: the only solution that helps is complete reset.

Don’t waste your time on searching for solutions – I did it for you. Also reset is not that painful as you might imagine. Just ensure you have all your media and application data backed-up to some clouds or at least local drive. With good internet connection, which no doubt you have at home, reinstallation of apps is super quick, plus you will get rid of junk apps.

To reset either go to “Settings->About->Reset you phone” or use more geeky way with using phone buttons: turn off phone, then press both volume up and down buttons and hold and then press the power button (briefly) to switch the phone on. When you see reset screen release volume buttons and follow instructions.

You might be interested to know if there are some specifics to my situation, so here they are:

Phone: HTC Mozart T8698
Update: 7.10.7740.16 => 7.10.8107.79
When: On performing actual update
OS: Windows 7
Zune: latest version possible

Some links in case you have other update troubles:

 

If you found another solution to this problem please let me know.


No comments

Wow – Always check the Inner Exception

August 4, 2010 Errors, QuickTip 3 comments

Today, I worked with services logic that sends me light objects mapped from NHibernate entities with AutoMapper, and I got an exception stating that something is wrong with mappings. I wrote UT that reproduces my issue and surrounded code with try{}catch(){} to see exception closer, and it said me the same I saw. I took a look on Inner exception message and at first glance it was absolutely identical to parent message.

Then I spent about 10-20 minutes verifying all my entities, trying to find some stupid mistake and I would spend more if I would not take a look on Inner exception of the Inner exception and it was a bit different, saying that another entity mapping is wrong, so I took a look into Inner exception of it and so on…

So do you have an idea where did I stop? See:

So I went through 11 Inner Exceptions to get to the null Inner Exception :) Highlighted message provided me information I needed to resolve my issue.

Moral: Always take a look into Inner Exception!


3 comments

Using NHibernate Profiler with Unit Tests

July 5, 2010 NHibernate, QuickTip, UnitTesting No comments

Today, I’ve been writing few methods for my DAL classes, of course, I decided to have one or few integration unit tests that hits database and see actual SQLs with NHibernate Profiler.

So in couple of minetes I’ve got unit tests that had following line in the beggining of each:

 HibernatingRhinos.Profiler.Appender.NHibernate.NHibernateProfiler.Initialize();

if you don’t know, this line attaches your execution code with NHibernate Profiler, so I’m able to see SQLs NHibernate generates.

When I run bunch of unit tests in my testing file, I’ve got strange picture with duplicating of queries for each test with arithmetic progression. And N+1 problem, but hold on, I’m sure that I did everything through joins.

Reason is that profiler appends to my code on each new test run and that is why it start thinking that I have multiple selects to get list of something.

Solution, which I would recommend as pattern for writing Unit Tests with NHibernate Profiler is following:

        [SetUp]
        public void SetUp()
        {
            HibernatingRhinos.Profiler.Appender.NHibernate.NHibernateProfiler.Initialize();
        }
        [TearDown]
        public void TearDown()
        {
            HibernatingRhinos.Profiler.Appender.NHibernate.NHibernateProfiler.Stop();
        }


No comments

Quick & cheap way to rename colum in table – sp_rename

March 31, 2010 QuickTip, SQL No comments

Quick & cheap way to rename colum in table:

EXEC sp_rename
    @objname = ‘MY_TABLE.COULMN_NAME’,
    @newname = ‘COLUMN_NAME’,
    @objtype = ‘COLUMN’

Get fun!


No comments

Simple Web Page doesn’t work after Ctrl+F5 hit in VS under Windows 7

March 14, 2010 ASP.NET, Environment, QuickTip No comments

I’ve upgraded my system to Windows 7 couple of months ago, but I haven’t used web development.

The Problem
Few days ago, I created empty web application, added new page and added <p>bla-bla<p>, then I hit Ctrl+F5 and my default browser opened, but nothing happened. I was thinking that it is because of my browser, so I switched over 3 browsers and nothing helped, I also thought that it VS2010 broke something causing asp.net development server issues, so uninstalled it and installed back, but nothing helped.

I even had a thought to reinstall system, but decided to search over the internet more deeper and found that:

Reason
Reason is that when system uses IPv6 browsers could have issues with resolving localhost. 

Solution
Disabling IPv6 is solution for this problem.

You can disable IP 6 in your system by ensuring that you have this in your registry:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpip6ParametersDisabledComponents set to 0xffffffff

Firefox settings change
Also when searching I found for myself that we can change settings of Firefox with writing about:config in address bar.


No comments