Helpers + Extensions

Xy.ExcelMigration.cs

#region namespace Xy.ExcelMigration
namespace Xy.ExcelMigration
{
    using System.ComponentModel.DataAnnotations.Schema;
    using Xy.Office.ExcelExtensions;
    using Excel = Microsoft.Office.Interop.Excel;

    public interface IExistingColumnAction
    {
        string OldColumnName { get; }
    }
    public interface IColumnAction<TEntity>
    {
        void Apply(Excel.Worksheet worksheet, int destinationColumnIndex);
    }
    public class UpdateColumn<TEntity> : IColumnAction<TEntity>, IExistingColumnAction
    {
        public string OldColumnName { get; private set; }
        public string ColumnName { get; private set; }
        public Action<Excel.Range>[] Transforms { get; private set; }

        /// <param name="columnName">Original column name</param>
        /// <param name="newName">New name to give, if omitted the column will be deleted</param>
        /// <param name="transforms">Set of instructions to be applied on the column</param>
        public UpdateColumn(string oldColumnName, Expression<Func<TEntity, object>> propertySelector, params Action<Excel.Range>[] transforms)
        {
            this.OldColumnName = oldColumnName;
            this.ColumnName = Reflexion.GetPropertyName(propertySelector);
            this.Transforms = transforms;
        }

        public void Apply(Excel.Worksheet worksheet, int destinationColumnIndex)
        {
            var index = worksheet.UsedRange
                .Rows[1].AsRange()
                .Cells.Cast<Excel.Range>()
                .First(x => Convert.ToString(x.Value) == OldColumnName)
                .Column;
            worksheet.Cells[1, index].AsRange().Value = ColumnName;

            foreach (var transform in Transforms)
            {
                var column = worksheet
                    .Cells[2, index].AsRange()
                    .Resize[worksheet.UsedRange.Rows.Count - 1].AsRange();

                transform(column);
            }

            if (destinationColumnIndex != index)
            {
                worksheet.Columns[destinationColumnIndex].AsRange()
                    .Insert(Excel.XlInsertShiftDirection.xlShiftToRight, worksheet.Columns[index].AsRange().Cut());
            }
        }
    }
    public class CreateColumn<TEntity> : IColumnAction<TEntity>
    {
        public string ColumnName { get; private set; }

        public CreateColumn(Expression<Func<TEntity, object>> propertySelector)
        {
            this.ColumnName = Reflexion.GetPropertyName(propertySelector);
        }

        public void Apply(Excel.Worksheet worksheet, int destinationColumnIndex)
        {
            worksheet.Columns[destinationColumnIndex].AsRange().Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
            worksheet.Cells[1, destinationColumnIndex].AsRange().Value = ColumnName;
        }
    }
    public class DeleteColumn<TEntity> : IColumnAction<TEntity>, IExistingColumnAction
    {
        public string OldColumnName { get; private set; }

        public DeleteColumn(string oldColumnName)
        {
            this.OldColumnName = oldColumnName;
        }

        public void Apply(Excel.Worksheet worksheet, int destinationColumnIndex)
        {
            worksheet.UsedRange
                .Rows[1].AsRange()
                .Cells.Cast<Excel.Range>()
                .First(x => Convert.ToString(x.Value) == OldColumnName)
                .EntireColumn.Delete();
        }
    }

    public abstract class TableActionBase<TEntity> : List<IColumnAction<TEntity>>
    {
        public abstract void Apply();
    }
    public class TableTransform<TEntity> : TableActionBase<TEntity>
    {
        private Excel.Worksheet worksheet;
        private int headerRows;

        public TableTransform(Excel.Worksheet worksheet, int headerRows = 0)
        {
            this.worksheet = worksheet;
            this.headerRows = headerRows;
        }

        public override void Apply()
        {
            worksheet.Activate();
            DeleteHeaderRows();
            AssertColumnIntegrity();

            int index = 1;
            foreach (var columnAction in this)
            {
                columnAction.Apply(worksheet, index);

                if (!(columnAction is DeleteColumn<TEntity>))
                    index++;
            }

            worksheet.UsedRange.Rows.AutoFit();
            worksheet.UsedRange.Columns.AutoFit();

            worksheet.ListObjects.Add(Source: worksheet.UsedRange, XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes)
                .Name = typeof(TEntity).Name;
        }

        public void DeleteHeaderRows()
        {
            if (headerRows != 0)
            {
                worksheet.Rows[$"1:{headerRows}"].AsRange().EntireRow.Delete();
            }
        }
        public void GenerateColumnActions()
        {
            worksheet.UsedRange
                .Rows[1].AsRange()
                .Value.As<object[,]>().Cast<object>()
                .Select(x => $"new DeleteColumn<{typeof(TEntity).Name}>(\"{x}\"),")
                .Dump();
        }
        public void AssertColumnIntegrity()
        {
            // check if UsedRange is correct, since working with 1,048,576 x 16,384 cells is a fucking PITA
            if (worksheet.UsedRange.Rows.Count > 5000 || worksheet.UsedRange.Columns.Column > Count)
            {
                // if we landed here, it is very likely that the UsedRange is returning the whole table
                throw new InvalidDataException("There is way too many row/column than expected: " + worksheet.UsedRange.Address + Environment.NewLine +
                    "TODO: manually delete all empty rows and columns. (delete, not clear)");
            }

            var titleRow = (Excel.Range)worksheet.UsedRange.Rows[1].AsRange();
            var columnHeaders = titleRow
                .Value.As<object[,]>()
                .Cast<object>()
                .Select(x => Convert.ToString(x));

            var columnNames = this
                .OfType<IExistingColumnAction>()
                .Select(x => x.OldColumnName);
            if (!columnHeaders.OrderBy(x => x).SequenceEqual(columnNames.OrderBy(x => x)))
            {
                Util.HorizontalRun(true, columnNames, columnHeaders)
                    .Dump("Expected columns vs Actual columns", 0);
                columnNames
                    .Select(x => string.Format("new UpdateColumn<{1}>(\"{0}\", null),", x, typeof(TEntity).Name))
                    .Dump("UpdateColumn...", 0);
                throw new InvalidDataException("Invalid table schema: " + worksheet.Name);
            }
        }
    }

    public static class ColumnActions
    {
        public static Action<Excel.Range> Convert<T>(Func<object, T> converter)
        {
            return column => column.Value = column.Application.WorksheetFunction.Transpose(column
                .Value.As<object[,]>().Cast<object>()
                .Select(converter)
                .ToArray());
        }

        public static Action<Excel.Range> Map<T>(IDictionary<object, T> mappings)
        {
            return Convert(x => mappings[x]);
        }
    }

    public static class Reflexion
    {
        public static string GetPropertyName(Expression expression)
        {
            switch (expression.NodeType)
            {
                case ExpressionType.Lambda: return GetPropertyName(((LambdaExpression)expression).Body);
                case ExpressionType.Convert: return GetPropertyName(((UnaryExpression)expression).Operand);
                case ExpressionType.MemberAccess: return ((MemberExpression)expression).Member.Name;
            }

            return null;
        }
    }
}
#endregion

Xy.Logging.cs

#region namespace Xy.Logging
namespace Xy.Logging
{
    using System.Runtime.CompilerServices;
    using Splat;
    
    public class ConsoleLogger : ILogger
    {
        public LogLevel Level { get; set; }

        public ConsoleLogger()
        {
            this.Level = LogLevel.Info;
        }

        public void Write(string message, LogLevel level)
        {
            if ((int)level < (int)Level)
                return;

            Console.WriteLine(message);
        }
    }

    /// <summary>Logger that writes stylized and timestamped message into LINQPad result panel</summary>
    public class StylizedConsoleLogger : ILogger
    {
        public LogLevel Level { get; set; }

        private IDictionary<LogLevel, string> styles;

        /// <param name="styles">Use css for style</param>
        public StylizedConsoleLogger(IDictionary<LogLevel, string> styles = null)
        {
            this.Level = LogLevel.Info;

            // default styles is color coded based on level and monospaced
            this.styles = styles ?? typeof(LogLevel).GetEnumValues().Cast<LogLevel>()
                .Zip(new[] { "Green", "Black", "Orange", "Red", "DarkRed" }, Tuple.Create)
                .ToDictionary(x => x.Item1, x => string.Format("color: {0}; font-family: 'Lucida Console', Monaco, monospace;", x.Item2));
        }

        public void Write(string message, LogLevel level)
        {
            if ((int)level < (int)Level)
                return;

            Util.WithStyle(
                string.Format("> {0:yyyy-MM-dd HH:mm:ss.fff}: {1}", DateTime.Now, message),
                styles[level]
                ).Dump();
        }
    }

    /// <summary>Cached logger that writes log message on behalf of the given class.</summary>
    /// <remarks>Static class are also support.</remarks>
    public class TypeNamedLogger
    {
        public IFullLogger Instance { get { return instance.Value; } }

        private readonly Lazy<IFullLogger> instance;

        /// <summary>Create a logger named after current method's declaring type.</summary>
        public TypeNamedLogger()
        {
            var frame = new StackFrame(1);
            var type = frame.GetMethod().DeclaringType;

            instance = new Lazy<IFullLogger>(() => GetLogger(type));
        }

        /// <summary>Create a logger named after the given type.</summary>
        public TypeNamedLogger(Type type)
        {
            instance = new Lazy<IFullLogger>(() => GetLogger(type));
        }

        private static IFullLogger GetLogger(Type type)
        {
            // taken from LogHost.Default.get
            var factory = Locator.Current.GetService<ILogManager>();
            if (factory == null)
                throw new Exception("ILogManager is null. This should never happen, your dependancy resolver is broken.");

            return factory.GetLogger(type);
        }
    }

    public static class ExceptionExtensions
    {
        public static TException BindContext<TException>(this TException exception, IDictionary context)
            where TException : Exception
        {
            foreach (DictionaryEntry entry in context)
            {
                exception.Data[entry.Key] = entry.Value;
            }

            return exception;
        }
    }
    public static class LoggingExtensions
    {
        public static void LogCurrentMethod(this IFullLogger logger, LogLevel level, [CallerMemberName]string caller = null)
        {
            new Dictionary<LogLevel, Action<string>>
                {
                    { LogLevel.Debug, logger.Debug },
                    { LogLevel.Info, logger.Info },
                    { LogLevel.Warn, logger.Warn },
                    { LogLevel.Error, logger.Error },
                    { LogLevel.Fatal, logger.Fatal },
                }[level](FormatHeader(caller));
        }
        private static string FormatHeader(string message, char padding = '=', int totalLength = 80)
        {
            if (message.Length > totalLength)
                return message;

            const int TwoSpaces = 2;
            var length = totalLength - message.Length - TwoSpaces;
            return string.Join(" ",
                new string(padding, length - length / 2),
                message,
                new string(padding, length / 2));
        }
    }
    public static class PrettifyingExtensions
    {
        public static string Prettify<TKey, TValue>(this IEnumerable<KeyValuePair<TKey, TValue>> pairs, string itemSeparator = ", ", string keyValueSeparator = "=")
        {
            return string.Join(itemSeparator, pairs
                .Select(x => x.Key + keyValueSeparator + x.Value));
        }
    }
}
#endregion

Xy.Office.cs

using System.Runtime.InteropServices;
using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;
using Outlook = Microsoft.Office.Interop.Outlook;

public static class OfficeHelper
{
    public static Word.Application GetWordApplication()
    {
        const string ProgID = "Word.Application";

        try
        {
            return Marshal.GetActiveObject(ProgID) as Word.Application;
        }
        catch
        {
            return new Word.ApplicationClass() { Visible = true };
        }
    }
    public static Excel.Application GetExcelApplication(bool createInstance = true)
    {
        const string ProgID = "Excel.Application";

        try
        {
            return Marshal.GetActiveObject(ProgID) as Excel.Application;
        }
        catch
        {
            if (!createInstance)
                throw new InvalidOperationException(ProgID + " was not started");
            
            return new Excel.ApplicationClass() { Visible = true };
        }
    }
    public static Outlook.Application GetOutlookApplication()
    {
        const string ProgID = "Outlook.Application";

        try
        {
            return Marshal.GetActiveObject(ProgID) as Outlook.Application;
        }
        catch
        {
            return new Outlook.ApplicationClass();
        }
    }
}