Upload/DownLoad from Excel

[Guest]

HI,

I am doing an aspx application and need to let my users be able to edit the
information in my database by the page. My first Idea was to let them export
the information to an excel file at local machine where they can edit it och
then upload the file by the page again to save down their changes down to the
database.
Now, this is the code I am using to Upload from sql to excel :
HttpResponse response = HttpContext.Current.Response;

// first let's clean up the response.object
response.Clear();
response.Charset = "";
string filename = "myExcel.xls";
// set the response mime type for excel
DataSet ds = RulesFacade.GetRuleList();
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" +
filename + "\"");

// create a string writer
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
response.Write(sw.ToString());
response.End();
}
}
this works perfectly and I have an excel file on my desktop. The things is
that the files looks a little bit strange, with letters like #¤%& etc, not
everywhere but in many places.

And when I try to upload the file (the file I just uploaded to my local
machine) whith this code
public void UploadFromExcell(string filePath,strFileName)
{
try
{
/*strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source="+Server.MapPath(".\\" + StrFileName)+";" +
"Extended Properties=Excel 8.0;";*/
File1.PostedFile.SaveAs(Server.MapPath(".\\" + StrFileName));
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source="+filePath+";" +
"Extended Properties=Excel 8.0;";

//System.Diagnostics.Debug.WriteLine(Server.MapPath(".\\" + StrFileName));

//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM
[TrandaxRegler$]",strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet, "ExcelInfo");
foreach(DataRow r in myDataSet.Tables[0].Rows)
{
System.Diagnostics.Debug.WriteLine(r[0]);
System.Diagnostics.Debug.WriteLine(r[1]);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}

}
I get an exception that the file is not correctly configured. is it cause it
only has one sheet?? this code works perfectly with another excel file I have
on my machine, but the one that works has more than one sheet...
Any idea how to solve my problem?? download to excel from sql, do changes in
excel file and upload the save file and save it to database.

 

[Patrice]

1) use BinaryWrite

2) Always mention the exact message you have
Don't catch exception if you don't do anything else than ASP.NET do.
Before just dump the connection string to see what is its final value and if
it looks correct

I'm doing thse kind of things for "mass updates" by the application
administrators. If this is more directed to end useers you could of course
crzeate a web form including posisbkly all needed checks etc...

--
Patrice

HI,

I am doing an aspx application and need to let my users be able to edit
the
information in my database by the page. My first Idea was to let them
export
the information to an excel file at local machine where they can edit it
och
then upload the file by the page again to save down their changes down to
the
database.
Now, this is the code I am using to Upload from sql to excel :
HttpResponse response = HttpContext.Current.Response;

// first let's clean up the response.object
response.Clear();
response.Charset = "";
string filename = "myExcel.xls";
// set the response mime type for excel
DataSet ds = RulesFacade.GetRuleList();
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" +
filename + "\"");

// create a string writer
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
response.Write(sw.ToString());
response.End();
}
}
this works perfectly and I have an excel file on my desktop. The things is
that the files looks a little bit strange, with letters like #¤%& etc, not
everywhere but in many places.

And when I try to upload the file (the file I just uploaded to my local
machine) whith this code
public void UploadFromExcell(string filePath,strFileName)
{
try
{
/*strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source="+Server.MapPath(".\\" + StrFileName)+";" +
"Extended Properties=Excel 8.0;";*/
File1.PostedFile.SaveAs(Server.MapPath(".\\" + StrFileName));
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source="+filePath+";" +
"Extended Properties=Excel 8.0;";

//System.Diagnostics.Debug.WriteLine(Server.MapPath(".\\" + StrFileName));

//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM
[TrandaxRegler$]",strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet, "ExcelInfo");
foreach(DataRow r in myDataSet.Tables[0].Rows)
{
System.Diagnostics.Debug.WriteLine(r[0]);
System.Diagnostics.Debug.WriteLine(r[1]);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}

}
I get an exception that the file is not correctly configured. is it cause
it
only has one sheet?? this code works perfectly with another excel file I
have
on my machine, but the one that works has more than one sheet...
Any idea how to solve my problem?? download to excel from sql, do changes
in
excel file and upload the save file and save it to database.