How to read an excel file and do the mapping

Question

How to read an excel file and do the mapping

Navigation

#1 by (2 votes)

2

I would like to know how I can read an excel file and then do the mapping to a datagridview.

Code I tried:

var fileName = @"C:\Users\HP8200\Desktop\test.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
  using (var conn = new OleDbConnection(connectionString)) {
    conn.Open();
    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "PARAC1" });
    using (var cmd = conn.CreateCommand()) {
      cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["PARAC1"].ToString() + "] ";
      var adapter = new OleDbDataAdapter(cmd);
      var ds = new DataSet();
      adapter.Fill(ds);

c# excel datagridview

asked by anonymous 25.09.2017 / 11:22

1 answer

Excel rounding value 7,256E + 18 Why even with margin 0 and padding 0 is there still space between the elements?

score 2 · Accepted Answer

    private void preencherDataGridView()
    {

        var fileName = @"C:\Desenv\Pasta1.xlsx";
        var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;

        var conexao = new System.Data.OleDb.OleDbConnection(connectionString);

        var sql = "SELECT * FROM [Plan1$]";

        var cmm = new System.Data.OleDb.OleDbCommand(sql, conexao);
        var dt = new System.Data.DataTable();

        conexao.Open();

        System.Data.OleDb.OleDbDataReader dr = cmm.ExecuteReader();
        dt.Load(dr);

        conexao.Close();

        dataGridView1.DataSource = dt;
    }