This first thing to do is to add EPPlus to your application. I have linked to the project site where downloads are available, but the recommended way to add this kind of library to your project is via Nuget - if the library is hosted there, and this one is. It can be added to your site by typing
install-package EPPlus
into the Package Manager Console.
The following section of code is a simple .aspx file that includes a file upload control, button and 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>
The button has a click event handler wired up to it. Here is the code for it along with the rest of the code behind file contents:
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.
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.
Summary
This short article shows how to use the free EPPlus library to read the contents of an uploaded Excel 2007+ file without saving it to disk first. EPPlus is a very powerful tool and I shall explore some more of its capabilities in future articles.
