Well, I'm writing a web application using asp.net C#, that must do the upload of an excel file via web into my database (sql server) here's the behind code:
protected void Unnamed1_Click(object sender, EventArgs e)
{
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUploadBtn.PostedFile.FileName);
FileUploadBtn.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUploadBtn.PostedFile.FileName);
switch (extension)
{
case ".xls":
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx":
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string folha1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcel = new DataTable();
dtExcel.Columns.AddRange(new DataColumn[3]
{
new DataColumn ("Nome", typeof(string)),
new DataColumn ("Cidade",typeof(string)),
new DataColumn ("Idade",typeof(int))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + folha1 + "]", excel_con))
{
oda.Fill(dtExcel);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["costumizadoConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Teste";
sqlBulkCopy.ColumnMappings.Add("Nome", "Nome");
sqlBulkCopy.ColumnMappings.Add("Cidade", "Cidade");
sqlBulkCopy.ColumnMappings.Add("Idade", "Idade");
con.Open();
sqlBulkCopy.WriteToServer(dtExcel);
con.Close();
}
}
}
}
}
} '
heres web.config:
<configuration>
<add name="costumizadoConnectionString" connectionString="Provider=SQLOLEDB; Data Source='PEDRO-PC\SQLEXPRESS;Initial Catalog=costumizado;Integrated Security=True';" providerName="System.Data.Client" />
<add name="Excel03ConString" connectionString= "provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties =Excel 8.0;HDR=n " />
<add name="Excel07+ConString" connectionString= "provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties =Excel 8.0;HDR=YES " />
I think my code Have a lot of erros but I can't find them or search them... Any help is nice :)
Aucun commentaire:
Enregistrer un commentaire