将一个电子表格文件从fileupload控件读入文件流

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.

我试图找到使用fileupload控件选择excel电子表格(xls和xlsx)的最佳方法,然后使用filestream对象解析它,然后填充数据集。

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.

到目前为止,这是我的代码,它完成了工作,但它的不同之处在于我将excel电子表格保存到我的解决方案中的文件夹,然后使用Microsoft ACE OLEDB连接查询数据。

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

是否有一种更清晰的方式来编写我缺少的代码。谢谢!!

1 个解决方案

#1

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

如果你不能保存uploded文件,你如何将数据源提供给你的oleDbConnection字符串?

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

如果您不想保留文件,则可以在完成此执行后删除文件。

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

你也可以这样参考https://*.com/a/12420416/284240

#1