Hej jeg har gjort så man kan uploade en excel fil til serveren og så ved klik på en anden knap lægger dataen sig ned i en Ms sql database.
Min kode for upload ser sådan ud:
protected void Button2_Click(object sender, EventArgs e)
{
string sSavePath;
sSavePath = "../admin/database.xls";
if (File1.PostedFile != null)
{
// Check file size (mustn’t be 0)
HttpPostedFile myFile = File1.PostedFile;
int nFileLen = myFile.ContentLength;
if (nFileLen == 0)
{
lblOutput_excel.Text = "No file was uploaded.";
return;
}
if (System.IO.Path.GetExtension(myFile.FileName).ToLower() != ".xls")
{
lblOutput.Text = "The file must have an extension of xls";
return;
}
byte[] myData = new Byte[nFileLen];
myFile.InputStream.Read(myData, 0, nFileLen);
System.IO.FileStream newFile
= new System.IO.FileStream(Server.MapPath(sSavePath),
System.IO.FileMode.Create);
newFile.Write(myData, 0, myData.Length);
newFile.Close();
}
og min kode for at indlæse dataen i ms sql ser sådan ud:
protected void Button1_Click(object sender, EventArgs e)
{
string excelConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Server.MapPath("database.xls"));
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
string sqlConnectionString = "Data Source=212.97.133.33;Initial Catalog=kischi2_database;UID=kischi2_radio;PWD=kischi;";
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "PROGRAM";
bulkCopy.WriteToServer(dr);
connection.Close();
}
Men så ville jeg sætte dem sammen så ved klik på den knap som uploader filen til serveren, skal den også indlæse dataen til ms sql serveren.
Det skrev jeg sådan her:
protected void Button2_Click(object sender, EventArgs e)
{
string sSavePath;
sSavePath = "../admin/database.xls";
if (File1.PostedFile != null)
{
// Check file size (mustn’t be 0)
HttpPostedFile myFile = File1.PostedFile;
int nFileLen = myFile.ContentLength;
if (nFileLen == 0)
{
lblOutput_excel.Text = "No file was uploaded.";
return;
}
if (System.IO.Path.GetExtension(myFile.FileName).ToLower() != ".xls")
{
lblOutput.Text = "The file must have an extension of xls";
return;
}
byte[] myData = new Byte[nFileLen];
myFile.InputStream.Read(myData, 0, nFileLen);
System.IO.FileStream newFile
= new System.IO.FileStream(Server.MapPath(sSavePath),
System.IO.FileMode.Create);
newFile.Write(myData, 0, myData.Length);
newFile.Close();
}
string excelConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Server.MapPath("database.xls"));
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
string sqlConnectionString = "Data Source=212.97.133.33;Initial Catalog=kischi2_database;UID=kischi2_radio;PWD=kischi;";
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "PROGRAM";
bulkCopy.WriteToServer(dr);
connection.Close();
}
Men det lægger ikke dataen i databasen, men den kommer heller ikke med nogle fejl.
Er der nogle som kan hjælpe mig?
Mvh. Adam