OpenXml

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXMLSample
{
    public class StyleSheetFormats
    {
        public void FormatStyleSheet(string fileName)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                // Adding style
                WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
                stylePart.Stylesheet = GenerateStylesheet();
                stylePart.Stylesheet.Save();

                // Setting up columns
                Columns columns = new Columns(
                        new Column // hours column
                        {
                            Min = 1,
                            Max = 1,
                            Width = 4,
                            CustomWidth = true
                        },
                        new Column // day/hours
                        {
                            Min = 2,
                            Max = 180, // maxColumnCount
                            Width = 2.4,
                            CustomWidth = true
                        });

                worksheetPart.Worksheet.AppendChild(columns);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" };

                sheets.Append(sheet);

                workbookPart.Workbook.Save();

                List<Employee> employees = Employees.EmployeesList;

                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                // Constructing header
                Row row = new Row();

                row.Append(
                    ConstructCell("Id", CellValues.String, 2),
                    ConstructCell("Name", CellValues.String, 2),
                    ConstructCell("Birth Date", CellValues.String, 2),
                    ConstructCell("Salary", CellValues.String, 2));

                // Insert the header row to the Sheet Data
                sheetData.AppendChild(row);

                // Inserting each employee
                foreach (var employee in employees)
                {
                    row = new Row();

                    row.Append(
                        ConstructCell(employee.Id.ToString(), CellValues.Number, 1),
                        ConstructCell(employee.Name, CellValues.String, 1),
                        ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 1),
                        ConstructCell(employee.Salary.ToString(), CellValues.Number, 1));

                    sheetData.AppendChild(row);
                }

                worksheetPart.Worksheet.Save();
            }
        }

        private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0)
        {
            return new Cell()
            {
                CellValue = new CellValue(value),
                DataType = new EnumValue<CellValues>(dataType),
                StyleIndex = styleIndex
            };
        }

        private Stylesheet GenerateStylesheet()
        {
            Stylesheet styleSheet = null;

            Fonts fonts = new Fonts(
                new Font( // Index 0 - default
                    new FontSize() { Val = 10 }

                ),
                 new Font( // Index 1 - header
                    new FontSize() { Val = 12 },
                    new Bold(),
                    new Color() { Rgb = "FFFFFF" },
                    new FontName() { Val = "Calibri" }
                ),
                new Font( // Index 2 - cell innerText
                    new FontSize() { Val = 10 },
                    new Color() { Rgb = "FFFFFF" },
                    new FontName() { Val = "Calibri" }
                ));

            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "C6E0B4" } })
                    { PatternType = PatternValues.Solid }), // Index 1 - primary row
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFE699" } })
                    { PatternType = PatternValues.Solid }) // Index 2 - candle
                );

            Borders borders = new Borders(
                new Border(
                    new LeftBorder(),
                    new RightBorder(),
                    new TopBorder(),
                    new BottomBorder(),
                    new DiagonalBorder()), // index 0 - default
                new Border( // index 1 primary row
                    new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Dotted },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None }),
                new Border( // index 2 one cell candle black border
                    new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None }),
                new Border( // index 3 middle candle cell black border
                    new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None }),
                new Border( // index 4 top candle cell black border
                    new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None }),
                new Border( // index 5 bottom candle cell black border
                    new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                    new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None }),
                new Border( // index 6 header cell gray border
                    new LeftBorder(new Color() { Rgb = new HexBinaryValue() { Value = "808080" } }) { Style = BorderStyleValues.Thin },
                    new RightBorder(new Color() { Rgb = new HexBinaryValue() { Value = "808080" } }) { Style = BorderStyleValues.Thin },
                    new TopBorder(new Color() { Rgb = new HexBinaryValue() { Value = "808080" } }) { Style = BorderStyleValues.Thin },
                    new BottomBorder(new Color() { Rgb = new HexBinaryValue() { Value = "808080" } }) { Style = BorderStyleValues.Thin },
                    new DiagonalBorder(new Color() { Auto = true }) { Style = BorderStyleValues.None })
                );

            CellFormats cellFormats = new CellFormats(
                new CellFormat(), // default
                new CellFormat { FontId = 1, FillId = 0, BorderId = 6, ApplyFont = true, ApplyBorder = true }, // header
                new CellFormat { FontId = 0, FillId = 1, BorderId = 1, ApplyBorder = true, ApplyFont = true, ApplyFill = true }, // primary row cell
                new CellFormat { FontId = 2, FillId = 2, BorderId = 2, ApplyBorder = true, ApplyFont = true, ApplyFill = true }, // one candle cell
                new CellFormat { FontId = 2, FillId = 2, BorderId = 3, ApplyBorder = true, ApplyFont = true, ApplyFill = true }, // middle candle cell
                new CellFormat { FontId = 2, FillId = 2, BorderId = 4, ApplyBorder = true, ApplyFont = true, ApplyFill = true }, // top candle cell
                new CellFormat { FontId = 2, FillId = 2, BorderId = 5, ApplyBorder = true, ApplyFont = true, ApplyFill = true }  // bottom candle cell
                );

            styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

            return styleSheet;
        }
    }
}