locked
Loading Excel data into ASP.NET 2.0 page (again) RRS feed

  • Question

  • User-1191281687 posted

    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 data it?

    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...

    Wai

    Monday, July 27, 2009 5:35 AM

Answers

  • User187056398 posted

     Have you tried something like this:

            // using System.Data.OleDb
            OleDbConnection ExcelConection = null;
            OleDbCommand ExcelCommand = null;
            OleDbDataReader ExcelReader = null;
            OleDbConnectionStringBuilder OleStringBuilder = null;
    
            try
            {
                OleStringBuilder =
                    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$]";
    
                ExcelConection.Open();
                ExcelReader = ExcelCommand.ExecuteReader();
    
                GridView1.DataSource = ExcelReader;
                GridView1.DataBind();
            }
            catch (Exception Args)
            {
                LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
            }
            finally
            {
                if (ExcelCommand != null)
                    ExcelCommand.Dispose();
                if (ExcelReader != null)
                    ExcelReader.Dispose();
                if (ExcelConection != null)
                    ExcelConection.Dispose();
            }
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 27, 2009 9:50 AM
  • User187056398 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2009 9:30 AM

All replies

  • User187056398 posted

     Have you tried something like this:

            // using System.Data.OleDb
            OleDbConnection ExcelConection = null;
            OleDbCommand ExcelCommand = null;
            OleDbDataReader ExcelReader = null;
            OleDbConnectionStringBuilder OleStringBuilder = null;
    
            try
            {
                OleStringBuilder =
                    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$]";
    
                ExcelConection.Open();
                ExcelReader = ExcelCommand.ExecuteReader();
    
                GridView1.DataSource = ExcelReader;
                GridView1.DataBind();
            }
            catch (Exception Args)
            {
                LabelErrorMsg.Text = "Could not open Excel file: " + Args.Message;
            }
            finally
            {
                if (ExcelCommand != null)
                    ExcelCommand.Dispose();
                if (ExcelReader != null)
                    ExcelReader.Dispose();
                if (ExcelConection != null)
                    ExcelConection.Dispose();
            }
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 27, 2009 9:50 AM
  • User-1191281687 posted

    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.....

    Monday, July 27, 2009 8:28 PM
  • User187056398 posted

    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.

    Monday, July 27, 2009 9:08 PM
  • User-1191281687 posted

    Sorry of my ignorance.. even if I use a file upload control to allow the user to upload a file?

    Monday, July 27, 2009 11:41 PM
  • User187056398 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2009 9:30 AM
  • User-1191281687 posted

    Thanks...I think I am convinced now that's the direction I am heading to...

    Tuesday, July 28, 2009 7:40 PM