CODEWITHSUNDEEP
c#excelexport-to-excel
guetty abboud
guetty abboud

Reputation: 31

Index was out of range error while exporting to Excel

I am trying to export data from c# to excel using the following code:

enter  worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";

//Loop through each row and read value from each column. 
for (int i = 0; i < dataGridView1.Rows.Count + 1; i++)
{
    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}

for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
    for (int j = 0; j < dataGridView1.Columns.Count - 1; j++)
    {
        // Excel index starts from 1,1. As first Row would have the Column headers, 
        // adding a condition check. 

        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
    }
}

I get the following error:

Index was out of range.Must be non negative and less than the size of the collection. Parameter name: index.

UPDATE I solved the problem by changing this for statement:

for ( int i = -1; i < DataGridView1.Columns.Count; i++)

Upvotes: 0

Views: 4154

Answers (2)

Rahul Baghel
Rahul Baghel

Reputation: 1

Try:

sheet.GetRow(rowNumber).CreateCell(columnNumber);

And then fill the cell value.

Upvotes: 0

L. Guthardt
L. Guthardt

Reputation: 2056

I think the problem is that many online guides and tutorials exlpain that when you count through Lists<>, Arrays and rows/columns of a Table you need to add +1 because all these object containers have a start index of 0.

As a newcomer it might be hard to figure out at the beginning where you have to place the +1 and especially when you have to. Maybe you were confused because you wanted the total amount of rows as your max definition of i. But as you start your loop with int i = 0 (what is correct, because you dont want to skip the row with the index 0) you start as well at the point 0 and not 1. So there is no need to add +1 to the max breakpoint, because you still go dataGridView1.Rows.Count times (<-- amount how often your loop gets executed) through the rows.

This exception Index was out of range tells you that you wanted to do something with a row, which was out of range. It was out of range because this row's item didnt exist. Let´s say you have 10 rows with the index 0 - 9. Now you start going through them beginning at 0. So after 10 times executing, you went through rows 0 - 9. As dataGridView1.Rows.Count gives you the total amount of rows, in this example 10. But you set as the breakpoint dataGridView1.Rows.Count + 1 so the loop wants to do your task the 11th time with the row that has the index 10, but the index of your last row is 9. So it can't find this row and thats the situation when it gives you the Index out of range execption. Now I hope you understand what went wrong and why.

Upvotes: 1

Related Questions