Reading Excel Files Without Saving To Disk In ASP.NET

4.47 (32 votes)

This article allows me the opportunity to demonstrate a feature of my newest favourite open source library: EPPlus. EPPlus is a .net library that reads and writes Excel 2007+ files using the Open Office Xml format (xlsx). This fantastic, easy to use (and free) library enables the use of Excel in ASP.NET applications without the need to install the Access Database Engine (ACE) or having to mess about with unsupported Office Automation on the web server (assuming you can even get Office installed there). Prompted by a rash of similar questions that appeared on the ASP.NET forums recently, this article looks at reading the contents of an uploaded Excel file without saving it, and displaying the data in a Web Forms GridView.

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.

MVC 6 and 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.

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.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 48852

16 Comments

- Satyabrata Mohapatra

Ahh.....this is awesome.
Happy to see after a long time you wrote a article on web form :D

- Manoj Kulkarni

Nice article. Thank you

- Sunny Okoro

This is the easiest method I have ever come across in reading from an excel file without uploading to disk.

Thanks a lot buddy.

- jayak

If I want to copy the data from this excel file to and import it to an existing or new ms sql server table using razor, how would we do that?

- Mike

@jayak

You can loop through the DataTable that gets created in the ToDataTable method and insert each row one by one.

- Peter Molloy

Very good article. Clean and simple. The best way to develop powerful agile classes. Thank you

- Emilio

Nice,
I would like to add that I'd have called ExcelPackage within an using block because it implements the IDisposable interface:
using (ExcelPackage package = new ExcelPackage(FileUpload1.FileContent))
{
GridView1.DataSource = package.ToDataTable();
GridView1.DataBind();
}

- Deepak Nagar

You made my day. thanks a lot. such a good article.

- Gigin Krishnan

Wonderful article..it is very simple to use. thanks

- Sumith

Nice Article not registered any provider in the local machine.Its simple.

- Abdul Latif

Could anyone please help me, I am getting : "OfficeOpenXml.ExcelPackage" does not contain a definition for 'ToDataTable'

- Mike

@Abdul,

It's an extension method. The code for it is on the article.

- Richardson

Nice!!!

And for dynamic (merged) Excel columns?

- venky

I am getting following error please help me.

1.'OfficeOpenXml.ExcelPackage' does not contain a definition for 'ToDataTable'

2.'OfficeOpenXml.ExcelPackage' does not contain a definition for 'First'

- Mike

@venky,

Read the reply to Abdul's comment.

- David Valdez

Muchas, muchas, gracias.
Thank you so much from Dominican Republic.

Recent Comments

Gfw 03/02/2017 09:48
In response to Free SSL Certificates On IIS With LetsEncrypt
I have used WinSimple for about the last 9 months - works great. One thing that you want to make of...

Ted Driver 02/02/2017 13:24
In response to Free SSL Certificates On IIS With LetsEncrypt
This looks great is you have command line access to your web server - what about those of us on Is...

Carl T. 06/11/2016 05:43
In response to Server.MapPath Equivalent in ASP.NET Core
Very succinct and easy to follow. Worked perfectly the first time for me. Thanks!!...

Manoj Kulkarni 04/11/2016 05:47
In response to Entity Framework Core DbContext Updated
Great post....

Sivu 19/10/2016 08:21
In response to Entity Framework Core TrackGraph For Disconnected Data
Oh that's very very very nice ! Thanks for the write up Mike, much appreciated for the taking the to...

Mark 12/10/2016 16:42
In response to ASP.NET Web Pages vNext or Razor Pages
Although "Web Pages" was removed from the roadmap, has it just been renamed to "Razor Pages"?...

Satyabrata 12/10/2016 09:20
In response to Entity Framework Core TrackGraph For Disconnected Data
Nice article. Please write more articles featuring ASP.Net web pages. Thank you...

Julian 26/09/2016 14:27
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Fantastic, many thanks Mike! Had got half way down this road before finding your article - saved...

Abolfazl Roshanzamir 14/09/2016 05:36
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Nice article. Thanke you so much ....

cyrus 02/09/2016 15:12
In response to ASP.NET Web Pages vNext or Razor Pages
I've got some news. As Damian stated in this link: https://github.com/aspnet/Mvc/issues/5208 “We...