MileageTraker/Web/Utility/ExcelWriter.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using ExcelLibrary.SpreadSheet;
using MileageTraker.Web.Models;
using MileageTraker.Web.ViewModels;

namespace MileageTraker.Web.Utility
{
	public abstract class ExcelWriter<T>
	{
		public static void WriteXls(IEnumerable<T> items, string filename, string worksheetTitle, string worksheetName)
		{
			using (var fileStream = new FileStream(filename, FileMode.Create))
				WriteXls(items, fileStream, worksheetTitle, worksheetName);
		}

		public static byte[] WriteXls(IEnumerable<T> items, string worksheetTitle, string worksheetName)
		{
			using (var stream = new MemoryStream())
			{
				WriteXls(items, stream, worksheetTitle, worksheetName);
				return stream.ToArray();
			}
		}

		public static void WriteXls(IEnumerable<T> items, Stream stream, string worksheetTitle, string worksheetName)
		{
			var workbook = new Workbook();

			workbook.Worksheets.Add(WriteWorksheet(items, worksheetTitle, worksheetName));

			workbook.Save(stream);
		}

		protected static IEnumerable<PropertyInfo> GetProperties()
		{
			return
				typeof (T).GetProperties()
				          .Where(p => !p.PropertyType.IsNonStringEnumerable())
						  .Where(p => !p.IsHiddenFromModel())
				          .Where(p => !p.Name.Contains("PreviousLog"))
				          .ToList();
		}

		public static int GetPropertyCount()
		{
			return GetProperties().Count();
		}

		public static Worksheet WriteWorksheet(IEnumerable<T> items, string worksheetTitle, string worksheetName)
		{
			var worksheet = new Worksheet(worksheetName);

			// write worksheet header
			worksheet.Cells[0, 3] = new Cell(worksheetTitle);

			WriteColumnHeaders(worksheet, 2);

			WriteItems(worksheet, items, 3);

			PadWorksheet(worksheet);
			return worksheet;
		}

		public static void WriteColumnHeaders(Worksheet worksheet, int startRow = 0, int startCol = 0, string formatString = "")
		{
			// write column headers
			GetProperties().Zip(
				CustomExtensions.GetNumbers().Skip(startCol),
				(p, c) =>
				worksheet.Cells[startRow, c] = new Cell(p.GetName(), formatString)
				).ToList();
		}

		public static void WriteItems(Worksheet worksheet, IEnumerable<string> items, int startRow = 0, int startCol = 0)
		{
			var r = startRow;

			foreach (var cell in items.Select(item => new Cell(item)))
			{
				worksheet.Cells[r, startCol] = cell;
				r++;
			}
		}

		public static void WriteItems(Worksheet worksheet, IEnumerable<T> items, int startRow = 0, int startCol = 0)
		{
			// write the data
			items.Zip(
				CustomExtensions.GetNumbers().Skip(startRow),
				(item, r) =>
				GetProperties().Zip(CustomExtensions.GetNumbers().Skip(startCol),
				    (p, c) => {
					        var value = p.GetValue(item);

					        string formatString = null;

					        if (value is decimal) // assume that it's currency
						        formatString = "#,##0.00";

					        if (value is DateTime && p.Name.Contains("Date"))
						        formatString = @"MM/DD/YYYY";
					        else if (value is DateTime)
						        formatString = @"MM/DD/YYYY hh:mm:ss";

					        if (p.Name == "GasPurchased") // format to the .000 place
						        formatString = "#,##0.000";

                            if (value is string valInt && int.TryParse(valInt, out var intValue))
						        value = intValue;

                            if (value is string valDbl && double.TryParse(valDbl, out var doubleValue))
						        value = doubleValue;

					        if (value is MileageLogTypeWrapper valMt)
						        value = valMt.Enum.GetDisplayName();

                            if (value is SelectListViewModel valSelList)
                                value = valSelList.ToString();

					        if (value == null)
						        value = p.GetNullDisplayText(item);

					        var cell = 
                                formatString != null 
                                    ? new Cell(value, formatString) 
                                    : new Cell(value);
					        worksheet.Cells[r, c] = cell;

					        return (string) null;
					    }).ToList()).ToList();
		}

		/// <summary>
		/// Fill the worksheet with some empty space if not "enough" cells
		/// </summary>
		public static void PadWorksheet(Worksheet worksheet)
		{
			Func<int> totalCells = () => (worksheet.Cells.LastRowIndex + 1)*(worksheet.Cells.LastColIndex + 1);
			for (var r = worksheet.Cells.LastRowIndex + 1; totalCells() < 320; r++)
			{
				for (var c = 0; c < worksheet.Cells.LastColIndex; c++)
				{
					worksheet.Cells[r, c] = new Cell("");
				}
			}
		}
	}
}