/EPPlusTutorial

Code snippets for Excel creation with EPPlus

Primary LanguageC#MIT LicenseMIT

EPPlusTutorial

Attention: EPPlus is no longer free for commercial use

The UnitTests can be executed to generate Excels in bin/Debug/excels. (Some tests even contain assertions!)

# Install last free version:
Install-Package EPPlus -Version 4.5.3

# Install commercial version:
Install-Package EPPlus

1-QuickTutorial

  • Basic usage
  • Loading & saving
  • Selecting cells
  • Writing values
  • Formatting sheets, columns and cells
  • Conditional formatting

2-Formulas-DataValidation

  • BasicFormulas
  • DataValidation
  • Attaching a logger to the FormulaParser

3-Import

Loading data from

  • LoadFromCollection & LoadFromArrays (IEnumerable)
  • LoadFromDataTable & DataReader
  • LoadFromText (CSV)

4-Miscellaneous

  • Printing
  • Workbook properties
  • Converting Excel Addresses
  • Adding comments & rich text
  • Protection against edit

5-Formulas-Reference

  • String manipulation
  • Numbers & Math
  • Date & Time
  • Boolean logic

Charts

Don't seem to work for LibreOffice. Example code can be found in the official EPPlus examples.

  • Sample4.cs: Basic example
  • Sample5.cs: A pie
  • Sample6.cs: Pretty nifty, worth checking out!

They also have a chart example on their main documentation.

Wish List

Will we cover these also, sometime?

  • ConditionalFormatting in more detail: See Sample14.cs
  • Filtering
  • Grouping and ungrouping
  • Tables
  • Inserting VBA: See Sample15.cs
  • Numberformat.Format = [$$-409] --> Get info on those numbers
  • 1-QuickTutorial ExcelPrinting: insert a company picture in the print footer: sheet.HeaderFooter.EvenHeader.InsertPicture()
  • Password protection: Add Encryption
  • WebApi integration and calling code for Superagent, Angular Http and Fetch? :)

WebApi:

public class ExcelResult : ActionResult
{
    public string FileName { get; set; }
    public ExcelPackage Package { get; set; }

    public override void ExecuteResult(ControllerContext context)
    {
        context.HttpContext.Response.Buffer = true;
        context.HttpContext.Response.Clear();
        context.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + FileName);
        context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
        context.HttpContext.Response.BinaryWrite(Package.GetAsByteArray());
    }
}

Adding a picture:

Bitmap icon = GetIcon(dir.FullName);
ws.Row(row).Height = height;
if (icon != null)
{
    ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon);
    pic.SetPosition((int)20 * (row - 1) + 2, 0);
}