Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / how will data export to excel in datagridview
Ask Question

how will data export to excel in datagridview

Kenny Tangnde 9452 21 Sep 2011
Hi all,
   I have two question,First:Is how will data export to excel in datagridview.Second:
Is how will data import  to database in excel.
Thanks in advance.
c# c#
Updated 22 Aug 2013
Kenny Tangnde
Kenny Tangnde

Other

Message

17 Answers

Ely Sanders
Ely Sanders 22 Aug 2013
Report Abuse

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.

Anonymous User
Anonymous User 03 Oct 2011
Report Abuse
Hi Aken..
You can use following link to export data from excel to data-grid view,
http://support.microsoft.com/kb/321686
Thanks,
James Smith
James Smith 01 Oct 2011
Report Abuse
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,
John Smith
John Smith 27 Sep 2011
Report Abuse
Yes really good one...
Amit Singh
Amit Singh 27 Sep 2011
Report Abuse
Thanks Rohit this code work for me.
Kenny Tangnde
Kenny Tangnde 25 Sep 2011
Report Abuse

Hi all,
thanks Awadhendra Tiwari,It helps me a lot. 

Thanks.

Anonymous User
Anonymous User 24 Sep 2011
Report Abuse
Thanks Awadhendra for the links. It helped me.
Anonymous User
Anonymous User 24 Sep 2011
Report Abuse
Hi Aken H,
You can take a look at following link for learning linq
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.
Kenny Tangnde
Kenny Tangnde 23 Sep 2011
Report Abuse

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.

 

John Smith
John Smith 23 Sep 2011
Report Abuse
You got it aken :)
Uttam Misra
Uttam Misra 23 Sep 2011
Report Abuse
use namespace:
using System.Data.SqlClient;
Anonymous User
Anonymous User 23 Sep 2011
Report Abuse
Thanks Rohit. It helps me a lot.
Anonymous User
Anonymous User 23 Sep 2011
Report Abuse
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.
Kenny Tangnde
Kenny Tangnde 22 Sep 2011
Report Abuse

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.

Chris Anderson
Chris Anderson 22 Sep 2011
Report Abuse

hi,

If you want that only checked rows will be exported then simply manipulate the above code:

List<DataGridViewRow> rows_with_checked_column = new List<DataGridViewRow>();
            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.

Kenny Tangnde
Kenny Tangnde 22 Sep 2011
Report Abuse

hi Rohit Kesharwani,

private void btnExportToExcel_Click(object sender, EventArgs e)
   
 {
       
    List<DataGridViewRow> rows_with_checked_column=new List<DataGridViewRow>();
                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?
 
        {
                                 
  }
Chris Anderson
Chris Anderson 22 Sep 2011
Report Abuse
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.
advertising