We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

How can I exceed the limit of exporting to Excel?

Hello,


The maximum number of rows that Excel supports is 1,048,576. If there is more data than this in the SfDataGrid, it gives an error in the transfer process. To overcome this, instead of exporting to excel, I want to export to more than one excel. Does the SfDataGrid support bitwise transfer during the transfer process? In other words, can I save the first 1 million data in one excel and the second million data in another excel in the whole SfDataGrid? Also can I implement this if I only want to export data between 300,000-400,000?


3 Replies 1 reply marked as answer

VS Vijayarasan Sivanandham Syncfusion Team April 17, 2023 01:52 PM UTC

Hi Özgür,

We are analyzing your requirement of "How can I exceed the limit of exporting to Excel" and update you with further details on April 19, 2023.

Regards,
Vijayarasan S



VS Vijayarasan Sivanandham Syncfusion Team April 19, 2023 05:38 PM UTC

Özgür,

Find the responses to your queries below.

Queries

Responses

 

The maximum number of rows that Excel supports is 1,048,576. If there is more data than this in the SfDataGrid, it gives an error in the transfer process. To overcome this, instead of exporting to excel, I want to export to more than one excel. Does the SfDataGrid support bitwise transfer during the transfer process? In other words, can I save the first 1 million data in one excel and the second million data in another excel in the whole SfDataGrid?

 

We are still checking the possibilities to achieve your requirement and need two more business days to validate. We will update you with further details on April 24, 2023

 

Also can I implement this if I only want to export data between 300,000-400,000?

 


Currently, SfDataGrid does not contain direct support to achieve your requirement. However, we have created a workaround by customizing the SfDataGridToExcelConverter as shown below,

ExcelExportingOptions options = new ExcelExportingOptions();

 options.ExcelVersion = ExcelVersion.Excel2013;

 

 // Create the instance of CustomSfDataGridToExcelConverter

 CustomSfDataGridToExcelConverter customSfDataGridToExcelConverter = new CustomSfDataGridToExcelConverter();

 

 // Set the FirstDataRowIndex and LastDataRowIndex to specific records export to excel

 customSfDataGridToExcelConverter.FirstDataRowIndex = 300000;

 customSfDataGridToExcelConverter.LastDataRowIndex = 400000;

 var excelEngine = customSfDataGridToExcelConverter.ExportToExcel(this.sfDataGrid, sfDataGrid.View, options);

 

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



C# Code snippet related to customization of SfDataGridToExcelConverter:

// Customize the SfDataGridToExcelConverter to export the specific records to Excel.

 public class CustomSfDataGridToExcelConverter : SfDataGridToExcelConverter

 {

     // Set the FirstDataRowIndex and LastDataRowIndex to split the records.

     public int FirstDataRowIndex { get; set; } = -1;

     public int LastDataRowIndex { get; set; } = -1;    

   

     protected override void ExportRecordsToExcel(SfDataGrid grid, IWorksheet sheet, ExcelExportingOptions excelExportingOptions,

         IEnumerable records, IPropertyAccessProvider propertyAccessProvider, Group group)

     {

         bool hasrecords = false;

         foreach (var rec in records)

         {

             hasrecords = true;

             break;

         }

         if (!hasrecords)

             return;          

 

         if (grid.DetailsViewDefinition.Count == 0)

         {

             ObservableCollection<object> splitedRecords = new ObservableCollection<object>();

            

             int recordCount = 0;              

 

             foreach (var rec in records)

             {                  

                 // Split the records based on the FirstDataRowIndex and LastDataRowIndex.

                 if(recordCount > FirstDataRowIndex && recordCount < LastDataRowIndex)

                 {

                     // Add the records to the splitedRecords collection.

                     splitedRecords.Add(rec);                     

                 }

 

                 recordCount++;

             }

 

             if (splitedRecords.Count > 0)

             {

                 // Export the splitedRecords to Excel.

                 base.ExportRecordsToExcel(grid, sheet, excelExportingOptions, splitedRecords, propertyAccessProvider, group);

             }

 

             // Clear the splitedRecords collection.

             splitedRecords.Clear();              

         }

     }

 }

 

Find the sample demo in the attachment.


Attachment: Sample_44a35656.zip


VS Vijayarasan Sivanandham Syncfusion Team April 20, 2023 03:46 PM UTC

Özgür,

Currently, SfDataGrid does not contain direct support to achieve your requirement. However, we have created a workaround to achieve your requirement by overriding the ExportToExcel and ExportRecordsToExcel methods in the SfDataGridToExcelConverter as shown below,

public class CustomSfDataGridToExcelConverter : SfDataGridToExcelConverter

 {

     // Number of records per sheet

     int NumberOfRecordsPerSheet { get; set; } = 1048575;

     public override ExcelEngine ExportToExcel(SfDataGrid grid, ICollectionViewAdv view, ExcelExportingOptions excelExportingOptions)

     {

         ExcelEngine engine = new ExcelEngine();

         IWorkbook workbook = engine.Excel.Workbooks.Create(1);

         IWorksheet sheet;

         if (view == null)

             return engine;

         if (grid.DetailsViewDefinition.Count > 0)

             excelExportingOptions.AllowOutlining = true;

 

         bool exportAllPages = excelExportingOptions.ExportAllPages;

 

         workbook.Version = excelExportingOptions.ExcelVersion;

         excelExportingOptions.GetType().GetProperty("GroupColumnDescriptionsCount", System.Reflection.BindingFlags.NonPublic

             | System.Reflection.BindingFlags.Instance).SetValue(excelExportingOptions, view.GroupDescriptions.Count);

 

         var columns = (from column in grid.Columns where !excelExportingOptions.ExcludeColumns.Contains(column.MappingName) select column.MappingName).ToList();

 

         excelExportingOptions.GetType().GetField("columns", System.Reflection.BindingFlags.NonPublic |

             System.Reflection.BindingFlags.Instance).SetValue(excelExportingOptions, columns);

 

 

         sheet = workbook.Worksheets[0];

         ExportToExcelWorksheet(grid, view, sheet, excelExportingOptions);

 

         return engine;

     }

 

     protected override void ExportRecordsToExcel(SfDataGrid grid, IWorksheet sheet, ExcelExportingOptions excelExportingOptions,

         IEnumerable records, IPropertyAccessProvider propertyAccessProvider, Group group)

     {

         bool hasrecords = false;

         foreach (var rec in records)

         {

             hasrecords = true;

             break;

         }

         if (!hasrecords)

             return;

                  

         if (grid.DetailsViewDefinition.Count == 0)

         {

             ObservableCollection<object> splitedRecords = new ObservableCollection<object>();

             int sheetCount = 0;

             int recordCount = 0;

 

             int remainder = grid.View.Records.Count % NumberOfRecordsPerSheet;

             int numberOfSheets = (grid.View.Records.Count / NumberOfRecordsPerSheet);

 

             if (remainder > 0)

                 numberOfSheets++;

 

             foreach (var rec in records)

             {

                 recordCount++;

                 splitedRecords.Add(rec);

 

                 if (splitedRecords.Count >= NumberOfRecordsPerSheet)

                 {

                     if (sheet.Workbook.Worksheets.Count < numberOfSheets)

                         sheet.Workbook.Worksheets.Create();

                     if (sheetCount != 0)

                     {

                         // Export the columns header details for all other sheets

                         excelExportingOptions.RowIndex = 0;

                         ExportHeadersToExcel(grid, sheet.Workbook.Worksheets[sheetCount], excelExportingOptions);

                     }

                    

                     base.ExportRecordsToExcel(grid, sheet.Workbook.Worksheets[sheetCount], excelExportingOptions, splitedRecords, propertyAccessProvider, group);

 

                     this.GetType().BaseType.GetField("excelRowIndex", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).SetValue(this, 1);

 

                     sheetCount++;

                     splitedRecords.Clear();

 

                 }

 

             }

 

             if (splitedRecords.Count != 0)

             {

                 if (sheet.Workbook.Worksheets.Count < numberOfSheets)

                     sheet.Workbook.Worksheets.Create();

                 // Export the columns header details for all other sheets

                 excelExportingOptions.RowIndex = 0;

                 ExportHeadersToExcel(grid, sheet.Workbook.Worksheets[sheetCount], excelExportingOptions);

                 base.ExportRecordsToExcel(grid, sheet.Workbook.Worksheets[sheetCount], excelExportingOptions, splitedRecords, propertyAccessProvider, group);

             }

         }

     }

 }


In this workaround, we have added the header text for each sheet by using the ExportHeadersToExcel and reset the RowIndex as Zero.

Save data in Different Excel file:

Your requirement to save the first 1 million data in one Excel and the second million data in another Excel in the entire SfDataGrid can be achieved by saving each sheet in a different Excel file by creating a new workbook for each sheet. Refer to the below code snippet,

private void OnExportToExcelClicked(object sender, RoutedEventArgs e)

{

    ExcelExportingOptions options = new ExcelExportingOptions();

    options.ExcelVersion = ExcelVersion.Excel2013;

 

    // Create the instance of CustomSfDataGridToExcelConverter

    CustomSfDataGridToExcelConverter customSfDataGridToExcelConverter = new CustomSfDataGridToExcelConverter();

   

    var excelEngine = customSfDataGridToExcelConverter.ExportToExcel(this.sfDataGrid, sfDataGrid.View, options);

 

    // Save the each sheet in the workbook to a different file.

    foreach (var sheet in excelEngine.Excel.Workbooks[0].Worksheets)

    {

        // Create a new workbook.

        ExcelEngine engine = new ExcelEngine();

        // Create a new workbook with single worksheet.

        IWorkbook workBook = engine.Excel.Workbooks.Create(1);

        // Set the version of the workbook.

        workBook.Version = ExcelVersion.Excel2013;

 

        // Add the copied sheet to the workbook.

        engine.Excel.Worksheets.AddCopy(sheet);

 

        //Remove the first sheet which is created by default.

        engine.Excel.Worksheets.Remove(0);

        // Assign the name to the copied sheet.

        engine.Excel.Worksheets[0].Name = "Sheet1";

        workBook = engine.Excel.Workbooks[0];

 

        SaveFileDialog sfd = new SaveFileDialog

        {                   

            FilterIndex = 3,

            Filter = "Excel 97 to 2003 Files(*.xls)|*.xls|Excel 2007 to 2010 Files(*.xlsx)|*.xlsx|Excel 2013 File(*.xlsx)|*.xlsx"

        };

 

        if (sfd.ShowDialog() == true)

        {

            using (Stream stream = sfd.OpenFile())

            {                           

                workBook.SaveAs(stream);

            }

 

            //Message box confirmation to view the created workbook.

 

            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",

                                MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)

            {

 

                //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]

                System.Diagnostics.Process.Start(sfd.FileName);

            }

        }

    }

}      


Find the sample demo in the attachment.

Regards,

Vijayarasan S


If this post is helpful, please consider Accepting it as the solution so that other members can locate it more quickly.



Attachment: Sample_dac39cf4.zip

Marked as answer
Loader.
Live Chat Icon For mobile
Up arrow icon