private DataTable GetTabelaExcel(string arquivoExcel)
{
arquivoExcel = txtArquivoExcel.Text;
DataTable dt = new DataTable();
try
{
//pega a extensão do arquivo
string Ext = Path.GetExtension(arquivoExcel);
string connectionString = "";
//verifica a versão do Excel pela extensão
if (Ext == ".xls")
{ //para o Excel 97-03
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + arquivoExcel + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
else if (Ext == ".xlsx")
{ //para o Excel 07 e superior
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + arquivoExcel + "; Extended Properties = 'Excel 8.0;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
cmd.Connection = conn;
conn.Open();
DataTable dtSchema;
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string nomePlanilha = dtSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
//le todos os dados da planilha para o Data Table
conn.Open();
cmd.CommandText = "SELECT `RA ALUNO`, `ALUNO NOME`,`NASCIMENTO`,`IDADE DO ALUNO`,`SEXO`,`GRAU_INSTRUCAO`,`RUA`,`NUMERO`,`COMPLEMENTO`,`BAIRRO`,`ESTADO`,`CIDADE`,`CEP`,`TELEFONE 1`,`IDENTIDADE`,`CPF`,`E-MAIL`,`CARTEIRA TRABALHO`,`NOME DO PAI`,`TELEFONE DO PAI`,`NOME MÃE`,`TELEFONE MÃE`,`NOME CURSO`,`CODIGO TURMA`,`STATUS`,`TELEFONE ALUNO 2` from [" + nomePlanilha + "] WHERE (`RA ALUNO` <> '')";
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dt);
conn.Close();
string comando = "INSERT INTO Aluno (raAluno,nomeAluno, dataNas,idade,sexo,grau_instrucao,rua,numero,complemento,bairro,estado,cidade,cep,telefone1,identidade,cpf,email,carteira_de_trabalho,nomePai,telefonePai,nomeMae,telefoneMae,nomeCurso,codTurma,statusAluno,telefoneAluno2) VALUES ";
for (int i = 0; i < dt.Rows.Count; i++) //Linha
{
comando += "(";
for (int j = 0; j < 26; j++) //Coluna
{
string dado = dt.Rows[i][j].ToString(); //Valor
comando += (j < 25) ? "'" + dado + "'," : "'" + dado + "'";
}
comando += (i < (dt.Rows.Count - 1)) ? ")," : ");";
}
ClassConexaoBd conexaoBd = new ClassConexaoBd();
conexaoBd.Conectar();
conexaoBd.ExecutarComandosSql(comando);
Console.WriteLine(dataAdapter.ToString());
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
static void Main()
{
OleDbConnection connection = new OleDbConnection("Provider=sqloledb;Data Source=(local);Initial Catalog=Northwind;"
+ "Integrated Security=SSPI");
OleDbDataAdapter da = CreateCustomerAdapter(connection);
Console.WriteLine(da.ToString());
connection.Dispose();
Console.ReadLine();
}
private bool readDB(string sqlcmd)
{
bool op_result = false;
using (OleDbDataAdapter adapter = new OleDbDataAdapter(sqlcmd, conn))
{
try
{
Console.WriteLine(adapter.ToString());
Console.WriteLine(adapter.GetFillParameters());
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
return(op_result);
}
public void run()
{
Exception exp = null;
OleDbDataAdapter oleDBda = new OleDbDataAdapter();
try
{
BeginCase("ToString");
Compare(oleDBda.ToString(), typeof(OleDbDataAdapter).ToString());
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
public Data()
{
string fname = MyVariables.filePath;
string sheet = MyVariables.sheetName;
// COMMENTED OUT SO THAT CODE WILL COMPILE, WORKING ON THIS SECTION
string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fname +
";Extended Properties='Excel 8.0;HDR=YES;';";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand($"select * from [{sheet}$]", con);
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.ItemsSource = sda.ToString();
}