从上传的Excel文件中获取数据，而不保存到文件系统中

I have a requirement to allow a user of this ASP.NET web application to upload a specifically formatted Excel spreadsheet, fill arrays with data from the spreadsheet, and bind the arrays to a Oracle stored procedure for validation and insertion into the database. I must be able to read the data from the Excel spreadsheet without being able to save it to the web server's hard disk. This is the part I cannot figure out how to do. Here's a simple code example.

我有一个要求允许这个ASP的用户。NET web应用程序上载一个特定格式的Excel电子表格，用电子表格中的数据填充数组，并将数组绑定到Oracle存储过程中，以便在数据库中进行验证和插入。我必须能够从Excel电子表格中读取数据，而不能将数据保存到web服务器的硬盘中。这是我不知道该怎么做的部分。这里有一个简单的代码示例。

<%--ASP.NET Declarative--%>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Send File" OnClick="Button1_Click" />

// C# Code-Behind
protected void Button1_Click(object sender, EventArgs e) {
    var postedFile = FileUpload1.PostedFile;

    // ... Read file in memory and put in format to send to stored procedure ...

}

Can anyone help me with this? I appreciate anyone's consideration.

有人能帮我一下吗?我很感激任何人的考虑。

thx,
gabe

谢谢,加布

6 个解决方案

#1

I found a great lightweight open source API on Codeplex for doing this called ExcelDataReader.

我在Codeplex上发现了一个很棒的轻量级开源API，叫做ExcelDataReader。

It can transform an input stream of an excel file into a System.Data.DataSet object (probably parsing using BIFF specs).

它可以将excel文件的输入流转换为System.Data。数据集对象(可能是使用BIFF规范解析)。

Here's the link:

这是链接:

http://www.codeplex.com/ExcelDataReader

http://www.codeplex.com/ExcelDataReader

Here's a code sample:

这里有一个代码示例:

<%--ASP.NET Declarative--%>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Send File" OnClick="Button1_Click" />
<asp:GridView ID="GridView1" runat="server" />

// C# Code-Behind
protected void Button1_Click(object sender, EventArgs e) {
    // the ExcelDataReader takes a System.IO.Stream object
    var excelReader = new ExcelDataReader(FileUpload1.FileContent);
    FileUpload1.FileContent.Close();

    DataSet wb = excelReader.WorkbookData;
    // get the first worksheet of the workbook
    DataTable dt = excelReader.WorkbookData.Tables[0];

    GridView1.DataSource = dt.AsDataView();
    GridView1.DataBind();
}

#2

Use the FileUpload1.FileContent Stream. I guess your Excel library can handle streams directly.

使用FileUpload1。FileContent流。我想您的Excel库可以直接处理流。

#3

The COM libraries of Excel does not support loading file from another source than file. But there exists a lot of third-party components, which allows you read/write excel files.

Excel的COM库不支持从文件以外的其他源加载文件。但是有很多第三方组件，允许您读/写excel文件。

Othervise you can see a documentation for th XLS file format at [MS-XLS]: Excel Binary File Format (.xls) Structure Specification.

另外，您可以在[MS-XLS]中看到XLS文件格式的文档:Excel二进制文件格式(. XLS)结构规范。

Or you can use a same way of office files processing like in Sharepoint Server. See Microsoft.Office.Excel.Server.WebServices Namespace.

或者也可以使用与Sharepoint服务器相同的office文件处理方式。看到Microsoft.Office.Excel.Server。web服务名称空间。

#4

maybe have look on csvreader, it reads csv, xls and xlsx:

可以看看csvreader，它会读取csv, xls和xlsx:

http://www.csvreader.com

#5

This is something I've been playing with recently.

这是我最近一直在玩的东西。

Check this post: Write an excel workbook to a memory stream .NET

查看这篇文章:编写一个excel工作簿到一个内存流。net

It points to a great library by Carlos Aguilar Mares, which lets you work with Excel workbooks as XML.

它指向卡洛斯•阿吉拉尔•马里斯(Carlos Aguilar Mares)的一个伟大的图书馆，它让你以XML格式处理Excel工作簿。

ExcelXMLWriter

You dont need Excel installed on the server (which is kinda breaking the MS licensing anyway as you are accessing this over the web).

您不需要在服务器上安装Excel(这有点违反了MS授权，因为您正在通过web访问它)。

You can load the Excel workbook as a stream using Workbook.Load(stream)

可以使用workbook . load (stream)将Excel工作簿作为流加载

#6

Could you have your users upload a CSV file instead? Dealing with a plain text file would be much easier. I had a similar issue before and I asked the users and they were OK, saved me tons of work.

你能让你的用户上传一个CSV文件吗?处理纯文本文件会容易得多。我以前也遇到过类似的问题，我问过用户，他们都还好，帮了我很多工作。

Good luck.

祝你好运。

#1