Exporting XamDataGrid to Excel the hard way

This is purely an intellectual exercise. The XamDataGrid's Excel export feature is well implemented. But maybe you wondered how you would write it yourself using OpenXML. Given that Microsoft's implementation of OpenXML is so awful, it makes no sense to write it yourself - so here it is!

We want to export the entire hierarchy, with formatted column headings, and also export the nodes' expanded statuses. Like this...

XamDataGrid

Excel Export

We will subclass the XamDataGrid and add the export as a new method. Start a new C# class project called ExportableXamDataGrid.

Before you can use OpenXML you need to use NuGet to fetch the OpenXML package. Open Tools -> NuGet Package Manager -> Manage NugetPackages for Solution. Click Browse and search for DocumentFormat.OpenXml. Select it, select the project, and click [Install]

Rename Class1 to ExportableXamDataGrid. Add references to Infragistics, PresentationCore, and PresentationFramework as shown below.

You're going to need all these usings.

using System;

using System.Collections.Generic;

using Infragistics.Windows.DataPresenter;

using System.Windows;

using System.Collections;

using Infragistics.Windows.DataPresenter.Events;

using DocumentFormat.OpenXml.Packaging;

using System.Linq;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

And inherit from XamDataGrid.

public class ExportableXamDataGrid : XamDataGrid

We need a single new method called Export. We will export to a file called CustomExport.xlsx in the user's temp folder. The sequence in which you create the different parts of the spreadsheet, the style, the columns, and the data is critical, poorly documented, and there are no error messages giving you a clue of what you did wrong. That's why I say Microsoft's implementation is so poor that I get the feeling they don't want OpenXml to be widely adopted.

We will append columns to the columns collection as we need them and adjust the width based on the width of the data. ExportFieldLayout is a recursive routine that gets called to export the children of nodes that have children.

public void Export()

{

    string TempName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), "CustomExport.xlsx");

    SpreadsheetDocument ss = SpreadsheetDocument.Create(TempName, SpreadsheetDocumentType.Workbook);

    WorkbookPart wbp = ss.AddWorkbookPart();

    wbp.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();

    wsp.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

    Sheets sheets = ss.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

    Sheet sheet = new Sheet()

    {

        Id = ss.WorkbookPart.GetIdOfPart(wsp),

        SheetId = 1,

        Name = "Sheet 1"

    };

    sheets.Append(sheet);

    SetStyle(ref ss);

    Columns columns = new Columns();

    wsp.Worksheet.Append(columns);

    wsp.Worksheet.AppendChild(new SheetData());

    ExportFieldLayout(ref wsp, this.ViewableRecords);

  

    wsp.Worksheet.Save();

    wbp.Workbook.Save();

    ss.Close();

}

SetStyle creates a style with a background and bottom border. If you use custom styles, you have to recreate the default styles explicitly. This is an insane amount of work to create a single custom style.

private void SetStyle(ref SpreadsheetDocument ss)

{

    WorkbookStylesPart stylesPart = ss.WorkbookPart.AddNewPart<WorkbookStylesPart>();

    stylesPart.Stylesheet = new Stylesheet();

    // blank font list

    stylesPart.Stylesheet.Fonts = new Fonts();

    stylesPart.Stylesheet.Fonts.Count = 1;

    stylesPart.Stylesheet.Fonts.AppendChild(new Font());

    // create fills

    stylesPart.Stylesheet.Fills = new Fills();

    // create a solid red fill

    PatternFill HeadingFill = new PatternFill() { PatternType = PatternValues.Solid };

    HeadingFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFE0E0E0") };

    HeadingFill.BackgroundColor = new BackgroundColor { Indexed = 64 };

    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel

    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel

    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = HeadingFill });

    stylesPart.Stylesheet.Fills.Count = 3;

    // blank border list

    stylesPart.Stylesheet.Borders = new Borders();

    stylesPart.Stylesheet.Borders.Count = 2;

    stylesPart.Stylesheet.Borders.AppendChild(new Border());

    stylesPart.Stylesheet.Borders.AppendChild(new Border() { BottomBorder = new BottomBorder() { Style = BorderStyleValues.Medium, Color = new Color() { Indexed = 0 } } });

    // blank cell format list

    stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();

    stylesPart.Stylesheet.CellStyleFormats.Count = 1;

    stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());

    // cell format list

    stylesPart.Stylesheet.CellFormats = new CellFormats();

    // empty one for index 0, seems to be required

    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());

    // cell format references style format 0, font 0, border 1, fill 2 and applies the border and fill

    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyBorder = true, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });

    stylesPart.Stylesheet.CellFormats.Count = 2;

    stylesPart.Stylesheet.Save();

}

ExportFieldLayout does the meat of the work. I have two overloads - one with an implied Depth of zero and one with an explicit Depth.

If any of the records in the collection we are exporting is a data record then we need to start the group with headings. We also only want to display the headings and details if the parent record is currently expanded.We iterate through the fields in the first record (they all have the same headings), ignoring any fields that implement IList (because that field is used to populate that record's children). Then we insert the heading into the spreadsheet and set the cell style to our heading style so laboriously created in SetStyle.

Once the headings are exported, we iterate through the data records in our collection and for each one we iterate through the fields, ignoring the IList field(s), and exporting the values in the fields. If a record has children we export them before the next data record.

public void ExportFieldLayout(ref WorksheetPart wsp, ViewableRecordCollection vrc)

{

    ExportFieldLayout(ref wsp, vrc, 0);

}

public void ExportFieldLayout(ref WorksheetPart wsp, ViewableRecordCollection vrc, int Depth)

{

    SheetData sd = wsp.Worksheet.GetFirstChild<SheetData>();

    uint NextRowIndex;

    if (vrc.Any(r => r.IsDataRecord))

    {

        // headings

        NextRowIndex = (uint)wsp.Worksheet.GetFirstChild<SheetData>().Elements<Row>().Count() + 1;

        bool IsParentExpanded = vrc[0].ParentDataRecord == null ? true : vrc[0].ParentDataRecord.IsExpanded;

        foreach (Infragistics.Windows.DataPresenter.Field f in vrc[0].FieldLayout.Fields)

        {

            if (f.DataType.GetInterface("IList") == null)

            {

                DocumentFormat.OpenXml.Spreadsheet.Cell cell;

                cell = InsertCellInWorksheet(vrc[0].FieldLayout.Fields.IndexOf(f), NextRowIndex, f.Label.ToString(), wsp, Depth, false, IsParentExpanded);

                cell.StyleIndex = 1;

            }

        }

    }

    foreach (Record r in vrc)

    {

        if (r is DataRecord)

        {

            DataRecord dr = (r as DataRecord);

            bool IsExpanded = r.ParentDataRecord == null ? true : r.ParentDataRecord.IsExpanded;

            NextRowIndex = (uint)wsp.Worksheet.GetFirstChild<SheetData>().Elements<Row>().Count() + 1;

            foreach (Infragistics.Windows.DataPresenter.Field f in dr.FieldLayout.Fields)

            {

                if (f.DataType.GetInterface("IList") == null)

                {

                    InsertCellInWorksheet(vrc[0].FieldLayout.Fields.IndexOf(f), NextRowIndex, dr.Cells[f.Name].Value.ToString(), wsp, Depth, r.HasChildren, IsExpanded);

                }

            }

        }

        if (r.HasChildren) ExportFieldLayout(ref wsp, r.ViewableChildRecords, Depth + 1);

    }

}

The code to export a cell is surprisingly complex. It has to create a new row, if needed, create a new cell, if needed, and finally populate the cell. We adjust the width of the columns by looking at the data exported to them and adding a little bonus width.

private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(int ColumnIndex, uint rowIndex, String Value, WorksheetPart wsp, int Depth, bool HasChildren, bool IsVisible)

{

    DocumentFormat.OpenXml.Spreadsheet.Worksheet ws = wsp.Worksheet;

    SheetData sd = ws.GetFirstChild<SheetData>();

    int DisplayColumnIndex = ColumnIndex + Depth + 1;

    string ColumnName = GetColumnName(DisplayColumnIndex);

    string cr = ColumnName + rowIndex;

    Columns columns = ws.Elements<Columns>().FirstOrDefault();

    Row row;

    const int WidthFactor = 1;

    const int WidthBonus = 10;

    if (sd.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() > 0)

        row = sd.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

    else

    {

        row = new Row() { RowIndex = rowIndex, DyDescent = 0.25D, Collapsed = HasChildren, OutlineLevel = (Byte)Depth, Hidden = !IsVisible };

        sd.Append(row);

    }

    if (columns != null)

    {

        List<Column> MatchingColumns;

        int ColumnWidth = Value.Length * WidthFactor + WidthBonus;

        MatchingColumns = columns.ChildElements.Cast<Column>().Where(c => c.Min == DisplayColumnIndex).ToList();

        if (MatchingColumns.Count > 0)

        {

            if (MatchingColumns[0].Width < ColumnWidth) MatchingColumns[0].Width = ColumnWidth;

        }

        else

            columns.Append(new Column { Min = (uint)DisplayColumnIndex, Max = (uint)DisplayColumnIndex, Width = ColumnWidth, CustomWidth = true });

    }

   if (row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == ColumnName + rowIndex).Count() > 0)

        return row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == ColumnName + rowIndex).First();

    DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;

    foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())

    {

        if (cell.CellReference.Value.Length == cr.Length)

        {

            if (string.Compare(cell.CellReference.Value, cr, true) > 0)

            {

                refCell = cell;

                break;

            }

        }

    }

    DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cr, DataType = CellValues.String, CellValue = new CellValue(Value) };

    row.InsertBefore(newCell, refCell);

    ws.Save();

    return newCell;

}

The last thing we need is a function to convert a column index to a column name. This one only works up to 26 columns but it could be enhanced to be a simple 10 -> 26 base-conversion function.

private static String GetColumnName(int Index)

{   // Works for first 26 anyway

    return ((char)(64 + Index)).ToString();

}

At this point we can get a clean build. Now we add a WPF window to demonstrate the export. Add a WPF App to the solution called ExportableXamDataGridTest.

Add references to Infragistics and the ExportableXamDataGrid and set it as the startup project.

The XAML defines a hierarchical ExportableXamDataGrid and a single button.

<Window x:Class="ExportableXamDataGridTest.MainWindow"

        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

        xmlns:custom="clr-namespace:ExportableXamDataGrid;assembly=ExportableXamDataGrid"

        xmlns:igDP="http://infragistics.com/DataPresenter"

        xmlns:local="clr-namespace:ExportableXamDataGridTest"

        mc:Ignorable="d"

        DataContext="{Binding RelativeSource={RelativeSource Self}}"

        Title="MainWindow" Height="450" Width="800">

    <Window.Resources>

        <RoutedCommand x:Key="ExportCustom"/>

    </Window.Resources>

    <Window.CommandBindings>

        <CommandBinding Command="{StaticResource ExportCustom}" Executed="Export_Executed"/>

    </Window.CommandBindings>

    <Grid>

        <Grid.RowDefinitions>

            <RowDefinition Height="*"/>

            <RowDefinition Height="auto"/>

        </Grid.RowDefinitions>

        <custom:ExportableXamDataGrid  Grid.Row="0" DataSource="{Binding SearchResults}" ActiveDataItem="{Binding SelectedItem}">

            <igDP:XamDataGrid.FieldLayoutSettings>

                <igDP:FieldLayoutSettings SelectionTypeRecord="Single" AutoGenerateFields="False" ExpansionIndicatorDisplayMode="CheckOnDisplay"/>

            </igDP:XamDataGrid.FieldLayoutSettings>

            <igDP:XamDataGrid.FieldSettings>

                <igDP:FieldSettings AllowSummaries="False" AllowEdit="False"/>

            </igDP:XamDataGrid.FieldSettings>

            <igDP:XamDataGrid.FieldLayouts>

                <igDP:FieldLayout Description="Pallet" Key="Pallet">

                    <igDP:FieldLayout.Fields>

                        <igDP:TextField Label="ID" Name="ID"/>

                        <igDP:TextField Label="Pallet Name" Name="Name"/>

                        <igDP:Field Label="" Name="Boxes"/>

                    </igDP:FieldLayout.Fields>

                </igDP:FieldLayout>

                <igDP:FieldLayout ParentFieldLayoutKey="Pallet" ParentFieldName="Boxes" Key="Box">

                    <igDP:FieldLayout.Fields>

                        <igDP:TextField Label="ID" Name="ID"/>

                        <igDP:TextField Label="Box Name" Name="Name"/>

                        <igDP:Field Label="" Name="Items"/>

                    </igDP:FieldLayout.Fields>

                </igDP:FieldLayout>

                <igDP:FieldLayout ParentFieldLayoutKey="Box" ParentFieldName="Items" Key="Item">

                    <igDP:FieldLayout.Fields>

                        <igDP:TextField Label="ID" Name="ID"/>

                        <igDP:TextField Label="Item Name" Name="Name"/>

                    </igDP:FieldLayout.Fields>

                </igDP:FieldLayout>

            </igDP:XamDataGrid.FieldLayouts>

        </custom:ExportableXamDataGrid>

        <StackPanel Grid.Row="1" Orientation="Horizontal">

            <Button Content="Export Custom" Command="{StaticResource ExportCustom}"/>

        </StackPanel>

    </Grid>

</Window>

The code-behind creates the required properties and populates them. When the [Export] button is pressed it calls the ExportableXamDataGrid's export method which creates a CustomExport.xlsx file in your temp folder.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Windows;

using System.Windows.Input;

using System.Linq;

using System.Diagnostics;

namespace ExportableXamDataGridTest

{

    public partial class MainWindow : Window, INotifyPropertyChanged

    {

        private ExportableXamDataGrid.ExportableXamDataGrid xdg = null;

        public event PropertyChangedEventHandler PropertyChanged;

        private void NotifyPropertyChanged(String PropertyName = "")

        {

            if (PropertyChanged != null)

            {

                PropertyChanged(this, new PropertyChangedEventArgs(PropertyName));

            }

        }

        public class cPallet

        {

            public string ID { get; set; }

            public string Name { get; set; }

            public List<cBox> Boxes { get; set; }

        }

        public class cBox

        {

            public string ID { get; set; }

            public string Name { get; set; }

            public List<cItem> Items { get; set; }

        }

        public class cItem

        {

            public string ID { get; set; }

            public string Name { get; set; }

        }

        private List<cPallet> _SearchResults = new List<cPallet>();

        public List<cPallet> SearchResults

        {

            get { return _SearchResults; }

            set

            {

                _SearchResults = value;

                NotifyPropertyChanged("SearchResults");

            }

        }

        public String SelectedItem { get; set; }

        private List<cPallet> Inventory;

        public MainWindow()

        {

            InitializeDataGrid();

            InitializeComponent();

        }

        public void InitializeDataGrid()

        {

            SearchResults = new List<cPallet>

            {

                new cPallet() {ID="1", Name="Pallet from UPS", Boxes = new List<cBox>()

                {

                    new cBox() {ID="1A", Name="Box A on Pallet 1", Items= new List<cItem>() {new cItem() {ID="1Aa", Name="Item a in Box A" }, new cItem() {ID="1Ab", Name="Item b in Box A"} } }

                ,

                    new cBox() {ID="1B", Name="Box B on Pallet 1", Items= new List<cItem>() {new cItem() {ID="1Ba", Name="Item a in Box B" }, new cItem() {ID="1Bb", Name="Item b in Box B"} } }

                }

            }

            ,

                new cPallet() {ID="2", Name="Pallet from FedEx", Boxes = new List<cBox>()

                {

                    new cBox() {ID="2A", Name="Box A on Pallet 2", Items= new List<cItem>() {new cItem() {ID="2Aa", Name="Item a in Box A" }, new cItem() {ID="2Aa", Name="Item b in Box A"} } }

                ,

                    new cBox() {ID="2B", Name="Box B on Pallet 2", Items= new List<cItem>() {new cItem() {ID="2Ba", Name="Item a in Box B" }, new cItem() {ID="2Bb", Name="Item b in Box B"} } }

                }

            }

            ,

                new cPallet() {ID="3", Name="Pallet from USPS", Boxes = new List<cBox>()

                {

                    new cBox() {ID="3A", Name="Box A on Pallet 3", Items= new List<cItem>() {new cItem() {ID="3Aa", Name="Item a in Box A" }, new cItem() {ID="3Aa", Name="Item b in Box A"} } }

                ,

                    new cBox() {ID="3B", Name="Box B on Pallet 3", Items= new List<cItem>() {new cItem() {ID="3Ba", Name="Item a in Box B" }, new cItem() {ID="3Bb", Name="Item b in Box B"} } }

                }

            }};

        }

        private void Export_Executed(object sender, ExecutedRoutedEventArgs e)

        {

            xdg.Export();

        }

        private void XamDataGrid_Loaded(object sender, RoutedEventArgs e)

        {

            xdg = (sender as ExportableXamDataGrid.ExportableXamDataGrid);

        }

    }

}

Note: If you make a mistake in the structure of the export, Excel will allow you to try to recover the file and will generate an error file. 

Clicking on the link displays this worthless XML. I get the feeling Microsoft has tried very hard to make it look like they tried very hard, but tried even harder to make sure you fail.

One trick I've found is to put a breakpoint at ss.Save and look at wbp.Workbook.Outerxml and wsp.Worksheet.Outerxml using an xml visualizer. These can be compared to the expected schemas on pages such as https://docs.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet

Take a look at the worksheet XML below. You can see I have an empty columns collection. That's what is causing the problem above. I find it hard to believe Microsoft couldn't have mentioned that somehow.

Just for fun, let's compare our export to Infragistics' native export. In MainWindow.xaml add the following commands and buttons.

<RoutedCommand x:Key="ExportNative"/>

<CommandBinding Command="{StaticResource ExportNative}" Executed="ExportNative_Executed"/>

<Button Content="Export Native" Command="{StaticResource ExportNative}"/>

Add the following references to the ExportableXamDataGridTest project.

Add these usings to MainWindow.xaml.vb

using Infragistics.Windows.DataPresenter.ExcelExporter;

using Infragistics.Documents.Excel;

And add the new event handler in the code-behind.

private void ExportNative_Executed(object sender, ExecutedRoutedEventArgs e)

{

    string TempName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), "ExportNative.xlsx");

    DataPresenterExcelExporter exporter = new DataPresenterExcelExporter();

    exporter.Export(xdg, TempName, WorkbookFormat.Excel2007);

}

This exports the same grid to this excel spreadsheet.

If you add a few hundred more top level items and perform some timings you will see the Infragistics export is about 10 times faster than our custom export.