Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Julien Chable

He blogs, you blog, I blog ...

Archives

[Open XML] From IEnumerable<T> to Spreadsheet document

If you want to dump an IEnumerable<T> instance into an Excel spreadsheet document (using the latest Open XML SDK v2 CTP), here’s the code (also available in the zip in attachment) :

    static class OpenXMLExtensions
    {
        public static void ToSpreadsheetML<T>(this IEnumerable<T> items, String path) where T : class
        {
            ToSpreadsheetML<T>(items, new FileStream(path, FileMode.OpenOrCreate));
        }

        public static void ToSpreadsheetML<T>(this IEnumerable<T> items, Stream stream) where T : class
        {
            List<String> headers = (from PropertyInfo p in typeof(T).GetProperties() select p.Name).ToList<String>();

            List<List<String>> values = new List<List<String>>(items.Count());
            foreach (T item in items)
            {
                List<String> valCols = new List<string>();
                object obj = null;
                foreach (PropertyInfo property in typeof(T).GetProperties())
                {
                    obj = property.GetValue(item, null);
                    if (obj == null)
                        valCols.Add("");
                    else
                        valCols.Add(obj.ToString());
                }
                values.Add(valCols);
            }

            using (SpreadsheetDocument package =
                SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                package.PackageProperties.Creator = "Julien Chable - Wygwam (www.wygwam.com)";

                var workbookPart = package.AddWorkbookPart();
                GenerateWorkbookPart().Save(workbookPart);

                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
                GenerateWorksheetPart(headers, values).Save(worksheetPart);
            }
        }

        #region Private methods

        private static Workbook GenerateWorkbookPart()
        {
            var element =
                new Workbook(
                    new BookViews(
                        new WorkbookView() { XWindow = 120, YWindow = 150, WindowWidth = (UInt32Value)19095U, WindowHeight = (UInt32Value)8415U }),
                    new Sheets(
                        new Sheet() { Name = "IEnumerableDemo", SheetId = (UInt32Value)1U, Id = "rId1" }));
            return element;
        }

        private static Worksheet GenerateWorksheetPart(List<String> headers, List<List<String>> values)
        {
            var element =
                new Worksheet(
                    new SheetViews(
                        new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U }),
                    new SheetFormatProperties() { DefaultRowHeight = 15D },
                    PopulateData(headers, values));
            return element;
        }

        private static SheetData PopulateData(List<String> headers, List<List<String>> values)
        {
            Row headerRow = new Row();
            SheetData data = new SheetData(headerRow);

            foreach (String h in headers)
                headerRow.Append(new Cell(new CellValue(h)) { DataType = CellValues.String });

            foreach (List<String> vals in values)
            {
                Row valsRow = new Row();
                foreach (String val in vals)
                    valsRow.Append(new Cell(new CellValue(val)) { DataType = CellValues.String });
                data.AppendChild(valsRow);
            }
            return data;
        }

        #endregion
    }

The previous code add each object property with its value convert into a string (with ToString()) and then use the new Open XML SDK v2 to generate a document from scratch.

Here’s the example with a List<Person>:

List<Person> people = new List<Person>();
people.Add(new Person() { Name = "Julien",
                Blog = "
http://blogs.developpeur.org/neodante/" });
people.Add(new Person() { Name = "Aurélien",
                Blog = "
http://blogs.developpeur.org/aurelien/" });
people.ToSpreadsheetML("demo.xlsx");

image

Enjoy (thanks to Aurélien to have Linquify the code a bit)

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Posted: lundi 15 septembre 2008 09:29 par neodante
Classé sous :

Attachment(s): OpenXMLExtensions.zip

Commentaires

Pas de commentaires

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- TFS 2013 : Migration d’une ferme TFS 2010 vers 2013 sans upgrade par Blog Technique de Romelard Fabrice le 05-01-2015, 16:28

- [ #SharePoint 2016 ] frappe à nos portes ! (1/2) par Le blog de Patrick [MVP Office 365] le 04-19-2015, 23:21

- Lync devient Skype Entreprise par Le petit blog de Pierre / Pierre's little blog le 04-18-2015, 22:47

- [WCF] Prendre la main sur les protocoles par Blog de Jérémy Jeanson le 04-18-2015, 12:57

- yOS Tour Geneva - Retour des sessions par Blog Technique de Romelard Fabrice le 04-16-2015, 11:54

- YOS Genève 2015 : gestion des gros fichiers et plus … par The Mit's Blog le 04-13-2015, 11:56

- YOS Genève 2015 : App et bonnes pratiques par The Mit's Blog le 04-13-2015, 10:55

- [YOS Genève 2015] : Et si on adoptait enfin nos espaces collaboratifs par The Mit's Blog le 04-13-2015, 09:48

- [WCF] Les bases d’une configuration clean par Blog de Jérémy Jeanson le 04-11-2015, 11:48

- Dernière partie de cache cache avec l’AppFabric le 2/04/2016 par Blog de Jérémy Jeanson le 04-08-2015, 23:01