forum

Home / DeveloperSection / Forums / how will data export to excel in datagridview

Ask Question

how will data export to excel in datagridview

Kenny Tangnde883421-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 on 22-Aug-2013
Kenny Tangnde

Kenny Tangnde

Follow

Can you answer this question?

Answer

17 Answers

Ely Sanders

Ely Sanders

22-Aug-2013

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
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
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
Yes really good one...
Amit Singh

Amit Singh

27-Sep-2011
Thanks Rohit this code work for me.
Kenny Tangnde

Kenny Tangnde

25-Sep-2011

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

 

Thanks.

Anonymous User

Anonymous User

24-Sep-2011
Thanks Awadhendra for the links. It helped me.
Anonymous User

Anonymous User

24-Sep-2011
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

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
You got it aken :)
Uttam Misra

Uttam Misra

23-Sep-2011
use namespace:
using System.Data.SqlClient;
Anonymous User

Anonymous User

23-Sep-2011
Thanks Rohit. It helps me a lot.
Anonymous User

Anonymous User

23-Sep-2011
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

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

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

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
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.

Liked By