how will data export to excel in datagridview

Total Post:68

Points:476
C# 
C#
 7013  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. 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.


      Modified On Mar-31-2018 12:08:08 AM
  1. 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?
     
            {
                                    
      }

      Modified On Mar-31-2018 12:05:36 AM
  1. 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 Mar-31-2018 12:04:25 AM
  1. 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. 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. Post:14

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

    Thanks Rohit. It helps me a lot.

  1. Post:127

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

    use namespace:
    using System.Data.SqlClient;

  1. Post:106

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

    You got it aken :)

  1. 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. Post:40

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

    Thanks Awadhendra for the links. It helped me.

  1. 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. Post:103

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

    Thanks Rohit this code work for me.

  1. Post:106

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

    Yes really good one...

  1. 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. 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. 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 Mar-31-2018 12:03:53 AM

Answer