Named Styles "Applies To" function of Excel

[jongleur1983]

If you have a bug, request of a new feature, make sure you follow these guidelines [x] Write a detailed description of your issue. [] Attach a test to reproduce your issue, if it is a bug or unexpected behaviour. This is very important. [] If your issue requires a template xlsx file to be reproduced, make sure that you attach it to the issue.

With a little bit of luck this ticket collapses to a question, but I fear it doesn't. I'd like to apply named styles to certain CellRanges. These named styles are defined in the Excel document I loaded as Template.

Accessing the styles is easy, using them is as well, but there's a big difference between using EPPlus in code and Excel itself:

Excel (at least Excel 2017) supports to define what the cell format contains/defines, see (sorry for the German interface).

Using this Format I don't get NumberFormat and Alignment from it, as those are deselected here.

It would be great to be able to use the same in EPPlus.

My Use case here is: I have columns with a defined Format (I'd like to store that as a named format, but only applying to the NumberFormat and alignment (first 2 checkboxes in the screenshot), and some special rows defining different formats (e.g. when grouping etc.). Their format should apply to Colors, Fills etc. (the other boxes).

If possible allready I'd like to know how (and in that case sorry for opening the issue here), else I'd like to see the support for that added:

• reading NamedCellStyles should include those "filters",
• applying those cell styles should recognize them.

[jongleur1983]

Looking how I could achieve the same I see, that EPPlus already has the bool properties ApplyNumberFormat, ApplyFont, ApplyFill, ApplyBorder, ApplyAlignment and ApplyProtection. Those are read from Excel successfully, but never used through the whole library.

The flags are part of the ExcelXfs class and accessible (well, from inside EPPlus, as they are internal) via Workbook.Styles.CellStyleXfs[i].Apply*

For my current use case I know the filters at compile time, so I use this method

public void ApplyStyle(
  ExcelRange cells,
  string styleName,
  bool applyNumberFormat = false,
  bool applyAlignment = false,
  bool applyFonts = false,
  bool applyBorders = false,
  bool applyFill = false,
  bool applyProtection = false)
{
  if (applyNumberFormat 
      && applyAlignment 
      && applyFonts 
      && applyBorders 
      && applyFill 
      && applyProtection)
  {
    cells.StyleName = styleName;
  }
  else
  {
    var style = this.GetStyleByName(cells.Worksheet.Workbook, styleName).Style;
    if (applyNumberFormat)
    {
      cells.Style.Numberformat.Format = style.Numberformat.Format;
    }

    if (applyAlignment)
    {
      cells.Style.HorizontalAlignment = style.HorizontalAlignment;
      cells.Style.VerticalAlignment = style.VerticalAlignment;
      cells.Style.Indent = style.Indent;

      cells.Style.WrapText = style.WrapText;
      cells.Style.ShrinkToFit = style.ShrinkToFit;

      cells.Style.ReadingOrder = style.ReadingOrder;
      cells.Style.TextRotation = style.TextRotation;
    }

    if (applyFonts)
    {
      cells.Style.Font = style.Font;
    }

    if (applyBorders)
    {
      cells.Style.Border = style.Border;
    }

    if (applyFill)
    {
      cells.Style.Fill = style.Fill;
    }

    if (applyProtection)
    {
      cells.Style.Hidden = style.Hidden;
      cells.Style.Locked = style.Locked;
    }
  }
}