Reading a spreadsheetl file from fileupload control into filestream

Question

I am trying to find the best way of selecting an excel spreadsheet (xls and xlsx) using a fileupload control and then parsing it with filestream object and then populating a dataset.

This is my code so far which does the job but how it differs is I am saving the excel spreadsheet to a folder in my solution then querying the data using Microsoft ACE OLEDB connection.

protected void btnUpload_Click(object sender, EventArgs e)
    {
        string connectingString = "";
        if (ctrlFileUpload.HasFile)
        {
            string fileName =
                Path.GetFileName(ctrlFileUpload.PostedFile.FileName);

            string fileExtension =
                Path.GetExtension(ctrlFileUpload.PostedFile.FileName);
                //Path.GetExtension(ctrlFileUpload.PostedFile.ContentType);

            string fileLocation =
                Server.MapPath("~/App_Data/" + fileName);
            ctrlFileUpload.SaveAs(fileLocation);

            // Check whether file extension is xls or xslx
            if (fileExtension == ".xls")
            {
                connectingString =
                    "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" +
                    fileLocation + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\"";
            }

            else if (fileExtension == ".xlsx")
            {
                connectingString =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    fileLocation + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\"";
            }

            // Create OleDb Connection and OleD Command
            using (OleDbConnection con = new OleDbConnection(connectingString))
            {
                try
                {
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                    //DataTable dtExcelRecords = new DataTable();
                    DataSet ds = new DataSet();
                    con.Open();
                    DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                    cmd.CommandText = "Select * FROM [" + getExcelSheetName + "]";
                    dAdapter.SelectCommand = cmd;
                    //dAdapter.Fill(dtExcelRecords);
                    dAdapter.Fill(ds);
                    //GridView1.DataSource = dtExcelRecords;
                    //GridView1.DataBind();
                }
                catch (Exception ex)
                {

                }
            }

        }
    }

So to summarise I want to read the data in a spreadsheet and then bind it to a dataset but without saving the file to a physical path.

Is there a cleaner way of writing this code that I am missing. Thanks!!

Is Chandra one of your colleagues? http://stackoverflow.com/q/12420310/284240 — Tim Schmelter, Sep 14 '12 at 11:09
Lol no he is not but I will take a look at that link. Thanks! — nick gowdy, Sep 14 '12 at 11:12
Are there not any solutions which can be used without having to download a custom class library? — nick gowdy, Sep 14 '12 at 15:03

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

I think it not possible to get data without save file to your server

If you can't save uploded file how can you give Data Source to your oleDbConnection string ??

you can delete file after finish this execution if you don't want to keep the file.

you can also refer this way https://stackoverflow.com/a/12420416/284240

edited May 23 '17 at 12:29

Community

1
1

answered Sep 14 '12 at 11:10

Anant Dabhi

10,864
3
31
49

That was the suggestion that I made to my manager but he thought that reading the file using filestream would be better. – nick gowdy Sep 14 '12 at 11:11

Reading a spreadsheetl file from fileupload control into filestream

1 Answers1