Reading Excel Files in .net Using LinqToExcel

planetsI’m sure at some point you’ve needed to read some Excel files in an application. You’ve probably even used the Office Interop libraries (using something like: new Excel.Application();). Below I’ll show a much easier way that makes use of a data model to read an Excel file using LinqToExcel. If you’re looking for the complete code, scroll to the end of the page.

  1. Start a new solutions / project. In my example I’m just using a console app.
  2. Make sure you save your solution before running step 3, or you’ll get a “The current environment doesn’t have a solution open.” error.
  3. In the Package Manager Console, at the “PM>” prompt, enter “Install-Package LinqToExcel” and press enter. When complete, it should read: “Successfully added ‘LinqToExcel 1.10.1’ to…”
  4. Add these lines to the very top of your code (if not already there):
    using System;
    using System.Linq;
    using LinqToExcel;

    using LinqToExcel.Attributes;
  5. Add your data model. I’m using an Excel spreadsheet that contains the planets sizes and masses (taken from this sweet NASA page). Why? Because it’s really cool.
    xls1
    My model looks like:

    internal class Planet
     {
     [ExcelColumn("Planetary Body")]
     public string Name { get; set; }
    
    [ExcelColumn("Mass (1024kg)")]
     public double Mass { get; set; }
    
    [ExcelColumn("Diameter (km)")]
     public int Diameter { get; set; }
     }
  6. Notice how the ExcelColumn attribute contains the formatted name of the column (with spaces, etc.). Make sure these match to your Excel columns.
  7. Now in your Main method, let’s add some code to read the Excel file (replacing the below w/ your file path, model name and sheet name (“Planets” in the below):
    var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")
    {
     DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
     TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
     UsePersistentConnection = true,
     ReadOnly = true
     };
    var planets = from p in excel.Worksheet<Planet>("Planets")
     select p;
  8. Now let’s add something to see what data this retrieved:
    foreach (Planet planet in planets)
     {
     Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
     }
    Console.ReadLine();
  9. Your results should be the data in your Excel file.

You can filter the data in the Linq statement using something like:

var planets = from p in excel.Worksheet<Planet>("Planets")
 where p.Diameter > 5000
 select p;

Or order the data using:

 foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))

This is a very simple example of this powerful method of reading Excel spreadsheets. https://github.com/paulyoder/LinqToExcel has many more examples and documentation.

If you’re looking to use this on a machine without Office installed, I believe you need https://www.microsoft.com/en-au/download/details.aspx?id=13255 to enable ACE.

Below is the entire code from this example:

using System;
using System.Linq;
using LinqToExcel;
using LinqToExcel.Attributes;

namespace cSharpExcelRead
{
    internal class Planet
    {
        [ExcelColumn("Planetary Body")]
        public string Name { get; set; }

        [ExcelColumn("Mass (1024kg)")]
        public double Mass { get; set; }

        [ExcelColumn("Diameter (km)")]
        public int Diameter { get; set; }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")

            {
                DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
                TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
                UsePersistentConnection = true,
                ReadOnly = true
            };

            var planets = from p in excel.Worksheet<Planet>("Planets")
                          select p;

            foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))
            {
                Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
            }

            Console.ReadLine();
        }
    }
}
Reading Excel Files in .net Using LinqToExcel

Using Parallel and ThreadPool / QueueUserWorkItem in Combination with Writing / Locking Files

If you’ve wanted to combine parallel / multi-threading and also writing to files, you’ve probably experienced the issue of a file being locked when another process is writing at the same time. Something like:

An unhandled exception of type ‘System.IO.IOException’ occurred in mscorlib.dll

Additional information: The process cannot access the file ‘c:\yourfile’ because it is being used by another process.

One of the ways to get around this is utilize the “lock” statement.

In my below example, I’m running parallel tasks (including the optional MaxDegreeOfParallelism) and writing to a file. I also added a stopwatch and a .sleep to add time and debugging to the method. If you were to remove the lock() from the below, you should experience the ioexception.

Hope this helps as you create parallel tasks (so many great uses) and write files (maybe some simple logging, etc.)

You can also download the sample here.

using System;
using System.Diagnostics;
using System.IO;
using System.Threading;
using System.Threading.Tasks;

namespace parallelTasks_Example
{
    //custom object for passing to queue task
    public class UpdateFile
    {
        public string lineData { get; set; }

        public string filePath { get; set; }

        public long timeTaken { get; set; }
    }

    internal class Program
    {
        //set an update to use a lock
        private static object fileLock = new object();

        private static void Main(string[] args)
        {
            //just for testing
            Stopwatch timer = new Stopwatch();

            timer.Reset();

            //get some data
            string[] contacts = { "jon", "jon2", "aaron", "efren", "diedrich", "tina", "sandy", "haylie", "trevor", "shondrella" };

            //do something with the data
            timer.Start();
            Parallel.ForEach(contacts, new ParallelOptions { MaxDegreeOfParallelism = 3 }, contact =>
                {
                    //do the task
                    Console.WriteLine(contact + " " + timer.ElapsedMilliseconds);
                    //create an object to past to the queue item
                    UpdateFile fileupdate = new UpdateFile { filePath = @"c:\temp\temp.txt", lineData = contact, timeTaken = timer.ElapsedMilliseconds };
                    //add the queue item
                    ThreadPool.QueueUserWorkItem(UpdateFile, fileupdate);
                });

            Console.ReadLine();
        }

        private static void UpdateFile(object fileData)
        {
            //set object to be locked
            lock (fileLock)
            {
                //get the file
                using (var fw = File.AppendText(((UpdateFile)fileData).filePath))
                {
                    //write the data
                    fw.WriteLine(((UpdateFile)fileData).lineData + ": " + ((UpdateFile)fileData).timeTaken.ToString());
                    //make process take long (for example)
                    System.Threading.Thread.Sleep(100);
                }
            }
        }
    }
}
Using Parallel and ThreadPool / QueueUserWorkItem in Combination with Writing / Locking Files

Using SortedSet as a Sorted List with Custom Comparer – c#

Sometimes you have a list of objects you want to stay sorted. You can usually use an IList and then linq: orderby to sort the list – but other times it helps to have the list always sorted. In those times, SortedSet works very well. In my below example, a sorted set contains some people – and whenever you look at the list in the SortedSet – they are already sorted (by age, name & location).

I was concerned about the performance of the SortedSet vs. a List, so after running some tests, as expected – the SortedSet needs some initial time when adding items (to compare) vs. the List which will is fast to write to – but needs a little time when sorting the list after. Below is some really fast numbers (data in milliseconds) I’ve compiled based on adding / reading 25000 records:

sortedSet-performance

Below is the sample using SortedSet and an IComparer:

using System;
using System.Collections.Generic;

namespace sortedSet
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            //create the SortedSet
            SortedSet<Person> people = new SortedSet<Person>(new PersonComparer());

            //add some random folks:
            Random rnd = new Random();
            for (int i = 1; i <= 25; i++)
            {
                //new person
                Person person = new Person();
                person.name = "Bob " + i.ToString();
                person.location = "Miami" + i.ToString();

                //random age
                int r = rnd.Next(1, 99);
                person.age = r;

                //add person to set
                people.Add(person);
            }

            //show our list:
            foreach (Person person in people)
            {
                Console.WriteLine(person.name + " - Age: " + person.age.ToString());
            }

            Console.ReadLine();
        }
    }

    //create comparer
    internal class PersonComparer : IComparer<Person>
    {
        public int Compare(Person x, Person y)
        {
            //first by age
            int result = x.age.CompareTo(y.age);

            //then name
            if (result == 0)
                result = x.name.CompareTo(y.name);

            //a third sort
            if (result == 0)
                result = x.location.CompareTo(y.location);

            return result;
        }
    }

    internal class Person
    {
        public string name { get; set; }

        public string location { get; set; }

        public int age { get; set; }
    }
}

-If you don’t have a need to sort your lists – don’t forget about HashSet (very fast).

Using SortedSet as a Sorted List with Custom Comparer – c#

Force WWW & Fix Redundant Hostnames on Google / SEO

I feel the term “SEO” is completely overused, however, there are a few things you want to do besides just having great content. One is make sure your site url is consistent. chrisbitting.com is different from http://www.chrisbitting.com. 

Google Analytics will provide you a suggestion to fix this if you’re experiencing traffic from multiple hostnames. Something like:

Property http://www.yourdomain.com is receiving data from redundant hostnames. Some of the redundant hostnames are:

This is easy to fix using your Global.asax page. Just add this to your code, replacing “yourdomain” with your actual domain. The Application_BeginRequest will catch and redirect to the altered url, also issuing a 301 to help search engines.

void Application_BeginRequest(object sender, EventArgs e)
    {
        

        if (HttpContext.Current.Request.Url.ToString().ToLower().Contains(

            "http://yourdomain.com"))
        {

            HttpContext.Current.Response.Status = "301 Moved Permanently";

            HttpContext.Current.Response.AddHeader("Location",

                HttpContext.Current.Request.Url.AbsoluteUri.ToString().ToLower().Replace(

                    "http://yourdomain.com", "http://www.yourdomain.com"));

            HttpContext.Current.Response.End();
        }
    }

You could do this using web.config + rewrite, but I enjoy this method more.

Force WWW & Fix Redundant Hostnames on Google / SEO

C# – Measure String Width & Height (in pixels) Including Multi-line Text

A while back I posted a VB snippet of a simple way to measure text length in pixels. The below is a C# version, with some minor improvements (like not needing a form, so it’s okay to use with console and asp.net apps).

To measure a single line string:

//set font, size & style
Font f = new Font("Microsoft Sans Serif", 14, FontStyle.Regular);

//create a bmp / graphic to use MeasureString on
Bitmap b = new Bitmap(2200, 2200);
Graphics g = Graphics.FromImage(b);

//measure the string
SizeF sizeOfString = new SizeF();
sizeOfString = g.MeasureString("This is a text line", f);

//use:
Debug.WriteLine("String Height: " + sizeOfString.Height);
Debug.WriteLine("String Width: " + sizeOfString.Width);

This is a multi-line string – notice the addition of the width parameter:

//set font, size & style
Font f = new Font("Microsoft Sans Serif", 14, FontStyle.Regular);

//create a bmp / graphic to use MeasureString on
Bitmap b = new Bitmap(2200, 2200);
Graphics g = Graphics.FromImage(b);

//measure the string and choose a width:
SizeF sizeOfString = new SizeF();
sizeOfString = g.MeasureString("This is line 1" + Environment.NewLine + "This is line 2", f, 300);

//use:
Debug.WriteLine("String Height: " + sizeOfString.Height);
Debug.WriteLine("String Width: " + sizeOfString.Width);

For my examples, you’ll want to add the below to the top of your code:

using System.Diagnostics;
using System.Drawing;
C# – Measure String Width & Height (in pixels) Including Multi-line Text

Fixing / Removing Invalid Characters from a File Path / Name – c#

Below is a simple method for fixing bad filenames and paths. This uses the character lists from Path.GetInvalidPathChars and Path.GetInvalidFileNameChars (part of System.IO).

You should be able to pass a filename, directory or path. Example, calling these three lines would yield the below:

cleanPath(@"c:\tem|<p\fi<>le.txt")
cleanPath(@"c:\tem|<p\")
cleanPath(@"fi<le.txt")

Returns:

c:\tem-p\fi-le.txt
c:\tem-p\
fi-le.txt

You can also pass a string that’s used to replace the bad characters.

cleanPath(@"c:\tem|<p\fi<>le.txt", string.Empty)

Returns:

c:\temp\file.txt
 private string cleanPath(string toCleanPath, string replaceWith = "-")  
      {  
           //get just the filename - can't use Path.GetFileName since the path might be bad!  
           string[] pathParts = toCleanPath.Split(new char[] { '\\' });  
           string newFileName = pathParts[pathParts.Length - 1];  
           //get just the path  
           string newPath = toCleanPath.Substring(0, toCleanPath.Length - newFileName.Length);   
           //clean bad path chars  
           foreach (char badChar in Path.GetInvalidPathChars())  
           {  
                newPath = newPath.Replace(badChar.ToString(), replaceWith);  
           }  
           //clean bad filename chars  
           foreach (char badChar in Path.GetInvalidFileNameChars())  
           {  
                newFileName = newFileName.Replace(badChar.ToString(), replaceWith);  
           }  
           //remove duplicate "replaceWith" characters. ie: change "test-----file.txt" to "test-file.txt"  
           if (string.IsNullOrWhiteSpace(replaceWith) == false)  
           {  
                newPath = newPath.Replace(replaceWith.ToString() + replaceWith.ToString(), replaceWith.ToString());  
                newFileName = newFileName.Replace(replaceWith.ToString() + replaceWith.ToString(), replaceWith.ToString());  
           }  
           //return new, clean path:  
           return newPath + newFileName;  
      }  

Hope it helps!

Fixing / Removing Invalid Characters from a File Path / Name – c#

How To Install Redis on Windows and Get Started With C#

Dictionary_IconRedis is a key-value store that is fast, sits in memory and runs on many different platforms. Installing on Windows isn’t straightforward from the Redis site, so below are the steps I use to get going.

1. Download the latest .zip from: https://github.com/mythz/redis-windows/tree/master/downloads

2. Extract these files to a folder (I did f:\redis)

3. In a command prompt, run

redis-server.exe redis.windows.conf

(from your directory above).

Redis should now be running on port 6379. You can change this in redis.windows.conf  if you’d like.

4. Start a new Visual Studio Project (I’m using c# / .net 4.5).

5. In Package Manager Console (NuGet) let’s install the client, run:

Install-Package StackExchange.Redis

6. Include using StackExchange.Redis;

7. Now you can use the below to save and get values:

  //setup your connection
  ConnectionMultiplexer redisConn = ConnectionMultiplexer.Connect("localhost");

 //get your db
 IDatabase redDb = redisConn.GetDatabase();

 //save a key & val
 redDb.StringSet("testKey", "test val");

 //get a key & val
 Console.WriteLine(redDb.StringGet("testKey"));

This is just start of what Redis can do, find more at the official Redis site.

How To Install Redis on Windows and Get Started With C#

Simple Single Page / Single User Forms Authentication without DB – C#

simple-loginOn smaller projects, maybe for internal usage, you want to protect a page with a username and password. Below is an easy way you can protect a page (or pages / folders) using a user and pass (kept in the web.config for easy access). This method uses forms authentication but doesn’t require a database or other source for user details since a single user is just stored in the web.config.

You’ll basically need to.

  1. Create a login page (simple .aspx page)
  2. Modify your web.config

 

Let’s start with your web.config. Below are the areas you’ll want to add: Continue reading “Simple Single Page / Single User Forms Authentication without DB – C#”

Simple Single Page / Single User Forms Authentication without DB – C#

Search and Truncate / Trim Paragraph By Sentence C# (not word or character)

mag-glassI’ve had the need on more than one occasion to preview or trim a paragraph around a search term, but I don’t want to just use a character count and cut words in half, or a word could and cut sentences in half. My simple method below takes a paragraph and a search word and returns a string that is truncated by sentences. With some additional params, you can decide how many sentences to include from around your desired sentence, if you want a “read more” tag or begging tag to indicate more text exists.

In this example, I’m searching for a word in a string object (‘para’).

truncateBySent(para, "redmond", 1, 1, true, " View More...", false, "...", false, "Not Found")

In this example paragraph:

By a board session on the weekend of Dec. 14, some board members were exhausted from the months of work, and were concerned that the process had dragged on, said a person familiar with the matter. They left the meeting, at a hotel less than 8 miles from Microsoft’s Redmond, Wash., headquarters, without a pick. The board had more potential CEO candidates with whom they wanted to meet, and were frustrated that research wasn’t ready on at least one new prospect, said people familiar with the situation.

It would return:

…They left the meeting, at a hotel less than 8 miles from Microsoft’s Redmond, Wash., headquarters, without a pick. The board had more potential CEO candidate s with whom they wanted to meet, and were frustrated that research wasn’t ready on at least one new prospect, said people familiar with the situation.
 

Another variation:

truncateBySent(para, “board”, 1, 1, true, ” View More…”, false, “…”, false, “Not Found”)

Returns:

By a board session on the weekend of Dec. 14, some board members were exhausted from the months of work, and were concerned that the process had dragged on, said a person familiar with the matter. They left the meeting, at a hotel less than 8 miles from Microsoft’s Redmond, Wash., headquarters, without a pick. View More…
 

And here is the function, you’ll need to import:

using System.Linq;
using System.Text.RegularExpressions;
  public static string truncateBySent(string source, string searchWord, int sentPrepend = 1, int sentAppend = 1, bool onlyShowFirst = true, string viewMoreTag = "", bool alwaysShowViewMoreTag = false, string startTruncTag = "", bool returnSourceIfKeywordNotFound = false, string returnNotFound = "")
        {
            //going to be the final string
            string truncated = "";

            //parse source sentences
           string[] sents = Regex.Split(source, @"(?<=[.?!;])\s+(?=\p{Lu})");

            //create some search start & end holders
            int i = 0;
            int ssent = -1;
            int esent = 0;

            //find start / end
            foreach (string sent in sents)
            {
                //search using regex for word boundaries \b
                if (Regex.IsMatch(sent, "\\b" + searchWord + "\\b", RegexOptions.IgnoreCase))
                {
                    if (ssent == -1)
                    {
                        ssent = i;
                    }
                    else
                    {
                        esent = i;
                    }
                }

                i++;
            }

            //make final string:

            if (esent == 0 || onlyShowFirst == true) esent = ssent;

            i = 0;

            foreach (string sent in sents)
            {
                if (i == ssent - sentPrepend || i == ssent || i == esent + sentAppend || (i >= ssent - sentPrepend && i <= esent + sentAppend))
                {
                    truncated = truncated + sent + " ";
                }

                i++;
            }

            //add view more

            if (esent + sentAppend + 1 < sents.Count() || alwaysShowViewMoreTag == true)
            {
                truncated = truncated + viewMoreTag;
            }

            //add beginning tag
            if (ssent - sentPrepend > 0)
            {
                truncated = startTruncTag + truncated;
            }

            //check if anything was even found:

            if (ssent == -1)
            {
                if (returnSourceIfKeywordNotFound)
                { truncated = source; }
                else
                {
                    truncated = returnNotFound;
                }
            }

           //and now return the final string - do a trim and remove double spaces.
            //did i ever mention how much i despise double spaces?
            return truncated.Trim().Replace("  ", " ");
        }

 

Happy Searching!

Search and Truncate / Trim Paragraph By Sentence C# (not word or character)

Checking if a file is locked in C# using Win32

Below is a simple, fast way you can check if a file is locked, using Interop to use a win32 function. I’ve found this method to be pretty fast, but I would caution, you should still have some error logic in your code in case in the window of time between you checking your file for availability and using your file, it should become locked. Below is the c# code:

Using:

using System;
using System.IO;
using System.Runtime.InteropServices;

And in your class:

  [DllImport("kernel32.dll")]
        private static extern Microsoft.Win32.SafeHandles.SafeFileHandle CreateFile(string lpFileName, System.UInt32 dwDesiredAccess, System.UInt32 dwShareMode, IntPtr pSecurityAttributes, System.UInt32 dwCreationDisposition, System.UInt32 dwFlagsAndAttributes, IntPtr hTemplateFile);

        private static readonly uint GENERIC_WRITE = 0x40000000;
        private static readonly uint OPEN_EXISTING = 3;

        [DllImport("kernel32.dll")]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool CloseHandle(SafeHandle hObject);

        public static bool boolFileLocked(string fPath)
        {
            if (!File.Exists(fPath))
            {
                return false;
            }

            SafeHandle sHandle = null;

            try
            {
                sHandle = CreateFile(fPath, GENERIC_WRITE, 0, IntPtr.Zero, OPEN_EXISTING, 0, IntPtr.Zero);

                bool inUse = sHandle.IsInvalid;

                return inUse;
            }
            finally
            {
                if (sHandle != null)
                {
                    CloseHandle(sHandle);
                }
            }
        }

To test, toss this into your Main:

long tStart = DateTime.Now.Ticks;
Console.WriteLine("start");
Console.WriteLine(boolFileLocked(@"c:\locked.xlsx"));
Console.WriteLine(string.Format("{0:n0}", DateTime.Now.Ticks - tStart));
tStart = DateTime.Now.Ticks;
Console.WriteLine(boolFileLocked(@"c:\unlocked.xls"));
Console.WriteLine(string.Format("{0:n0}", DateTime.Now.Ticks - tStart));
tStart = DateTime.Now.Ticks;
Console.WriteLine(boolFileLocked(@"c:\missing.xls"));
Console.WriteLine(string.Format("{0:n0}", DateTime.Now.Ticks - tStart));
Console.WriteLine("end");
Console.ReadLine();
Checking if a file is locked in C# using Win32