How to export c# dataGridView to Ms Excel in few seconds.

Posted on 2018-10-12 10:06:48

Download Source Code / File

This is the easiest way to export c# dataGridView to Ms Excel. It's only few seconds. only one thing you have to do; copy and paste below code and add ms excel reference. and remember; you must installed the ms Excel before.

 // creating a excel object. 
            microsoft.office.interop.excel._application excel = new microsoft.office.interop.excel.application();
            microsoft.office.interop.excel._workbook workbook = excel.workbooks.add(type.missing);
            microsoft.office.interop.excel._worksheet worksheet = null;

            try
            {

                worksheet = workbook.activesheet;

                worksheet.name = "report name (worksheet name)";

                int cellrowindex = 1;
                int cellcolumnindex = 1;

                //loop through each row and read value from each column. 
                for (int i = 0; i <= datagridview1.rows.count; i++)
                {
                    for (int j = 0; j < datagridview1.columns.count; j++)
                    {
                        // excel index starts from 1,1. as first row would have the column headers, adding a condition check. 
                        if (cellrowindex == 1)
                        {
                            worksheet.cells[cellrowindex, cellcolumnindex] = datagridview1.columns[j].headertext;

                        }
                        else
                        {
                            worksheet.cells[cellrowindex, cellcolumnindex] = datagridview1.rows[i - 1].cells[j].value.tostring();
                        }
                        cellcolumnindex++;
                    }
                    cellcolumnindex = 1;
                    cellrowindex++;
                }

                //getting the location and file name of the excel to save from user. 
                savefiledialog savedialog = new savefiledialog();
                savedialog.filter = "excel files (*.xlsx)|*.xlsx|all files (*.*)|*.*";
                savedialog.filterindex = 2;
                savedialog.filename = "report_1"; //put file name here

                if (savedialog.showdialog() == system.windows.forms.dialogresult.ok)
                {
                    workbook.saveas(savedialog.filename);
                    messagebox.show("export successful");
                }
            }
            catch (system.exception ex)
            {
                messagebox.show(ex.message);
            }
            finally
            {
                excel.quit();
                workbook = null;
                excel = null;
            }