Hello,
.NET(6.0.18)
Radzen.Blazor(4.10.4)
DocumentFormat.OpenXml(2.20.0)
I'm trying to Export to Excel some data from DataGrid that was created dynamically. Taking Blazor DataGrid supports dynamic data sources and Blazor DataGrid export to Excel and CSV as an examples I have the following code:
@page "/test"
@using System.Linq.Dynamic.Core
@using DocumentFormat.OpenXml
@using DocumentFormat.OpenXml.Packaging
@using DocumentFormat.OpenXml.Spreadsheet
@using System.Reflection
@inject IJSRuntime JS
<RadzenButton Icon="grid_on" style="margin-bottom: 10px" Text="Export To Excel" class="mb-4 me-2" Click="DownloadFileFromStream">
</RadzenButton>
<RadzenDataGrid @bind-Value=@selectedItems Data="@data" TItem="IDictionary<string, object>" ColumnWidth="200px"
AllowFiltering="true" FilterPopupRenderMode="PopupRenderMode.OnDemand" FilterMode="FilterMode.Advanced" AllowPaging="true" AllowSorting="true">
<Columns>
@foreach (var column in columns)
{
<RadzenDataGridColumn TItem="IDictionary<string, object>" Title="@column.Key" Type="column.Value"
Property="@GetColumnPropertyExpression(column.Key, column.Value)">
<Template>
@context[@column.Key]
</Template>
</RadzenDataGridColumn>
}
</Columns>
</RadzenDataGrid>
@code {
IList<IDictionary<string, object>> selectedItems;
public IEnumerable<IDictionary<string, object>> data { get; set; }
public IDictionary<string, Type> columns { get; set; }
public string GetColumnPropertyExpression(string name, Type type)
{
var expression = $@"it[""{name}""].ToString()";
if (type == typeof(int))
{
return $"int.Parse({expression})";
}
else if (type == typeof(DateTime))
{
return $"DateTime.Parse({expression})";
}
return expression;
}
protected override async Task OnInitializedAsync()
{
await base.OnInitializedAsync();
columns = new Dictionary<string, Type>()
{
{ "EmployeeID", typeof(int) },
{ "FirstName", typeof(string) },
{ "LastName", typeof(string) },
{ "HireDate", typeof(DateTime) },
};
foreach (var i in Enumerable.Range(0, 50))
{
columns.Add($"Column{i}", typeof(string));
}
data = Enumerable.Range(0, 100).Select(i =>
{
var row = new Dictionary<string, object>();
foreach (var column in columns)
{
row.Add(column.Key, column.Value == typeof(int) ? i :
column.Value == typeof(DateTime) ? DateTime.Now.AddMonths(i) : $"{column.Key}{i}");
}
return row;
});
}
private async Task DownloadFileFromStream()
{
var fileStream = ToExcel(data.AsQueryable());
var fileName = "Export.xlsx";
using var streamRef = new DotNetStreamReference(stream: fileStream);
await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);
}
public Stream ToExcel(IQueryable query)
{
var columns = GetProperties(query.ElementType);
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>();
worksheetPart.Worksheet = new Worksheet();
var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
GenerateWorkbookStylesPartContent(workbookStylesPart);
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
sheets.Append(sheet);
workbookPart.Workbook.Save();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
foreach (var column in columns)
{
headerRow.Append(new Cell()
{
CellValue = new CellValue(column.Key),
DataType = new EnumValue<CellValues>(CellValues.String)
});
}
sheetData.AppendChild(headerRow);
foreach (var item in query)
{
var row = new Row();
foreach (var column in columns)
{
var value = GetValue(item, column.Key);
var stringValue = $"{value}".Trim();
var cell = new Cell();
var underlyingType = column.Value.IsGenericType &&
column.Value.GetGenericTypeDefinition() == typeof(Nullable<>) ?
Nullable.GetUnderlyingType(column.Value) : column.Value;
var typeCode = Type.GetTypeCode(underlyingType);
if (typeCode == TypeCode.DateTime)
{
if (stringValue != string.Empty)
{
cell.CellValue = new CellValue() { Text = DateTime.Parse(stringValue).ToOADate().ToString() };
cell.StyleIndex = (UInt32Value)1U;
}
}
else if (typeCode == TypeCode.Boolean)
{
cell.CellValue = new CellValue(stringValue.ToLower());
cell.DataType = new EnumValue<CellValues>(CellValues.Boolean);
}
else if (IsNumeric(typeCode))
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue(stringValue);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
}
row.Append(cell);
}
sheetData.AppendChild(row);
}
workbookPart.Workbook.Save();
}
if (stream?.Length > 0)
{
stream.Seek(0, SeekOrigin.Begin);
}
//var result = new Microsoft.AspNetCore.Mvc.FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//result.FileDownloadName = "Export.xlsx";
return stream;
}
public static object GetValue(object target, string name)
{
return target.GetType().GetProperty(name).GetValue(target);
}
public static IEnumerable<KeyValuePair<string, Type>> GetProperties(Type type)
{
return type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p => p.CanRead && IsSimpleType(p.PropertyType)).Select(p => new KeyValuePair<string, Type>(p.Name, p.PropertyType));
}
public static bool IsSimpleType(Type type)
{
var underlyingType = type.IsGenericType &&
type.GetGenericTypeDefinition() == typeof(Nullable<>) ?
Nullable.GetUnderlyingType(type) : type;
var typeCode = Type.GetTypeCode(underlyingType);
switch (typeCode)
{
case TypeCode.Boolean:
case TypeCode.Byte:
case TypeCode.Char:
case TypeCode.DateTime:
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.SByte:
case TypeCode.Single:
case TypeCode.String:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
return true;
default:
return false;
}
}
private static bool IsNumeric(TypeCode typeCode)
{
switch (typeCode)
{
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
return true;
default:
return false;
}
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet 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");
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders() { Count = (UInt32Value)1U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)2U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat 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);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension 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;
}
}
Additionally I have javascript added to _Layout.cshtml body section for downloadFileFromStream script which is taken from ASP.NET Core Blazor file downloads | Microsoft Learn :
<script>
window.downloadFileFromStream = async (fileName, contentStreamReference) => {
const arrayBuffer = await contentStreamReference.arrayBuffer();
const blob = new Blob([arrayBuffer]);
const url = URL.createObjectURL(blob);
const anchorElement = document.createElement('a');
anchorElement.href = url;
anchorElement.download = fileName ?? '';
anchorElement.click();
anchorElement.remove();
URL.revokeObjectURL(url);
}
</script>
This results in an empty .xlsx file. Is there a way to Export to Excel dynamically created DataGrid? Looks like :
public IEnumerable<IDictionary<string, object>> data { get; set; }
is is being the issue here instead of :
public IEnumerable<SomeSpecificallyTypedObject> data { get; set; }