EPPlus Library Part-1

EPPLUS Library - Beginners Guide Part-1



Hindi Video : Click here
Source Code :  Click to download [672 KB]

What is EPPlus?
  • A library to manage excel spreadsheets. EPPlus is a .NET library that reads and writes Excel 2007/2010 or higher files using the Open Office Xml format. It is support .xlsx, .xlsm excel file format.
Why we are using EPPlus?
  • First of all EPPlus is a free tool.
  • We can do same thing by using Microsoft interop excel. When you are like to doing server side office automation. That is not a supported scenario by Microsoft. Follow this link : http://support.microsoft.com/kb/257757
How to attach EPPlus Library :
  • Option 1 : Download it from : http://epplus.codeplex.com/
  • Option 2 : To install EPPlus, run the following command in the Package Manager Console
    PM> : Install-Package EPPlus
EPPlus Supports :
  • Cell Ranges
  • Cell styling (Border, Color, Fill, Font, Number, Alignments)
  • Charts
  • Pictures
  • Shapes
  • Comments
  • Tables
  • Protection
  • Encryption
  • Pivot tables
  • Data validation
  • Conditional formatting
  • VBA
  • Formula calculation..etc
Today we are learning :
  • Create a demo an epplus project using .NET technologies (Language C#)

Open Visual Studio (Create a console application) :
using OfficeOpenXml;
using System.IO;
using System;
//add these namespace

class Program
    {
        static void Main(string[] args)
        {
            ExcelPackage ExcelPkg = new ExcelPackage();
            ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
           
            using (ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2])
            {
                Rng.Value = "Welcome to Everyday be coding - tutorials for beginners";
                Rng.Style.Font.Size = 16;
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Italic = true;
            }
            wsSheet1.Protection.IsProtected = false;
            wsSheet1.Protection.AllowSelectLockedCells = false;
            ExcelPkg.SaveAs(new FileInfo(@"D:\New.xlsx"));
        }
    }
  • Now build & execute this code. File is (New.xlsx) store on D: drive of computer.
Thank you for reading this article. 
Please subscribe my YouTube Channel & don't forget to like and share. 

YouTube :https://goo.gl/rt4tHH
Facebook :https://goo.gl/m2skDb
Twitter :https://goo.gl/nUwGnf

15 comments:

  1. thanks so much, i am a new user and i have few questions, i already have an excel file(will all formatted) and i just want to insert values into the sheet. i have to call the sheet from embedded resource.i am making xll plugins using excel-dna and now i want export the result into the sheet and save in local drive.Thank you

    ReplyDelete
  2. Thanks for your tutorial. It help me to begin with EPPlus

    ReplyDelete
  3. Nice but what is the purpose of wsSheet1.Cells[2, 2, 2, 2] ?
    I mean [2,2,2,2] and can I create dynamic columns?

    ReplyDelete
  4. Nice snippet to start with .. but what is the purpose of doing wsSheet1.Cells[2, 2, 2, 2] ? I mean 2,2,2,2 ? and can I create dynamic columns using EPPlus? Probably yes! then how?

    ReplyDelete
    Replies
    1. You will select the cell with them
      for example,
      excelWorksheet.Cells[FromRow: 5, FromCol: 1, ToRow: 5, ToCol: 3]
      OR
      excelWorksheet.Cells[5, 1,5, 3]
      OR
      excelWorksheet.Cells[Address: "A1"]
      OR
      excelWorksheet.Cells["A1"]

      Delete
    2. Get the count value if you want post data Row by row,
      excelWorksheet.Cells["A" + count].Value = "something";
      excelWorksheet.Cells["B" + count].Value = "something";
      excelWorksheet.Cells["C" + count].Value = "something";

      Delete
  5. Provide with the Link to get EPPLUS dll. the current one is not working

    ReplyDelete
  6. can you show how to read from an existing Excel Spredsheet please?

    ReplyDelete
  7. Thanks, this is the best resource I've found in the web about EPPlus to get started.

    ReplyDelete
  8. could you show how do we read and edit spreadsheets?

    ReplyDelete
  9. Sir, I have seen ur video on upload to Google Drive using vb.Net I want same in vba I want upload my excel file converted to pdf.

    ReplyDelete
  10. Can I save the Excel file in C > Desktop? Because it doesn't work

    ReplyDelete