Articles in categories

Now on Amazon

Read Excel file with Epplus

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). I use it when I need to read or create Excel files. 

Reading an Excel file:

using (var ms = new MemoryStream(File.ReadAllBytes(filePath)))
{
	using (var package = new ExcelPackage(ms))
	{
		var ws = package.Workbook.Worksheets[1];

		var startRow = ws.Dimension.Start.Row + 1; // first row contains column name/title
		var endRow = ws.Dimension.End.Row;

		for (var i = startRow; i <= endRow; i++)
		{
			if (ws.Cells[i, 1].Value != null)
			{
				personList.Add(new Person()
				{
					FirstName = ws.Cells[i, 1].Value  != null ? Convert.ToInt32(ws.Cells[i, 1].Value) : "N/A",
					LastName = ws.Cells[i, 2].Value != null ? Convert.ToInt32(ws.Cells[i, 2].Value) : "N/A",
					Age = ws.Cells[i, 3].Value  != null ? Convert.ToInt32(ws.Cells[i, 3].Value) : 0;
				});
			}
			else
			{
				break;
			}
		}
	}
}

Just very basic example code. Reading the file in a MemoryStream, which is not really needed. You could also just pass a FileInfo object to the Epplus constructor.

var file = new FileInfo(filePath);
using (var package = new ExcelPackage(file))
{
  // do magic here
}

 

 

Make a filesize readable

I was looking for how to make a filesize in kb readable, in other words format it so it would show the size in KB, MB, GB, TB and so on. I've found many different solutions on Google, but I took this one. 

public string GetSizeReadable(long i)
{
	string sign = (i < 0 ? "-" : "");
	double readable = (i < 0 ? -i : i);
	string suffix;
	if (i >= 0x1000000000000000) // Exabyte
	{
		suffix = "EB";
		readable = (double)(i >> 50);
	}
	else if (i >= 0x4000000000000) // Petabyte
	{
		suffix = "PB";
		readable = (double)(i >> 40);
	}
	else if (i >= 0x10000000000) // Terabyte
	{
		suffix = "TB";
		readable = (double)(i >> 30);
	}
	else if (i >= 0x40000000) // Gigabyte
	{
		suffix = "GB";
		readable = (double)(i >> 20);
	}
	else if (i >= 0x100000) // Megabyte
	{
		suffix = "MB";
		readable = (double)(i >> 10);
	}
	else if (i >= 0x400) // Kilobyte
	{
		suffix = "KB";
		readable = (double)i;
	}
	else
	{
		return i.ToString(sign + "0 B"); // Byte
	}
	readable = readable / 1024;

	return sign + readable.ToString("0.## ") + suffix;
}

The actual article can be found here.

Parse string to enum

Parse a string value to an Enum type. The stringToParse value needs to exist in the enumeration.

var test = (EnumType) Enum.Parse(typeof (EnumType), "stringToParse");

 

Check if array of numbers is sequential

Simple method to check if an array of numbers is sequential. Returns true or false.

public static bool IsSequential(int[] array)
{
	return array.Zip(array.Skip(1), (a, b) => (a + 1) == b).All(x => x);
}

Usage:

int[] t = { 0, 1, 2, 3, 4, 5, 6 };
bool isSeq = IsSequential(t);
// returns true

int[] t = { 0, 1, 2, 3, 3, 4, 5, 6 };
bool isSeq = IsSequential(t);
// returns false

int[] t = { 0, 1, 2, 4, 5, 6 };
bool isSeq = IsSequential(t);
// returns false

 

Sort a Dictionary with Key or Value

Sort a Dictionary on the Key:

var mDict = new Dictionary<string, string>();
// fill the Dictionary with items here

// sort the Dictionary (IOrderedEnumerable) and convert back to Dictionary
var orderedDict = mDict.OrderBy(kv => kv.Key).ToDictionary(pair => pair.Key, pair => pair.Value);

And on the Value:

var mDict = new Dictionary<string, string>();
// fill the Dictionary with items here

// sort the Dictionary (IOrderedEnumerable) and convert back to Dictionary
var orderedDict = mDict.OrderBy(kv => kv.Value).ToDictionary(pair => pair.Key, pair => pair.Value);

 

Subcategories