How to add colour to particular excel columns based on conditions using openxml?

Question

I'm generating an excel sheet and I want to color the excel columns based on conditions. Now my all excel columns get red colors. I want to color only particular column names.

I'm using open XML for generating excel, is there any way to find the cells to apply color

 using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

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

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

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

                sheets.Append(sheet);

                var stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet.AddNamespaceDeclaration("mc", "http: //schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet.AddNamespaceDeclaration("x14ac", "http: //schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

                var fills = new Fills() { Count = 5U };
                var fonts = new Fonts() { Count = 1U, KnownFonts = true };
               // var cellFormats = new CellFormats();
                Font font = new Font();
                font.Append(new Color() { Rgb = "ff0000" });
                fonts.Append(font);

               // cellFormats.AppendChild(new CellFormat() { FontId = 0U });
                stylesheet.Append(fonts);
                stylesheet.Append(fills);
                //stylesheet.Append(cellFormats);
                //stylesheet.Append(fill);                 
                var stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
                stylePart.Stylesheet = stylesheet;
                stylePart.Stylesheet.Save();

                workbookPart.Workbook.Save();

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

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

                foreach (DataExchangeDefinition a in importColList)
                {
                    defnExist = true;
                    if (a.MustFieldYN == true)
                    {
                        row.Append(
                        ConstructCell(a.FieldCaption, CellValues.String));
                    }
                    else
                    {
                        row.Append(
                        ConstructCell(a.FieldCaption, CellValues.String));
                    }
                }

                if (defnExist == false)
                {
                    row.Append(
                    ConstructCell("Excel Template Definition Missing", CellValues.String));
                }
                // Insert the header row to the Sheet Data
                sheetData.AppendChild(row);

                // Inserting each employee

                worksheetPart.Worksheet.Save();
            }

Here is construct cell method

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

Is there any way to solve this?

er-sho · Accepted Answer · 2019-02-14 07:40:08Z

3

1) Add style sheet to your WorkBookPart

WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

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

Note: Add above WorkbookStylesPart code just below the WorkbookPart otherwise you can't get it to work.

2) Add below function that return stylesheet,

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 = 10 },
                new Bold(),
                new Color() { Rgb = "FFFFFF" }

            ));

        Fills fills = new Fills(
                new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
                new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
                { PatternType = PatternValues.Solid }) // Index 2 - header
            );

        Borders borders = new Borders(
                new Border(), // index 0 default
                new Border( // index 1 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())
            );

        CellFormats cellFormats = new CellFormats(
                new CellFormat(), // default
                new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
                new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header
            );

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

        return styleSheet;
    }

3) And your ConstructCell method,

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

4) And call your above method like

If you dont want to apply style then use below

ConstructCell(a.FieldCaption, CellValues.String));

And if you want to apply style on body cell then use below

ConstructCell(a.FieldCaption, CellValues.String, 1);

And if you want to apply style on header cell then use below

ConstructCell(a.FieldCaption, CellValues.String, 2);

edited Feb 14, 2019 at 7:40

answered Feb 14, 2019 at 6:22

er-sho

9,8212 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Dhanil Dinesan Over a year ago

Thank you very much for your effort. Let me try with this

Dhanil Dinesan Over a year ago

Its showing error before opening excel Repaired Records: Cell information from /xl/worksheets/sheet1.xml part and the all columns are colored same ie Red

er-sho Over a year ago

show me how you implemented my answer to your code? add your code in your post

er-sho Over a year ago

Add this stylePart code just below this line => workbookPart.Workbook = new Workbook(); see my step 1

Dhanil Dinesan Over a year ago

thank you very much for reply me, i find a solution and its working.

|

Collectives™ on Stack Overflow

How to add colour to particular excel columns based on conditions using openxml?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related