HOW WILL DATA EXPORT TO EXCEL IN DATAGRIDVIEW

Kenny Tangnde

Total Post:68

Points:476
Posted by  Kenny Tangnde
C# 
C#
 6209  View(s)
Ratings:
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.
  1. Chris Anderson

    Post:65

    Points:455
    Re: how will data export to excel in datagridview

    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.


  1. Kenny Tangnde

    Post:68

    Points:476
    Re: how will data export to excel in datagridview

    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?
     
            {
                                    
      }

  1. Chris Anderson

    Post:65

    Points:455
    Re: how will data export to excel in datagridview

    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
  1. Kenny Tangnde

    Post:68

    Points:476
    Re: how will data export to excel in datagridview

    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
  1. Awadhendra Tiwari

    Post:126

    Points:882
    Re: how will data export to excel in datagridview

    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.

  1. Carl Pieterson

    Post:14

    Points:98
    Re: how will data export to excel in datagridview

    Thanks Rohit. It helps me a lot.

  1. Uttam Misra

    Post:126

    Points:906
    Re: how will data export to excel in datagridview

    use namespace:
    using System.Data.SqlClient;

  1. John Smith

    Post:103

    Points:727
    Re: how will data export to excel in datagridview

    You got it aken :)

  1. Kenny Tangnde

    Post:68

    Points:476
    Re: how will data export to excel in datagridview

    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.

     

  1. Alex R

    Post:40

    Points:280
    Re: how will data export to excel in datagridview

    Thanks Awadhendra for the links. It helped me.

  1. Kenny Tangnde

    Post:68

    Points:476
    Re: how will data export to excel in datagridview

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

     

    Thanks.

  1. Amit Singh

    Post:103

    Points:721
    Re: how will data export to excel in datagridview

    Thanks Rohit this code work for me.

  1. John Smith

    Post:103

    Points:727
    Re: how will data export to excel in datagridview

    Yes really good one...

  1. James Smith

    Post:48

    Points:336
    Re: how will data export to excel in datagridview

    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,

  1. Jenry Hock

    Post:18

    Points:126
    Re: how will data export to excel in datagridview

    Hi Aken..

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

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

    Thanks,

  1. Ely Sanders

    Post:3

    Points:21
    Re: how will data export to excel in datagridview

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!