Last post Jul 28, 2009 07:40 PM by waimanlam
Jul 27, 2009 05:35 AM|waimanlam|LINK
I have an issue here which is driving me nuts....
My Web app allows an user to upload an Excel (2000) file (but NOT saved anywhere on the Web server), and my Web page supposes to read, extract the Excel data, and then save the extracted data onto a ORACLE db via stored procedures..sounds easy huh?
CSV won't work here because there is Chinese data in the Excel file and CSV output will just become "????"...
I used Jet to read the data as native Excel format and it worked beautiful in my local computer but then when it migrates to the remote staging area it won't work because of security!! (don't try to encourage me to tamper with the security issue, won't
work with my clients!)
I tried to use MemoryStream to achieve the result and I found that if the original Excel spreadsheet is saved as HTML format I can see all the data inside a huge HTML-office-page loaded into a string using MemoryStream/InputStream but then how can I extract
My apologies on sounding vague but I don't really know how to approach this problem sensibily...
Thanks for your help in advance. If possible pls supply some sample code...
asp.NET 2.0 excel office import
Jul 27, 2009 09:50 AM|SGWellens|LINK
Have you tried something like this:
// using System.Data.OleDb
OleDbConnection ExcelConection = null;
OleDbCommand ExcelCommand = null;
OleDbDataReader ExcelReader = null;
OleDbConnectionStringBuilder OleStringBuilder = null;
new OleDbConnectionStringBuilder(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
OleStringBuilder.DataSource = MapPath(@"~\App_Datav\MyExcelWorksheet.xls");
ExcelConection = new OleDbConnection();
ExcelConection.ConnectionString = OleStringBuilder.ConnectionString;
ExcelCommand = new OleDbCommand();
ExcelCommand.Connection = ExcelConection;
ExcelCommand.CommandText = "Select * From [Sheet1$]";
ExcelReader = ExcelCommand.ExecuteReader();
GridView1.DataSource = ExcelReader;
catch (Exception Args)
LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
if (ExcelCommand != null)
if (ExcelReader != null)
if (ExcelConection != null)
Jul 27, 2009 08:28 PM|waimanlam|LINK
Thanks for your quick reply....well...that's what I did basically...but your file is under the app_data so it will work beautifully... in my case I have no control on where the files could be on my client's PC (could be desktop, c:\documents...) so that's
my big issue.....
Jul 27, 2009 09:08 PM|SGWellens|LINK
in my case I have no control on where the files could be on my client's PC (could be desktop, c:\documents...) so that's my big issue.....
You will not be able to 'reach into' a client's machine and read an Excel file. The security violations would be tremendous. The file will have to be uploaded to the server.
Jul 27, 2009 11:41 PM|waimanlam|LINK
Sorry of my ignorance.. even if I use a file upload control to allow the user to upload a file?
Jul 28, 2009 09:30 AM|SGWellens|LINK
If you use the file upload control, the user will be able to browse anywhere on their machine to upload the file.
Then, YOU determine where to save the file on the server so you will know where it is.
Jul 28, 2009 07:40 PM|waimanlam|LINK
Thanks...I think I am convinced now that's the direction I am heading to...