Get the error The given key '3' was not present in the dictionary when read the excel file

[Chinh]

Hi all, 

I got some problems when I try to read my Excel file as below:

1. It skipped the null cells, it only read data at the cells having value. For example in my case, after reading cell 2 at row 2, it jumped to cell 14. 

2. It showed the error message "The given key '3' was not present in the dictionary." at cell 15

You can see my code, the Excel file I used, and the result in below

My code

@page "/testpage"
@using Telerik.Documents.SpreadsheetStreaming;
<div>
    @((MarkupString)(str.ToString()))
</div>


@code {
    private StringBuilder str = new StringBuilder();
    protected override void OnInitialized()
    {
        str = ReadData();
    }
    private StringBuilder ReadData()
    {

        try
        {
            string filename = ".\\Template.xlsx";
            using (FileStream fs = new FileStream(filename, FileMode.Open))
            {
                using (IWorkbookImporter workBookImporter = SpreadImporter.CreateWorkbookImporter(SpreadDocumentFormat.Xlsx, fs))
                {
                    foreach (IWorksheetImporter worksheetImporter in workBookImporter.WorksheetImporters)
                    {
                        foreach (IRowImporter rowImporter in worksheetImporter.Rows)
                        {
                            foreach (ICellImporter cell in rowImporter.Cells)
                            {
                                if(cell.Value!= null)
                                {
                                    str.Append(cell.Value.ToString());

                                }
                                else
                                {
                                    str.Append("NULL");
                                }
                                str.Append(",");
                            }
                            str.Append("<br/>");
                        }

                    }
                }
            }
            return str;
        }
        catch(Exception ex)
        {
            str.Append("<br/>");
            str.Append(ex.Message);
            return str;
        }

    }
}
 My Excel file as a picture below, I also attached my Excel in the question (Template.rar)

  The result when I run 

  Everyone who know how to fix it, please help me.

Thank you

[Nadezhda Tacheva]

Hi Chinh,

I see that the product of the ticket is Upload for Blazor, however, it looks like the scenario targets the Document Processing libraries.

I have changed the product, so my colleagues from the Document Processing team can revise the case. For that purpose, can you please send the Excel file in question, so they can test reading it? This will help speed up the case resolving process.

Thank you in advance!

[Chinh]

Thank you Nadezhda, I attached the excel file I used in this question, please see it.

It is an urgent case, I hope your team can help me.

[Yoan]

Hello Chinh,

Thank you for the provided document. After testing it I managed to reproduce both behaviors you are describing.

Firstly, the thrown exception is caused when a cell has its 'Normal font' formatting property disabled. I have logged an item regarding this case in our Feedback Portal on your behalf: SpreadStreamProcessing: KeyNotFoundException: when iterating cells having a disabled 'Normal font' formatting option. If you'd like you can subscribe to the task to track its progress and receive notifications about any status updates.

I apologize for the inconvenience and for any possibly caused setbacks. As a thank you for bringing this up to us, I have also updated your Telerik Points. If it fits your scenario and the difference won't be much of an issue, as a workaround I can offer you using the SpreadProcessing library instead. I have applied the same approach with it and iterating the cells does not throw an exception:

Workbook workbook;
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();

using (Stream input = new FileStream("Template.xlsx", FileMode.Open))
{
    workbook = formatProvider.Import(input);
}

StringBuilder str = new StringBuilder();

CellRange usedCellRange = workbook.ActiveWorksheet.UsedCellRange;
for (int rowIndex = usedCellRange.FromIndex.RowIndex; rowIndex <= usedCellRange.ToIndex.RowIndex; rowIndex++)
{
    for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
    {
        CellSelection cell = workbook.ActiveWorksheet.Cells[rowIndex, columnIndex];
        
        if (cell.GetValue().Value.RawValue != "")
        {
            str.Append(cell.GetValue().Value.RawValue.ToString());

        }
        else
        {
            str.Append("NULL");
        }
        str.Append(',');
    }
}

As for the skipping of the empty cells, the document structure of such a file only has the cells with values stored inside it, so only they will be iterated in that case. If you wish to use SpreadStreamProcesisng I can suggest manually verifying when there is a skip in the cells, calculating how many cells this skip is, and inserting the same amount of "NULL" entries in the string builder.

The alternative SpreadProcesisng approach I have shared eliminates this skipping by utilizing 'for' iterations, which include the empty cells as well. If the SpreadProcessing library method satisfies you, the above-provided approach should eliminate both issues for you.

If you have any questions or feedback, I remain at your disposal.

Regards,
Yoan
Progress Telerik