MudDataGrid and RadzenDataGrid Export to CSV and Excel

DataGridExporter.cs

/*
    Possibility to export a MudDataGrid or a RadzenDataGrid to CSV or Excel. 
    In my project both DataGrid Types are used, but also only one of it could be used by removing the other.
    
    Usage MudDataGrid:        var data = Exporter.GetTableData(grid.RenderedColumns, grid.FilteredItems);
    
    Usage RadzenDataGrid:     var data = Exporter.GetTableData(grid.ColumnsCollection, grid.View);
        
    Followed by:              var fileContentAsByteArray = Exporter.GenerateCSV(data); // or Exporter.GenerateExcel(data);

*/

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using MudBlazor;
using Radzen.Blazor;
using System.Globalization;
using System.Reflection;
using System.Text;
using Color = DocumentFormat.OpenXml.Spreadsheet.Color;

namespace MyApp.Classes.Tools
{
    public class DataGridExporter
    {
        public TableData GetTableData<T>(IEnumerable<Column<T>> columns, IEnumerable<T> items)
        {
            var tableData = new TableData { SheetName = "Items" };
            var header = new List<Cell>();
            foreach (var column in columns)
            {
                if (!column.Hidden && !string.IsNullOrEmpty(column.PropertyName))
                    header.Add(new Cell() { CellValue = new CellValue(column.Title), DataType = CellValues.String });
            }
            tableData.Cells.Add(header);

            var type = items.FirstOrDefault()?.GetType();
            foreach (var item in items)
            {
                if (item == null || type == null) continue;
                List<Cell> row = [];
                foreach (var column in columns)
                {
                    if (!column.Hidden && !string.IsNullOrEmpty(column.PropertyName))
                        row.Add(GetCell(item, type.GetProperty(column.PropertyName)));
                }
                tableData.Cells.Add(row);
            }
            return tableData;
        }

        public TableData GetTableData<T>(IEnumerable<RadzenDataGridColumn<T>> columns, IEnumerable<T> items)
        {
            var tableData = new TableData { SheetName = "Items" };
            var header = new List<Cell>();
            foreach (var column in columns)
            {
                if (column.GetVisible() && !string.IsNullOrEmpty(column.Property))
                    header.Add(new Cell() { CellValue = new CellValue(column.Title), DataType = CellValues.String });
            }
            tableData.Cells.Add(header);

            var type = items.FirstOrDefault()?.GetType();
            foreach (var item in items)
            {
                if (item == null || type == null) continue;
                List<Cell> row = [];
                foreach (var column in columns)
                {
                    if (column.GetVisible() && !string.IsNullOrEmpty(column.Property))
                        row.Add(GetCell(item, type.GetProperty(column.Property)));
                }
                tableData.Cells.Add(row);
            }
            return tableData;
        }

        private static Cell GetCell(object? item, PropertyInfo? prop)
        {
            var cell = new Cell();
            if (item == null || prop == null) return cell;
            var value = prop.GetValue(item);
            var valueType = prop.PropertyType;
            var stringValue = value?.ToString()?.Trim() ?? "";

            var underlyingType = valueType.IsGenericType && valueType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(valueType) : valueType;

            var typeCode = Type.GetTypeCode(underlyingType);

            if (typeCode == TypeCode.DateTime)
            {
                if (!string.IsNullOrWhiteSpace(stringValue) && value != null)
                {
                    cell.CellValue = new CellValue() { Text = ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture) };
                    cell.DataType = CellValues.Number;
                    cell.StyleIndex = (UInt32Value)1U;
                }
            }
            else if (typeCode == TypeCode.Boolean)
            {
                cell.CellValue = new CellValue(stringValue.ToLower());
                cell.DataType = CellValues.Boolean;
            }
            else if (IsNumeric(typeCode) && underlyingType?.IsEnum != true)
            {
                if (value is double doubleValue)
                    stringValue = Math.Round(doubleValue, 2).ToString().Replace(',', '.');
                else if (value != null)
                    stringValue = Convert.ToString(value, CultureInfo.InvariantCulture);

                cell.CellValue = new CellValue(stringValue ?? "");
                cell.DataType = CellValues.Number;
            }
            else
            {
                cell.CellValue = new CellValue(stringValue);
                cell.DataType = CellValues.String;
            }

            return cell;
        }

        public byte[] GenerateCSV(TableData data)
        {
            var sb = new StringBuilder();
            foreach (var rowData in data.Cells)
            {
                var row = new List<string>();
                foreach (var cell in rowData)
                {
                    var value = cell.CellValue?.Text?.Trim() ?? "";
                    if (cell.StyleIndex?.Value == 1 && cell.CellValue?.TryGetDouble(out var number) == true)
                    {
                        var date1 = DateTime.FromOADate(number);
                        value = date1.ToString("dd.MM.yyyy HH:mm");
                    }
                    if (cell.DataType != null && cell.DataType == CellValues.Date && cell.CellValue?.TryGetDateTime(out var date) == true)
                    {
                        value = date.ToString("dd.MM.yyyy HH:mm");
                    }
                    if (value.Contains(',')) value = $"\"{value}\"";
                    row.Add(value);
                }
                sb.AppendLine(string.Join(",", row.ToArray()));
            }
            return Encoding.UTF8.GetBytes(sb.ToString());
        }

        public byte[] GenerateExcel(TableData data)
        {
            using var stream = new MemoryStream();
            using var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            {
                var workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                GenerateWorkbookStylesPartContent(workbookStylesPart);
                if (document.WorkbookPart == null) return stream.ToArray();
                var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

                var sheet = new Sheet()
                {
                    Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = data.SheetName ?? "Sheet 1"
                };
                sheets.AppendChild(sheet);

                AppendDataToSheet(sheetData, data);

                workbookPart.Workbook.Save();
                document.Dispose();
            }
            return stream.ToArray();
        }

        private static void AppendDataToSheet(SheetData sheetData, TableData data)
        {
            foreach (var rowData in data.Cells)
            {
                var row = new DocumentFormat.OpenXml.Spreadsheet.Row();
                sheetData.AppendChild(row);
                foreach (var cell in rowData)
                {
                    row.AppendChild(cell);
                }
            }
        }

        private static bool IsNumeric(TypeCode typeCode)
        {
            return typeCode switch
            {
                TypeCode.Decimal or TypeCode.Double or TypeCode.Int16 or TypeCode.Int32 or TypeCode.Int64 or TypeCode.UInt16 or TypeCode.UInt32 or TypeCode.UInt64 => true,
                _ => false,
            };
        }

        private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
        {
            var stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac x16r2 xr" } };
            stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
            stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");

            var fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };

            var font1 = new Font();
            var fontSize1 = new FontSize() { Val = 11D };
            var color1 = new Color() { Theme = (UInt32Value)1U };
            var fontName1 = new FontName() { Val = "Calibri" };
            var fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
            var fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };

            font1.Append(fontSize1);
            font1.Append(color1);
            font1.Append(fontName1);
            font1.Append(fontFamilyNumbering1);
            font1.Append(fontScheme1);
            fonts1.Append(font1);

            var fills1 = new Fills() { Count = (UInt32Value)2U };

            var fill1 = new Fill();
            var patternFill1 = new PatternFill() { PatternType = PatternValues.None };

            fill1.Append(patternFill1);

            var fill2 = new Fill();
            var patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

            fill2.Append(patternFill2);

            fills1.Append(fill1);
            fills1.Append(fill2);

            var borders1 = new Borders() { Count = (UInt32Value)1U };

            var border1 = new Border();
            var leftBorder1 = new LeftBorder();
            var rightBorder1 = new RightBorder();
            var topBorder1 = new TopBorder();
            var bottomBorder1 = new BottomBorder();
            var diagonalBorder1 = new DiagonalBorder();

            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);

            borders1.Append(border1);

            var cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
            var cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

            cellStyleFormats1.Append(cellFormat1);

            var cellFormats1 = new CellFormats() { Count = (UInt32Value)2U };
            var cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
            var cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };

            cellFormats1.Append(cellFormat2);
            cellFormats1.Append(cellFormat3);

            var cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
            var cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

            cellStyles1.Append(cellStyle1);
            var differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
            var tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };

            var stylesheetExtensionList1 = new StylesheetExtensionList();

            var stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
            stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");

            var stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
            stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            OpenXmlUnknownElement openXmlUnknownElement4 = workbookStylesPart1.CreateUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");

            stylesheetExtension2.Append(openXmlUnknownElement4);

            stylesheetExtensionList1.Append(stylesheetExtension1);
            stylesheetExtensionList1.Append(stylesheetExtension2);

            stylesheet1.Append(fonts1);
            stylesheet1.Append(fills1);
            stylesheet1.Append(borders1);
            stylesheet1.Append(cellStyleFormats1);
            stylesheet1.Append(cellFormats1);
            stylesheet1.Append(cellStyles1);
            stylesheet1.Append(differentialFormats1);
            stylesheet1.Append(tableStyles1);
            stylesheet1.Append(stylesheetExtensionList1);

            workbookStylesPart1.Stylesheet = stylesheet1;
        }
    }

    public class TableData
    {
        public List<List<Cell>> Cells { get; set; } = [];
        public string SheetName { get; set; } = "";
    }
}

Hi @Apflkuacha : As a relative beginner, I find implementing this solution challenging. Any guidance as to how to implement?

My project is organized as: web assembly API, shared lib and client. I understand the call would begin at the client razor page, where I call:

1. var data = Exporter.GetTableData(grid.RenderedColumns, grid.FilteredItems);
2. var fileContentAsByteArray = Exporter.GenerateExcel(data);

The "grid." in the parameters of step1 I presume to be the name of my MudDataGrid, so I assume I'd change that to be (MyMudDataGrid.RenderedColumns, MyMudDataGrid.FilteredItems.

But where should the DataExporter and TableData classes exist? I assume I'd need to organize them somewhere, and inject the service that implements them into the razor page...but this has not worked as I hoped.

Apologies, relatively new to component libraries, appreciate any guidance.

Hello @Advaita-X
I am in a perfect mood today and took some time to make an example project, it could be found here: https://github.com/Apflkuacha/MudBlazorExampleExportTable

Somehow the Click Event doesn't occure in that example project I took but you could see how it is done to get the data from the MudDataGrid.

HI @Apflkuacha ,

My sincerest gratitude for your taking the time to provide an example project - answered many of my questions. I was able to go through it step by step and have integrated the structure into my project.

One follow-up question regarding the button click event 'not working'. When debugging, I see that the button is triggering the event and the two following lines of code are executed:

var data = Exporter.GetTableData(grid.RenderedColumns, grid.FilteredItems);
var fileContentAsByteArray = Exporter.GenerateCSV(data); // or Exporter.GenerateExcel(data);

But that is where it stops, I see that the "data" variable has the data, and subsequently fileContentAsByteArray is populated. But there is no download/save triggered in the browser. Is there another line of code that I need to add to turn fileContentAsByteArray into an excel spreadsheet that is downloaded/opened?

Godd that it worked, then VisualStudio had some problem that for me the breakpoint wasn't hit. Well i couldn't help you on all problems and teach you how to code - that takes a lot of time. But you could simply save it on the Computer where the application is running with File.WriteAllBytes(path, fileContentAsByteArray) or more complex, open the file in a new browser tab, my method as example here:

public static async Task OpenFileInNewTab(NavigationManager NavigationManager, IJSRuntime JSRuntime, string filename, byte[] data)
{
    var path = Path.Combine("Temp", DateTime.Now.ToString("MMdd.HHmmss"), filename);
    var filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", path);
    Directory.CreateDirectory(Path.GetDirectoryName(filePath) ?? "");
    await File.WriteAllBytesAsync(filePath, data);
    var fileUrl = NavigationManager.ToAbsoluteUri(path).ToString();
    try { await JSRuntime.InvokeVoidAsync("window.open", fileUrl, "_blank"); } catch { }
}

But before asking where to get the NavigationManager or IJSRuntime: search the web or any KI for help, example: https://duckduckgo.com/?q=DuckDuckGo&ia=chat

Hello Apflkaucha,
Thank you for the follow up. After nearly 10 hours of working through this, it finally works. Without the solutions you provided, I'd still be lost - thank you for taking the time to respond. Best feeling ever seeing it finally work.

Hello Apflkaucha,

This is incredible work! I am very new to Blazor and C# and I took a look at the MudBlazor Library today. Exporting to Excel, is absolutely critical for me, and without the feature you created I would discount MudBlazor immediately.

It took a while to cleanup your code because it wasn't working and there were no errors provided from Visual Studio. There was a disparity between the two Elements you created and the Elements model. When I cleaned that up it worked.

If you have time, I recommend you clean up the code you provided and advertise it more. This was the only working code I found after searching for a while for Exporting MudBlazor DataGrid. As mentioned above without this feature I would have to look at other Libraries.

Thank you Apflkaucha.

@Axium7 Do you have a cleaned up version of the code that works in a GitHub repo? If so, please can you share the link.

Thanks.

Hi AJ,

I'll share the code sometime this week. It's Christmas where I am.

Happy Holidays!

Thanks, would be interested in seeing It working. It is Christmas where I am as well. Happy holidays.

@AJ1000 See the below link.

ExcelCSVExport

Thank you very much for doing this, it works great. You should advertise this, as I am sure other people are trying to make this functionality work.

I informed one member of the MudBlazor team about this. I think it should be part of the MudDataGrid.

Well I didn't had time the last months, but as I had a look now you haven't changed the code, it was more or less only the RadzenDataGrid export removed. That is used by me as we also use that DataGrid next to the one from MudBlazor in our project.
But advertising it at the MudBlazor team is a good idea, but there were already discussions about implementing exporting of it the years before..

Thanks for your efforts as well Apfkuacha