Unplanned
Last Updated: 20 Mar 2024 12:49 by Göran
Göran
Created on: 20 Mar 2024 12:49
Category: SpreadProcessing
Type: Bug Report
1
SpreadProcessing: Exporting is slow when a ListDataValidationRule referring to a CellRange from a sheet is used

Use the below code snippet to generate XLSX document and export it. You will notice that the export operation is extremely slow:

Stopwatch sw = new Stopwatch();
sw.Start();
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets.Add();
worksheet2.Name ="Days";
List<string> weekdays = new List<string>() { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"};
for (int i = 0; i < 7; i++)
{
    worksheet2.Cells[0, i].SetValue(weekdays[i]);
}

for (int i = 0; i < 200; i++)
{
    for (int j = 0; j < 10; j++)
    {

        CellIndex cellIndex = new CellIndex(i, j);
        CellSelection selection = worksheet.Cells[cellIndex];
        selection.SetValue("Wednesday");

        var context = new ListDataValidationRuleContext(worksheet, cellIndex)
        {
            InputMessageTitle = "InputMessageTitle",
            InputMessageContent = "InputMessageTitle"
        };

        context.ErrorStyle = ErrorStyle.Stop;
        context.ErrorAlertTitle = "ErrorAlertTitle";
        context.ErrorAlertContent = "ErrorAlertContent";
        context.InCellDropdown = true;
        context.Argument1 = "=Days!A0:A6"; //"Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday";
        ListDataValidationRule rule = new(context);
        worksheet.Cells[cellIndex].SetDataValidationRule(rule);
    }
}

string outputFile = @"..\..\..\output.xlsx";
File.Delete(outputFile);

using (Stream output = new FileStream(outputFile, FileMode.Create))
{

    formatProvider.Export(workbook, output);
}
sw.Stop();
Debug.WriteLine("Export " + sw.ElapsedMilliseconds);

0 comments