apply background color to specific cell in excel using openxml

Question

Following is the code I have and in the final method below, I do all the operation. Please ignore the return types of methods, I have changed it later.

public static byte[] CreateExcelDocument<T>(List<T> list, string filename)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(ListToDataTable(list));
            byte[] byteArray = CreateExcelDocumentAsStream(ds, filename);
            return byteArray;
        }
public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
        {
            try
            {
                System.IO.MemoryStream stream = new System.IO.MemoryStream();
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
                {
                    WriteExcelFile(ds, document);
                }
                stream.Flush();
                stream.Position = 0;

                Response.ClearContent();
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";

                //  NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
                //  manually added System.Web to this project's References.

                Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
                Response.AddHeader("content-disposition", "attachment; filename=" + filename);
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                byte[] data1 = new byte[stream.Length];
                stream.Read(data1, 0, data1.Length);
                stream.Close();
                Response.BinaryWrite(data1);
                Response.Flush();
                Response.End();

                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine("Failed, exception thrown: " + ex.Message);
                return false;
            }
        }

private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
        {
            //  Create the Excel file contents.  This function is used when creating an Excel file either writing 
            //  to a file, or writing to a MemoryStream.
            spreadsheet.AddWorkbookPart();
            spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
            spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

            //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
            WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
            //var workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            Stylesheet stylesheet = new Stylesheet(new Fills(
                // Index 0 - required, reserved by Excel - no pattern
                    new Fill(new PatternFill { PatternType = PatternValues.None }),
                // Index 1 - required, reserved by Excel - fill of gray 125
                    new Fill(new PatternFill { PatternType = PatternValues.Gray125 }),
                // Index 2 - no pattern text on gray background
                    new Fill(new PatternFill
                    {
                        PatternType = PatternValues.Solid,
                        BackgroundColor = new BackgroundColor { Indexed = 64U },
                        ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" }
                    })
                ));            
            workbookStylesPart.Stylesheet = stylesheet;
            workbookStylesPart.Stylesheet.Save();
            // create a solid red fill



            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
            uint worksheetNumber = 1;
            Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            foreach (DataTable dt in ds.Tables)
            {
                //  For each worksheet you want to create
                string worksheetName = dt.TableName;

                //  Create worksheet part, and add it to the sheets collection in workbook
                WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
                sheets.Append(sheet);

                //  Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
                WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

                worksheetNumber++;
            }

            spreadsheet.WorkbookPart.Workbook.Save();            
            spreadsheet.Close();
        }

        private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
        {
            OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetData());

            string cellValue = "";

            //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
            //
            //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
            //  cells of data, we'll know if to write Text values or Numeric cell values.
            int numberOfColumns = dt.Columns.Count;
            bool[] IsNumericColumn = new bool[numberOfColumns];

            string[] excelColumnNames = new string[numberOfColumns];
            for (int n = 0; n < numberOfColumns; n++)
                excelColumnNames[n] = GetExcelColumnName(n);

            //
            //  Create the Header row in our Excel Worksheet
            //
            uint rowIndex = 1;

            writer.WriteStartElement(new Row { RowIndex = rowIndex });
            for (int colInx = 0; colInx < numberOfColumns; colInx++)
            {
                DataColumn col = dt.Columns[colInx];
                //AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
                AppendTextCell1(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
                IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
            }
            writer.WriteEndElement();   //  End of header "Row"

            //
            //  Now, step through each row of data in our DataTable...
            //
            double cellNumericValue = 0;
            foreach (DataRow dr in dt.Rows)
            {
                // ...create a new row, and append a set of this row's data to it.
                ++rowIndex;

                writer.WriteStartElement(new Row { RowIndex = rowIndex });

                for (int colInx = 0; colInx < numberOfColumns; colInx++)
                {
                    cellValue = dr.ItemArray[colInx].ToString();

                    // Create cell with data
                    if (IsNumericColumn[colInx])
                    {
                        //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
                        //  If this numeric value is NULL, then don't write anything to the Excel file.
                        cellNumericValue = 0;
                        if (double.TryParse(cellValue, out cellNumericValue))
                        {
                            cellValue = cellNumericValue.ToString();
                            AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                        }
                    }
                    else
                    {
                        //  For text cells, just write the input data straight out to the Excel file.
                        AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
                    }
                }
                writer.WriteEndElement(); //  End of Row
            }
            writer.WriteEndElement(); //  End of SheetData
            writer.WriteEndElement(); //  End of worksheet

            writer.Close();
        }

        private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            //writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
            writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });            
        }
        private static void AppendTextCell1(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            //writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
            writer.WriteElement(new Cell(new CellValue(cellStringValue)) { CellReference = cellReference, DataType = CellValues.String, StyleIndex = 2 });
        }

        private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number });
        }

Above is my code. I do try to add color by filling in stylesheet, however i dont understand the concept of styleindex to apply to a cell. Please help.

Solution

A console application using OPENXML which will generate excel file and set background color

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.IO;

namespace OpenXMLBGColorOfACell
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                WorkbookPart workbookPart = null;

                try
                {
                    using (var memoryStream = new MemoryStream())
                    {
                        using (var excel = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
                        {
                            workbookPart = excel.AddWorkbookPart();
                            workbookPart.Workbook = new Workbook();
                            uint sheetId = 1;
                            excel.WorkbookPart.Workbook.Sheets = new Sheets();
                            Sheets sheets = excel.WorkbookPart.Workbook.GetFirstChild<Sheets>();

                            WorkbookStylesPart stylesPart = excel.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                            stylesPart.Stylesheet = GenerateStyleSheet();
                            stylesPart.Stylesheet.Save();


                            string relationshipId = "rId1";
                            WorksheetPart wSheetPart = workbookPart.AddNewPart<WorksheetPart>(relationshipId);
                            string sheetName = "BackgroundColor";
                            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                            sheets.Append(sheet);

                            Worksheet worksheet = new Worksheet();

                            wSheetPart.Worksheet = worksheet;

                            SheetData sheetData = new SheetData();
                            worksheet.Append(sheetData);

                            string[] excelColumns = new string[] { "A", "B", "C", "D", "E", "F", "G" };


                            AddToCell(sheetData,4, 2, excelColumns[0], CellValues.String, "Test Cell Background");




                            excel.Close();
                        }

                        FileStream fileStream = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "CellBackgroundColor.xlsx", FileMode.Create, FileAccess.Write);
                        memoryStream.WriteTo(fileStream);
                        fileStream.Close();
                        memoryStream.Close();
                    }
                }
                catch (Exception ex)
                {

                    throw ex;
                }
            }
            catch (Exception ex)
            {

                // logging, etc.
            }
        }

        public static void AddToCell(SheetData sheetData, UInt32Value styleIndex, UInt32 uint32rowIndex, string strColumnName, DocumentFormat.OpenXml.EnumValue<CellValues> CellDataType, string strCellValue)
        {
            Row row = new Row() { RowIndex = uint32rowIndex };
            Cell cell = new Cell();

            cell = new Cell() { StyleIndex = styleIndex };
            cell.CellReference = strColumnName + row.RowIndex.ToString();
            cell.DataType = CellDataType;
            cell.CellValue = new CellValue(strCellValue);
            row.AppendChild(cell);

            sheetData.Append(row);
        }

        public static Stylesheet GenerateStyleSheet()
        {
            return new Stylesheet(
            new DocumentFormat.OpenXml.Spreadsheet.Fonts(
            new DocumentFormat.OpenXml.Spreadsheet.Font(new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),// Index 0 - The default font.
            new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 1 - The bold font.
            new Font(new Italic(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 2 - The Italic font.
            new Font(new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 3 - The Times Roman font. with 16 size
            new Font(new Bold(), new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 4 - The Times Roman font. with 16 size
            new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } }, new FontName() { Val = "Calibri" })  // Index 5 - The bold font.

            ),
            new Fills(
            new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 0 - The default fill.
            new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.None }),
            new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 1 - The default fill of gray 125 (required)
            new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.Gray125 }),
            new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 2 - The yellow fill.
            new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
            new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
            )
            { PatternType = PatternValues.Solid }),
            new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 3 - The Blue fill.
            new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
            new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "8EA9DB" } }
            )
            { PatternType = PatternValues.Solid })
            ),
            new Borders(
            new Border( // Index 0 - The default border.
            new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
            new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
            new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
            new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
            new DiagonalBorder()),
            new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
            new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.Thin },
            new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.Thin },
            new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.Thin },
            new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.Thin },
            new DiagonalBorder()),
                   new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
            new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.None },
            new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.None },
            new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
            new Color() { Auto = true }
            )
            { Style = BorderStyleValues.None },
            new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
            new Color() { Rgb = new HexBinaryValue() { Value = "FFA500" } }
            )
            { Style = BorderStyleValues.Thin },
            new DiagonalBorder())
            ),
            new CellFormats(
            new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
            new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 1 - Bold
            new CellFormat() { FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 2 - Italic
            new CellFormat() { FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 3 - Times Roman
            new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }, // Index 4 - Yellow Fill
            new CellFormat( // Index 5 - Alignment
            new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
            )
            { FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
            new CellFormat() { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // Index 6 - Border
             new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 7 - Alignment
             { FontId = 1, FillId = 0, BorderId = 0, ApplyAlignment = true },

             new CellFormat() { FontId = 4, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 8 - Times Roman
             new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 0, FillId = 0, BorderId = 2, ApplyFont = true }, // Index 9 - Bottom Border with Color 70AD47
             new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 10 - Alignment
             { FontId = 5, FillId = 3, BorderId = 0, ApplyAlignment = true }


             )
            ); // return
        }




    }
}

Comments

[dmoore1181]

This is extremely useful for getting started with OpenXML. I was able to use this as a good starting point, add a bunch of enums and get a proof of concept going quickly because of your answer. Thank you.

[Foxhunt]

I saw a lot of this question asked, 1st answer to work. Any idea why the Index 1 Fill is required ?