在ASP.NET中读取Excel文件而不保存到磁盘

本文允许我有机会演示我最新的最喜欢的开源库的功能: epplus.。 epplus是一个.NET库,使用Open Office XML格式(XLSX)读取和写入Excel 2007+文件。这种奇妙的,易于使用(和免费的)库,可以在ASP.NET应用程序中使用Excel,而无需安装Access数据库引擎(ACE)或必须在Web服务器上使用不受支持的Office自动化(假设您可以甚至在那里安装办公室)。通过在最近出现在ASP.NET论坛上出现的类似问题的皮疹提示,本文介绍读取上传的Excel文件的内容而不保存它,并在Web窗体GridView中显示数据。

这首先要做的是将epplus添加到您的应用程序中。我已链接到项目网站,其中可用下载,但推荐的方式将这种库添加到项目中是通过Nuget - 如果库在那里托管,而且这个是。它可以通过键入添加到您的网站

install-package  EPPlus

进入Package Manager控制台。

以下部分代码是一个简单的.aspx文件,包括文件上传控制,按钮和gridview:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ExcelUpload.aspx.cs" Inherits="WebFormsTest.ExcelUpload" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
    <br />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</asp:Content>

该按钮有一个点击的事件处理程序连接到它。以下是它的代码以及文件内容背后的其余代码:

using OfficeOpenXml;
using System;
using System.IO;

namespace WebFormsTest
{
    public partial class ExcelUpload : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
                {
                    ExcelPackage package = new ExcelPackage(FileUpload1.FileContent);
                    GridView1.DataSource = package.ToDataTable();
                    GridView1.DataBind();
                }
            }
        }
    }
}

The things of note here start with the using directive at the top of the file, making the OfficeOpenXml namespace, which contains most of the EPPlus features, available to the code-behind. The button click event handler code checks to see if a file was uploaded and if it has the correct extension. An ExcelPackage instance is created from the upload's FileContent property, which is a Stream. This is subjected to an extension method (ToDataTable) that returns a DataTable and then used as the datasource for a GridView. Finally, DataBind is called and the content is displayed in the browser.

MVC 6和EF 7

Here's the ToDataTable extension method:

using OfficeOpenXml;
using System.Data;
using System.Linq;

namespace WebFormsTest
{
    public static class ExcelPackageExtensions
    {
        public static DataTable ToDataTable(this ExcelPackage package)
        {
            ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
            DataTable table = new DataTable();
            foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
            {
                table.Columns.Add(firstRowCell.Text);
            }

            for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
            {
                var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
                var newRow = table.NewRow();
                foreach (var cell in row)
                {
                    newRow[cell.Start.Column - 1] = cell.Text;
                }
                table.Rows.Add(newRow);
            }
            return table;
        }
    }
}

At no point during the process is the uploaded file saved to disk. The extension method accesses the first worksheet and loops through its contents, adding rows to a DataTable as it goes. This then returned from the method. The code in the method doesn't include any kind of error checking to keep things simple. For example, you would obviously want to check that the Worksheets collection isn't empty before you use the First method on it. The method also assumes that the worksheet has a header row. The for loop starts with the second row when copying data to the DataTable.

概括

这篇简短的文章显示如何使用免费的EPPlus库读取上载的Excel 2007+文件的内容,而不首先将其保存到磁盘。 EPPLUS是一个非常强大的工具,我将在未来的文章中探讨其一些能力。