Problem exporting multiple SfDataGrids in the same Worksheet

Question

Hi, I am having some problems when I export many sfDataGrids to a unique worksheet, previously in UWP, I used SfDataGrid. ExportToExcel, but this is not possible in Xamarin, they are used DataGridExcelExportingController instead, and I dont having luck in this, when I export to excel the sfDataGrids are having the itemsSource correctly (count) but the exported excel file is empty. Like the image:

To replicate the scenario just use: Export_To_MSExcel.ExportStaticListsToExcel();

My Code:

public class Export_To_MSExcel
    {
        public class GameConsole
        {
            public string Name { get; set; }

            public string CreatedBy { get; set; }
        }
        public class Manufacturer
        {
            public string Name { get; set; }

            public int FoundationYear { get; set; }
        }
        public class Values_GameConsole : ObservableCollection { }
        public class Values_Manufacturer : ObservableCollection { }
        public static class StaticCollections
        {
            public static Values_GameConsole GameConsoles = new Values_GameConsole();
            public static Values_Manufacturer Manufacturers = new Values_Manufacturer();
            static StaticCollections()
            {
            }
        }
        public static async void ExportStaticListsToExcel()
        {
            FillData();
            var WorkBook = WorkbookCreator();
            MemoryStream stream = new MemoryStream();
            WorkBook.SaveAs(stream);
            WorkBook.Close();
            await DependencyService.Get().SaveFile(stream);
        }
        private static void FillData()
        {
            StaticCollections.GameConsoles.Add(new GameConsole()
            {
                Name = "Xbox",
                CreatedBy = "Creator 1",
            });
            StaticCollections.GameConsoles.Add(new GameConsole()
            {
                Name = "SNES",
                CreatedBy = "Creator 2",
            });
            StaticCollections.GameConsoles.Add(new GameConsole()
            {
                Name = "NES",
                CreatedBy = "Creator 3",
            });

            StaticCollections.Manufacturers.Add(new Manufacturer()
            {
                Name = "Sony",
                FoundationYear = 1000,
            });
            StaticCollections.Manufacturers.Add(new Manufacturer()
            {
                Name = "Nintendo",
                FoundationYear = 1000,
            });
            StaticCollections.Manufacturers.Add(new Manufacturer()
            {
                Name = "Microsoft",
                FoundationYear = 1000,
            });
        }
        private static IWorkbook WorkbookCreator()
        {
            ExcelEngine excelEngine = new ExcelEngine();
            IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
            workbook.Version = ExcelVersion.Excel2016;
            SfDataGrid GameConsoles_SfDataGrid = new SfDataGrid() { AutoGenerateColumns = false, };
            SfDataGrid Manufacturers_SfDataGrid = new SfDataGrid() { AutoGenerateColumns = false, };
            GameConsoles_SfDataGrid.Columns.Add(new GridTextColumn() { HeaderText = string.Empty, MappingName = "Name" });
            GameConsoles_SfDataGrid.Columns.Add(new GridTextColumn() { HeaderText = string.Empty, MappingName = "CreatedBy" });
            Manufacturers_SfDataGrid.Columns.Add(new GridTextColumn() { HeaderText = string.Empty, MappingName = "Name" });
            Manufacturers_SfDataGrid.Columns.Add(new GridTextColumn() { HeaderText = string.Empty, MappingName = "FoundationYear" });
            GridQueryableCollectionViewWrapper GameConsoles_Wrapper = new GridQueryableCollectionViewWrapper(StaticCollections.GameConsoles, GameConsoles_SfDataGrid);
            GridQueryableCollectionViewWrapper Manufacturers_Wrapper = new GridQueryableCollectionViewWrapper(StaticCollections.Manufacturers, Manufacturers_SfDataGrid);
            GameConsoles_SfDataGrid.ItemsSource = GameConsoles_Wrapper;
            Manufacturers_SfDataGrid.ItemsSource = Manufacturers_Wrapper;
            DataGridExcelExportingController GameConsoles_Controller = new DataGridExcelExportingController();
            DataGridExcelExportingController Manufacturers_Controller = new DataGridExcelExportingController();
            GameConsoles_Controller.CellExporting += GameConsoles_Controller_CellExporting;
            Manufacturers_Controller.CellExporting += Manufacturers_Controller_CellExporting;
            DataGridExcelExportingOption GameConsoles_Options = new DataGridExcelExportingOption
            {
                StartColumnIndex = 1,
                StartRowIndex = 1,
            };
            DataGridExcelExportingOption Manufacturers_Options = new DataGridExcelExportingOption
            {
                StartColumnIndex = 1,
                StartRowIndex = 1 + 2 + StaticCollections.GameConsoles.Count,
            };
            GameConsoles_Options.ApplyGridStyle = true;
            Manufacturers_Options.ApplyGridStyle = true;
            GameConsoles_Controller.ExportToExcel(GameConsoles_SfDataGrid, GameConsoles_Wrapper, GameConsoles_Options, workbook.ActiveSheet);
            Manufacturers_Controller.ExportToExcel(Manufacturers_SfDataGrid, Manufacturers_Wrapper, Manufacturers_Options, workbook.ActiveSheet);
            return workbook;
        }
        private static void Manufacturers_Controller_CellExporting(object sender, DataGridCellExcelExportingEventArgs e)
        {
            if (e.CellType == ExportCellType.RecordCell)
            {
                if (e.Range.Column == 1)
                {
                    e.Range.CellStyle.Font.Size = 12;
                    e.Range.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
                    e.Range.CellStyle.Font.Bold = true;
                }
                if (e.Range.Column == 2)
                {
                    e.Range.CellStyle.Font.Bold = false;
                }
            }
        }
        private static void GameConsoles_Controller_CellExporting(object sender, DataGridCellExcelExportingEventArgs e)
        {
            if (e.CellType == ExportCellType.RecordCell)
            {
                if (e.Range.Column == 1)
                {
                    e.Range.CellStyle.Font.Size = 12;
                    e.Range.CellStyle.Font.Bold = true;
                }
                if (e.Range.Column == 2)
                {
                    e.Range.CellStyle.Font.Bold = false;
                }
            }
        }
    }

I hope you can help me export correctly.
Best regards.

Sivakumar R · Answer

Hi Juan,

We have analyzed with code snippet you have shared and it seems you are trying to export by creating datagrid without adding it as child for any layout. SfDataGrid views will be created only in OnSizeAllocated events which will get called when setting any parent view for data gird.

You need to load datagrid inside any view to export to excel sheet with the data. Refer the below code example.

Code snippets

private async void ExPortToExcel(object sender, EventArgs e)

{

layout = new StackLayout();

this.BindingContext = new OrderInfoRepository();

SfDataGrid grid = new SfDataGrid();

grid.ItemsSource = ((this.BindingContext) as OrderInfoRepository).OrderInfoCollection;

grid.AutoGenerateColumns = false;

grid.Columns.Add(new GridTextColumn() { MappingName = "OrderID" });

grid.Columns.Add(new GridTextColumn() { MappingName = "CustomerID" });

SfDataGrid grid1 = new SfDataGrid();

grid1.ItemsSource = ((this.BindingContext) as OrderInfoRepository).OrderInfoCollection;

grid1.AutoGenerateColumns = false;

grid.Columns.Add(new GridTextColumn() { MappingName = "Customer" });

grid.Columns.Add(new GridTextColumn() { MappingName = "ShipCountry" });

layout.Children.Add(grid);

layout.Children.Add(grid1);

this.Content = layout;

await Task.Delay(500);

DataGridExcelExportingController excelExport = new DataGridExcelExportingController();

DataGridExcelExportingOption options = new DataGridExcelExportingOption();

options.ExcelVersion = ExcelVersion.Excel2010;

var excelEngine = excelExport.ExportToExcel(grid, options);

var workBook1 = excelEngine.Excel.Workbooks[0];

var worksheet1 = workBook1.Worksheets[0];

excelEngine = excelExport.ExportToExcel(grid1, options);

var workBook2 = excelEngine.Excel.Workbooks[0];

var worksheet2 = workBook2.Worksheets[0];

var columnCount = grid.Columns.Count;

MemoryStream stream = new MemoryStream();

workBook1.SaveAs(stream);

if (Device.RuntimePlatform == Device.UWP)

Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().Save("DataGrid.xlsx", "application/msexcel", stream);

else

Xamarin.Forms.DependencyService.Get<ISave>().Save("DataGrid.xlsx", "application/msexcel", stream);

}

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ExportingTwoGrid-363526979

Thanks,

Sivakumar

Unknown · Answer

Thank you for your code snippet.I'm not having luck by adapting their code to my project, because I want to export 10+ datagrids which will not be shown on the page. Is there any way to export datagrids that are not displayed on the current page?, something similar to what is possible in Synfusion UWP.I have also noticed that their code snippet uses await Task. Delay (500), which if commented, the original text disappears and I get an empty excel file as results as expressed in the original question. What is the objetive of this? I'm worried about having to add delays when exporting each of the 10+ DataGrids.

Jayaraman Ayyanar · Answer

Hi Juan, 
 
We consider your requirement as feature request and we have added it to our feature request list. The feature would be included in any one of our upcoming release. 
Disclaimer : The date for the feature implementation is tentative and not a commitment on our parts.  
 
Regards, 
Jayaraman.