none
C# Index was out of range error when export to excel small amount of data RRS feed

  • Question

  • I have a winform built in C# which can export data from SQL and then allows exporting it to Excel. The problem I faced is when I have only one row of data table wont be exported and I get an error that index is out of range. There is no problem whatsoever if there are two rows and more. Could you help me figure out what should be fixed? Debugging brings me to line where I assign file name - saveFileDialog1.FileName = "Report - " + dataGridView1.Rows[1].Cells[0].Value.ToString();

    full code:

    private void btnExport1_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            this.Text = "Exporting...";

            _Application excel = new Microsoft.Office.Interop.Excel.Application();
            _Workbook workbook = excel.Workbooks.Add(Type.Missing);
            _Worksheet worksheet = null;
            Range headersRow;
            Range allData;
            Range updateTimestamp;
            saveFileDialog1.FileName = "Report - " + dataGridView1.Rows[1].Cells[0].Value.ToString();
            excel.ActiveWindow.Zoom = 80;

            try
            {

                worksheet = workbook.ActiveSheet;
                worksheet.Name = "Summary";
                worksheet.Cells.WrapText = false;
                worksheet.Cells.Font.Name = "Text";
                worksheet.Cells.Interior.Color = Color.FromArgb(255, 255, 255);
                worksheet.Cells.NumberFormat = "@";


                for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count ; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < dataGridView1.Columns.Count +1; colIndex++)
                    {
                        if (rowIndex == 0) 
                        {
                            worksheet.Cells[rowIndex + 4, colIndex + 1] = dataGridView1.Columns[colIndex].HeaderText;
                        } 
                        else
                        {
                            worksheet.Cells[rowIndex + 4, colIndex + 1] = dataGridView1.Rows[rowIndex-1].Cells[colIndex].Value.ToString();
                        }
                    }
                }



                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        worksheet.Columns.AutoFit();
                        // worksheet.Application.ActiveWindow.SplitRow = 4;
                        // worksheet.Application.ActiveWindow.FreezePanes = true;
                        worksheet.Shapes.AddPicture(@"... logo.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 180, 39);

                        allData = worksheet.UsedRange;
                        allData.Borders.LineStyle = XlLineStyle.xlContinuous;

                        Microsoft.Office.Interop.Excel.Range ur = worksheet.UsedRange;
                        Microsoft.Office.Interop.Excel.Range r = worksheet.Cells[4, ur.Columns.Count];
                        headersRow = worksheet.Range["A4", r];

                        headersRow.Cells.Font.Bold = true;
                        headersRow.Cells.Font.Color = Color.FromArgb(255, 255, 255);
                        headersRow.Cells.Interior.Color = Color.FromArgb(0, 055, 085);


                        worksheet.Cells[2, 5] = worksheet.Cells[5, 19];
                        worksheet.Cells[2, 5].Font.Bold = true;
                        worksheet.Cells[2, 4] = "Report date:";
                        worksheet.Cells[2, 4].Font.Bold = true;
                        worksheet.Cells[2, 4].HorizontalAlignment = XlHAlign.xlHAlignRight;

                        updateTimestamp = worksheet.Range["S5", "S5"].EntireColumn;
                        updateTimestamp.Delete();

    Monday, November 11, 2019 8:40 AM

Answers

  • Hi Marija,

    Thanks for posting here.

    For your question, you got an error that index is out of range.

    The dataGridView has one property: AllowUserToAddRows. By default, its value is true.

    At this point if you use datagridview.rows.count, you will get 2 (if there is only one row of data).

    But if you try to use rows[1], this error will appear.

    I think you should be misled by this property.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 12, 2019 5:54 AM