Using LinqPad's native Spreadsheet column header issue

neilpendlington

I'm in the awkward position of needing to support a .net framework solution so I am tied entirely to LinqPad 5.
As such, I'm hoping that the improvement (dare I say, fix? ) could be back-ported.

var importWorkbook = Util.ToSpreadsheet(values, "Test", true);

Where values is a List<Foo>, and Foo is:

public class Foo
{
     public string HotPotato;
}

My issue is that the schema of the spreadsheet I need to output is fixed and the column headers need space characters in them.
I had hoped that I could decorate the property with PropertyNameAttribute and override the name, but looking at the logic it's just building the headers based on a .GetProperties() call to the type.

As a workaround, I also tried to manipulate the .Cells directly on the worksheet but couldn't get it to override the column value.

sgmoore

If you are looking for a workaround would it be feasible to convert values to a DataTable and change the ColumnName?

For example, if you use FastMember nuget package you can do something like

    DataTable table = new DataTable();
    using (var reader = FastMember.ObjectReader.Create(values)) 
        table.Load(reader);

    table.Columns[0].ColumnName = "Hot Potato";

    var importWorkbook = Util.ToSpreadsheet(table, "Test", true);

BCinU

You can make it like this :
``

void Main()
{
    var values = new List<Foo>
    {
        new Foo { HotPotato = "Pomme de terre chaude", Quantity = 5 },
        new Foo { HotPotato = "Très chaude", Quantity = 10 }
    };

    (string SheetName, IEnumerable<Foo> Data, Func<Foo, IDictionary<string, object>> Map)[] sheets =
    {
        ("Sheet1", values, (Foo f) => new Dictionary<string, object>
        {
            ["Hot Potato"] = f.HotPotato,
            ["Quantity (kg)"] = f.Quantity
        })
    };

    ExportExcelMultiSheetsLocal(@"C:\Temp\Test.xlsx", sheets);
}

public class Foo
{
    public string HotPotato { get; set; }
    public int Quantity { get; set; }
}

public static void ExportExcelMultiSheetsLocal<T>(
    string outputExcelFile,
    IEnumerable<(string SheetName, IEnumerable<T> Data, Func<T, IDictionary<string, object>> Map)> sheets,
    LINQPad.Spreadsheet.WorksheetOptions options = null)
{
    if (options == null)
        options = new LINQPad.Spreadsheet.WorksheetOptions
        {
            FreezeTopRow = true,
            AutoSizeColumns = true,
            AutoFilter = true
        };

    var wb = new LINQPad.Spreadsheet.Workbook();

    foreach (var (SheetName, Data, Map) in sheets)
    {
        var rows = Data.Select(Map).ToList();

        // If the list is empty, create a line with only the headers.
        if (!rows.Any())
        {
            var templateKeys = Map(default(T)).Keys; 
            var emptyRow = templateKeys.ToDictionary(k => k, k => (object)null);
            rows.Add(emptyRow);
        }

        wb.AddSheet(rows.Cast<IDictionary<string, object>>(), SheetName, true, options);
    }

    wb.Save(outputExcelFile);
    wb.Open();
}

JeffMercado

I believe this should still be possible in 5, but you should be able to use an ExpandoObject to be able to generate arbitrary property names and thus headers. You'd just have to map them over prior to generating the spreadsheet. I use this a lot when making custom ToDump() methods.

var mapped = values.Select(v =>
{
    IDictionary<string, object> map = new ExpandoObject();
    map["Hot Potato"] = v.HotPotato;
    return map;
});
var importWorkbook = Util.ToSpreadsheet(mapped, "Test", true);

BCinU

My code is running fine from Lindpad5 to Linqpad9. I just extracted a piece of code of more complicated extension method I wrote.