How to avoid "Index Out of Range Exception" while accessing Excel worksheet?

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files.

Worksheet Indices in Excel Workbook

The worksheet index in an Excel workbook starts with 0 and must always be non-negative and less than the size of collection.

Why does the Index Out of Range Exception is thrown?

Index Out of Range exception occurs when wrong index value is specified. For example, if an Excel workbook has 3 worksheets, then their respective indices will be 0,1 and 2. Trying to access the worksheet with index 3 and above results in Index Out of Range exception.

C#

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
 
//Create workbook with 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
 
//Accessing first worksheet from the worksheet collection
IWorksheet sheet1 = workbook.Worksheets[0];
 
//Accessing second worksheet from the worksheet collection
IWorksheet sheet2 = workbook.Worksheets[1];
 
//Accessing third worksheet from the worksheet collection
IWorksheet sheet3 = workbook.Worksheets[2];
 
//Accessing fourth worksheet which is not the worksheet collection. 
IWorksheet sheet4 = workbook.Worksheets[3];

The above code snippet results in Index Out Of Range exception while accessing the fourth worksheet, because it is not in the worksheet collection.

Index Out Of Range Exception

How to overcome this Index Out of Range Exception?

As the reason for this exception is discussed above, the only way to overcome or avoid this exception is to access the worksheets that are existing in the worksheet collection alone.

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions etc., with code examples.

Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.

Conclusion

I hope you enjoyed learning about how to handle "Index Out of Range Exception" while accessing Excel worksheet.

You can refer to our WinForms XIsIO’s feature tour page to know about its other groundbreaking feature representations. You can also explore our WinForms XIsIO documentation to understand how to present and manipulate data.

For current customers, you can check out our WinForms components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WinForms XIsIO and other WinForms components.

If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!