Reputation: 27
I'm working in ASP.NET and i want to import a Excel Sheet to my table in SQL Server. The problem is that one column of my Excel has a percentage type (example : 25%). Do you have a solution to stock this percentage value in my table in SQL Server ?
I post my code if you want :
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string path = string.Concat((Server.MapPath("~/temp/" + FileUpload1.FileName)));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
// Feuil1 est la première page du fichier Excel
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Feuil1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
OleDbcon.Open();
DbDataReader dr = cmd.ExecuteReader();
// configuration de la chaîne de connexion de SQL Server
string con_str = @"Data Source=.;Initial Catalog=projetDGCS;Integrated Security=True";
// On copie dans SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
// On mets le nom de la table de destination
bulkInsert.DestinationTableName = "reponse";
bulkInsert.WriteToServer(dr);
OleDbcon.Close();
// On a stocké le fichier Excel dans le dossier "temp" et on le supprime après
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.ForeColor = Color.Green;
Label1.Text = "Insertion réussie !";
}
Upvotes: 2
Views: 1160
Reputation: 62159
Excel has no percentage type for fields. It has a percentage FORMATTING for NUMERIC fields - the same your application can apply. 0.01 = 1% (at least my school did teach me that).
This is very objective - you can store a percentage value either in any numeric field. How you format it and whether you use for example a tinyint (1 = 1%) is totally up to you as programmer.
Upvotes: 2