Commonly used EPPlus functions to format cells

The same formatting options you would have creating an Excel spreadsheets are generally available to you in an EPPlus report, such as:
  • Font size, color, font
  • Font attributes (bold, italic, etc.)
  • Column / Row size
  • Cell / Range color, borders
  • Justification (Vertical or Horizontal)
Options for identifying the syntax for formatting:
  • Internet search, e.g. “EPPlus font color” will yield many examples from many sources
  • Record a macro in Excel, then view the code
  • Implementation Accelerator and Base databases in 12.14 and later include several reports using EPPlus that have many examples to help you. If you have upgraded from an earlier version and would like example scripts, please contact Support.

Example

Add a lower border under a range of cells:
sheet1.Cells(5, 1, 5, 10).Style.Border.Bottom.Style = ExcelBorderStyle.Thin
Apply bolding to a value, merge a range of cells, and apply center justification:
sheet1.Cells(2, 1).Style.Font.Bold=True
	sheet1.Cells(2, 1, 2, 6).Merge = true
	sheet1.Cells(2, 1, 2, 6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center
Apply color (conditionally selected) and bold font to a value and cell:
'Add NutriScore value if present at endcol + 2 for title, endcol + 3 for value
    dim sNS as string = ObjProperty("VALUE.TPALL", "", "", "NUTRISCORE", "PARAM_CODE")
    if isblank(sNS) = 0 then
        sheet1.Cells(2, 8).Value = "Nutri-Score Result:"
        sheet1.Cells(2, 8).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right

        sheet1.Cells(2, 9).Value = sNS
        sheet1.Cells(2, 9).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center
        sheet1.Cells(2, 9).Style.Font.Color.SetColor(Color.White)
        sheet1.Cells(2, 9).Style.Font.Size = 14
        sheet1.Cells(2, 9).Style.Font.Bold = True
        
        Using Rng1 As ExcelRange = sheet1.Cells(titlerow, endcol + 3)
	Rng1.Style.Fill.PatternType = Style.ExcelFillStyle.Solid
	if sNS = "E" then 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(201, 60, 3))
	elseif sNS = "D" then 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.Orange)
	elseif sNS = "C" then 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.Yellow)
	elseif sNS = "B" then 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.LightGreen)
	elseif sNS = "A" then 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.DarkGreen)
	else 
	    Rng1.Style.Fill.BackgroundColor.SetColor(Color.Black)
	end if
End Using