Exporting datagridview data into Excel Worksheet

Question

Am working on a project and I need to export datagridview data into an excel sheet. My datagridview has 38 columns and about 120 rows(with spaces between the words) and the data is entered from the datagridview. When I run the app, I get this Exception, " Object reference not set to an instance of an object.". This is my code block:

private void exportbtn_Click(object sender, EventArgs e)

        if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            ExcelApp.Columns.AutoFit();

            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                   ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                MessageBox.Show("Data Exported Successfully");
            }
            ExcelApp.ActiveWorkbook.SaveAs("");
            ExcelApp.ActiveWorkbook.Saved = true;              
            ExcelApp.Visible = true;
            ExcelApp.Quit();
         
         }

And I populated the datagridview first column(Facillity) as:

        ArrayList row = new ArrayList();
        row.Add("Techiman Holy Family Hospital"); dataGridView1.Rows.Add(row.ToArray());
        row = new ArrayList();
        row.Add("Opoku Agyeman Hospital"); dataGridView1.Rows.Add(row.ToArray());
        row = new ArrayList();
        row.Add("Kintampo Municipal Hospital"); dataGridView1.Rows.Add(row.ToArray());

Also I don't want to choose new excel workbook anytime am exporting to excel. I want to save all sheets in the same workbook. Is there a way to edit the following code to achieve that:

        saveFileDialog1.InitialDirectory = ("C:");
        saveFileDialog1.Title = "Save as Excel File";
        saveFileDialog1.FileName = "";
        saveFileDialog1.Filter = "Excel Files(2003)|*.xls| Excel Files(2007)|*.xlsx";

Please any help? Thanks.

Please confirm that you have read, understood and applied the advice found in What is a null reference exception and how do I fix it? and then edit your question to show some understanding, what you've tried and why it didn't work out.. If you don't it's quite likely this question will end up closed as a duplicate of that one, because there's really only one cause for this type of exception and it's pretty easy to chase down, and usually a really basic error — Caius Jard
@emma aboagye, Is any update? Please check if my answer works for you. — Jack J Jun - MSFT

Jack J Jun - MSFT Jack J Jun - MSFT · Accepted Answer · 2021-03-23T02:44:50

First of all, I suggest that you can refer to the following code to add data to your datagridview.

 private void Form1_Load(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            table.Columns.Add("Hospital");
            table.Columns.Add("Name");
            table.Columns.Add("Age");
            table.Columns.Add("Address");
            table.Columns.Add("Id");
            table.Columns.Add("Sex");
            table.Rows.Add("Techiman Holy Family Hospital", "test11", "22","home","1001","Man");
            table.Rows.Add("Techiman Holy Family Hospital", "test2", "23", "home", "1001", "Man");
            table.Rows.Add("Opoku Agyeman Hospital", "test8", "22", "home", "1001", "Man");
            table.Rows.Add("Opoku Agyeman Hospital", "test6", "26", "home", "1001", "Man");
            table.Rows.Add("Kintampo Municipal Hospital", "test21", "22", "home", "1001", "Man");
            table.Rows.Add("Kintampo Municipal Hospita", "test12", "22", "home", "1001", "Man");
            dataGridView1.DataSource = table;
        }

Second, the key point to solve the problem is that we need to change dataGridView1.Rows.Count to dataGridView1.Rows.Count-1 because the datagirdview will generate one empty row automatically.

Finally, here is a code example you can refer to.

 private void button1_Click(object sender, EventArgs e)
        {
            saveFileDialog1.InitialDirectory = ("E:");
            saveFileDialog1.Title = "Save as Excel File";
            saveFileDialog1.FileName = "";
            saveFileDialog1.Filter = "Excel Files(2003)|*.xls| Excel Files(2007)|*.xlsx";

            if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                Excel.Application ExcelApp = new Excel.Application();
                ExcelApp.Application.Workbooks.Add(Type.Missing);
                ExcelApp.Columns.AutoFit();
                Excel.Worksheet sheet = ExcelApp.ActiveSheet;
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    sheet.Cells[1, i+1] = dataGridView1.Columns[i].HeaderText;
                }

                for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        sheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                    
                }
                MessageBox.Show("Data Exported Successfully");
                sheet.SaveAs(saveFileDialog1.FileName);
                ExcelApp.Visible = true;
                ExcelApp.Quit();
                
            }
        }

Exporting datagridview data into Excel Worksheet

1 Answers