--- title: "how will data export to excel in datagridview" description: "how will data export to excel in datagridview" author: "Kenny Tangnde" published: 2011-09-21 updated: 2013-08-22 canonical: https://www.mindstick.com/forum/313/how-will-data-export-to-excel-in-datagridview category: "c#" tags: ["c#"] reading_time: 4 minutes --- # how will data export to excel in datagridview Hi all,\ I have two [question](https://www.mindstick.com/blog/23175/how-to-solve-neet-question-paper-in-less-time),First:Is how will [data](https://www.mindstick.com/articles/13050/salesforce-aiming-to-dominate-predictive-analytics-with-data-science) [export](https://www.mindstick.com/articles/12966/a-guide-to-export-pallets) to [excel](https://www.mindstick.com/articles/12937/how-to-import-excel-data-in-sql-server-2014) in datagridview.Second:\ Is how will data [import](https://www.mindstick.com/blog/294/how-to-import-or-export-sql-server-table-data-in-ms-excel-sheet-using-c-sharp-code) to [database](https://www.mindstick.com/articles/12226/use-of-database-in-sencha-extjs-and-insert-record-from-user-form-using-ajax) in excel.\ Thanks in advance. ## Replies ### Reply by Ely Sanders Hi, here is an alternative and for me a more flexible and simplier way you can try to export DataGridView to Excel file in C#:\ \ ``` private void btnExportToExcel_Click(object sender, EventArgs e){     // Create DataTable for Excel file.     var dataTable = ((DataTable)dataGridView1.DataSource).Copy();    // Remove rows that do not meat the condition.     for (int i = 0; i < dataTable.Rows.Count; i++)        if (Convert.ToBoolean(dataTable.Rows[i][0]) == false)            dataTable.Rows.Remove(dataTable.Rows[i]);    // Remove unwanted column.     dataTable.Columns.Remove(dataTable.Columns[0]);    // Create new excel file.     var excelFile = new ExcelFile();    // Create new excel worksheet.     var excelSheet = excelFile.Worksheets.Add(dataTable.TableName);    // Insert data from dataTable to the worksheet starting at cell "A1".     excelSheet.InsertDataTable(dataTable, new InsertDataTableOptions("A1") { ColumnHeaders = true });    // Save as XLSX file.     excelFile.Save("New File.xlsx");} ``` \ Also here is how you can import Excel file to a DataTable in C#:\ \ ``` private void btnImportExcelToDB_Click(object sender, EventArgs e){    var dataSet = new DataSet();    // Load excel file.     var excelFile = ExcelFile.Load("New File.xlsx");    var excelWorksheet = excelFile.Worksheets.ActiveWorksheet;    // Extract the data from the worksheet to newly created DataTable starting at      // first row and first column until the first empty row appears.     var dataTable = excelWorksheet.CreateDataTable(new CreateDataTableOptions()    {        StartRow = 0,        StartColumn = 0,        ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow    });    dataTable.TableName = excelWorksheet.Name;    // Add DataTable to DataSet.     dataSet.Tables.Add(dataTable);    // TODO import dataSet to Database } ``` I used this C# Excel component. ### Reply by Anonymous User Hi Aken..\ \ You can use following link to export data from excel to data-grid view,\ \ http://support.microsoft.com/kb/321686\ \ Thanks, ### Reply by James Smith Hi all..\ This is really good.\ Can any one give me some example of datagridview demo in c#. So i can make a complete program on it.\ \ Thanks, ### Reply by John Smith Yes really good one... ### Reply by Amit Singh Thanks Rohit this code work for me.\ ### Reply by Kenny Tangnde Hi all,\ thanks Awadhendra Tiwari,It helps me a lot. Thanks.\ ### Reply by Anonymous User Thanks Awadhendra for the links. It helped me. ### Reply by Anonymous User Hi Aken H,\ You can take a look at following link for learning linq\ \ [http://msdn.microsoft.com/en-us/vcsharp/aa336766](http://msdn.microsoft.com/en-us/vcsharp/aa336766)\ http://geekswithblogs.net/elroydsilva/archive/2008/11/03/learning-linq---an-overview.aspx\ \ http://www.linqlearning.com/sp/16295-LINQ-to-SQL-Tutorials-Learning-Resources/\ \ Thanks. ### Reply by Kenny Tangnde hi Awadhendra Tiwari,Rohit Kesharwani, Thank you for your help,Thanks Rohit Kesharwani . he helps me a lot. again ask a question: you can provide LINQ learning resources to me,and LINQ in project the integrated application. Grateful.\ ### Reply by John Smith You got it aken :) ### Reply by Uttam Misra use namespace:\ using System.Data.SqlClient; ### Reply by Anonymous User Thanks Rohit. It helps me a lot. ### Reply by Anonymous User Hi aken H..\ You can make following modification on above code\ \ using System.Data.SqlClient; //import this namespace at top\ \ then replace OleDbCommand by SqlCommand class.\ \ Then above code also work for you.\ \ Thanks.\ ### Reply by Kenny Tangnde hi Rohit Kesharwani, i not used using System.Data.OleDb; i want used using System.Data.SqlClient; Thank you always help me, I hereby express my sincere thank you. ### Reply by Chris Anderson hi, If you want that only checked rows will be exported then simply manipulate the above code: ``` List rows_with_checked_column = new List();            foreach (DataGridViewRow rows in dataGridView1.Rows)            {                if (Convert.ToBoolean(rows.Cells[0].Value) == true)                {                    oledbCon.Open();                    OleDbCommand cmd = new OleDbCommand("insert into [" + row["TABLE_NAME"].ToString() + "] values ('" + rows.Cells[1].Value.ToString() + "','" + rows.Cells[2].Value.ToString() + "')", oledbCon);                    cmd.ExecuteNonQuery();                    oledbCon.Close();                }            } ``` Thanks. ### Reply by Kenny Tangnde hi Rohit Kesharwani, ``` private void btnExportToExcel_Click(object sender, EventArgs e)    {           List rows_with_checked_column=new List();                foreach (DataGridViewRow row in dgv.Rows)          {              if (Convert.ToBoolean(row.Cells[0].Value) == true)            //if value is true then start export to excel.           //how to write code?         {                                   } ``` ### Reply by Chris Anderson hello aken,\ Here are the solution of your both the problem:\ \ **Export Data from Excel in dataGridView:**\ \ **Write the below code on the button click or on form load function:**\ \ ``` using System.Data.OleDb;OleDbConnection oledbCon = null; DataTable dt = null;string csExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\rohit\\File.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;\" ";oledbCon = new OleDbConnection(csExcel); oledbCon.Open(); dt = oledbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);oledbCon.Close();String[] excelSheets = new String[dt.Rows.Count];DataRow row = dt.Rows[0];oledbCon.Open(); OleDbDataAdapter oledbAdapter = new OleDbDataAdapter("select * from [" + row["TABLE_NAME"].ToString() + "]", oledbCon);oledbCon.Close();DataSet excelDataSet = new DataSet();oledbAdapter.Fill(excelDataSet, "student");DataTable excelTable = excelDataSet.Tables["student"];dataGridView1.DataSource = excelTable; ``` \ **Import Data from dataGridView in Excel:**\ \ ``` using System.Data.OleDb;OleDbConnection oledbCon = null;DataTable dt = null;string csExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\rohit\\File.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;\" ";oledbCon = new OleDbConnection(csExcel);oledbCon.Open();dt = oledbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);oledbCon.Close();String[] excelSheets = new String[dt.Rows.Count];DataRow row = dt.Rows[0]; oledbCon.Open(); for (int i = 0; i < dataGridView1.Rows.Count-1; i++){  OleDbCommand cmd = new OleDbCommand("insert into [" + row["TABLE_NAME"].ToString() + "] values ('" +           dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "')",     oledbCon); cmd.ExecuteNonQuery(); }oledbCon.Close(); ``` \ \ Happy Coding.\ \ \ --- Original Source: https://www.mindstick.com/forum/313/how-will-data-export-to-excel-in-datagridview Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.