阅读使用FileUpload控件上载的Excel文件，但不保存在服务器上

问题描述

需要能够读取ASP.NET使用FileUploadControl上传Excel文件。该解决方案将被托管的服务器上。我不想存储在服务器上的Excel文件。 我想直接转换Excel的内容转换为数据集或数据表和利用。

Need to be able to read an Excel file uploaded using FileUploadControl in ASP.NET. The solution will be hosted on a server. I do not want to store the Excel file on the server. I would like to directly convert the excel content into a dataset or a datatable and utilize.

下面是两种解决方案我已经找到了，但不会为我工作。

Below are the two solutions I already found but would not work for me.

1. LINQTOEXCEL - 这个方法的时候你有你的本地计算机上的Excel文件，你在本地机器上运行您的code。在我的情况下，用户试图使用托管服务器上的网页，从他的本地计算机上传的Excel文件。

1. LINQTOEXCEL - This method works when you have an excel file on your local machine and you are running your code on the local machine. In my case, the user is trying to upload an excel file from his local machine using a webpage hosted on a server.

ExcelDataReader - 我目前使用的这一个，但是这是一个第三方工具。我动不了这给我们的客户。此外，如果一个行/列交点被携带式，则该行/列交叉点的数据没有被读入的数据集

ExcelDataReader - I am currently using this one, but this is a third party tool. I cannot move this to our customer. Also if a row/column intersection is carrying a formula, then that row/column intersection's data is not being read into the dataset.

大多数时候我发现谷歌和计算器工作时，既对Excel和.NET解决方案是在同一台机器上的建议。但在我的，我需要的时候解决方案托管在服务器上它的工作，而用户试图使用其本地计算机上托管的网页上载的Excel。如果您有任何其他建议，请你让我知道？

Most of the suggestions i found on google and StackOverflow work when both the excel and the .NET solution are on the same machine. But in mine, I need it to work when the solution is hosted on a server, and users are trying to upload excel using the hosted webpage on their local machine.If you have any other suggestions, could you please let me know?

推荐答案

您可以使用<$c$c>InputStream财产 HttpPostedFile 读取文件到内存中。

You can use the InputStream property of the HttpPostedFile to read the file into memory.

下面是一个例子展示了如何创建一个数据表从 IO.Stream A HttpPostedFile 使用的 EPPlus

Here's an example which shows how to create a DataTable from the IO.Stream of a HttpPostedFile using EPPlus:

protected void UploadButton_Click(Object sender, EventArgs e)
{
    if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xlsx")
    {
        using (var excel = new ExcelPackage(FileUpload1.PostedFile.InputStream))
        {
            var tbl = new DataTable();
            var ws = excel.Workbook.Worksheets.First();
            var hasHeader = true;  // adjust accordingly
            // add DataColumns to DataTable
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                tbl.Columns.Add(hasHeader ? firstRowCell.Text
                    : String.Format("Column {0}", firstRowCell.Start.Column));

            // add DataRows to DataTable
            int startRow = hasHeader ? 2 : 1;
            for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                DataRow row = tbl.NewRow();
                foreach (var cell in wsRow)
                    row[cell.Start.Column - 1] = cell.Text;
                tbl.Rows.Add(row);
            }
            var msg = String.Format("DataTable successfully created from excel-file. Colum-count:{0} Row-count:{1}",
                                    tbl.Columns.Count, tbl.Rows.Count);
            UploadStatusLabel.Text = msg;
        }
    }
    else
    {
        UploadStatusLabel.Text = "You did not specify a file to upload.";
    }
}

下面是在 VB.NET版本

Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    If (FileUpload1.HasFile AndAlso IO.Path.GetExtension(FileUpload1.FileName) = ".xlsx") Then
        Using excel = New ExcelPackage(FileUpload1.PostedFile.InputStream)
            Dim tbl = New DataTable()
            Dim ws = excel.Workbook.Worksheets.First()
            Dim hasHeader = True ' change it if required '
            ' create DataColumns '
            For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
                tbl.Columns.Add(If(hasHeader,
                                   firstRowCell.Text,
                                   String.Format("Column {0}", firstRowCell.Start.Column)))
            Next
            ' add rows to DataTable '
            Dim startRow = If(hasHeader, 2, 1)
            For rowNum = startRow To ws.Dimension.End.Row
                Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
                Dim row = tbl.NewRow()
                For Each cell In wsRow
                    row(cell.Start.Column - 1) = cell.Text
                Next
                tbl.Rows.Add(row)
            Next
            Dim msg = String.Format("DataTable successfully created from excel-file Colum-count:{0} Row-count:{1}",
                                    tbl.Columns.Count, tbl.Rows.Count)
            UploadStatusLabel.Text = msg
        End Using
    Else
        UploadStatusLabel.Text = "You did not specify an excel-file to upload."
    End If
End Sub

有关为了完整起见，这里的ASPX：

For the sake of completeness, here's the aspx:

<div>
   <h4>Select a file to upload:</h4>

   <asp:FileUpload id="FileUpload1"
       runat="server">
   </asp:FileUpload>

   <br /><br />

   <asp:Button id="UploadButton"
       Text="Upload file"
       OnClick="UploadButton_Click"
       runat="server">
   </asp:Button>

   <hr />

   <asp:Label id="UploadStatusLabel"
       runat="server">
   </asp:Label>
</div>

这篇关于阅读使用FileUpload控件上载的Excel文件，但不保存在服务器上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！