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?
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
Ö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?
|
Find the sample demo in the attachment. |
Ö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.