Home > DeveloperSection > Forums > how will data export to excel in datagridview
Kenny Tangnde

Total Post:68

Points:476
Posted on    September-21-2011 11:40 PM

 C# C# 
Ratings:


 17 Reply(s)
 4991  View(s)
Rate this:
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.


Chris Anderson
Chris Anderson

Total Post:65

Points:455
Posted on    September-22-2011 1:14 AM

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.



Kenny Tangnde

Total Post:68

Points:476
Posted on    September-22-2011 4:04 AM

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

Total Post:65

Points:455
Posted on    September-22-2011 5:09 AM

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.


Modified On Sep-22-2011 05:14:10 AM

Kenny Tangnde

Total Post:68

Points:476
Posted on    September-22-2011 10:03 AM

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.


Modified On Sep-22-2011 10:27:44 AM

Awadhendra Tiwari

Total Post:126

Points:882
Posted on    September-23-2011 12:21 AM

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.

Carl Pieterson
Carl Pieterson

Total Post:14

Points:98
Posted on    September-23-2011 3:31 AM

Thanks Rohit. It helps me a lot.

Uttam Misra

Total Post:124

Points:888
Posted on    September-23-2011 4:33 AM

use namespace:
using System.Data.SqlClient;

John Smith

Total Post:101

Points:711
Posted on    September-23-2011 4:37 AM

You got it aken :)

Kenny Tangnde

Total Post:68

Points:476
Posted on    September-23-2011 11:35 AM

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.

 


Awadhendra Tiwari

Total Post:126

Points:882
Posted on    September-24-2011 12:09 AM


Alex R
Alex R

Total Post:40

Points:280
Posted on    September-24-2011 5:50 AM

Thanks Awadhendra for the links. It helped me.

Kenny Tangnde

Total Post:68

Points:476
Posted on    September-25-2011 6:53 PM

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

 

Thanks.


Amit Singh

Total Post:103

Points:721
Posted on    September-27-2011 2:50 AM

Thanks Rohit this code work for me.

John Smith

Total Post:101

Points:711
Posted on    September-27-2011 10:27 AM

Yes really good one...

James Smith

Total Post:48

Points:336
Posted on    October-01-2011 10:06 AM

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,

Jenry Hock
Jenry Hock

Total Post:18

Points:126
Posted on    October-03-2011 5:46 AM

Hi Aken..

You can use following link to export data from excel to data-grid view,

http://support.microsoft.com/kb/321686

Thanks,

Ely Sanders

Total Post:3

Points:21
Posted on    August-22-2013 12:53 AM

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.


Modified On Aug-22-2013 12:54:24 AM

Don't want to miss updates? Please click the below button!

Follow MindStick