none
How to loop by row then cloumn without coulmn name RRS feed

  • Question

  • I am working on a Open XML SDK project to export tables/entity models to excel, however I can't figure out how to iterate the table without the strongly type column names. Can anyone give me some guidance? Thank you.
    Wednesday, October 25, 2017 11:00 PM

All replies

  • Hi,

    >>I am working on a Open XML SDK project to export tables/entity models to excel.

    What type of project  do you use? WPF?

    If you want to export  DataGrid to Excel in WPF, following should be helpful.

    http://www.c-sharpcorner.com/article/exporting-datagrid-to-excel-in-wpf/

    https://kidaatlantis.wordpress.com/2013/11/04/data-export-from-datagrid-to-excel-in-wpf/

    If I have any misunderstanding, maybe you can rephrase your question and give us more context.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 26, 2017 9:40 AM
  • I am currently working with the code via a console application... just trying to solve this piece.

    I am trying to create a generic method to export database tables to excel using Entity Framework and the Open XML SDK. I have the Open XML SDK code/method worked out, however when writing the data to XML I need to loop through each row by column. The challenge is (which usually is a benefit) the strongly typed column names. I can't figure out how to loop through each column of any table/model passed to the method. I tried Reflection getting the property names, however this returns more than just the actual table columns e.g. it also returns Navigation properties/related tables. Also, I would like to avoid Reflection if possible, it is my understanding that it is not the most efficient/performance drag.

    Any advice would be greatly appreciated. Thank you.

    Code snippets below:

     class Program
        {
            static void Main(string[] args)
            {
                EF.NorthwindEntities db = new EF.NorthwindEntities();
    
                var custList = from c in db.Customers
                               select c;
    
                foreach (var row in custList) 
                {
                    foreach (var col in row) // for (int i=0; > row.Length; i++)  Console.Write($"{col[i]}");
                    {
                        Console.Write($"{col}");
                    }
                }
    
                foreach (var row in custList)
                {
                    Console.Write($"{row.ContactName} { row.CompanyName} {row.Address} {row.City} {row.PostalCode}");
                }
            }
         }

    Open XML SDK code snippet:

    var columnNames = typeof(Customer).GetProperties().Select(property => property.Name).ToArray();  
    
    foreach (var c in columnNames)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(c);
                            headerRow.AppendChild(cell);
                        }
    
    
                        sheetData.AppendChild(headerRow);
    
                    foreach (var row in custList)
                        {
                         
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
              
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell1 = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell1.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell1.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(row.CompanyName); //
                                newRow.AppendChild(cell1);
    
                                DocumentFormat.OpenXml.Spreadsheet.Cell cell2 = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell2.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell2.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(row.ContactName); //
                                newRow.AppendChild(cell2);
    
                            sheetData.AppendChild(newRow);
                        }


    Friday, October 27, 2017 12:11 PM
  • https://stackoverflow.com/questions/22683040/how-to-check-all-properties-of-an-object-whether-null-or-empty

    You can loop through the collection of objects using a foreach.

    You then have to loop  through each property of the object, a the example shows how to loop through the properties of the object.

    Monday, October 30, 2017 7:32 PM
  • Hi,

    Maybe following articles would helpful to you.

    https://www.codeproject.com/Tips/676675/Entity-Model-to-Excel-using-LINQ-in-Csharp

    EPPlus-Create advanced Excel spreadsheets on the server

    Sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 31, 2017 5:36 AM