LoadingDataWithDynamicObjects.cs

/*************************************************************************************************
  Required Notice: Copyright (C) EPPlus Software AB. 
  This software is licensed under PolyForm Noncommercial License 1.0.0 
  and may only be used for noncommercial purposes 
  https://polyformproject.org/licenses/noncommercial/1.0.0/

  A commercial license to use this software can be purchased at https://epplussoftware.com
 *************************************************************************************************
  Date               Author                       Change
 *************************************************************************************************
  07/22/2020         EPPlus Software AB           EPPlus 5.2.1
 *************************************************************************************************/
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using OfficeOpenXml;
using OfficeOpenXml.LoadFunctions.Params;
using OfficeOpenXml.Table;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Text;

namespace EPPlusSamples.LoadingData
{
    public static class LoadingDataWithDynamicObjects
    {
        public static void Run()
        {
            // Create a list of dynamic objects
            dynamic p1 = new ExpandoObject();
            p1.Id = 1;
            p1.FirstName = "Ivan";
            p1.LastName = "Horvat";
            p1.Age = 21;
            dynamic p2 = new ExpandoObject();
            p2.Id = 2;
            p2.FirstName = "John";
            p2.LastName = "Doe";
            p2.Age = 45;
            dynamic p3 = new ExpandoObject();
            p3.Id = 3;
            p3.FirstName = "Sven";
            p3.LastName = "Svensson";
            p3.Age = 68;

            List<ExpandoObject> items = new List<ExpandoObject>()
            {
                p1,
                p2,
                p3
            };

            // Create a workbook with a worksheet and load the data into a table
            using(var package = new ExcelPackage(FileUtil.GetCleanFileInfo("04-LoadDynamicObjects.xlsx")))
            {
                var sheet = package.Workbook.Worksheets.Add("Dynamic");
                sheet.Cells["A1"].LoadFromDictionaries(items, c =>
                {
                    // Print headers using the property names
                    c.PrintHeaders = true;
                    // insert a space before each capital letter in the header
                    c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
                    // when TableStyle is not TableStyles.None the data will be loaded into a table with the 
                    // selected style.
                    c.TableStyle = TableStyles.Medium1;
                });
                package.Save();
            }

            // Load data from json (in this case a file)
            var jsonItems = JsonConvert.DeserializeObject<IEnumerable<ExpandoObject>>(File.ReadAllText(FileUtil.GetFileInfo("04-LoadingData", "testdata.json").FullName));
            using (var package = new ExcelPackage(FileUtil.GetCleanFileInfo("04-LoadJsonFromFile.xlsx")))
            {
                var sheet = package.Workbook.Worksheets.Add("Dynamic");
                sheet.Cells["A1"].LoadFromDictionaries(jsonItems, c =>
                {
                    // Print headers using the property names
                    c.PrintHeaders = true;
                    // insert a space before each capital letter in the header
                    c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
                    // when TableStyle is not TableStyles.None the data will be loaded into a table with the 
                    // selected style.
                    c.TableStyle = TableStyles.Medium1;
                });
                sheet.Cells["D:D"].Style.Numberformat.Format = "yyyy-mm-dd";
                sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column].AutoFitColumns();
                package.Save();
            }
        }
    }
}