DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Writing Large Datasets to Excel Files Using EPPlus in C#

Recently, I had to resolve an issue of a corrupt report file of an enterprise application in production. People suggested I use EPPlus, and I was NOT disappointed.

Ayobami Adewole user avatar by
Ayobami Adewole
·
Dec. 15, 17 · Tutorial
Like (2)
Save
Tweet
Share
52.92K Views

Join the DZone community and get the full member experience.

Join For Free

Prior to the availability of Open XML SDK and other Excel libraries in .NET, manipulating Microsoft Excel files from ASP.NET web applications was a daunting task. Open XML SDK makes it easy to manipulate documents that conform to the Office Open XML file format specifications — with a trade-off of writing plenty of lines of code. Similar to OpenXML, ClosedXML is another elegant library that further simplifies the process of reading and writing to Excel files and hides the complexities involved, without needing to deal with XML documents.

Recently, I had to resolve an issue of a corrupt report file of an enterprise application in production. Reviewing the logs indicated that the reporting module threw a System.OutOfMemory exception that originated from ClosedXML. The Excel file being created had five sheets with around 400,000 rows each and ClosedXML could not handle this large amount of data.

EPPlus

Apparently, a lot of folks have had the same issue using the library, and many suggested different workarounds or alternative libraries. Catching my attention from the various suggestions is the EPPlus library, which has been around for a while but never really had much publicity like OpenXML and ClosedXML. The library is available for use via Nugget.

To test the performance and robustness of this library, I decided to throw a large dataset at it. I tried to make it create five sheets in a single workbook and save 1M rows in each sheet. It took 61 seconds on a Dell Latitude E5470 (Intel Core i5, 8GB RAM, 250GB SSD) for the library to process and save the records. It handled the large data graciously and also with fewer lines of codes compared to ClosedXML. See the source code below.

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Epplus_Poc
{
    class Program
    {
        static void Main(string[] args)
        {
            List codeDetails = PopulateCodeDetails();
            FileInfo fileInfo = new FileInfo(@"C:\Temp\file.xlsx");
            using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
            {
                var workSheet = GetWorkSheet(excelPackage, 0);
                var workSheet1 = GetWorkSheet(excelPackage,1);
                var workSheet2= GetWorkSheet(excelPackage,2);
                var workSheet3= GetWorkSheet(excelPackage,3);
                var workSheet4 = GetWorkSheet(excelPackage,4);                
                workSheet.Cells["B2"].LoadFromCollection(codeDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
                workSheet1.Cells["B2"].LoadFromCollection(codeDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
                workSheet2.Cells["B2"].LoadFromCollection(codeDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
                workSheet3.Cells["B2"].LoadFromCollection(codeDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
                workSheet4.Cells["B2"].LoadFromCollection(codeDetails, false, OfficeOpenXml.Table.TableStyles.Medium1);
                excelPackage.Save();
            }
        }
        static ExcelWorksheet GetWorkSheet(ExcelPackage excelPackage, int count)
        {
            var workSheet = excelPackage.Workbook.Worksheets.Add("Content - "+count);
            workSheet.View.ShowGridLines = false;
            workSheet.Cells["B1"].Value = "Code";
            workSheet.Cells["C1"].Value = "Time";
            workSheet.Cells["D1"].Value = "Date";
            workSheet.Cells["B1:D1"].Style.Font.Bold = true;
            return workSheet;
        }
        public static List PopulateCodeDetails()
        {
            List codeDetails = new List();
            Random random = new Random();
            for(int i=1; i<=1000000; i++)
            {
                CodeDetail codeDetail = new CodeDetail();
                codeDetail.Code = random.Next(12324343).ToString();
                codeDetail.Time = DateTime.Now.ToShortTimeString();
                codeDetail.Date = DateTime.Now.ToShortDateString();
                codeDetails.Add(codeDetail);
            }
            return codeDetails;
        }
    }
    public class CodeDetail
    {
        public string Code { get; set; }
        public string Time { get; set; }
        public string Date { get; set; }
    }
}       

The next time you need to process a large dataset and save it to an Excel file, you ought to try EPPlus library.

Published at DZone with permission of Ayobami Adewole, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Building a Scalable Search Architecture
  • What Is Policy-as-Code? An Introduction to Open Policy Agent
  • Beginners’ Guide to Run a Linux Server Securely
  • Easy Smart Contract Debugging With Truffle’s Console.log

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: