-
June 13th, 2013, 06:15 AM
#1
from Excel to DataGreedView in C#
Good morning
I have an excel file from wich i want to extract the first or more column to a DataGreedView "as is".
File: c:\temp.xlsx
Sheet: "Sheet1"
Column: A
When i press the buton nothing happens and data is not imported.
Any idea Why?
adapter.Fill(dataset);
dataGridView1.DataSource = dataset;
The sourcecode i already have:
Code:
// Abre janela para selecionar ficheiro a importar.
OpenFileDialog ofImport = new OpenFileDialog();
// Define propriedades para a janela de selecionar ficheiro.
ofImport.Title = "Select file";
ofImport.InitialDirectory = @"c:\";
// ofImport.FileName = "temp.xlsx";
ofImport.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
ofImport.FilterIndex = 1;
ofImport.RestoreDirectory = true;
if (ofImport.ShowDialog() == DialogResult.OK)
{
// Define Caminho completo para ficheiro em STRING.
string path = System.IO.Path.GetFullPath(ofImport.FileName);
// Define pedido a fazer em forma de STRING.
string query = "SELECT * FROM [Sheet1$]";
// Define ligação como nova ligação OleDB.
OleDbConnection conn = new OleDbConnection();
// Define linha de comunicação com todos os dados recuperados até aqui.
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofImport.FileName + ";Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
// Define adaptador para pedido "query" (tipo string) ao adaptador que comunica com o ficheiro excel através da ligação CONN definida anteriormente.
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
DataSet dataSet = new DataSet();
adapter.Fill(Sheet1, A:A);
dataGridView1.DataSource = Sheet1;
}
else
{
ofImport.Dispose();
}
-
June 13th, 2013, 09:15 AM
#2
Re: from Excel to DataGreedView in C#
Hi
you can do the following:
public static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
{
return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
Filename.Replace("'", "''"),
FirstRowContainsHeaders ? "Yes" : "No");
}
inside the button put the below.
string connStr = BuildExcelConnectionString(fileName, true);
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$]", conn))
{
using (OleDbDataReader dr = cmd.ExecuteReader())
{
if (dt != null)
dt.Dispose();
dt = new DataTable();
dt.Load(dr);
}
}
}
this should work.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|