Worksheet.UsedCellRange reporting inaccurate cell usage

[Doug]

I'm using SpreadProcessing in conjunction with a TelerikSpreadsheet in a Blazor app where the user can modify a spreadsheet and click a submit button. In my submit handler I'm using the result of Worksheet.UsedCellRange to set up my loops for the purpose of validation. When the user clicks submit, I get the expected result from Worksheet.UsedCellRange and then I spin the used rows and columns and apply validation. If there are validation errors I make the cell red and then export the workbook back to a memory stream and assign the byte array back to the variable to which the spreadsheet is bound. That works fine.

The issue I'm having is that if the user clicks submit, gets validation errors, makes a change and then clicks the submit button a second time, the result I get from Worksheet.UsedCellRange includes all rows, even if only one or two rows actually have data. To narrow down the issue I removed all my validation logic so I am not making any changes to the cells before I assign the byte array, but the second time through the result of UsedCellRange still reports all rows. Am I not assigning the byte array correctly at the end of my submit method? See code:

    private async Task OnSubmitAsync()
    {
            byte[] sheet = await _spreadsheetRef.ExportToExcelAsync();

            using MemoryStream ms = new(sheet);

            XlsxFormatProvider formatProvider = new();
            Workbook workbook = formatProvider.Import(ms, new TimeSpan(0, 0, 5));
            Worksheet worksheet = workbook.Worksheets.First();

            // The first time through this is correct, second time through it reports all 200 rows are in use. Why?
            CellRange cellRange = worksheet.UsedCellRange;

            // Validation logic would normally go here

            // Set the byte array back to the bound variable
            // Should I be doing this differently?
            ms.SetLength(0);
            formatProvider.Export(workbook, ms, new TimeSpan(0, 0, 30));
            _fileData = ms.ToArray(); // _fileData is bound to the TelerikSpreadsheet component
   }

 

[Dess | Tech Support Engineer, Principal]

Hello, Doug,

Following the provided information, I tried to replicate the issue locally without any success in order to identify whether the problem exists in the SpreadProcessing library or the Blazor's Spreadsheet component. It is essential for the further troubleshooting since this determines which team would provide the best possible assistance. Could you please elaborate?

The behavior you are seeing with Worksheet.UsedCellRange reporting all rows as "used" after the first submit is due to how this property works in SpreadProcessing. UsedCellRange considers any cell that contains data or formatting as "used." When you apply formatting (like setting a cell's background to red for validation errors), those cells are now included in the used range—even if their values are empty. This is by design.If you need to loop only over cells that have actual values (ignoring formatting), use the GetUsedCellRange method and specify that you only want to consider cell values: Iterating Used Cells. This approach ensures that only cells containing data are included in the range, and formatted-but-empty cells are excluded.

The ExportToExcelAsync method returns a byte array. However, it would be greatly appreciated if you can save the result at that specific moment to an Excel file (in both cases), before importing the content with the XlsxFormatProvider. Thus, we can identify whether the UsedCellRange is somehow change by the Spreadsheet component itself or the SpreadProcessing library.

Thank you in advance for your cooperation. I am looking forward to your reply.

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik

[Doug]

Dess,

I changed my code to use GetUsedCellRange and that works as I expect so I'll use that for the time being, but I used the code in the original post to create a standalone Blazor WebAssembly app (attached) which demonstrates the issue with UsedCellRange. Instructions to repro it are at the top of the home page. I'll let you modify that as you see fit to determine which side the issue is coming from, but to my knowledge I'm not putting any formatting on the cells, and yet UsedCellRange reports a ToIndex of 199 the second time through.

[Dess | Tech Support Engineer, Principal]

Hi, Doug,

I am glad that the GetUsedCellRange approach works for your scenario. Thank you for the provided sample project. After reviewing it, indeed, the second submitting reports 200 rows are used:

I used the Save option offered by the Spreadsheet component to save the XLSX document initially after starting the application and after the first clicking of the Submit. Here is the comparison:

The second file contains much more empty cells. That is why the UsedCellRange returns more rows the second time. As expected, the appropriate way is the already suggested GetUsedCellRange() method with passed CellPropertyDefinitions.ValueProperty argument to extract only cells with actual values.

After looking into the code implementation and after discussing the case with the Blazor team that are well familiar with the Spreadsheet component and its export functionality I noticed that the memory stream is not properly managed. This is the needed modification:

    private async Task OnSubmitAsync()
    {
        try
        {
            {

                // Map the spreadsheet data to the unit detail model
                _loaderContainerVisible = true;

                // Refresh the UI: https://docs.telerik.com/blazor-ui/components/loader/overview#creating-blazor-loader
                await Task.Delay(1);

                byte[] sheet = await _spreadsheetRef.ExportToExcelAsync();
                Workbook workbook;
                XlsxFormatProvider formatProvider = new();
                using MemoryStream ms = new(sheet);
                {

                    workbook = formatProvider.Import(ms, new TimeSpan(0, 0, 5));
                    Worksheet worksheet = workbook.Worksheets.First();

                    // The first time through this is correct, second time through it reports all 200 rows are in use. Why?
                    CellRange cellRange = worksheet.UsedCellRange;

                    Console.WriteLine($"UsedCellRange FromIndex = {cellRange.FromIndex}");
                    Console.WriteLine($"UsedCellRange ToIndex = {cellRange.ToIndex}");

                    // Validation logic would normally go here

                    // Set the byte array back to the bound variable
                    // Should I be doing this differently?
                    // ms.SetLength(0);
                }
                formatProvider.Export(workbook, ms, new TimeSpan(0, 0, 30));
                _fileData = ms.ToArray(); // _fileData is bound to the TelerikSpreadsheet component
            }
        }
        catch (Exception)
        {
        }
        finally
        {
            _loaderContainerVisible = false;
        }
    }

Thus, we will ensure that the stream is closed after importing and the UsedCellRange returns identical value each time.

Regards,
Dess | Tech Support Engineer, Principal
Progress Telerik

[Doug]

Dess,

That makes sense. Thanks for looking into that.