generic method to iterate property names and values

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

generic method to iterate property names and values

Posted 11 January 2019 - 02:49 AM

I have the (ASP.NET Core) controller action below to create an Excel workbook based on the property names and values of IEnumerable<ContactExport>. I would like to create a generic method that would then create a spreadsheet regardless of the type. I will have CVExport which will, again, only include the field names that I actually wish to go into Excel. (ContactExport is effectively a ViewModel that reduces the original Contact model to specific fields for export.)

        [Route("Contacts")]
        [HttpPost]
        public async Task<ActionResult> ExportContacts([FromBody]IEnumerable<ContactExport> contacts)
        {
            var webRoot = _env.WebRootPath;
            var excelPath = webRoot + "/excel";
            var timestamp = DateTime.Now.ToString("ddMMyyHHmmss");
            var filePath = Path.Combine(excelPath, $"Contacts{timestamp}.xlsx");

            FileInfo fileInfo = new FileInfo(filePath);
            MemoryStream memory = new MemoryStream();

            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Contacts");

                int rowIndex = 0;

                IRow header = sheet.CreateRow(rowIndex++);
                header.CreateCell(0).SetCellValue("Contact ID");
                header.CreateCell(1).SetCellValue("First Name");
                header.CreateCell(2).SetCellValue("Last Name");
                header.CreateCell(2).SetCellValue("Job Title");
                header.CreateCell(2).SetCellValue("Office");
                header.CreateCell(2).SetCellValue("Email");
                header.CreateCell(2).SetCellValue("Phone");
                header.CreateCell(2).SetCellValue("Mobile");

                foreach (var contact in contacts)
                {
                    IRow row = sheet.CreateRow(rowIndex++);
                    row.CreateCell(0).SetCellValue(contact.ContactID);
                    row.CreateCell(1).SetCellValue(contact.FirstName);
                    row.CreateCell(2).SetCellValue(contact.LastName);
                    row.CreateCell(2).SetCellValue(contact.JobTitle);
                    row.CreateCell(2).SetCellValue(contact.Office);
                    row.CreateCell(2).SetCellValue(contact.Email);
                    row.CreateCell(2).SetCellValue(contact.Phone);
                    row.CreateCell(2).SetCellValue(contact.Mobile);
                }

                workbook.Write(fs);
            }

            return Json(new { Stamp = timestamp, Type = "Contacts", Status = "OK" });
        }

I know this would use Reflection, which I haven't played with too often, and code such as the following

PropertyInfo[] properties = user.GetType().GetProperties();
foreach (PropertyInfo pi in properties)
{
    sb.Append(
        string.Format("Name: {0} | Value: {1}", 
                pi.Name, 
                pi.GetValue(user, null)
            ) 
    );
}

or maybe

List<PropertyInfo> propDictionary = someObject.GetType()
     .GetProperties(BindingFlags.Instance | BindingFlags.Public)
          .ToList<PropertyInfo>();

Where would I create this generic method? Do I need to create a static class and/or interface? Or could it just be a method of the controller?

I just need a little push in the right direction.

Is This A Good Question/Topic? 0
  • +

Replies To: generic method to iterate property names and values

#2 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 03:13 AM

I've just added the following method that works into the controller but would like some guidance on where this should be placed and whether this is a good approach.

        private void DataToExcel<T>(IEnumerable<T> data)
        {
            List<PropertyInfo> propDictionary = typeof(T)
                .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .ToList<PropertyInfo>();

            foreach (PropertyInfo property in propDictionary)
            {
                Debug.WriteLine($"{property.Name}");
            }

            foreach (var item in data)
            {
                foreach (PropertyInfo property in propDictionary)
                {
                    Debug.WriteLine($"{item.GetType().GetProperty(property.Name).GetValue(item)}");
                }
                
            }
        }

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 03:37 AM

Sorry, I've been pressing ahead with this (but still encourage advice). I have this method on the controller, which works:

        private IWorkbook DataToExcel<T>(IEnumerable<T> data, string sheetName)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);

            List<PropertyInfo> propDictionary = typeof(T)
                .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .ToList<PropertyInfo>();

            int rowIndex = 0;
            int colIndex = 0;

            IRow header = sheet.CreateRow(rowIndex++);

            foreach (PropertyInfo property in propDictionary)
            {
                header.CreateCell(colIndex++).SetCellValue(property.Name);
            }
            colIndex = 0;

            foreach (var item in data)
            {
                IRow row = sheet.CreateRow(rowIndex++);

                foreach (PropertyInfo property in propDictionary)
                {
                    row.CreateCell(colIndex++).SetCellValue(item
                        .GetType().GetProperty(property.Name).GetValue(item).ToString());
                }
                colIndex = 0;
            }
            return workbook;
        }

A further question I could add is, rather than ToString() for writing to Excel, how do I determine if the current value is numeric?
Was This Post Helpful? 0
  • +
  • -

#4 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 411
  • Joined: 01-February 16

Re: generic method to iterate property names and values

Posted 11 January 2019 - 04:40 AM

Firstly, why do you need to know if the value is Numeric if you are appending the value to excel? In my experience anything written to the workbook is a string value and Excel then figures out what it is. Or you apply a format to the cell that represents a number, only then do you need to know if it is a string value or not. Why do you need to know if it is not a string?

EDIT: It looks like you are not using COM objects, how are you creating the Excel file?

This post has been edited by JapanDave: 11 January 2019 - 04:43 AM

Was This Post Helpful? 1
  • +
  • -

#5 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 05:24 AM

Thank you for your response.

For Excel I am using a package NPOI. It's SetCellValue() method has 5 overloads with string, double, bool, etc. I wanted to test the double version to see if it additionally coerced the cell to being formatted for a number. Currently, as you suggest, Excel receives the values as text and they have the green warning for a numeric value being displayed as text. (If the double version didn't work I suppose I could add an additional statement to format the column.)
Was This Post Helpful? 1
  • +
  • -

#6 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 411
  • Joined: 01-February 16

Re: generic method to iterate property names and values

Posted 11 January 2019 - 05:58 AM

I have no idea how NPOI works so I can't say much, but with COM objects you can format the cell as you please, testing if a value is a string is or something else entirely up to how you would normally do this this in C#. I personally prefer to use COM objects for the flexibility it provides, but it does take a bit of time to use it. Do you need to use NPOI?

Alternatively, why can't you just test the value using the normal C# tools for string, double, bool, etc?
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 06:06 AM

Quote

I personally prefer to use COM objects for the flexibility it provides, but it does take a bit of time to use it. Do you need to use NPOI?

I don't have time on my side and NPOI seems straightforward and is working, works with .NET Core (is named DotNetCore.NPOI).

Quote

Alternatively, why can't you just test the value using the normal C# tools for string, double, bool, etc?

I could do that (trying to parse the value, use a switch, etc.) but I think Reflection should be able to tell me what kind of value it is more directly.
Was This Post Helpful? 0
  • +
  • -

#8 JapanDave   User is offline

  • D.I.C Regular

Reputation: 35
  • View blog
  • Posts: 411
  • Joined: 01-February 16

Re: generic method to iterate property names and values

Posted 11 January 2019 - 06:38 AM

It seems like an easy alternative, but I still can't see why you would need to determine the type? But, that said, you have no option but to use C# and try parse the value to get the type if you want to do that?
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6664
  • View blog
  • Posts: 22,744
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 10:03 AM

Regarding the question where to put the code: I would put it a static class as an extension method:
public static class NpoiExtensions
{
    public static IWorkbook DataToExcel<T>(this IEnumerable<T> data, string sheetName)
    {
        // code here
    }
}


Was This Post Helpful? 2
  • +
  • -

#10 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6664
  • View blog
  • Posts: 22,744
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 10:09 AM

And as for the code above, I would change this:
foreach (PropertyInfo property in propDictionary)
{
    row.CreateCell(colIndex++).SetCellValue(item
        .GetType().GetProperty(property.Name).GetValue(item).ToString());
}



to:
foreach (PropertyInfo property in propDictionary)
{
    row.CreateCell(colIndex++)
       .SetCellValue(property.GetValue(item)
                             .ToString());
}



Reflection is expensive enough. Don't do it more often that you have to by repeatedly calling GetType() and GetProperty() when you already have the PropertyInfo readily available.
Was This Post Helpful? 0
  • +
  • -

#11 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 171
  • View blog
  • Posts: 1,019
  • Joined: 05-December 13

Re: generic method to iterate property names and values

Posted 11 January 2019 - 10:39 AM

If I recall, when using int.tryparse and int.parse; be sure to wrap int.parse in try catch because if your string is super long, it may overflow, resulting in exception. To note; While tryparse is silent, parse will overflow with an error.

Something else which I wasn't sure about that you said about identifying if a number was in fact a number. Well if you want to check declarations, you could check typeof(declaration) such as typeof(int) to determine a declaration type and then later check the value of those numbers using regex. Regex will also allow you to check pattern of chars which may help with my next question. Have you thought of anything regarding how to determine a value of specific types yet? If not, scope some regex patterns...

Woops, didn't see you had replied before submitting Sky. >.<

This post has been edited by Sheepings: 11 January 2019 - 11:10 AM

Was This Post Helpful? 0
  • +
  • -

#12 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6664
  • View blog
  • Posts: 22,744
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 10:54 AM

There is no need to guess at the type by trying to parse the string. Simply use the PropertyInfo that you already have: PropertyInfo.PropertyType
Was This Post Helpful? 1
  • +
  • -

#13 andrewsw   User is online

  • head thrashing
  • member icon

Reputation: 6667
  • View blog
  • Posts: 27,302
  • Joined: 12-December 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 12:20 PM

Yes, thanks, I thought there would be a way to reflect the type and avoid detailed parsing (and/Or regex). As mentioned, reflection is costly so might as well use it to the max.
Was This Post Helpful? 0
  • +
  • -

#14 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6664
  • View blog
  • Posts: 22,744
  • Joined: 05-May 12

Re: generic method to iterate property names and values

Posted 11 January 2019 - 02:07 PM

Part of the reason why I'm relatively familiar with PropertyInfo is I've embarked on a similar journey that you are on now with regards to serializing the property values of objects in a container. Four of the other things that you'll eventually want are:
  • A way to skip/hide some properties.
  • A way to set the order of the columns/properties.
  • A way to override the column header name (or in my case the dictionary key name).
  • A way to override the formatting of the data as its written out for a particular column. (Think more in the lines of WPF's value converter, instead of just the Crystal Reports style passing in a C# format string.)

Was This Post Helpful? 0
  • +
  • -

#15 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7361
  • View blog
  • Posts: 15,284
  • Joined: 16-October 07

Re: generic method to iterate property names and values

Posted 11 January 2019 - 05:12 PM

Without any reflection, and just for fun:
private void SetExcelRow(IRow row, IEnumerable<Action<ICell>> setValues) {
    int colNum = 0;
    foreach(var f in setValues) {
        f(row.CreateCell(colNum++));
    }
}
private void SetExcelRow(IRow row, IEnumerable<string> values) =>
    SetExcelRow(row, values.Select(x => new Action<ICell>((cell) => cell.SetCellValue(x))));

private void LoadSheet<T>(ISheet sheet, IEnumerable<T> items, IEnumerable<(string name, Action<ICell, T> setValue)> columnDefs) {
    int rowIndex = 0;
    SetExcelRow(sheet.CreateRow(rowIndex++), columnDefs.Select(x => x.name));
    foreach (var item in items) {
        SetExcelRow(sheet.CreateRow(rowIndex++), columnDefs.Select(x => CellMap(item, x.setValue)));
    }
    Action<ICell> CellMap(T item, Action<ICell, T> def) => (cell) => def(cell, item);
}

private void LoadSheet<T>(IWorkbook workbook, string sheetName, IEnumerable<T> items, IEnumerable<(string name, Action<ICell, T> setValue)> columnDefs) =>
    LoadSheet(workbook.CreateSheet(sheetName), items, columnDefs);

private void ToExcelFile<T>(string filePath, string sheetName, IEnumerable<T> items, IEnumerable<(string name, Action<ICell, T> setValue)> columnDefs) {
    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) {
        var workbook = new XSSFWorkbook();
        LoadSheet(workbook, sheetName, items, columnDefs);
        workbook.Write(fs);
    }
}
// ExportContacts([FromBody]IEnumerable<ContactExport> contacts)

private void ExportExcel<T>(string webRoot, string sheetName, IEnumerable<T> items, IEnumerable<(string name, Action<ICell, T> setValue)> columnDefs) =>
    ToExcelFile(Path.Combine($"{webRoot}/excel", $"{sheetName}{DateTime.Now.ToString("ddMMyyHHmmss")}.xlsx"), sheetName, items, columnDefs);

private void ExportContacts(IEnumerable<ContactExport> contacts) {
    ExportExcel(_env.WebRootPath, "Contacts", contacts, new (string name, Action<ICell, ContactExport> setValue)[] {
        ("Contact ID", (c,x) => c.SetCellValue(x.ContactID)),
        ("First Name", (c,x) => c.SetCellValue(x.FirstName)),
        ("Last Name", (c,x) => c.SetCellValue(x.LastName)),
        ("Job Title", (c,x) => c.SetCellValue(x.JobTitle)),
        ("Office", (c,x) => c.SetCellValue(x.Office)),
        ("Email", (c,x) => c.SetCellValue(x.Email)),
        ("Phone", (c,x) => c.SetCellValue(x.Phone))
    });
}



If you want to use some kind of reflection, you might investigate Custom Attributes to describe how you want the data layed out.

You could reasonably cache such mappings using something like the code above.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2