EPPlus Open Office Xml Office solution using C#

EPPlus

Is an open Office XML solution, highly useful library capable to manipulate Microsoft Excel similar to Introp APIs. Why EPPlus? As per Microsoft the solutions (Excel, Word, PowerPoint, Outlook etc) built for client architecture but what about Servers? Let’s take an example that I have recently faced and EPPlus helped me a lot here.

Consider you have created application which performs certain calculations using Excel application and the application is deployed over IIS or other web server. The servers are configured for high security and will not allow to retrieve COM using code. You will try changing the DCOM configuration by adding exceptions but nothing will work. Here EPPlus works as an alternate to your problem.

Installation

Open NuGet Manager console and type following in the command:

PM> Install-Package EPPlus

Code example

using System;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Threading;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create new file
            string fileName = @"C:\\Users\\nawazish\\Downloads\\test.xlsx";

            //set license
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            //Object for new excel
            ExcelPackage excel =new ExcelPackage();
            
            //Add worksheet
            var osheet = excel.Workbook.Worksheets.Add("New Sheet");

            //Write some data
            osheet.Cells["A1"].Value = "No.";
            //Formatting
            osheet.Cells["A1"].Style.Font.Bold = true;
            osheet.Cells["A1"].Style.Font.Size = 20;
            osheet.Column(1).AutoFit();

            //Write values from row number 2 to 10
            int col = 1;
            for (int i = 2; i <= 10; i++)
            {
                osheet.Cells[i, col].Value = i;
            }

            if (File.Exists(fileName))
                File.Delete(fileName);
            // Create excel file on physical disk  
            FileStream objFileStrm = File.Create(fileName);
            objFileStrm.Close();

            // Write content to excel file  
            File.WriteAllBytes(fileName, excel.GetAsByteArray());
            
            //Close Excel package 
            excel.Dispose();
            Console.ReadKey();
        }
    }
}

Above example covers everything that you might need to manipulate your excel. LicenseContext should be set to NonCommercial if you are not using License otherwise, code will not work.

Next >> String vs. StringBuilder in C# with code example

Share on Facebook

Tweet