generic method to iterate property names and values

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1099 Views - Last Post: 13 January 2019 - 04:38 AM Rate Topic: -----

#16 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7397
  • View blog
  • Posts: 15,329
  • Joined: 16-October 07

Re: generic method to iterate property names and values

Posted 12 January 2019 - 03:17 AM

Ok, plinked on this this morning, for probably way too long... I normally avoid showing extensions, though it is the way I often solve problems. And, since I can see myself using this at some point...

I came up with.
public static class ExcelExt {
    // personal helper, without this I become mildly annoyed
    public static void ForEach<T>(this IEnumerable<T> xs, Action<T> f) { foreach (var x in xs) { f(x); } }
    public static void ForEach<T>(this IEnumerable<T> xs, Action<T, int> f) { int i = 0; foreach (var x in xs) { f(x, i++); } }

    public static ICell SetCellValueObject(this ICell cell, object value) {
        if (value == null) {
            cell.SetCellValue("");
        } else if (value is double) {
            cell.SetCellValue((double)value);
        } else if (value is int) {
            // no int in system
            if (double.TryParse(value.ToString(), out double n)) {
                cell.SetCellValue(n);
            } else {
                cell.SetCellValue(value.ToString());
            }
        } else if (value is DateTime) {
            // cell.SetCellType(CellType.)
            cell.SetCellValue((DateTime)value);
        } else if (value is IRichTextString) {
            cell.SetCellValue((IRichTextString)value);
        } else if (value is string) {
            cell.SetCellValue((string)value);
        } else if (value is bool) {
            cell.SetCellValue((bool)value);
        } else {
            cell.SetCellValue(value.ToString());
        }
        return cell;
    }


    public static IRow CreateCells(this IRow row, IEnumerable<Action<ICell>> actions, int colOffset = 0) {
        actions.ForEach((action, i) => action(row.CreateCell(i + colOffset)));
        return row;
    }

    public static IRow CreateCells(this IRow row, int cellCount, int colOffset = 0) {
        Enumerable.Range(0, cellCount).ForEach(i => row.CreateCell(i + colOffset));
        return row;
    }

    public static IRow CreateCells(this IRow row, IEnumerable<string> xs, int colOffset = 0) {
        xs.ForEach((x, i) => row.CreateCell(i + colOffset).SetCellValue(x));
        return row;
    }

    public static ISheet LoadSheetData<T>(this ISheet sheet, IEnumerable<T> items, IEnumerable<Action<ICell, T>> mapper, int rowOffset = 0, int colOffset = 0) {
        items.ForEach((item, idx) => sheet.CreateRow(rowOffset + idx).CreateCells(mapper.Select(f => CellMap(item, f))));
        return sheet;
        Action<ICell> CellMap(T item, Action<ICell, T> def) => (cell) => def(cell, item);
    }

    public static ISheet LoadSheetData<T>(this ISheet sheet, IEnumerable<T> items, IEnumerable<(string, Action<ICell, T>)> mapper, int rowOffset = 0, int colOffset = 0) {
        sheet.CreateRow(rowOffset).CreateCells(mapper.Select(x => x.Item1), colOffset);
        return sheet.LoadSheetData(items, mapper.Select(x => x.Item2), rowOffset + 1, colOffset);
    }
    public static List<(string, Action<ICell, T>)> ToSheetMapper<T>(IEnumerable<System.Reflection.PropertyInfo> propsToUse) {
        return propsToUse.Select(ToMap).ToList();
        Action<ICell, T> ToMapAction(System.Reflection.PropertyInfo pi) {
            return (cell, x) => cell.SetCellValueObject(pi.GetValue(x));
        }
        (string, Action<ICell, T>) ToMap(System.Reflection.PropertyInfo pi) =>
            (pi.Name, ToMapAction(pi));
    }

    public static List<(string, Action<ICell, T>)> ToSheetMapper<T>() =>
        ToSheetMapper<T>(typeof(T).GetProperties());

    public static ISheet LoadSheetData<T>(this ISheet sheet, IEnumerable<T> items, int rowOffset = 0, int colOffset = 0) =>
        sheet.LoadSheetData(items, ToSheetMapper<T>(), rowOffset, colOffset);
}



To me, the absolute weirdest thing about the lib is the lack of setting a cell value with an object. The second weirdest is that I don't see a way to format my cell.

Given that, I wanted to write a quick test case. As I'd been mocking up a .NET core demo, I had one that would work well. Some may recognize the sample data:
class ExcelTest {
    public class WeatherForecast {
        public DateTime Day { get; set; }
        public int TemperatureC { get; set; }
        public string Summary { get; set; }
        public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
    }
    private static string[] Summaries = new[] {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };
    private static Lazy<Random> rnd = new Lazy<Random>();
    private static string RandSummary() => Summaries[rnd.Value.Next(Summaries.Length)];
    private static WeatherForecast RandWeather(int idx) => new WeatherForecast {
        Day = DateTime.Now.AddDays(idx),
        TemperatureC = rnd.Value.Next(-20, 55),
        Summary = RandSummary()
    };

    public static IEnumerable<WeatherForecast> WeatherForecasts(int sampleSize = 5) => 
        Enumerable.Range(1, sampleSize).Select(RandWeather);

    private static string TsFilename(string sheetName) => 
        $"{sheetName}{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";

    public static void RunTest() {
        var sheetName = "Weather";
        using (var fs = new FileStream(TsFilename(sheetName), FileMode.Create, FileAccess.Write)) {
            var workbook = new XSSFWorkbook();
            workbook.CreateSheet(sheetName).LoadSheetData(WeatherForecasts(100));
            workbook.Write(fs);
        }
    }
}



This worked so well it was surprising. Again, here, you can cache the results of the sheet mapper and avoid interrogating the type again. Or, hell, generate some result code from it and never run it in production.
Was This Post Helpful? 1
  • +
  • -

#17 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 6763
  • View blog
  • Posts: 23,064
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 12 January 2019 - 02:57 PM

Since you are already using the newer C# support for local methods, might as well also use C#'s pattern matching:
switch (value)
{
case double d:              cell.SetCellValue(d);                   break;
case int n:                 cell.SetCellValue(n);                   break;
case DateTime dt:           cell.SetCellValue(dt);                  break;
case IRichTextString rts:   cell.SetCellValue(rts);                 break;
case string s:              cell.SetCellValue(s);                   break;
case bool b:                cell.SetCellValue(b );                  break;
default:                    cell.SetCellValue(value?.ToString());   break;
}


Or if you don't believe in switch statements:
if (value == null) {
    cell.SetCellValue("");
} else if (value is double d) {
    cell.SetCellValue(d);
} else if (value is int n) {
    cell.SetCellValue(n);
} else if (value is DateTime dt) {
    cell.SetCellValue(dt);
} else if (value is IRichTextString rts) {
    cell.SetCellValue(rts);
} else if (value is string s) {
    cell.SetCellValue(s);
} else if (value is bool b ) {
    cell.SetCellValue(b );
} else {
    cell.SetCellValue(value.ToString());
}
return cell;



As an aside, I took out the trying to parse the int into a double because the compiler should be able to handle passing an int into a method that takes a double based on this small test I have:
using System;

namespace GetLocationProperty
{
    interface IRichTextString
    {
    }

    class Cell
    {
        public void SetCellValue(double d) { }
        public void SetCellValue(string s) { }
        public void SetCellValue(bool B)/>/>/>/> { }
        public void SetCellValue(DateTime dt) { }
        public void SetCellValue(IRichTextString rts) { }
    }

    class Program
    {
        static void Main(string[] args)
        {
            object value = 12;
            Cell cell = new Cell();
 
            if (value == null)
            {
                cell.SetCellValue("");
            }
            else if (value is double d)
            {
                cell.SetCellValue(d);
            }
            else if (value is int n)
            {
                cell.SetCellValue(n);
            }
            else if (value is DateTime dt)
            {
                cell.SetCellValue(dt);
            }
            else if (value is IRichTextString rts)
            {
                cell.SetCellValue(rts);
            }
            else if (value is string s)
            {
                cell.SetCellValue(s);
            }
            else if (value is bool B)/>/>/>/>
            {
                cell.SetCellValue(B)/>/>/>/>;
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }
    }
}


This post has been edited by Skydiver: 12 January 2019 - 03:00 PM
Reason for edit:: Put spaces between 'b' an closing parenthesis to make forum software happy.

Was This Post Helpful? 1
  • +
  • -

#18 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7397
  • View blog
  • Posts: 15,329
  • Joined: 16-October 07

Re: generic method to iterate property names and values

Posted 12 January 2019 - 04:04 PM

I like the switch, if not the really finicky spacing. The special int case was more to highlight the odd absence in the ICell.

Test case is also nice, though it isn't highlighting the result, so:
namespace GetLocationProperty {
    interface IRichTextString { }

    class Cell {
        public void SetCellValue(double x) { Debug.WriteLine($"double {x}"); }
        public void SetCellValue(string x) { Debug.WriteLine($"string {x}"); }
        public void SetCellValue(bool x) { Debug.WriteLine($"bool {x}"); }
        public void SetCellValue(DateTime x) { Debug.WriteLine($"DateTime {x}"); }
        public void SetCellValue(IRichTextString x) { Debug.WriteLine($"IRichTextString {x}"); }
        public void SetCellValueObject(object value) {
            switch (value) {
                case double x: SetCellValue(x); break;
                case int x: SetCellValue(x); break;
                case DateTime x: SetCellValue(x); break;
                case IRichTextString x: SetCellValue(x); break;
                case string x: SetCellValue(x); break;
                case bool x: SetCellValue(x); break;
                default: SetCellValue(value?.ToString()); break;
            }
        }
        public static void Test() {
            var cell = new Cell();
            cell.SetCellValueObject(null);
            cell.SetCellValueObject("Hi");
            cell.SetCellValueObject(123);
            cell.SetCellValueObject(123.4);
            cell.SetCellValueObject(DateTime.Now);
            cell.SetCellValueObject(true);
        }
    }
}


Was This Post Helpful? 1
  • +
  • -

#19 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 6763
  • View blog
  • Posts: 23,064
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 12 January 2019 - 04:15 PM

I just realized there is no check in the code above for the decimal type. I'm unsure if a conversion to double would be safe or appropriate; or if it should be treated as a string.
Was This Post Helpful? 0
  • +
  • -

#20 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6735
  • View blog
  • Posts: 27,712
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 13 January 2019 - 04:38 AM

Thank you for this great stuff. I'm on my phone at the mo but will study it all when I get a chance.

One thing I will do is to write a method to convert PascalNames to Spaced Words for column headings. Perhaps I'll add it as a small challenge topic ;) as it is reasonably interesting. I could extend it slightly by requiring any ending of Id to uppercase ID.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2