XlsxFormatProvider Export Async

[Carlitos]

Is it possible to run the XlsxFormatProvider in an Async to report the progress on a RadProgressBar?

[Nikolay Demirev]

Hi Carlitos,

The XlsxFormatProvider does not provide information about the export or import progress. Still, you could use it in a separate thread so your UI does not freeze. If you use it to export data you could track the progress of filling the workbook with data, or if you use it to import data, you could track the progress of getting the data from the workbook.

I hope this helps.

Regards,
Nikolay Demirev
Telerik by Progress

[Carlitos]

Thanks Nikolay. I figured. Here is some sample code for users. 

In the UI: 

private readonly IReportBase report; //THIS IS MY OWN DEFINITION OF A REPORT
private readonly BackgroundWorker customExportWorker;
private string customExportFileName;

private void RadButtonCustomExport_Click(object sender, EventArgs e)
{
    using (var saveFileDialog = Utils.CreateExcelSaveFileDialog())
    {
        if (saveFileDialog.ShowDialog() == DialogResult.OK && !string.IsNullOrWhiteSpace(saveFileDialog.FileName))
        {
            if (!customExportWorker.IsBusy)
            {
                reportStart = DateTime.Now;
                customExportFileName = saveFileDialog.FileName;
                customExportWorker.RunWorkerAsync();
                radProgressBar.Minimum = 0;
                radProgressBar.Maximum = report.CustomExportSteps.Count;
                radProgressBar.Text = @"Starting...";

                EnableControlSettings(false);
            }
        }
    }
}

private void CustomExportWorker_DoWork(object sender, DoWorkEventArgs e)
{
    e.Result = report.CustomExport((BackgroundWorker)sender, customExportFileName);
}

private void CustomExportWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    radProgressBar.Value1 = e.ProgressPercentage;
    radProgressBar.Text = report.GetCustomExportStepName(e.ProgressPercentage);
}

private void CustomExportWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    reportEnd = DateTime.Now;
    var time = reportEnd - reportStart;
    radProgressBar.Text = $"Finished in {time:mm\\.ss}!!!";
    radProgressBar.Value1 = report.CustomExportSteps.Count;
    EnableControlSettings(true);
}

Then the report class inheriter has a specification on the export to be done: 

public override object CustomExport(BackgroundWorker worker, string fileName)
{
    if (fileName != null)
    {
        using (var stream = File.OpenWrite(fileName))
        {
            worker.ReportProgress(0);
            var workbook = new Workbook();
            var formatProvider = new XlsxFormatProvider();
            var worksheet = workbook.Worksheets.Add();
            worksheet.Name = $"{DateTime.Now:yyyy-MM-dd}";

            worker.ReportProgress(1);
            CellSelection reportHeaderSelection = worksheet.Cells[0, 0, 3, 41];
            reportHeaderSelection.SetIsBold(true);

            CellSelection reportNameCells = worksheet.Cells[0, 0, 0, 41];
            reportNameCells.Merge();
            reportNameCells.SetValue($"{Company.Name} - {Name}");
            reportNameCells.SetHorizontalAlignment(RadHorizontalAlignment.Left);
            reportNameCells.SetFontSize(21.5);

            CellSelection parameterCells = worksheet.Cells[1, 0, 1, 41];
            parameterCells.Merge();
            parameterCells.SetFontSize(14.7);

            string parameters = string.Empty;

            if (ItemsFrom == String.Empty && ItemsTo == ProgramSettings.LastReportValue)
            {
                parameters += "For all Items. ";
            }
            else
            {
                parameters += $"For items from {ItemsFrom} to {ItemsTo}. ";
            }

            if (VendorsFrom == String.Empty && VendorsTo == ProgramSettings.LastAccpacValue)
            {
                parameters += $"For all Vendors on {VendorType}. ";
            }
            else
            {
                parameters += $"For vendors from {VendorsFrom} to {VendorsTo} on {VendorType}. ";
            }

            parameters += $"From period {PeriodFrom} to {PeriodTo}.";

            parameterCells.SetValue($"{parameters}");
            parameterCells.SetHorizontalAlignment(RadHorizontalAlignment.Left);

            worker.ReportProgress(2);
            worksheet.Cells[3, 0].SetValue("Item Depot");
            worksheet.Cells[3, 1].SetValue("Vendor Id");
            worksheet.Cells[3, 2].SetValue("Item No");
            worksheet.Cells[3, 3].SetValue("Description");
            worksheet.Cells[3, 4].SetValue("Inactive Opt");
            worksheet.Cells[3, 5].SetValue("Stock Code");
            worksheet.Cells[3, 6].SetValue("Location");
            worksheet.Cells[3, 7].SetValue("Case Qty");
            worksheet.Cells[3, 8].SetValue("Per01");
            worksheet.Cells[3, 9].SetValue("Per02");
            worksheet.Cells[3, 10].SetValue("Per03");
            worksheet.Cells[3, 11].SetValue("Per04");
            worksheet.Cells[3, 12].SetValue("Per05");
            worksheet.Cells[3, 13].SetValue("Per06");
            worksheet.Cells[3, 14].SetValue("Per07");
            worksheet.Cells[3, 15].SetValue("Per08");
            worksheet.Cells[3, 16].SetValue("Per09");
            worksheet.Cells[3, 17].SetValue("Per10");
            worksheet.Cells[3, 18].SetValue("Per11");
            worksheet.Cells[3, 19].SetValue("Per12");
            worksheet.Cells[3, 20].SetValue("Current Month");
            worksheet.Cells[3, 21].SetValue("Total Excluding Current Month");
            worksheet.Cells[3, 22].SetValue("Qty Total On Hand");
            worksheet.Cells[3, 23].SetValue("Qty On Purchase Orders");
            worksheet.Cells[3, 24].SetValue("Qty On Sales Orders");
            worksheet.Cells[3, 25].SetValue("Average6 Months");
            worksheet.Cells[3, 26].SetValue("Average12 Months");
            worksheet.Cells[3, 27].SetValue("Actual Qty To Order");
            worksheet.Cells[3, 28].SetValue("Buyers Current Order Comments");
            worksheet.Cells[3, 29].SetValue("Moh Po For6 Months");
            worksheet.Cells[3, 30].SetValue("Moh Po So For6 Months");
            worksheet.Cells[3, 31].SetValue("Moh Po For12 Months");
            worksheet.Cells[3, 32].SetValue("Buying Customers In12 Months");
            worksheet.Cells[3, 33].SetValue("Item Notes");
            worksheet.Cells[3, 34].SetValue("New Po Value");
            worksheet.Cells[3, 35].SetValue("Vendor Unit Cost");
            worksheet.Cells[3, 36].SetValue("Qty In Transit");
            worksheet.Cells[3, 37].SetValue("Vendor Group");
            worksheet.Cells[3, 38].SetValue("Fob Cost");
            worksheet.Cells[3, 39].SetValue("Item Type");
            worksheet.Cells[3, 40].SetValue("Stock Unit");
            worksheet.Cells[3, 41].SetValue("Qty On Transfer");

            CellSelection columnNamesSelection = worksheet.Cells[3, 0, 3, 41];
            columnNamesSelection.SetBorders(new CellBorders(null, null, null,
                new CellBorder(CellBorderStyle.Thin, new ThemableColor(Color.FromRgb(0, 0, 0))), null, null,
                null, null));
            columnNamesSelection.SetIsWrapped(true);
            columnNamesSelection.SetFontSize(12);

            worksheet.Rows[3].SetHeight(new RowHeight(65, true));

            int currentRow = 4;
            int currentFormula = 5;

            worker.ReportProgress(3);
            foreach (var model in report)
            {
                worksheet.Cells[currentRow, 0].SetValueAsFormula($"=C{currentFormula}&G{currentFormula}");

                worksheet.Cells[currentRow, 1].SetValueAsText(model.VendorId ?? string.Empty);
                worksheet.Cells[currentRow, 2].SetValueAsText(model.ItemNo ?? string.Empty);
                worksheet.Cells[currentRow, 3].SetValueAsText(model.Description ?? string.Empty);
                worksheet.Cells[currentRow, 4].SetValueAsText(model.InactiveOpt ?? string.Empty);
                worksheet.Cells[currentRow, 5].SetValueAsText(model.StockCode ?? string.Empty);
                worksheet.Cells[currentRow, 6].SetValueAsText(model.Location ?? string.Empty);
                worksheet.Cells[currentRow, 7].SetValue((double)model.CaseQty);
                worksheet.Cells[currentRow, 8].SetValue((double)model.Per01);
                worksheet.Cells[currentRow, 9].SetValue((double)model.Per02);
                worksheet.Cells[currentRow, 10].SetValue((double)model.Per03);
                worksheet.Cells[currentRow, 11].SetValue((double)model.Per04);
                worksheet.Cells[currentRow, 12].SetValue((double)model.Per05);
                worksheet.Cells[currentRow, 13].SetValue((double)model.Per06);
                worksheet.Cells[currentRow, 14].SetValue((double)model.Per07);
                worksheet.Cells[currentRow, 15].SetValue((double)model.Per08);
                worksheet.Cells[currentRow, 16].SetValue((double)model.Per09);
                worksheet.Cells[currentRow, 17].SetValue((double)model.Per10);
                worksheet.Cells[currentRow, 18].SetValue((double)model.Per11);
                worksheet.Cells[currentRow, 19].SetValue((double)model.Per12);
                worksheet.Cells[currentRow, 20].SetValue((double)model.CurrentMonth);

                worksheet.Cells[currentRow, 21].SetValueAsFormula(
                    $"=SUM(I{currentFormula}:T{currentFormula})");

                worksheet.Cells[currentRow, 22].SetValue((double)model.QtyTotalOnHand);
                worksheet.Cells[currentRow, 23].SetValue((double)model.QtyOnPurchaseOrders);
                worksheet.Cells[currentRow, 24].SetValue((double)model.QtyOnSalesOrders);

                worksheet.Cells[currentRow, 25].SetValueAsFormula(
                    $"=ROUND(SUM(O{currentFormula}:T{currentFormula})/6,0)");

                worksheet.Cells[currentRow, 26].SetValueAsFormula($"=ROUND(V{currentFormula}/12,0)");

                worksheet.Cells[currentRow, 27].SetValue((double)model.ActualQtyToOrder);
                worksheet.Cells[currentRow, 28].SetValueAsText(model.BuyersCurrentOrderComments ??
                                                               string.Empty);

                worksheet.Cells[currentRow, 29].SetValueAsFormula(
                    $"=IF(Z{currentFormula}=0, 0, ROUND((W{currentFormula}+X{currentFormula}+AB{currentFormula})/Z{currentFormula},1))");

                worksheet.Cells[currentRow, 30].SetValueAsFormula(
                    $"=IF(Z{currentFormula}=0, 0, ROUND((W{currentFormula}+X{currentFormula}+AB{currentFormula}-Y{currentFormula})/Z{currentFormula},1))");

                worksheet.Cells[currentRow, 31].SetValueAsFormula(
                    $"=IF(AA{currentFormula}=0, 0, ROUND((W{currentFormula}+X{currentFormula}+AB{currentFormula})/AA{currentFormula},1))");

                worksheet.Cells[currentRow, 32].SetValue(model.BuyingCustomersIn12Months);
                worksheet.Cells[currentRow, 33].SetValueAsText(model.ItemNotes ?? string.Empty);

                worksheet.Cells[currentRow, 34].SetValueAsFormula(
                    $"=ROUND(AJ{currentFormula}*AB{currentFormula},2)");

                worksheet.Cells[currentRow, 35].SetValue((double)model.VendorUnitCost);
                worksheet.Cells[currentRow, 36].SetValue((double)model.QtyInTransit);
                worksheet.Cells[currentRow, 37].SetValueAsText(model.VendorGroup ?? string.Empty);
                worksheet.Cells[currentRow, 38].SetValue((double)model.FobCost);
                worksheet.Cells[currentRow, 39].SetValueAsText(model.ItemType ?? string.Empty);
                worksheet.Cells[currentRow, 40].SetValueAsText(model.StockUnit ?? string.Empty);
                worksheet.Cells[currentRow, 41].SetValue((double)model.QtyOnTransfer);

                CellSelection reportRow = worksheet.Cells[currentRow, 0, currentRow, 41];
                reportRow.SetFontSize(12);

                currentRow++;
                currentFormula++;
            }

            worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();

            worker.ReportProgress(4);
            formatProvider.Export(workbook, stream);
        }
    }

    return null;
}

Hope that makes sense. But the UI gets refreshed as the export is done. 

Thanks anyways, 

Carlitos

[Nikolay Demirev]

Hello Carlitos,

I have created sample project showing how to export and update a progress bar on the same time without freezing the UI.

The sample project implements the export of the same document using our RadSpreadProcessing and RadSpreadStreamProcessing libraries.

Regards,
Nikolay Demirev
Telerik by Progress

[Carlitos]

Thanks Nikolay on the sample project. Didn't realize there was a SpreadStreamProcessing library which is much faster. Will update my project accordingly. Much appareciated!

[Nikolay Demirev]

Hi Carlitos,

Please bear in mind that RadSpreadStreamProcessing has some missing functionality compared to RadSpreadProcessing. Here you can read more about the RadSpreadStreamProcessing.

Regards,
Nikolay Demirev
Telerik by Progress

[Carlitos]

Hi Nikolay, 

I still used the BackgroundWorker for my winforms project but very happy with the RadSpreadStreamProcessing. With the RadSpreadProcessing the time to export took roughly 3-4 minutes. With the RadSpreadStreamProcessing it takes less than a second to generate the same file. Unbelievable fast! Here is my code: 

public override object CustomExport(BackgroundWorker worker, string fileName)
        {
            if (fileName == null)
            {
                return null;
            }

            using (var stream = File.OpenWrite(fileName))
            {
                using (var workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, stream))
                {
                    using (var worksheetExporter = workbookExporter.CreateWorksheetExporter($"{DateTime.Now:yyyy-MM-dd}"))
                    {
                        using (var row = worksheetExporter.CreateRowExporter())
                        {
                            using (var cell = row.CreateCellExporter())
                            {
                                var titleFormat = new SpreadCellFormat
                                {
                                    FontSize = 20,
                                    IsBold = true
                                };

                                cell.SetValue($"{Company.Name} - {Name}");
                                cell.SetFormat(titleFormat);
                            }
                        }

                        using (var row = worksheetExporter.CreateRowExporter())
                        {
                            using (var cell = row.CreateCellExporter())
                            {
                                var parameterFormat = new SpreadCellFormat
                                {
                                    FontSize = 14,
                                    IsBold = true
                                };

                                cell.SetValue(BuildReportParameters());
                                cell.SetFormat(parameterFormat);
                            }
                        }

                        // ReSharper disable once UnusedVariable
                        using (var row = worksheetExporter.CreateRowExporter())
                        {
                            // EMPTY ROW
                        }

                        using (var row = worksheetExporter.CreateRowExporter())
                        {
                            row.SetHeightInPoints(50);

                            var columnsFormat = new SpreadCellFormat()
                            {
                                FontSize = 9,
                                IsBold = true,
                                WrapText = true,
                                BottomBorder = new SpreadBorder(SpreadBorderStyle.Thin, SpreadThemableColor.FromRgb(0, 0, 0))
                            };

                            CreateColumnHeadingCell(row, columnsFormat, "Item Depot");
                            CreateColumnHeadingCell(row, columnsFormat, "Vendor Id");
                            CreateColumnHeadingCell(row, columnsFormat, "Item No");
                            CreateColumnHeadingCell(row, columnsFormat, "Description");
                            CreateColumnHeadingCell(row, columnsFormat, "Inactive Opt");
                            CreateColumnHeadingCell(row, columnsFormat, "Stock Code");
                            CreateColumnHeadingCell(row, columnsFormat, "Location");
                            CreateColumnHeadingCell(row, columnsFormat, "Case Qty");
                            CreateColumnHeadingCell(row, columnsFormat, "Per01");
                            CreateColumnHeadingCell(row, columnsFormat, "Per02");
                            CreateColumnHeadingCell(row, columnsFormat, "Per03");
                            CreateColumnHeadingCell(row, columnsFormat, "Per04");
                            CreateColumnHeadingCell(row, columnsFormat, "Per05");
                            CreateColumnHeadingCell(row, columnsFormat, "Per06");
                            CreateColumnHeadingCell(row, columnsFormat, "Per07");
                            CreateColumnHeadingCell(row, columnsFormat, "Per08");
                            CreateColumnHeadingCell(row, columnsFormat, "Per09");
                            CreateColumnHeadingCell(row, columnsFormat, "Per10");
                            CreateColumnHeadingCell(row, columnsFormat, "Per11");
                            CreateColumnHeadingCell(row, columnsFormat, "Per12");
                            CreateColumnHeadingCell(row, columnsFormat, "Current Month");
                            CreateColumnHeadingCell(row, columnsFormat, "Total Excluding Current Month");
                            CreateColumnHeadingCell(row, columnsFormat, "Qty Total On Hand");
                            CreateColumnHeadingCell(row, columnsFormat, "Qty On Purchase Orders");
                            CreateColumnHeadingCell(row, columnsFormat, "Qty On Sales Orders");
                            CreateColumnHeadingCell(row, columnsFormat, "Average 6 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Average 12 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Actual Qty To Order");
                            CreateColumnHeadingCell(row, columnsFormat, "Buyers Current Order Comments");
                            CreateColumnHeadingCell(row, columnsFormat, "Moh Po For 6 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Moh Po So For 6 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Moh Po For 12 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Buying Customers In 12 Months");
                            CreateColumnHeadingCell(row, columnsFormat, "Item Notes");
                            CreateColumnHeadingCell(row, columnsFormat, "New Po Value");
                            CreateColumnHeadingCell(row, columnsFormat, "Vendor Unit Cost");
                            CreateColumnHeadingCell(row, columnsFormat, "Qty In Transit");
                            CreateColumnHeadingCell(row, columnsFormat, "Vendor Group");
                            CreateColumnHeadingCell(row, columnsFormat, "Fob Cost");
                            CreateColumnHeadingCell(row, columnsFormat, "Item Type");
                            CreateColumnHeadingCell(row, columnsFormat, "Stock Unit");
                            CreateColumnHeadingCell(row, columnsFormat, "Qty On Transfer");
                        }

                        var stringFormat = new SpreadCellFormat()
                        {
                            FontSize = 8
                        };

                        var intergerFormat = new SpreadCellFormat()
                        {
                            FontSize = 8,
                            NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* \"-\"_);_(@_)"
                        };

                        var oneDecimalFormat = new SpreadCellFormat()
                        {
                            FontSize = 8,
                            NumberFormat = "_(* #,##0.0_);[Red]_(* (#,##0.0);_(* \"-\"???_);_(@_)"
                        };

                        var twoDecimalFormat = new SpreadCellFormat()
                        {
                            FontSize = 8,
                            NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);_(* \"-\"???_);_(@_)"
                        };

                        var threeDecimalFormat = new SpreadCellFormat()
                        {
                            FontSize = 8,
                            NumberFormat = "_(* #,##0.000_);[Red]_(* (#,##0.000);_(* \"-\"???_);_(@_)"
                        };

                        var currentRow = 5;
                        var progressReport = 0;

                        foreach (var model in report)
                        {
                            using (var row = worksheetExporter.CreateRowExporter())
                            {
                                CreateDataCellFormula(row, stringFormat, $"=C{currentRow}&G{currentRow}");
                                CreateDataCell(row, stringFormat, model.VendorId ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.ItemNo ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.Description ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.InactiveOpt ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.StockCode ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.Location ?? string.Empty);
                                CreateDataCell(row, intergerFormat, (double)model.CaseQty);
                                CreateDataCell(row, intergerFormat, (double)model.Per01);
                                CreateDataCell(row, intergerFormat, (double)model.Per02);
                                CreateDataCell(row, intergerFormat, (double)model.Per03);
                                CreateDataCell(row, intergerFormat, (double)model.Per04);
                                CreateDataCell(row, intergerFormat, (double)model.Per05);
                                CreateDataCell(row, intergerFormat, (double)model.Per06);
                                CreateDataCell(row, intergerFormat, (double)model.Per07);
                                CreateDataCell(row, intergerFormat, (double)model.Per08);
                                CreateDataCell(row, intergerFormat, (double)model.Per09);
                                CreateDataCell(row, intergerFormat, (double)model.Per10);
                                CreateDataCell(row, intergerFormat, (double)model.Per11);
                                CreateDataCell(row, intergerFormat, (double)model.Per12);
                                CreateDataCell(row, intergerFormat, (double)model.CurrentMonth);
                                CreateDataCellFormula(row, intergerFormat, $"=SUM(I{currentRow}:T{currentRow})");
                                CreateDataCell(row, intergerFormat, (double)model.QtyTotalOnHand);
                                CreateDataCell(row, intergerFormat, (double)model.QtyOnPurchaseOrders);
                                CreateDataCell(row, intergerFormat, (double)model.QtyOnSalesOrders);
                                CreateDataCellFormula(row, oneDecimalFormat, $"=ROUND(SUM(O{currentRow}:T{currentRow})/6,0)");
                                CreateDataCellFormula(row, oneDecimalFormat, $"=ROUND(V{currentRow}/12,0)");
                                CreateDataCell(row, intergerFormat, (double)model.ActualQtyToOrder);
                                CreateDataCell(row, stringFormat, model.BuyersCurrentOrderComments ?? string.Empty);
                                CreateDataCellFormula(row, oneDecimalFormat, $"=IF(Z{currentRow}=0, 0, ROUND((W{currentRow}+X{currentRow}+AB{currentRow})/Z{currentRow},1))");
                                CreateDataCellFormula(row, oneDecimalFormat, $"=IF(Z{currentRow}=0, 0, ROUND((W{currentRow}+X{currentRow}+AB{currentRow}-Y{currentRow})/Z{currentRow},1))");
                                CreateDataCellFormula(row, oneDecimalFormat, $"=IF(AA{currentRow}=0, 0, ROUND((W{currentRow}+X{currentRow}+AB{currentRow})/AA{currentRow},1))");
                                CreateDataCell(row, intergerFormat, model.BuyingCustomersIn12Months);
                                CreateDataCell(row, stringFormat, model.ItemNotes ?? string.Empty);
                                CreateDataCellFormula(row, twoDecimalFormat, $"=ROUND(AJ{currentRow}*AB{currentRow},2)");
                                CreateDataCell(row, threeDecimalFormat, (double)model.VendorUnitCost);
                                CreateDataCell(row, intergerFormat, (double)model.QtyInTransit);
                                CreateDataCell(row, stringFormat, model.VendorGroup ?? string.Empty);
                                CreateDataCell(row, threeDecimalFormat, (double)model.FobCost);
                                CreateDataCell(row, stringFormat, model.ItemType ?? string.Empty);
                                CreateDataCell(row, stringFormat, model.StockUnit ?? string.Empty);
                                CreateDataCell(row, intergerFormat, (double)model.QtyOnTransfer);
                            }

                            progressReport++;
                            worker.ReportProgress(progressReport);
                            currentRow++;
                        }

                        worksheetExporter.MergeCells(0, 0, 0, 41);
                        worksheetExporter.MergeCells(1, 0, 1, 41);
                    }
                }
            }

            return null;
        }

        private void CreateColumnHeadingCell(IRowExporter row, SpreadCellFormat columnFormat, string columnName)
        {
            using (var cell = row.CreateCellExporter())
            {
                cell.SetValue(columnName);
                cell.SetFormat(columnFormat);
            }
        }

        private void CreateDataCellFormula(IRowExporter row, SpreadCellFormat columnFormat, string data)
        {
            using (var cell = row.CreateCellExporter())
            {
                cell.SetFormula(data);
                cell.SetFormat(columnFormat);
            }
        }

        private void CreateDataCell(IRowExporter row, SpreadCellFormat columnFormat, string data)
        {
            using (var cell = row.CreateCellExporter())
            {
                cell.SetValue(data);
                cell.SetFormat(columnFormat);
            }
        }

        private void CreateDataCell(IRowExporter row, SpreadCellFormat columnFormat, double data)
        {
            using (var cell = row.CreateCellExporter())
            {
                cell.SetValue(data);
                cell.SetFormat(columnFormat);
            }
        }

And the BackgroundWorker events are set like this: 

private void RadButtonCustomExport_Click(object sender, EventArgs e)
{
    using (var saveFileDialog = Utils.CreateExcelSaveFileDialog())
    {
        if (saveFileDialog.ShowDialog() == DialogResult.OK && !string.IsNullOrWhiteSpace(saveFileDialog.FileName))
        {
            if (!customExportWorker.IsBusy)
            {
                reportStart = DateTime.Now;
                customExportFileName = saveFileDialog.FileName;
                customExportWorker.RunWorkerAsync();
                radProgressBar.Minimum = 0;
                radProgressBar.Maximum = radGridViewReportData.RowCount;
                radProgressBar.Text = @"Starting...";
                EnableControlSettings(false);
            }
        }
    }
}

private void CustomExportWorker_DoWork(object sender, DoWorkEventArgs e)
{
    e.Result = report.CustomExport((BackgroundWorker)sender, customExportFileName);
}

private void CustomExportWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    radProgressBar.Value1 = e.ProgressPercentage;
    radProgressBar.Text = $@"Exporting record {e.ProgressPercentage}...";
}

private void CustomExportWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    reportEnd = DateTime.Now;
    var time = reportEnd - reportStart;
    radProgressBar.Text = $@"Finished in {time:mm\.ss}!!!";
    EnableControlSettings(true);
}

Thanks for your help in letting me know about the RadSpreadStreamProcessing. 

 

Carlitos

[Nikolay Demirev]

Hello Carlitos,

I am glad you liked the RadSpreadStreamProcessing.

I have reviewed your code and I would suggest you the following:

You are using this line to insert empty row:

// ReSharper disable once UnusedVariable
using (var row = worksheetExporter.CreateRowExporter())
{
    // EMPTY ROW
}

Instead, you could use 

worksheetExporter.SkipRows(1);

It is the same, but this way you will not receive the ReSharper warning.

Regards,
Nikolay Demirev
Telerik by Progress

[Carlitos]

Thanks Nikolay! Not sure what I was doing wrong before but I was not using the SkipRows method because it was giving me an error and hence creating an empty row. I have changed my code accordingly. Again, many thanks for your help. Much appreciated!